Crystal report can be substituted by pivot table and MS query
When crystal report (CR) was launched, it created a lot of excitement in the market. It was hailed as THE tool for business people to pull data from databases. With it, business people no longer have to depend on their IT team to extract those data required for decision making. They can tap into databases that used to be accessible only to the IT team in the company. With it, business users no longer have to wait for days or weeks to get hold of that report for decision making. It even
allows users to prepare reports that can be used for management reporting.
Crystal report also allows users to perform customised calculations by setting up formulas in the report. The application can perform the calculation based on the pre-defined formula and return only the results to the report. It also allows users to send just the report template to other users. The report pulls the data required from the database only when users open the report. It also allows users to present the report in multiple levels, i.e. organise and present data by sub-categories. For example, the data can be present first by division, then by product group, product type, etc.
Crystal report is very useful for large databases, particularly those which are stored in database servers. When it comes to small databases as MS Access, Dbase and other desktop databases, using CR would be an overkill. The cost of owning and maintaining CR is too high for smaller scale applications and databases. For smaller databases, use pivot table and MS Query. It works like CR. And this option is free if you own Microsoft office. This option is not known to a lot of Excel users because the installation of MS Query must be installed manually. And it does not come with the standard installation of MS Office.
MSQuery pulls out data from databases. In fact, it can be used to pull out data from database servers too, just like CR. The only limitation in using MSQuery to pull out data is memory. However, I have not really encountered any problem when using MSQuery to pull data from SQL Server. After the data is pulled out from the database or database server, it can be output to a pivot table . Once this is done, you can drag and drop the fields and present the database you want easily. It can also present the data in multiple dimensions like the CR. The functions and features provided by pivot table is very similar to those offered by crystal report. Once you know how to use pivot table with MS Query, you may not take a look at crystal report or other reporting tools again.
Do you have any question regarding crystal reports or pivot table? Ask Jason Khoo.
