November 15, 2012 at 5:26 pm
Hello All,
I have a ssrs report that based on a stored procedure.
The procedure looks something like this.
CREATE PROCEDURE CompanyReport
@ExecutionParameter varchar (20)
@Companyid int
AS
IF @ExecutionParameter = 'Company'
BEGIN
SELECT
....
FROM Companies
WHERE @Companyid = @Companyid
END
IF @ExecutionParameter = 'CompanyOrders'
BEGIN
SELECT
....
FROM CompanyOrders
WHERE @Companyid = @Companyid
END
In my report I have two data sets Companies and CompanyOrders
I call the procedure for each dataset like this
EXEC CompanyReport 'Company',1
EXEC CompanyReport 'CompanyOrders',1
This works great...for a single company.
I have a new requirement to to run the report for all companies or a group of companies.
My question: Can I use the existing model with two datasets being returned to the report and have each company and its orders appearing on a separate page in the report?
Or do I need to combine the two datsets into one on the SQL server side and have two tablixes on my report each using the unified datset with a different filter for each tablix?
Thanks if you can help...
November 16, 2012 at 8:36 am
You can.
Create a new report, Place a table, create a dataset that will have the multiple companies and embed the existing report into the table as a subreport. Using the Company id from the dataset passed into the subreport it will run once for each company.
Subreports are not always the most efficient something you may want to experiment with.
Simple Subreport Vid
http://www.dailymotion.com/video/xu82h5_reporting-services-subreports_tech
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply