Search code examples
reporting-servicesssasmdx

SSRS report from cube parameter: text input field instead of drop-down menu

Good afternoon,

I'm very new to MDX. Using Report Builder I created SSRS report based on cube data. All looks good and I'm able to add a parameter for this report too, but the problem is that I only managed to get it working as a drop-down menu. What I really need is it to work in a typical SSRS way - text input field.

So searching for an information I got an impression that the only way to achieve this is to amend dataset's MDX query and set the desired field as StrToMember. The most relevant example that I found was this one. Other examples on the internet weren't too helpful to me with the knowledge I have.

My question is: having this query, where/how should I use StrToMember to be able to use the field [Inventory information].[Parent Item].[Parent Item] as a free text parameter? 

SELECT { } ON COLUMNS, { 
    ([Inventory information].[BOM].[BOM].ALLMEMBERS * 
    [Inventory information].[Item number].[Item number].ALLMEMBERS * 
    [Inventory information].[Line number].[Line number].ALLMEMBERS * 
    [Inventory information].[Parent Item].[Parent Item].ALLMEMBERS * 
    [Inventory information].[Product name].[Product name].ALLMEMBERS * 
    [Inventory information].[Quantity].[Quantity].ALLMEMBERS * 
    [Inventory information].[Sequence].[Sequence].ALLMEMBERS )}
        DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
        FROM [Inventory analysis] CELL PROPERTIES VALUE

This query doesn't return any errors, but at the same time it removes all fields from a dataset. 

SELECT { } ON COLUMNS, { 
    ([Inventory information].[BOM].[BOM].ALLMEMBERS * 
    [Inventory information].[Item number].[Item number].ALLMEMBERS * 
    [Inventory information].[Line number].[Line number].ALLMEMBERS * 
    StrToMember ('[Inventory information].[Parent Item].[Parent Item].&[' + @InventoryinformationParentItem + ']') *
    [Inventory information].[Product name].[Product name].ALLMEMBERS * 
    [Inventory information].[Quantity].[Quantity].ALLMEMBERS * 
    [Inventory information].[Sequence].[Sequence].ALLMEMBERS )}
        DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
        FROM [Inventory analysis] CELL PROPERTIES VALUE
Solution

  • Your dataset is not identifying fields returned by your MDX script. I think you did not define the parameter in the designer. Add the parameter from the designer.

    enter image description here

    Try your query without an associated parameter.

    SELECT { } ON COLUMNS, { 
    ([Inventory information].[BOM].[BOM].ALLMEMBERS * 
    [Inventory information].[Item number].[Item number].ALLMEMBERS * 
    [Inventory information].[Line number].[Line number].ALLMEMBERS * 
    StrToMember ("[Inventory information].[Parent Item].[Parent Item].&[" + "SomeString" + "]") *
    [Inventory information].[Product name].[Product name].ALLMEMBERS * 
    [Inventory information].[Quantity].[Quantity].ALLMEMBERS * 
    [Inventory information].[Sequence].[Sequence].ALLMEMBERS )}
        DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
        FROM [Inventory analysis] CELL PROPERTIES VALUE

    Also I am unsure about your syntax in the Parent Item attribute you posted.

    Try this:

    StrToMember ("[Inventory information].[Parent Item].&[" + "SomeString" + "]")

    Let me know if this can help you.