Trying again - calendar table

building reports desktopintelligence
1

I posted a day or so ago regarding the above and although I was helped by a member here no satisfactory answer was obtained.

I have a dataset which includes an ID field, a start date and an end date. Start and end dates are in different fields within the dataset.

Also, I have created a calendar table which includes a date and a column that contains a 1 or a 0 to allow a sum to be calculated and, if needed, a field that contains a Y or an N to indicate if the record should be included or not.

So my problem is as follows

I need, for each ID, to calculate the number of days (excluding holidays and weekends) between the start and end date for each record.

Could anyone explain how I can achieve this ?

The suggestion was to use sum(countfield) where include = Y

If I don’t link my calendar table and do a sum it will return all records giving me a result of over 9500. If I link the calendar table, and I can only link on either the start or end date, the result is around 2200.

Can someone please just let me know if it is possible to achieve what I need to do in BO itself or do I need to have something in place in either the universe or database itself ?

dc8 :uk: (BOB member since 2009-07-10)

2

Just to establish a few things.

1/ What version of BusinessObjects are you using?

2/ Where does the dataset come from - a universe, a SQL statement, a spreadsheet?

3

You have now split your discussion:-

https://bobj-board.org/t/139422

Mak 1 :uk: (BOB member since 2005-01-06)

4

Probably better closing the other one, though, given that it went nowhere.

5

Hi Mark P

Using BO 6.5 SP4

Main data is obtained from a Universe.

Calendar table currently is in an Excel spreadsheet as a PDF. I also have an Access version. If required, the calendar data can be placed in either the universe of the database itself.

C

dc8 :uk: (BOB member since 2009-07-10)

6

Ideally the calendar table should be in the database and subsequently in the universe. This will make your calculations easy. You can then use a between join in your universe and create an object for your count as Sum(Calendar_Table.Working_Days)

What sort of database is it - Oracle, Sybase, SQL Server?

7

Mark,

OK. I’ll talk to our database guy and get him to add to database and universe.

Database in use is Progress.

C

dc8 :uk: (BOB member since 2009-07-10)

8

Hi Mark,

Unsure whether this approach would work, have a look at the other post…:slight_smile: !

Unfortunately I have only done this in SQL Server, haven’t ever seen progress!

Mak 1 :uk: (BOB member since 2005-01-06)

9

My Progress syntax is a bit rusty (2 years!) so please bear with me.

Who will be making the universe changes, the DBA?

10

It will be our DBA.

I’ve only been using BO and the whole company system for about 6 weeks so you can possibly understand my lack of knowledge.

C

dc8 :uk: (BOB member since 2009-07-10)

11

You will not get this to work with Excel as a data source because the only join mechanism you have between two data providers is “equals”. You need a between in order to get the range between your two dates.

There is a white paper posted that provides the details to do exactly what you need, although it’s written for Oracle.

Creating / Using Oracle Stored Functions in Business Objects

The document details how to build your calendar table and a very simple stored function that you can create and then call in your universe in order to do what you want. Even if the exact same syntax is not available in your database it should provide your DBA with enough information to get started.

Dave Rathbun :us: (BOB member since 2002-06-06)

12

Hi Dave,

When we did this we resolved the count of Monday to Friday days between two different date columns using SQL.

I posted it here:-

https://bobj-board.org/t/139422

then subtracted a seperate, simple, DB function to calculate the holidays to subtract.

We found this performed better than doing everything within a function, but that was using SQL Server.

When first thinking about this problem, you think you could use a holiday flag in your calendar which is fine if you are just looking at counting dates in the one column.

It gets trickier when you are trying to compare a start and an end date which are contained within different columns i.e going crossways rather than downwards :crazy_face: .

Thanks for the Oracle example though, I’ll store it with all my other “stuff”…:mrgreen:

Mak 1 :uk: (BOB member since 2005-01-06)

13

DBA is now putting calendar table into universe so will hopefully be able to test this soon.

In the meantime, I have created an Access database and this is the code that returns EXACTLY the results I am looking for

SELECT 
   Patients.[Patient Number], Patients.[Booking Date], Patients.[Appointment Date], Sum(Calendar.[countflag]) AS wait
FROM 
   Patients, Calendar
WHERE 
   (((Calendar.datevalue) Between [Booking Date] And [Appointment Date]))
GROUP BY 
   Patients.[Patient Number], Patients.[Booking Date], Patients.[Appointment Date]
ORDER BY 
   Patients.[Booking Date], Patients.[Appointment Date];

This took me about 10 minutes in Access which actually includes the time taken to extract the data from BO, import the data and create the query.

Is it possible to recreate this via BO ?

[Moderator Edit: Added code formatting - Andreas]

dc8 :uk: (BOB member since 2009-07-10)

14

Mak, the document listed above uses a calendar table with holidays already processed, and does a “count distinct” dates between to source dates. It’s quite efficient and does not require any math at all.

dc8, you can try creating a derived table that includes your logic but it would have to join to your other source table (using the booking date and appointment date) to get the date range.

Ultimately the best choice would be to run a nightly script that does this calculation and stores it in the database so you can simply retrieve it, rather than calculating it over and over and over… :wink:

Dave Rathbun :us: (BOB member since 2002-06-06)