Break Table down by date?

nverma

I have a table with bunch of information (date, time, number of users, database name, etc). This table is updated every 5 mintues.

I am creating a report to summarize this information. During the day, number of users that are on the system will vary depending on the time of day, so in my report, I just want to show the maximum number of users that were logged on the database and the time it happened and the date it happened on. Since this task is put in Job Schedular, it is run every 5 minutes. The problem i am facing: lets say at 11:30am, total number of users that are logged on to system are 25 (maximum of the whole day) and few hours later; lets say 2:35pm the total number of users that are logged on are again 25. This code will display the maximum number of users as 25 (which is correct), but it will display this information twice since it happened at 11:30 and 2:35. Having this redundant information over and over again on the report is pointless.

This what I have so far: Its in OnAfterGetRecrord of the dataitem
MaximumUsers is a int variable that i created.

IF MaximumUsers < "Users Logged On"."Current Users" then THEN
    MaximumUsers := "Users Logged On"."Current Users";
    Time := "Users Logged On".Time;
    Date := "Users Logged On".Date;

I also tried doing this to eliminate the repetitivness of the report (this does get rid of the repetitiveness, but it creates a whole new can of worms to deal with):

IF (MaximumUsers = "Users Logged On"."Current Users") THEN //OR (Date = "Users Logged On".Date) THEN
    CurrReport.SKIP
ELSE
    MaximumUsers := "Users Logged On"."Current Users";
    Time := "Users Logged On".Time;
    Date := "Users Logged On".Date;

For the whole day I just want to show one entry which will display the max number of users, date and time it happened for each date. Is there a way to break the table down so that it looks at the date first and goes through all the records for that date and find the max number of users that were on, and then it moves on to the next date and read all the records and find the maximum and displays this info on the report and so on. I was thinking that grouping might work, but then i realize that it wont work al grouping will do is put all the similar dates together....

Any suggestions???

gerrykistler

If your dataitem is grouped by day use a Group Footer to display instead of a Body section. Otherwise gather the information into a temporary table - one record per day, then use an Integer data item (filtered from 1 to the number of records in the temp table) to loop through the temporary table and display the results in the Body of the Integer item.

nverma

I like your idea about using a footer to display the information rather than the body. Let me give that a try.

I tried doing it by the footer method that you suggested. But its only showing one entry. Its not considering an entry for every date.

gerrykistler

Not a plain Footer but a Group Footer, with GroupTotalFields on the DataItem as the Date field.

Savatage

Seems to me a graph would simply your daily review. If your goal is to see when are the "full user" times.

Whomever you show it to can see clearly what's going on & when.

See pic for example.

nverma

Savage- that would be alot better...how do you make a graph in nav???

Savatage

search.php

gerrykistler

php brings up alot of unrelated posts,

Try here:
http://www.mibuso.com/forum/viewtopic.php?f=23&t=28843

nverma

I created a Group Footer to try out the method you suggested, but its not working. Any idea why?? . I took a screen shot so maybe you can pin point something that i might have missed.

Screen shot of everything:
http://www2.zshare.ma/rla8owplm0yo

Screen shot of Preview Report:
http://www2.zshare.ma/2famcbgpf2u7

any idea what I might be missing???

gerrykistler

Date is not in the key you are sorting the data item on - you are using Line No. This field must be in the key you are using in order for it to use it.

nverma

You are a GENIUS!!! It worked like a charm! Thanks!!!

nverma

I am having a slight issue: its getting me the correct number of users but its messing up on getting me the correct time. For some odd reason its picking the last time for that particular date.

I tried doing: "users logged on".setrange("Current Users", maximumusers);

This statements gets me the correct time, but it only runs once...which is pretty obvious...is there a way to get the correct time ( for the max users on the system) and then do a un-setrange ( i know that no such thing exists) or something like that...I was trying to think of a way of using find...but i couldnt come up with anything.

Any suggestions??

gerrykistler

You are missing a BEGIN END:

IF MaximumUsers < "Users Logged On"."Current Users" then THEN BEGIN
    MaximumUsers := "Users Logged On"."Current Users";
    Time := "Users Logged On".Time;
    Date := "Users Logged On".Date;
END;

nverma

I thought that was the issue aswell. So I did insert begin and end in the code, but that didnt fix it.

gerrykistler

Then the issue is your variable name which I should have noticed first. Time is the same as the field name so it will display the field name. Change your variable name to something else like "PeakTime" and change the source in the report section.

nverma

hmmm....thats so strange....but when i changed it; it worked...

I learned something new today... 8)

gerrykistler

It will always take the field name when you are some place where you do not need to specify the dataitem or record before it looks for the variable. Also be careful with field names where they may conflict with standard functions. For instance in your current code you included the dataitem name along with the Time field, but you could have excluded that and had an issue becuase the system would convert the field name Time to the system function TIME, and you would get the current time in your variable.
If you really want Time displayed to the user then name the field something like Recorded Time but make the caption Time.

nverma

Thanks makes sense. :thumbsup: