How to sum multiple records that are returned in a table.

0 Answers 136 Views
Filtering Report Designer (standalone) Table
Michael
Top achievements
Rank 1
Iron
Michael asked on 24 Mar 2022, 09:00 PM
I have a table in a parent group (not the detail). I have two report parameters, and two filters. One is the Fiscal Year, and the other is the Fiscal Period. I am trying to do a YTD sum based on the filter fields.Fiscal Period <= Parameters.FiscalPeriod.Value, and then SUM(Fields.Value). If I have have period 3 selected, for example, I get three rows returned. How do I SUM all the returned rows based on the period I selected?
Dimitar
Telerik team
commented on 29 Mar 2022, 12:25 PM

I am not sure whether I have understood the case correctly, but from what I understand, after the filtering is applied, the table returns three rows of data(not sure if this actually represents three data rows or that the table has three rows produced after the filter that actually were generated from a group of data) and these values should be summed up.

If that is the case, then you can right-click on the Value column in your table, and if the Value field is a number(int, float, double or decimal), you should see an Add Totals option in the opened menu. Clicking it should automatically generate a new row for the table where the values will be summed up.

If that does not work, please attach a sample report with sample data to this thread so that I may inspect it locally.

Michael
Top achievements
Rank 1
Iron
commented on 29 Mar 2022, 01:31 PM

I am not seeing what you described. I've never seen this option. The table/field in the question is in the upper right of the report. Please see the uploaded report.
Dimitar
Telerik team
commented on 31 Mar 2022, 02:36 PM

Thank you for the report!

It appears that you have structured your report in a way that you are making tables through the use of the group and detail sections. However, this is not a table in the meaning of using the Table report item. The Add Totals functionality is only available for the table and crosstab report items, that is why the option is not visible when you right-click the textboxes.

Regarding the topic of summing the periods, If I understand correctly, you wish to sum the 1-30, 31-60, and over 60 periods into one sum, is that correct?

If you wish to sum the three periods, then you can combine their sums in the following way:

= Sum(Fields.Aging30) + Sum(Fields.Aging60) + Sum(Fields.Aging90)

Or you could create a calculated field for each of those sums, then you could sum up the calculated fields, see the Calculated Fields article for more information.

Michael
Top achievements
Rank 1
Iron
commented on 31 Mar 2022, 02:56 PM

Oh, I am so sorry, I uploaded the incorrect report!! This is the correct report!
Dimitar
Telerik team
commented on 05 Apr 2022, 12:03 PM

Thank you for the attached report!

I can open the report but this report also uses SQL data sources that I cannot connect to.

To reiterate the problem is that the sum: = SUM(Fields.SalesConvOnly) is displayed three times for each row, and is not actually the sum of all the rows, is that correct?

This is probably because the row of the textbox, in which this expression is located, is part of the table detail group. Using the Add Total method suggested earlier should create a new row outside this group with the summed data but I cannot test that because I cannot connect to the database thus the designer does not know if this field is numeric but it should work for you locally.

If the option is not selectable, you can manually add a row outside the table group by right-clicking on the textbox with that expression and selecting Inset Row -> Outside Group - Below. Then copy that expression into one of the textboxes on that row. Now, the sum should be correct.

I have attached a short video to demonstrate how to create such a row, please check it out.

Michael
Top achievements
Rank 1
Iron
commented on 05 Apr 2022, 03:04 PM

Yes, adding the Row --> Outside Group resolved the issue. I thought my first row in that table WAS outside the group, but apparently not. Thank you for the assistance!!

No answers yet. Maybe you can help?

Tags
Filtering Report Designer (standalone) Table
Asked by
Michael
Top achievements
Rank 1
Iron
Share this question
or