My current client uses a calendar different from the BO calendar, not to mention that our data is offset by a week. I’ve been slamming my head against the wall trying to figure out how to filter data based on the dates needed. Mainly, Current Week and Current Month. Doing a MonthNumber(CurrentData()) doesn’t work because of the calendar differences. I added a second data provider bringing back the max values from the main data fact table but it’s not working correctly and I can’t add a multicube because it’s not a dimension.
I’m trying to display
Current Quarter
DATA
Current Month
DATA
Current Week
DATA
Anyone have thoughts how how to accomplish this? Can I load a custom calendar in to BO somehow? My searches came up dry…
The way to accomplish this is to modify the universe using Business Objects Designer: include a Calendar table (which has to be built in the database) joined to your fact table(s).
I’ve done that, but when I try to set a filter to see only the data for a specific time frame it doesn’t work.
Example:
In the BO Calendar April 1st is the first day of the second Quarter
But in the clients Calendar March 29th is the first day of the Second Quarter.
I tried to set up a second data provider that brought back the max Qtr and Month for the given date in the clients calendar for the given day, but for some reason it’s not doing what I expected it to do. It’s linked correctly to the other DP… I’m at a loss
Quarter(ToDate('03/29/2004')) the result is 1 which is incorrect in the case of the client's calendar, it should be 2
It is 2 in the Fiscal Calendar table we have set up but I am having a difficult time getting that value in to the report becasue you can’t filter on an aggregate i.e. Max(Fiscal_Qtr)
Again:
If you setup your own custom calendar table in the database, see my previous example, join it to your fact table, and finally create universe objects such as “Financial Quarter” etc. (all within Business Objects Designer) you should be easily able to accomplish what you are after.
The problem is that in the query I bring back All of the current Quarter’s data and on the report page, I have 3 cross tabs.
One for the current quarter by Region (no filter)
One for the current Month by Region (Current Month Only filter)
One for the current Week by Region (Current Week only filter)
In a second data provider I am bringing back the Current Month and Week, but when I try to you that in a Boolen filter (If Current Quarter = Quarter then 1 else 0) it doesn’t work. I thinks that every week/month is the current. When I unlink the data providers, it identifies the current month/week correctly, but the filters don’t work (obviously)
No other solution, but I had a nice calendar example in the presentation I gave at the BO conference. You should take a look. Get it from tech support: “Empowering End Users through Smart Universe Design”
Are the dates for the beginning and end of a quarter always the same? Like your example, is March 29th always quarter 2?
If it is you can create an Object in the Universe using some datemath and get what you want.
Dates and date math aren’t that hard and even though a calander tables works, I really think that is over the top for finding current and previous quarters unless you are on a really specialized calander system.
(BOB member since 2003-08-28)
(BOB member since 2002-06-20)
(BOB member since 2002-06-20)