Pages

Monday, March 28, 2011

Custom Reports using Crystal Reports and MySQL


Reporting is one of the main important factors and features and part of IT Sector. Reports are important in various aspects such as Forecasting of Business, Trend Analysis, Service review, display statistics to client, track records etc.
We have various reporting tools in market like Microsoft SQL Server Reporting Services, Microsoft Excel, and Crystal Reports etc.  
Select which product of reports or version is the not the question to be asked, But how well can be the report presented is the question to be asked.
So I have decided to take you through some of the key features and how a customized report can be prepared and present using crystal reports and the database as MySQL. This can also be done with other databases as well.
Whenever we are working on some technologies we need to know something about the technology/product we are working on. So let us get to know some basic information about the products.

2.1 About Crystal Reports

Create any report you can imagine
Crystal Reports is designed to produce the report you want from virtually any data source. Built-in report experts guide you step by step through building reports and completing common reporting tasks. Formulas, cross-tabs, sub reports, and conditional formatting help make sense of data and uncover important relationships that might otherwise be hidden. Geographic maps and graphs communicate information visually when words and numbers are simply not enough.
The flexibility of Crystal Reports doesn't end with creating reports — your reports can be published in a variety of formats including Microsoft Word and Excel, E-mail and even over the Web. Advanced Web reporting lets other members of your workgroup view and update shared reports inside their web browser.

Application and web developers can save time and meet their users’ needs by integrating the report processing power of Crystal Reports into their database applications. Support for most popular development languages makes it easy to add reporting to any application.
Whether it's the web master in IT, the promotion manager in marketing, the database administrator in finance or the CEO, Crystal Reports is a powerful tool designed to help everyone analyze and interpret the information that's important to them.
The MySQL database has become the world's most popular open source database because of its high performance, high reliability and ease of use. It is also the database of choice for a new generation of applications built on the LAMP stack (Linux, Apache, MySQL, PHP / Perl / Python.) Many of the world's largest and fastest-growing organizations including Facebook, Google, Adobe, Alcatel Lucent and Zappos rely on MySQL to save time and money powering their high-volume Web sites, business-critical systems and packaged software.
MySQL runs on more than 20 platforms including Linux, Windows, Mac OS, Solaris, HP-UX, IBM AIX, giving you the kind of flexibility that puts you in control. Whether you're new to database technology or an experienced developer or DBA, MySQL offers a comprehensive range of database tools, support, training and consulting services to make you successful.
Now we are done with a brief introduction to the technologies we shall now go ahead with creating crystal reports.

2.3.1                     Installing MySQL connector for ODBC connections.

You can download the MySQL connector from the below link.
Select the appropriate msi package from above link and download it on to your machine where you have installed crystal reports. Install the connector using instructions provided by the installer during installation.

2.3.2                     Creating DSN to establish connectivity from Crystal Reports to MySQL server.

a)     Go to control panel and select Administrative tools.
b)    Under Administrative tools select Data Sources (ODBC) which will open up ODBC Data Source Administrator.
c)     Under System DSN click on Add and select MySQL ODBC 5.1 driver and click finish
d)    When you click on finish MySQL Connector/ODBC Data Source Configuration wizard appears. Fill up the appropriate connection parameters to MySQL server and click ok.
e)     Now click on OK and close ODBC Data Source Administrator.
That’s all we are done with DSN connection.

2.3.3                     Designing Crystal Report.

Now we are done with creation of DSN to establish connectivity between crystal reports and MySQL server, let us start designing a report. Below steps would help you to create a simple crystal report and export it to format of your choice from available choices
a)     Go to Start à All Programs à Crystal Reports 2008 and select crystal reports 2008.exe.
b)    On Start Page Under “Start a New Report” select Report Wizard to open up Standard Report Creation Wizard.
c)     In Standard Report Creation Wizard under Available Data Sources, Expand Create New Connection, search for ODBC (RDO) and expand it, which will open ODBC (RDO) screen.
d)    In ODBC (RDO) screen under available Data Source Name, select the DSN which was created in Step 2.3.2 and click next. Enter username and password and select the database and click finish.
e)     Now you will be able to find the ODBC connection, expand it and click on Add Command, which will open up “Add command to Report” screen.
f)     Enter the SQL Command through which you want to extract custom reports from MySQL database and click ok.
g)    Now you will be able to see the ODBCàCommand under Selected Tables menu in “Add command to Report”, now click next.
h)     You can now see available columns from your query in Available Fields. Select Fields to Display in report and click on the Arrow marks to Move the Fields from Available Fields to Fields to Display and click next.
i)      Now if you want to group the data by any of the field you can select the field and click on arrow mark to group the output data and click next.
j)      If you want to summarize the fields you can select the field from which you want to summarize the data and click next.
k)     Select the criteria to sort the data depending upon the Group and select next.
l)      Select the chart type if at you want a chart in Report and click next.
m)   If you want any of your fields to be displayed as a subset of main data select the fields and click next else just click next.
n)     Select the template in which you want your data to be displayed and click Finish.
o)    Now you will be able to see your report preview and design tabs. You can go to design tab and customize the look of the report.
p)    If you want to export the report, then go to fileàexportàexport report.
q)    Select the export format in which you want your report and select destination and click ok.
Bingo you are done with your report.
We are now done with creation of customized report preparation. Next time you want to use the report just open the .rpt file which you saved and go to report menu and click refresh data and export the report. This was just a basic creation of a report. You can just play like anything and prepare more complicated and customized reports using crystal report features like functions, parameters etc.
You can also use any RDBMS as back end to extract the report with appropriate drivers and connectors installed.

4 comments:

  1. Thank you for the step-by-step guide. You saved me a considerable amount of trial-and-error. Your instructions were flawless!

    ReplyDelete
  2. Thanks Buddy, you saved my day.

    ReplyDelete
  3. Can you please include which versions of CR and MySQL this will work with? Thank you.

    ReplyDelete