Help required on Dimensional reporting

[ricky]

Hi,

I am working on creating a active report dashboard. It has 2 dimensions and a single fact table.

Dim Global -- Has 6 levels
Dim Date: The usual hierarchy Year --> Quarter --> month --> Week --> Day

It will have relative time periods like:

1) Today|Yesterday
2) Current week | Last Week
3) Current month | Last month
4) Current Quarter | Last Quarter
5) Current Year(YTD)

The report will be based off a Dynamic cube and this is the approach that I have followed.

Today|Yesterday :

Got the current date:

Item(Tail(filter(members(Cube.Dimtime.Htime.LevelDay), Tuple(<fact>, currentmember(Cube.TimeDim.TimeH)) is not null), 1) , 0)

Since creating a cross tab using the Global and dim Time will lead to a cross product, I created Set expressions for all the 6 levels of dim global:

filter(dimglobal.Hier.Level1 , tuple(<Fact>, currentmember(cube.dimglobal.hier), currentmember(cube.dimtime.Htime)) is not null)

to ensure that I am getting the correct fact, the calculation I have used is:

Aggregate(<Fact> within set(Current Day))

When I create a crosstab, I get some blank rows for level1 ie there is not fact present since it has not data available for that day as shown in img 01. Per my understanding these rows should already have been filtered. This prompted me to use a detailed filter Aggregate(<Fact> within set(Current Day)) <> NULL. I know that this is not the recommended approach. Would be great if somebody could provide me an opinion on a better approach. This is what I tried to avoid using the detail filter:

Tried filtering the levels in global hierarchy based on the fact and the current day: 

filter( level1,Tuple(<fact>,currentmember(cube.dimglobal.hier), [Current Day]) <> NULL)     

This is the error that I am getting: XQE error encountered: Unable to convert null to set type, tuple type
Not sure if there is any way to resolve this.