MDX Query returns value in report but not in Visual Basic code

Question

This is for an application which dynamically sets data for and renders reports.

I have an MDX query for a report which relies on a parameter. The query is:

SELECT NULL ON COLUMNS, strtomember(@DateYear) ON ROWS FROM [MYDATACUBE]

When running this in the report query designer, it returns a value properly. However, when running this in visual basic code, it returns nothing. Here is the important part of my code:

    Dim cn = New AdomdConnection(adomdparamconnectionstrings(countparamsadomd))
    Dim da As AdomdDataAdapter = New AdomdDataAdapter()
    Dim cmd = New AdomdCommand(paramcommands(countparamsadomd), cn)
    Dim tbl = New DataTable

    If (adomdparams) Then 'If there are parameters, adds them to the query
        For l As Integer = 0 To (countparamsadomd - 1)
            If (adomdparamconnectionstrings(l) = "NODATASET") Then
                Dim p As New AdomdParameter(paramvaradomd(l), paramadomd(l))
                cmd.Parameters.Add(p)
            Else
                Dim p As New AdomdParameter(paramvaradomd(l), adomdqueryvalues(l))
                cmd.Parameters.Add(p)
            End If
        Next
    End If

    da.SelectCommand = cmd

    cn.Open()
    da.Fill(tbl)
    cn.Close()

I know the connection string works because all the other datasets use the same one. I know the command is right using break points. I know the parameter's value is right also using break points. I know the code overall works because it works with every dataset I've tested it with except this one. Using break points, everything seems to work as it does for other datasets, but then it just doesn't return any values.

The table resulting from this does have a properly named column ([Date].[Year].[Year].[MEMBER_CAPTION]) but has no rows. The returned value should be a single row with the year in it.

Answer 1

I have done something similar in C#.

If you have access to the dimension and hierarchy name, then you can have a generic query like this:

WITH
    MEMBER [Measures].[Member Caption] AS StrToMember(@Hierarchy).HIERARCHY.CURRENTMEMBER.MEMBER_CAPTION
SELECT 
    [Measures].[Member Caption] ON COLUMNS, 
    StrToMember(@DateYear) ON ROWS
FROM 
    [MYDATACUBE]

Note that you'll have to add the parameter, @Hierarchy, to the command before executing the query. There is a little trick in the value expression for the calculated member. When passing a hierarchy expression to StrToMember(), it returns the default member from that hierarchy. From the default member, you can use the HIERARCHY function to work backwards to get the hierarchy. From the hierarchy, you can get the CURRENTMEMBER and its MEMBER_CAPTION property.

If you want a query specific for the hierarchy in your example, you can use:

WITH
    MEMBER [Measures].[Member Caption] AS [Date].[Year].CURRENTMEMBER.MEMBER_CAPTION
SELECT 
    [Measures].[Member Caption] ON COLUMNS, 
    StrToMember(@DateYear) ON ROWS
FROM 
    [MYDATACUBE]