ISTech Support Forum - Using CM-A Class Codes

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1919
Gender: male

Using CM-A Class Codes
03/04/03 at 16:10:04

I'm trying to use the Class Codes in CM-A to make a selection. When a customer is either out-of-business, or we get mail back as undeliverable I have added the class code of "X".

It appears to me that the data is in BKCMACCL_B??.BKCM_ACCL_KEY - at least, when I browse the data what I see makes sense. What I see is the Account Code followed by the Class Code. For instance, Account Code PIEFRA has the Class Codes of FR and 2ND, so I see
PIEFRA FR
PIEFRA X

However, when I select that field to be "Not like *X" it's filtering out more than it should.

Is it because some have more than one code? BTW, lots of them don't have any codes at all.

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 634
Gender: male

Re: Using CM-A Class Codes
Reply #1 - 03/21/03 at 16:30:50

David,
If I understand you correctly, you want to generate a report selection that DOES NOT include anyone that has a class of X assigned. To do this, you use a SQL select called a correlated subquery.

To do this correctly, you should be accessing your dba data through an ODBC datasource using the DDF files generated by the off menu program 'ODBCDDF'. This is how you leverage the relational side of the Pervasive database and enjoy the benifits of SQL select statements getting "pushed down" to the server. This is the only method that I use to access DBA datafiles.

You also need to use the Visual Linking Expert to link the BKCM_ACCT_CODE field of BKCMACCT to the BKCM_ACCL_CODE field of BKCMACCL with a Left Outer type Join. The arrow of the join should point to the BKCMACCL file. This is key to including entries that do not have any class codes assigned.

The bkcmaccl file has an entry for every customer that has class codes assigned. Each record consists of 3 fields, a key field (BKCM_ACCL_KEY) , a customer code field (BKCM_ACCL_CODE) , and a class field (BKCM_ACCL_CLASS). The key field is NOT used at all for this report. The idea in the subquery, is that only customer codes (BKCM_ACCL_CODE) that DO NOT show up in a subquery specification will be included in the report.

To do this in crystal reports, you first specify a simple selection critera by clicking the select expert, and set BKCM_ACCL_CODE equal to 'HERE'. This will create an SQL select statement template that we will now edit.

If you now click "Show SQL Query" Under "Database", you will see the query that Crystal will send to the database to retrieve a record set. in the Where Clause of that statment, you will see the following:

WHERE
BKCMACCL."BKCM_ACCL_CODE" = 'HERE'

This is the line that we will replace to include a subquery. You need to change the line following the WHERE clause to:

BKCMACCL."BKCM_ACCL_CODE" NOT IN ( SELECT DISTINCT CM.BKCM_ACCL_CODE FROM DBADATA.BKCMACCL CM WHERE CM.BKCM_ACCL_CLASS = 'X' )

(Where it says "DBADATA" above, replace it with the name of your ODBC datasource)

I have tested this with my datafiles and it works perfectly.

Hope this helps.

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1919 Gender:	Re: Using CM-A Class Codes Reply #2 - 03/25/03 at 07:07:36 Kevin, Thanks for the detailed instructions. I'll give it a try when I have a little bit of time to work it out. Thanks!
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1919 Gender:	Re: Using CM-A Class Codes Reply #3 - 06/27/03 at 10:16:24 OK, I'm stuck. I ran ODBCDDF.RUN from within DBA. It ran through with no errors. I'm assuming that I should use either ODBC\Connect Using File Data Source or ODBC\Create New Data Source, But I can't figure out what to do. I guess I need the ODBC for Dummies course.
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 634
Gender: male

Re: Using CM-A Class Codes
Reply #4 - 06/27/03 at 13:14:45

David,
There are some additional steps you need to take to use the DDF files created by ODBCDDF as an ODBC data source.

1. Go to your server machine, and log in as an administrator.
2. Go into settings->control panel->Data Sources (ODBC).
3. Click the "System DSN" tab.
4. Click the Add Button.
5. Select the "Pervasive ODBC Engine Interface" Driver and select Finish.
6. Enter a name to refer to this data source. I recommend using something relitivly short with no spaces.
7. Down in the area titled Database, click the Create button.
8. For the database name, use the same name you put on the data source.
9. Click the browse button, and select the directroy where you created the ddf files with ODBCDDF.
10. Click OK
11. I think you need to click OK again.

Now you have created the Engine Data Source that the Client data source at the Workstation will be able to access.

Now, At the workstation, do the following:

1. Go into Settings->Control Panel->ODBC Data sources.
2. Click the System DSN tab
3. Click Add
4. Choose the "Pervasive ODBC Client Interface" driver and click Finish.
5. For Data Source Name, you can use the same name as you did on the server.
6. Enter the name of your server.
7. In the Data source name box, you should be able to click the "Get DSN List" button.
8. Select the name you created at the server.
9. You can click test to see if the database is accessable.
10. Click OK to complete the Client Driver Setup.

Now in crystal reports, When you go to add a data source, in the Data Explorer, there should be an ODBC folder that you will find the datasource name that you just created. If you click the little + sign next to it, all the DBA tables should be displayed. You can then add the tables that you need to your report.

Hope this helps

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1919 Gender:	Re: Using CM-A Class Codes Reply #5 - 06/30/03 at 07:06:54 Kevin - It sounds like that's the backgrounf info I need. I'm on vacation this week, so I won't get to try it until at least next week, but I will let you know how I make out. Thank you very much for your help.
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1919
Gender: male

Re: Using CM-A Class Codes
Reply #6 - 07/12/03 at 10:22:08

Quote from kevind on 06/27/03 at 13:14:45:

9. You can click test to see if the database is accessable.

Well, I got that far. However, when I test, or if I try to open the Data Source in Crystal, I get an error meesage, the gist of which is "the SQL Connection Manager is not running at the specified port number". Any further suggestions?

Thanks,

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

kevind Active Member I was more than willing to "pony up", how b'out U? Posts: 634 Gender:	Re: Using CM-A Class Codes Reply #7 - 07/14/03 at 11:02:58 Make sure that both the Pervasive Transactional engine and the Relational engines are running on your server. It sounds like the Relational Engine is not running. DBA only uses the Transactional engine.
Back to top	Kevin Damke Spectronics Corporation ISTECH 2004.1 7/26/13 SP1 - 20 user Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user (Prev version was 2004.1 10/24/12) (All Patches Installed as of 7/26/13) Pervasive 2000i SP4 - Crystal Reports V10 IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 634
Gender: male

Re: Using CM-A Class Codes
Reply #8 - 07/14/03 at 13:47:34

David,
It looks like the TCP/IP Multihomed Setting was the culprit. Changing this setting to ON told the Connection Manager to ignore the Listen IP Address Paramiter (currently 0.0.0.0). Since your machine is not Multihomed, you do not care what adapter it listens on (because there is only 1).

I would recommend that you apply all available service packs to your Pervasive installation. There are significant fixes with regard to Relational Joins among others.

You may still run into problems after updating your pervasive. If you do, there is a .REG file available from Crystal Decisions that fixes the JOIN syntax that Crystal uses.

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1919
Gender: male

Re: Using CM-A Class Codes
Reply #9 - 07/15/03 at 12:33:24

All right, now that I've got the connection working (BTW, the access time is incredibly faster Grin

) I can't get the query to work right. After following the instructions in Kevin's very first message I get the following error message:

"Unknown table or correlation name [BKCMACCL]"

I will say that I couldn't figure out any way to specify a "Left Outer Join", but Crystal says that the only type of join it can do in this circumstance is the Left Outer, so I assumed that that is what it did. I've tried playing around with adding and removing the various ".BVH" and "_BVH" in suffixes of the query, not to mention re-doing it several dozen times both before and after making other selection criteria, to no avail.

Just "normal" selection criteria work fine.

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 634
Gender: male

Re: Using CM-A Class Codes
Reply #10 - 07/16/03 at 09:21:03

David,
To change the join type, just right click on the arrow that connects two tables in the crystal reports Visual Linking Expert (Database->Visual Linking Expert) and Choose "Options". Be patient, because sometimes it is difficult to click the arrow just right.

You will be presented with a screen that lets you change the type of Join.

Could you post the SQL that Crystal is generating?? Just select Database->Show SQL Query..., then highlight the SQL text with the mouse and copy it to the Clipboard (ALT-C). You could then paste this text (ALT-V) into a forum message. I will take a look at it.

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1919
Gender: male

Re: Using CM-A Class Codes
Reply #11 - 07/21/03 at 06:27:41

All right, I changed the join type, and get the same error message. Here's the code:

SELECT
BKCMACCT_BVH."BKCM_ACCT_NAME"
FROM
{ oj "DBA"."BKCMACCT_BVH" BKCMACCT_BVH LEFT OUTER JOIN "DBA"."BKCMACCL_BVH" BKCMACCL_BVH ON BKCMACCT_BVH."BKCM_ACCT_CODE" = BKCMACCL_BVH."BKCM_ACCL_CODE"}
WHERE
BKCMACCL."BKCM_ACCL_CODE" NOT IN ( SELECT DISTINCT CM.BKCM_ACCL_CODE FROM DBA.BKCMACCL CM WHERE CM.BKCM_ACCL_CLASS = 'X' )

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 634
Gender: male

Re: Using CM-A Class Codes
Reply #12 - 07/21/03 at 10:09:31

David,
Try this one:

SELECT
   BKCMACCT_BVH."BKCM_ACCT_NAME"
FROM
   { oj "DBA"."BKCMACCT_BVH" LEFT OUTER JOIN "DBA"."BKCMACCL_BVH" ON "BKCMACCT_BVH"."BKCM_ACCT_CODE" = "BKCMACCL_BVH"."BKCM_ACCL_CODE"}
WHERE
   "BKCMACCL_BVH"."BKCM_ACCL_CODE" NOT IN ( SELECT DISTINCT "ACCL"."BKCM_ACCL_CODE" FROM "DBA"."BKCMACCL_BVH" ACCL WHERE "ACCL"."BKCM_ACCL_CLASS" = 'X' )

After You plug in this SQL, you may need to go back into the Visual Linking Expert and re-establish the Outer Join Link.

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1919 Gender:	Re: Using CM-A Class Codes Reply #13 - 07/21/03 at 14:52:09 Houston, we have ignition! Kevin, I can't thank you enough for all the help you've been. I only hope I can be as much help to you or someone else some day.
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 634
Gender: male

Re: Using CM-A Class Codes
Reply #14 - 07/22/03 at 09:18:52

David,
Good, I'm glad it is working.

Now what you could do with that nicely formed select statement would be to create a SQL View.

A View works just like a table. The neat thing about that is your crystal report would only have the one table in it (The view you define). You can even link this pseudo table to other data if you want. When you define the view, you include all the fields in it that you may want to report on. This makes fields from different table look like they are all together in one table.

This is another way of controlling access to the data.

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged