Newest Record 3

[Pandal]

I'm trying to create a report to show newest date of sidewalk inspections.If I group by date descending this will show the data I need. My problem is I have other fields I need the report to sort by first . For example rating summary and unit id
when I group these first they are sorted first and all the inspections now show
I was hoping there might be a way of creating a formula etc and making the date field = the formula
any help will be appreciated
thanks

 

[synapsevampire]

Show only the newest date?

A cheat is to also insert the date group and select sort descending, and show the data only in the group header.

You might also limit the rows to only that date using a group select of maximum({table.date}, @formula) wher the formula is a combination of the 2 other group fields.

-k

 

[Pandal]

thanks for the post
the problem I have is I need to show the Rating descending
then the unitid descending then just the most recent inspections of the unitid.
the only time i can view the data I want is to have Date field decending grouped first but this while I get the data I need doesn't help me because I need to see rating field descending first
when i move the rating field to the first group I lose the recent inspections it shows all inspections.
My knowledge of formulas is very limited ...could you expand on the maximum function when I try it gives me a must be a boolean or cant use at this time...
thanks again for the prompt reply

 

[lbass]

If I understand you correctly, the other groups are causing multiple dates of sidewalk inspections to appear. This might be a case when a subquery in the SQL is the best answer. A description of how to do this is in thread767-560177. See Lyanch's post for the details. If only one record is returned by the SQL per sidewalk then your other groupings will not interfere.

-LB

 

[Pandal]

thanks to both of you for your prompt replies
I'll be giving it a go and hopefully have the problem fixed
After reading the many posts in here I've enrolled in a few database courses.
thanks again , what a great site this is
Rod

 

[Ngolem]

SV's approach is the simplest but perhaps is not described in enough detial for you

Group your report as you show and enable/suppress the sections of the report as I have shown:


Group 1 header - {table.Rating} descending (enabled with data)
Group 2 Header - {table.unitId) descending (enabled with data)
Group 3 header - {Table.date} descending (enabled showing the date and important information)
Details - suppressed
Group 3 footer - suppressed (if enabled you would show the oldest date data)
Group 2 footer - suppressed
Group 1 footer - suppressed

Doing it this way only show the most recent date for a given Rating/UnitId



Jim Broadbent

 

[Pandal]

Ngolem thankyou for your reply
The report works perfectly for most recent date for Rating
My problem is all the inspections ever done have a rating and I just need to see the newest rating pertaining to that chunk of sidewalk Basically I need to group by date field first but sort by rating first . I really thought I'd be able to display the data easily .... I'm learning very quickly nothing is easy .. thanks again to all who take the time ... for a newbie like me it's not only educational its very very helpfull.

 

[lbass]

That was why I suggested the SQL solution--since the SQL only returns the maximum records, the higher level grouping on rating will not make additional dates show up, as it does when you use group selection or suppression methods. Try going to Database->Show SQL Query and then at the end of the WHERE clause add something like:

and yourtable."yourdatefield" = (select max(tt."yourdatefield&quot from yourtable tt where tt."UnitID" = yourtable."UnitID&quot

where you should substitute the name of your table that contains the date,the correct date field name, and the correct unit id field (assuming that this is the field you are grouping on to determine the most recent date). The "tt" could be something else, or just use that, since it is a sort of alias (I think).

Before trying this approach, save a copy of the report under a new name, since once you change the SQL, you won't be able to edit the selection criteria in the usual way.

I have used this approach in 8.0 but have not tried it in earlier versions. There might be other limitations on its use, I'm not sure.

-LB

 

[Pandal]

thanks again lbass I was just in the process of trying it out
It looks like exactly what I need
thanks to all for your support

 

[Pandal]

Update
I'll be suprised if your still out there
i tried the sql solution and I get improper ending (I'm sure it's user error)
AND INLAI."COMPDTTM" = SELECT MAX(TT."COMPDTTM&quot FROM COMPSW TT WHERE TT."UNITID" = COMPSW."UNITID&quot

INLAI is the table
compdttm is date field in the table INLAI.COMPDTTM

compsw is the table
unitid is the sidewalk Id im grouping on COMPSW.UNITID

I tried many entries but as I said I'm sure user error is the cause

 

[lbass]

Try:

AND INLAI."COMPDTTM" = (SELECT MAX(TT."COMPDTTM&quot FROM INLAI TT WHERE TT."UNITID" = INLAI."UNITID&quot

Please note the paren before "Select" and the table changes after "FROM" and in the final field. This is the way I've done it in the past, although I don't know too much about this myself, only having used it a few times.

-LB

 

[Pandal]

UPDATE 2
BEFORE MODIFICATION

WHERE
"COMPSW"."COMPKEY" = "INLAI"."COMPKEY"(+) AND
"COMPSW"."ADDRKEY" = "ADDR2"."ADDRKEY"(+) AND
"INLAI"."INSPKEY" = "INLAIR"."INSPKEY"(+)

AFTER
WHERE
"COMPSW"."COMPKEY" = "INLAI"."COMPKEY"(+) AND "COMPSW"."ADDRKEY" = "ADDR2"."ADDRKEY"(+) AND "INLAI"."INSPKEY" = "INLAIR"."INSPKEY"(+) AND INLAI."COMPDTTM" = (SELECT MAX(TT."COMPDTTM&quot FROM INLAI TT WHERE TT."UNITID" = INLAI."UNITID&quot

which produces invalid column name

 

[lbass]

Gosh, we need a SQL expert here (and it isn't me!). When I look at my own SQL query, I do not show "" around the tables in the where clause--mine would look like this COMPSW."COMPKEY", but with the "" around the table name in the select and from parts of the statement. I'm not sure what this means about the differences in our databases, but since your where statement seems to have the quotes around the table names, you probably should carry that through the added statement somehow? Just a guess really! I think you'll have to experiment. Sorry to not know the next step here.

-LB

 

[Pandal]

thanks for your time and effort
strange that typing in here is alot more enjoyable than creating the reports hehe
have a good one

 

[Ngolem]

Pandal - I cannot help you with the SQL approach. You always seem to give only part of the problem....even in the SQL approach you are using...you only give the "where' clause.

SV's approach will work based on what you have told us. I have ABSOLUTELY no understanding about what you mean when you say this...

My problem is all the inspections ever done have a rating and I just need to see the newest rating pertaining to that chunk of sidewalk Basically I need to group by date field first but sort by rating first .

Unless you mean that for a given "rating" there can be several "unitID's" and you want the latest date.

IT WOULD BE A LOT CLEARER if you posted a sample of what you are getting for an report output and what you would like for a report output...AND Show how you grouped the report to get the actual output.

As much as you like writing in this forum....your problem defination leaves a bit to be desired.

Jim Broadbent

 

[Pandal]

Ngolem thanks for your reply
Ok I'll try and explain exactly what I need in the report and where I am.
This report is a sidewalk inspection report
Each block of sidewalk is given a unitid "COMPSW.UNITID
Each sidewalk has multiple inspections on it over the years. With each inspection "INLAI.ISPNO" there is a damage rating "INLAIR.RATING"
I need to show just the latest inspection for each Unitid
and this then needs to be viewed by Rating descending so the worst Unitid's in need of repair will be shown first.
When I group by Rating decending first all the ratings appear from worst to best then I group by Unitid and I get all the data showing exactly the way I need it.

What I need to do is keep the way the report now looks with Rating then Unitid but just show the most recent INLAI.ISPNO for all the UNITIDS'S and I would be completed

thankyou all for your time , I'll try and be more descriptive in the future

 

[Ngolem]

ok ...now it makes sense...much better description

Is there a case where a unitID was rated badly...then improved its rating....or is each successive inspection revealing a worse/equal rating.

If the results get progressively worse then it is not so bad but if they rise and fall with time it is more difficult.

One thought I have is to collect each UnitID in an array as they are reported and then test this array in later ratings to see if the UnitID has already been reported and suppress that record if found.

Depending on your version of crystal you may be limited to 1000 unitid's though. It isn't hard to do this we can persue it if you wish

Jim Broadbent

 

[Pandal]

Jim
Thanks for your reply
yes, each successive inspection reveals a worse/equal rating.
I/m currently using crystal 8.0 with Oracle 8.

you stated" Depending on your version of crystal you may be limited to 1000 unitid's though"

If a newer version of crystal is needed I'll order it
I've never used the array before so look forward to doing so
This I'm sure will help me in many of the reports I'm asked to create
Look forward to your reply
thanks again

Rod

 

[Ngolem]

well CR 8.0 could do it...one could use 2 or more arrays if necessary...do you have any idea of how many UnitId's are involved??

ok...here is how it is done the way I suggested with arrays

As I mentioned in an earlier post you would group your report this way:

Group 1 header - {table.Rating} descending (enabled with data)
Group 2 Header - {table.unitId) descending (enabled with data)
Group 3 header - {Table.date} descending (enabled showing the date and important information)
Details - suppressed
Group 3 footer - suppressed (if enabled you would show the oldest date data)
Group 2 footer - suppressed
Group 1 footer - suppressed

First you must initialize your array(we will do it with only a single array of 1000 elements...hopefully that is enough but it will show you how it is done

//@Initialize (placed suppressed in the Report Header)

WhilePrintingRecords;
//Create 1000 values of "" in this array.
//it seems tedious but with copy and paste it is relatively easy
StringVar Array UsedIDS := ["","","",...,"","",""]
NumberVar Pointer := 0;

now in the Section Expert for conditional suppress in the section for UnitID Group header place the following formula

WhilePrintingRecords;
StringVar Array UsedIDS;
NumberVar Pointer ;
BooleanVar Result;

if {Table.UnitID} in UsedIDS then
Result := True
else
(
Pointer := Pointer + 1;
if Pointer <= 1000 then
(UsedIDS[Pointer] := {Table.UnitID};
Result := False;
);
Result;

now this will update the first 1000 UnitID's you will see duplicates after that....we can test/update a second or third array here to expand our cabablity if necessary , of course we would have to modify the formula @Initialize as well

now in each subsequent section that would be normally displayed we put the following formula in the conditional suppress


WhilePrintingRecords;
StringVar Array UsedIDS;
NumberVar Pointer ;
BooleanVar Result:= False;

if {Table.UnitID} in UsedIDS then
Result := True

Result;

This is fairly simple to do....the speed of execution may be slow depending on the size of your database but it should crunch several tens of thousands records in under a minute. I usually use a minute as a criteria for an acceptable report

try it and see how it goes



Jim Broadbent

 

[Ngolem]

Sorry I missed a semi-colon in the last formula

WhilePrintingRecords;
StringVar Array UsedIDS;
NumberVar Pointer ;
BooleanVar Result:= False;

if {Table.UnitID} in UsedIDS then
Result := True;

Result;



Jim Broadbent