Hi. My office just started using Business Objects (along with Oracle 8.1)...and since somebody recalled that I had a SQL course awhile back I was elected to create some reports. Even though I have never used Business Objects before...I was able to putz around and come up with some real clunky things so I figured I would post here and maybe get some better stuff. Actually I believe if I just get some good SQL code I can make it work in Business Objects...So anyway here goes...
An authentication server sends info to a table call "Monthly" consisting of the following fields I need for the reports (in addition to a whole lot of fields I don't need):
TransTime (Varchar2)
Duration (Varchar2)
Address (Varchar2)
RecordType (Varchar2)
Each connection session will write two separate records.
TransTime comes in in the format DDD MMM YYYY HH:MM:SS for both connection and termination records of each session.
Duration comes in as a 0 in the connection record of each session to indicate the start of the timer and then again in the termination record of each session as the total number of seconds the user spent connected.
Address comes in as an IP address XXX.XXX.XXX.XXX for both connection and termination records (of which there are only two possible addresses which can be connected to and subsequently terminated from...obviously both of which are the same for an individual session)
RecordType comes in as a 1 in the connection record to indicate the Start of the connection and then as a 2 in the termination record to indicate the termination of the connection.
So for instance when a user connects the connection record would be written as:
RecordType = 1
TransTime = DDD MMM YYYY HH:MM:SS
Address = XXX.XXX.XXX.XXX
Duration = 0
When the user disconnects the termination record would be written as:
RecordType = 2
TransTime = DDD MMM YYYY HH:MM:SS
Address = XXX.XXX.XXX.XXX
Duration = SSSSSSSSSSSSSSS (depending on how many seconds the user was connected)
Note: I'm not sure why both RecordType and Duration are used when I believe Duration only would accomplish what I need (but that's my uninformed opinion)
Anyway...here is what I need to report:
Number of sessions per day per IP address in a weekly report
Number of sessions per month per IP address in a monthly report
Average duration of all sessions per IP address in a weekly report
Not to put another twist on it...the total time spend connected (Duration) is written in the number of seconds connected as mentioned above. Of course the powers-to-be want the report to indicate the average duration to be in the format of HH:MM:SS.
A little more information (for anybody still reading this)...at the end of the calendar month the contents of table "Monthly" will be written to a second table called "Archive" and "Monthly" will be purged and start over empty. I foresee a problem with my limited knowledge on how I go about getting the weekly reports after the first week's data has been posted. So if anybody can keep this in mind and put me in the right direction also it would be greatly appreciated.
Ok...that's if for this long-winded plea for assistance and thanks in advance for anybody who takes the time to help me out.
An authentication server sends info to a table call "Monthly" consisting of the following fields I need for the reports (in addition to a whole lot of fields I don't need):
TransTime (Varchar2)
Duration (Varchar2)
Address (Varchar2)
RecordType (Varchar2)
Each connection session will write two separate records.
TransTime comes in in the format DDD MMM YYYY HH:MM:SS for both connection and termination records of each session.
Duration comes in as a 0 in the connection record of each session to indicate the start of the timer and then again in the termination record of each session as the total number of seconds the user spent connected.
Address comes in as an IP address XXX.XXX.XXX.XXX for both connection and termination records (of which there are only two possible addresses which can be connected to and subsequently terminated from...obviously both of which are the same for an individual session)
RecordType comes in as a 1 in the connection record to indicate the Start of the connection and then as a 2 in the termination record to indicate the termination of the connection.
So for instance when a user connects the connection record would be written as:
RecordType = 1
TransTime = DDD MMM YYYY HH:MM:SS
Address = XXX.XXX.XXX.XXX
Duration = 0
When the user disconnects the termination record would be written as:
RecordType = 2
TransTime = DDD MMM YYYY HH:MM:SS
Address = XXX.XXX.XXX.XXX
Duration = SSSSSSSSSSSSSSS (depending on how many seconds the user was connected)
Note: I'm not sure why both RecordType and Duration are used when I believe Duration only would accomplish what I need (but that's my uninformed opinion)
Anyway...here is what I need to report:
Number of sessions per day per IP address in a weekly report
Number of sessions per month per IP address in a monthly report
Average duration of all sessions per IP address in a weekly report
Not to put another twist on it...the total time spend connected (Duration) is written in the number of seconds connected as mentioned above. Of course the powers-to-be want the report to indicate the average duration to be in the format of HH:MM:SS.
A little more information (for anybody still reading this)...at the end of the calendar month the contents of table "Monthly" will be written to a second table called "Archive" and "Monthly" will be purged and start over empty. I foresee a problem with my limited knowledge on how I go about getting the weekly reports after the first week's data has been posted. So if anybody can keep this in mind and put me in the right direction also it would be greatly appreciated.
Ok...that's if for this long-winded plea for assistance and thanks in advance for anybody who takes the time to help me out.
