Hi Steemians,
This post is the third of the series of blogs I am writing on Reports design tool Crystal Reports. Below are the links for earlier posts in that order.
1 Overview of Crystal Reports - Part 1
2 Overview of Crystal Reports - Part 2
We saw about history, versions, vendors and how to create a data source for Crystal Reports in earlier posts.
In this post, let’s create a simple report using the tables provided in Oracle 11g Database. This includes creating a select statement to select required database columns and creating joins between multiple tables.
We checked about creating a data source in second post and now, let’s use the same data source and select the required tables for creating a simple tabular report.
We use default tables provided in Oracle such as employees and departments and create an “Employee Information Report” as in below steps.
Logon to Oracle database using the credentials and database service name as in below screenshot
Once we logon, we get all the database objects like tables, views, stored procedures as in below screenshot
Note the highlighted parts. HR is the schema which contains the tables, views etc. And the other highlighted part “Add Command” is the important one as it provides an option to write freehand SQL using different tables, creating joins between those and restricting the data retrieved using a where clause.
Below 2 screenshots provides more information on different objects under HR schema and how to use Command Object respectively.
As you can see in previous screenshot, we have an option to include the freehand SQL to pull the data based on our requirement.
P.S: There is also an option to directly select the tables and Crystal Reports will automatically build the joins based on relative column names in different tables, which can be viewed and changed in case the join built by Crystal is wrong.
So, once we click OK in above screenshot, we will be taken to different screens as below with options to select the fields to be displayed in report output, grouping of data, any summary to be created for grouped field(s).
Select the fields to be included in the report and click Next.
Select fields for grouping (E.G Group data by Department) and click Next.
If any Summary of data is required (E.g. Sum of Salary for each Department) and click Next.
So, now everything is set to create our first report which will display the Employee details grouped by Department as in below screenshot. Note that there is a report heading included to display the report title “Department-wise Employee Details”.
As usual, I urge you all to provide your comments, upvote and resteem if you like the post.