Monthly Archives: May 2011

#12 Accpac Error “Could not open UI”

Date: 29 May 2011

Some of the time while working on Accpac we might face an issue regarding Accpac UI on client PC or on the server and then we are unable to open the Accpac UI. The error message says ”Could not open UI”.
There is not a single thing on which the Accpac is dependent to run a UI. Accpac checks several things before opening its UI. Like Database should not be locked due to any database related process. Even if somebody is working on database like module activation, Accpac will not allow opening the UI.
Ok, so we can check few or all the following points in order to analyze and rectify the UI problems:

1. Maybe somebody is doing activation: In this case we can wait until the activation completes.
2. Delete SEMAPHOR.BIN: This file contains the detail of the Accpac UIs. It will create itself when first user logs in to the system. This file will be in the Site folder of Accpac. You can delete this file so that Accpac itself recreate it.
3. Check your ODBC DSNs: if database administrator make some changes on Accpac application or database server, it become the cause of the error. ODBC should be checked or else delete the ODBC which is server name32. Accpac will recreate the ODBC with new configurations.
4. Make sure the path to Accpac.exe is the same as the path in the registry: Some time if you reinstall the workstation or make changes on the server, error comes then you can check the properties of Accpac exe and make sure that it is pointing to the same path as it is there in the following registry entry HKLM\Software\Accpac\Configuration

Hope by following above stated steps one can rectify the Accpac UI issue. If someone still face problem then he need to contact Sage Accpac Certified consultant or local Accpac partner.



Posted by on May 29, 2011 in Accpac Support


Tags: , ,

#11 Sage Accpac Financial Reporter

Date: 22 May 2011

Sage Accpac Financial Reporter is very easy and strong reporting tool that Sage includes in General Ledger of Accpac. Financial reporter install with the Installation of the Sage Accpac GL (General Ledger). Since financial reporter using Excel for all the reporting, its strength to generate and represent report increase widely. Apart from using formulas and parameters given by financial reported we can use all the functions, formulas, graphs and features that are in standard Microsoft Excel. Further there is option to include the drilldown so that we can get more from the same report and get into the details of the figures that a FR (Financial Report) is showing.
Let’s tell you an interesting fact. If I am not wrong then many Sage Accpac consultant and end users even having couple of years’ experience in Sage Accpac are not very familiar with Sage Accpac FR. On a contrary FR is so simple and user friendly that even end user that do not have any technical knowledge can build a FR according to his need and represent the required data in a representable manner. Since FR using Excel therefore it become very easy for a person to build FR, who is already using excel. And the fact is almost all the accountant and end user of Sage Accpac has already worked on the Excel. Therefore we can say it is easy for all consultant and end user to build and use FR.
When we install Accpac GL we get FR inside GL. There we can see two icons “Print Financial Statements” and “Statement Designer”.

“Print Financial Statements” is used to print the financial report that we have already build. Here we can give different parameters that can be used to get our desired data on the report.
Another on is “Statement Designer” that is used to generate the FR. Statement Designer opens the MS Excel with the additional add-on utility called FR. Where we can find tools that assist us in writing new FR.
The first and most important step to write a FR is to define report specification. Each financial statement is defined in a report specification. The specification tells Financial Reporter what data is included on the statement, and how it should be formatted. Because the specification is actually a worksheet, you can use standard worksheet formulas, formatting commands, and graphing abilities to embellish the report, and you can create multidimensional models to perform further analysis on the data. To define report specification in the Excel, define a new name “SPEC” under formulas after selecting the rows and columns of your excel sheet.
If there is no Spec range in the financial statement worksheet, the Financial Reporter recalculates the sheet and prints it when you print the report. Any Sage Accpac formulas will be updated with the latest values from General Ledger.
We can redefine the spec range in our worksheet after report generation, for that use FR Clear to clean up the output area, and then make changes to the spec range.

The first four columns of the Spec range (A through D) are reserved for control information for the Financial Reporter. All other columns form a template which determines the appearance of the report and the type of data which will appear. The Spec range is already defined in all financial report specifications that come with the Financial Reporter. Similarly, if you are converting a specification from ACCPAC Plus for DOS, the conversion macro will define the Spec range.

Column A to D in FR

The contents of column A of each row (the first column of the Spec range) determine the purpose of all the columns in the row.
There are five types of rows in a report specification:
• Comment rows
• “As is” rows
• Default rows
• Title rows
• Body specification rows

If column A starts with “..”, the row is a comment row.
If column A is blank, the row is part of the report and will be printed as is.
If column A starts with “\\”, the row sets up defaults for the remaining rows on the report. You can have more than one default specification row; each one applies to the rows between it and the next default row.
If column A starts with “\T”, the row defines a title line on the final report. You can have as many title lines as you want to appear at the top of each page of the report (immediately under the header lines).
If column A contains any other data, that data is assumed to be a reference to one or more account numbers. Furthermore, if an account number appears in column A, it generally means that all the printed columns in the row will contain data from that account.

Although you specify account numbers in column A of the report specification, you can use columns B, C, and D to refine the account selection.

Column A can specify a range of accounts; it determines what Financial Reporter does when it generates the financial report.
Column B can contain a filter to restrict the range of accounts in column A to a particular account group (or to an account type or account name). Column B can also refer to the filter for posted transactions if Column D refers to listing by posted transaction details or by posted transaction consolidated details.
• Column C can remove any lines from the report that have zero balances (use “Z” to omit zero‐balance lines) (or meet condition such as “balances less than $1,000”).
• Column D can consolidate the balances of the range of accounts, consolidate by account segment, list each account separately, consolidate all values from retrieved optional fields, or print the optional field value of the first retrieved record. This can also be consolidated by transaction date and posting sequence.

The result can look like this:

The report template actually starts in Column E, the fifth column of the Spec range. Any value or formula placed in the report template area is generally part of the final report. The following worksheet shows a report specification displayed in formula view:

The ʺ\ʺ in the right three columns indicates that each cell value will be generated from the default formula set for the column. (The Financial Reporter inserts the account reference in column A into any default formula that requires the account reference parameter.)

Further we can see that in column G14 to I14 we have used Excel formulas to calculate the total.

At last I can say that by using FR we can build much complex reports that might be difficult to achive through Crystal.


1 Comment

Posted by on May 22, 2011 in Accpac FR, Sage Accpac



#10 Accpac Financial Report Problem

Date: 15/05/2011

This week I face a very strange problem with the Sage Accpac Financial Report. This is very interesting and will be helpful for many.
Sage Accpac Financial Reporter is a tool that gives the power to the end user to design the financial reports. Last week a user of Sage Accpac called me and narrated his story that “Profit and lost report” giving different output on different Client machines. I did not believe him but asked him to check the version of Office on the both the machine.
He called me again after about half an hour and told me the same story. I planned a visit there and investigated the issue by my own. First when I saw the same report giving different output on different Systems it was surprising to me. I checked everything in Accpac but everything was correct. Now after being sure nothing wrong with Accpac, I start thinking on other aspects.
The major difference I found is in the OS. Both machine having different OS. One with Windows XP giving correct output whereas one with windows7 giving wrong output. Then to my surprise Sage Accpac Financial reporter range formulas working differently on different version of OS. Following is my analysis.
While designing a Financial Report in Accpac we generally give the account range in the Column A. let’s say company is using two account segment one with 5 digits and second with 3 digits. We can give the range in column A like “60000:61000”. This range suppose to print all the accounts having the first account number between 60000 and 61000 irrespective of the number in the second segment.
Again coming to the problem, the Financial Reporter on the System with Windows XP is working exactly as explained above. But on the system with Windows7 it is reading the accounts between 60000-000 and 61000. Here financial report is unable to read any record further from 61000 and cannot print accounts having second segment.


To rectify the above issue, I change the way range was given. Instead of 60000:61000 I gave range like “60000:61000-999”. Although both ranges should be treated the same way according to financial report guide lines but it is treated differently on different versions of OS and Office.

Generally solution for big issues is small and easy but tricky to find.



#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


#8 Accpac GL Sub-Ledger

Date: 01/05/2011

Continue from last post…
The following section describes fields:
BASEGL This field contains the base General Ledger number that the substitution processor will edit. This number may be either formatted or unformatted.
This field will be unchanged by a Process call.

SEGNUM1..9 This is the 6-character segment key code in General Ledger specifying a named segment to substitute. Note that while this key is numeric it is not the segment number in the account — consult the relevant General Ledger documentation for more information on segment values and account structures. Unused fields should have the key set to “000000.”
These fields will be unchanged by a Process call.

SEGVAL1..9 This is the up-to-15 character segment value that the substitution processor will substitute for the corresponding segment in the base General Ledger number.
These fields will be unchanged by a Process call.

SEGLENGTH1..9 These fields contain the lengths of the substituted segments. The length will be zero if the substitution processor did not find the corresponding segment in the raw
General Ledger number.

SEGINSERT1..9 These fields contain the offsets (1-origin) in the unformatted General Ledger number of the substituted segments. The offset will be zero if the substitution processor did not find the corresponding segment in the raw General Ledger number.

RESULTGL This field contains the resulting General Ledger number generated in the substitution process. This number is unformatted.

FORMATGL This field contains the resulting General Ledger number generated in the substitution process. This number is formatted.

OLDSEGVAL1..9 These fields contain the old segment values that the substitution processor substituted with the new segment values. The value will be blank if the substitution processor did not find the corresponding segment in the raw General Ledger number.
The positions correspond to the sequence in the input fields.

NEWSEGVAL1..9 These fields contain the new segment values that the substitution processor substitutes for old segment values.The value will be blank if the substitution processor did not find the corresponding segment in the raw General Ledger number.

NOTE: The client application should not specify the same segment key twice with different values; doing so may give incorrect results.


%d bloggers like this: