SSRS How to Add Max Expression to an Existing Expression

Question

I have the following code and I now want to only show the max or distinct values for a single field within SSRS.

=Join(LookUpSet(Fields!Baseacctnbr.Value, 
Fields!Baseacctnbr.Value, 
Fields!Acctnbr.Value, 
"DataSet1"), ",")

Right now with this expression the code brings back all the accounts, but it brings back multiples of the same account because of the SQL query and other data that is needed. I would like to only show the MAX or Distinct values of the AcctNbr on a single field within SSRS.

Is there a way to add the MAX expression to this existing expression?

UPDATE: When I add the SSRS Code Block found on another question, then I receive an error message that states: "Too many arguments to 'Public Shared Function RemoveDuplicates(m_Array() As Object) As String()."

Thanks.

Yes, I need the expression above to only show the max or distinct values. — BIReportGuy, Nov 23 '15 at 20:19
I'm not able to add an example within this comment section.. With the current expression above I get a comma delimited list of accounts like this: 765,765,765,799,396,670,765,396,799. I only want to see the distinct values of 765,799,396,670 on this field. — BIReportGuy, Nov 23 '15 at 20:30
Possible duplicate of [SSRS distinct lookupset function](http://stackoverflow.com/questions/27047483/ssrs-distinct-lookupset-function) — Nathan Griffiths, Nov 23 '15 at 20:53
Why are you stuck? use `Join(Code.RemoveDuplicates(LookUpSet(Fields!Baseacctnbr.Value, Fields!Baseacctnbr.Value, Fields!Acctnbr.Value, "DataSet1")),",") ` — alejandro zuleta, Nov 23 '15 at 22:03
To be fair @Alejandro Zuelta, when I try to replicate his problem using all the steps above (and the previous question) I get the following error `The Value expression for the textrun ‘Textbox6...’ contains an error: At least one element in the source array could not be cast down to the destination array type.` — Jonnus, Nov 23 '15 at 22:30
I'm using the following code - '=Join(Code.RemoveDuplicates(LookupSet(Fields!BaseAcctNbr.Value), Fields!BaseAcctNbr.Value, Fields!AcctNbr.Value, "DataSet1")), ",")' and now receiving an error message _the value expression for the textrun has incorrect number of parameters for the function 'LookupSet'_ — BIReportGuy, Nov 23 '15 at 22:40
@Jonnus, note the OP update, it seems he is passing incorrect number of args to the custom function. I don't know if besides the OP issue the function doesn't work as expected. I'll try to replicate the issue a soon I can. — alejandro zuleta, Nov 23 '15 at 22:42
@Hermanator you have a Close Brace `)` after the first instance of `Fields!BaseAcctNbr.Value` hence your current error — Jonnus, Nov 23 '15 at 22:46
@Jonnus you are correct. I removed the close brace and it worked fine. I had also entered this code before your response and it worked fine since the account is actually 8 characters long. '=Join(Code.RemoveDuplicates(LookupSet(Left(Fields!BaseAcctNbr.Value, 8), Fields!BaseAcctNbr.Value, Fields!AllPhase3AcctNbrs.Value, "DataSet1")), ",")' Thank you both! — BIReportGuy, Nov 23 '15 at 23:00
If you have found a resolution please consider posting it as an answer, so that this question does not show up as "unanswered" - thanks! — Nathan Griffiths, Nov 24 '15 at 01:28

score 0 · Accepted Answer · answered Nov 24 '15 at 02:14

I removed the close brace and it worked fine. I had also entered this code before your response and it worked fine since the account is actually 8 characters long.

=Join(Code.RemoveDuplicates(LookupSet(Left(Fields!BaseAcctNbr.Value, 8), Fields!BaseAcctNbr.Value, Fields!AllPhase3AcctNbrs.Value, "DataSet1")), ",")

Thanks for your help!

SSRS How to Add Max Expression to an Existing Expression

1 Answers1