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.

DBCC Shrinkfile unable to Shrinkfile

Many times it happens that you will not able to shrink datafile using DBCC Shrinkfile command. There may be various reason/error for it. One of them says as mentioned below:

"file id of database id cannot be shrunk as it is either being shrunk by another process or is empty"

This happens when already a DBCC shrinkfile has been run and due to some reason it was unable to shrink the file and the internal files are not re-organized or file has not grown to shrink. Or SQL server somewhere it feels that the file you are shrinking is in middle of Shrinking process

One of the solution for this file is to add some MB of data to the file which you are trying to shrink and then use DBCC Shrinkfile. This will work as adding a few MB to the data file resets an internal counter or switch that tells it it's not in the middle of a shrink now

Impersonating Sql User

As a SQL server DBA many times you may get to hear from users that he is not able to access the database, SQL server or any of the objects in SQL server. This may sometimes that the user is passing a wrong credentials or may be is trying to connect to SQL server through some applications but due to some ODBC / OLEDB connectivity problem the application is not able to connect even though user is passing correct credentials. Sometimes the issue may be genuine too that he is actually not having the access. So as a DBA what we can take up as a initial troubleshooting is to Impersonating the SQL user account to check whether is user having access to database or not. So below mentioned are the steps to be carried out.

Step1. Connect to SQL Server with Sysadmin Privilege account ( Normally DBA’s will be having SysAdmin Privilege)

Step2. Select a New Query from Menu and go to Database the user is trying to access using below command.

USE [ProblemDatbaseName]

GO

Step3. Now Impersonate the account using below SQL query and check for the status.

 

EXECUTE AS USER = 'YOURDOMAIN\ACCOUNTNAME'

If the user account is invalid you will get the below Error:

Msg 15404, Level 16, State 11, Line 1

Could not obtain information about Windows NT group/user 'YOURDOMAIN\ACCOUNTNAME', error code 0x534.

Else if you get the output as “command (s) completed successfully” then check for the access using below command.

SELECT SUSER_NAME(),USER_NAME()

Now the above query will give you username and access.

If you want to check for specific permission you can do so by using below command. If the result set returns 1 means he is a member of that permission for example I have taken db_owner in below example. If not it will return you 0.

SELECT IS_MEMBER('db_owner')

Step 4. Now run REVERT to go back to execution from your credentials.

The above process can generally be used when testing a database user’s permission. Hope this is of some help. Thank you.

Technorati Tags: Impersonate,SQL server,SQL Login

Cannot use the PAGE granularity hint on the table

This seems to be an very interesting error which one may receive while running DBCC ShrinkDatabase command.

The full error is as follows

Cannot use the PAGE granularity hint on the table “tablename” because locking at the specified granularity is inhibited.

The error occurs when you have one or more non-clustered indexes created on table without a clustered index on table. One should always create a clustered index on table, without which it does not make more sense.

The resolution for above error is just to create a Clustered Index on the table on which you are getting error. Will try to dig in and get more info so as to what exactly causes this error and will update you all.

Hope this information is helpful.

Technorati Tags: Page granularity,page,granularity,locking

Dropping A Role in SQL server using T-SQL

It happens so that when you drop/delete a Database Role in SQL Server Database, the Role members who were assigned to this role wont get de-associated from this Role. No doubt the role will be dropped/deleted but the member will be having this Role still existing in their permission list. So I came up with the below T-SQL which will first de-associate all the role member’s from the role to be dropped and then it will go ahead and drop the role. For Testing Purpose, I have taken Role “db_execute” as the role which needs to be dropped/deleted.

/* De-Associating RoleMember From Role To Be Dropped*/

USE [AdventureWorks]

GO

 

DECLARE @RoleName sysname

set @RoleName = N'db_execute'

IF  EXISTS (SELECT * FROM dbo.sysusers WHERE name = @RoleName AND issqlrole = 1)

Begin

 

      DECLARE @RoleMemberName sysname

      /* Cursor to Loop in for Each Member have the Role Privilege and Drop RoleMember */

      DECLARE Member_Cursor CURSOR FOR

      select [name]

      from dbo.sysusers

      where uid in (

            select memberuid

            from dbo.sysmembers

            where groupuid in (

                  select uid

                  FROM dbo.sysusers where [name] = @RoleName AND issqlrole = 1))

 

      OPEN Member_Cursor;

 

      FETCH NEXT FROM Member_Cursor

      into @RoleMemberName

 

      WHILE @@FETCH_STATUS = 0

      BEGIN

 

            exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName

 

            FETCH NEXT FROM Member_Cursor

            into @RoleMemberName

      END;

 

      CLOSE Member_Cursor;

      DEALLOCATE Member_Cursor;

      /* End Of Cursor */

 

end

GO

/* Checking If Role Name Exists In Database */

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_execute' AND type = 'R')

DROP ROLE [db_execute]

GO

Technorati Tags: Role Member,Drop Role,Role,Member,Drop Role Member

SELECT @@SERVERNAME Showing Null

SELECT @@SERVERNAME

With multiple instances of SQL Server installed, @@SERVERNAME returns the following local server name information if the local server name has not been changed since setup.

Instance	Server information
Default instance	'servername'
Named instance	'servername\instancename'
failover clustered instance - default instance	'virtualservername'
failover clustered instance - named instance	'virtualservername\instancename'

It may happen so that when you run SELECT @@SERVERNAME, it may return NULL value.

Whenever you change the Network Name of Server Name, @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.

To make SELECT @@SERVERNAME reflect the new changed Network Name perform the below task.

USE master

GO

EXEC sp_dropserver 'OLDSERVERNAME'

GO

EXEC SP_addserver 'NEWNETWORKNAME','LOCAL'

GO

Once done please Stop and Start the SQL Services and then Query SELECT @@SERVERNAME. Bingo You are Done. Now you should be getting the New Network Name.

Logon failure: unknown user name or bad password

This is one of the very often faced error for job failure.

Exact error goes as follows:
Unable to start execution of step (reason: Error authenticating proxy , system error: Logon failure: unknown user name or bad password.). The step failed.

This happens when the secret word(password) you provided while creating a credential for a proxy has got expired. Exact scenario may be, you have used a domain account which enforces password expiration policy and you have changed you password. This happens because sql server resolves your changed password when you authentication mode is set to accept mixed mode, but the credentials which you have created does not accept this or resolve the change. Now you if you have to make this proxy work you need to delete the credential and again create it with new password and map to proxy. This sometime may again hit your jobs. So better way is to alter the credential and provide the new password. This can easily be achieved by below script by replacing the values according as per your needs:

Script:
USE [master]
GO
ALTER CREDENTIAL [ProxyCredentialsName] WITH IDENTITY = N'DOMAIN\ACCOUNTNAME', SECRET = N'NewPasswordchanged'
GO