Category Archives: Accpac & Crystal Report

#14 Filtering Optional Field Values on Crystal Report from Accpac Database

Date: 03 July 2011

Recently I came to know that some of my colleagues and crystal report writers are facing issues at the time of fetching the optional fields form the Accpac database and showing it on the reports. It is may be because the way Accpac keeping the entries in the Database. Ok, now lets see how Accpac maintaining the optional fields and how we can easily filter it into our reports.

When we create any optional field, Accpac create a new entry in the optional field table. Let’s take Inventory control there is the master table “ICITEM” where all items will be saved. Now if we want to attach optional fields with the inventory then first we have to create optional fields from the Common Services “Optional Fields” form. Optional fields and there values will be stored in the optional field table “CSOPTFH” and “CSOPTFD” table. Further when we link it to inventory control module this information save into “ICOFD” table. Now at the time of creating new item we attach already created optional field with the new item and assign optional field value to it. Then, Accpac will store optional field values for each item in “ICITEMO” table with respective item numbers. I will try to explain this by diagrammatic representation:

From the above structure we can figure it out that In “ICITEMO” table the Item and optional field are coming from “ICITEM” and “ICOFD” tables.

Now the problem comes when we are trying fetching this information on the crystal report table where we have to show Optional fields horizontally. If we are putting condition like where optional field is equal to OPT1 then it will not bring OPT2 value on the report and if we not give any condition then it will duplicate the record. To solve this dilemma use duplicate tables instead of trying to retrieve data from one table. Here I am talking about “ICITEMO” table.

For example we need crystal report as follows

Here we can see that optional value one and two for each item is coming in front of respective item. How to do this easily? Let’s see. We know that the optional values are coming from one table “ICITEMO”. So we need to attach this table on the report and link it with “ICITEM”. But as I stated earlier if we give the condition for the optional value then either some optional value will not come or it will duplicate the data. Now to solve this, make and link alias of “ICITEMO” table on crystal report. Take as many number of “ICITEMO” tables as optional fields are. Like, here two optional values are there so we will take the Alias of “ICITEMO” table two times. Further we put the condition for each optional field with separate table. Let’s see how. Following is the crystal report link screen:

Here we can see that the ICITEMO table is used twice and linked to the “itemno” column of “ICITEM” table. Now this is how we can create the alias of the same table with different alias names. Here it is “ICITEMO” and “ICITEMO_1”. We can also give the alias name as the name of the optional field to make it easy. Just note down here that each alias is going to give one optional field value. Now after making such kind of link we have to set the condition also to retrieving the right value. So we will put the condition for each alias.

Now see the fig above, we can see that we are putting the condition that opt1 value should match with “ICITEMO” table and OPT2 should match with “ICITEMO_1” table. After doing this report is ready only we need to put right field at right place.

Like this we can take Optional value one from table “ICITEMO” and optional value two from “ICITEMO_1”.

Hope that this post will help the people how to take optional fields and make the report right. Further to add every person has its own way of doing things and each of them thinks he is right. But I want to say the option that I am giving will solve your problem and this is the easiest way among the couple of other solutions that I have. Even thought if any of you have any better one please comment and share with us.



#9 Content of .INI file

Date: 08/05/2011

To create a Cyrstal report that uses a datapipe DLL, youmust provide create a DATAPIPE.INI text file in the same directory where the DLL resides. Here is a small briefing of the .ini file. The format of this text file is relatively simple. It includes one section for each datapipe DLL. Each DLL section contains a query entry listing, containing the following information:

-The query name and a list of the default values for the datapipe parameters, separated by “\n”.

NOTE: These parameters have a one-to-one correspondence with the datapipe query entry of the report section in the xxrpt.ini file.

-Parameter values P1 through P4, which specify the default database from which Crystal will read when designing the report form.

If a datapipe contains multiple queries, then list all queries in the datapipe.ini file. However, when you create a new Crystal datapipe report, all queries, except for the one required for the report, must be commented out in the datapipe.ini file.


This example shows the datapipe DLL section in the DATAPIPE.INI which is required to create the Cyrstal datapipe report,

query=”GLPJCON1\n0000\n999999\n \n0\n ”
‘query=”GLPJCON3\n0000\n999999\n \n0\n ”
‘query=”GLPJCON4\n0000\n999999\n \n0\n ”
‘PARAMETER 3 4 12 13


#3 Sage Accpac Reporting and Crystal Report

Date: 27/03/2011

Sage Accpac ERP uses crystal report and excel to do the reporting. By using Business object’s crystal report and Microsoft’s excel, Sage Accpac represent its data on report. Where Financial Reporter using excel to generate financial reports like balance sheet and profit and loss statement, wide tools of crystal report is used to render the ERP data for different modules in the reporting format.

In this article focus in manly on crystal report and its deployment in Accpac. Sage makes it very simple to customize existing report or to generate a new report form Accpac database and deliver it to the end users. With Accpac there are different ways that we can deliver the required report to the user. Either we can directly put the report designed report icon to the sage Accpac desktop or we can call the report through a VBA or VB program and put the short cut on the Accpac Desktop. When we are writing report in crystal for Accpac we need to keep few points in our mind.

Since Sage Accpac comes with the crystal report viewer we can directly put the report on the Accpac desktop and view it. When we run the report crystal parameter screen ask for the parameters user in the report if any. The important thing that we need to consider in that our report should work for all the companies and should pick the correct companies data from where it is running. For ex. if there are two companies set up in a client’s side one is ABC and other is XYZ, then Same report should pick the ABC company data when run from ABC company and XYZ data when run from XYZ company. Accpac provide mechanism for it. Sage Accpac pass “CMPNAME” Parameter that will have the name of the company from which the report been called and will also change the DSN of the report at the runtime to have the database for the company it is running from.

We can also record a macro or create a report UI to integrate our reports to Accpac Desktop. In either case, we must define our report in corresponding application’s report initialization file, xxrpt.ini. The report descriptor file is an ASCII file, structured in the

Windows “.INI” format. It includes the names and parameters of all the reports in our application. The command-level interface uses the report descriptor file. The report descriptor file is xxvvv\xxRPT.INI, where xx is the two-letter application id, and vvv is the program version. For example, the report descriptor file for General Ledger version 5.6A would be GL56A\GLRPT.INI. 

When we record a macro to display and print a Crystal report, the resultant macro code is only valid for displaying and printing the report locally, from the Sage Accpac ERP Desktop. To display the report from the Sage Accpac ERP Web Desktop, the macro code must be modified to use the AccpacPrintReport control, and the Crystal ActiveX Report Viewer must exist on the client.


1 Comment

Posted by on March 27, 2011 in Accpac & Crystal Report


Tags: ,

%d bloggers like this: