Any Sum/Count functions outside of the data model?
If I join 2 tables to get detail data yet in some reports want to sum a given field from the detail dataset, how would I do this?
AdminBhupesh Malhotra (Admin, GrapeCity) commented
@Jeremy, ActiveReports Server will add an attribute for each entity called "#<entity name>". This attribute is a count attribute for records from that entity.
You can use that attribute in tables and charts on the report. This same attribute will automatically aggregate if you use it in group headers or in rows on tables with parent entity data.
Jeremy Drice commented
How can I go about adding a count field rather than a total?
You can do this today by dragging out the "Sum <Field>" to the report. For example, using the ActiveTunes sample model we could create a report showing a list of customers from the Customer table/entity and then show a Sum of the Total field from the Invoice table/entity for each customer. To do that, take the following steps with the ActiveTunes sample model included with the product
1: Create a new report by clicking File>New and then selecting the "ActiveTunes" logical data model.
2: Select "Customer" in the "Entity Tree" and while Customer is selected, drag & drop the "Full Name" attribute onto the report page. You will be prompted to choose a Table or select one of the various chart types. Lets create a Table in this example.
Now you should have a table on the report surface with a single column: "Customer Full Name". With this table on the report page selected, on the left sidebar in the Entity Tree, you should see the "Invoices" table/entity underneath the highlighted Customer table/entity.
3: Click on the "Invoice" table/entity to select it. Then in the bottom in the "Attribute Tree", click the arrow beside Total to expand the aggregates for the Invoice Total. Drag & drop "Sum Total" right beside the Customer Full Name column onto the table on the report page.
Now, on the report you should see a table with a "Customer Full Name" column and a "Sum Total" column.
Preview the report and you should see a list of customers with the total amount of all invoices from that customer.
So this is how you can create a join across two tables in your database and use an aggregate function to summarize the detail data.
I hope this helps. If you have more questions please let us know.