SSRS Report Builder Sub-Report Sorting

0
votes

I have an SSRS report that contains 20 sub reports in it. Each sub-report represents a phase in a specific cycle. All sub-reports are identical in formatting and such, containing specific data to that phase and displaying a start and end date. I was wondering if there is a way in Report Builder to essentially have those sub-reports be dynamic and sort themselves based on their specific start dates instead of just displaying them the one I have them listed in my report?

1
sql-serverreporting-services

1 Answers

0
votes

This is quite simple.

You already have you main report and sub reports so that's good. I assume your subreport takes a parameter which indicates the phase.

Assuming this, let's say the parameter your sub-report accepts is called pPhase

  1. In your main report, remove all the existing sub reports
  2. Next, create a dataset, lets call it dsLoop
  3. Set dsLoops dataset query to return the phases in order for example SELECT DISTINCT PhaseNumber from myTable ORDER BY PhaseNumber
  4. Now add a table control, remove the header row and two of the columns leaving just a single cell.
  5. Make this cell as wide as you need, wide enough for your subreport, height does not matter.
  6. Set the DataSetName property of the table to dsLoop
  7. In the remaining cell, right-click and choose Insert --> Subreport. A subreport control will fill the cell.
  8. Right-Click the subreport control and choose Subreport Properties, choose your subreport from the list
  9. Still in the subreport properties, click the parameters tab, add a new parameter.
  10. Set the Name to the parameter name that your subreport accepts (pPhase in our example) . Finally, set the Value to the name of the field that contains your parameter data (PhaseNumber in our example)

That's it...

Now when you main report runs, a row will be created in your table for each entry in dsLoop, each row will contain your subreport and each will have the parameter passed to it that is specific to each row.

If you have any issues, let me know and I'll post a full example.