March 4, 2013 at 6:48 pm
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
March 10, 2013 at 12:22 pm
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
March 11, 2013 at 8:07 am
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.
March 11, 2013 at 3:24 pm
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
March 12, 2013 at 2:38 am
Glad you have found a solution
Wkr,
Van Heghe Eddy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy