Not to beat a dead horse, but this is another SSRS aggregation situation against a cube data source that can be solved by math. This is the third in a series, and I suspect there will be a few more, since each situation is different.
You can get the concept from my previous posts Filter Zeros from a Percent Increase and Aggregating Last Child values. This one is even more complicated, but I’ll give the quick and dirty version.
The client wants a report that allows the user to choose a week, and have report columns for the most recent day in that week (so it will be Saturday for all past weeks, and today for the current week), for the week-to-date, and for the full week. This get tricky because some of the values in these sets of columns include most recent day Last Year, and week-to-date Last Year. I can’t filter the data set because some of the columns need the full week values for Budget and for Last Year, even if we’re only part way through the week. Below is a report with the weekdays toggled open so you can see exactly how the values are summing up for the groups. Thursday is the most recent day in the week, so it is the only one included in the Thursday totals. Sunday to Thursday are included in the Week-To-Date totals.
To accomplish this I use math to zero out any unwanted values. The math itself takes some thinking, and every situation is different. I put the values in a spreadsheet and play with them until I get the right combination. Here is how I solved this one.
CREATE TWO FACTORS – Week-To-Date and Most Recent Day
The end goal is to create two multipliers, one for Most Recent Day, and one for Week To Date. You will be able to use these multipliers on any measure in your data set to filter out unwanted values.
The user can select a single week for the report. The report dataset must contain the integer value of the Week Number out of the parameter select by the user. **Note: this solution only works if the weeks in your cube are numbered uniquely and consecutively over time. So the first week in your Date table should be week one (or whatever starting point you choose), and each week number thereafter should be greater than the last.
1. CREATE NEW FIELDS IN THE REPORT DATASET PROPERTIES
Double click on your dataset and go to the Fields tab in the Dataset Properties. Click on the Add button and select Calculated Field.
2. FIRST WE FIND THE MAXIMUM DAY FOR THE SELECTED WEEK
Add the following Calculated Fields. The end goal is to create two multipliers, one for Most Recent Day, and one for Week To Date. You will be able to use these multipliers on any measure in your data set to filter out unwanted values. See Excel screenshot below for examples broken down for current week and previous week.
1. NowDay – This gives me the day number of today from 1 to 7
=DatePart("w",Now(),FirstDayOfWeek.Sunday)
2. CurrentWk – There is a hidden parameter in the report defaulting to whatever the week number is of today. Example today could fall in week # 319 according to my DIM_Time table. **
=Replace(Mid(Parameters!CurrentWeekID.Value,InStr(Parameters!CurrentWeekID.Value,"].&[")+4,10),"]","")
3. Floor – The floor returns either a 1 or a 0. It returns a 1 if the week selected is the current week, otherwise it returns 0.
=FLOOR ( Fields!Fiscal_Week_ID.Value/Fields!CurrentWk.Value )
4. Ceiling – The ceiling returns either a 7 or a 0. It returns a 0 if the week selected is the current week, otherwise it returns a 7. I use a +1 to prevent a divide by zero.
=(CEILING( (Fields!CurrentWk.Value-Fields!Fiscal_Week_ID.Value) / (ABS(Fields!CurrentWk.Value-Fields!Fiscal_Week_ID.Value)+1) ))*7
5. MaxDayTW –This is the maximum day number (1 to 7) for the week selected. If it is the current week it will be whatever number day of the week today is. If is a past week it will return a 7. So if today is Wednesday, and my week starts on Sunday, then MaxDayTW will be 4. The way it works is if the current week is selected then the Ceiling will be zero, and we will keep the NowDay value. If it is a past week, then the NowDay value will be zero’d out and the Ceiling will be 7, making it the MaxDayTW = 7, the last day of the week.
=(Fields!NowDay.Value*Fields!Floor.Value) + Fields!Ceiling.Value
6. MaxDayTW_Name – This gives the weekday name for the MaxDayTW that we calculated – Monday, Tuesday, etc. to be used in the column header of the report.
=WeekdayName(Fields!MaxDayTW.Value)
3. THEN WE CREATE THE MULTIPLIERS
Add the following calculated fields:
7. WeekToDate_multiplier – This will return a 1 or a 0. It will be a 1 for all days which are less than or equal to the MaxDayTW, and zero out any unwanted days in the week-to-date.
=CEILING ( FLOOR ( Fields!MaxDayTW.Value / Fields!Day_Of_Week_ID.Value ) / ( Fields!MaxDayTW.Value / Fields!Day_Of_Week_ID.Value ) )
8. MostRecentDay_multiplier – This will return a 1 or a 0. It will be 1 for the most recent day of the week and a 0 for any other days of the week.
=FLOOR (Fields!Day_Of_Week_ID.Value /Fields!MaxDayTW.Value)*Fields!WeekToDate_multiplier.Value
4. USE THE MULTIPLIERS IN YOUR REPORT
You can either create additional calculated fields in your dataset, or you can just apply the multipliers directly in your report. I prefer to use them in the report so I don’t have to track back to multiple places to find out exactly how a calculation is working. Be sure to use the multiplier BEFORE aggregating, since you want to multiply the individual rows, not the aggregate.
Examples:
1. To get Most Recent Day Sales $ use this calculation:
=Sum(Fields!Sales_Dollars.Value*Fields!MostRecentDay_multiplier.Value
2. To get Week To Date Sales $ for Last Year use this calculation:
=Sum(Fields!Sls_Dollars_LY.Value*Fields!WeekToDate_multiplier.Value)
Here are the results. The weekdays are toggled open so you can see exactly how the values are summing up for the groups. Thursday is the most recent day in the week, so it is the only one included in the Thursday totals. Sunday to Thursday are included in the Week-To-Date totals.
This is not a simple solution, but once it is in place it is very simple to use. It gets around all the complexities of having to use Custom Code to sum up each different measure for each different group in your report. It also allows you to have repeating headers on each page rather than having to hold your table within a List to make the Custom Code work properly in all renderings. Get the two multipliers right, and you can aggregate as many measures as you like without any hassle at all.
** It’s worth repeating that this solution only works if the weeks in your cube are numbered uniquely and consecutively over time. So the first week in your Date table should be week one (or whatever starting point you choose), and each week number thereafter should be greater than the last.