Filter multidimensional OLAP cube in SSRS report retrieving parameter values using a store procedure

Question

I have created a SSRS report that shown data from an OLAP cube. In the report there are some parameters and a lot of these have "available" and "default" values retrieved using a MDX query.

In the report there are also these parameters: @Year,@Month,@Day

I calculate these parameters, using yesterday as date (NOW()-1), retrieving values from a MDX query.

WITH  MEMBER [Measures].[Full Date] as 'NOW()-1'
MEMBER [Measures].[Day] as 'DAY([Full Date])'
MEMBER [Measures].[Month] as 'MONTH([Full Date])'
MEMBER [Measures].[Year] as 'YEAR([Full Date])'
SELECT
   {[Full Date],[Day],[Month],[Year]} ON COLUMNS
FROM [CallCenter]

I need to replace the "NOW () - 1" date with the previous working day.

WITH  MEMBER [Measures].[Full Date] as '[previous working day]'

In the datawarehouse I have created a store procedure that calculate the previous working day.

DECLARE @yesterday date 
SET @yesterday = DATEADD(day,-1,@date)

select
    today = @date
    ,todayname = DATENAME(WEEKDAY,@date)
    ,yesterday = @yesterday
    ,yesterdayname = DATENAME(WEEKDAY,@yesterday)   
    ,day = CASE
            WHEN DATENAME(WEEKDAY,@yesterday) = 'Sunday'
            THEN DATEADD(day,-2,@yesterday)
            WHEN DATENAME(WEEKDAY,@yesterday) = 'Saturday'
            THEN DATEADD(day,-1,@yesterday)
            -- **************************
            -- LUNEDI' DELL'ANGELO
            -- **************************
            WHEN @yesterday = DATEADD(day,1,intern.GetEasterSunday (Year(@yesterday)))
            THEN DATEADD(day,-3,@yesterday)
            -- **************************
            -- CAPODANNO
            -- **************************
            WHEN MONTH(@yesterday) = 1 and DAY(@yesterday) = 1
            THEN
                CASE    
                    WHEN DATENAME(weekday,@yesterday) = 'Monday'
                    THEN DATEADD(day,-3,@yesterday)
                    WHEN DATENAME(weekday,@yesterday) = 'Sunday'
                    THEN DATEADD(day,-2,@yesterday)                         
                    ELSE DATEADD(day,-1,@yesterday)
                END
            -- **************************
            -- EPIFANIA
            -- **************************       
            WHEN MONTH(@yesterday) = 6 and DAY(@yesterday) = 1
            THEN
                CASE    
                    WHEN DATENAME(weekday,@yesterday) = 'Monday'
                    THEN DATEADD(day,-3,@yesterday)
                    WHEN DATENAME(weekday,@yesterday) = 'Sunday'
                    THEN DATEADD(day,-2,@yesterday)                             
                    ELSE  DATEADD(day,-1,@yesterday)
                END
            -- **************************
            -- FESTA DELLA LIBERAZIONE
            -- **************************
            WHEN MONTH(@yesterday) = 4 and DAY(@yesterday) = 25
            THEN
                CASE    
                    WHEN DATENAME(weekday,@yesterday) = 'Monday'
                    THEN DATEADD(day,-3,@yesterday)
                    WHEN DATENAME(weekday,@yesterday) = 'Sunday'
                    THEN DATEADD(day,-2,@yesterday)                         
                    ELSE  DATEADD(day,-1,@yesterday)    
                END
            -- **************************
            -- FESTA DEI LAVORATORI
            -- **************************
            WHEN MONTH(@yesterday) = 5 and DAY(@yesterday) = 1
            THEN
                CASE    
                    WHEN DATENAME(weekday,@yesterday) = 'Monday'
                    THEN DATEADD(day,-3,@yesterday)
                    WHEN DATENAME(weekday,@yesterday) = 'Sunday'
                    THEN DATEADD(day,-2,@yesterday)                         
                    ELSE DATEADD(day,-1,@yesterday)
                END
            -- **************************
            -- FESTA DELLA REPUBBLICA
            -- **************************
            WHEN MONTH(@yesterday) = 6 and DAY(@yesterday) = 2
            THEN
                CASE    
                    WHEN DATENAME(weekday,@yesterday) = 'Monday'
                    THEN DATEADD(day,-3,@yesterday)
                    WHEN DATENAME(weekday,@yesterday) = 'Sunday'
                    THEN DATEADD(day,-2,@yesterday)                         
                    ELSE  DATEADD(day,-1,@yesterday)
                END
            -- **************************
            -- FERRAGOSTO
            -- **************************
            WHEN MONTH(@yesterday) = 8 and DAY(@yesterday) = 15
            THEN
                CASE    
                    WHEN DATENAME(weekday,@yesterday) = 'Monday'
                    THEN DATEADD(day,-3,@yesterday)
                    WHEN DATENAME(weekday,@yesterday) = 'Sunday'
                    THEN DATEADD(day,-2,@yesterday)                         
                    ELSE  DATEADD(day,-1,@yesterday)
                END
            -- **************************
            -- FESTA DI OGNISSANTI
            -- **************************
            WHEN MONTH(@yesterday) = 11 and DAY(@yesterday) = 1
            THEN
                CASE    
                    WHEN DATENAME(weekday,@yesterday) = 'Monday'
                    THEN DATEADD(day,-3,@yesterday)
                    WHEN DATENAME(weekday,@yesterday) = 'Sunday'
                    THEN DATEADD(day,-2,@yesterday)                         
                    ELSE  DATEADD(day,-1,@yesterday)
                END
            -- **************************
            -- IMMACOLATA CONCEZIONE
            -- **************************
            WHEN MONTH(@yesterday) = 12 and DAY(@yesterday) = 8
            THEN
                CASE    
                    WHEN DATENAME(weekday,@yesterday) = 'Monday'
                    THEN DATEADD(day,-3,@yesterday)
                    WHEN DATENAME(weekday,@yesterday) = 'Sunday'
                    THEN DATEADD(day,-2,@yesterday)                         
                    ELSE  DATEADD(day,-1,@yesterday)
                END
            -- **************************
            -- NATALE
            -- **************************
            WHEN MONTH(@yesterday) = 12 and DAY(@yesterday) = 25
            THEN
                CASE    
                    WHEN DATENAME(weekday,@yesterday) = 'Monday'
                    THEN DATEADD(day,-3,@yesterday)
                    WHEN DATENAME(weekday,@yesterday) = 'Sunday'
                    THEN DATEADD(day,-2,@yesterday)                         
                    ELSE DATEADD(day,-1,@yesterday)     
                END
            -- **************************
            -- SANTO STEFANO
            -- **************************
            WHEN MONTH(@yesterday) = 12 and DAY(@yesterday) = 26
            THEN
                CASE    
                    WHEN DATENAME(weekday,@yesterday) = 'Monday'
                    THEN DATEADD(day,-3,@yesterday)
                    WHEN DATENAME(weekday,@yesterday) = 'Tuesday'
                    THEN DATEADD(day,-4,@yesterday)                         
                    ELSE DATEADD(day,-2,@yesterday)
                END
            ELSE @yesterday
        END

I'm not able to replace the "NOW()-1" date with the result of the store procedure.

I need some help. Thank.

MoazRub MoazRub · Accepted Answer · 2019-06-04T17:59:25

You can use the following code

member measures.t 
as 
case 
when  datepart('w', Now()) = 1 then Now()-2 //Sunday is 1 by default
when datepart('w', Now()) = 7 then Now()-1  //Saturday is 7 by default
else 
Now()
end

However if you want to implement the same logic to a dimension take a look at the below sample

with 
member measures.t 
as 
case 
when  datepart('w', [date].[date].currentmember.name) = 1 then dateadd('d',-2,[date].[date].currentmember.name)
when datepart('w', [date].[date].currentmember.name) = 7 then dateadd('d',-1,[date].[date].currentmember.name)
else 
cdate([date].[date].currentmember.name)
end 
select  measures.t 
on 0, 
[Date].[Date].[Date]
on 1
from [Adventure Works]
where 
([Date].[Calendar Year].&[2012],[Date].[Month of Year].&[2])

Result (Take a look at Feb 11, and 12)

Filter multidimensional OLAP cube in SSRS report retrieving parameter values using a store procedure

1 Answers