SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dates and columns


dates and columns

Author
Message
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 200
hi,

i have another question (or two) Smile

1) i want the mdx to always return the data from yesterday without choosing the data, can someone please tell me how to do it? everything i tried doesnt work Sad
2) also the mdx returns the measures for the data i asked on the first column, how do i go iif i want different dates on different columns to return the data

thanks in advance Smile
robert.gerald.taylor
robert.gerald.taylor
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1810 Visits: 1493
astrid 69000 (3/15/2013)
hi,

i have another question (or two) Smile

1) i want the mdx to always return the data from yesterday without choosing the data, can someone please tell me how to do it? everything i tried doesnt work Sad
2) also the mdx returns the measures for the data i asked on the first column, how do i go iif i want different dates on different columns to return the data

You can use the VBA function Now() to calculate yesterday like:

WITH MEMBER [Measures].[Yesterday] AS
""+ FORMAT(Now()-1,"MM/dd/yyyy") +""
SELECT {[Measures].[Yesterday]} ON COLUMNS
FROM [AdventureWorks];


I'm not sure what your second question is asking.

HTH,
Rob
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 200
thanks!
the second question was if i want different dates on different columns.
first column yesterday, the second column the day before yesterday, etc etc.

:-)
robert.gerald.taylor
robert.gerald.taylor
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1810 Visits: 1493
astrid 69000 (3/18/2013)
thanks!
the second question was if i want different dates on different columns.
first column yesterday, the second column the day before yesterday, etc etc.

I guess you could create a [yesterday], [two days ago], [three days ago], ect... separate calculated members. But that doesn't seem particularly efficient. I don't know a better way to do that.

Sorry,
Rob
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27703 Visits: 13268
If you process your cube daily, you could put a view on your time dimension.
The view simply adds a column that calculates for every day the relative position towards today.
For example, the column will read 'Today' for today's date (2013-03-20) and 'Yesterday' for 2013-03-19.
The next day, the column will display 'Yesterday' for 2013-03-20 and 'D - 2' for 2013-03-19 and so on.

This is very easy to implement in SQL.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 200
thanks to both of you...
i will check the calculated members.
i know i can add the dynamic time on sql (that one i actually know how to do lol), but the whole idea right now is to draw the report only from the cube using mdx and not using sql... w00t
i am planning to take a long weekend away from coding and next week start all over lol. :-D
Mackers
Mackers
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 499
You could create a set of dates using MDX

I believe in a previous post of yours DavosCollective suggested the following MDX for Yesterday

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



In order to get the last 14 days you could do the following


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



If you want all days prior to yesterday you could do

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


Mack
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 200
the strtomember worked perfectly when i changed the date with the dynamic date (someone pointed that out for me).
the problem that i encounter with that was when i needed to put together two dynamic type of dates.

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 [Measures].[VOID] As
CROSSJOIN({strToMember("[CHB Date].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-T00:00:00") +"]") },
{ [Chargebacks].[CHB_Tree].[CHB Type].&[IRS VOID] }), [Measures].[Purchase - converted to USD]


select strToMember("[Time].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-ddT00: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

Basically this part doesn't work.
if i change the strToMember, with a date that is not dynamic it does work, but since the report needs to be run automatly on daily basis, all dates need to be dynamic.

member [Measures].[VOID] As
CROSSJOIN({strToMember("[CHB Date].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-T00:00:00") +"]") },
{ [Chargebacks].[CHB_Tree].[CHB Type].&[IRS VOID] }), [Measures].[Purchase - converted to USD]

w00t
Mackers
Mackers
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 499
You are converting a set to a member which you can't do

Try

member [Measures].[VOID] As
(strToMember("[CHB Date].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-T00:00:00") +"]") , [Chargebacks].[CHB_Tree].[CHB Type].&[IRS VOID] , [Measures].[Purchase - converted to USD])

Mack
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 200
i tried that already and nop, doesnt work.
it doesnt the calculation but it doesnt bring the results, that is why i tried to go with the crossjoin.

i get '#Error'

Unsure well it can always be worse :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search