Want To Display First Record But Only If It Matches A Certain Value

[kc27]

My Crystal Reports 9 report is set up as follows:

GH1: Merch ID Merch Description
D: Merch ID Check ID Type Vendor Destination Route No. Ad no. Date
GF1:


Their can be multiple rows in the details section. I have the section sorted by Check ID in descending order. In the Details Section Expert I used this formula to only show the first row - the one that should have highest value Check ID number.

Not OnFirstRecord
AND
Previous({CHECK_MRCH.merch_id}) = {CHECK_MRCH.merch_id}

Once I've got the row with highest value for the Check ID number displayed, a second condition must be met. I only want to display those rows that have value of BTSIT or BTONSIT for the "Vendor" field. If the row with the highest Check ID number has a vendor anything other than BTSIT or BTONSIT, then it and its group header should not be displayed.

I've tried the Select Expert tool to select the values of BTSIT or BTONSIT for the Vendor field, but doing so ignores the highest check IDvalue requirement.

What do I need to do to further filter the report to only show rows that have the specific vendors I need. Any help on this would be appreciated.

 

[kc27]

I amended my details section expert code to the following:
Not OnFirstRecord
AND
Previous({CHECK_MRCH.merch_id}) = {CHECK_MRCH.merch_id} and
{CHECK_MRCH.check_vendor} <> "BTSIT" or
{CHECK_MRCH.check_vendor} <> "BTONSIT";

The addition of the additional code did not produce the intended results. It removed the details underneath every header in my report and just left the headers, but it did not eliminate the header rows that contained vendor names other than BTSIT and BTONSIT.

Any ideas how to get this to work?

 

[briangriffin]

This would be much simpler if you are able to include a command object to only return the latest check:

select MerchID, maximum(CheckID) as MaxCheckDate from CHECK_MRCH

Then link that command to the existing CHECK_MRCH table on MerchID and CheckID. Make sure they are inner joins and enforced both ways.

That should give you a group for each MerchID with one record in each group - the latest CheckID. Once you get that working then it's easy to suppress groups you don't want. Let me know if this approach will work for you, and if you get the command working. Actually you don't really need the group at all.

 

[pmax9999]

I think the approach can be simplified. I haven't tested this but think it will work - try this:
[ol 1]
[li]Add the following code to the Record Selection formula:

{CHECK_MRCH.check_vendor} in ["BTSIT", "BTONSIT"]

[/li]
[li]Split GH1 into two parts (GH1a and GH1b);[/li]
[li]Move existing GH1 contents to GH1a;[/li]
[li]Sort report on CheckID (descending) so highest number is first;[/li]
[li]Move fields in Details section to GH1b. Only the first row will show, which will be the one with the highest CheckID;[/li]
[li]Suppress Details section.[/li]
[/ol]

And a word of warning regarding the Command approach. If you are using a command, I strongly suggest against joining a Command to other tables, as in my experience the join will NOT be processed at the database server level, meaning that all data from the Command and the Query created by Crystal for the rest of the report will be returned to the local machine for processing. In simple queries with small datasets the impact may not be huge but my advice is if you are using a Command, the most efficient approach is to include the entire query within the Command.

Hope this helps.

Regards
Pete

 

[kc27]

Thanks Brian and pmax for the help. I gave them both a try, independent of each other, but did not have any success.

To add the command object, I opened the Database Expert and clicked "Add Command". I pasted the code provided: "select MerchID, maximum(CheckID) as MaxCheckDate from CHECK_MRCH"
and clicked OK. At that point I got the message "Failed to open a rowset. Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]'maximum' is not a recognized function name. I also tried "SELECT merch_id MAX(check_id) FROM CHECK_MRCH" and did not have any luck with that either.

When I added "{CHECK_MRCH.check_vendor} in ["BTSIT", "BTONSIT"]" to the Record Selection formula, no records were returned in the report.

Does this information above give you any ideas on what I might be doing wrong? And thanks again for sharing your expertise.

 

[pmax9999]

When you say it returned no records, do you mean that the record count at the bottom of the screen showed zero, or do you mean none were displayed?

Did you also remove the conditional suppression for each of the sections you had previously added it to?

Did you already have other code in the Record Selection formula? If so, please post the entire code.

Cheers
Pete

 

[kc27]

Hi Pete

It returns 0 records - and i do not have any sections suppressed.

Here is the record selection formula (before adding the portion you suggested):
{CHECK_MRCH.check_type} = "O" and
{ad_info.ad_run_start_dt} >= DateTime (2015, 01, 01, 00, 00, 00) and
{ad_merch.merch_status} = "A"

This is what my report design looks like.

This is what my query returns as currently configured

My goal is to determine the maximum check_ID value for each merch_id number, then see if that row has a check_dest of "O" and a Check Vendor value of BT OFF-SIT or BT OFF SITE. If the maximum check_ID row meets the check_dest of "O" and Check Vendor value of BT OFF-SIT or BT OFF SITE, then display it, and suppress any rows that do not meet that condition.

I was able to get the row with the maximum check_id value to display by going into the Details Section Expert and using the formula below to only show the first row - the one that should have maximum check_ID value because they are sorted in descending order of check_ID. (The screenshot I linked in this response shows the report as it appeared before I implemented the formula below).

Not OnFirstRecord
AND
Previous({CHECK_MRCH.merch_id}) = {CHECK_MRCH.merch_id}

Again - I really appreciate the help, and any ideas you can share to help me get this report displaying the way I want it to appear.

 

[pmax9999]

The sample data in the second link does not include any records where the {CHECK_MRCH.check_vendor} is "BTSIT" or "BTONSIT", which is exactly why my additional code returns no records. Obviously I have misunderstood what you are trying to achieve.

Using the sample data from that link, please explain exactly the results you would expect to see.

Pete

 

[kc27]

Does this screenshot make more sense? I commented it to show the rows I want to display. Also, below is the sql behind this report. It shows how I have the data ordered in the report.

SELECT "CHECK_MRCH"."merch_id", "ad_info"."ad_run_start_dt", "CHECK_MRCH"."check_type", "CHECK_MRCH"."check_vendor", "CHECK_MRCH"."check_dest", "ad_merch"."route_order", "ad_merch"."merch_status", "merch"."merch_desc", "CHECK_MRCH"."check_id", "ad_merch"."ad_nbr"
FROM (("dbadvprod"."informix"."CHECK_MRCH" "CHECK_MRCH" INNER JOIN "dbadvprod"."informix"."ad_merch" "ad_merch" ON "CHECK_MRCH"."merch_id"="ad_merch"."merch_id") INNER JOIN "dbadvprod"."informix"."merch" "merch" ON "CHECK_MRCH"."merch_id"="merch"."merch_id") INNER JOIN "dbadvprod"."informix"."ad_info" "ad_info" ON "ad_merch"."ad_nbr"="ad_info"."ad_nbr"
WHERE "CHECK_MRCH"."check_type"='O' AND "ad_info"."ad_run_start_dt">={ts '2015-01-01 00:00:00'} AND "ad_merch"."merch_status"='A'
ORDER BY "CHECK_MRCH"."merch_id", "ad_merch"."route_order" DESC, "CHECK_MRCH"."check_id" DESC

 

[pmax9999]

OK, that helps a lot. It wasn't quite as I understood it.

I have done some testing and achieved the result I think you are looking for. However, the sample data and desired results you posted does raise a question. For {Table.merch_id} 305819 for example, both records match what I understand is your requirement. If you only expect 1 row you would need to determine a way to distinguish which one to display.

Anyway, I did it this way:

1. Group the report by merch_id (as it is) and sort it only by check_id in descending order (which is NOT how the report is currently sorted);
2. Create the following 2 formulas and place them in the Details Section (they can be hidden in need):
[Code {@Test1}]
If {Table.check_id} = Maximum({Table.check_id}, {Table.merch_id}) and
{Table.check_dest} = 'O' and
{Table.check_vendor} in ["BT OFF-SIT", "BT OFFSITE"]
Then 1
Else 0
[/Code]
and
[Code {@Test2}]
Select {Table.check_vendor}
Case "BT OFF-SIT" : 1
Case "BT OFFSITE" : 1
Default : 0
[/Code]
3. Use the following code to conditionally suppress the details section:

{@Test1} <> 1

4. Use the following code to conditionally suppress the Group Header:

{@Test1} + {@Test2} = 0

Please note that the success of this approach depends on the sort order.

Hope this helps.

Cheers
Pete

 

[kc27]

I worked on this again. I figured out why adding {CHECK_MRCH.check_vendor} in ["BTSIT", "BTONSIT"] via Select Expert returned zero results. It was because the values in my original post were incorrect. They shoudl have been "BT OFF-SIT" and "BT OFFSITE". I had tried that earlier in the week, and realized using the select expert won't get me where I need to go.

This report is for a piece of merchandise that can be transferred to multiple locations multiple times. The Check_ID, I believe, will increment each time the merchandise is transferred. That's why I am grouping all the merch_id's and sorting the check_id's underneath them in descending order. It's that last transfer (the highest value check_id in the group) that I care about. I need to know if the value of check_vendor that corresponds to the highest value check_id is "BT OFF-SIT" or "BT OFFSITE".

Sorry for the confusion I may have caused on this. This is how the report looks with the Details Section Expert formula to only show the first row - the one that should have highest value Check ID number.

Not OnFirstRecord
AND
Previous({CHECK_MRCH.merch_id}) = {CHECK_MRCH.merch_id}

Any ideas on how to suppress the rows and group headers for those merch_id's that do not have a value of "BT OFF-SIT" or "BT OFFSITE" for the check_vendor field would be appreciated.

 

[kc27]

We must have been writing at the same time! Thank you so much for the time you took in deciphering what I was trying to accomplish. I decided to start from scratch, and employed your suggestions - the formulas you wrote cleared out the non "BT OFF-SIT" and "BT OFFSITE" values. I guess I need to develop an understanding of formula writing in order to get the most out of my reports. My last iteration of the report seemed close (the example I posted at 22 Feb 15 00:29), but I could just not clear out the unneeded values.

Is there any way to remove the extra rows that appear in the details section? Here is an example. Having only the "BT OFF-SIT" and "BT OFFSITE" values in the report will make it so much easier to use, but if possible, I'd like to eliminate the duplicate rows.

Anyway, thanks again for sharing your time and expertise in figuring this out.

 

[kc27]

To get rid of the duplicate detail lines, I moved fields up in to the group header and hid the details section. Not sure if this is the best way to accomplish this, but it works.