Custom Reports using Crystal Reports and MySQL

1 Introduction

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.

2 About products/Technologies

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

Crystal Reports is designed to work with your database to help you analyze and interpret important information. Crystal Reports makes it easy to create simple reports, and, it also has the comprehensive tools you need to produce complex or specialized 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.

Extend reporting to the Web

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.

 

Incorporate reports into applications

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.

2.2 About MySQL

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.

2.3 Creation of Crystal Reports

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.

http://dev.mysql.com/downloads/connector/odbc/5.1.html

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.

3 Conclusion

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.

 

Advertisements

2 thoughts on “Custom Reports using Crystal Reports and MySQL

  1. I’m impressed, I must say. Seldom do I come across a blog
    that’s equally educative and engaging, and let me tell you, you
    have hit the nail on the head. The problem is something not enough men and women are
    speaking intelligently about. I’m very happy I came across this during my search for something relating to this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s