#18 MS Excel as Business Intelligent Tool

Date: 5 April 2012

ERP- Enterprise resource planning as name suggests it is to plan about the resources in an Enterprise. When we say planning that means forecasting future and making decision that will help an Enterprise to grow at the steady pace. Good decisions which define the fate of an organization can only be made by peeping into the piles of data which have been accumulated by the course of organization’s operation. As a saying goes “One cannot make bricks without mud”, similarly one cannot make good decision if there is no data to work on. Further this data should be used efficiently to make forecasting dissension. One of the advantages of ERP is that it securely builds that heap of data to be used for the future forecast.

Further to utilize the data accumulated by ERP, effective Business intelligence tool is required. There are various tools available in the market that may be good what they claim. But what can be better than the one which is already known to most of the people. Especially in the management teams around the world there are hardly anyone who doesn’t know Microsoft Excel. Yes, MS-Excel can be used as BI tool also as they say “Known devil is better than unknown angel”.

Let’s take an example to see how we can use excel as BI tool. There is a sales head that wants to see the past performance of the company to make various decisions. Let’s see how we can accomplish this in MS-Excel and how we can easily convert data to a graph.

Microsoft Excel shipped with a very handy tool called Microsoft query. Microsoft query give power to the users to extract data from almost any data source. We just need to create an ODBC in the System DSN. In MS Excel “Data” tab there is option to extract data from “Other sources”.

There is an option called “From Microsoft Query”. When you select this option you will get the screen to select the data source.

 

Select your data source and click “OK” to open Microsoft Query screen as bellow:

 

Here in Microsoft query you can enter you SQL statement to extract the sales data. Once you did that you can press “Return Data” button to send the data on you selected excel sheet. The data looks like as follows:

 

Now the same data can easily be converted to graph or any other type of diagram that user wants to represent data in much better way:

 

Now it’s up to the capability of the individual to make it more and more interactive. Above is an easy example, but we can make and design much complex reports as well where user can enter parameters to extract the data that he wants and see it in different formats like table, bar diagram, pie chart etc.

Aggyey

 

#17 Advantage and Disadvantage of ERP

Date: 24 July 2011

Enterprise resource planning (ERP) integrates internal and external management information across an entire organization, embracing finance/accounting, manufacturing, sales and service, customer relationship management, etc. ERP systems automate this activity with an integrated software application. Its purpose is to facilitate the flow of information between all business functions inside the boundaries of the organization and manage the connections to outside stakeholders

Advantage and Disadvantage

Advantages:

Integration

Integration can be the highest benefit of them all. The only real project aim for implementing ERP is reducing data redundancy and redundant data entry. If this is set as a goal, to automate inventory posting to G/L, then it might be a successful project. Those companies where integration is not so important or even dangerous tend to have a hard time with ERP. ERP does not improve the individual efficiency of users, so if they expect it, it will be a big disappointment. ERP improves the cooperation of users.

Efficiency

Generally, ERP software focuses on integration and tends to not care about the daily needs of people. I think individual efficiency can suffer by implementing ERP. The big question with ERP is whether the benefit of integration and cooperation can make up for the loss in personal efficiency or not.

Cost reduction

It reduces cost only if the company took accounting and reporting seriously even before implementation and had put a lot of manual effort in it. If they didn’t care about it, if they just did some simple accounting to fill mandatory statements and if internal reporting did not exists of has not been financially-oriented, then no cost is reduced.

Fewer personnel

Same as above. Less reporting or accounting personnel, but more sales assistants etc.

Accuracy

No. People are accurate, not software. What ERP does is makes the lives of inaccurate people or organization a complete hell and maybe forces them to be accurate (which means hiring more people or distributing work better), or it falls.

Disadvantages:

Expensive

This entails software, hardware, implementation, consultants, training, etc. Or you can hire a programmer or two as an employee and only buy business consulting from an outside source, do all customization and end-user training inside. That can be cost-effective.

Not very flexible

It depends. SAP can be configured to almost anything. In Navision one can develop almost anything in days. Other software may not be flexible.

Aggyey

 

#16 Accpac Finder

Date: 17 July 2011

The Finder is a pop-up window that displays records in a column format for a view. Records are displayed in one of two formats, selected by the user. The user can also select the order for record display. If desired, the caller can provide a filter to restrict the records displayed by the Finder.

Uses of the Finder
The Finder can be used for:
Inquiry
Searching
Selection

Inquiry In this case, the Finder provides a detailed list of items. The user can select the fields to be displayed by the Finder.
Searching In this case, the user chooses the Finder button to initiate the search for a specific item. The selection is placed in a buffer and the Finder is terminated. For this purpose, the Finder should be started modally by the calling program, so that no further work can be done by the caller until a selection has been made.
Selection In this case, the Finder selects an item to be operated on by another object. After the item has been selected, an object is started, and the selection is passed to it. After the object has been started, the Finder can either remain or terminate. If the Finder remains, the object started by the Finder is modeless. Subsequent Finder selections can be made and further instances of the object started. The objects started can be either peers or children of the Finder. If an object started is a peer, the user can terminate the Finder, but any other objects started by the Finder will not be terminated. If an object started is a child of the Finder, terminating the Finder causes all the Finder’s children to also be terminated.
If the Finder terminates after starting an object, the object obviously must have been started as a peer. The Finder provides the primary key for the selected item in the key area of the object that is started. It is also possible for the caller to provide a user area to be passed to the object started by the Finder.

Following is the code to call the finder that will show the item form ICITEM table in Accpac

Private Sub Cmditemfrm_Click()
Dim fnd As AccpacFinder.ViewFinder
Set fnd = New AccpacFinder.ViewFinder
fnd.Session = AccpacSession
fnd.ViewID = “IC0310″
fnd.DisplayFieldIDs = Array(7, 3, 5, 1, 8 )
fnd.ReturnFieldIDs = Array(7, 3)
fnd.Filter = “” ‘condition here
fnd.InitKeyType = USER_PASSING_INIT_KEY
fnd.AutoTabAway = False
If fnd.Finder = True Then
‘Code here
End If
End Sub

Aggyey

 

#15 Accpac Views Protocol for INSERTING and UPDATING Data

Date: 10 July 2011

When we are using Accpac views to insert data into Accpac most of the validation it does by itself. This is one of the most important reasons, why we need to use Accpac views to insert or update data into Accpac Database.

The protocol that Accpac follows while doing Insert is as follows:
1. If the next header number is generated by the view, Put zero in the header number field and use Init to generate the next available header number. If the header number is specified by the caller, use Init to initialize the fields of the header, then Put the header
number in the header view.
2. Put the fields in the header.
3. Init detail to initialize the fields.
4. Put zero into the detail number field to insert from the start.
5. Put values in the other detail fields.
6. Verify detail. (Optional.)
7. Insert detail.
8. Go to step 5 until no more detail.
9. Verify header. (Optional.)
10. Insert the header. (This will do a Post of the details.)

Protocol for updating the data in Accpac is as follows (Including deleting the details):
1. Put header key into header view.
2. Read or Browse/Fetch the header view to get to the header.
3. Put the fields to be updated in the header view. (Optional.)
4. Put detail key into the detail view.
5. Read or Browse/Fetch the detail view to get to the detail.
The header number in the detail view should have been set by the header (because of the explicit composition).
6. Put the fields to be updated in the detail view.(Optional.)
7. Verify the detail. (Optional.)
8. Update or delete the detail.
9. Go to step 4 to process another detail.
10. Verify the header. (Optional.)
11.Update the header. (This will do a Post of the details.)

Browse/Fetch is the functions that help us to navigate through the data. Browse property do not have the “Where” or “Having” clause so we can use if condition to filter our data.

Aggyey

 

#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.

Aggyey

 

#13 Accpac Help Not working

Date 05 June 11

Last week one of my clients faces an issue where on his PC Client help was not working. And the error he getting is “Internet Explorer cannot display the webpage”. Is it related to Sage Accpac? No way, this error is related to Accpac.
Then why it is coming when pressing F1 to access Accpac help. It is since Accpac Help is not in the URL allows list setting in the registry.

The solution to the above problem is:
Log is to the registry by following command
“Regedit”
Then follow the following path:
MyComputer/[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp\1.x\ItssRestrictions]
If not available then create one.
Further add following two values under the above key:
“MaxAllowedZone” as Dword value and set it either 1 or 2 or 3 or 4
“UrlAllowedList” as string value =”Accpac server help folder path”
After doing the above just restart your PC and enjoy your Accpac help is working now.

Aggyey

 

#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.

Aggyey

 

#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.

Aggyey

 

#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.

Solution:

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.

Aggyey

 

#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.

Example:

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

GLPJCON1.rpt.
[GLPJCON.DLL]
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
P1=SAMLTD
P2=ADMIN
P3=
P4=ENG
**********
Aggyey