Hi All,

I am trying to build a report using SSRS.
I need to retrieve a distinct value from my initial query and place this in the header or group row.
The detail lines are hidden but may be expanded(shown).
I then have another group row in which I require the sum of all distinct values from my previous group row.

To explain pictorially I have the following:

Scroll right to see the pink squares.
Note that the total in the pink square is the sum of the individual var3 values distinct so 10 even when var3 =4 in euro currency.

I have tried using sum(var5) but this will give the incorrect value as it sums all detail lines not just the distinct values.

I have tried using another dataset but when referenced the grouping behaviour expected is non existent so where I expect 40 I get 80 i.e. sum disregarding values of var1 and var2 although these are the grouping variables.

I need it to be dynamic so I am unable to use IIF(...).

I do not mind if this is resolved in the SSRS rdl or sql statement that generates the data being input.

If I could get var4 or var6 to be the totals I require I could work with that.

Any help gratefully received, thanks

I have also tried the following custom code:

Dim public unitLimit as Double

Public Function Getcpvalue(ByVal limit_amount1 AS Double) AS Double

unitLimit = unitLimit+ limit_amount1

return limit_amount1

End Function

Public Function Totalunitvalue()

return unitLimit

End Function

where limit_amount1 is the reportitems! cell name that needs to be totalled.
When run an error is produced:

Getcpvalue is not declared or is not accessible due to permissions

the value of limit_amount1 is =Getcpvalue().

any help gratefully received

• Edited by BrubleWB 16 hours 52 minutes ago

The custom code seems to work.

You need to call it =Code.Getcpvalue(<value that cell should have>)

May need to somehow initialise registers but have not worked out how yet.

• Marked as answer by BrubleWB 16 hours 15 minutes ago