How To Default SSRS Parameter to Current Month using an SSAS Data Source

When dealing with report parameters that involve time, I usually like to default the report to the current unit of time used by the report, often month. When a user views the report in a particular month the parameter will always default to that month, saving the user from having to select it every time.

There are two elements to making this happen. One is to define the current month in your Time dimension, and the other is to build the dataset into your report to feed the default for the Time parameters.

1. Define current month in the SSAS Time dimension

I know there is Time functionality built into SSAS cubes, but I still like the flexibility of building attributes into my DIM_Date table in the data warehouse. In this way it can be used in both cube and SQL queries alike. I won’t go into details about how the Dim_Date table is created as that is another subject. The DIM_Date table is rebuilt nightly and an attribute called MonthPeriod identifies whether the date is in Current Month, Previous Month, etc. You can label any months you like, with your own descriptions. Some examples are below.

The important thing about this attribute is to ensure that MonthPeriodID for the current month is always 0, and the MonthPeriodID for other months is relative to that. So ‘Current Month –1’ is 1, ‘Current Month –2’ will be 2, etc. This way you can build logic into your reports which relies on the fact that, for example, last month has a MonthPeriodID of 1. It is critically important that the underlying ids belonging to each description are always the same. If Current Month is 0 today and changes to 10 tomorrow, the defaults in your reports will not work. SSRS stores the MDX tuple associated with the description and not the actual description. So when you select ‘Current Month’ in step 3 below, SSRS is storing [TIME].[Month Period].&[0] and not the ‘Current Month’ label.

Once you have this attribute defined in your DIM_Date table and it is being updated nightly, add it to the SSAS cube for use in your reports.

2. Build the default dataset into your report to feed the SSRS Parameter.

Background:

When you build the main dataset for the report and add parameters in your dataset,

SSRS automatically creates the dataset for the Available Values for the parameter. You can view these hidden datasets used to feed Available Values by right clicking on [Datasets] in the [Report Data] window in SSRS and selecting [Show Hidden Datasets].

You can view and/or edit any of these hidden datasets.

I don’t recommend editing the hidden datasets, since they are created and used by SSRS by default and your changes can sometimes be overwritten if you add additional datasets using the same parameters. If you wish to customize the Available Values you are better off creating a new dataset with a different name specific to that purpose and then changing Report Parameter Properties to use the new dataset to retrieve the Available Values.

Building the Default Dataset

For this particular report the user can choose Year and then Month Of Year. The same dataset can be used to feed both the default current year and the current month.

1. Right click on Datasets in the Report Data window and select Add Dataset.

2. Select the Radio Button ‘Use a dataset embedded in my report’. I like to use Shared Datasets for this type of thing, but for some reason shared datasets used for defaults with an SSAS data source don’t seem to work when published to SharePoint. So for now just embed it in the report. Select (or define) the cube you are using as your Data source, and click on the Query Designer button.

3. From the TIME dimension drag the ‘Month Period’ attribute defined in Part 1 to the filter area of the Query Designer. Click in the Filter Expression and select ‘Current Month’. Click OK.

4. Drag Year and Month of Year onto the Query design page. Right click on the design page and select ‘Include Empty Cells’ so the current attributes will appear when the query is executed.

5. Right click in the Calculated Members area and select ‘New Calculated Member’. From the Metadata pane drag the Year attribute from the TIME dimension into the Expression pane. Add to the end of the expression “.CurrentMember.UniqueName”. Give the Calculated Member a name of ‘YearValue’. Click OK.

6. Right click on the new calculated member called YearValue and select Add to Query.

7. Add another calculated member following steps 5 & 6 called MonthOfYearValue.

8. Your dataset now contains the Label and the underlying MDX tuple needed to feed the default time values to your report parameter.

9. Click OK to close out of the Query Designer. Give the dataset a name of DefaultYearMonth. Click OK to save the dataset.

10. Double click the TIMEYear parameter in the Paramaters folder of the Report Data window to edit the Report Parameter Properties. Select the Default Values option. Choose the ‘Get values from a query’ radio button. In the dataset drop down choose the DefaultYearMonth dataset you created. In the Value field dropdown select YearValue.

11. Do the same as Step 10 for the TIMEMonthOfYear parameter. Select DefaultYearMonth as the dataset, and MonthOfYearValue for the Value field.

When you preview your report, the parameters will now default to the current year and month. Going forward the default for the parameter will roll over to whichever month is current. Users appreciate not having to choose the current month every time they look at a report.

How to Default to ALL in an SSRS Multi-select Parameter

This seems like it should be easy, but SSRS has nothing built in to allow you to do this. I searched an couldn’t come up with a solution that fit all my requirements for a report. There are ways to default the selection list to every value in the list, but I needed to add the option of ALL to my parameter list, default to ALL, and then most importantly have ALL show up in my header instead of showing every item in the list. I was determined to find a way, and here is how I did it.

In this example I would like to allow the report to default to ALL Layout Codes, rather than selecting every Layout Code in the list. there is already a multi-select parameter in the report called Layout_Code, and the main query filters where Layout_Code IN (@Layout_Code)

1. ADD ‘ ALL’ TO YOUR PARAMETER QUERY

The first thing to do is add the value ‘ALL’ to your parameter query. You’ll want it show up at the top of your select list. A simple way to do this is to put a space before the A to make it sort to the top.

SELECT DISTINCT Layout_Code
FROM Your_Table
UNION
SELECT ‘ ALL’ AS Layout_Code
ORDER BY Layout_Code

2. EDIT YOUR MAIN QUERY TO USE BOTH PARAMETERS

Edit the where clause in your main query. If you are using Query Designer in Text mode, simply add this statement. Be sure to use outer brackets to contain both statements if you have other items in your where clause.

WHERE (‘ ALL’ IN (@Layout_Code)) OR (Layout_Code IN (@Layout_Code)) )

If you have multiple parameters in your main query are using Query Designer in the View mode rather than Edit Text mode, your WHERE clause will have to return all combinations using AND and OR. Let Query Designer build your query for you by using outer brackets and replace:

(Layout_Code IN (@Layout_Code))

with

( (‘ ALL’ IN (@Layout_Code)) OR (Layout_Code IN (@Layout_Code)) )

I suggest you have your WHERE clause already complete before adding the OR, since the results can get confusing.

3. CASCADING PARAMETERS WITH ‘ ALL’

You can use this method for cascading parameters where multiple parameters have the ALL value added. You’ll need to add the OR clause to your underlying parameter queries where applicable.

For example, if you have a parameter preceding the Layout_Code parameter called Layout_Group you will follow the same steps above, plus an additional step to edit the Layout_Code dataset query.

Add ‘ ALL’ to your Layout_Group parameter with a Union clause and add an order by
Edit your main query where clause to use @TopLayoutCode . Adjust the AND/OR in the WHERE clause accordingly.
Edit your cascaded parameter query, in this case Layout_Code, to OR in the where clause. Adjust the AND/OR accordingly.

Your main query where clause will look like this in the Query Designer View mode:

WHERE �
(‘ ALL’ IN (@Current_Layout_Group)) AND (‘ ALL’ IN (@Layout_Code)) OR
(‘ ALL’ IN (@Current_Layout_Group)) AND (Layout_Code IN (@Layout_Code)) OR
(‘ ALL’ IN (@Layout_Code)) AND (Layout_Group_Current IN (@Current_Layout_Group)) OR
(Layout_Code IN (@Layout_Code)) AND (Layout_Group_Current IN (@Current_Layout_Group))

Your Layout_Code query will look like this

SELECT DISTINCT Layout_Code
FROM         Your_Table
WHERE     (Layout_Group_Current IN (@Current_Layout_Group)) OR (‘ ALL’ IN (@Current_Layout_Group))UNION
SELECT     ‘ ALL’ AS Layout_Code
ORDER BY Layout_Code

4. SET YOUR PARAMETER DEFAULTS TO ALL

Set the default value for each of your parameters, Layout_Code and Layout_Group, to the value ALL. Be sure to put a space before the A if you are using this method to sort your list in Step 1.

How to Create an Inventory Aging Report from a Cube Data source

This is the fourth in a series of blogs about how to use math to filter out values from an SSAS dataset, and then create aggregations in a report. You want to filter out some values from a report column and then sum them, but you can’t use an IIF statement because it can’t be aggregated. Related blogs : Filter Most-Recent-Day, Week-To-Date and Full Week, Filter from Percentage where Last Year = Zero, and Aggregate Last Child

In this situation the client would like an Inventory Aging report. The report needs to include columns for inventory received 0-3 months, 4-6 months, 7-9 months and 10-12 months ago. The user can choose a Fiscal Week for the On Hand Inventory, and the report must calculate the aging based on the Last Receipt Date, which is built into the Inventory On Hand table. This seems simple enough until you get into the details.

The end results should look like this:

1. CUBE MUST HAVE TWO DIFFERENT TIME DIMENSIONS

The user must be able to select the week of the On Hand Inventory, and the report must render the aging based on Receipt Date. So two different date fields are required in your Inventory FACT table – Inventory Date and Receipt Date, and two different time dimensions should be created from these.

2. CUBE DIMENSION NEEDS TWO WAYS TO ACCESS FISCAL MONTH ID

The next thing you need is a dataset with the right fields in it to allow you calculate the aging. You’ll need the Fiscal Month ID of the selected Inventory Date as well as the Fiscal Month ID of the Receipt Dates, so you can calculate how many months apart they are for the aging. Unfortunately you can’t include two fields in your dataset with the same name, which is what would happen if I just pulled in each of the Fiscal_Month_ID fields from the two Time dimensions.

If I try to build a calculated field in order to rename one of the fields I still need something from the Time dimension that will populate the CURRENTMEMBER aspect. So if I built a calculated field like this [TIME OH Last Receipt Date].[Fiscal Month].CURRENTMEMBER.UNIQUENAME, it will return [TIME OH Last Receipt Date].[Fiscal Month].[All] unless I pull in the Fiscal Month ID field to give the CURRENTMEMBER it’s context. This will mean two fields with the same name again, and the query will not accept it.

I worked around this by including Fiscal_Month_ID AND Fiscal_Month in my Time dimensions, with Fiscal_Month using the Fiscal_Month_ID as it’s key. This gives me two ways to access the Fiscal Month ID, one from each Time dimension.

3. REPORT DATASET NEED CORRECT FIELDS TO YIELD DESIRED RESULTS

In the Dataset Query I included Fiscal_Month_ID from the OH Inventory Time dimension and Fiscal_Month from the Last Receipt Date Time dimension. Then I added a calculated field called ReceiptMonth to my query which yields the tuple showing the underlying Fiscal Month ID.

[TIME OH Last Receipt Date].[Fiscal Month].CURRENTMEMBER.UNIQUENAME

I will reference this field to find the number of months difference between the user Selected Month and the Last Receipt Date.

4. ADD CACULATED FIELDS

Right click on your Dataset and select Dataset Properties. Go to the Fields selection and add the following calculated fields:

1. ReceiptMonthID – This pulls the Month ID out of the tuple. My month IDs are in the form of 201001 for example for January 2010.

=Mid(Fields!RecepitMonth.Value,InStr(Fields!RecepitMonth.Value,"].&[")+4,6)

2. MonthDiff – Because the Month IDs are in the form of 201001, they need to be turned into dates to calculate the number of months between them. The +1 will prevent division by zero in the next calculations.

=DateDiff("M",
CDate(RIGHT(Fields!ReceiptMonthID.Value,2)+"-01-"+LEFT(Fields!ReceiptMonthID.Value,4)),
CDate(RIGHT(Fields!Fiscal_Month_ID.Value,2)+"-01-"+LEFT(Fields!Fiscal_Month_ID.Value,4)))+1

3. ThreeMonths – This actually included 4 months, the current month plus the previous 3 months. It returns a 1 if the MonthDiff between 1 and 4, otherwise it returns a 0.

=CEILING(FLOOR(4/Fields!MonthDiff.Value)/(4/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value

I take the Floor of 4/MonthDiff to get a positive value if the MonthDiff <= 4. Then I divide it by (4/MonthDiff) to get a value of between 0 and 1. And I take the Ceiling of this to end up with either a 1 or a zero.

4. SixMonths – This includes the current month plus the previous 6 months. It returns a 1 if the MonthDiff between 1 and 6, otherwise it returns a 0.

=CEILING(FLOOR(7/Fields!MonthDiff.Value)/(7/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value

5. NineMonths – This includes the current month plus the previous 9 months. It returns a 1 if the MonthDiff between 1 and 9, otherwise it returns a 0.

=CEILING(FLOOR(10/Fields!MonthDiff.Value)/(10/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value

6. TwelveMonths – This includes the current month plus the previous 12 months. It returns a 1 if the MonthDiff between 1 and 12, otherwise it returns a 0.

=CEILING(FLOOR(13/Fields!MonthDiff.Value)/(13/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value

5. ADD A DATASET FILTER TO REMOVE FUTURE MONTHS

Right click on the Dataset and go to the Filters tab. Add this filter. Be sure to select the data type of Integer or you will get an error.

6. DO A BIT OF MATH IN THE REPORT TO USE THE CALCULATED FIELDS

0 –3 MONTHS =Sum(Fields!ThreeMonths.Value)

4 – 6 MONTHS =SUM(Fields!SixMonths.Value)-SUM(Fields!ThreeMonths.Value)

7 – 9 MONTHS =SUM(Fields!NineMonths.Value)-SUM(Fields!SixMonths.Value)

10 – 12 MONTHS =SUM(Fields!TwevleMonths.Value)-SUM(Fields!NineMonths.Value)

OLDER THAN 12 MONTHS =SUM(Fields!Inv_Dollars.Value)-SUM(Fields!TwevleMonths.Value)

This is just junk data, but you can get the idea of how the report slots the inventory into the right aging based on the MonthDiff.

Now hide the details to show the correct aggregates for your report.

This is not as complex as some situations, but it’s nice to be able to filter out values from your query and aggregate the results without having to use custom code for to do the aggregation for every value that has an Iif statement. I find this much cleaner, once you get the math right.

How to build a Weekly Report with Most-Recent-Day and Week-To-Date values Filtered correctly for Last Year

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.

How to Filter out Last Year Value from a Percent Increase aggregation when the Last Year value is less than or equal to Zero

This is a sister solution to my last post which describes how to use math to ignore unwanted values in a cube query for an SSRS report, but still be able to aggregate the resulting values. IIF statements can’t be aggregated, and aggregations can’t be nested, but math can always be aggregated in SSRS.

A common requirement is to calculate a Percent Increase over Last Year (LY). The Percent Increase calculation is:

=(This Year – Last Year) / Last Year

But what if you want to ignore all values in a row where LY <= 0? You can hide the detail rows using an IIF statement in your report, but to sum and calculate the percentage at a group level all values will be summed. If this Year (TY) is more than zero it will be included in the % calculation, even though LY was less than or equal to zero. And if LY is less than zero it will also be included in the sum. This will skew the results and not give the desired results. For example:

The basic premise is that you want to zero out LY and TY in your calculation if LY <= 0. Here’s how.

1. ADD A CALCULATED FIELD TO YOUR DATASET

Add a calculated field to your Dataset Query called “LY_Inc_Multiple”. Right click on the report Dataset, select Dataset Properties, Select Fields, click on the Add button and select Calculated Field.

2. WRITE YOUR CALCULATION

We want to create a calculation that will produce either a 1 if LY is positive or a 0 if LY is zero or negative. We can then use this to multiply the values in the aggregate percent calculation for the Group to exclude any unwanted values from the result. Here I will divide LY by the absolute value of itself, plus one. The ‘plus one’ is to prevent division by zero if LY is zero. Take the Ceiling of this calculation will give you a result of either 1 or 0.

Click on the function button in the Field Source of your new LY_Inc_Multiple calculation. Enter the following:

=(Ceiling(Fields!LY.Value/(ABS(Fields!LY.Value)+1)))

Here is how the calculation works, broken down step by step

3. USE YOUR CALCULATION IN THE REPORT

You can then go ahead and multiply your Increase Percent variables to zero out any unwanted values. Be sure to use the LY_Inc_Multiple in your statement BEFORE summing.

For clarity, your calculation is this:

=(LY_Inc_Multiple*(TY-LY))/(LY_Inc_Multiple*LY)

In your report at the group level it manifests as this :

=Iif(Sum(Fields!LY_Inc_Multiple.Value*Fields!LY.Value)=0,"",((SUM(Fields!LY_Inc_Multiple.Value*(Fields!TY.Value-Fields!LY.Value))/Sum(Fields!LY_Inc_Multiple.Value*Fields!SunLY.Value))))

The IIF statement will hide any division by zero.

That’s basically it. Each situation you come across will be slightly different, and you’ll need to think about the math you need for your particular situation. It’s brain teaser for sure, but once you get the hang of it it can make report building much simpler. You can avoid custom code solutions which have their own headaches, and you can control your summing and grouping without any funkiness.

SSRS – Aggregate LAST YTD or LAST CHILD Value in an SSAS query

Trying to aggregate an aggregate is not possible in SSRS. You can write custom code to capture the running total of values in a report, which works, but can have unexpected behaviour when groups in your report change. It also prevents you from using some other SSRS functionality, for example repeating column headers on a page.

Here is an example of a solution that works using math, which doesn’t prohibit you from using any functionality, since SSRS has no trouble aggregating math equations. This solution has many applications in filtering out unwanted values in a row. I will show you an example where only the most recent (LAST) value of YTD is wanted for each group of rows in a query.

1. In my cube, the YTD calculation requires a member of the “Week Period To Date” hierarchy to be selected.

Aggregate
(
PeriodsToDate
(
[TIME].[Week Period To Date].[Fiscal Year],
[TIME].[Week Period To Date].CurrentMember
),
[Measures].[Sales Dollars]
)

2. In my report, the user can select a range of weeks. So I include the Week ID from the “Week Period To Date” hierarchy in the query in order to return results for YTD. Notice that the YTD Sales grow incrementally each week.

I can sum the Sales Units in the report, and my report Groups will capture the correct value when using Sum. But to aggregate YTD across groups I want to capture only the LAST value. In my detail I can select the last value for the detail group, but now I want to SUM these LAST values for the parent group. SSRS gives an error when specifying SUM (LAST(Sls Un YTD)). IIF statements can’t be aggregated either, not even from a calculated field in the query.

3. The solution is so simple, it only took me 3 days to figure out.

First, I built the query against the cube as above, including the weeks and the YTD field from the cube. Then I added a Calculated Field to the Dataset. Right click on the report Dataset, select Dataset Properties, Select Fields, click on the Add button and select Calculated Field.

4. I added a calculated field for the last week in the range selected by the user. The calc pulls the integer value of the Week Number out of the parameter select by the user. ***Note: this 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.

=Replace(Mid(Parameters!ToTIMEFiscalWeekIDName.Value,InStr(Parameters!ToTIMEFiscalWeekIDName.Value,”].&[“)+4,10),”]”,””)

5. Now the magic. I added a calculated field called Sls_Un_YTD_calc. I always name my calculations whatever the altered measure name is plus “_calc”. This makes it very easy to use/replace in fields and calculations in the report. Here is the calculation.

=Floor(Fields!Fiscal_Week_ID.Value/Fields!ToWeek.Value)*Fields!Sls_Un_YTD.Value

Simple, right? This basically says, whatever week you are looking at, if it’s the last week in the selected range then muliply the YTD by One otherwise multiply it by Zero. By dividing the week in the row, but the maximum week in the range, you will either get One for the last week, or less than one for any other week. Taking the Floor of this value makes anything less than one into zero. Ta da! You have successfully zeroed out any unwanted values from your dataset, and in your report you can sum away to your hearts content.