CODEWITHSUNDEEP
sql-serverdynamicreporting-servicesssrs-2012
Freddy
Freddy

Reputation: 91

SSRS 2012 - loop through one Parameter

I have a SSRS Report which is using a dynamic SQL Server stored procedure with one parameter only and it works fine.

I want to create a report which whenever I run it, it loop through multiple values for that parameter and creates multiple results, instead of doing that manually.

I found below links from Stack Overflow, but none of them help a lot.

Link1

Link2

I also read some I can use List in SSRS, but I am not sure how to do it in my case.

Any idea?

Upvotes: 0

Views: 725

Answers (2)

Tihomir Budic
Tihomir Budic

Reputation: 309

You need to use data driven subscription. I had cases with dynamic parameters and the only solution was data driven subscription.

Upvotes: 1

FLICKER
FLICKER

Reputation: 6683

This might not be an answer as you have not provided enough detail, but I'm sure this idea will help you.

I think you'd better to change your stored procedure to accept multiple values and in your procedure return multiple result sets or combine record sets for each parameter value.

For example, assume you have procedure Proc1, which gets a number as parameter. 

create procedure Proc1 (@aYear int)
as
begin

    select *
    from Orders
    where OrderYear = @aYear

end

Now, what you need to do is changing your procedure to accept multiple years (for instance as a comma separated value)

create procedure Proc1 (@csv_years varchar(100))
as
begin
    -- use some function or techniques to split the @csv_years 
    -- and store it into a temp table #years ([Year] int)

    -- now, join your main data with this #years table
    select #years.[Year], Orders.*
    from Orders
      inner join #years on #years.Year = Orders.OrderYear
end

Now, your are returning the result for multiple years and you can show that in your report by grouping on [Year] column

Upvotes: 1

Related Questions