Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS Reporting assitance- consolidated report using 3 Stroed PROC Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 6:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Hi,

I have 3 Stored Procedures
1) SP_JobPostingID,
2)SP_WorkerOrder, and
3) SP_WorkOrderRevision

Note- all the 3 have same number and signature of Columns, what I need to know is how develop an SSRS report using them combined in Microsoft Visual Studio.

one way I can think of is creating 3 Datasets for the same Reportdata but wished to know if any other way out .

Thanks
Dhananjay
Post #1426554
Posted Sunday, March 10, 2013 12:22 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 30, 2014 8:31 AM
Points: 647, Visits: 760
hi Dhananjay

if those 3 SP's have the same signature in the form of columns then why not combin those 3 SP's into 1 SP or create a 4th SP that puts the results of al of them into 1 temptabel of table var (depending on recordnbrs)
en put a final select at the end of this SP to pick up the collected data.
This way you would have only 1 dataset.

It would defenitely work easier in SSRs handling 1 dataset if you want to use a table object, i know

hope this helps you further,
Wkr,
Van Heghe Eddy
Post #1429000
Posted Monday, March 11, 2013 8:07 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:03 AM
Points: 486, Visits: 1,221
I have a similar report where the datasets and report format are exactly the same but 6 stored procs are called.

I created a non-queried report parameter where the parameter value is the stored proc name. Then in my dataset query I use an expression to construct the call to the stored proc selected in the parameter. Something like
="EXEC MyDatabase.dbo." & Parameters!MySprocParam.Value

In this way I can use one RDL instead of 6.



Post #1429283
Posted Monday, March 11, 2013 3:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Hi Ed,

thanks for the info, yes I have used similar logic and it's working-

///

LOGIC FOR CONSOLIDATED NEW SLLI REPORT-

create table #tmp_Set_Flags- temp table created
insert into #tmp_Set_Flags
select JP RECORDS
FROM [WorkForce_JobPosting] –
Select
into #tmp_SLII_Report
from #tmp_Set_Flags
select * from #tmp_SLII_Report
Delete * from #tmp_Set_Flags
insert into #tmp_Set_Flags
Select WO RECORDS
FROM [WorkForce_WorkOrder] –
Select
into #tmp_SLII_Report
from #tmp_Set_Flags
select * from #tmp_SLII_Report
Delete * from #tmp_Set_Flags
insert into #tmp_Set_Flags
Select WOR (Revision) RECORDS
FROM [WorkForce_WorkOrder] –
Select
into #tmp_SLII_Report
from #tmp_Set_Flags
select * from #tmp_SLII_Report—final report display


///


Thanks
Dhananjay
Post #1429495
Posted Tuesday, March 12, 2013 2:38 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 30, 2014 8:31 AM
Points: 647, Visits: 760
Glad you have found a solution

Wkr,
Van Heghe Eddy
Post #1429617
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse