SSRS report Tables and Sub Reports

Question

I have an SSRS, which has Games. Within each game, there are multiple teams, and then they are classified as offensive teams and defensive teams. So there is a game, then there is game detail. Within that there are multiple teams, and then information about the teams, regardless of whether they are offensive or defensive. Then if they are assigned as offensive, they will have an offensive record and ID. If there are defensive, they will have a defensive record and ID.

I set up a main report, based on a stored procedure, which has the game, and main game details. Then I created a sub report for teams, with details for the teams, regardless of whether they are offensive or defensive. This is a game-team record. Multiple per game record. So the heirarchy and report/subreport set up is as follows:

• Game (stored proc parameter passed as GID - game id)
  
• Game Teams (stored proc with same GID parameter)
  
• Game Teams - Offensive (stored proc with same GID parameter)
  
• Game Teams - Defensive (stored proc with same GID parameter)
  

In the game report, I have a tablix, with the fields , and then an extra tablix row, with columns merged. I put a subreport object in there, and set it to the Game Teams report object.

The subreport properties has the parameter set Name GID (matches subreport parameter name) and value = ID (which is the GID field).

That Game Teams report has a tablix with it's detail fields, and then 2 extra tablix rows, each with columns merged. One has the offensive report set in a subreport object, and the other has the defensive report set in a subreport object. Each of those has the same GID parameter from their stored procedures, and then I set up a GTID parameter, and link those in the Game Teams report to the GTID field in the Game Teams dataset.

I can get the reports to run, but the links don't seem to be pulling the data correctly. I have been researching this all over, but it is so confusing.

when I set up the GTID report parameter in the Game Teams - Offensive/Defensive reports, I don't see how I tell the report that when this parameter is linked to the Game Teams report, the subreport should be filtering the dataset results to the GTID field.

I hope what I'm saying makes sense. This is my first time trying to do an SSRS subreport, and it is so confusing. I have done them in crystal reports, in the past, and in Access. You link a field to a field. I don't get why you don't do that in SSRS.

Answer 1

I don't know what you do in Crystal, but in SSRS it's pretty simple.

In the Sub-Report properties, click on the ellipsis in parameters and you will get a dialog box. For each parameter that the subreport expects (they are in the name column), you supply a value that tells the report what to pass to the subreport for that parameter. You can use the fx button next to each value to help you build the expression.

If you have tried this and not gotten the expected results, you can show us a screenshot of the Parameters dialog box and explain what you're trying to get.

Answer 2

I figured out my problem!!! I created the report parameter in the subreport- GTID. To link it to the field in the subreport, I went to that report's dataset propertys, added a filter, chose the GTID field from the data set fields, chose "In" and then clicked the fx and chose the GTID parameter value.

Once this is set, you cannot run the subreport on it's own, unless you leave that report parameter visible, so you can specify a value.

Everything else was right, on the main report, except that it's good to have your main report grouped, and then have a detail grouping, if you have stuff in a table or tablix. I put the subreport in a detail row, with all cells merged. I set the subreport properties parameter to add a parameter linking the GTID sub-report parameter, to the GTID field in the main report's data set fields.

Voila!