Results 1 to 9 of 9

Thread: Basic MDX Code

  1. #1
    Join Date
    Dec 2004
    Location
    Cape Town
    Posts
    5

    Basic MDX Code

    Hi there.
    I am trying to use MDX code to create a measure in ProClarity. Please help!!

    A store can be one of a number of ‘Brands’. The MDX segment below gives me the Sales Value of a selected item, but for Store Brand 'Brand1' only. This works fine - but how do I add a brand? That is, how do I see the combined Sales Value for the selected item for 'Brand1' and 'Brand2'?

    ([Store Brand].[Brand1],[Measures].[Sales Value],[Item].CurrentMember)


    B

  2. #2
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Maybe this helps?

    I tried to replicate the requirement using dims / measures in the Sales cube, so as to show the result. While Stores there don't have "brands," Products do.

    Is this shorthand for what you're trying to do, in the context of your own data?

    SELECT

    {[Measures].[Store Sales]} ON COLUMNS,

    {[Product].[Product Brand Name].[Good], [Product].[Product Brand Name].[Pearl]} ON ROWS

    FROM

    [SALES]

    with a result set resembling:


    ---------------- Store Sales

    Good --------- $ 500.18

    Pearl -------- $ 549.85

    (The dashes are just alignment placeholders...)

    If I'm missing the point, please advise ...

    Thanks.

    Bill

  3. #3
    Join Date
    Dec 2004
    Location
    Cape Town
    Posts
    5
    Hi Bill.

    Thanks so much for your reply. I realize I was confusing.

    We do infact have store brands e.g. in our group of stores we have the following brands: Shoprite, Checkers,
    Checkers Hyper, Usave etc.

    Some products are sold at certain Store Brands only e.g. Ritebrand products will be sold at Shoprite only; or Housebrand products will be sold at Checkers and Checkers Hyper only.

    I am using ProClartiy as a front end. My actual aim is to work out Percentage Paricipation for certain products in their particular product category; but for a particular Store Brand only. I use the following code for Shoprite and it works! BUT NOW: I need to do this for products sold at Checkers AND Checkers Hyper.


    iif(([Measures].[Sales Value], [Item].CurrentMember.parent.parent) <> 0, ([Store Brand].[Shoprite],[Measures].[Sales Value], [Item].CurrentMember) / ([Store Brand].[Shoprite],[Measures].[Sales Value], [Item].CurrentMember.Parent.Parent)*100 , NULL)


    Thx
    Berenice

  4. #4
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    Did you try .Currentmember with [Store Brand]?

    Am I understanding the question?

    Bill

  5. #5
    Join Date
    Dec 2004
    Location
    Cape Town
    Posts
    5
    I substituted [StoreBrand].[Shoprite] with [StoreBrand].CurrentMember. This means I still physically have to select Store Brands 'Checkers' and 'Checkers Hyper'. This is however the best solution so far and it works.
    Just out of interest though, if you ever find out how to put both Store Brands in the query, Please let me know.

    Thanks for you help!!
    Berenice

  6. #6
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    Post back the full query as it is with the latest change, so I can see it, and I'll give it some thought. I guess I'm still not seeing why this won't do what I think you want. You want to select two members of a set, not the whole set, in the report, but you don't want to specify the members you want to isolate?

    Additionally, can you parameterize a query like this in ProClarity (like you can easily do in Reporting Services?) Just curious ...

    B*

  7. #7
    Join Date
    Dec 2004
    Location
    Cape Town
    Posts
    5
    iif(([Measures].[Sales Value], [Item].CurrentMember.parent.parent) <> 0, ([Store Brand].CurrentMember,[Measures].[Sales Value], [Item].CurrentMember) / ([Store Brand].CurrentMember,[Measures].[Sales Value], [Item].CurrentMember.Parent.Parent)*100 , NULL)


    This is how it looks now. But I DO WANT to specify the members of the set. I am able to specify one member (([Store Brand].[Shoprite]) but not two. It's probably not necessary to specify - one could just select the members. But now I wonder how one would do it.

    I selected the two members and looked at the MDX that Proclarity generated - It uses the AGGREGATE function to combine the two members. I got errors when I tried to use AGGREGATE.

  8. #8
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    It dawns on me that this boils down to "how to do it in ProClarity," which is why we seem to be talking about two different things. The MDX you show is the ProClarity rendering, based, I assume, upon design in the GUI. It is also only the portion of the query underlying the calculated member you are defining.

    When you ask about "coding in MDX," I assume you mean direct coding. Do you understand the components of an MDX query?

    In a simple MDX query, the SELECT statement specifies both Columns and Rows. Assuming this measure was your "Column," you would specify, in the SELECT statement, your two brands on the rows. You could specify all members of Brands, or just the two or more you wanted to see, etc.

    That is what I was saying with my original "pseudocode:"

    SELECT

    {[Measures].[Calculated Measure]} ON COLUMNS,

    {[Store Brand].[Brand 1], [Store Brand].[Brand 2]} ON ROWS

    FROM

    [CUBE]


    What I'm trying to see in the MDX is the "ON ROWS" Row - can you post the entire MDX query, not just the definition of the calculated member? I assume you can modify the MDX directly, and to do so, you would have to be able to get to the central query.

    Am I making sense?

    B*

  9. #9
    Join Date
    Dec 2004
    Location
    Cape Town
    Posts
    5
    Hi Bill.

    Yes. Definitely "how to do it in ProClarity". I am trying to do is set up a measure for a user.

    MDX is new to me - thank you for the explanation. However, I do NOT want to choose columns and rows. The user will select this in Proclarity.


    As mentioned earlier, the actual query (a calculated measure)looks like this:

    iif(([Measures].[Sales Value], [Item].CurrentMember.parent.parent) <> 0, ([Store Brand].[Shoprite],[Measures].[Sales Value], [Item].CurrentMember) / ([Store Brand].[Shoprite],[Measures].[Sales Value], [Item].CurrentMember.Parent.Parent)*100 , NULL)

    So I only need the definition of the calculated member. Not the whole query.

    Here we look at a value for Shoprite (1 member) only . I now want to see the COMBINED Values for 2 members.

    I manually selected two brands just to see what ProClarity generates so I could use the code. It generates code that uses AGGREGATE, But it seems AGGREGATE can only be used with SELECT (which I don't want to use).


    I would have expected something like [Store Brand].[Brand1].&[Store Brand].[Brand2) to work. (Brand 1 and Brand 2 being members of the dimension 'Store Brand')

    Thanks!
    Berenice

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •