on 05-12-2010 1:03 PM
Hi,
Please help us how to build a Hierarchy based on particular values. i.e we need to create an hierarchy in universe level(for ex: Derived Table) and database is SQL Server.
hierarchy : Year->Quarter->month-> date
For date object the query is current date value. (this we can get)
Month -> It should be last day on of the day value in that particular month(i.e if jan is the month then we should get 31st value as month value)
Quarter -> It should be last day value of the quarter (i.e if quarter =2 then we should get quarter 2 value as june 30th value)
Year-> last day of the year as year value.
Thanks In Advance
Hi Suresh,
So you want to create a Hierarchy where you would be combining multiple dimensions such as Year > Quarter > Month > Date.
Create a new hierarchy from hierarchy editor, Tools > Hierarchies.
When hierarchy editor opens you will see all available objec in your universe on the left side of the pane.
You have to add required objects on the right side.
When you are creating new hierarchy you are creating new new folders in the custom hierarchy pane and then adding required dimensions.
Here are the steps...
1. From the Hierarchies editor, click the New button.
2. Type a name for the hierarchy.
3. Press RETURN to apply the name.
4. Select the new hierarchy.
The hierarchy is highlighted.
5. Expand a default hierarchy node in the left pane.
This is the hierarchy that contains dimensions that you want to add to the
new custom hierarchy.
6. Click a dimensions you want to add
Now come to your other issue for selecting last day of the month etc. For this you will have to write down formulas to achieve this functionality.
Regards,
Bashir Awan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Bashir,
Please go through my requirement clearly. I know how to create a hierarchy in universe level. Before creating hierarchy we need to create those objects in universe level. I got struck there.
i.e hierarchy : Year->Quarter->month-> date
Here I need how to write the queries for the below objects (my back end is sql server)
For date object the query is current date value. (this we can get)
Month -> It should be last day of the month in that particular month but if last date is null then it should be the day before last date and so on...(i.e if jan is the month then we should get 31st value as month value)
Quarter -> It should be last day value of the quarter but if last date is null then it should be the day before last date and so on (i.e if quarter =2 then we should get quarter 2 value as june 30th value)
Year-> last day of the year as year value but if last date is null then it should be the day before last date and so on.
Cheers,
Suresh Babu Aluri.
This thread over at the BOB forum should help you with the code for the relative dates.
http://www.forumtopics.com/busobj/viewtopic.php?t=152613
Please note that the objects will not parse when you build them because they do not reference a table.
However, they will work whenever you use them with an object that references a table.
These relative date objects are useful for building predefined conditions for scheduled reports - Sales Date between Start of Previous Month and End of Previous Month, for example, could be built once you have built the two corresponding relative date objects.
In terms of hierarchy, you would typically build a hierarchy against "physical" dates - those associated with facts - order date, order week, order month, order quarter, order year and so on.
I think you need to be clear on what you are trying to do because you seem to be confusing the two concepts.
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.