Manual Crosstab Report with utilizing date ranges 1

[oanion]

Please bear with me on an explanation of what I’m trying to accomplish.

My is very strict on the number of licenses being using for a particular application. Therefore they need reports with call statistics. This particular report is supposed to determine the maximum number of licenses used on a specific server, for a specific application, on a specific day. Once the maximum number of license is determined, the corresponding information, time and date, should be displayed. The fields in the database that are used are the following:

Server - Server name
Date – Corresponding date
Time – Corresponding time
App1 - Number of license used for App1
App2 - Number of license used for App2

The format should be as follows:
8/1/06 (Page1)
Peak Utilization Peak Date/Time
App1 App2 App1 App1 App2 App2
Server1 max license max license RelatedDate RelatedTime RelatedDate RelatedTime
Server2 max license max license RelatedDate RelatedTime RelatedDate RelatedTime
Server3 max license max license RelatedDate RelatedTime RelatedDate RelatedTime


Peak Utilization Peak Date/Time

8/2/06 (Page2)

App1 App2 App1 App1 App2 App2
Server1 max license max license RelatedDate RelatedTime RelatedDate RelatedTime
Server2 max license max license RelatedDate RelatedTime RelatedDate RelatedTime
Server3 max license max license RelatedDate RelatedTime RelatedDate RelatedTime


There are date parameters which will determine the number of days the information will be shown. Dates can range from 1 day to numerous days. For each day, a page needs to be printed with information for that particular day. For instance, if the date range is for 08/01/06 to 08/02/06, there should be 2 pages. Each page should show two sections, Peak Utilization and Peak Date time. The Peak Utilization section displays the maximum license for a specific server for a specific application. The Peak Date/Time section will display the corresponding date and time for each application. The same information should be repeated for each day in the date range. I'm almost positive I need to use a manual crosstab because separate formulas are needed for each combination of server and applications.

As you can see, this is very complicated and I’m not sure how to start, let alone finish. Does anyone have any ideas on how I can accomplish this?

 

[synapsevampire]

Group by the server and then the date. right click the group footer for the date and select format section and turn on the new page after.

Now a formula of maximum({table.app1},{table.date}) will return the max for each date within that server, and maximum({table.app2},{table.date}) will return the maximum for app2.

What you don't share is if there are numerous rows for each date, meaning timestamps, and if so what to do if there are more than one day with the same maximum amount?

Better than describing data, post example data and the intended output, which should have clarified this.

-k

 

[oanion]

Synapsevampire, thanks for the assistance. I've generated an example of the output that I'm looking for. The output should be as follows:

Server App1Max App2Max App1Date App1Time App2Date App2Time
Server01 43 8 8/1/2006 2:00PM 8/01/2006 3:00AM
Server02 171 102 8/1/2006 4:45PM 8/1/2006 10:50AM
Server03 279 219 8/1/2006 10:25AM 8/1/2006 10:25AM
Server01 178 129 8/2/2006 10:35AM 8/2/2006 10:35AM
Server02 158 2 8/2/2006 1:00PM 8/2/2006 2:00PM
Server03 154 118 8/2/2006 9:40AM 8/2/2006 9:15AM
Let's take a look at the data on the 2nd line. This information shows that Server2 has a maximum license value of 171 for App1 and a max value of 102 for App2. The corresponding date for App1 is 08/1/2006 and the time is 4:45pm. The corresponding date for App2 is 08/1/2006 and the time is 10:50AM. This is the requested format for the report. Also, when the max values are the same for the same date, the most recent time should be displayed. I'm not sure if it can be done in Crystal. Please Advise

 

[oanion]

If possible, can someone refer me to a formula that will help me retrieve the time from the corrsponding maximum app values for this problem. I have everything else in the report except for the times. I know that individual formulas need to be written for each AppTime, in order to retreive the correpsonding time, however, I have exhausted all of my sources and knowledge so far. Please Advise.

 

[lbass]

Insert a group on date and then on server, and then create a formula like:

//{@reset} to be placed in the server group header:
whileprintingrecords;
datevar app1date := date(0,0,0);
timevar app1time := time(0,0,0);
datevar app2date := date(0,0,0);
timevar app2time := time(0,0,0);

//{@accum} to be placed in the detail section:
whileprintingrecords;
datevar app1date;
timevar app1time;
datevar app2date;
timevar app2time;

if {table.App1} = maximum({table.App1},{table.server}) then
app1date := app1date + {table.date};
if {table.App1} = maximum({table.App1},{table.server}) then
app1time := {table.time};
if {table.App2} = maximum({table.App2},{table.server}) then
app2date := app2date + {table.date};
if {table.App2} = maximum({table.App2},{table.server}) then
app2time := {table.time};

Then reference the variables in the server group footer by creating separate formulas for each one that look like:

//{@displApp1date}:
whileprintingrecords;
datevar app1date;

-LB

 

[oanion]

Thanks Ibass, it worked perfectly!!!!
You're my HERO!!!