Data driven subscriptions. Has anybody implemented it?
-
We'd like to create data driven subscriptions system.
I was wondering if anybody has any experience with it
and if yes then maybe they could share some tips or ideas.
Currently we have about 250 subscriptions
and it we're loosing control. It is becoming very time consuming to
maintain them through Report Manager.
Things like removing emails, adding new emails to the distribution lists, changing parameters
takes too much of our time. We'd like to support subscriptions through the database.
Thank you.
-
I have actually implemented this and it is pretty slick. The only thing that I had trouble with is when it failed to send an email to an internal address because it was bad, it took an act of god to figure out which one was the problem. Other than that though it is pretty clean.
We (unfortunately) were using a spreadsheet to hold all the subscriptions and updated the table daily.
Building an app would probably be a better solution.
Verify all the parameters if you can, ESPECIALLY if you are generating reports against SSAS, I had a bear of a time with that part.
You are using Enterprise edition right? DDS are only available in Enterprise.. There is a third-party tool that does this as well, it is like $1000..
CEWII
-
What third party DDS tool?
Can you give me a link please?
We were going to design our own system.
I guess it's a few tables with SubscriptionID, SubscriptionName,
ParameterName, ParameterValue, etc. columns
We're not sure yet. We're actually looking for a good tables structure
and overall solution. Maybe youcan give me any links, articles, samples.
I would appreciate it.
Thank you.
-
I have been using Data Driven Subscriptions for several years. I haven't done anything fancy with it, I just create tables in a sql database with columns for all of the necessary parameters. Different reports usually have their own table. It works great for sending multiple variations of the same report to multiple people. I currently have more that 25 reports that are emailed via DDS on either a daily, weekly or monthly basis. One of these goes out daily, customized for 75 different locations.
-
the question is:
SSRS comes with some DDS solution.
[New Data Driven Subscription] button at the toolbar in Report Manager
How do I use it?
I can't find any documentation on this.
Any samples?
-
I am not sure which version of Reporting Services you are using, but here is a link to a tutorial for 2005: http://msdn.microsoft.com/en-us/library/ms169673(SQL.90).aspx
And here is a link to the appropriate sections of SQl Server 2005 books online - http://msdn.microsoft.com/en-us/library/ms156012(SQL.90).aspx
I assume you have done some google searching on your own.
I hope these are helpful. I wish I had more time to do screen shots and create my own tutorial for you. I remember how I struggled to understand the concepts.
-
The biggest part is having a query that provides all the input values for the report being generated. This is pretty straightforward if you are reporting of relational data, when it is SSAS data the format is quite tricky, because the values have to be in a specific format which is NOT easy to figure out. You then map the report parameters to the fields from the query, the screen will help you with this.
I am still looking for the 3rd party app that does this without having to have Enterprise.
CEWII
-
When I need to understand how exactly something works
MSDN is the worst place.
I don't know, maybe it's just me but
I just get very angry when you read 2-3 pages article
and realise it does not answer the main question - how exactly it works.
Did you read the link you sent me?
It's all written for idiots:
"To create a data-driven subscription, from Report Manager select a report... Click Create..."
I know I have to click "Create" but how about the real questions?
1. What exactly built-in DDS allows me to do?
2. Just store email recipients addresses or something more?
3. If more then will I be able to store all my Report configuration settings in a subscription?
4. If yes, then how exactly? Do I need to create custom tables to hold Subscriber, report parameters information.
5. Can I distribute the same report to different users using different parameters?
If yes, how do I implement it?
These are the most interesting questions.
It should be short and to the point.
This MSDN article talks about some bullshit that has no value for a real developer.
-
What exactly built-in DDS allows me to do?
It allows you to schedule report generation and delivery with different parameters for each recipient.
Just store email recipients addresses or something more?
Addresses and More.
If more then will I be able to store all my Report configuration settings in a subscription?
If you are refering to the parameters that would be chosen for each report then yes. If that is not what you are refering to then please clarify.
If yes, then how exactly? Do I need to create custom tables to hold Subscriber, report parameters information.
Yes, you need to build a table to hold all possible parameters and email address(es). The structure is purely dependent on your reports, but if you have a lot of reports you might try to build a table that can be reused. The way this table is used is when you define the DDS you specify a query to get the data out of the table and then map the table columns to the report input parameters, the screen will walk you through that part.
Can I distribute the same report to different users using different parameters?
Yes, they will be different rows in the table.
If yes, how do I implement it?
See above..
More questions?
CEWII
-
Thank you Elliot.
-
We use a main table for all subscribers, and another table for each report. So if someone gets more than 1 report, we only have to list them once.
And you can also split out the Report title, Comments, etc. so it's a bit more flexible.
Consider using multiple recipients if the same report is being sent. Use ;space between email addresses.
Give careful consideration to data sources. With the nature of DDS, you may be using an ID that can see all data. So you may end up publishing twice - once for a subscription, once for interactive use, and securing users from accessing the subscription version.
Greg E
-
So far we came up withia set of tables to build our own DDS system.
Below is the schema.
USE [Subscribers]GO
CREATE TABLE [dbo].[UserInfo](
[UserEmail] [varchar](50) NOT NULL,
[Name] [varchar](50) NULL,
[Format] [varchar](50) NOT NULL,
[Attach] [bit] NOT NULL,
[Link] [bit] NOT NULL,
CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED
(
[UserEmail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ParameterInfo](
[DistributionID] [int] NULL,
[ParamName] [varchar](40) NOT NULL,
[ParamValue] [varchar](200) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ReportInfo](
[ReportID] [int] IDENTITY(1,1) NOT NULL,
[ReportName] [varchar](100) NULL,
CONSTRAINT [PK_ReportInfo] PRIMARY KEY CLUSTERED
(
[ReportID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UserGroup](
[UserEmail] [varchar](50) NOT NULL,
[GroupName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DistributionInfo](
[DistributionID] [int] NOT NULL,
[FileName] [varchar](30) NULL,
[Path] [varchar](150) NULL,
[RenderFormat] [varchar](50) NULL,
[WriteMode] [varchar](20) NULL,
[FileExtn] [varchar](4) NULL,
[RecipientType] [varchar](3) NULL,
[GroupName] [varchar](50) NOT NULL,
[ReportID] [int] NOT NULL,
CONSTRAINT [PK_DistributionInfo_1] PRIMARY KEY CLUSTERED
(
[DistributionID] ASC,
[GroupName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: ForeignKey [FK_FileInfo_ReportInfo] Script Date: 10/20/2009 09:42:11 ******/
ALTER TABLE [dbo].[DistributionInfo] WITH CHECK ADD CONSTRAINT [FK_FileInfo_ReportInfo] FOREIGN KEY([ReportID])
REFERENCES [dbo].[ReportInfo] ([ReportID])
GO
ALTER TABLE [dbo].[DistributionInfo] CHECK CONSTRAINT [FK_FileInfo_ReportInfo]
GO
/****** Object: ForeignKey [FK_UserGroup_UserInfo] Script Date: 10/20/2009 09:42:11 ******/
ALTER TABLE [dbo].[UserGroup] WITH CHECK ADD CONSTRAINT [FK_UserGroup_UserInfo] FOREIGN KEY([UserEmail])
REFERENCES [dbo].[UserInfo] ([UserEmail])
GO
ALTER TABLE [dbo].[UserGroup] CHECK CONSTRAINT [FK_UserGroup_UserInfo]
GO
We' re still testing it and most likely it will change.
Right now we have an issue with storing Parameter values for "Mutliple list".
Example:
CURRENCY:
USD
CAD
GBP
We need to be able to store all three parameter values.
I'll keep you posted.
-
We' re still testing it and most likely it will change.
Right now we have an issue with storing Parameter values for "Mutliple list".
Example:
CURRENCY:
USD
CAD
GBP
We need to be able to store all three parameter values.
If I understand what you're trying to do, wouldn't just making a ParmCurrency table, and keying this to the user / group work?
Or widen one of the tables and do ParmCurrency, ParmRegion, Parm Company, etc as needed.
I'd assume that 3 runs would be needed - 1 for each currency - if a particular user / group needed to see all currencies.
You might want to start simple and just get things up and running for awhile. Then you can see what changes you might need. Our main subscriber table and a separate table for each report has proved easy for us to manage.
Greg E
-
You have to keep in mind.
We have about 150 reports for 5 or 6 different groups/departments.
Each report is serving different needs and tasks.
We cannot create a table ParamCurrenct or something like that.
This would be not a dynamic solution.
Our goal is to create a flexible system that can handle
hundreds of different reports with thousands of parameters,
multiple distibution lists and users.
And it should be relatively easy to create/update a subscription through using system.
Example:
We have "Limit Monitoring" report with about 8 parameters.
There are 25 subscriptions for this report.
Different parameters are used for different users.
Let's say we need to remove 2 emails and add 5 new emails to subscription 5.
How would you do it?
With our solution you just locate the subscription in [ReportInfo] table,
then find associated records in [DistributionInfo], then find associated GroupName,
go to [UserGroup] table.
Let's say it's UserGroup=14
So we have to delete 2 records for these 2 users where UserGroup=14
and create 5 new users with UserGroup=14.
Done.
-
So your parameter table is a name-value pair type of arrangement. For this I can see that. When you build the query for each report how are you going to get those different parameters on a single line? I know it isn't the best performing (and Jeff M won't like it..) But you could build a function to get the value and it would simplify the query but hurt query performance for the DDS portion, that is probably acceptable..
CEWII
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply