SQLTeam.com | Weblogs | Forums

Refining my 'action'


#1

Hi,

Attached is a screen shot of what I'm trying to accomplish.

When SERV is collapsed, that last column reads five but when you click on it, it goes to a report but only show the four results, not the five like it should (the four in the first row, then the one from the second row).

When SERV is expanded like you see here, the links in the last column work correctly.

So I my issue is having it link correctly when each group is collapsed, in this case when you click on the 5, it should return all those 5, not the 4 from the first row.

Hope that makes sense.

Thanks


#2

I would make the LINK (of the 4 & 1, and on the "5" when it is collapsed) include a LIST of the IDs of the rows that make up the Section (i.e. "SERV" in your example)

Then the SQL Query behind that link needs to receive the LIST, "split" it into rows and JOIN those to the IDs of the underlying data.


#3

How would I accomplish a list? I'm passing the parameter of 4 (74) and 1 (75) but I don't know how to pass them both for the 5.


#4

I am presuming when you press the [-] button, next to SERVE, you get a row that says something like

[+] SERVER | {various} | {various} | 5 |

and the "5" is an HREF or similar.

Assuming that:

The HREF for on the "4" of the 74 row = ?PARAM1=SERV&PARAM2=74
The HREF for on the "1" of the 75 row = ?PARAM1=SERV&PARAM2=75

then for the contracted "5" line the HREF would be ?PARAM1=SERV&PARAM2=74,75

and your SQL code would need to

  • detect that PARAM2 is a multiple-value parameter
  • "split" PARAM2 into a #TEMP table
  • "join" the #TEMP table into your query to include all matching rows ("74" and "75" in this example)

#5

No it's not really set up like that in SSRS. It's an action that's set up, in this case the action is to run another rep[ort and I'm just telling it what parameteres to pass to that second report.

There is no separate setting of how the 'action' behaves when you click on the 5 or 4 or 1.

Thanks!


#6

How do the two lines for SERV contract into a single line - is that something that SSMS does for you, or something that you program?

If the contraction of those two rows is under your, program,control then you need to "embed" a list of IDs so that when the "5" is clicked you then use that list to find all the referenced records.

If SSMS just "adds them together" I can't see how you can reference the underlying data, other than to see if the matching row equals that value, and if not trying to add up the values for all the rows matching the "SERV" column, and if that matches then assume that the user had contracted the SERV rows - and thus display include all child rows of SERV. But that sounds like a) hard work! and b) an ambiguous / error prone route.