ODBC Subreport in Crystal Report form (V8.03)

I needed to add some info to the ARForm (the AR invoice printout), and couldn’t do it in the RDD (report data def), so I created a sub-report that gets the data via an ODBC connection.

Anything wrong with doing this? I ask because occasionally a user will go to print invoices and be presented with a dialog box that looks like it is looking for login info for the ODBC connection.

Entering the correct password doesn’t resolve the issue. And when this happens, there are some error log entries about too many connections.

You have attempted to connect to a database with too many users connected to it. Retry the connection later, or increase -n on the server. (5291)

Calvin

Yeah, that’s definitely not the “preferred” method of doing it, but it works. What are you wanting to bring into the RDD that’s causing you to do this ODBC work-around? The error you are getting is fixed by changing a startup param in OpenEdge for the Database. Progress Customer Community

I need to bring in OrderHed.ShortChar08 and ShipVia.Desc. I do these with two seperate sub-reports

The linking goes as:

Sub report linking: INVCHEAD.OrderNum_OrderNum -> OrderNum (a parameter field in the sub report)

Subreport data: MC_MfgSys.OrderHed1 (MC_MfgSys is the ODBC connection)

and for the ShipVia

Sub report linking: INVCHEAD.Calc_PackNum -> ShipHead1.PackNum

Subreport data: MC_MfgSys.ShipHead1 <-> MC_MfgSys.ShipVia1 where MC_MfgSys.ShipHead1.ShipViaCode = MC_MfgSys.ShipVia1.ShipViaCode (and company’s are equal)
(MC_MfgSys is the ODBC connection)

We only do 1 shipment per invoice, so the INVCHEAD.OrderNum_OrderNum, and INVCHEAD.Calc_PackNum should only have one value each.

Calvin

You can easily do what you want inside the RDD – let me kick you in the right direction.

Open your ARForm RDD. Go to the Report Table tab, List tab and click on the OrderRel table highlighted below. Then, click on the Linked Tables tab.

In Linked Tables, in the left column “Lookup Available” choose “OrderNum” which is a bad way to say you are selecting the OrderHed table. Once it is in the right column, “Lookup Picked” press SAVE. You must press SAVE. (bug). Then…

Flip to the Description Fields tab, ensure “OrderNum” is selected in the drop down and then scroll down and select your ShortChar08 field and move it over. Save. Run your ARForm again. Take that XML sample and pull it into Crystal and you will see your OrderNum.ShortChar08 under the “OrderRel” dataset/table in Crystal.

For your ShipVia.Description, you can still get to it from a Linked Table from OrderRel just like above. “ShipViaCode” is the “table” you want and you can then pull in your ShipVia.Description field like shown below.

Doing it this way keeps everything contained in one XML file for your Crystal ARForm and circumvents your need for a subreport and ODBC connectivity.

1 Like

Thanks for the detailed “kick”.

Did everything you said, but when I load the new XML file I don’t see the OrderDtl or OrderRel tables in CR’s field explorer.

I know I did the RDD update properly, as the new data is in the XML File (snippet below)

Hmmm … cant past a snippet of XML code… I’ll try a screen shot

Is the OrderRel data stored in one of the other “tables” listed?

Calvin

Strange, OrderRel is not an active table? OK, in Crystal, preview the data (F5)/open the XML sample. Once it’s open, in Crystal, on the menu, go to Database and then choose Database Expert. On the left side under Current connections if you see the OrderRel table, add it. It may/should ask you to link the new table with a relationship, go ahead and do so… OrderRel should join fine with InvcDtl already in your dataset above. Once you get it added in Crystal, your fields will be under the OrderRel table prefixed with “OrderNum_”.

To see the new fields in Crystal Reports, you need to create an XML by running the report. Check to see if the new fields are in the XML. Then in Crystal, set the database location to the XML. The fields should then be available automatically but you may have to refresh the database.

Going slight off of the original topic …

I’ve looked into this and have found lots of good info on the proper sizing of the -n param (as well as the -Ma and -Mpb params).

But have to admit, that I’m not exactly sure where these parameters are entered. Is it somewhere in a DB or AppServer properties (via PET), or is it the mfgsys.pf file (in E:\Epicor\mfgsys803\Server\config)?

I’m rather hesitant to make guess of this nature and start editing configuration files based on a hunch.

If it is the mfgsys.pf file, does it matter where the parameters are? I only ask because it seems odd to have a config file with multiple lines - unless each line has just one parameter.

Here’s mine:
-Mm 1024 -mmax 65534 -Bt 5000 -s 215 -yy 1970 -stsh 31 -inp 32000 -tok 4000 -TB 31 -TM 32 -D 500 -l 1000 -ttmarshal 5
-tmpbsize 8
-T E:\epicor\mfgwrk803
-db E:\epicor\mfgsys803\db\mfgsys
-cpinternal utf-8 -cpstream utf-8 -cprcodein utf-8

(weird … I had to add a space before each line, so they could be converted to “Preformatted Text”. And even then could only “pre-format” one line at a time.)

In theory you should be able to put that -n parameter anywhere in your mfgsys.pf file. Perhaps append it to the first line like so:

-Mm 1024 -mmax 65534 -Bt 5000 -s 215 -yy 1970 -stsh 31 -inp 32000 -tok 4000 -TB 31 -TM 32 -n 60

It’s been a while, but because this is ODBC related, you may want to try this first in PET:

Look below; navigate to your live DB (mine is TEST) and drill down to ODBCGroup and right click on it, Properties and modify my circled value to something higher. Restart your appservers and db to apply.

I’ll try adjusting the Clients per server number in the ODBCGroup first. And see how that goes before messing with the .pf file.

Thanks,

Calvin