MDX Query works in SSMS but not in Visual Studio

Question

I have an MDX query that runs without any problems in SSMS. But, I am trying to use this query in Visual Studio to pull data for a report. When I run it in VS, it returns an empty table.

I had been playing around with the NON EMPTY statement because before I realized this problem, I was seeing that with the NON EMPTY, I would only get my measures fields. Without the NON EMPTY I would get all the measures and 6/7 of my ROW fields. Finally I looked and saw that it was just pulling an empty table.

SELECT
{
[Measures].[Retail Number Sold],
[Measures].[Retail Sales Amount],
[Measures].[Retail Sales Tax],
[Measures].[Wholesale Number Sold],
[Measures].[Wholesale Sales Amount],
[Measures].[Wholesale Sales Tax] } ON COLUMNS,
NON EMPTY
{
(
IIF(len(@District) > 0, [Customer - Invoice account].[StoreDistrict].[StoreDistrict].[@District], [Customer - Invoice account].[StoreDistrict].[StoreDistrict].ALLMEMBERS) *
IIF(len(@AgencyID) > 0, [Customer - Invoice account].[Store].[Store].[@AgencyID], [Customer - Invoice account].[Store].[Store].ALLMEMBERS) *
[Customer - Invoice account].[StoreName].[StoreName].ALLMEMBERS *
IIF(len(@BrandID) > 0, [Released products].[Product number].[Product number].[@BrandID], [Released products].[Product number].[Product number].ALLMEMBERS) *
[Released products].[Product name - Released products].[Product name].ALLMEMBERS *
IIF(len(@Category) > 0, [Released products].[CategoryName].[CategoryName].[@Category],[Released products].[CategoryName].[CategoryName].ALLMEMBERS) *
[Customer].[Customer group].[Customer group].ALLMEMBERS
) } ON ROWS
FROM
[Sales cube];

Maybe I am wrong here but when I test the query, I should be able to see my results. I also want to be able to see all of my row and column fields so that I can put these into my report.

Aidan Baker Aidan Baker · Accepted Answer · 2019-08-09T19:47:24

I figured out the problem I was running into. In SSMS, I was passing hard coded values instead of the parameters I was using in Visual Studio. So, it didn't occur to me until awhile later that my parameter syntax was incorrect, and that is why I wasn't able to pull any data.

When passing a parameter into a tuple, you can't simply just put

[Customer - Invoice account].[StoreDistrict].[StoreDistrict].[@District]

MDX requires you to place your parameter into a string and convert that string to a member, like so.

StrToMember("[Customer - Invoice account].[StoreDistrict].[StoreDistrict].[" + @District + "]")

MDX Query works in SSMS but not in Visual Studio

1 Answers