how to get sub totals for scoped sets

Question

Hi All

i have named sets called working days , Non working days , Peaktime  ,Non peakTime , NonPeakTime Non working days .  i have to calculate mesures in all the above sets . but i am not getting subtotals For Peaktime named sets example in one day how many call attempts happened in peak time . i am able to get the result per hour but not showing the sub total . How to get the sub totals on day basis on peal time and non peak time named sets . i want see the aggregated values for eaxmple in one month how many call attempts

CREATE SET CURRENTCUBE.[Non Working Dates]
 AS ORDER(UNION(EXISTS([Date].[Date].CHILDREN, [Date].[Is Weekend].&[Y]),EXISTS([Date].[Date].CHILDREN, [Date].[Is Holiday].&[Y])),[Date].[Date].MEMBERVALUE, ASC);    

CREATE SET CURRENTCUBE.[Working Dates]
 AS EXCEPT(EXISTS([Date].[Date].CHILDREN, [Date].[Is Weekend].&[N]),EXISTS([Date].[Date].CHILDREN, [Date].[Is Holiday].&[Y]));

CREATE SET CURRENTCUBE.[PeakTime]
AS (EXISTS([Time].[Hour].CHILDREN,[Time].[Is Working].&[Y] )*EXISTS([Date].[Date].CHILDREN,[Working Dates]));    

CREATE SET CURRENTCUBE.[NonPeakTime]
AS (EXISTS([Time].[Hour].CHILDREN,[Time].[Is Working].&[N] )*[Working Dates]); 

CREATE SET CURRENTCUBE.[NonPeakTime Non working days]
AS ([Non Working Dates] );    

CREATE MEMBER CURRENTCUBE.[Measures].[MDS Peak Attempts] AS NULL,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'MDS DEPOSIT RESULT';    

SCOPE([Measures].[MDS Peak Attempts],[PeakTime]);  
THIS = [Measures].[CALL ATTEMPT];
END SCOPE;  

CREATE MEMBER CURRENTCUBE.[Measures].[MDS NonPeak Attempts] AS NULL,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'MDS DEPOSIT RESULT';    

SCOPE([Measures].[MDS NonPeak Attempts],[NonPeakTime]);  
THIS = [Measures].[CALL ATTEMPT];
END SCOPE; 
SCOPE([Measures].[MDS NonPeak Attempts],[NonPeakTime Non working days]);  
THIS = [Measures].[CALL ATTEMPT];
END SCOPE;

---

Surendra Thota

Answer 1

Hi Surendra,

I think, the cleanest way to handle this issue is, create more levels in [Date] and [Time] dimensions

a. Create a new  level in  hierarchies in  date dimension

I assume you date dimension have a hierarchy "Calendar" as follows

Year --> Quarter --> Month --> Date

Add one more level between "Month" and "Date" level

Year --> Quarter --> Month --> [Date Type]--> Date

[Date Type] may have two values "Working Dates" and "Non Working Dates"

b.Similarly add a new level in time dimension

Hour --> Minutes

[Hour Type]--> Hour --> Minutes

[Hour Type] may have two values "Peak Hour" and "Non Peak Hour"

I hope this helps,

-Ashim

Note : If this is helpful, Do not forget to mark as "Answered"

Answer 2

Hi Ashim

working days and Non working days are giving expected results as it is related to single dimension . but peak hours and Non peak hours are dependent on time as well as date dimension. example peak hours is working hours of working days only .so its difficult to keep a new level in time dimension

---

Surendra Thota

Answer 3

Hi Ashim

i am getting the peakhours measures but it is not giving total at day level .ie just the total of the peakhours ;easure of the day > how can we get it . as date is another dimension.

---

Surendra Thota