March 15, 2013 at 11:00 pm
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 🙂
March 18, 2013 at 6:34 am
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
March 18, 2013 at 6:54 am
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.
🙂
March 20, 2013 at 2:56 pm
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
March 20, 2013 at 3:31 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 21, 2013 at 12:52 am
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. 😀
March 21, 2013 at 2:05 am
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
March 21, 2013 at 2:38 am
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:
March 21, 2013 at 3:05 am
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
March 21, 2013 at 3:49 am
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 🙂
March 21, 2013 at 4:00 am
The syntax looks fine
It will be a problem with a member - best double check that the members of the tuple are correct e.g. is [Chargebacks].[CHB_Tree].[CHB Type].&[IRS VOID] correct?
Mack
March 21, 2013 at 4:22 am
yes it is, i just double checked it.
maybe it has to do with the fact that i am formatting the dynamic date of the chb even though i dont need the actual date written anywhere.
i did try
member [Measures].[VOID] As
(strToMember("[CHB Date].[Year - Month - Period - Date].[Date].&[]"), [Chargebacks].[CHB_Tree].[CHB Type].&[IRS VOID], [Measures].[Purchase - converted to USD])
without the format, but i also get an error.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply