ssrs order of operation question
-
hi i need "sample size" in my report's 4th data region which is a grid with 2 additional full grid with comments. It is shown in the image.
i want to add a 3rd additional comment row to the same tablix . I want its visibility to depend on "sample size" being less than 10. I think i know how to handle visibility in a text box. i'll have to deal a bit with the rules for the borders in these additional rows.
from what i've seen of the report there are 2 ways to find sample size <10...
- the last column in this grid's core dataset is 'N/A' when the sort field in that dataset row is 3. there can be at most 1 row with sort field = 3 but i believe there might be no rows where sort field = 3. if an N/A appeared, the sample size had to be less than 10.
- count(*) from a table inserted to with this user's samples BY the 3rd dataset (via proc) of 8 datasets in this report. the predicate would be where userid=parameter @userid. the core dataset for this grid (4th region) is the 7th of 8 datasets. and it actually depends on the table to which the inserts were made for this user by the 3rd dataset. but those total counts below but "attached" to the grid come from totally different datasets in this report.
the biggest question i have is on order of operation. and this is my preferred approach. how can i be sure that if i add a dataset that gets sample size and sets the value of a new internal parameter, that it will run after that 3rd dataset that inserts records for this userid to that table i mentioned and before my new text box needs that count? it would use count(*) ...where...
...or if the expression in this new text box instead depends on this grid's core dataset, that i can locate the value in the row marked with a sort value of 3 if such a row even exists?
- This topic was modified 1 month, 1 week ago by stan.
-
the 1st thing i tried was to add a dataset (last dataset) for sample size, plumb it to a param and then pass the param to the core dataset for grid/table 4.
it did not work and i suppose i cant blame ssrs for that as how could it know what i want. whatever was in that userid based sample data points table BEFORE is being used, not what the 3rd ds caused to be inserted there for this run. i tried moving the new sample size dataset up with the up arrow thinking i can help ssrs to understand the dependency. it wont move but after thinking about it, thats probably dumb because ssrs is clearly already running that ds before grid 4's core ds anyway. back to the drawing board.
-
Are all the datasets coming from the same database?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
yes.
-
OK, then consider putting all of your data into a single (wide) temp table, adding a 'GroupNo' column for each dataset. Filter on the GroupNo within SSRS to split out the datasets again.
You should be able to do all of the manipulation you need before the data gets to SSRS, with no need to worry about the order in which things are processed.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
thx Phil, I like that solution. And will use it and mark your post as an answer here in a second.
an ugly solution i got to work while experimenting went like this ...
- in region 4's core dataset add a bogus repeated column called SampleSize.
- For the bottom border under that 2nd total use this kind of expression to determine if this will be the last comment =Iif(First(Fields!SampleSize.Value, "ds4")>0 And First(Fields!SampleSize.Value, "ds4") <10,False,True)
- similar thinking for even showing the 3rd/new comment
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply