I am trying to create reports with Pie and Serial Charts using a SQL Query but it doesn’t show any data.
I want to show the total amount of products that have been purchased (Y axis) ordered by Purchase_Date (X axis)
I do the following steps:
1 - Create New Report - Add Report Bands - SQL Dataset Type - Copy my SQL Query
2 - Create a template. Pie or Serial Chart.
3 - Add Parameters: List of entities. Add corresponding Entity
Add Format: Date format dd/MM/yyyy
4 - Run the report and select all the data from the List of Entities
5 - The report runs but doesn’t show any data.
I run the query on SQL Managemet Studio and it shows 6 different products and the Chart leyend shows 6 different coloured circles but no data.
Thank you for reporting a problem, I created an issue.
To solve your problem you can use Groovy script or jpql.
Example:
Entity:
@Table(name = "TEST_PRODUCT")
@Entity(name = "test_Product")
public class Product extends StandardEntity {
private static final long serialVersionUID = -6000426574155447300L;
@Temporal(TemporalType.DATE)
@Column(name = "DATE_SALE")
protected Date dateSale;
@Column(name = "AMOUNT")
protected Integer amount;
...
}
Groovy-script:
import com.company.test.entity.Product
def result = []
def products = dataManager.load(Product.class)
.query('select e from test_Product e where e.id in :products order by e.dateSale')
.parameter('products', params.products)
.list()
for(def p : products) {
result << ['amount' : p.amount, 'dateSale' : date]
}
return result
JPQL:
select
e.amount as "amount",
e.dateSale as "dateSale"
from test_Product e
where e.id in ${products}
order by e.dateSale
