hi,
i’m using the following select case statement for measure obejet. but no idea how to get userresponse values in report side for start date and enddate for previous month or fiscal year. if i run this function of measure object in report user should able to see the previous start date and end date basically.
dbo.ufn_getmanhours(Entity_dt.EntityType,Entity_dt.EntityCode,
CASE
WHEN @Prompt('Enter Period','A',{'Reporting Month','Reporting Fiscal Year'},mono,constrained,,)='Reporting Month' THEN (SELECT case
WHEN datepart(day,getdate()) < 11 then
(
SELECT dbo.SAF_DATE.DateDt from dbo.SAF_DATE
where dbo.SAF_DATE.DayOfMnth= 1
and dbo.SAF_DATE.CalMnthNbr= datepart(month,Dateadd( mm, -2, getdate() ))
AND dbo.SAF_DATE.CalYr= datepart(year,Dateadd( mm, -2, getdate() ))
)
ELSE
(
SELECT dbo.SAF_DATE.DateDt from dbo.SAF_DATE
where dbo.SAF_DATE.DayOfMnth= 1
and dbo.SAF_DATE.CalMnthNbr = datepart(month,Dateadd( mm, -1, getdate() ))
AND dbo.SAF_DATE.CalYr= datepart(year,Dateadd( mm, -1, getdate() ))
)
end)
WHEN @Prompt('Enter Period','A',{'Reporting Month','Reporting Fiscal Year'},mono,constrained,,)='Reporting Fiscal Year' THEN (SELECT Min(DateDt) FROM dbo.SAF_DATE WHERE FiscYr= datepart(year,getdate()))
END,
CASE
WHEN @Prompt('Enter Period','A',{'Reporting Month','Reporting Fiscal Year'},mono,constrained,,)='Reporting Month' THEN (SELECT case
WHEN datepart(day,getdate()) < 11 then
(
SELECT dbo.SAF_DATE.DateDt from dbo.SAF_DATE
where dbo.SAF_DATE.LastDayInMnthInd= 'YES'
and dbo.SAF_DATE.CalMnthNbr= datepart(month,Dateadd( mm, -2, getdate()))
AND dbo.SAF_DATE.CalYr= datepart(year,Dateadd( mm, -2, getdate() ))
)
ELSE
(
SELECT dbo.SAF_DATE.DateDt from dbo.SAF_DATE
where dbo.SAF_DATE.LastDayInMnthInd= 'YES'
and dbo.SAF_DATE.CalMnthNbr = datepart(month,Dateadd( mm, -1, getdate() ))
AND dbo.SAF_DATE.CalYr= datepart(year,Dateadd( mm, -1, getdate() ))
)
end)
WHEN @Prompt('Enter Period','A',{'Reporting Month','Reporting Fiscal Year'},mono,constrained,,)='Reporting Fiscal Year' THEN (SELECT Max(DateDt) FROM dbo.SAF_DATE WHERE FiscYr= datepart(year,getdate()))
END)Thank you
Pv
[Moderator Edit: Added code formatting - Jansi]
sownu (BOB member since 2009-08-27)