Running Stored Procedures on a Report Services Model.

views:

answers:

Running Stored Procedures on a Report Services Model.

I am designing an SQL Report Server Model, to replace a table that was used as a cut down version of the main database to report from. So the report will use the model to report from not the cut down database.

I was wondering if there was quick and easy way to take the Stored procedures, that were used for 100+ report on the cut down and point them on the new model.

Thanks

A SQL Server reporting services (SSRS) model is likely not a direct replacement for your Table.

A Model is used so that end users can write their own reports using a friendly report writer rather than writing complex sql statements (your existing stored procedures.) The model works on underlying tables but it doesn't ETL data from them into a separate storage area. If there were reasons to use the cut down table going back to the source data with your model may do more harm than good.

You can't run stored procedures against a reporting model; you can write SSRS reports that call stored procedures though so you could reuse your existing code and just deliver it via SSRS reports.

u07ch 2010-08-07 14:22:18

Thanks for the Reply.The cut down table was only designed so the user only had data they needed, so really the same principle as making a model.As the users that will have access to the model, wont have access to add, edit or remove data, just run report on it. Why would you think it would more harm than good? Thanks

Chris 2010-08-09 07:04:55

I was concerned that this table was created to get around concurrency or performance which is common in reporting. If this table was created to stop heavy reporting from impacting the live system then you may want to keep it. If thats not the case then switch to the live data.

u07ch 2010-08-09 08:17:51

ThanksI am attempting to create a Dataset connected to the Model, using SQL script as I have two table and would like to do case statement, joins etc within the dataSet. (This is why I was asking about the SP.) I keep on getting an error stating "The semantic query is not valid .." Error.Am I doing something wrong or can you only use the Drag and Drop to create dataset?

Chris 2010-08-12 08:28:06

My IssueI have create a model to replace the cut down database. The cut down database has two tables. Currently the model uses the same names for the two table and all fields. Now I am looking at reports that use SQL Script that have join etc as a dataSet (Example Below). The SQL works fine if I point the dataset straight to the cut down table, but does not work when I point it to the model. [Continues on Next Comment]

Chris 2010-08-12 09:12:04

Now I am looking at reports that use SQL Script that have join etc as a dataSet (Example Below). The SQL works fine if I point the dataset straight to the cut down table, but does not work when I point it to the model. [Continues on Next Comment]

Chris 2010-08-12 09:12:44

[SCRIPT]SELECT dbo.tbl_activities.SRA_SR_ID As IDCaisGwasanaeth, dbo.tbl_siebelReports.CeremonyLocation As Venue FROM dbo.tbl_siebelReports LEFT OUTER JOIN dbo.tbl_activities ON dbo.tbl_siebelReports.ROW_ID = dbo.tbl_activities.SRA_SR_ID WHERE (dbo.tbl_activities.TODO_CD IN ('Part. Sifil - Sermoni', 'Priodas - Seremoni', 'Part. Sifil - Rhybudd', 'Part. Sifil - Cyn-Sermoni', 'Priodas - Rhybudd','Priodas - Cyn-Seremoni')) AND (dbo.tbl_activities.EVT_STAT_CD = 'Scheduled') AND dbo.tbl_siebelReports.SR_TYPE_CD = 'External'[/SCRIPT]Help, Thanks

Chris 2010-08-12 09:16:27

You can create this as a View and point your model at the view rather than at the underlying tables...

u07ch 2010-08-12 09:45:58

ThanksI have create a view with two named queries that have made two separate tables. Each table is collating data from multiple tables.Example: table 1 is the task and table 2 is all sub task to the tasks in table 1.Then I have pointed a model to this view. Now that I am using this current model with two table, I need to create a Report that needs to join the two tables. Thanks

Chris 2010-08-12 10:31:04

Solved the Linking Issue.I had created the two table within the Model Viewer and created the model and deployed. After deploying, I added a relationship between the two tables and deployed the model again. What I had not done was regenerate the model before the deploy. Due to the school boy error, it looked like I could not use the relationship, wen building the report!Thanks for your time u07ch.

Chris 2010-08-12 12:40:18

ansaurus

tags:

views:

answers:

Running Stored Procedures on a Report Services Model.

related questions