We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).
Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.
Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.
So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?
Thanks.Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.
You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.
In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).
The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.
All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.
So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?
Or, do you have any other suggestions?
Thanks again.|||
Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.
You can use a data driven subscription regardless of whether you have one or many subscribers.
That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.
Hope this clears things up for you.
|||Thanks again for the info!I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.
Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?
Thanks!|||
Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.
|||Hi,
In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.
This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.
Thanks,
Mahesh
Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.
You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.
Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.
Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.
Hope that helps,
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:
Procedure dbo.sp_ReportMaster @.myid int=null
When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.
What am I missing here?
Thanks, Iris
|||Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.
The issue is the Report manager and management studio UI don't handle the case of a stored proc.
You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.
Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.
-Lukasz
|||I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.
The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.
I installed the packages in order like it said, and still got the error.
Thanks, Iris
No comments:
Post a Comment