February 23, 2010 at 9:21 am
Hi,
I have got for example
Financial Year = '2008'
Month '01 April','02 may','03 June','04 July'
and I want to show this data for the last 12 months. So for example If Im in 11 February for Financial year 2009 I want it to show (Along the columns of a table)
2009
12 Mar, 01 April, 02 May, 03 Jun, 04 Jul, 05 Aug, 06 Sep, 07 Oct, 08 Nov, 09 dec, 10 jan,11 Feb
and next month it will change to
2009
01 April, 02 May, 03 Jun, 04 Jul, 05 Aug, 06 Sep, 07 Oct, 08 Nov, 09 dec, 10 jan,11 Feb,12 Mar
Similarly I want to show another grouping of the last 5 years of data so this financial year its
2005, 2006, 2007, 2008, 2009
and it will soon go to
2006, 2007, 2008, 2009, 2010
Im creating this by dragging and dropping the details in reporting services and Im imagining I need to add something into the dimension filter expression but Im not sure.
Has anyone got any advice of how to do this?
Thanks in advance
Debbie
February 26, 2010 at 7:19 pm
You could setup some named sets in the cube that you could reference that would contain the group of time periods that you would like to use. If you have a reference of the current day in your date dimension that you could utilize that attribute to get at the specific time periods that you are looking for and create named sets that you could then reference in your reports.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
March 1, 2010 at 1:56 am
Fantastic,
So I need to do a bit of research on sets in cubes? I dont suppose you would know of any literate or web pages that could give me some extra info on how to do this?
Debbie
March 1, 2010 at 6:50 am
Here are a couple of postings. The first one is by Mosha and talks about some options to get at Today's date. The second one is almost exactly the type of setup that you are looking to implement (with the exception of the time periods):
How to get the today's date in MDX
Using Named Sets in Analysis Services to Deliver Special Date Ranges to End Users
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
March 1, 2010 at 7:49 am
Excellent.
Thanks for that. Ill have a read 🙂
Debbie
March 2, 2010 at 3:42 am
Hello again,
Looked through the documentation and I still cant get it to work. Here is what I have done so far.
Right Clicked the cube to Open.
Gone to the calculations table Form View is defaulted.
Selected the new names set icon.
Entered the MDX (This is the bit I have probably got wrong. I don’t know how to test it or anything???)
filter([Dim Date Time].[Financial Year - Financial Month].Members,
[Dim Date Time].[Financial Year - Financial Month].CurrentMember.Properties("TimeID")
= Format(Now(), "yyyyMMdd"))
My Dimension is [Dim Date Time]
My Hierarchy is [Financial Year - Financial Month]
In the hierarchy I have the date set against the TimeID (Name Column – Source)
I do have a Date attribute in the dimension
I have checked the syntax and it says successful.
I then go to build and process (Full build)
Returned to the cube designer and the browser tab.
Clicked reconnect icon.
Added a fact and then expanded the data time hierarchy but that is as far as I get. The names set isn’t in their and I can’t find any other documentation that helps.
I have used the following…
The first link says
“Once you've added the member and deployed the cube, you can test it by browsing. Add the new named set to the filter, the Date dimension to the rows and add your default measure. You should see that the only member of the date dimension available in the grid is the current date.”
There is no name set here to apply to the filter.
If anyone has got any more advice that would be brilliant.
Thanks in advance
Debbie
March 2, 2010 at 4:10 am
Do you even have the Date member in the hierarchy to reference? It looks like you just have Year and Month based on the name. The TimeID field is most likely not a member property either, I would assume it is actually the Key attribute in the dimension.
For testing purposes you can just do this in SQL Server Management Studio by entering in something like the following in a new MDX query window:
with [CurrentDays] as filter([Dim Date Time].[Financial Year - Financial Month].Members,
[Dim Date Time].[Financial Year - Financial Month].CurrentMember.member_key
= Format(Now(), "yyyyMM"))
select measures.[<measure name here>] on 0,
[CurrentDays] on 1
from [<cube name here>]
One thing that you might want to take a look at installing is BIDS Helper out on CodePlex (http://bidshelper.codeplex.com). Once you have this installed one of the nice features it has on the calculations tab is the 'Deploy MDX Script' option. This allows you to push the calculation script out to the server (which ever one you have configured in the Deployment settings of the project) so that you can quickly test out your changes. These are all performed at runtime, so they do not require you to reprocess the entire cube.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
March 2, 2010 at 5:07 am
denglishbi (3/2/2010)
Do you even have the Date member in the hierarchy to reference? It looks like you just have Year and Month based on the name. The TimeID field is most likely not a member property either, I would assume it is actually the Key attribute in the dimension.For testing purposes you can just do this in SQL Server Management Studio by entering in something like the following in a new MDX query window:
with [CurrentDays] as filter([Dim Date Time].[Financial Year - Financial Month].Members,
[Dim Date Time].[Financial Year - Financial Month].CurrentMember.member_key
= Format(Now(), "yyyyMM"))
select measures.[<measure name here>] on 0,
[CurrentDays] on 1
from [<cube name here>]
I have now got date in the hierarchy and changed the MDX to
filter([Dim Date Time].[Financial Year - Financial Month].Members,
[Dim Date Time].[Financial Year - Financial Month].CurrentMember.Properties("Date")
= Format(Now(), "yyyyMMdd"))
and called the name set [Todays_date]
Looking at the above query with [CurrentDays] as filter .... does this mean I have to have the name set up and running. When I deploy I get this message...
Error -1056309245 : MdxScript(Registration Cube) (9, 5) The Date dimension attribute was not found.
March 2, 2010 at 5:19 am
Once again I don't belive that the Date is a member property, it is an attribute. If you are going to use the Properties reference it has to actually be a member property, not an attribute. In your case the Key column information is most likely what you want to reference here unless you actually have a property available under the Date attribute you can reference.
filter([Dim Date Time].[Financial Year - Financial Month].Members,
[Dim Date Time].[Financial Year - Financial Month].CurrentMember.member_key = Format(Now(), "yyyyMMdd"))
The reason you are getting the error is because you are trying to reference a member property that does not exist in your dimension.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
March 2, 2010 at 5:48 am
Sorry to be appearing dim but I am no where near as advanced as the solutions you have. Im a beginner at MDX but I know what attribues and members are again (I had forgotten) :w00t:
I have just used the last MDX query which ran fine.
In the cube browser I have just reconnected and hooray the named set is now there. Ive just tried a query and there is nothing there but I think that’s fine. I don’t have any registrations in on today's date yet. So if I continue through the example in the documentation hopefully I might be OK.Ill keep going with it and let you know.
Thanks for the help.
Debbie
March 2, 2010 at 6:34 am
Glad to hear. Please feel free to follow-up if you have any other questions or get stuck again.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
March 2, 2010 at 7:07 am
Well I knew It wouldn’t take long to get stuck again :doze:
Going through the documentation in http://my.opera.com/duncans/blog/using-custom-sets-in-analysis-services-to-deliver-special-date-ranges-to-end-use
After creating the first names set sorted out with your help (Thanks again) I have created the next named set [CalendarTodaySet] which does work. However the documentation doesn’t really explain what it is and why its been created apart from “it applies the result of the set to your main date hierarchy”
I create the next names sets in the list
[Current Year to This Month]
[CalendarTodaySet].ITEM(0).ITEM(0).Parent.Parent.FirstChild :
[CalendarTodaySet].ITEM(0).ITEM(0).Parent.CurrentMember
[Previous Year To This Month]
[CalendarTodaySet].ITEM(0).ITEM(0).Parent.Parent.Lag(1).FirstChild :
[CalendarTodaySet].ITEM(0).ITEM(0).Parent.CurrentMember.Lag(12)
Processing the cube I now get 2 errors
Error -1056309189 : MdxScript(Social Care Child Protection Registrations) (13, 5) The CURRENTMEMBER function expects a hierarchy expression for the 0 argument. A member expression was used.
Error -1056309189 : MdxScript(Social Care Child Protection Registrations) (16, 5) The CURRENTMEMBER function expects a hierarchy expression for the 0 argument. A member expression was used.
So they havent worked but I don’t understand enough about them to know why etc.
To make matters worse there is a bit in the documentation that says “assuming your calendar hierarchy is Year-Month-Date”
True but the members in my hierarchy are for example
[Financial Year].[2010]etc
To
[Financial Month].[01 April]etc
to
[Date].[2010-04-01 00:00:00] etc
I imagine the 01 April 02 May ect may cause problems but I cant find any information on what the above script does.
I guess Ill only realised whats actually been created when I get to the end and it works…
March 2, 2010 at 7:35 am
So their posting might not be entirely accurate...but it had the general idea. You would need to modify the other sets that they list so that they look like the following:
[Current Year to This Month]
[Todays_Date].ITEM(0).Parent.Parent.FirstChild :
[Todays_Date].ITEM(0).Parent
[Previous Year To This Month]
[Todays_Date].ITEM(0).Parent.Parent.Lag(1).FirstChild :
[Todays_Date].ITEM(0).Parent.Lag(12)
You don't need the second set that they displayed in the article either, you can reference your first one that you setup. Based on the above you can see that I removed the CurrentMember reference and one of the ITEM(0) references along with referencing your original set that you created with the filter.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
March 2, 2010 at 7:52 am
When you say second set, do you mean CalendarTodaySet ?
exists([Dim Date Time].[Financial Year - Financial Month].[Date], [Current_date]).ITEM(0).ITEM(0)
I didn’t understand this bit.
I now have the named sets Current_date, calendarTodaySet (These bother can be viewed in the date time dimension in the browser.)
I also have the newly working [Current year to this month] and [Pervious year to this month] These two don’t appear in the browser.
The only problem is the documentation doesn’t say what happens next. What do you do with these items. It says you can join them up but now how or anything. Im a little bewildered at this point.
March 4, 2010 at 4:39 am
Well Ive sorted it even if I have done it in a completely different way, staying away from MDX. Here is my novice style solution....
Thanks for all the help
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply