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»»

dates and columns Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 11:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:39 AM
Points: 53, Visits: 105
hi,

i have another question (or two) :)

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
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 :)
Post #1431861
Posted Monday, March 18, 2013 6:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:50 AM
Points: 1,234, Visits: 1,273
astrid 69000 (3/15/2013)
hi,

i have another question (or two) :)

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
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
Post #1432169
Posted Monday, March 18, 2013 6:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:39 AM
Points: 53, Visits: 105
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.

Post #1432176
Posted Wednesday, March 20, 2013 2:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:50 AM
Points: 1,234, Visits: 1,273
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
Post #1433501
Posted Wednesday, March 20, 2013 3:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1433508
Posted Thursday, March 21, 2013 12:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:39 AM
Points: 53, Visits: 105
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...
i am planning to take a long weekend away from coding and next week start all over lol.
Post #1433606
Posted Thursday, March 21, 2013 2:05 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
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
Post #1433621
Posted Thursday, March 21, 2013 2:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:39 AM
Points: 53, Visits: 105
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]


Post #1433635
Posted Thursday, March 21, 2013 3:05 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
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
Post #1433644
Posted Thursday, March 21, 2013 3:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:39 AM
Points: 53, Visits: 105
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'

well it can always be worse
Post #1433679
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse