Search code examples
excelparametersssasmdx

Excel - MDX query to SSAS Cube with cell values as parameters

I am trying to get an MDX query to be executed through Excel, using cell values as parameter values. Is there any help with this query please? 

WITH MEMBER
[Measures].[SubDrillDown] AS [Statement Financial Analysis].[Report      Framework].PARENT_UNIQUE_NAME
SELECT NON EMPTY {
[Measures].[SubDrillDown],
[Measures].[Budget Opening],
[Measures].[Budget Closing], 
[Measures].[Actuals Opening],
[Measures].[Actuals Closing],
[Measures].[Budget],
[Measures].[Actuals],
[Measures].[Commitments Closing],
[Measures].[Job Budget],
[Measures].[Job Budget Closing],
[Measures].[Job Annual Plan],
[Measures].[Job Annual Plan Closing],
[Measures].[Job Commitments]

} ON COLUMNS, 
NON EMPTY { 
(
[Statement Financial Analysis].[Report Framework].[Group Code].ALLMEMBERS *
[Financial Periods].[Financial Periods].[Financial Period].ALLMEMBERS 
*[Financial Periods].[Financial Period Name].[Financial Period Name].MEMBERS
*[Journal Transaction Structure].[Journal Code].MEMBERS
) 
} ON ROWS 
FROM ( SELECT ({'"&c1&"'})  ON COLUMNS 
FROM ( SELECT ({'"&c3&"'})  ON COLUMNS 
FROM ( SELECT ({'"&c4&"'})  ON COLUMNS 
FROM ( SELECT ({'"&c5&"'})  ON COLUMNS 
FROM [Fin] ))))

The query has been written previously and I am replacing 

FROM ( SELECT STRTOMEMBER(@prmYear)  ON COLUMNS 
FROM ( SELECT STRTOMEMBER(@prmDept)  ON COLUMNS 
FROM ( SELECT STRTOSET(@prmEntity)  ON COLUMNS 
FROM ( SELECT STRTOSET(@prmActivity)  ON COLUMNS

Obviously, C1,C3,C4 and C5 are cell references on sheet1.Each of these cells has a full MDX value in, forexample:

c1 is =CONCATENATE("[Financial Periods].[Financial Periods].[Financial Period].&[",VLOOKUP(B1,Sheet2!A:B,2,FALSE),"-",C2,"-01 T00:00:00]")

which displays as [Financial Periods].[Financial Periods].[Financial Period].&[2005-01-01 T00:00:00], hopefully the MDX would interpret the final value

I am getting an error "Details: "AnalysisServices: Query (30, 15) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used."" and am stumped tbh. Any help would be appreciated.

I have ran the expression with the FULL TEXT values in and works as planned, its just the referencing of the cell that is not working.

Thankyou.

Solution

  • Not an ideal answer as it is simply a link but I found the following walkthrough really informative about the power of Excel's cube functions:

    https://channel9.msdn.com/Events/TechEd/NewZealand/2013/DBI304

    Using the techniques he outlines you can deconstruct your mdx and make values in cells parameters for the mdx