0

Is this possible without editing subscription table and SSRS SQL Job?

Let say I have a report that takes one parameter. The parameter1 is either EAST and WEST. SO I want to create ONE subscription that will programmatically run the report (in this case two times, because I have two possible option in the parameter)

Let say I have a select statement that output 

EAST
WEST

I am wondering if I can use a built in SSRS tool and take that output to run the report (in this case two because there are two possible EAST and WEST)

one will run with parameter set to EAST and a second run with parameter set to WEST

I simplify my problem to better explain. In reality I have 50 possible option in parameter1. but I don't want to create 50 subscription.

I know I can alter the SQL job and altering the parameter back door. but I am wondering if there is a more elegant solution (built in)

I know I can also can do this with R-TAG and such But I am looking is SSRS 2016 can do this out of the box?

I am using SQL 2016 and SSRS 2016 THX

BobNoobGuy
  • 1,551
  • 2
  • 30
  • 62
  • https://learn.microsoft.com/en-us/sql/reporting-services/subscriptions/data-driven-subscriptions?view=sql-server-ver15 – aduguid Jan 04 '20 at 03:46
  • Are you using SQL Server Standard or Enterprise? If enterprise, look at Data Driven Subscription. – Harry Jan 05 '20 at 19:33
  • Yea, we don't have data driven subscription unfortunately it's standard edition or some sort. not enterprise. – BobNoobGuy Jan 08 '20 at 18:27

1 Answers1

0

My clunky solution to this.

create a while loop and update Subscriptions table and set the parameter within the loop

  1. First I create a temp table, with "east" and "west".
  2. have a cursor that select table above. fetch the cursor into a variable
  3. While loop and update the parameter in subscription table.. @t2 will contain east or west.

update Subscriptions set Parameters = 'Location' + @t2 + 'Month12' where SubscriptionID = @SBidEventData

  1. execute the report
  2. wait for execution to finish

set @bool1 = ISNULL((select eventdata from event where eventdata = @SBidEventData), '') while @bool1 <> '' begin set @bool1 = ISNULL((select eventdata from event where eventdata = @SBidEventData), '') if @bool1 = '' begin -- SET PATH AND PARAMETER TO THE WAY IT WAS WAITFOR DELAY '000:00:07' update Subscriptions set Parameters = 'LocationWest' where SubscriptionID = @SBidEventData end
end

BobNoobGuy
  • 1,551
  • 2
  • 30
  • 62