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.
Hello Joe,
You might be able to get around this with the use of some custom code for the report and maybe a global variable to store these values.
Jon
For my own curiosity I ended up trying this aproach. I basically had two methods one to set the value and one to retrieve it. I set the value in the detail section. What I found is that when accessing the global variable in the group header and footer the value was always 0. The reason is that the headers and footers are calculated first. Sorry t don't think this will work either.
Is it possible to have this value part of your dataset?
Hi Jon
Here is what I am trying to do, this is a report that I am converting into SSRS and although the stored proc returns the data needed, there is some code within the infomaker object that has restrictions on how the data is being calculated.
Basically I am trying to make this report behave exactly as the current report.
Thanks again for your help.
Joe, Does the total field need to be inside a trailer group or can it be outside of the table?
It needs to be within the table since the totals build off of one another. I think that for now I am going to get the totals a different way than how the infomaker object is totaling.
Thanks again for your help
Ok, I can't think of a way to get around that when the total for the calculated fields are in the table. I think if you had the total outside then it might work.
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