trying to make my first report and it is not going :(

  • Hi,

    I am new here, I have a question, unfortunately every time I solve it on my own I keep mixing the code even worse.

    I need to get as a result a table that looks like this.

    Item Yesterday

    Sum Payments Measure…

    Transaction count Measure…

    New paying users Measure…

    (sorry, i didnt know how to make it look like a table.

    The problem is that no matter what I do, I cannot make it work 🙁

    I don’t know how to set the name of the row to be different from the name of the measure.

    I don’t know how to change the date to bring yesterday every time I run it.

    Basically I am a newbie having a mdx crisis.

    Here is my code.

    with member [Time].[Year - Month - Period - Date].[Date].&[2013-03-10T00:00:00]

    as 'aggregate ({[Time].[Year - Month - Period - Date].[Date].&[2013-03-10T00:00:00]})'

    select {[Time].[Year - Month - Period - Date].[Date].&[2013-03-10T00:00:00]} on columns,

    {[Measures].[Purchase - converted to USD],

    [Measures].[Purchases_Count],

    [Measures].[Site Pay User],

    [Measures].[Free_signups],

    [Measures].[free to site pay user],

    [Measures].[click to site pay user],

    [Measures].[Click Count],

    [Measures].[Guests Logins Count],

    [Measures].[Guests Unique Logged in],

    [Measures].[Guests Credit Spent],

    max([Hour Dim].[D Hour].[All]},[Measures].[Purchase - converted to USD]),

    min([Hour Dim].[D Hour].[All]},[Measures].[Purchase - converted to USD]),

    Avg([Hour Dim].[D Hour].[All]},[Measures].[Purchase - converted to USD])} on rows

    FROM [Dw Virtual]

    WHERE ([Sites Dim Vw].[Site Group Tree].[Site Group].&[ All],

    [Device Name].[Device Name].[All],

    [Time].[Year - Month - Period - Date].[Date].&[2013-03-10T00:00:00])

    CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

    Thanks a bunch

    Astrid

  • Hi Astrid,

    Your MDX is quite unusual and without access to your cubes I can't really say I've helped you but try the below MDX

    I would say that:

    If you want averages/max etc you can't do that in select part of MDX - it's either done in the "with" part of the MDX or in the cube's calculation script

    The cube will auto-aggregate your data so there wasn't the need for the aggregate clause in your MDX

    You only need to specify the datetime once - in the case on the rows. You don't need to do this in the where clause as well

    with

    member [Measures].[Max] As

    max({[Hour Dim].[D Hour].[All]},[Measures].[Purchase - converted to USD])

    member [Measures].[Min] As

    min({[Hour Dim].[D Hour].[All]},[Measures].[Purchase - converted to USD])

    member [Measures].[Avg] As

    min({[Hour Dim].[D Hour].[All]},[Measures].[Purchase - converted to USD])

    select {[Time].[Year - Month - Period - Date].[Date].&[2013-03-10T00:00:00]} on columns,

    {[Measures].[Purchase - converted to USD],

    [Measures].[Purchases_Count],

    [Measures].[Site Pay User],

    [Measures].[Free_signups],

    [Measures].[free to site pay user],

    [Measures].[click to site pay user],

    [Measures].[Click Count],

    [Measures].[Guests Logins Count],

    [Measures].[Guests Unique Logged in],

    [Measures].[Guests Credit Spent],

    [Measures].[Max],

    [Measures].[Min],

    [Measures].[Avg]} on rows

    FROM [Dw Virtual]

    WHERE ([Sites Dim Vw].[Site Group Tree].[Site Group].&[ All],

    [Device Name].[Device Name].[All])

    Mack

  • my mdx is from someone who is just learning 🙂

    thanks so much i will try it!!!

  • hi,

    i did what you suggested and it worked perfectly. but then i tried to do the yesterday thingy and i got the following error 🙁

    Executing the query ...

    The Measures hierarchy already appears in the Axis0 axis.

    Execution complete

    but i dont understand why, cause i do put the measures on the rows and the yesterday thingy on the column 🙁

    here is the code.

    with

    member [Measures].[Max] As

    max({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])

    member [Measures].[Min] As

    min({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])

    member [Measures].[Avg] As

    avg({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])

    member yesterday as dateadd("d",now(),-1)

    select yesterday on columns,

    {[Measures].[Purchase - converted to USD],

    [Measures].[Purchases_Count],

    [Measures].[Site Pay User],

    [Measures].[Free_signups],

    [Measures].[free to site pay user],

    [Measures].[click to site pay user],

    [Measures].[Click Count],

    [Measures].[Guests Logins Count],

    [Measures].[Guests Unique Logged in],

    [Measures].[Guests Credit Spent],

    [Measures].[Max],

    [Measures].[Min],

    [Measures].[Avg]} on rows

    FROM [Dw Virtual]

  • There's two issues here. Firstly MDX won't let you put measures on both the columns and the rows. Best to keep measures on the columns and dimensions on the rows.

    The second issue is your calculation for yesterday. What you want to do is dynamically produce a 'yesterday' based on today's date, but it needs to map directly to your date dimension. The way you have the yesterday calc doesn't make sense to the cube, it's just a date out of context.

    You can do this by re-creating a member as a string and then using the StrToMember() function to get the cube to recognise it as a member reference.

    So if your target looks like this:

    [Time].[Year - Month - Period - Date].[Date].&[2013-03-10T00:00:00]

    You would create a function like

    StrToMember("[Time].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-ddT00:00:00") +"]")

    and use that directly in the rows part of the MDX. Don't create it in the "With" part where you are defining custom measures.

  • it worked!!!!

    thanks soooooooooooooo much!!!!! :):)

    but my questions are not over lol.

    how do i change name to the rows? now it give me the names of the measures or the calculated measures, but how do i put an alias?

    for example I have

    [Measures].[Click Count], what happen if in the report i want to call it "Yesterday Clicks"

    thanks in advance 🙂

  • yay!!

    You can alias a measure in a calc:

    With [Measures.[Yesterday Clicks] as

    (

    [Measures].[Click Count]

    )

    That's going to cause you problems though, if you want to show both today's and yesterday's clicks on the same dataset. Then you would need to include a date modification in the measure, somewhat like

    With [Measures.[Yesterday Clicks] as

    (

    [Measures].[Click Count], [Date Dimension].[Date].currentmember.prevmember

    )

    Then you would be able to use today's date directly as a dimension in the rows, without it calculating to yesterday. My syntax there might need correcting.

    [Edit] actually I think what I've shown there is wrong but that's the general idea. Let me know if you want to do something like that and I'll post how.

  • Well the second one I didn’t understand, but then we can blame the coffee since apparently didn’t kick in yet 😀

    I must say I did try, but it didn’t go, I also tried

    member [Measures].[Click] As [Measures].[Click Count]

    since I do have a with at the beginning of the query. And still didn’t work lol. And of course I also tried changing the name from max to something else and nada de nada :w00t:

    member [Measures].[max] As

    max({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])

    maybe is just too early for mdx for me 😀

  • I made a typo in there sorry, left off a square brack and forgot the keyword "Member".

    Yes it's always too early for MDX! Obviously by my error it was too late for MDX too 😉

    You're right you only need one "With" at the beggining and then you can list out the calcs

    Here's the right one:

    With Member [Measures].[Yesterday Clicks] as

    (

    [Measures].[Click Count]

    )

  • it didnt work :w00t:

    life on mdx land is never easy 😀

  • This works on the adventure works cube.

    Post your query

    WITH Member [Measures].[Something Made Up] as

    (

    [Measures].[Internet Order Count]

    )

    Select

    {

    [Measures].[Internet Order Count]

    ,[Measures].[Something Made Up]

    }

    On Columns,

    (

    [Sales Territory].[Sales Territory Country].&[Canada]

    )

    on rows

    FROM [Adventure Works]

  • i will try it. thanks a bunch 🙂

Viewing 12 posts - 1 through 11 (of 11 total)

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