Passing a parameter into an MDX Query using Power Query in Excel

Question

I am using a parameter to build an MDX query to analysis services in a SQL Server Cube/Tabular Model. This is an example

AnalysisServices.Database("server_name", db_param, [Query = "SELECT * FROM table_a WHERE db = 'db_name'])

My parameter is 'db_param', and it works fine to build the connection to the right database inside the server, but I can't manage to pass it to the MDX query. I would essentially like to replace 'db_name' with db_param such that it calls the parameter.

Any ideas? I tried single brackets, double brackets, inside quotation marks, not inside quotation marks. And I can't seem to create a chain either. (e.g. "SELECT * FROM table_a WHERE db = " + db_param + "rest of the query"]

Any ideas?

In general, you need to use & not + to combine. let aa = "ddd", bb= "ccc", statement = "SELECT * FROM "&aa& " WHERE db='"&bb&"'", Source =[Query = statement] — horseyride
– horseyride, Commented Jul 6, 2023 at 13:48
Thank you, that worked. You indeed need to place the variable in "&var_here&" for it to work. I also noticed that in a where statement you still need to wrap the whole thing in ''. So for example "... WHERE constraint = '"&var_here&"' " If you write your comment in an answer I'll gladly accept it. — James
– James, Commented Jul 7, 2023 at 12:02

horseyride · Accepted Answer · 2023-07-07 12:31:19Z

1

In general, you need to use & not + to combine.

let aa = "ddd", 
bb= "ccc", 
statement = "SELECT * FROM "&aa& " WHERE db='"&bb&"'", 
Source =[Query = statement]
in Source

edited Jul 7, 2023 at 12:31

answered Jul 7, 2023 at 12:05

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Passing a parameter into an MDX Query using Power Query in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related