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

Question

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.

You have to use the macros to pass a parameter directly to the query: gqbi.wordpress.com/2014/03/20/… — Danylo Korostil
– Danylo Korostil, Commented Apr 19, 2017 at 7:21
@DanyloKorostil why can't the user use excel's cubefunctions to construct a dynamic mdx environment? — whytheq
– whytheq, Commented Apr 19, 2017 at 7:26

whytheq · Accepted Answer · 2017-04-19 07:28:56Z

3

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

answered Apr 19, 2017 at 7:28

whytheq

35.7k69 gold badges179 silver badges283 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

whytheq Over a year ago

@DanyloKorostil well it is quite a different approach - you need to extract sets and members and add them to different cells in the worksheet - then sort of rebuild your mdx script using what is now pseudo-embedded in the worksheet. Worth having a play as quite good fun.

user3735855 Over a year ago

Thankyou @whytheq, previously I had passed this video over as its an hour long - but it turns out that there is some good stuff in it! Thankyou for the prompt.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related