Report Includes Too Much Data - Filtering Not Working

[kc27]

I thought I had this report working, but not yet. I did attempt to simplify it, but am still running into issues. It is configured like this:

I am accessing three tables:
CHECK_MRCH
check_id
merch_id
check_type
check_date
check_ad
check_vendor
check_dest

ad_merch
merch_status

merch
merch_desc

I have the report grouped by merch_id in ascending order and within each merch_id group the details (records are sorted in descending order by CHECK_MRCH.check_id

There can be many check ids for under each merch_id. I only want to display the most recent (highest value) of the check_id, so I put this formula "IF recordnumber >1 then True" in the "Suppress" portion of the Section Editor for details.

To make the report neater I moved the fields from the details section into the Group Header section to appear in-line with the merch_id header.

Where I am getting stuck is once I have the record displayed for the highest value of the check_id, I further want to filter the records so that only those records that have a value of "BT OFF-SIT" or "BT OFFSITE" for the CHECK_MRCH.check_vendor field are displayed. I utilized the advice that Pete gave me in this thread, [URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=1745149[/url] it does not seem to work with how this latest version of the report is configured.

I would use the select expert to get the "BT OFF-SIT" or "BT OFFSITE" records, but in this case, I need to determine which record has the highest value check_id for each merch_id, then further evaluate those records to display only those records that have a check_vendor value of "BT OFF-SIT" or "BT OFFSITE".

Any advice would be appreciated.

 

[briangriffin]

Since you're getting stuck, I'm going to suggest that you try the command object approach again. It's true that this is not the most efficient way to process the data, but the other approach also brings in a lot of records that you ultimately discard or hide.

This query:

SELECT merch_id MAX(check_id) FROM CHECK_MRCH group by merch_id

should certainly work, you may need to fully qualify the table name with the database: ... from DATABASE.CHECK_MRCH

If you can get this working then the report is simple to write since you're not having to hide anything. I write complete commands for almost every report I create, but not everyone is experienced in writing SQL or wants to be. However, if you can learn just a little bit then it really eases the load on Crystal - for instance, it almost completely removes the need for subreports.

If you copy your existing report, remove the selection criteria, and post back the SQL then I'd be happy to take a stab at writing the whole thing, which would then run much faster.

 

[kc27]

I understand the command object will reduce the number of records returned. If I can get that working that will be a big help. We'll see what happens tonight when I give that approach another try.

My issue will still come down to having to suppress the rows that do not contain a value of "BT OFF-SIT" or "BT OFFSITE" for the check_vendor field.

 

[kc27]

To get this report completed, I exported to Excel and did my filtering in Excel.

For my own knowledge, I am still trying to get the report to output as cleanly as possible from Crystal. How much work can you make a command object do? Could a command object be employed to pull records with highest value check_ids as long as they had a vendor of "BT OFF-SIT" or "BT OFFSITE"?

 

[briangriffin]

You could definitely return the final record set from the command, but that would take a little more complicated SQL. If you use the command to just return the latest check number then it would be simple to use Crystal to filter out the non-BT OFF-SIT/BT OFFSITE groups.

 

[kc27]

Can anyone tell what is wrong with the statements below? I am trying to add a command object as suggested a couple of posts above, have tried both ways shown, and each time I get this error:

Failed to open a rowset.
Details: 42000[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

Here are the two statements I tried.

SELECT merch_id, MAX('CHECK_MRCH'.'check_id') as MaxCheckDate FROM {'CHECK_MRCH'.'check_date'}

Also tried it this way:

SELECT merch_id, MAX("CHECK_MRCH"."check_id") as MaxCheckDate FROM {"CHECK_MRCH"."check_date"}

I have also tried both of the above statements with quotes only around the field names and not the table names. Still got the error message

Any ideas on how to make this work would be appreciated.

 

[briangriffin]

After the 'from' you need a table name, but you have the check_date field name.
Also, since this is being passed to the database server, the {} won't work since those are Crystal-specific.
The single quotes may work but I would get rid of them anyway.
Ideally you would be able to test this in a SQL Editor so you know it works, then paste it into the CR command.

SELECT merch_id, MAX(CHECK_MRCH.check_id) as MaxCheckDate FROM CHECK_MRCH

I swear this gets a lot easier after you do it a few times...

 

[kc27]

Decided to make things simpler and to try to return the merch IDs that have the maximum check_id value. I still get an error with this code.

SELECT "dbadvprod"."informix"."CHECK_MRCH"."merch_id" MAX("dbadvprod"."informix"."CHECK_MRCH"."check_id") FROM "dbadvprod"."informix"."CHECK_MRCH"

The error is:
Failed to open a rowset
Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'dbadvprod'.

How is the syntax wrong?

 

[briangriffin]

SELECT
merch_id,
MAX(check_id) as maxcheckID

FROM
dbadvprod.informix.dbo.CHECK_MRCH

GROUP BY
merch_id


I forgot to include the group by clause, which is needed whenever using aggregate functions such as MAX or SUM.
Also, all the fields in the select clause need to be separated by commas, and SQL Server will not recognize double quotes.
I've included 'dbo' in the qualified name assuming that's the owner of the table - the syntax is Server.Database.Owner.Object

 

[kc27]

Thanks - I had to put quotes around the table name to get it to work - FINALLY!
I really appreciate your help on this - thanks again.

SELECT
merch_id,
MAX(check_id) as maxcheckID

FROM
"dbadvprod"."informix"."CHECK_MRCH"

GROUP BY
merch_id

 

[kc27]

I just validated some of the data, and see that the report is not always displaying the highest value check_id

When the command object was added, I did get this message:
Database Warning
More than one database or a stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group is performed.

For example:
Merch ID 629093 has the following Check_ID values
2426464
2424527

The lower value one, 2424527, is being incorrectly displayed on the report. Some merch ids have the correct highest value, others are incorrect as in the above example.

The database links are as follows:
Command.merch_id > CHECK_MERCH.merch_id
CHECK_MERCH.merch_id > merch.merch_id

Does any of the above information offer any clues as to why the report contains some check_ids that are not the highest value?

 

[briangriffin]

Those joins should be equal, you're pulling in checkID's that are not for the appropriate merchID. It would be rare that you use > or < in a join unless it's on date fields. While you're at it check Enforced Both Ways.

As a test, create a new report with just the command. Put the fields in the details section, refresh and then validate the data. Also, that record count should be the record count of the real report once you get the kinks worked out.

Ignore the warning, it will always appear but is not an issue with your report.

 

[kc27]

OK, thanks for the heads up about the warning.

I inadvertently mislead you with my diagram. The arrows were meant to indicate the link order, as shown in the screen shot below. The joins are inner joins, and are equal joins.

I will try the report from scratch and see how that goes.

 

[kc27]

I did a bare-bones report from scratch and added the command. It is still not pulling the highest value check_id. In one case, the lowest check_id instead of the highest value was on the report. Others it was neither the lowest nor the highest, but somewhere in the middle.

SELECT
merch_id,
MAX(check_id) as maxcheckID

FROM
"dbadvprod"."informix"."CHECK_MRCH"

GROUP BY
merch_id

 

[kc27]

I grouped by merch_id. Multiple check_ids are appearing in the report for the merch_ids - not just the highest value. In one instance, where a merch_id had six corresponding check_ids, the two highest check_ids were not included in the report. Does that offer any clues as to what might be wrong?

 

[briangriffin]

That is odd. Can you please post some sample data from the check_mrch table? Is the check ID field numeric or text? If they are text, is there also a check date field, or something you can use to conclusively identify the latest check? If you can attach a spreadsheet with those values it would be even better.

 

[kc27]

The problem was me - I realized rather than putting the CHECK_MRCH.check_id and CHECK_MRCH.merch_id fields on the report, I should use the command.merch_id and the command.MaxCheckID fields instead. I did that, and am getting multiple values of the command.MaxCheckID instead of the highest value.

Here is an example of what I am seeing and how the report is configured.

 

[briangriffin]

From the command you'll need to link on both merch_ID and check_ID. If the results are still not what is expected, please put the merchID and checkID from both the command and the table into the details, run the report, and post an image of the results, thanks.

 

[kc27]

I reexamined the query, and I believe I found the reason why I am not getting the maximum value of the Check ID. The report is supposed to only include records with a Check Date less than or equal to 10/1/2014. I used the Select Expert to accomplish that. Using the Select Expert had the unintended consequence of filtering out rows in the report that I first needed to examine and filter. I'll have to take off the Select Expert filtering by Check Date, suppress the rows that do not contain "BT OFF-SIT", and then filter what remains to remove any rows that have a check date of 10/1 or later. Does that make sense?

 

[briangriffin]

Yes, but I would first get the report to return everything based on the check ID. Then when you're sure it's right work on excluding the groups that do not contain BT OFF_SIT. I don't think you'll need any selection in CR other than group selection (in other words, you won't be filtering out individual records).