Need to Slice data based on Count of Boolean Flags (Same Dimension, No Hierarchy)

Question

I need to slice the data based on Multiple Flags on the Transactional record. The Flags are on the same dimension but not in the same hierarchy.

Below is the sample transnational data and corresponding fact table that we have generated. I need to write MDX query in order to generate report as whoen in the Expected Report below.

Here PrintMail, App Notifications, SMS and Email are various types of communication channels through with the message was delivered. Some of the messages are delivered through multiple channels.

Transactional Table

Date            MessageCount    PrintMail       AppNotification SMS     Email   
1/1/2019        1               1               1                               
1/1/2019        1               1               1                               
1/1/2019        1                                               1       1       
2/1/2019        1               1               1                               
2/1/2019        1                               1

Fact Table

Date            MessageCount    PrintMail       AppNotification SMS     Email   
1/1/2019        3               2               2               1       1       
2/1/2019        2               1               2               0       0

Expected Report

                       All     1/1/2019 2/1/2019       
Message Count           5       3        2              
PrintMail               3       2        1              
AppNotification         2       1        1              
SMS                     3       2        1              
Email                   1       1        0

My question is: how can I show various channels are rows here? I am not sure if this will require a new dimension and if yes, how would that look like.

score 1 · Accepted Answer · answered Jan 25 '19 at 15:35

1

By the looks of your Fact table the diffrent channels are your facts. If that is the case then you dont need a new measure. All you need to do is in your MDX query put the measures on row axis and the date on column axis. The Query should look like

Select {[DimDate].[Date].members} on columns,
{[Measures].[MessageCount],[Measures].[PrintMail],
[Measures].[AppNotification],[Measures].[SMS],[Measures].[Email]} 
on rows
from [YourCube]

answered Jan 25 '19 at 15:35

MoazRub

2,881
2
10
20

Thanks a million, @MoazRub. You saved my day. I knew I was doing something silly mistake and couldn't quite get the query right. Thanks. – Kritul Rathod Jan 25 '19 at 16:06
1

@Kritul Rathod you are most welcome. I am glad I was able to help. – MoazRub Jan 25 '19 at 16:21
I need some more detailed discussion on similar lines? Is there a way to contact you? – Kritul Rathod Jun 26 '19 at 00:56
moazrubku@yahoo.com you can email me at this address – MoazRub Jun 26 '19 at 06:54

Need to Slice data based on Count of Boolean Flags (Same Dimension, No Hierarchy)

1 Answers1