How to show the field value based on other aggregate field

2 posts, 1 answers
  1. Su
    Su avatar
    1 posts
    Member since:
    Mar 2018

    Posted 20 Jun Link to this post

    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. 

  2. Answer
    Todor
    Admin
    Todor avatar
    175 posts

    Posted 22 Jun Link to this post

    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
Back to Top