Hello Cognoise
I am currently building a sales report by time (hourly) over a dynamic cube. My issue concerns creating a custom defined chart axis that groups the first seven hours (ie 00:00 AM, 01:00 AM .... 07:00 AM) under ONE custom grouping 'Morning until 07:00 AM' and then leaves the rest of the axis as normal hourly intervals.
eg 'Morning until 07:00 AM' 08:00 AM 09:00 AM..... 15:00 PM
and NOT:
00:00 AM 01:00 AM 02:00 AM 03:00 AM 04:00 AM 05:00 AM 06:00 AM 07:00 AM 08:00 AM 09:00 AM..... 15:00 PM
I can generate the normal axis hourly intervals (eg 08:00 AM 09:00 AM..... 15:00 PM) by simply removing the early morning interval from the full day time interval.
except(
periodsToDate([VC_FCT_CHAN_SALES_DT_AND_FCT_STORE_DT].[Trading AEST Calendar Time of Day Qtr Hr].[AEST Calendar Time - 24Hr> Half 24Hr> Qtr 24Hr].[(All)], parent(#prompt('pAESTTime','token', '[15:30]')#)),
periodsToDate([VC_FCT_CHAN_SALES_DT_AND_FCT_STORE_DT].[Trading AEST Calendar Time of Day Qtr Hr].[AEST Calendar Time - 24Hr> Half 24Hr> Qtr 24Hr].[(All)],
[7:00 AM])
)
However the grouping of the first 7 hours as one time unit 'Morning until 07:00 AM' on the axis seems much harder! Your help would be appreciated as I have spent a day on this (and am newer to the dimensional reporting mode)
Thanks
... if this was a relational report I could just use the if/else clause and test for member inclusion in the morning interval. But as its a dimensional OLAP source I was thinking a set based solution would be required ...