automatic parameter selection

building semantic layer
1

I have a dilemma I am seeking suggestions on…

In the health care industry, each enrollment period is regulated by the government (called production periods). Because of this, all of our reporting is done by production period. What we want to do is to have reports automatically run for current production month to date. For example, if the production period is 6/26/98-7/27/98, on 7/13, if the report is run, we want it to automatically detect the beginning and ending parameters based on the current date.

I am looking as to ways to do this. Obviously I will need a table in my database, but what are some good ways to build this into the universe and into the reports?

I am thinking this table structure:

current_date begin_date end_date
======== ======= ======
6/26/98 6/26/98 7/27/98
6/27/98 6/26/98 7/27/98
6/28/98 6/26/98 7/27/98
…
7/27/98 6/26/98 7/27/98
7/28/98 7/28/98 8/26/98
7/29/98 7/28/98 8/26/98
…

Basically, I want the report to do this: When the report is run, it looks at the current date, goes to this table, picks the beginning and ending dates and puts them into the beginning and ending date parameters for the report.

Any ideas?

Thanks,

Tim

Tim Heuer
PacifiCare Health Systems
(714) 825-5702 - office
(800) 946-4645 pin 1404017 - pager
heuer_tk@exchange.phs.com

Listserv Archives (BOB member since 2002-06-25)

2

Tim,

Here is a suggestion that was used at a company I used to work for.

Add a field (prod_period) into your calendar table structure as indicated below.

current_date begin_date end_date prod_period
============ ========== ======== ===========
6/24/98 5/24/98 6/25/98 1
6/25/98 5/24/98 6/25/98 1
…
6/26/98 6/26/98 7/27/98 0
6/27/98 6/26/98 7/27/98 0
6/28/98 6/26/98 7/27/98 0
…
7/27/98 6/26/98 7/27/98 0
7/28/98 7/28/98 8/26/98 -1
7/29/98 7/28/98 8/26/98 -1
…

Simply join all of your fact tables to this calendar table by current_date.

You can create condition objects for Current Period (prod_period=0), Last Period (prod_period=1), Two Periods Ago (prod_period=2) … Likewise, if you need to report on future periods, those prod_period values will be -1, -2, … By using these condition objects your reports will automatically be updated when the prod_period changes.

To change the prod_period you will have to create an update script that runs at the beginning of each period to update the prod_period field. It is a simple update SQL statement to increment prod_period by 1:

update calendar
set prod_period = prod_period + 1;

You can even get really clever and set up a script that can run daily to check to see if it should automatically run the update to the calendar table.

Hope this helps. Call me if you would like more clarification.

Regards,

Paul Mathern
Maxtor Corp.
303-702-4333
paul_mathern@maxtor.com

=========================
Sent by:“Heuer Tim K” Heuer_TK@EXCHANGE.PHS.COM
I have a dilemma I am seeking suggestions on…

In the health care industry, each enrollment period is regulated by the government (called production periods). Because of this, all of our reporting is done by production period. What we want to do is to have reports automatically run for current production month to date. For example, if the production period is 6/26/98-7/27/98, on 7/13, if the report is run, we want it to automatically detect the beginning and ending parameters based on the current date.

current_date begin_date end_date prod_period
============ ========== ======== ===========
6/24/98 5/24/98 6/25/98 1
6/25/98 5/24/98 6/25/98 1
…
6/26/98 6/26/98 7/27/98 0
6/27/98 6/26/98 7/27/98 0
6/28/98 6/26/98 7/27/98 0
…
7/27/98 6/26/98 7/27/98 0
7/28/98 7/28/98 8/26/98 -1
7/29/98 7/28/98 8/26/98 -1
…

Listserv Archives (BOB member since 2002-06-25)

3

I am assuming an Oracle database here. If you aren’t using Oracle, you’ll have to try something else.

First of all, your table doesn’t need to have three fields. You only need two: BEGIN_DATE and END_DATE:

BEGIN_DATE END_DATE
6/26/98 7/27/98
7/28/98 8/26/98
…

Why don’t you need CURRENT_DATE? Read further…

Suppose you have a table, T1, that has a TRANS_DATE field. Suppose your BEGIN_DATE/END_DATE table is called PROD_PD (PRODuction PerioD). First, create a join between these two tables:

T1.TRANS_DATE Between PROD_PD.BEGIN_DATE And PROD_PD.END_DATE (1)

Second, build a Condition Object, say, “Current Production Period”, off of PROD_PD with the following criteria:

SYSDATE Between PROD_PD.BEGIN_DATE And PROD_PD.END_DATE (2)

(This is why you only need two fields in PROD_PD, and don’t need the CURRENT_DATE field. Your tendency is to have three fields and define the above criteria as SYSDATE = PROD_PD.CURRENT_DATE. But with the above criteria, you don’t need that third field and you’ll achieve the same results.)

You’re now done in Designer. Switch over to the reporter and build your query. Add your Current Production Period condition. Because you added this condition object to your query, the SYSDATE criteria (2) is included in the where clause. And since you now have objects that refer to two tables, T1 and PROD_PD, the join between those two tables (1) is also included in the where clause.

Try it!

(NOTE: PROD_PD with two fields is a very small table, so you don’t really need to build an index on it. Even if you have ten years documented in that table, that’s only 120 rows. Assuming your system uses 2 bytes to store a date, that’s 4 bytes for a whole record, or 480 bytes for the whole table. Your system would probably read it in a single disk I/O. PROD_PD with three fields, however, would have 3,650 or so rows for ten years’ worth of data, taking about 22 KB for the whole table. You’ll want to index it on CURRENT_DATE.)

Erich Hurst
Compaq Computer Corporation
(281) 514-9445
Erich.Hurst@Compaq.com

[…] For

example, if the production period is 6/26/98-7/27/98, on 7/13, if the
report is run, we want it to automatically detect the beginning and ending parameters based on the current date.

I am looking as to ways to do this. Obviously I will need a table in my
database, but what are some good ways to build this into the universe
and into the reports?

I am thinking this table structure:

current_date begin_date end_date
======== ======= ======
6/26/98 6/26/98 7/27/98
6/27/98 6/26/98 7/27/98
6/28/98 6/26/98 7/27/98
…
7/27/98 6/26/98 7/27/98
7/28/98 7/28/98 8/26/98
7/29/98 7/28/98 8/26/98
…

Listserv Archives (BOB member since 2002-06-25)

4

In a message dated 98-07-16 12:51:16 EDT, you write:

report is run, we want it to automatically detect the beginning and
ending parameters based on the current date.

I am looking as to ways to do this. Obviously I will need a table in my database, but what are some good ways to build this into the universe and into the reports?

I am thinking this table structure:

current_date begin_date end_date
======== ======= ======
6/26/98 6/26/98 7/27/98
6/27/98 6/26/98 7/27/98
6/28/98 6/26/98 7/27/98
…
7/27/98 6/26/98 7/27/98
7/28/98 7/28/98 8/26/98
7/29/98 7/28/98 8/26/98
…

Basically, I want the report to do this: When the report is run, it looks at the current date, goes to this table, picks the beginning and ending dates and puts them into the beginning and ending date parameters for the report.

Any ideas?

Thanks,

Tim

Tim:

If you are going to the trouble of building a table, then why not just join to that table in your universe design. You could build a Filter Object (Predefined Condition) that links to the begin / end date table using the system current date. No script - no fuss! Just a little bit of SQL code that goes in to each report.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com See you in Orlando in '98!

Listserv Archives (BOB member since 2002-06-25)

5

why not write a database function that takes the current date as an input parameter together with a parameter stating whether you want the start or end date for the period. The function can then return the period start/end date from your table. Base two objects on the function (one for start and the other for end). This is a very quick and easy solution and you don’t need to worry about putting a new table into your Universe design.

Regards

Jonathan

Project Leader
Global Medical IS
jc14547@glaxowellcome.co.uk

From: Heuer Tim K [SMTP:Heuer_TK@EXCHANGE.PHS.COM] Sent: 15 July 1998 17:52

I have a dilemma I am seeking suggestions on…

In the health care industry, each enrollment period is regulated by the
government (called production periods). Because of this, all of our reporting is done by production period. What we want to do is to have reports automatically run for current production month to date. For example, if the production period is 6/26/98-7/27/98, on 7/13, if the report is run, we want it to automatically detect the beginning and ending parameters based on the current date.

I am looking as to ways to do this. Obviously I will need a table in my
database, but what are some good ways to build this into the universe and into the reports?

I am thinking this table structure:

current_date begin_date end_date
======== ======= ======
6/26/98 6/26/98 7/27/98
6/27/98 6/26/98 7/27/98
6/28/98 6/26/98 7/27/98
…
7/27/98 6/26/98 7/27/98
7/28/98 7/28/98 8/26/98
7/29/98 7/28/98 8/26/98
…

Basically, I want the report to do this: When the report is run, it looks at the current date, goes to this table, picks the beginning and ending dates and puts them into the beginning and ending date parameters
for the report.

Any ideas?

Thanks,

Tim

Tim Heuer
PacifiCare Health Systems
(714) 825-5702 - office
(800) 946-4645 pin 1404017 - pager
heuer_tk@exchange.phs.com

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
For old 3.1 info:
Penn: Page not found
Search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

Listserv Archives (BOB member since 2002-06-25)

6

Thanks for the replies for my scripting problems…

Here is some more information:

I am using MS SQL Server
I do not want to build the table into the universe, because then I have to join the date with any dates that individuals may report off of. Using some other form, I would not have to do this, it would merely put the value in whatever date they selected for the parameter.

Due to our change control process at my company, it would take months to implement any database/server side programming. If I can do it on the user end, nobody would no the difference.

I have figured out how to eliminate the time value using SQL Server’s getdate() function, although it is a little messy.

The report/script has to be built that no matter when it is run (without user intervention) the report will be refreshed for the current production month to date.

What I am doing now is having the first portion of the script get the date value and put it in a text file. The second portion opens that file and reads the date and places it in the application variable. I would rather have the result of the first script go directly into the variable, but I am unable to figure this out.

Tim

Listserv Archives (BOB member since 2002-06-25)

7

Just an idea of a VERY slight chance…

Do these ranges follow a mathematical pattern, i.e. start end end date of week etc.

If so you could do something like… (please excuse pseudocode)

start = (date_to_int(date) DIV 7) * 7
end = start + 6

Just a though… probably no chance of it being that simple!

Phil Morris
Analyst Programmer
BITS dept.
Tarmac Heavy Building Materials UK Ltd.
PO Box 8
Ettingshall
Wolverhampton
West Mids
WV4 6JP

Tel: 01902 382183
Email: pimorris@tarmac.demon.co.uk

“My own opinions are not necessarily those of my employer…”

Listserv Archives (BOB member since 2002-06-25)

8

Tim,

I think the structure you are suggesting (if I understand correctly) would have an entry for every single date, containing the period that that date is in. I’d suggest that this will be a nightmare in terms of storage (a record for EVERY day), maintainance and also is redundant; you can detect the period from the current date because it lies between the begin and end !

I am thinking this table structure:

current_date begin_date end_date
======== ======= ======
6/26/98 6/26/98 7/27/98
6/27/98 6/26/98 7/27/98
6/28/98 6/26/98 7/27/98

How about:

period table

begin date end date
========== ========
6/26/98 7/27/98
7/28/98 8/29/98

Then use this table as a predefined condition in your universe (sysdate between period.begin_date and period.end_date).

I see David has suggested this - but I think the point about the table structure is relevant.

Regards,

Paul

Listserv Archives (BOB member since 2002-06-25)

9

Sent: 17. hein”kuuta 1998 18:20

What I am doing now is having the first portion of the script get the date value and put it in a text file. The second portion opens that file and reads the date and places it in the application variable. I would rather have the result of the first script go directly into the variable, but I am unable to figure this out.

Tim,

Desiger:
MyStuff.Date => @Script(‘StartDate’, ‘D’, ‘DateMachine’) AND
MyStuff.Date <= @Script(‘EndDate’, ‘D’, ‘DateMachine’)

ReportScript:
Sub main /* in script file “DateMachine” in “Scripts” directory /
application.variables.item(“StartDate”).value = / Enter complex date stuff1 here /
application.variables.item(“EndDate”).value = / Enter complex date stuff2 here */
End sub

Disclaimer:
I have not tried this. I just modified examples from Designer Help and Reporter Help, both on @script function.

You may run into “challenges” with the “Date” data type, but i guess you can get them as strings from @Script and then use an SQL server function to convert them back into dates.

HTH,

Harri Kinnunen
Consultant / BusinessObjects
Enator Group, Finland
hkinnunen@iki.fi

Listserv Archives (BOB member since 2002-06-25)