Create Multiple SSRS Subscriptions for a Parameterised SSRS Report - Programmatically / Dynamically / Quickly

Question

Create Multiple SSRS Subscriptions for a Parameterised SSRS Report - Programmatically / Dynamically / Quickly

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

chjquest Ten Centuries Points: 1174 More actions · Answer 1

shell_l_d - Wednesday, June 21, 2017 1:03 AM
Hi
We have 470 subscriptions in total, of which 224 are for the same parameterised ssrs report but pass different parameter values (including some parameters having multiple values selected, eg: location). Unfortunately we lost all 224 subscriptions when the report was removed then re-added.
How can the subscriptions be re-created quickly & easily please without hours of data entry?
The SSRS reports were created using Report Builder 3.0 using a Data Source to an SQL Server 2012 database, parameters (including multi-select parameters), shared data sets and a dataset to a stored procedure. They are accessed via a Report Server.
P.S. These are NOT Data-Driven Subscriptions, but subscriptions created manually 1 by 1 for particular user-defined reports with user-defined parameters & shared data sets with a common data source, all stored in the report file (eg: myreport.rdl)

Not sure if you find the solution.
If not, there is a way to recreate your subscription.
Please let me know if you still need it - it's kind of long.

Easy to say that I have similar case and have solution to rebuild subscription (no matter it's regular subscription or DDS).
I can rebuild your subscription (only parameters portion) through system table. However, as for the other (recipient, date to send, subject), it will be on your own. If that's good enough, you can reply/or message me.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

chjquest - Friday, July 28, 2017 10:20 AM
Not sure if you find the solution.
If not, there is a way to recreate your subscription.
Please let me know if you still need it - it's kind of long.
Easy to say that I have similar case and have solution to rebuild subscription (no matter it's regular subscription or DDS).
I can rebuild your subscription (only parameters portion) through system table. However, as for the other (recipient, date to send, subject), it will be on your own. If that's good enough, you can reply/or message me.

Post it anyway. And keep in mind this is not one subscription. The original request is:

of which 224 are for the same parameterised ssrs report but pass different parameter values (including some parameters having multiple values selected, eg: location). Unfortunately we lost all 224 subscriptions when the report was removed then re-added.
How can the subscriptions be re-created quickly & easily please without hours of data entry?

So I would love to see how you recreate all 224 subscriptions with the different parameter values. And get all of those different parameters correct without having them.

Sue

chjquest Ten Centuries Points: 1174 More actions · Answer 3

Sue_H - Friday, July 28, 2017 10:26 AM
chjquest - Friday, July 28, 2017 10:20 AM
Not sure if you find the solution.
If not, there is a way to recreate your subscription.
Please let me know if you still need it - it's kind of long.Easy to say that I have similar case and have solution to rebuild subscription (no matter it's regular subscription or DDS).
I can rebuild your subscription (only parameters portion) through system table. However, as for the other (recipient, date to send, subject), it will be on your own. If that's good enough, you can reply/or message me.
Post it anyway. And keep in mind this is not one subscription. The original request is:
of which 224 are for the same parameterised ssrs report but pass different parameter values (including some parameters having multiple values selected, eg: location). Unfortunately we lost all 224 subscriptions when the report was removed then re-added.
How can the subscriptions be re-created quickly & easily please without hours of data entry?
So I would love to see how you recreate all 224 subscriptions with the different parameter values. And get all of those different parameters correct without having them.
Sue

1. The original report will be revised (Long story here). After revision, the report will be able to take "Parameter Profile".
(I have live report using this method, detail will not be discussed here)
The report after revision will be looks like this,

2. The report with the 224 missed one could be found from the system table - if they have good backup and kept long enough.
Assuming those 224 subscriptions just ran, all parameters will be saved in the report database/table in URL format.
The table content looks like below,

Then, the DB guy will need to profile/pick all the 224 (if applicable) and save them into a table, which the revised report will able to pickup.

Script is here (my environment is SSRS 2008R2, script may need revision to match other versions if required).
SELECT cat.Name, ex.InstanceName, ex.ReportID, ex.UserName, ex.Parameters, ex.TimeStart FROM ExecutionLog AS ex INNER JOIN Catalog AS cat ON ex.ReportID = cat.ItemID where ex.Parameters is not null ORDER BY ex.TimeStart DESC

3. In the subscription, create subscription or dds based on the saved profile within the time and recipient, done.

Note, the Parameter is asking for the ID corresponding to the figure 1 Parameter drop down. This information/data is saves in a table. Good thing is this can save some time when creating subscription on the parameter step ONLY. it won't help on the other steps.

This is being said, if the table "ExecutionLog" does not keep that long for the 224 subscription run, then....too bad, this method won't help.

*Due to the implementation is in live system, screenshot has been processed to mask/remove certain sensitive information.

Sue_H SSC Guru Points: 90860 More actions · Answer 4

chjquest - Friday, July 28, 2017 10:39 AM
Sue_H - Friday, July 28, 2017 10:26 AM
chjquest - Friday, July 28, 2017 10:20 AM
Not sure if you find the solution.
If not, there is a way to recreate your subscription.
Please let me know if you still need it - it's kind of long.
Easy to say that I have similar case and have solution to rebuild subscription (no matter it's regular subscription or DDS).
I can rebuild your subscription (only parameters portion) through system table. However, as for the other (recipient, date to send, subject), it will be on your own. If that's good enough, you can reply/or message me.
Post it anyway. And keep in mind this is not one subscription. The original request is:
of which 224 are for the same parameterised ssrs report but pass different parameter values (including some parameters having multiple values selected, eg: location). Unfortunately we lost all 224 subscriptions when the report was removed then re-added.
How can the subscriptions be re-created quickly & easily please without hours of data entry?
So I would love to see how you recreate all 224 subscriptions with the different parameter values. And get all of those different parameters correct without having them.
Sue
1. The original report will be revised (Long story here). After revision, the report will be able to take "Parameter Profile".
(I have live report using this method, detail will not be discussed here)
2. The report with the 224 missed one could be found from the system table - if they have good backup and kept long enough.
Assuming those 224 subscriptions just ran, all parameters will be saved in the report database/table in URL format.
Then, the DB guy will need to profile/pick all the 224 (if applicable) and save them into a table, which the revised report will able to pickup.
Script is here (my environment is SSRS 2008R2, script may need revision to match other versions if required).
SELECT cat.Name, ex.InstanceName, ex.ReportID, ex.UserName, ex.Parameters, ex.TimeStart FROM ExecutionLog AS ex INNER JOIN Catalog AS cat ON ex.ReportID = cat.ItemID where ex.Parameters is not null ORDER BY ex.TimeStart DESC
3. In the subscription, create subscription or dds based on the saved profile within the time and recipient, done.

Interesting. I just had 12 basic subscriptions, just set to email me. And none of them have anything in Parameters in the ExectutionLog3. Checked the other subscriptions that have ran and the parameters column is null. And ran them all on 2008R2. .
I can see the parameters in the subscription table though. So where are these saved? Can you create a subscription, just to email and see the email address in the Parameters column ExecutionLog3? You say ExecutionLog3 as well as report database/table in URL format but I don't have these in the ExecutionLog3.
I have come across XML columns but not sure what you mean by URL format. I can see the AdditionalInfo column which is XML but it has processing, scalability, memory, etc values. Nothing with parameters from any of the reports.

Sue

Sue_H SSC Guru Points: 90860 More actions · Answer 5

One other thing - you edited after I did a reply and got side tracked but these are NOT data driven subscriptions. Poster kept saying that but the only thing discussed was just regular subscriptions.

Sue

chjquest Ten Centuries Points: 1174 More actions · Answer 6

Sue_H - Friday, July 28, 2017 12:06 PM
chjquest - Friday, July 28, 2017 10:39 AM
Sue_H - Friday, July 28, 2017 10:26 AM
chjquest - Friday, July 28, 2017 10:20 AM
Not sure if you find the solution.
If not, there is a way to recreate your subscription.
Please let me know if you still need it - it's kind of long.
Easy to say that I have similar case and have solution to rebuild subscription (no matter it's regular subscription or DDS).
I can rebuild your subscription (only parameters portion) through system table. However, as for the other (recipient, date to send, subject), it will be on your own. If that's good enough, you can reply/or message me.
Post it anyway. And keep in mind this is not one subscription. The original request is:
of which 224 are for the same parameterised ssrs report but pass different parameter values (including some parameters having multiple values selected, eg: location). Unfortunately we lost all 224 subscriptions when the report was removed then re-added.
How can the subscriptions be re-created quickly & easily please without hours of data entry?
So I would love to see how you recreate all 224 subscriptions with the different parameter values. And get all of those different parameters correct without having them.
Sue
1. The original report will be revised (Long story here). After revision, the report will be able to take "Parameter Profile".
(I have live report using this method, detail will not be discussed here)
2. The report with the 224 missed one could be found from the system table - if they have good backup and kept long enough.
Assuming those 224 subscriptions just ran, all parameters will be saved in the report database/table in URL format.
Then, the DB guy will need to profile/pick all the 224 (if applicable) and save them into a table, which the revised report will able to pickup.
Script is here (my environment is SSRS 2008R2, script may need revision to match other versions if required).
SELECT cat.Name, ex.InstanceName, ex.ReportID, ex.UserName, ex.Parameters, ex.TimeStart FROM ExecutionLog AS ex INNER JOIN Catalog AS cat ON ex.ReportID = cat.ItemID where ex.Parameters is not null ORDER BY ex.TimeStart DESC
3. In the subscription, create subscription or dds based on the saved profile within the time and recipient, done.
Interesting. I just had 12 basic subscriptions, just set to email me. And none of them have anything in Parameters in the ExectutionLog3. Checked the other subscriptions that have ran and the parameters column is null. And ran them all on 2008R2. .
I can see the parameters in the subscription table though. So where are these saved? Can you create a subscription, just to email and see the email address in the Parameters column ExecutionLog3? You say ExecutionLog3 as well as report database/table in URL format but I don't have these in the ExecutionLog3.
I have come across XML columns but not sure what you mean by URL format. I can see the AdditionalInfo column which is XML but it has processing, scalability, memory, etc values. Nothing with parameters from any of the reports.
Sue

Hi Sue,

I just tried one report with subscription on my end, see screenshot.

My first entry is the subscription run. Not sure why it's not working on your end. The parameter is saved in a customized table in a customized database.

As for the recipient, I can't find it - it could/may be saved somewhere (if exists), but for history, I didn't research that, will take sometime to see where the subscription information/data saved.

One thing to mention, run my script in ReportServer database, not system (msdb, master or others). And sorry for my bad wording, it should be "Parameters" column, just I kept thought it's URL format (jumping in between my work and posting).

Sorry for the mass editing, want to make sure the image I uploaded is clear enough and does not contain any information may get myself fired~

Sue_H SSC Guru Points: 90860 More actions · Answer 7

chjquest - Friday, July 28, 2017 12:36 PM
Hi Sue,
I just tried one report with subscription on my end, see screenshot.

My first entry is the subscription run. Not sure why it's not working on your end. The parameter is saved in a customized table in a customized database.
As for the recipient, I can't find it - it could/may be saved somewhere (if exists), but for history, I didn't research that, will take sometime to see where the subscription information/data saved.
One thing to mention, run my script in ReportServer database, not system (msdb, master or others). And sorry for my bad wording, it should be "Parameters" column, just I kept thought it's URL format (jumping in between my work and posting).
Sorry for the mass editing, want to make sure the image I uploaded is clear enough and does not contain any information may get myself fired~

It is working fine from my end and I see no email address in any table. I believe you said it was in the parameters column of the ExecutionLog3 view. I don't believe that is correct.
It's not a data driven subscription and the parameters are in the subscription table. And you state that you can't find the recipient. It's in the subscription table - no need to research it. This is what you said:

will take sometime to see where the subscription information/data saved

and that is exactly what is discussed in this thread. That is what you told the OP they could get. So I'm confused as the subscription information is in the subscription table in the ReportServer database. Delete a subscription and it's not in that table anymore. And there are no backups. You said you can recreate them but you don't know where the information is? That doesn't make much sense.

And I just noticed another thing -

The report with the 224 missed one could be found from the system table - if they have good backup and kept long enough.
Assuming those 224 subscriptions just ran, all parameters will be saved in the report database/table in URL format.

It's 224 subscriptions and they all had parameters. And we had already discussed they don't have a backup. So that is the question - they have no backup, the subscriptions have all been deleted from the new report, the old report would be in an old backup in the catalog table in the ReportServer database. And that would not have the parameters saved with it. When you have 224 subscriptions to a report, the report doesn't update on every subscription added and put parameters in it. The parameters would be in the subscription table in the ReportServer database. And they do not have a backup of the database and the subscriptions are all gone.

So...what is the method for the poster to recreate all 224 of the non-data driven subscriptions and provide all of the original parameters? I see your posts but I still don't see how that can happen and you said you were unable to find the recipient on your test. I just deleted one of my reports that I subscribed to. I just redeployed the report. Tell me how to recreate the subscription with no backup?

Sue