December 13, 2013 at 6:10 am
Hi,
I have a situation like this:
I have four radio buttons in my SSRS report which is used for visibility (making visible/ hidden) of the respective parts in the report.
@ShowApprovedWorkload
@ShowDraftWorkload
@ShowDoneWorkload
@ShowInProgressWorkload
Presently in the stored proc, the total is hsowing, without considering the visiblity. Ie. when the approved workload needs to be shown in the report and Draft Workload not required to be shown in the report depending upon the value of parameters @ShowApprovedWorkload = 1 and @ShowDraftWorkload = 0. The approved workload is 10 and Draft Workload is 10, the total workload should be 10, as @ShowDraftWorkload = 0. But the total workload now shown is 20 itself.
In my calculation I want to use these parameters in such a way that the total workload is calculated correctly based on the visibilty required.
For example, if approved workload is 10 and Draft Workload is 10, the total workload should be 20, when the approved workload and Draft Workload needs to be shown in the report depending upon the value of parameters only if @ShowApprovedWorkload = 1, @ShowDraftWorkload = 1, @ShowDoneWorkload = 0 and @ShowInProgressWorkload = 0.
Please help in finding the solution and I attaching the existing stored proc.
ALTER PROCEDURE [dbo].[TotalWorkload_Calculation]
(@from_date datetime,
@to_date datetime,
@ShowApprovedWorkloadbit,
@ShowDraftWorkloadbit,
@ShowDoneWorkloadbit,
@ShowInProgressWorkload bit)
AS
--DECLARING VARIABLES
declare @Minval as int
declare @Maxval as int
declare @Act_ID as int
declare @Act_MAXID as int
Begin
set @Minval = (select min(sno1) from #Activities2)
set @Maxval = (select max(sno1+1) from #Activities2 )
---------*********** This is where I need to apply the @ShowApprovedWorkload, @ShowDraftWorkload, @ShowDoneWorkload, @ShowInProgressWorkload filters ( where the total workload is calculated ) ------******************
-- total workload by day
create table #WorkloadTMP1 (Thedate datetime,workload FLOAT(2),Activityincid int )
while @Minval < @Maxval
begin
set @Act_ID = (select activityIncId from #Activities2 where sno1 = @Minval)
declare @start_date1 datetime
declare @end_date1 datetime
declare @budget1 float
declare @Datediff1 int
set @start_date1 = (select min(A.startdate) from #Activities2 AS A where activityIncId = @Act_ID )
set @end_date1 = (select max(A.enddate) from #Activities2 AS A where activityIncId = @Act_ID)
set @budget1 = (select budgetedTotalworkload from #Activities2 where activityIncId = @Act_ID )
set @datediff1 = (select datediff(day,@start_date1,@end_date1)+1)
insert into #WorkloadTMP1
SELECT *,CAST(@budget1 as float)/cast(@datediff1 as float) ,@Act_ID from eufn_e5_eSM_AS01_ExplodeDates (@start_date1,@end_date1)
------SELECT * FROM #WorkloadTMP1
set @Minval = @Minval+1
end
END
---------***********------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------******************
-- approved workload by day (todo)
Begin
set @Minval = (select min(sno) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 2)
set @Maxval = (select max(sno+1) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 2)
create table #WorkloadTMP2 (Thedate datetime,Approvedworkload FLOAT(2),Activityincid int )
while @Minval < @Maxval
begin
set @Act_ID = (select activityIncId from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 2 and sno = @Minval) -- ToDo
declare @start_date2 datetime
declare @end_date2 datetime
declare @budget2 float
declare @Datediff2 int
set @start_date2 = (select min(A.startdate) from #Activities2 AS A where activityIncId = @Act_ID )
set @end_date2 = (select max(A.enddate) from #Activities2 AS A where activityIncId = @Act_ID)
set @budget2 = (select budgetedTotalworkload from #Activities2 where activityIncId = @Act_ID )
set @datediff2 = (select datediff(day,@start_date2,@end_date2)+1)
insert into #WorkloadTMP2
SELECT *,cast(@budget2 as float)/cast(@datediff2 as float),@Act_ID from eufn_e5_eSM_AS01_ExplodeDates (@start_date2,@end_date2)
------SELECT * FROM #WorkloadTMP2
set @Minval = @Minval+1
end
end
-- draft workload by day (draft)
Begin
set @Minval = (select min(sno) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 1)
set @Maxval = (select max(sno+1) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 1)
create table #WorkloadTMP3 (Thedate datetime,Draftworkload FLOAT(2),Activityincid int )
while @Minval < @Maxval
begin
set @Act_ID = (select activityIncId from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 1 and sno = @Minval) -- Draft
declare @start_date3 datetime
declare @end_date3 datetime
declare @budget3 float
declare @Datediff3 int
set @start_date3 = (select min(A.startdate) from #Activities2 AS A where activityIncId = @Act_ID )
set @end_date3 = (select max(A.enddate) from #Activities2 AS A where activityIncId = @Act_ID)
set @budget3 = (select budgetedTotalworkload from #Activities2 where activityIncId = @Act_ID)
set @datediff3 = (select datediff(day,@start_date3,@end_date3)+1)
insert into #WorkloadTMP3
SELECT *,cast(@budget3 as float)/cast(@datediff3 as float),@Act_ID from eufn_e5_eSM_AS01_ExplodeDates (@start_date3,@end_date3)
------SELECT * FROM #WorkloadTMP3
set @Minval = @Minval+1
End
end
-- done workload by day (done)
Begin
set @Minval = (select min(sno) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 6)
set @Maxval = (select max(sno+1) from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 6)
create table #WorkloadTMP4 (Thedate datetime,DoneWorkload FLOAT(2),Activityincid int )
while @Minval < @Maxval
begin
set @Act_ID = (select activityIncId from #Activities2 where ActivityStatusSqlId = 134 AND ActivityStatusIncId = 6 and sno = @Minval) -- Done
declare @start_date4 datetime
declare @end_date4 datetime
declare @budget4 float
declare @Datediff4 int
set @start_date4 = (select min(A.startdate) from #Activities2 AS A where activityIncId = @Act_ID )
set @end_date4 = (select max(A.enddate) from #Activities2 AS A where activityIncId = @Act_ID)
set @budget4 = (select budgetedTotalworkload from #Activities2 where activityIncId = @Act_ID )
set @datediff4 = (select datediff(day,@start_date4,@end_date4)+1)
insert into #WorkloadTMP4
SELECT *,cast(@budget4 as float)/cast(@datediff4 as float),@Act_ID from eufn_e5_eSM_AS01_ExplodeDates (@start_date4,@end_date4)
------SELECT * FROM #WorkloadTMP4
set @Minval = @Minval+1
end
end
-- In progress or On hold status workload by day (In progress or On hold status)
Begin
set @Minval = (select min(sno) from #Activities2 where (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 4) OR (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 5))
set @Maxval = (select max(sno+1) from #Activities2 where (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 4) OR (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 5))
create table #WorkloadTMP5 (Thedate datetime,ProgressHoldWorkload FLOAT(2),Activityincid int )
while @Minval < @Maxval
begin
set @Act_ID = (select activityIncId from #Activities2 where (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 4) OR (ActivityStatusSqlId = 134 AND ActivityStatusIncId = 5) and sno = @Minval) -- In progress or On hold
declare @start_date5 datetime
declare @end_date5 datetime
declare @budget5 float
declare @Datediff5 int
set @start_date5 = (select min(A.startdate) from #Activities2 AS A where activityIncId = @Act_ID )
set @end_date5 = (select max(A.enddate) from #Activities2 AS A where activityIncId = @Act_ID)
set @budget5 = (select budgetedTotalworkload from #Activities2 where activityIncId = @Act_ID )
set @Datediff5 = (select datediff(day,@start_date5,@end_date5)+1)
insert into #WorkloadTMP5
SELECT *,cast(@budget5 as float)/cast(@Datediff5 as float),@Act_ID from eufn_e5_eSM_AS01_ExplodeDates (@start_date5,@end_date5)
------SELECT * FROM #WorkloadTMP4
set @Minval = @Minval+1
end
end
CREATE TABLE #WorkloadTMP_new([Date] DATETIME, TotalWorkload DECIMAL(18,2), approvedworkload DECIMAL(18,2) , draftworkload DECIMAL(18,2), Doneworkload DECIMAL(18,2), ProgressHoldWorkload DECIMAL(18,2))
------INSERT into #WorkloadTMP
select T1.Thedate,T1.WORKLOAD,T2.Approvedworkload,T3.Draftworkload,T4.DoneWorkload, T5.ProgressHoldWorkload
into #WorkloadTMP
from #WorkloadTMP1 T1
Left Join #WorkloadTMP2 T2 on T2.Activityincid = T1.Activityincid and T2.thedate = T1.thedate
Left Join #WorkloadTMP3 T3 on T3.Activityincid = T1.Activityincid and T3.thedate = T1.thedate
Left Join #WorkloadTMP4 T4 on T4.Activityincid = T1.Activityincid and T4.thedate = T1.thedate
Left Join #WorkloadTMP5 T5 on T5.Activityincid = T1.Activityincid and T5.thedate = T1.thedate
------select * from #WorkloadTMP
Drop table #WorkloadTMP1
Drop table #WorkloadTMP2
Drop table #WorkloadTMP3
Drop table #WorkloadTMP4
Drop table #WorkloadTMP5
-----Final SELECT Statement
SELECT * FROM #WorkloadTMP
--Go
December 16, 2013 at 12:58 pm
Looks like logic is coded in a complicated way, too much use ot temp tables. Sorry for not looking in depth but you can create a dynamic query to execute a particular block of SQL statements based on parameter selection.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply