InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 16:32:37 GMT20http://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspxyes 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.Thu, 21 Mar 2013 04:22:47 GMTastrid 69000http://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspxThe syntax looks fineIt 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?MackThu, 21 Mar 2013 04:00:24 GMTMackershttp://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspxi 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 :-)Thu, 21 Mar 2013 03:49:27 GMTastrid 69000http://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspxYou are converting a set to a member which you can't doTrymember [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])MackThu, 21 Mar 2013 03:05:43 GMTMackershttp://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspxthe 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] Asmax({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])member [Measures].[Min] Asmin({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])member [Measures].[Avg] Asavg({[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:Thu, 21 Mar 2013 02:38:51 GMTastrid 69000http://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspxYou could create a set of dates using MDXI believe in a previous post of yours DavosCollective suggested the following MDX for Yesterday[code="other"]StrToMember("[Time].[Year - Month – Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-ddT00:00:00") +"]")[/code]In order to get the last 14 days you could do the following[code="other"]{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") +"]")}[/code]If you want all days prior to yesterday you could do[code="plain"]{null:StrToMember("[Time].[Year - Month – Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-ddT00:00:00") +"]")}[/code]MackThu, 21 Mar 2013 02:05:04 GMTMackershttp://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspxthanks 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. :-DThu, 21 Mar 2013 00:52:01 GMTastrid 69000http://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspxIf 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.Wed, 20 Mar 2013 15:31:20 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspx[quote][b]astrid 69000 (3/18/2013)[/b][hr]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.[/quote]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,RobWed, 20 Mar 2013 14:56:21 GMTrobert.gerald.taylorhttp://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspxthanks!the second question was if i want different dates on different columns.first column yesterday, the second column the day before yesterday, etc etc.:-)Mon, 18 Mar 2013 06:54:32 GMTastrid 69000http://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspx[quote][b]astrid 69000 (3/15/2013)[/b][hr]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[/quote]You can use the VBA function Now() to calculate yesterday like:[code="plain"]WITH MEMBER [Measures].[Yesterday] AS ""+ FORMAT(Now()-1,"MM/dd/yyyy") +""SELECT {[Measures].[Yesterday]} ON COLUMNSFROM [AdventureWorks];[/code]I'm not sure what your second question is asking.HTH,RobMon, 18 Mar 2013 06:34:52 GMTrobert.gerald.taylorhttp://www.sqlservercentral.com/Forums/Topic1431861-17-1.aspxhi,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 datathanks in advance :)Fri, 15 Mar 2013 23:00:10 GMTastrid 69000