This is a migrated thread and some comments may be shown as answers.

How to show the field value based on other aggregate field

1 Answer 225 Views
Report Designer (standalone)
This is a migrated thread and some comments may be shown as answers.
Su
Top achievements
Rank 1
Su asked on 20 Jun 2018, 06:12 AM

Hi,
I am creating the report with standalone designer. 
My sample datasource will be like this below,

Data Source
-----------
Name,QtyValue,DateTime
S001,100,2018-05-01
S001,200,2018-05-02
S001,300,2018-05-03
S001,50,2018-05-04
S001,250,2018-05-05
S001,100,2018-05-06
S002,200,2018-05-02
S002,30,2018-05-04
S002,100,2018-05-05

In my report group footer, I would like to display the 
Maximum/Minimum Qtyvalue and the DateTime value for that maximum value. 
I tried to use the expression 
IIF(Fields.QtyValue =  Max(Fields.QtyValue), Fields.DateTime, "")

It didn't work. Can you guys please advise me how to achieve.

Expected
---------
Name: S001

Max                                        Min
Value         Date                     Value            Date
300            2018-05-03          50                 2018-05-04


Actual
-------
Name: S001
Max                                Min
Value  Date                    Value          Date
300                                 50              

Thanks in advance. 

1 Answer, 1 is accepted

Sort by
0
Accepted
Todor
Telerik team
answered on 22 Jun 2018, 03:41 PM
Hello Su,

The requirement to display the DateTime when the QtyValue is max/min in the GroupFooter section can be achieved with a Custom Aggregate, i.e. a User Aggregate Function (lets say for the max GetValueForMaxValue) that receives as arguments Fields.QtyValue and Fields.DateTime, finds the max/min of the QtyValue while aggregating along the group values, and returns the corresponding DateTime.
The expression in the report will look like:

= GetValueForMaxValue(Fields.DateTime, Fields.QtyValue)

I have attached a sample (CustomAggregate) demonstrating this approach.
Note that the assembly containing the custom aggregate functions (CustomAggregates.dll) should be copied to the folder of the Standalone designer - (Telerik Reporting installation folder)\Report Designer (for example C:\Program Files (x86)\Progress\Telerik Reporting R2 2018\Report Designer), and registered in its configuration file (Telerik.ReportDesigner.exe.config) as explained in the Extending Report Designer article.


Another approach is to display the max/min group values in the Detail instead of GroupFooter section. The max/min for the corresponding group can be taken using the scope of the group, i.e.

=IIF(Fields.QtyValue = Exec('group', Min(Fields.QtyValue)), "Min: " + Fields.QtyValue + ", Date: " + Fields.DateTime, "")

This approach is demonstrated in the attached MinMaxSample report.

Regards,
Todor
Progress Telerik
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Tags
Report Designer (standalone)
Asked by
Su
Top achievements
Rank 1
Answers by
Todor
Telerik team
Share this question
or