SSRS Reporting assitance- consolidated report using 3 Stroed PROC

Question

SSRS Reporting assitance- consolidated report using 3 Stroed PROC

dhananjay.nagarkar

Default port

Points: 1496
More actions
March 4, 2013 at 6:48 pm

#394307

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 1

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

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 2

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.

dhananjay.nagarkar Default port Points: 1496 More actions · Answer 3

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

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 4

Van Heghe Eddy

SSCarpal Tunnel

Points: 4449

March 12, 2013 at 2:38 am

#1595844

Glad you have found a solution 🙂

Wkr,

Van Heghe Eddy