Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

trying to make my first report and it is not going :( Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:17 PM
Points: 49, Visits: 96
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
Post #1429759
Posted Thursday, March 14, 2013 10:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
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
Post #1431130
Posted Thursday, March 14, 2013 1:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:17 PM
Points: 49, Visits: 96
my mdx is from someone who is just learning :)
thanks so much i will try it!!!
Post #1431244
Posted Sunday, March 17, 2013 9:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:17 PM
Points: 49, Visits: 96
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]
Post #1431995
Posted Sunday, March 17, 2013 6:04 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 3:42 PM
Points: 452, Visits: 848
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.
Post #1432050
Posted Monday, March 18, 2013 12:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:17 PM
Points: 49, Visits: 96
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 :)
Post #1432081
Posted Monday, March 18, 2013 12:09 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 3:42 PM
Points: 452, Visits: 848
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.
Post #1432082
Posted Monday, March 18, 2013 12:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:17 PM
Points: 49, Visits: 96
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

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
Post #1432089
Posted Monday, March 18, 2013 4:55 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 3:42 PM
Points: 452, Visits: 848
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]
)

Post #1432399
Posted Tuesday, March 19, 2013 12:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:17 PM
Points: 49, Visits: 96
it didnt work

life on mdx land is never easy
Post #1432490
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse