Post reply

Display Member Property

  • October 4, 2005 at 11:07 pm

    #110210

    I have a Parent-Child Dimension named "Product" whose:

    Member Key Column is the ID field from the Product table,

    Parent Key Column is the ParentID field from the Product table (Foreign Key to ID),

    Member Name Column is the ProductName field from the Product table.

    The Product Dimension has four levels:

    1. ProductBrand
    2. SubBrand
    3. ProductCategory
    4. Product

    I need to send these ProductBrand names along with some measures and other dimensions to Reporting Services by making an MDX query.

    The problem I face is that that though in the MDX Sample Application the ProductBrand name is properly displayed, in Reporting Services the Product ID is what is displayed instead of the ProductBrand name even though it's the same query - Pretty Confusing. I figured this might be because Reporting Services maybe using the Member Key Column. Therefore I decided to display the Member Name Column in the MDX query by using dimension members, but always end up with errors.

    Can somebody help. Thanks in advance...

  • October 5, 2005 at 4:57 pm

    #595307

    Can you post both of your MDX queries for us to look at?

    Steve.

  • October 6, 2005 at 2:30 am

    #595357

    This is what I'm using right now. The section in red is what's displaying the Product Brand name (Member Name Column) properly in MDX Sample Application, and as Product ID (presumed: Member ID Column) in Reporting Services:

    WITH

     MEMBER [Measures].[Actual] AS 'IIF(ISNULL([Measures].[Sales Quantity]), 0, [Measures].[Sales Quantity])'

     MEMBER [Measures].[Target] AS 'IIF(ISNULL([Measures].[Target Quantity]), 0, [Measures].[Target Quantity])'

     MEMBER [Time].[DateRange] AS 'SUM([Time].[All Time].[2002].[August].[Week 5].[28/08/2002 (WED)] : [Time].[All Time].[2002].[August].[Week 5].[28/08/2002 (WED)])'

    SELECT

     [Product].[Brand].Members ON COLUMNS,

      CROSSJOIN

      (ORDER

       (HIERARCHIZE

        (DRILLDOWNLEVEL

         ([Geography].[All Geographies].[Colombo 01].[WESTERN].[NEW TRADING STORES].Children)

    ), [Geography].CurrentMember.Name, ASC

    ), {[Measures].[Actual], [Measures].[Target]}

    ) ON ROWS

    FROM

     [Sales]

    WHERE

     [Time].[DateRange]

    Thanks a lot

  • October 6, 2005 at 6:10 am

    #595415

    Have you tried putting only the measures on the columns and nesting (crossjoin-ing) your other dimensions on the rows? Keep in mind it will still come back as a flat rowset in RS so you should be able to put the products back into the columns (in a matrix) and the remainder on the rows (in the matrix).

    Steve.

  • October 7, 2005 at 3:52 am

    #595800

    I figured out was wrong: I had to put the Products ON PAGES. Now it works exactly the way I want it to.

    Thanks a lot Steve, especially for coming to my rescue lghtning fast

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply