CODEWITHSUNDEEP
reporting-servicesreportbuilder3.0
kgadmin
kgadmin

Reputation: 13

Return other column value from MAX of date

I'm using Report Builder 3 and I'm trying to return 1 value (Amount) from a record based on the max value of another field (date) in the same record. My data looks something like this:

Type          Date          Amount       Cust
-----------------------------------------------------

Credit        2015-01-01    10           A
Inv           2015-01-01    11           A
Check         2015-01-01    12           A
Check         2015-01-02    13           A
Inv           2015-01-02    12           A

In actual fact, I need 2 fields - Last Check Date and Last Pay Amount. The Last Check Date I can get by using:

=Max(iif(Fields!type.Value = "CHECK", Fields!date.Value, Nothing))

My data is in date order so I had hoped something similar would work to get the Last Check amount, like:

=Last(iif(Fields!type.Value = "CHECK", Fields!amount.Value, Nothing))

but what I think this is doing is looking for the last record in the whole dataset and if it is type = check, then it gives me the amount, otherwise nothing. Unfortunalty, the last record is often not a check. This aproach results in nothing being displayed. I also tried wrapping the "last" inside the iif but this has the same effect.

In short, from the above dataset I would be looking to produce the following. I have one group in report builder - Cust and I have no detail section

Cust              A
Last Chk Date     2015-01-02
Last Chk Amt      13

I'm usually good at searching around for the answers to these type issue but for this one, I've drawn a complete blank! Can anyone help with this?

Thanks in advance...

Upvotes: 0

Views: 2096

Answers (2)

kgadmin
kgadmin

Reputation: 13

Thanks for your efforts, I've marked your answer as accepted. Using the Lookupset was the key. As it turned out, using the exact code from your answer gave me a long concatenated list of almost all of either the Date or Amount field from every "check" record. I found that the RevInStr was looking for the first occurrence of the comma from the right side of the string. So the Right was displaying everything after that. To get it working for me, I used InStr(StrReverse to work out the number of characters I needed.

I'm not sure of the etiquette regarding comments vs answers but I couldn't get a comment to include my explanation and code. I thought I should post my changes in case this helps anyone else...

=Right(Join(LookupSet("Check", Fields!type.Value, Fields!date.Value, "credit"), ","), InStr(StrReverse(Join(LookupSet("Check", Fields!type.Value, Fields!date.Value, "credit"), ",")), ",") - 1)

Thanks again for the help Michael, it's much appreciated.

Upvotes: 1

michael.abdulakh
michael.abdulakh

Reputation: 121

Not sure that it is the best solution, but you can try to use the expression with Lookup or LookupSet function.

Lookup looks like a better option but it returns the first value but not the last. LookupSet returns all values and then with Right function you can "cut" the last value.

For last date: =Right(Join(LookupSet("Check",Fields!Type.Value,Fields!Date.Value,"DataSet1"),","),InStrRev(Join(LookupSet("Check",Fields!Type.Value,Fields!Date.Value,"DataSet1"),","),",")-1)

For last amount: =Right(Join(LookupSet("Check",Fields!Type.Value,Fields!Amount.Value,"DataSet1"),","),InStrRev(Join(LookupSet("Check",Fields!Type.Value,Fields!Amount.Value,"DataSet1"),","),",")-1)

enter image description here

Upvotes: 0

Related Questions