SSRS run same stored procedure to generate dataset multiple times during one instance

0
votes

I have a stored procedure that returns 100 rows. I use this procedure in SSRS to build a dataset.

Now I have two tablix, one named Top 5 which shows the top 5 rows of my dataset and one named Top 10 which shows the top 10 rows. In my stored procedure, I do Select Top X where X is controlled by a variable.

Can I set it up so that both tablix use the same dataset, but one gets a parameter with a value of 5 and uses that for the X and one get a value of 10 to replace the X with. Is this possible and if so, how?

1
sqlsql-serverreporting-servicesssrs-2008
Any reason you want to do this in the stored procedure instead of in the SSRS tablix? – Tab Alleman
How can I do it inside the tablix? – user11703506
stackoverflow.com/questions/46587781/… or google.com/… or stackoverflow.com/questions/14225792/… – Tab Alleman
You cannot create a temporary table in SSRS that is used repeatedly. You can invoke a proc that creates it but it will disappear as soon as the proc is done. If you create a temp table it will be gone by the time the next section of the report needs it. – benjamin moskovits
You actually can use a temp table if you configure the "Use a single transaction when processing the queries" data source option. Of course managing the field list in ReportBuilder is a hassle. – David Browne - Microsoft

1 Answers

0
votes

The limiting of rows was just an example. Is there any way to have two different tablixes run the same stored procedure but each with different parameters.

I think what you're really asking is can you do this with a single dataset, in which case the answer is no. If the two Tablixes have to supply different parameters, you will need to have two datasets, both of which call the same stored proc, but with their own set of parameters.

However, there are a number of ways that you can call a dataset once, and apply different filters in the Tablixes that use it, and that might be a workable solution for whatever it is that you actually need to do. The limiting of rows, after all, was just an example.