Create Multiple Tables according to a list input in SSRS Reports

Question

I'm new to SSRS Reports. I have an SSRS Report that takes a list of category Ids And I stored procedure that called SP_GetProductsByCategory that takes categoryId

I want to create the tables dynamically according to the list. If it takes 3 Ids , a 3 tables will be create with 3 procedure calls.

Answer 1

This is fairly simple to do using subreports.

First create a report (let's called it singleCategory) that accepts a single categoryId as a parameter (call the parameter pCategoryId for example). Setup the report so that it produces the table you require but just for this single category.

Once that is done, test it and make sure it's working as expected. Remember this should only accept a single categoryId.

Note: I'm assuming your users will select categories from a database driven parameter list...

Next, create a new report (e.g. CategoryMasterReport) and add a dataset (dsCategories) to get a full list of available categories for the user to choose from. This might be something as simple as SELECT categoryId, categoryName FROM myCategoryTable.

Next, add a parameter (pCategories for example). Make this a multi-value parameter

Set the available values for the pCategories parameter to the dsCategories dataset and choose label and value columns as appropriate.

Now create another dataset, this one will list all the categories selected by the user. Call it dsSelectedCategories and set the query to something like...

SELECT categoryId 
    FROM myCategoryTable
    WHERE categoryId IN(@pCategories)

Next, add a table to your report and set it's DataSetName property to the name of the dataset we just created (dsSelectedCategories). We only need one column in this table so you can remove the rest. You can also remove the header row.

In the table's remaining textbox, right-click and choose "Insert ==> Subreport". This will fill the textbox with a subreport placeholder. Right-Click the subreport placeholder and choose 'subreport properties'.

In the general tab set the subreport to be used as the one we created at the start of this (e.g. singleCategory).

In the parameters tab, click 'Add' then either choose the name of the parameter from the drop down list or type it in (following the example names from earlier this will be pCategoryId). For the value property, click the drop down and choose the categoryId field (if you followed closely this might be the only field listed.

That's it.

Now run the main report (CategoryMasterReport), select some categories from the list and you should see a table for each selected category.