Scenario :
Some weeks ago I was suprized to realize that a few analysts used a lot of time extracting data from an application and sending reports saved in Excel format to customers. The extract was based on customers account numbers and a customer might have one or even 10 account numbers. The reports were sligthly customized and delivered by mail on a daily, weekly or monthly basis. Besides, that was an extra requirement regarding the timeline of each type of report: the weekly
reports could reflect the latest n weeks ( n= 1...24), and n was different from one customer to another. The same idea for monthly and daily reports.
Challange : To fully automate this task and give the analysts the opportunity to maintain it, to create new customers or change data for the existing
customers such as time delivery , e-mail adresses, cancelling one or more deliveries for good or for a certain period of time, etc.
Keywords: Data-driven subscriptions based on a Reporting Services in Sharepoint integrated mode infrastructure and a dialogue with the business.
Hereby the following main steps for the solution :
1) Agree on a separate prototype for the daily, weekly and the monthly report. It's a matter of 'aggregating' all content requirements for all customers for each type of report. Some customers may receive some extra information not originally required, but this is seldom a problem.
2) Ask questions. Agree on all the definitions if you'are in doubt. Weekly for instance: American or European way ? Calendar week or the latest 7 days ? Can we agree on three schedule definitions for the daily, weekly and monthly deliveries ?
3) You are now ready to start designing the three reports and highly parametrize your datasets. Create 10 parameters @acc_1 ... @acc_10 for covering up to ten account numbers for potentially each of your customer and a @dd parameter covering the timeline.
4) Create a table for your data driven subscription already at this stage. The statement may look like: CREATE TABLE [dbo].[SubscriptionTable](
[SubscriptionID] [smallint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL, [Acc_1] [int] NOT NULL,
[Acc_2] [int] NULL, [Acc_3] [int] NULL, [Acc_4] [int] NULL, [Acc_5] [int] NULL, [Acc_6] [int] NULL,
[Acc_7] [int] NULL, [Acc_8] [int] NULL, [Acc_9] [int] NULL, [Acc_10] [int] NULL, [Email] [nvarchar](150) NOT NULL, [Daily] [smallint] NULL, [Weekly] [smallint] NULL, [Monthly] [smallint] NULL,
CONSTRAINT [PK_SubscriptionTable] PRIMARY KEY CLUSTERED
(
[SubscriptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
5) Design the queries for the above mentioned parameters. The @acc_1 ... @acc_10 parameters are based on datasets like: select distinct acc_1 from SubscriptionTable, and so on. That is why the data-driven subscription table has to be created so early. Your main query, used by the tablix is UNION based with a prototype like :
select
< information from the columns>
from
< join her all the tables you need to retrieve the information>
where
C.Customerid in ( @acc_1 )
and datediff ( day , <your extract date> , getdate() ) between 1 and @dd
UNION
....
UNION
< information from the columns>
from
< join her all the tables you need to retrieve the information>
where
C.Customerid in ( @acc_10 )
and datediff ( day , <your extract date> , getdate() ) between 1 and @dd
6) Test all the queries by assigning values for the parameters and finish designing of the three reports. In my case, the weekly and monthly reports were displaying the same summary and detailed information but the time perspective was of course different.
7) Deploy the three reports on a sharepoint library and create a data-driven subscription for each of them.
8) The query for the weekly subscription may look like : select
Name + ' ' + ' - Weekly report for latest ' + convert( char(2),Weekly) + ' week(s) issued ' + convert( varchar(12), GETDATE(), 104) as Subject , [Acc_1] ,[Acc_2] ,[Acc_3] ,[Acc_4] ,[Acc_5] ,[Acc_6] ,[Acc_7] ,[Acc_8]
,[Acc_9] ,[Acc_10] ,[Email] ,Weekly from SubscriptionTable where Weekly > 0
9) You may perhaps realize now that the table contains one row pr. customer. Name is a user friendly customer name used as part of the subject mail, email contains the customer email adresses and weekly serves as the number of weeks covering data for that particular customer.
10) Find a user friendly way for the business users in order to setup and maintain the solution and instruct them. It's often easier and more effective to produce a video clip for this purpose: http://youtu.be/ADoMp_SdGYo
If you have other alternatives or even a way for a better scaling of the solution - when a customer can be related to more then 10 accounts - than do not hesitate to contact me.
