I have a requirement for an overtime report built on a Dynamic Cube. The report need to summarizes overtime hours for the month or year but only if the overtime is on the weekend, during certain shifts and total hours for the day is greater than 8.5. I have a report that will show the correct amount if the day level is displayed on the report. The requirement is to have the report start at the current year and allow the user to drilldown. Here are the data items I have created so far:
Tuple L1 - total(tuple(currentMember([ENT-ETS_DC].[Time (Daily)].[Time (Date)]), [Ot Hrs]) within set [Shift (1,2,4)],[Day of Rest (1,2)])
OT>8.5 - if ([Tuple L1] > 8.5) then ([Tuple L1]) else (0) -- measure on report
Employees - filter(members([ENT-ETS_DC].[EPDW (Hist)].[Manager].[Employee]), [Tuple L1]>=8.5) - rows on report
Current Year - columns on report
Example: If employee works on three shifts that meet criteria in January, 9, 5, 10 hours respectively report should display 19 for January. Report currently displays 24 because January is currentMember in calc.
Thanks,
Brad
The trick here is to always evaluate the measure on the day level, then summarize up to the current level.
Two ideas.
1. Total(measure within set descendants (currentmember(),daylevel))
2. The generate function should help here. You can calculate the tuple for each member on the day level.
Thanks Paul, I will give those a try.