Post reply

SSRS2008 - Retrieve max value for group based on date

  • July 10, 2015 at 5:03 am

    #319655

    Hello!

    I have a report that shows a monthly breakdown of money retrieved and outstanding for each account. In the group header, I need to sum up the paid figures (done), and only retrieve the outstanding amount for the latest 'MonthEnd' value.

    I've attached an image of a dummy version which will show what I mean. Instead of '36,093' in the Outstanding header, I need it to show '10,033' (which is the value for the latest date '28/02/2015'). Gross Paid and Net Paid still need to sum up values, so they are correct as they are. The date ranges for each group can be different - e.g 'CH1' may have Dec14 - Feb15, 'CH2' may have Aug14 - Dec14 etc.

    I know I can do this in the original TSQL query, but I wondered if there's a quick and easy way to do so in SSRS?

    So far I've managed to get this, but of course it just retrieves the same value (i.e 10,033) for every single group, as it is looking at the dataset as a whole rather than the tablix grouping.

    =lookup(MAX(Fields!MonthEnd.Value),Fields!MonthEnd.Value,Fields!Outstanding.Value,"MyAmazingDataset")

    LinkedIn

  • July 10, 2015 at 5:11 am

    #1811553

    Hi

    I think on the ch1 header you have to put the expression

    Max(Fields!Outstanding.Value)

    It will get the max of the group.

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 10, 2015 at 5:13 am

    #1811556

    Thankyou for responding.

    However, I need the 'Outstanding' value for the maximum date - this may not necessarily be the highest value.

    So, January could have '1,000', February could have '400', and I would need to return '400'.

    Sorry if I wasn't clear!

    LinkedIn

  • July 10, 2015 at 5:42 am

    #1811569

    Ok.

    In that case maybe you have to try the First() or Last() aggregate function

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 10, 2015 at 7:00 am

    #1811600

    This will work as long as the group is sorted by date, but as the report is subject to change I was hoping there would be a way to retrieve the value using the date column.

    Ah well, looks like I'll have tackle it via the dataset!

    Thankyou for lending me your time 🙂

    LinkedIn

  • July 10, 2015 at 8:28 am

    #1811627

    Maybe a

    Iif(Max(Fields!MonthEnd.Value)=Fields!MonthEnd.Value,Fields!OutStanding.value,Nothing)

    ?

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

  • July 10, 2015 at 8:40 am

    #1811632

    D'oh!! That works great, thankyou!

    LinkedIn

  • July 10, 2015 at 9:08 am

    #1811645

    Great! I am happy this helped you 🙂

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply