Running SQL Code prior to report generation

Hello,
Basically I have a problem where I need to run SQL code to populate a table
which the report then uses to generate data. The "easy" solution is to
included the code before the SQL query in a single dataset.
BUT I am currently using multiple datasets which all need to access the
newly poplutated table. So, is there a way to force RS to run a dataset
before the others? Or to run some SQL code before the rest of the report?
Does anyone have any suggestions?
ThankThe following suggestion should work for RS 2000 but with each release it
could change (might not but this is an undocumented solution). RS executes
queries in the order found in the RDL. The RDL is just an xml file. You can
see and edit the code in VS by do a View-> code. Be sure to save a copy
before mucking with it. Manually rearrange the RDL to put the datasets in
the order you want. In this case I would suggest you add a new dataset. You
don't have to bind it to anything. That dataset can end up empty but it
would call the SP (or SQL code) that populates your table. Have that dataset
first in the RDL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Marek.Dziedzic" <MarekDziedzic@.discussions.microsoft.com> wrote in message
news:90C1E6BB-9E4B-4F7A-9ACF-7F1D8B0B2C80@.microsoft.com...
> Hello,
> Basically I have a problem where I need to run SQL code to populate a
> table
> which the report then uses to generate data. The "easy" solution is to
> included the code before the SQL query in a single dataset.
> BUT I am currently using multiple datasets which all need to access the
> newly poplutated table. So, is there a way to force RS to run a dataset
> before the others? Or to run some SQL code before the rest of the report?
> Does anyone have any suggestions?
> Thank|||Easiest way is to put as much of the upfront stuff as you can in a
VIEW. If that's not appropriate, you could do it in a data warehousing
style - seperate the number-crunching entirely and generate a large
static table. Include the parameters you want to query on as columns in
that table, denormalise as much as you need. Regenerate that table
periodically, overnight perhaps.|||My SQL code Dataset is the first one in the RDL file. It still doesn't work.
I'm not sure why. That was my first thought too... but it seems like the SQL
code finishes after the other datasets are already populated.
My RDL file reads:
<DataSets>
<DataSet Name="Code">
<Query>
<DataSourceName>c2csbo</DataSourceName>
<CommandText>
...
...
</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="XLearn">
...
Is this the only place that I need to change this or is there something else
that I should modify?
"Bruce L-C [MVP]" wrote:
> The following suggestion should work for RS 2000 but with each release it
> could change (might not but this is an undocumented solution). RS executes
> queries in the order found in the RDL. The RDL is just an xml file. You can
> see and edit the code in VS by do a View-> code. Be sure to save a copy
> before mucking with it. Manually rearrange the RDL to put the datasets in
> the order you want. In this case I would suggest you add a new dataset. You
> don't have to bind it to anything. That dataset can end up empty but it
> would call the SP (or SQL code) that populates your table. Have that dataset
> first in the RDL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Marek.Dziedzic" <MarekDziedzic@.discussions.microsoft.com> wrote in message
> news:90C1E6BB-9E4B-4F7A-9ACF-7F1D8B0B2C80@.microsoft.com...
> > Hello,
> >
> > Basically I have a problem where I need to run SQL code to populate a
> > table
> > which the report then uses to generate data. The "easy" solution is to
> > included the code before the SQL query in a single dataset.
> >
> > BUT I am currently using multiple datasets which all need to access the
> > newly poplutated table. So, is there a way to force RS to run a dataset
> > before the others? Or to run some SQL code before the rest of the report?
> > Does anyone have any suggestions?
> >
> > Thank
>
>|||I can't make a view since it's not a SQL query, but sql code. There is no
return values. I tried and it would not let me create the view.
I tried making a stored procedure and "exec" it just before my query, but RS
doesn't seem to like that either, it gave me the following error:
An error has occured during report processing.
Query execution failed for dataset 'ReportData'.
Must declare the variable '@.paramTeam'.
@.paramTeam is a report parameter that I use for the query (from a drop down
just before the report is generated) If I run the dataset query in the Data
tab, it works fine... but in preview mode it doesn't seem to work.
"timseal" wrote:
> Easiest way is to put as much of the upfront stuff as you can in a
> VIEW. If that's not appropriate, you could do it in a data warehousing
> style - seperate the number-crunching entirely and generate a large
> static table. Include the parameters you want to query on as columns in
> that table, denormalise as much as you need. Regenerate that table
> periodically, overnight perhaps.
>|||If you use a large table and generate everything the previous night,
you don't have to do any "exec" from the RDL. Not sure what else to
say, without seeing the sql code.|||Thanks, I was thinking of running a cron... but management want to
dynamically be able to run the reports.
There is no pre-run option, right? Like before running the report, run this
code? Or alternately, would it be possible to write a script the runs the
code then the report? I don't know what language would be useful to do that
in. I guess something along the lines of
<code>
run code.sql
run http://<server>/reportserver?%2fmy+report+directory%2freport (blah blah)
</code>
which would run the sql code then open the report.
/peritus/reportserver?%2fPeritus+Reporting+System%2fXLearn&rs:Command=Render
"timseal" wrote:
> If you use a large table and generate everything the previous night,
> you don't have to do any "exec" from the RDL. Not sure what else to
> say, without seeing the sql code.
>