running the same report different parameters

Is there a way to run the same report with different parameter values?
I have 159 different parameter values (In other words - 159 reports to
save). I have already written a VB.NET application (with reporting
services web service) to automate the saving of these reports (by way
of pdf files).
I was wondering if there is a *better way* to do this. I thought
subscriptions would be good idea; however, I have 159 different
subscriptions to set up not a good idea! Is there a way to configure
subscriptions under SQL Server (via, jobs), so I would not have to
create a custom application using a webservice.
Thanks in advance!
TravisTry a linked report, and there is an app to help you if you go that way.
http://www.sqldbatips.com/showarticle.asp?ID=73
Reeves
"Travis" wrote:
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||I can't help you with your issue, but maybe you can help me with mine. Im
trying to get my reports to PDF via a VB.NET application, and I'm having no
luck. If you can help maybe contact me at specialk_ca@.hotmail.com
Michael C
"Travis" wrote:
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||You can kick off subscriptions via sql server agent. (MS would probably not
recommend or support you if you did it). I have done this in the past to
overcome some of Reporting Service's shortcomings regarding executing
reports based on a fiscal calendar. If you're good at SQL scripting you
shouldn't have much trouble. The downside of doing it this way is that RS
creates new GUIDS for subscriptions anytime that they are updated. The sql
job's stored proc call is going to reference one of the subscription guids
and of course if you have alot of people tinkering with your subscriptions
you could have issues with reports not firing off because the guid
referenced in your job changes. In hindsight I think I could have written
the sql job to dynamically obtain the guid's it was about to execute but
I've been away from RS for almost a year and I don't remember the specifics
of it. Look on your report serverdb for the available stored procs. I
don't think the name of the execute one was too obfuscated. Good luck!
-Michael Abair
"Travis" <tswill54@.bellsouth.net> wrote in message
news:1187734326.317571.264180@.19g2000hsx.googlegroups.com...
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||I read that a little too fast and my previous answer was way off.
I would think setting up a subscription would be the best, as stated in the
previous reply. Setting up 159 reports will be fun. If you want to run it
through your vb.net app you can path out to it, but then I'm not sure if you
can force the save at that point, but you might want to look at calling the
service directly from your app and then save the stream. Not sure on the
total details.
Sorry about the first post.
Reeves
"Travis" wrote:
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||I think you can use Data Driven Subscriptions to fullfill your task.
Refer to steps involved in it from
http://msdn2.microsoft.com/en-us/library/aa225509(SQL.80).aspx
Thanks
Subhash

Running stored procedure multiple times

I'm binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I'm using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?

newSqlCommand = New SqlCommand("getDistinctValues", newConn)
newSqlCommand.CommandType = CommandType.StoredProcedure

Dim ownrParam As New SqlParameter("@.owner_id", SqlDbType.Int)
Dim colParam As New SqlParameter("@.column_name", SqlDbType.VarChar)
newSqlCommand.Parameters.Add(ownrParam)
newSqlCommand.Parameters.Add(colParam)

ownrParam.Value = OwnerID

colParam.Value = "Make"
newConn.Open()
ddlMake.DataSource = newSqlCommand.ExecuteReader()
ddlMake.DataTextField = "distinct_result"
ddlMake.DataBind()
newConn.Close()

colParam.Value = "Model"
newConn.Open()
ddlModel.DataSource = newSqlCommand.ExecuteReader()
ddlModel.DataTextField = "distinct_result"
ddlModel.DataBind()
newConn.Close()

and so on for 9 columns…

First of all, reusing the connection will increase your performance significantly.

|||

Hi

if you create a stored procedure returning multiple result sets and load these result sets into the DataSet, you will avoid multiple roundtrips to SQL server.

-yuriy

|||

Under ADO.NET, only the first Open opens the database, the rest pick up a connection from a pool as closing the database connection does not actually close the connection but returns it to a pool. You still open and close in your code, but what happens behind the scenes is slightly different from previous access methods such as ADO.

|||

Thanks guys.

Tatworth, I believe you are saying that I'm reusing the same connection by doing it this way. In that case, is it an acceptable and reasonably efficient way to do things?

I need the DISTINCT values for these columns, so I can't simply select all the values for all 9 columns at once, unless I'm prepared to eliminate repeated values in code. Is it possible for a single stored procedure to return 9 sets of distinct values?

|||

>Tatworth, I believe you are saying that I'm reusing the sameconnection by doing it this way. In that case, is it an acceptable andreasonably efficient way to do things? yes

> need the DISTINCT values for these columns, so I can't simplyselect all the values for all 9 columns at once, unless I'm prepared toeliminate repeated values in code. Is it possible for a single storedprocedure to return 9 sets of distinct values?
It is possible as a stored procedure can return multiple recordsets but not practical as ASP.NET can only read the first recordset without extra code - so keep doing it the way you are doing it now.

I will be unable to access this site for some days, so I suggest you mark this thread as closed and open a new thread if you have further queries.

|||

There are multiple approaches to handling the above depending on your situation, and the data involved.

Normally, I would use a sqldatasource (9 of them), and bind the dropdowns to the datasources, and evaluate from there if necessary. The SqlDatasource control is nice because you can quickly and easily add caching if necessary (If you think that the ownerid will be called multiple times in a short period of time and the results should always remain the same for each call, then it'd be very helpful).

However, since you've gone down the approach of handling it manually, the only thing that is going to really buy you anything (other than coding any caching yourself), is to return the results in a single trip from the database. One such method would be to do something like return a single resultset that has 2 values, one being the column name, the other the value (one of the distinct values) likes:
col1 val1

col1 val2

col1 val3

col2 val1

col2 val2

Then you can retrieve this resultset from the database into a dataset. With the dataset, you can then derive a view that has a filter clause (Like a WHERE clause), and bind to the dataview.

Another approach, although I have not personally tested this is to use the datareaders nextresult property. Like:

conn.open

dim dr as sqldatareader=cmd.executereader

{Your code to prep control 1 here}

control1.datasource=dr

control1.databind

{Ending stuff for control 1 here}

dr.nextresult

{Your code to prep control 2 here}

control2.datasource=dr

control2.databind

{Ending stuff for contorl 2 here}

dr.nextresult

...

This approach would be extremely easy to modify your current code into, as you would just make need to make a master stored procedure like:

CREATE PROCEDURE dbo.sp_TheMaster ( @.ownerid int) AS

EXEC sp_OriginalProcedure @.ownerid,'column1'

EXEC sp_OriginalProcedure @.ownerid,'column2'

...

That would return the 9 resultsets that you need, although I'm not sure of it's performance, but I suspect it would be better.

|||

Thanks for your detailed response Motley. I considered the sqldatasource – I think it would be possible to use a single datasource for all 9 queries – but I chose the RP option only because it seems tidier to me (less 'markup'). I'm assuming there would be no performance difference?

As time is an issue, and as it seems that what I'm doing is not disastrous in performance terms, I'll leave it and monitor it when the site is uploaded.

Thanks all for your input.