Running a Stored Proc before report runs

Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?

Thanks!

You can make individual datasets be populated by a stored procedures. I think what you're eluding to is having one stored proc return multiple tables/results which is not supported.

The only way to achieve this is potentially to use a custom data delivery extension.

|||

I haven't tried this, but if your dataset's are coming from stored procedures, you could just call your 'data generation' stored procedure at the beginning of your reporting stored procedure.

Hope this helps.

Jarret

|||Nope, not alluding to one stored procedure return mulitple data-sets. :) I knew that wouldn't work. Actually, the stored procedure populates 4 tables with data from various other tables. Those 4 tables are used in the 4 different data-sets in the report. I'd like to be able to run my data-populating stored procedure before the report runs. I can do this using a scheduler and make sure it runs before the report. But sometimes things go wrong, and I can see the stored procedure not running and then the report will go out with no data...or something of that nature. I just thought it would be great if the running of the stored procedure could be tied to the report somehow.|||Actually, I tried that. I put the execution of the SP in the beginning of the dataset of the first table on the report. That 1st table had data. But the other 4 don't. I was hoping that since that was the first one, it might run in sequential order. :) Guess not.|||

Is there anyway you can break up the 'data generation' into 4 stored procedures so that each report calls an individual one to populate the data?

Jarret

|||

Nope. They all use the same tables.

Just for further clarification (don't feel like you need to read this).... The "data generation" SP takes all the call the calls that come into our call-center, gets the number of the person calling, the length of the call, etc. This data (after much manipulation) goes into one table (CTICalls). Then it creates another table for all the "work tickets" that were created due to the calls that came in. Now, all this data is in various other separate tables from the CTI Calls. It's a completely different system. Because of this, you can't just match up the name of the person who called to a ticket...further manipulation is required. All these tickets go into another table (CRMTickets). On top of that, the silly people who want this report want to know that name of the person who called. :) Of course, all I've got is the phone number. This requires another table because the table which actually contains the phone numbers has lots of duplicates and other bad things. So now I've got a phone number table with the name of the person calling. Great. So now the SP creates another table, which matches the CTI calls to the CRM tickets, sticks in the name of the person calling. So now I've got the table I need for the report. The first report is a summary - the number of calls per call center agent, and the number of tickets created. Next report lists all the calls that have no ticket created. Then we just list all of the calls, and then all of the tickets.

Hopefully, now you can see why I want to run the data generation first and why it can't really be broken up.

Jennifer

|||The only way I can think of ensuring the order in which the dataset queries are executed is to use dependant parameters, even if you just use some dummy values.|||

Hi,

I had quite similar problem with multiple sp-based datasets. The first one populated the global temporary tables and subsequent datasets displayed the data. To make sure the master SP will be executed first and no other datasets will be run before master SP completes, you have to do the following:

- Organize your datasets in exact order you need them to run. Datasets can only be moved by editing the RDL file. Find the <Datasets> section and move individual dataset sections.

- Enable transaction flag of the data source to prevent you datasets from being executed concurrently. In the RDL file, add <Transaction> element to the data source properties. Below is the example.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_inv_rep">
<Transaction>true</Transaction>
<DataSourceReference>ds_inv_rep</DataSourceReference>
<rd:DataSourceID>7f21a5bb-83e1-4c9c-a32b-5ee080055ed6</rd:DataSourceID>
</DataSource>

Wapper