0

Currently I have 4 or 5 reports (more are coming) with over 50 subscriptions on it (different email and different parameters), and I want to regroup them using the data driven subscription.

I can create the dds subscription, I have test it and it is running fine, my problem is that I want to have every parameter in a table, to "centralize" my subscription, that way it will be easier to configure instead of re-enter in each of my 50 subscriptions.

Currently I see three option,
1 - To create a global table, with every subscription parameters as a new columns, but I can have a limitation of the number of columns and the majority of my rows will be empty
2 - To create a new table for every report, but if I have 10 or 20 reports like this it wil not be "clean" in my database
3 - To create a main table (email, report name), and another one with the parameters (ParameterName, ParameterValue) and select it using a pivot table, but it will be confusing to keep it updated.

What is the recommendation in my situation, or I am doing something wrong ?
Thank you.

I am using Sql Server 2008 R2.

AlphaDaemon
  • 11
  • 2
  • 6

1 Answers1

0

I'm not sure "centralizing" the subscriptions into one or two tables is necessarily going to make them easier to configure, but if you are going to attempt it then something like option 3 is your best bet. The DDS query can contain any logic necessary to combine subscriber details and parameter values, as long as it returns one row per subscriber.

If your subscription delivery requirements are complex, you could look at using a third-party tool to help manage them, e.g. http://www.christiansteven.com/solutions/sql-rd/

Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51