Saturday, August 30, 2014

Shared Dataset for SSRS - Data Defaults

I have a shared dataset in an SSRS reporting project that is used to create some date defaults to use as parameters in reports. Here is the query:



SELECT 'Calendar ' + CONVERT(varchar(4), YEAR(CurrentDate)) AS CurrentCalendarYear,
'[Time].[Year - Month].[Year].&[' + CONVERT(varchar(4), YEAR(CurrentDate)) + '-01-01T00:00:00]' AS CurrentCalendarYearMDX,
'[Time].[Year - Half Year - Quarter - Month - Date].[Year].&[' + CONVERT(varchar(4), YEAR(CurrentDate)) + '-01-01T00:00:00]' AS CurrentCalendarYearExMDX,
CAST(DATENAME(month, CurrentDate) AS varchar(10)) + ' ' + CONVERT(varchar(4), YEAR(CurrentDate)) AS CurrentMonth,
'[Time].[Year - Month].[Month].&[' + CONVERT(varchar(4), YEAR(CurrentDate)) + '-' + RIGHT('0' + CONVERT(varchar(2), MONTH(CurrentDate)), 2) + '-01T00:00:00]' AS CurrentMonthMDX,
'[Time].[Year - Half Year - Quarter - Month - Date].[Month].&[' + CONVERT(varchar(4), YEAR(CurrentDate)) + '-' + RIGHT('0' + CONVERT(varchar(2), MONTH(CurrentDate)), 2) + '-01T00:00:00]' AS CurrentMonthExMDX,
'Calendar ' + CAST(YEAR(LastMonth) AS varchar(5)) AS PreviousMonthCalendarYear,
'[Time].[Year - Month].[Year].&[' + CONVERT(varchar(4), YEAR(LastMonth)) + '-01-01T00:00:00]' AS PreviousMonthCalendarYearMDX,
'[Time].[Year - Half Year - Quarter - Month - Date].[Year].&[' + CONVERT(varchar(4), YEAR(LastMonth)) + '-01-01T00:00:00]' AS PreviousMonthCalendarYearExMDX,
CAST(DATENAME(month, LastMonth) AS varchar(10)) + ' ' + CAST(YEAR(LastMonth) AS varchar(5)) AS PreviousMonth,
'[Time].[Year - Month].[Month].&[' + CONVERT(varchar(4), YEAR(LastMonth)) + '-' + RIGHT('0' + CONVERT(varchar(2), MONTH(LastMonth)), 2) + '-01T00:00:00]' AS PreviousMonthMDX,
'[Time].[Year - Half Year - Quarter - Month - Date].[Month].&[' + CONVERT(varchar(4), YEAR(LastMonth)) + '-' + RIGHT('0' + CONVERT(varchar(2), MONTH(LastMonth)), 2) + '-01T00:00:00]' AS PreviousMonthExMDX
FROM (SELECT GETDATE() AS CurrentDate, DATEADD(month, - 1, GETDATE()) AS LastMonth) AS d

What I'd also like to do is update the query to return the current week and the previous week. AS far as I can tell, there is no function in T-SQL to return week - it apparently takes some formatting.


If someone could provide some insight in to how to make this work, it would be greatly appreciated. It needs to be in the format of 27-July-2014.


Thanks!




A. M. Robinson


No comments:

Post a Comment