Hello all,
I would like to run billing reports monthly through doc agent. This should all be automated. The problem would be with the dates. Even if I tell Doc Agent to run this report every month, how do I tell the report to change the billing dates as well in the conditions? Thanks you guys.
Neil Buranakanchana
SPS Payment Systems
Listserv Archives (BOB member since 2002-06-25)
Neil,
The easiest way to do this would be to hard code the date information in the SQL. Such as
db.billingdate between (sysdate-1)-30 and (sysdate-1)+1 This would give you the billing dates from the last 30 days, against Oracle, if it is DB2 use current date vs sysdate.
Hope this helps.
Debra
Hello all,
I would like to run billing reports monthly through doc agent. This should all be automated. The problem would be with the dates. Even if I tell Doc Agent to run this report every month, how do I tell the report to change the billing dates as well in the conditions? Thanks you guys. Neil Buranakanchana
SPS Payment Systems
Listserv Archives (BOB member since 2002-06-25)
you could create a table that contains a field with ‘first day of month’ and one for ‘last day of month’. then update that when the data is loaded for the new month. you would then create objects that select this data.
your conditions for pulling the current month’s data should something like this:
where Billing date >= first day of month and Billing date <= last day of month
the cells on the report that contain the ‘from’ and ‘to’ dates can use to this info also.
frank b. duncan
decision support specialist
national city corp.
______________________________ Reply Separator _________________________________
Author: Business Objects Query Tool BUSOB-L@LISTSERV.AOL.COM at ~internet
Date: 12/2/98 1:41 PM
Hello all,
I would like to run billing reports monthly through doc agent. This should all beautomated. The problem would be with the dates. Even if I tell Doc Agent to
run this report every month, how do I tell the report to change the billing dates as ell in the conditions? Thanks you guys.
Neil Buranakanchana
SPS Payment Systems
Listserv Archives (BOB member since 2002-06-25)
Debra,
thanks for your help! is there a way to have the dates fall exactly within the months. because as you know months are not always exactly 30 days. is there a way to do if/then type stuff? thanks
Listserv Archives (BOB member since 2002-06-25)
Well this is where it gets kind of tricky. You have to kinda outsmart the SQL… If you are running a report that shows say, sales from the prior month, and you always run this report on the first day of the month:
db.colname between last_day(last_day(sysdate-1)-40)+1 and (sysdate-1) This gives you the following
Date July 1st reporting period June 1- June 30 (1999)
sysdate-1 is June 30, 1999
last_day is June 30, 1999
and
sysdate-1 is June 30,1999
Let’s try it for March 1st reporting period Feb 1- Feb 28 (1999)
sysdate -1 is Feb 28,1999
last_day is Feb 28, 1999
It really all depends on what time period you are consistantly running and when it will run. If you need more assistance I would be more than glad to do so. If you want to email me your reporting period information I could probably whip up the formula for you today.
Thanks,
Debra
NBURANA@SPSPAY.COM on 12/02/98 02:33:39 PM
Please respond to BUSOB-L@LISTSERV.AOL.COM
cc: (bcc: Debra Ann Braun/Frito-Lay/US)
Debra,
thanks for your help! is there a way to have the dates fall exactly within the months. because as you know months are not always exactly 30 days. is there a way to do if/then type stuff? thanks --------------------------------------------------------------------------- Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (9am-5pm ET only): listserv.aol.com/archives/busob-l.html
OR 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)
I would set the date check up in a condition in the universe, and apply that condition to all billing reports.
If you are using Oracle, there are some other date functions you can use that Debraann did not mention:
add_months(sysdate - 1) returns the date exactly one month ago. trunc(sysdate, ‘MM’) returns the first day of the current month. last_day(sysdate) returns the last day of the current month.
The following query will select everything dated in the previous month:
select *
from <my_table> t
where t.<date_column>
between trunc(add_months(sysdate,-1),‘MM’) and to_date(to_char(last_day(add_months(sysdate,-1)),‘MM/DD/YYYY’)||’ 23:59.59’,‘MM/DD/YYYY hh24:mi.ss’)
Maybe you can find a better way to represent the ending date, but it is structured this way for a reason. If the dates in your billing records have a time value, your query could miss some records if the time value of the ending date is not maximized (date comparisons do include the full value of the date, including time). Truncating the date in your records will also work, but if the date column is indexed and you use the TRUNC function on it in your query, you will take a serious performance hit.
Steven Jones
Consultant to BT Office Products International sjones@btopi.com
Listserv Archives (BOB member since 2002-06-25)