Maximum Date Record to the Displayed 1

[spacemanspiffy04]

Hi,

I use CR XI with DB2.

I am creating a report that has 2 groups as shown below.

One of the fields in this report is a date field. I need to only pick up the latest date from this field for each of the distinct account numbers.

So, for the example below, I want the report to just show account number 123 for date of 11-27 and account number 678 for date 12-02. I do not need the other records pertaining to these account numbers.

Location :A (GROUP 1)
Area: B01 (GROUP 2)
Account # Date User ID Comments
123 11-25 XXXX XXXXXX
123 11-26
123 11-27 (DISPLAY THIS ROW)
678 12-01
678 12-02 (DISPLAY THIS ROW)

I had a similar question for a prior report, where I wanted only the maximum date at the "Area" group level and LBass had a great suggestion of going to :

report->selection formula->GROUP and entering:

{table.date} = maximum({table.date},{@group})

This worked great. However for this report I need to select the maximum date based on account number. I tried creating a group out of account number and using the formula above, and that worked, but then I could not get the sorting I wanted as I now had an unnecessary Account number group.


Any help would be greatly appreciated!!!

Thanks!

 

[spacemanspiffy04]

One more thing, I have created a parameter field for this report where the user has to enter a date and the report will return only those records that have a date greater than the date the user entered. Is there any way the user can only enter a number such as 10 days in the future, and the report will only return the number of records more than 10 days in the future? This way the user does not have to enter a date.

Thanks!

 

[lbass]

Please explain what the sorting is that you want to achieve, and show an example. I think the solution used in your last post is still the way to go, without further information.

To limit record to those more than n days in the future, create a number parameter {?daysinfuture} and use a record selection formula like:

{tabledate} > currentdate + {?daysinfuture}

-LB

 

[spacemanspiffy04]

Hi LB,

Thanks for the suggestions regarding limiting records to more than n days in the future. I'll give it a shot.

For the first part of my question - from my last post I got information on how to only display the maximum record for each group. However, I now have to pick a maximum record from a column of information that has not been sorted into a group. So, within the country group, I have a state group, and within the state group, I have account numbers that are listed. These account numbers are not grouped in any way. If there are several records related to one account number, then the account number is repeated several times. I do not want this to happen, I want the report to only display each account number once based on a date/time stamp that exists. I want it to pick the account number with the most recent date/time stamp.

This is what it currently does:

Country :USA
State: CA
Account # Date User ID Comments
123 11-25 XXXX XXXXXX
123 11-26
123 11-27
678 12-01
678 12-02

This is what I want it to do:

Country :USA
State: CA
Account # Date User ID Comments
123 11-27 XXX XXXX
678 12-02

I tried going to report> selection formula> and then to both record and group, and messed about with a formula, but it kept giving me an error message indicating that I can only pick a maximum record from a group. I put the account numbers into a group and manipulated the formatting so it would work, but then when it came time to set up the sort order for the various columns, I ran into trouble as I had an unnecessary group for account number.

Thanks!

Sid

 

[lbass]

You didn't answer my primary question about how you wanted the sort.

-LB

 

[spacemanspiffy04]

Sorry, here is the sort I want:

Country :USA
State: CA
Date Account # Date User ID Comments
12/02/06 123 11-27 XXX XXXX
11/27/06 678 12-02

Using the example above, I want to sort first by Country (which is a group), then by State (also a group), then by the Date (descending) and then by Account # (Ascending).

One more twist though, the max date I am looking for is not from the date field listed on the report, but from another date field that will not be displayed. Both these date fields (the one on the report and the one not being displayed) will contain different values. The date field on the report will always show a future date and the date field I want the max function for will be the date the comments were created. So basically I want to show just one record which has the latest comment in it.

Hope this is a better explanation.

Thanks!

 

[lbass]

Okay, remove the group on account and remove the group selection. Instead, try creating a command (database expert->your datasource->add command) like the following, where the datefield is the non-displayed date. Substitute your actual field and table names:

Select max(table.`date`) as maxdate, table.`country`,table.`state`,table.`acctno`
From `table`table
Group by table.`country`,table.`state`,table.`acctno`

Link this to the main table by the country, state, account number, and date fields (the same datefield, even though not displayed). Go to link options and check "Enforce both". Then go to record sort->add {table.displaydate} descending and {table.acctno} ascending.

-LB

 

[spacemanspiffy04]

Hi LB,

I tried what you suggested and after I tried to refresh the data on the report I got the following errors:

"Failed to retrieve data from the database" AND

"If the tables are already linked, then the join type cannot change"

Not sure what the problem is or what I might have done wrong?

The only other things that are prevelant on this report that I have not mentioned are

1. I've set up a parameter that the user needs to enter
2. I've concatenated a few field.

Don't think these will have any effect, but I wanted to get all the information out there.

Thanks again!

 

[lbass]

Please copy your command into the post and also describe how you linked it to the table. If you are using more than one table, you'd better explain the linking of all tables.

-LB

 

[spacemanspiffy04]

Hi LB,

Here is the command I put in, I have substituted the names for the tables as the tables I am using do not have intutive names:

SELECT
max (history_table. history_date) as maxdate, country_pool_table.cntry_code, country_pool_table.US_state, country_pool_table. acct_num
FROM
history_table, country_pool_table
GROUP BY
country_pool_table.cntry_code, country_pool_table.US_state, country_pool_table. acct_num

I entered the above command into the command window and saved it. I then linked:

Max date to the history date (non-displayed date field)
Country to the country field in the country_pool_table
State to the state field in the country_pool_table
Acct number to the acct number field in the country_pool_table

I then went to the link options for each of these links and chose Enforce Both.

Then went to the report sort expert and entered the sort options you listed above for date from the date field in the country-pool_table (displayed date) and the acct_number from country_pool.

i redid this a few times to make sure i was doing it right, and I now no longer get the error messages I mentioned above, now the report goes blank.

Here are a few things about the report. It has the following field;

Country, State, Date (displayed), Account #, Comments(this field is should have only the latest comments based on the max of the undisplayed date), Last User, History Text.

The history text has a few fields concatenated.
The State has several fields that are rolled up in one (using a prior suggestion you gave me). So, OH01, OH02, CA01, CA02 will just be grouped as OH and CA.
The user will enter a parameter number and the report will display only records that are above the current date plus that number. The date used is from the date field being displayed.

The linking is as follows:

There are 4 tables being used(excluding the command table i created):

history_table, country_pool_table, history text table and a comments table (that stored the values of the comments entered by the user, so a PR comment by the user will have a value of Please Review in the comment table. The comment code is in the country_pool_table).

The linking between the history_table, country_pool_table, and the history text table is by country and acct number. the linking between the country_pool_table and comments table is by comment code.

I'm sorry I have repeated a lot of information in this post, but I did not want you to have to sift through my prior posts. Hope this is the information you are looking for? Thanks, I truly appreciate the help!!!

 

[lbass]

I cannot tell how you are linking--in what direction, from what table to what table, from what field to what field, and what type of links, etc. The linking is the current problem, I think. Try to show links something like this (not that this is the correct linking):

History table History text
country \-------> country
acctno\ --\------> acctno
\ \
\ \ country_pool_table
\ \-> country
\---> acctno

I can't tell what would be logical, as I'm not sure whether you have any nulls you need to account for. Maybe if your linking is something like the above, you should have the command be the left most table as in:

Command History table History text
country ------> country \-------------> country
state -------> state \
acctno -------> acctno----- ---\------> acctno
maxdate-------> date \ \
\ \ country_pool_table
\ \-> country
\---> acctno

I think you should link on the state group, too.

-LB

 

[spacemanspiffy04]

COMMAND: COUNTRY_POOL_TABLE HISTORY_TABLE
Max date-------------------------->Undisplayed Date
Country--------Country------------>Country------/-----/
US State------>US state Comment code/ /
Acct Number----Acct Number-------->Acct Number/-----/----/
/ / /
HISTORY_TEXT_TABLE / / /
Undisplayed date<-----------/ / /
Country<-------------------------/ /
Acct Number<-------------------------/

COMMENT_TABLE
(this is linked to the Comment Code in the History_table)

Hi LB,

This is the way the linking stands. I have selected "enforce both" in the linking options for all links. Also, these are inner join links. I tried swapping the positions of the tables around i.e command table after all tables, History Table before Country pool etc., but I still get a blank report.

What really sucks about this is that I had the report at a point where it was almost complete, but I then got stuck because I had created an Account Number group (in order to pick up the Max Undisplayed Date from this group), but then I ran into problems when I tried to sort (as per the design) first by the displayed date and then by the account number. Since the displayed date was not a group, it was sorting the account number before the date.

I have messed about with this a lot, and my lim ited CR knowledge could not find a soultion.

Thanks!

 

[lbass]

Try linking from the command to the history table on all fields. Then link the history table fields to the country_pool fields, and the history table fields to the history text fields and the history table fields to the comments table like this:


command---->history----->country_pool
| \
| \
V \--->history text
comments

You should never have two tables leading to the same table.

-LB

 

[spacemanspiffy04]

hi LB,

I ran into the same issue where I got back a blank report. After I changed the links on the tables (thanks for the linking tip), i went back and refreshed the data, got the blank report, then i tried to pull the fields under the command into the window and that did not bring anything up as well. Anything you can think off that I am doing wrong?

THanks!

 

[lbass]

I can't really tell. Try unlinking the command, and then see if the report will run without the command.

-LB

 

[spacemanspiffy04]

Yes, it runs once the command is unlinked, however the records seems to be duplicating.

 

[lbass]

Isn't that because of the date fields? Try adding the command again, only linking it to the history table on all fields.

You might also want to test the command by itself, by copying it into a new report and placing the fields on the report. Or try linking it only to the history table and observe in the new report what happens.

-LB

 

[spacemanspiffy04]

Hi LB,

I put in the command into a blank report and inserted the fields and it showed only 1 record per the undisplayed date, which is what I want, so that was good. When I inserted the Max Date field into the report to see what happens (this will not be on the final report), it displayed the max date from the entire table, and therefore has the same date for all records, as opposed to the different dates that are on the table, but like I said above, this field will not be displayed, so that is not an issue.

Now it gets weird; when I modified the SQL statement to also include the Comment Code from the History table, the report started showing all the records from the Undisplayed date field, not just the Max Date like it was previously doing. I then went back to the original command and inserted the history table into the report using the database expert, then I went in and tried to pull the Comment Code field in, and everything on the report disappeared. I checked the links and they all seem fine.

Thanks!

 

[lbass]

First the maxdate should show the maxdate per the innermost group, as long as you have the group by clauses in the command. You should not add other fields to the command. If you want to use a comments field, pull that from the table itself.

I don't know whether you followed my last post re: linking. What happens when you just add the history table by itself and then place the comments code on the report? What happens when you then add the command and link it to the history table? I don't see why the comments field would affect anything unless you were maybe using a SQL expression for it in one place and then added the field itself as well--I've seen reports crash because of this.

-LB

 

[spacemanspiffy04]

Hi LB,

I used your suggestions, started from scratch and it worked. Thanks again for your help!