Hi
I have hidden fields in the detail group of a report that return integers based on the rownumber etc., (simply calculated values that are hidden) that will be used in later calculations. I want to sum these values outside of the detail scope, for instance a sub total based on the invisible calculated values.per that specific column
I tried getting the reportItems! but it errors saying that I can only sum within the scope of the value. I would imagine that this is possible since it is done in infomaker often where you would just sum based on the name of that calculated field for the group.
Any thoughts would be helpful.
As always thanks in advance for any input.
Joe
try this
=Sum(Fields![name of field].Value, "[dataset name here]")
Thanks for the input Matt but this is what I am trying to do:
=iif(Fields!source_no.Value <> Fields!source_no.Value or rownumber(nothing) = 1,Fields!num_cust.Value,0)
Numbers the row and inserts a value in a report item named num_cust_distinct so for instance it would look something like this (where the first column is the rowcount and the value is the calculation) and this is in the detail portion
1 90 --each value is in the reportitem num_cust_distinct instead of textbox 1 etc.
2 0
3 0
4 0
Again these are invisible and not in returned from the stored proc, they are calculated based on fields returned from the stored proc. I want to sum the values in the second column here. =Sum(reportItems!num_cust_distinct.value) will only work within the header or footer of the report and then I can only retrieve those items within the same scope of the group that those reside in. I want to sum these values outside of the scope, for instance in a trailer grouping.
Ah...I see now, I was unaware that the field data was not part of the proc data. Yes, since you are creating those values within the scope "dynamically" then they are owned by that scope.
It has been some time since I've needed to do something like that so it is not fresh in my mind. I will see if I can think of a work around. There are some limitations to SSRS (obviously).
Sorry I wasn't much help.
Hi Joe,
Good day.
How about before you return the final dataset "return_table_from_stored_procedure", use a left join to this table, do the caculation in SQL first, then return it?
In this way, the report will be very simple, basically leave the complication in SQL instead of in report.
And I think this is a basic rule of creating a report, we should create a pretty but simple report.
Normally special codes in SSRS are used for chart or coloring, but not for data.
have fun
Ben