Time to get familiar with crystal reports!!

So, Here is the reporting tool your client and you looking for “Crystal reports”. It contains built in features that allow users to design reports easily. These features include templates, ready-to-use data drivers, and flexible charting capabilities.

You can browse through data, sort it, filter it, apply custom groupings and explore the generated reports, and share the reports through the internet or through a secure server, can access data from most widely-used databases and can integrate data from multiple databases within one report using Open Database Connectivity ( ODBC ) and much more.

Those are just some of my reasons. One thing though – That its not the complicated tool but the people who complicates it. So if you know how to exploit it then it will unlock a world in which you can only
astonish and say to yourself, “why didn’t I do this before?”

Here I will start with what we were looking for in this blog that is filtering data in crystal reports.
There are multiple ways to filter data:

→ Using the Select Expert—This simple method provides a visual way to specify filtering.

→ Using the Record Selection Formula—This more granular, yet powerful, method involves creating a custom formula language expression to determine the filter criteria.

SELECT EXPERT

To use select expert go to Report Menu and select Select Expert option and you can also find a button on expert toolbar.

select expertFigure-1

To create a filter, first select a field (includes fields of reports as well as from database).

Now here I will take an example of a table employee used in crystal report to show use of select expert.

operations

Figure – 2

Here, a drop box contains number of option to be applied on that field for filtering data eg. Equal to, not equal to,one of etc

Eg. Suppose we want all employees who are in .Net department then we will select field department, operation “is like” and DotNet from dropdown which browse data for that particular item.
Now crystal report will automatically create formula which can be seen by clicking Show formula button.
So now crystal report will display all employees whose department is DotNet.So its pretty easy to apply
selection in crystal reports

select expert tab

Figure – 3

NOTE:
“Record Selection applied on database fields is faster than record selection applied on user defined fields(User defined fields are formula fields that we create for our reports to perform operation to customize report and it starts with syntax @).

rpt

Figure – 4

If selection is applied on user-defined fields then all data will be retrieved and that crystal report will apply calculation on filtered data. May be the data is not visible to you but it is there in background. So keep this in mind when dealing with large amount of data.”

Now we will move towards second method.

Record Selection Formula

To meet complex requirement which Select Expert can not fulfill, you have to create a custom filter and
for creating custom filters, you can use crystal reports built-in formula language which provides high level of controls in reports creation.

Formula Editor can be invoked by clicking the Formula Editor button in the Select Expert or by selecting the Report menu and choosing Selection Formulas, Record.

Write your customized formula and apply. That’s it 🙂
Like you now want to select employee whose salary will be greater than 50,000 on adding 20,000 to their salary
and belong to DotNet department.
So,

formula

Figure – 5

“AND” and “OR” are used in formula to customize selection according to your needs and works in similar manner as we already familiar with 🙂 .

Two selections are there in crystal report i.e. record selection and group selection. Grouping is a function of crystal, work in same way as fields,but one thing to keep in mind that before performing selection it requires all data same like user defined field. Database field selection is more efficient but sometimes you don’t have all required field or want to apply filter on calculated fields then group selection comes into play.

TIP:
“The more concentrated you make your “and” clauses the more focused the query be.”

So,start turning information from almost any data source into a sophisticated, interactive report – easily – with SAP Crystal Reports.

 

Written By: Kaveri Saxena, Software Engineer, Mindfire Solutions

Advertisements

Posted on September 25, 2014, in Reporting Tools and tagged , , , . Bookmark the permalink. Leave a comment.

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

%d bloggers like this: