Forecasting financial measures with linear regression

In this blog I explain the relevant steps towards developing a Microsoft Reporting Services report in which different financial measures are compared and forecasted using the build-in MDX, linear regression function LinRegPoint of SQL Server Analysis Services. You don’t have to implement a Data Mining model with SQL Server Analysis Services in order to use the linear regression function.

The preview of the SQL Server Reporting Services report is shown below.

Preview SQL Server Reporting Services report

The historical (actual) and forecasted figures are reported graphically and numerically. The measures Sales Amount and Gross Profit are plotted on the y-axis and the hierarchy level Calendar Quarter is plotted on the x-axis of the embedded graph. Each measure is aligned to a different y-axis in order to differentiate the numerical interval. The forecasted figures are represented by dotted lines and colored numbers.

The dataset of the report contains a dynamic MDX query with embedded report parameters for the selected measures, calendar years and the hierarchy level.

The dynamic MDX query of the SQL Server Reporting Services report is shown below.

Dataset SQL Server Reporting Services report

The dynamic MDX query is the parameterized version of the static MDX query shown below.

Static MDX Query

The defined Calculated Members consist of the selected financial measures and combine the historical and forecasted figures. Named Sets are defined for the selected hierarchy level and the x-axis of the calculated linear regression line. Ordinal positions are determined through the period ranking of the historical and forecasted figures and determine the x-values of the linear regression line. Additional, explanatory information about the components of the linear regression line are embedded in the MDX query and are shown in the results below.

Results MDX Query

If you require a more sophisticated, precise forecasting function. You should consider implementing a Data Mining model with SQL Server Analysis Services with the Time Series algorithm.