How To Do Rolling 6 Months In Ssrs Expression?

Total number of items received during the month, total dollars for the month, etc.) I want to add two new columns to the end of the report that will display a rolling twelve month total for each of the different rows of data. Plus a column that would show year to date totals for the same rows. Oct 17, 2013  Calculating Values within a Rolling Window in SQL Any time that you need to combine values across several rows in SQL, the problem can be challenging, particularly when it comes to performance. We will focus upon the rolling twelve-month totals problem, but our methods can be applied to any time window (e.g., 3 months) or to averages and other.

• How To Do Rolling 6 Months In Ssrs Expressions

A common calculation in trend analysis is the moving (or rolling) average. A moving average is the average of the, for instance, last 10 rows. The moving average shows a more smooth curve than the actual values, more so with a longer period for the moving average, making it an good tool for trend analysis. This blog post will show how to calculate moving average in T-SQL. Different methods will be used depending on the version of SQL Server.The chart below demonstrates the smoothing effect (red line) with an 200 days moving average. The stock quotes are the blue line. The long term trend is clearly visible.

T-SQL Moving Avergage 200 daysThe demonstration below requires the TAdb database that can be created with the script located.In the upcoming example we will calculate a moving average for the last 20 days. Depending of the version of SQL Server, there will be a different method to do the calculation. And, as we will see later, the newer versions of SQL Server has functions enabling a lot more effective calculation.

SQL Server 2012 and later Moving AverageThis version make use of a aggregate window function. Whats new in SQL 2012 is the possibility to restrict the size of the window by specifying how many rows preceding the window should contain: SELECTT0.StockId,T0.QuoteId,T0.QuoteDay,T0.QuoteClose,AVG(T0.QuoteClose) OVER (PARTITION BY T0.StockId ORDER BY T0.QuoteId ROWS 19 PRECEDING) AS MA20FROMdbo.Quotes AS T0Rows preceding is 19, because we will include the current row as well in the calculation. As you can see, the calculation of moving average in SQL Server 2012 is pretty simple.The figure below demonstrates the windowing principle. Current row is marked with yellow. The window is marked with an blue background. The moving average is simply the average of QuoteClose in the blue lines.

T-SQL Moving average window.The results of the calculations in older versions of SQL Server are the same, so they will not be shown again. SQL Server 2005 – 2008R2 Moving AverageThis version make use of a common table expression. The CTE is self referenced to get the last 20 rows for each row:;WITH CTEMA AS(SELECTStockId,QuoteId,QuoteDay,QuoteCloseFROMdbo.Quotes)SELECTT0.StockId,T0.QuoteId,T0.QuoteDay,AVG(T1.QuoteClose)FROMCTEMA AS T0LEFT OUTER JOINCTEMA AS T1ONT0.StockId = T1.StockIdANDT1.QuoteId = T0.QuoteId - 19GROUP BYT0.StockId,T0.QuoteId,T0.QuoteDayMoving Average before SQL Server 2005The pre 2005 version will use a left outer join to the same table to get the last 20 rows. The outer table can be said to contain the window that we want to calculate an average on: SELECTT0.StockId,T0.QuoteId,T0.QuoteDay,T0.QuoteClose,AVG(T1.QuoteClose) AS MA20FROMdbo.Quotes AS T0JOINdbo.Quotes AS T1ONT0.StockId= T1.StockIdANDT1.QuoteId = T0.QuoteId - 19GROUP BYT0.StockId,T0.QuoteId,T0.QuoteDay,T0.QuoteClosePerformance ComparisionIf we run the three different methods simultaneously and check the resulting execution plan, there is a dramatic difference in performance between the methods. Comparision of three different methods to calculate moving averageAs you can see, the windowing function improvements in SQL 2012 makes a huge difference in performance. UsageAs mentioned in the beginning of this post, moving averages are used as a tool to illustrate trends. A common approach is to combine moving averages of different lengths, in order to spot changes in the short, medium and long term trends respectively.

Of particular interest are the crossing of trend lines. For instance, when the short trend moves over the long or medium trend, this can be interpreted as an buy signal in technical analysis.

How To Do Rolling 6 Months In Ssrs Expressions

And when the short trend moves under a longer trend line, this can be interpreted as a sell signal.The chart below shows Quotes, Ma20, Ma50 and Ma200.

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. Define current month in the SSAS Time dimensionI know there is Time functionality built into SSAS cubes, but I still like the flexibility of building attributes into my DIMDate 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 DimDate table is created as that is another subject. The DIMDate 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 DIMDate table and it is being updated nightly, add it to the SSAS cube for use in your reports. 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 DatasetFor 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’.

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’. 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.Posted by at 7:56 pm Tagged with:,. 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 LayoutCode, and the main query filters where LayoutCode IN (@LayoutCode) 1.

ADD ‘ ALL’ TO YOUR PARAMETER QUERYThe 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 LayoutCodeFROM YourTableUNIONSELECT ‘ ALL’ AS LayoutCodeORDER BY LayoutCode 2.

EDIT YOUR MAIN QUERY TO USE BOTH PARAMETERSEdit 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 (@LayoutCode)) OR (LayoutCode IN (@LayoutCode)) )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:(LayoutCode IN (@LayoutCode))with( (‘ ALL’ IN (@LayoutCode)) OR (LayoutCode IN (@LayoutCode)) )I suggest you have your WHERE clause already complete before adding the OR, since the results can get confusing. 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 LayoutCode parameter called LayoutGroup you will follow the same steps above, plus an additional step to edit the LayoutCode dataset query. Add ‘ ALL’ to your LayoutGroup 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 LayoutCode, to OR in the where clause. 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:, andIn 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 DIMENSIONSThe 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. CUBE DIMENSION NEEDS TWO WAYS TO ACCESS FISCAL MONTH IDThe 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 FiscalMonthID 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 FiscalMonthID AND FiscalMonth in my Time dimensions, with FiscalMonth using the FiscalMonthID as it’s key.

This gives me two ways to access the Fiscal Month ID, one from each Time dimension. REPORT DATASET NEED CORRECT FIELDS TO YIELD DESIRED RESULTSIn the Dataset Query I included FiscalMonthID from the OH Inventory Time dimension and FiscalMonth 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.UNIQUENAMEI will reference this field to find the number of months difference between the user Selected Month and the Last Receipt Date. ADD CACULATED FIELDSRight 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!FiscalMonthID.Value,2)+'-01-'+LEFT(Fields!FiscalMonthID.Value,4)))+13. 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!InvDollars.ValueI take the Floor of 4/MonthDiff to get a positive value if the MonthDiff.

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. 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 DayThe 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 PROPERTIESDouble 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 SELECTEDAdd 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 DIMTime table. 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!FiscalWeekID.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!FiscalWeekID.Value) / (ABS(Fields!CurrentWk.Value-Fields!FiscalWeekID.Value)+1) )).75.

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.Value6.

MaxDayTWName – 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 MULTIPLIERSAdd the following calculated fields:7. WeekToDatemultiplier – 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!DayOfWeekID.Value ) / ( Fields!MaxDayTW.Value / Fields!DayOfWeekID.Value ) )8. MostRecentDaymultiplier – 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!DayOfWeekID.Value /Fields!MaxDayTW.Value).Fields!WeekToDatemultiplier.Value4.

USE THE MULTIPLIERS IN YOUR REPORTYou 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!SalesDollars.Value.Fields!MostRecentDaymultiplier.Value2.

To get Week To Date Sales $ for Last Year use this calculation:=Sum(Fields!SlsDollarsLY.Value.Fields!WeekToDatemultiplier.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. Posted by at 2:06 pm Tagged with:,. This is a sister solution to my 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 YearBut what if you want to ignore all values in a row where LY. 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 SlsUnYTDcalc. 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!FiscalWeekID.Value/Fields!ToWeek.Value).Fields!SlsUnYTD.ValueSimple, 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. You have successfully zeroed out any unwanted values from your dataset, and in your report you can sum away to your hearts content. Posted by at 8:33 pm Tagged with:,.