SSRS Linear Regression Line for data in SSAS Cube

Question

End Goal: Create a scatter plot with actual data (coming from SSAS Cube) and a best fit line using basic least-squares regression.

At the present, my MDX looks like this:

SELECT NONEMPTY({[Measures].[Invoice Total]}) ON COLUMNS,
NONEMPTY( { [Billed Date].[Date].ALLMEMBERS}) ON ROWS
FROM 
(
SELECT NONEMPTY(StrToMember(@StartDate,CONSTRAINED):StrToMember(@EndDate,CONSTRAINED)) ON COLUMNS,
NONEMPTY( STRTOSET(@Requestor)) ON ROWS
FROM [Task Billing]
WHERE STRTOSET(@Project)
)
WHERE STRTOSET(@Division)

As you can see, there are a large number of parameters used to filter which data should be included in the regression. I was thinking of using LinToPoint but I cannot really figure it out, since I am so new to MDX.

I am TOTALLY open to workarounds.

Any ideas on how to accomplish this? Surely it is a common issue...

Magnus Smith Magnus Smith · Accepted Answer · 2013-02-12T13:54:15

You're new to MDX....and I've forgotton all the advanced stuff I once knew! Not a great combination - sorry. All I can offer is the actual MDX I once used to show a trend line amongst real data points.

 with 
 member [Measures].[X] 
    as 'Rank([Time], [Time].[Week].members)' 
 member [Measures].[Trend] 
    as 'LinRegPoint(X, [Time].[Week].members, [Measures].[Gross], X)'  
 select 
    {[Time].[Week].members} on rows, 
    {[Measures].[Gross], Trend} on columns 
 from [Sales]

If you can get a static example working on your cube, using the bare bones I give above, you can plug the @parameters in later. I hope that helps in a some way. Feel free to comment and I'll try to advise, but I am veeeeery rusty.

SSRS Linear Regression Line for data in SSAS Cube

1 Answers