March 12, 2013 at 6:53 am
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
March 14, 2013 at 10:59 am
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
March 14, 2013 at 1:58 pm
my mdx is from someone who is just learning 🙂
thanks so much i will try it!!!
March 17, 2013 at 9:49 am
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]
March 17, 2013 at 6:04 pm
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.
March 18, 2013 at 12:02 am
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 🙂
March 18, 2013 at 12:09 am
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.
March 18, 2013 at 12:57 am
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 😀
March 18, 2013 at 4:55 pm
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]
)
March 19, 2013 at 12:34 am
it didnt work :w00t:
life on mdx land is never easy 😀
March 19, 2013 at 4:53 pm
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]
March 21, 2013 at 12:41 am
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