Mastering Crystal Reports 2013 Made Easy Training Tutorial

Crystal Reports is a database reporting application used by many different types of businesses and industries to generate accurate and powerful reports that assist in business decision-making. If you need the flexibility to generate reports from many different types of database files in your organization, then Crystal Reports is definitely for you! While there are many fine database report-writing software applications available, you will find that Crystal Reports is very useful in allowing you to use the same report-writing tool to access and analyze various types of data sources such as Microsoft Access, Oracle, and Microsoft SQL Server, among many others. Learn this and more during this lecture.

You can access many commands in Crystal Reports by using the Menu Bar. The Menu Bar is the toolbar at the top of the Design View which displays the command categories of “File,” “Edit,” “View,” “Insert,” “Format,” “Database,” “Report,” “Window,” and “Help.” Clicking a command category displays the names of functions which would logically belong to that category. Learn this and more during this lecture.

You can use the buttons on the toolbars in Crystal Reports to quickly and easily gain one-click access to some of the most commonly used commands and functions. Let’s review the names of the various toolbars and what functions are available on these toolbars. While many of the functions listed many not seem familiar yet, you will see how to use each function later. Learn this and more during this lecture.

The Design view of a report is the view in which you will spend the majority of your time as you create your report. When you create a new report, it is displayed in Design view by default. You can see the “Design” tab in the upper left corner of the report design section. Once you have previewed a report using the “Print Preview” function, there will also be a “Preview” tab in that same area as well. You can then click the names of the two tabs to switch between the two views. Learn this and more during this lecture.

To create a new blank report in Crystal Reports 2013, you can click the “Blank report” hyperlink in the “Start Page” or select “File| New| Blank Report…” from the Menu Bar. This will create a new, blank report and launch the “Database Expert” dialog box where you can select a report data source. Learn this and more during this lecture.

After you create a new report, you will see the “Database Expert” dialog box. This dialog box allows you to select the source of the data that will be used for the report. The pane at the left side of the dialog box lists the set of available types of data connections which you can use to connect to the desired data you wish to use for the report. Learn this and more during this lecture.

If you click the small plus sign next to the “Access/Excel (DAO)” folder in the “Create New Connection” section of the “Database Expert,” you will be presented with the “Access/Excel (DAO)” dialog box. You use this dialog box to create a data connection to a Microsoft Access file, a Microsoft Excel file, or one of many other file types using DAO (Data Access Objects) as the connection type versus an ODBC (Open Database Connectivity) connection. DAO is an object that works with Microsoft’s Jet database engine used in Microsoft Office. ODBC is simply a standard that allows any application to communicate and manipulate a variety of different database applications by using a standardized set of SQL (Structured Query Language) statements. SQL is the language used by all relational database applications to extract information from database tables. Learn this and more during this lecture.

If you click the small plus sign next to the “ADO.NET (XML)” folder in the “Create New Connection” section of the “Database Expert,” you will be presented with the “ADO.NET (XML)” dialog box. This option is used when you wish to push data to your report from an ADO.NET data set. This option also supports pulling data from a custom-developed DLL that can return an ADO.NET dataset, as well. Learn this and more during this lecture.

If you click the small plus sign next to the “Database Files” folder in the “Create New Connection” section of the “Database Expert,” you will be presented with the “Open” dialog box. You use this dialog box to create a data connection to one of the many types of available databases in Crystal Reports. Learn this and more during this lecture.

If you click the small plus sign next to the “Java Beans Connectivity” folder in the “Create New Connection” section of the “Database Expert,” you will be presented with a dialog box that allows you to specify your desired Java data source. Learn this and more during this lecture.

You can click the small plus sign next to the “JDBC (JNDI)” folder to make a data connection to a Java Database Connectivity (JDBC) data source or to a data source that has already been identified using the Java Naming and Directory Interface (JNDI). To start, click the small plus sign next to the “JDBC (JNDI)” folder in the “Create New Connection” section of the “Database Expert” dialog box. Learn this and more during this lecture.

When you click the small plus sign next to the “ODBC (RDO)” folder under the “Make New Connection” folder in the “Database Expert” dialog box, you will see the “ODBC (RDO)” dialog box appear. You use this dialog box to make an ODBC connection to a data source. Learn this and more during this lecture.

When you click the small plus sign next to the “OLAP” folder under the “Make New Connection” folder in the “Database Expert” dialog box, you will see the “OLAP Connection Browser” dialog box appear. You can use this dialog box to select an OLAP data source for your report. Online Analytical Processing (OLAP) is a popular data storage format for multi-dimensional analysis of relational data. Learn this and more during this lecture.

You can click the small plus sign next to the “OLE DB (ADO)” folder under the “Create New Connection” folder in the “Database Expert” dialog box to create a data connection to an OLE data source. OLE is a connectivity methodology created by Microsoft that allows the exchange of data and information between applications. Learn this and more during this lecture.

You can click the small plus sign next to the “Salesforce.com” folder under the “Create New Connection” folder in the “Database Expert” dialog box to create a data connection to Salesforce.com. You can then access information stored on that server. Learn this and more during this lecture.

You can click the small plus sign next to the “SAP BW MDX Query” folder under the “Create New Connection” folder in the “Database Expert” dialog box to create a data connection to information that is stored in an SAP NetWeaver Business Warehouse (BW). You can connect to either queries that have been created with SAP Business Explorer (BEx) Query Designer, or you may connect directly to the data cubes in BW itself. Learn this and more during this lecture.

You can click the small plus sign next to the “SAP Info Sets” folder under the “Create New Connection” folder in the “Database Expert” dialog box to create a data connection to SAP R/3 InfoSets (previously known as “Functional Areas”) and ABAP Queries. Learn this and more during this lecture.

You can click the small plus sign next to the “SAP Operational Data Store” folder under the “Create New Connection” folder in the “Database Expert” dialog box to create a data connection to existing ODS information that is stored in an SAP NetWeaver Business Warehouse (BW). You can connect to existing ODS objects using this data connection, and because this information is not multidimensional or parameterized, it can be reported on quickly. This connection type also pushes record selection to the server, often increasing report processing performance. Learn this and more during this lecture.

You can click the small plus sign next to the “SAP Table, Cluster, or Function” folder under the “Create New Connection” folder in the “Database Expert” dialog box to create a data connection that uses the Open SQL driver to access SAP’s transparent tables, pool tables, cluster tables, views, and ABAP data clusters and functions. Learn this and more during this lecture.

You can click the small plus sign next to the “Universes” folder under the “Create New Connection” folder in the “Database Expert” dialog box to create a data connection to a selected universe on a BusinessObjects Enterprise server. First, you must select the name of your system from the “System:” drop-down and then enter your “User name:” and “Password:” into the text boxes that are available. Learn this and more during this lecture.

You can click the small plus sign next to the “XML and Web Services” folder under the “Create New Connection” folder in the “Database Expert” dialog box to create a connection to XML content. Learn this and more during this lecture.

You can click the small plus sign next to the “Repository” folder in the “Database Expert” dialog box to create a connection to the BusinessObjects Enterprise Repository for your report. In the “Log On to BusinessObjects Enterprise” dialog box you must select the name of your system from the “System:” drop-down and then enter your “User name:” and “Password:” into the text boxes that are available. Learn this and more during this lecture.

In addition to the primary data sources displayed within the “Database Expert” dialog box, you also have an array of secondary data sources that are available for use. Many, but not all, of these connection types are included for backwards-compatibility with older information systems or software. You can click the small plus sign next to the “More Data Sources” folder within the “Database Expert” to view the secondary data sources displayed in a list. As with your primary data sources, you simply click the small plus sign next to the name of the secondary data source that you wish to use to make a connection of that type as your report’s data source. Learn this and more during this lecture.

Once you have made a connection to a data source of a specific type within the “Database Expert” dialog box, you will see the new connection that you have made appear. You can click the small plus sign next to the listed data source connection to view the various types of objects within the connection. Note that with some types of connections some of the tables may not be accessible based on your User ID and data access privileges. Otherwise, you will see any tables, commands, views, and stored procedures that are available for the type of data source to which you have made a connection. Learn this and more during this lecture.

After you have created a new data connection for a report, the connection will always be re-established when you open the report (after saving it) in the future. However, if you wish to delete a data connection that you no longer use, you will need to do that through the “Data Explorer” dialog box. You can also use this dialog box to log on and off of specified servers, and generally mange the data connection used by Crystal Reports on your computer. Learn this and more during this lecture.

Once you have a new report with a data source displayed in the report design view, you will then need to place fields from the data source into the desired sections of the report to display the data. You use the “Field Explorer” to add data fields to the report from the connected data source. Learn this and more during this lecture.

Sometimes you would like to see the data contained within a field that you are going to place into the report design view. You can select the name of a field within the “Field Explorer” and then click the “Browse” button that appears in the toolbar at the top of the “Field Explorer” pane to display the data in a separate browsing widow. This window will then show the first few records of data contained within the selected field. Learn this and more during this lecture.

Once you have fields placed into the report design view, you may need to change their position and size on the page. Before you can make any editing changes to a field, however, you need to select it first. You can click a field to select it. When a field is selected it will appear with a blue border around it in the report design view. You will also see four small squares around the perimeter of the object, one on each side of the field. These are called the “resizing handles.” You use these to change the height and width of the data field. Learn this and more during this lecture.

You can use the “Size” command to make multiple selected fields the same height, the same width, or the exact same size. To do this, first simultaneously select the desired fields that you wish to make the same width, height, or size. Then choose “Format| Make Same Size” from the Menu Bar. You could also simply right-click one of the selected fields and roll over the “Size” command from the pop-up menu that appears. Either way, a side menu of choices will appear from which you can select your desired option: “Same Width,” “Same Height,” or “Same Size.” Learn this and more during this lecture.

When you insert a text object into a report, you choose what text is displayed in the box provided and where to place the text object within the report. To insert a text object into a report, either click the “Insert Text Object” button at the left end of the Insert toolbar or select “Insert| Text Object…” from the Menu Bar. The mouse pointer will then appear as a crosshair over the report design area. You click and drag over the area of the report you wish the text object to cover. Learn this and more during this lecture.

You should save your design work frequently so that you do not lose your progress when designing reports. When you save a report for the first time, you must use the “Save As” dialog box. You can invoke this dialog box by selecting “File| Save As…” from the Menu Bar. In the “Save As” dialog box, you use the “Save in:” drop-down to select the folder (directory) to which you wish to save the report. Then type the name for the report into the “File name:” text box. When you are finished, click the “Save” button to save the report into the folder you selected, giving it the name that you specified. Learn this and more during this lecture.

You should inspect how your report will appear with actual data displayed from the data source using the “Preview” function. To preview a report for the first time, select “View| Print Preview” from the Menu Bar. You can also just click the “Print Preview” button in the Standard toolbar. Crystal Reports will then create a new tab, named “Preview,” where you can see the report with the associated data displayed. Once the “Preview” tab has been created, you can switch between the “Design” tab and the “Preview” tab in the future when you wish to view the data as it will display in the report when printed. Learn this and more during this lecture.

When you initially preview a report, Crystal Reports selects the necessary records from the underlying data source. It then stores those data records to the disk or to memory for later use when previewing the data. This makes previewing the data in the report more time efficient because Crystal Reports doesn’t need to then re-extract the records from the underlying data source each and every time you view the report. The date and time stamp displayed in the Status Bar below the report when it is previewed shows the last time that the data was actually extracted from the underlying data source. Learn this and more during this lecture.

It is important that you understand some basic terms and concepts involved in creating and using tables and databases if you wish to create more complex types of reports using Crystal Reports. Obviously, the deeper your knowledge of relational database design and structure is, the easier it will be to understand Crystal Reports and its place in the information storage and retrieval process. However, it is not required that you be a “whiz” with manipulating data tables to create basic reports. Learn this and more during this lecture.

If you add multiple tables to the report using the “Database Expert,” you will automatically see the “Links” tab appear next to the “Data” tab in this dialog box. Here is where you can view, edit, create and delete joins between tables in your report. Learn this and more during this lecture.

Using the Employees/Orders example, once you have the tables added to the report and they have an associated link between them on their “common field,” you can then pull data from the tables and Crystal Reports will accurately display the requested data from the tables. Note that the information displayed in the “common fields” is of the same data type. This is a requirement of joining fields: both fields must share the same data type. For example, you cannot link a “text” field to a “number” field. Learn this and more during this lecture.

When you double-click a join that is displayed in the “Links” tab of the “Database Expert” dialog box, you can see that you can change the join type, the way the join is enforced, and the link type. In the “Link Options” dialog box you can select either “Not Enforced,” “Enforced From,” “Enforced To,” or “Enforced Both” in the “Enforce Join” section. Enforcing table joins ensures the desired use of the tables in the SQL statement (query) used to extract the records for your report, even if none of the fields are used in the report. Learn this and more during this lecture.

In Crystal Reports, you can use the “Format Editor” dialog box or the Formatting toolbar to apply various aesthetic enhancements to the selected objects within your reports. The basic technique used to apply formatting is simple: select the report object or objects to which you wish to apply formatting, and then either use the buttons available in the Formatting toolbar or the choices available through the “Format Editor” dialog box to apply your desired choice of formatting options. Learn this and more during this lecture.

In the “Common” tab of the “Format Editor” dialog box, you can set many options that will always be available for selected fields. You can type a name for a single selected object into the “Object Name:” text box. This name, which can only consist of letters and numbers with no spaces or special characters allowed, is used for object referencing in various parts of Crystal Reports such as the Report Explorer and the Repository Explorer (if using Crystal Enterprise). Learn this and more during this lecture.

In the “Number” tab of the “Format Editor” dialog box, you can set options for the display of numeric fields in the report. Learn this and more during this lecture.

In the “Format Editor” dialog box you can click the “Font” tab to view the available choices that you can make to change the display of text in the selected report object or objects. Learn this and more during this lecture.

You can click the “Border” tab in the “Format Editor” dialog box to set the appearance of any border that you would like to appear around the selected report object or objects. Learn this and more during this lecture.

If you have selected a date/time field within the report to format, you will see the “Date and Time” tab appear within the tabs listed at the top of the “Format Editor” dialog box. You can click this tab to set the display options for the selected date/time fields. Learn this and more during this lecture.

In the “Format Editor” dialog box you may also have a “Paragraph” tab appear when you have a text-related report object selected. On the “Paragraph” tab, you can set the paragraph attributes for the selected text-containing report object. Learn this and more during this lecture.

If you selected a graphic to format in your report, then you can click the “Picture” tab in the “Format Editor” dialog box to set the formatting options for the graphic in your report. Learn this and more during this lecture.

If you select a field in a report that contains a logical (boolean) value, you can then select the “Boolean” tab in the “Format Editor” dialog box. Learn this and more during this lecture.

If you have a selected report object to which you wish to set a hyperlink upon which the users could click when previewing the report, you can set these types of options on the “Hyperlink” tab in the “Format Editor” dialog box. Learn this and more during this lecture.

On the “Subreport” tab of the “Format Editor,” you can specify the settings for any subreports that you have inserted into your main report. Subreports will be discussed in more detail in a later section. However, it is still useful to inspect what options you have for controlling the appearance and settings of subreport data. Learn this and more during this lecture.

Crystal Reports allows you to add some basic shapes, such as lines and rectangles to clarify information and enhance the report’s presentation display. You can draw a line by either selecting “Insert| Line” from the Menu Bar or by clicking the “Insert Line” button in the Insert toolbar. When you do this, your mouse pointer will appear as a pencil icon when you hold it over the report area. You can then click and drag from one point to another to create a vertical or horizontal line in the report. Learn this and more during this lecture.

You can draw boxes (rectangles) around important data to “set if off” in a report, or you may simply use the boxes as an additional enhancement to the appearance of the report. Creating boxes in a report is very much like creating lines in a report. You can create boxes in a report by either clicking the “Insert Box” button in the Insert toolbar or by selecting “Insert| Box” from the Menu Bar. Once again, your mouse pointer will appear as a pencil when you hold it over the report. This time, you will click and drag from one corner of the area over which you want to place the box across to the opposite corner, releasing the mouse pointer when the box covers the desired report area. Learn this and more during this lecture.

The “Format Painter” function allows you to copy the formatting settings from one object and then apply them to one or more other report objects, which saves you time in having to repeatedly re-apply the same formatting settings to multiple report objects. To use this feature, you need to select a report object that has the formatting attributes that you wish to copy to other objects. With this object selected, click the “Format Painter” button in the Standard toolbar, or select “Format| Format Painter” from the Menu Bar. Learn this and more during this lecture.

So far, you have discussed formatting entire selected report objects. Note that it is also possible to format only part of a text object within a report. For example, you could apply the bold format to a word within a report title even if the entire title is enclosed in a single text object. To select just part of the text within a text object, you must double-click the text object which contains the text which you wish to format. That will place the text object into “Edit Mode” where you can view the insertion point inside of the text object. At that point, click and drag over the text which you want to format independently of the other surrounding text to select it. Learn this and more during this lecture.

You can use the “Template Expert” to apply a pre-created format to your reports in Crystal Reports. This can be useful when you don’t have the time to spend formatting a report, but would still appreciate it having a professional appearance. With just a few quick clicks of the mouse, you can apply a report style that includes borders, boxes, shading and other formatting features to your entire report. Learn this and more during this lecture.

If you have a picture, such as a corporate logo, that you wish to insert into a report, you can do this easily in Crystal Reports. You can either click the “Insert Picture” button on the Insert toolbar or choose “Insert| Picture…” from the Menu Bar. This will launch the “Open” dialog box. Use the “Look in:” drop-down to select the folder into which you have the graphic saved on your computer or network. Select the desired graphic from the list of files and folders displayed, and then click the “Open” button. Learn this and more during this lecture.

You can use the “Select Expert” to create and apply filters to the report data. When you create and apply report filters, you in some way specify which records to include and exclude for the report. Unless you wish to display every single record from the underlying table or tables that you selected when you created the report, you must apply filters to restrict the data displayed in some manner. Learn this and more during this lecture.

Oftentimes, you may need to create and apply multiple filters to display the data that you want. You can apply multiple filters while using the “Select Expert” dialog box. After creating the first filtering criteria that you wish to apply, simply click the “” tab at the top of the “Select Expert” dialog box or click the “New…” button at the right side of the dialog box. This will launch the “Choose Field” dialog box where you can select the field by which you want to filter the report from the list displayed and then click “OK.” This will then create a new tab in the “Select Expert” dialog box with the name of the new field that you selected. On this tab, simply select the desired comparison operator and enter the necessary criteria in the drop-down text boxes to the right. Learn this and more during this lecture.

At times, you may find it necessary to edit the actual formula that is created by the “Select Expert” for record selection. This is especially useful for creating multiple criteria that you will join with the “Or” statement instead of the “And” statement, which is the default. To do this, create the needed filters and criteria in the “Select Expert” dialog box, first. Learn this and more during this lecture.

You can use the “Record Sort Expert” to sort the report data. When you sort a report, you can choose by which fields the data is sorted and in what order. To sort the displayed data, you can either click the “Record Sort Expert” button in the Experts toolbar, or you can choose “Report| Record Sort Expert…” from the Menu Bar. Learn this and more during this lecture.

You can use the “Group Expert” to create groupings within your report by which you can view subtotals and sort the report data. For example, let’s assume that you were requested to create a report that shows employees sales with totals by employee. Oftentimes, when you state the purpose of the report you will notice that you wish to see the data “by” some field. The word “by” is often an indication of by which field you will want to create the groups in your report. Learn this and more during this lecture.

The “Group Expert” is useful for creating multiple groupings on the fields within a report and setting all of the necessary properties while you are performing that task. It is also useful to note that you can create a single data grouping within a report by simply clicking the “Insert Group” button in the Insert toolbar or by choosing “Insert| Group…” from the Menu Bar. Learn this and more during this lecture.

One of the main reasons to creating groupings on the data values within fields is so that you can perform summary calculations upon some field for each unique value created by the groupings. Since summary calculations are performed over each grouping, they appear in the “Group Footer” sections. Note that you do not need to have the report data grouped before you can apply summary fields to the report. If you simply apply a summary field to a report that contains no data groupings, you will receive a “grand total” field at the very end of the report. Learn this and more during this lecture.

You can apply a grouping to a report that will illustrate hierarchical relationships in the underlying data source. Many times this type of relationship is created by what is referred to as a “self-join” within a table, where one field’s values (such as an “Employee ID”) appear as two different field’s values (such as an “Employee ID” field and a “Supervisor ID” field). Creating a hierarchical grouping allows you to view and sort the data based on the relationship between the two fields. Learn this and more during this lecture.

If you have summary fields inserted into your report that perform calculations over a group, then you can use the “Group Sort” expert to place the groups into an order that is based on the calculated values of their subtotals. Learn this and more during this lecture.

Special fields are data fields that you can insert into your reports which display information that is unrelated to the information stored in the tables of the report. Special fields retrieve general report and system information that you may find useful in reports, such as page numbering, the date the report was printed, the filename of the report, and other types of general report data. Inserting special fields uses the same technique for field insertion used for inserting “Database Fields.” You insert both types of fields in the same manner within the “Field Explorer” pane at the right side of the window. Learn this and more during this lecture.

You can change the report’s page setup options using the “Page Setup” dialog box. To access this dialog box, select “File| Page Setup…” from the Menu Bar to invoke the “Page Setup” dialog box. Then select the name of the printer to use from the drop-down in the “Printer Options” section. Then use the drop-down in the “Page Options” section to choose the size of the paper to which you will be printing. Learn this and more during this lecture.

When you wish to print your report, just click the “Print” button in the Standard toolbar or choose “File| Print…” from the Menu Bar. This launches the “Print Setup” dialog box. In the “Printer” section, you can select the printer to use to print the report. Learn this and more during this lecture.

Much of the power in Crystal Reports lies in its ability to use powerful analytical functions and mathematical expressions to create complex formulas which can manipulate and further calculate the data displayed in the report. For example, you could create a formula in a field that multiplies the values in one field by the values displayed in another field. This is just one example of the ways in which formulas can be used in Crystal Reports. In this lecture, you will examine the ways in which you can write formulas in Crystal Reports. Learn this and more during this lecture.

In Crystal Reports, you use the Formula Workshop window to create the various types of formulas that are used to select records, create custom group names, calculate values, and other formula-related activities. The Formula Workshop consists of two separate components: the Formula Editor and the Formula Expert. Learn this and more during this lecture.

You can also add a formula field into your report, and this is probably the most common reason (at first) to use the Formula Workshop window. Formula fields are simply fields that are placed into your report that calculate a value determined by the formula which you set. You can either create them through the Formula Workshop window, or through the Field Explorer pane. Learn this and more during this lecture.

The Crystal Syntax is commonly used to create formulas in Crystal Reports, as it has been accepted in every version of Crystal Reports. You can only use the alternative Basic syntax starting in version 8.0. However, almost any formula can be written using either syntax. Learn this and more during this lecture.

If you are familiar with Visual Basic programming, you can create formulas in Crystal Reports using the Basic syntax which may seem more familiar to you. Generally speaking, using the Basic syntax in Crystal Reports is just like using Visual Basic, except that it has specific extensions to handle reporting. Learn this and more during this lecture.

When you are composing your formulas in the “Formula Workshop” window, you may want to find more information about a particular function or formula that you think you would like to use. If you use the “Function Tree” and the “Operator Tree” panes to expand the functions and operators, it is helpful to note that you can click on the name of a function or an operator within these two panes and then click the “Help” button in the Formula Workshop toolbar. This will then launch the “Crystal Reports Online Help” window and automatically find the help file associated with the selected function or operator. Learn this and more during this lecture.