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

---

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.

---

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.

---

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

---

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

---

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.

---

Select col1, col1, col3 from table_with_three_columns

select getdate() as p0

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

---

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.

---

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.

---

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.

---

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.

---