Reputation: 197
I have a SSRS report that I pass parameters to. Within the base report are data regions displaying data from various datasets. Some of the datasets return one row, like the primary one. Others return multiple rows.
I have been learning about multiple valued parameters in order to address this. However, the report isn't yielding the results I anticipated. For instance, I want all the data about records one and two. I was hoping that the report would give me all the data about record 1 then would repeat with all of record 2 (new heading and such). However, my report yields all of record 1 and where there are multiple rows merges all record 1 and 2. Not desirable.
I'm new to SSRS, is this something I would use subreports for? How would I go about displaying multiple records?
Upvotes: 2
Views: 4232
Reputation: 23809
SSRS does not repeat the entire report for each record. The entire report is displayed once.
But within that report certain elements will repeat. In particular, the details rows of a table (tablix) or a list control will repeat for the number of items that are in the dataset associated with the table.
For example, imagine that you create a report with a TextBox and a Table, then drag a field from a dataset into the Text box, and drag another field from the dataset into the details row of the table. When you run the report you will see the Textbox once, containing the information from the first record in the dataset. (In the textbox you will see something like =FIRST(Fields!MyField.Value, "MyDataset")
) The table will appear only once as well, but it will have a row for each row of your dataset.
On to your specific problem: One way to get the results you are looking for is with a subreport:
Create a report that is designed to display only one of your records. It should have a parameter that indicates which record should be displayed. You can run this report on its own during development to make sure you are getting what you want. The datasets in the report should only return data pertaining to the record specified in the parameter.
Once you've got that report working, create a "parent" report. This report might have a cover page, or maybe hardly any content. It should have a dataset that returns IDs for all of the records on which you'll want in your report. These IDs need to match what the parameter in the child report expects. In the parent report, add a Table, and connect the table to your dataset. In a cell of the new table, place a subreport. Specify the parameter for the subreport so that it connects the subreport parameter's name to the Value of your ID field.
-or-
The structure of the report you create above is pretty sophisticated for newcomer to SSRS. If you want to create a simpler structure, then you should change your datasets.
Reduce your report down to one dataset. Put all of the data for one record into one row of that dataset, and repeat the rows for as many records as you need to display. (You might use other datasets to populate parameters.) If something is particularly hard to get into the single dataset, you can use the Lookup
function to examine other datasets.
In your report, place a table. Into this table place your fields. If you want lots of layouts options, put a rectangle into a cell of that table, and make the rectangle big. Then you can place textboxes as needed. But I recommend that you start experimenting just with dragging fields into cells in the table.
Upvotes: 3