Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Run a Query before the report begins

Author  Topic 

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-21 : 09:17:24
I have a report that uses a long-running query. In fact, it uses it in multiple places:

1. to set available and default parameters (several of them)
2. to populate the main report

It seems that SSRS is not smart enough to recognize this scenario, so the long-running query runs multiple times.

What I would like to do is run the query once, put the results in a results table on the server, then use the results table for the actual report. I realize that this means that the user needs write access to the database. I can set up a sandbox to minimize the impact.

I've been thinking that I could set up a report parameter, placed first in the list, that is invisible. That parameter would "get results from a query" but the query would just run the original, long-running query and store the results for the main report.

My questions:

1. are the report parameters processed in the sequence in which they appear in Report Builder (if not, why not)
2. Does this approach make sense, or is there a better way that I'm missing?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-21 : 09:35:19
I didn't quite get what you meant by whether the report parameters are processed in the order they appear in the report builder - if they are parameters sent to the database, they are all sent as one group into the query that you send to the database.

That aside, another possibility might be to create a status table that indicates whether the long running query has been run and has the current data. Whenever a report is run it should first query the status table to determine whether the long running query needs to be run. If it has not been run, then first it would run that query (which would also update the status table) and then run the report.

You may need the ability to store more than a true/false for the status of the long running query. You might need statuses such as a) no, it is not current, b) no, it is not current, but is currently being run, c) yes, it is current and useable etc.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-21 : 10:09:41
quote:
Originally posted by James K

I didn't quite get what you meant by whether the report parameters are processed in the order they appear in the report builder


In the Report Data pane, under Parameters, the report parameters are defined in a certain order (that you can change)

quote:
if they are parameters sent to the database, they are all sent as one group into the query that you send to the database.


I'm not passing any of the parameters to the query. Rather, I am populating parameters from the query.

quote:

That aside, another possibility might be to create a status table that indicates whether the long running query has been run and has the current data.


In this case, I am running the query (a select from a view that pulls data from linked servers that are often quite busy) from the report itself. I want to make sure that it is only run once per report rendering

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 07:22:16
quote:
Originally posted by gbritton

quote:
Originally posted by James K

I didn't quite get what you meant by whether the report parameters are processed in the order they appear in the report builder


In the Report Data pane, under Parameters, the report parameters are defined in a certain order (that you can change)
It just determines order in which parameters are to be displayed in the report and has nothing to do with how its passed to query. As said before, parameters are passed as a group to query behind
quote:
if they are parameters sent to the database, they are all sent as one group into the query that you send to the database.


I'm not passing any of the parameters to the query. Rather, I am populating parameters from the query.
But if you're using parameters to filter dataset inside dataset command then they will get passed to query as well
quote:

That aside, another possibility might be to create a status table that indicates whether the long running query has been run and has the current data.


In this case, I am running the query (a select from a view that pulls data from linked servers that are often quite busy) from the report itself. I want to make sure that it is only run once per report rendering
if you've used it inside a single dataset then it will only get executed once per report rendering by a user. but if multiple users run report simultaneoulsy then query will also get executed multiple times.
If you want to avoid prepopulate a table with result of query and use that table in query for your report dataset





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-22 : 11:51:21
quote:
Originally posted by visakh16

quote:
Originally posted by gbritton

quote:
Originally posted by James K

I didn't quite get what you meant by whether the report parameters are processed in the order they appear in the report builder


In the Report Data pane, under Parameters, the report parameters are defined in a certain order (that you can change)
It just determines order in which parameters are to be displayed in the report and has nothing to do with how its passed to query. As said before, parameters are passed as a group to query behind
quote:
if they are parameters sent to the database, they are all sent as one group into the query that you send to the database.


I'm not passing any of the parameters to the query. Rather, I am populating parameters from the query.
But if you're using parameters to filter dataset inside dataset command then they will get passed to query as well
I'm pretty sure that's not right. I have no parameters set up in my query. There's no way for BIDS to insert the parameters into the query (it wouldn't know where to put them). True, they are used to filter the results. However, I'm talking about something else. I set the available and default values for a parameter from a separate Dataset with its own parameterless query. I want to set up a new parameter that is invisible (never presented to the user) that gets its default values from a query but that query calls a sproc to do other work and the returns null. However, I would need to know that BIDS would process that parameter before all others. That is, does it respect the order of parameters as shown in the builder

In this case, I am running the query (a select from a view that pulls data from linked servers that are often quite busy) from the report itself. I want to make sure that it is only run once per report rendering
if you've used it inside a single dataset then it will only get executed once per report rendering by a user. but if multiple users run report simultaneoulsy then query will also get executed multiple times.
If you want to avoid prepopulate a table with result of query and use that table in query for your report dataset




yes! Exactly! I want to control this from the Report itself. So, it would do some SQL work before rendering the report. I'm thinking I can force it with an invisible parameter that get's its values from a query that does some work but only returns null

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-23 : 09:40:06
I'm pretty sure that's not right. I have no parameters set up in my query. There's no way for BIDS to insert the parameters into the query (it wouldn't know where to put them). True, they are used to filter the results. However, I'm talking about something else. I set the available and default values for a parameter from a separate Dataset with its own parameterless query. I want to set up a new parameter that is invisible (never presented to the user) that gets its default values from a query but that query calls a sproc to do other work and the returns null. However, I would need to know that BIDS would process that parameter before all others. That is, does it respect the order of parameters as shown in the builder
Nope. its right
you've to either pass it to query or you need to add parameter inside filter conditions of dataset /container. Otherwise parameter will never get applied at all. Also in that case there's no order of application. it applies all the filters after retrieving dataset data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-25 : 10:50:18
quote:
Originally posted by visakh16

I'm pretty sure that's not right. I have no parameters set up in my query. There's no way for BIDS to insert the parameters into the query (it wouldn't know where to put them). True, they are used to filter the results. However, I'm talking about something else. I set the available and default values for a parameter from a separate Dataset with its own parameterless query. I want to set up a new parameter that is invisible (never presented to the user) that gets its default values from a query but that query calls a sproc to do other work and the returns null. However, I would need to know that BIDS would process that parameter before all others. That is, does it respect the order of parameters as shown in the builder
Nope. its right
you've to either pass it to query or you need to add parameter inside filter conditions of dataset /container. Otherwise parameter will never get applied at all. Also in that case there's no order of application. it applies all the filters after retrieving dataset data.




OK. so how? Here's a sample query:


Select col1, col1, col3 from table_with_three_columns


Say I have two parameters defined: @p1 and @p2

I have no filters on the dataset (nor do I want any). I have no parameter substitutions in my query (nor do I want any). How could BIDS infer that I mean to filter my query or my dataset?

Note that I'm not talking about applying a parameter to a query. I'm talking about populating a parameter from a query. Say I define parameter @p0 as a date and specify that it gets its values from this query:

select getdate() as p0


I can verify that @p0 gets populated, so the query runs successfully. What I need to know is this: If I order my parameters in BIDS so that @p0 is at the top of the list, will it be processed first? If so, I can use it to do some pre-work -- kind of like a class constructor in an OOP program.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 14:14:25

Say I have two parameters defined: @p1 and @p2

I have no filters on the dataset (nor do I want any). I have no parameter substitutions in my query (nor do I want any). How could BIDS infer that I mean to filter my query or my dataset?

then why do you need parameter in the first place
The whole purpose of adding parameter is to do filtering for report data.
The way BIDS does is this. When you create a query first with parameters included (ie @p0,@p1 etc) BIDS will on the background create parameter for you. If you created the parameter already with same name as that used in query then it will do automatic mapping too. In case your created parameter name is different, then you need to do mapping yourself.
If your plan is to do filtering in dataset, then you've to add it yourself.
What I need to know is this: If I order my parameters in BIDS so that @p0 is at the top of the list, will it be processed first? If so, I can use it to do some pre-work
yes..based on order, query for the parameter will get executed.
Didnt understand what you mean by "use it to do some pre-work" please elaborate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-26 : 09:13:04
quote:
Originally posted by visakh16


Say I have two parameters defined: @p1 and @p2

I have no filters on the dataset (nor do I want any). I have no parameter substitutions in my query (nor do I want any). How could BIDS infer that I mean to filter my query or my dataset?

then why do you need parameter in the first place
The whole purpose of adding parameter is to do filtering for report data.
The way BIDS does is this. When you create a query first with parameters included (ie @p0,@p1 etc) BIDS will on the background create parameter for you. If you created the parameter already with same name as that used in query then it will do automatic mapping too. In case your created parameter name is different, then you need to do mapping yourself.
If your plan is to do filtering in dataset, then you've to add it yourself.
What I need to know is this: If I order my parameters in BIDS so that @p0 is at the top of the list, will it be processed first? If so, I can use it to do some pre-work
yes..based on order, query for the parameter will get executed.
Didnt understand what you mean by "use it to do some pre-work" please elaborate




from my original post:

quote:


I have a report that uses a long-running query. In fact, it uses it in multiple places:

1. to set available and default parameters (several of them)
2. to populate the main report

What I would like to do is run the query once, put the results in a results table on the server, then use the results table for the actual report. I realize that this means that the user needs write access to the database. I can set up a sandbox to minimize the impact.

I've been thinking that I could set up a report parameter, placed first in the list, that is invisible. That parameter would "get results from a query" but the query would just run the original, long-running query and store the results for the main report.




FWIW Filtering is not the only use for Report Parameters. A common use, to be sure. But its not too hard to come up with other uses!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 09:26:51
But its not too hard to come up with other uses!
Then why not enlighten us with examples of some of those uses?
I've been thinking that I could set up a report parameter, placed first in the list, that is invisible. That parameter would "get results from a query" but the query would just run the original, long-running query and store the results for the main report.

Thats exactly what we mean by "filtering the query"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 09:34:42
Also one more thing if long running query is always the same then why not populate table with results once per day or based on frequency depending on data volatility so that you dont need this step at all in report. In report you could just use a query con taining this table and adding required filters.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-26 : 13:12:03
quote:
Originally posted by visakh16

But its not too hard to come up with other uses!
Then why not enlighten us with examples of some of those uses?
I've been thinking that I could set up a report parameter, placed first in the list, that is invisible. That parameter would "get results from a query" but the query would just run the original, long-running query and store the results for the main report.





I see the confusion now! Better stated:

That parameter would cause the execution of an unparameterized query that would do so pre-work for the report.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 05:10:39
quote:
Originally posted by gbritton

quote:
Originally posted by visakh16

But its not too hard to come up with other uses!
Then why not enlighten us with examples of some of those uses?
I've been thinking that I could set up a report parameter, placed first in the list, that is invisible. That parameter would "get results from a query" but the query would just run the original, long-running query and store the results for the main report.





I see the confusion now! Better stated:

That parameter would cause the execution of an unparameterized query that would do so pre-work for the report.


Why cant you make this query execution separate then? that would make sure it will get executed only one time
Including it in report means it will get executed each time when report is rendered by someone. So unless there's any interactivity required like parameter values to be passed to query then its best to keep it separate.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-27 : 09:05:29
quote:
Originally posted by visakh16

quote:
Originally posted by gbritton

quote:
Originally posted by visakh16

But its not too hard to come up with other uses!
Then why not enlighten us with examples of some of those uses?
I've been thinking that I could set up a report parameter, placed first in the list, that is invisible. That parameter would "get results from a query" but the query would just run the original, long-running query and store the results for the main report.





I see the confusion now! Better stated:

That parameter would cause the execution of an unparameterized query that would do so pre-work for the report.


Why cant you make this query execution separate then? that would make sure it will get executed only one time
Including it in report means it will get executed each time when report is rendered by someone. So unless there's any interactivity required like parameter values to be passed to query then its best to keep it separate.




Actually, I plan to do just as you suggest. I'm setting up an Agent job to run the query overnight and save the results in a table for the report to use instead of running the query itself, not just because of my original problem but because the main report has an Action item for a few fields to "Go to Report" and THAT report needs to have the query results indexed. Since the query is against a view, I could index the view, but from my perspective, there's no real advantage.

I can schedule the Agent job to run when the ETL for the underlying data finishes and that should do it.
Go to Top of Page
   

- Advertisement -