4/11/2011

Crystal for CounterPoint SQL Beginner Marilyn Grant

Agenda • Getting Started with Crystal • Moving and Adding Fields • Column Heading Macros • Report Sections: Using the Section Expert • Creating a New Report and Linking Tables • Using Global Object Macros • Sub-Reports in CounterPoint’s Reports

1

4/11/2011

What is Crystal Reports? includes Purchasing Advice

Crystal Viewer

My New Report

to view existing reports

does not include

Crystal Reports

to modify or build new reports

Versions Supported by CP ● New report development Crystal Version 8.5 or later 8.5 – Standard Edition or better 9+ – Professional Edition or better

●

Modify existing CounterPoint reports Crystal Version 9 or later Professional Edition or better

● View existing CounterPoint reports Crystal Viewer Version 10 Supplied with CounterPoint

2

4/11/2011

Which Standard Report? How to tell which standard report to customize

Open Report in Crystal

Click to open an existing report

Crystal XI illustrated

3

4/11/2011

Browse to Existing Report V8.3.9 Standard reports located in CounterPoint \ System \ Reports V8.4 Standard reports located in Program Files \ Radiant Systems \ CounterPoint \ CPSQL.1 \ TopLevel \ System \ Reports

Connect to Your CP Database Select Database / Set Datasource Location

4

4/11/2011

Create New Connection Double-click Create New Connection

Double-click OLE DB (ADO)

Identify OLE DB Provider

Use SQL Server as the Microsoft OLE DB Provider for CounterPoint databases

5

4/11/2011

Provide SQL Login Info

Specify the name of your SQL server and a SQL user (and password) who has access to the database you want to use for the report. Enable the Integrated Security check box instead if you would rather use the current Windows user ID and password.

Select the Database

Then select the database that you want to use. The list shows only the databases managed on the server and that the SQL user has permission to use. Click

when done.

6

4/11/2011

Update Report to Your Data Source

Your SQL server connection should now appear in the Replace with area of the Set Datasource Location window. Highlight your server name and click .

Current Data Source

Your SQL server name should now appear as the Current Data Source for the report.

7

4/11/2011

Add Data Source to Favorites • Add data source to Favorites folder to avoid having to set it up again for other reports

Right-click the new data source in the lower pane and select Add to Favorites

Add Data Source to Favorites

The data source now appears in the Favorites folder and will remain there when you use Database / Set Datasource location for other reports. Click

when finished.

8

4/11/2011

View Your Data • Verify that the data source connection was updated by browsing the data In the Field Explorer, expand Database Fields and then expand one of the tables used in the report.

View Your Data

Right-click a field whose data you will recognize, and select Browse Data.

The values for that field from the current data source are shown.

9

4/11/2011

Preview Report with Data

To view the report with data, click on the toolbar, or select File / Print Preview.

Preview Report with Data

A Preview tab now appears and shows the first page of the report with data from the data source.

10

4/11/2011

Report Sections The bands across the report are called Sections and control where information appears on the report. RH are report header sections. PH are page header sections. GH are group header sections. D are detail sections. GF are group footer sections. PF are page footer sections. RF are report footer sections. We’ll look at sections more closely later.

Moving Fields on a Report You can move fields on either the Design tab or the Preview tab. Sometimes it’s easier to do it from the Preview tab, where you can see the actual values and the space they take. It’s also easy to see the report section that the field is in.

11

4/11/2011

Moving Fields on a Report

Click the field that you want to move. Use CTRL-click to select multiple fields to move them all at once.

Moving Fields on a Report

Then drag the field(s) to the new position and release your mouse button to drop them.

12

4/11/2011

Moving Fields on a Report

Repeat the process to move the corresponding column heading(s).

Moving Fields on a Report

When fields or column headings are selected, you can switch to the other tab (Preview or Design) and also see them selected there.

13

4/11/2011

Adding Fields to a Report

To add a new field to the report, switch to the Design tab so the “Details” section is visible. Then expand the table in the Field Explorer and locate the field you want to add.

Adding Fields to a Report

Drag the field to the “Details” section and release the mouse button to drop it. This won’t be the final position of the field, as you will see.

14

4/11/2011

Adding Fields to a Report

When you add a new field to the Details section of a report, Crystal adds a column heading for the field in the Page Header section.

Adding Fields to a Report Select both the heading and field, and change the font size so that it matches the other fields in the report. If necessary, move the heading and field so that the heading better fits in the space.

15

4/11/2011

Adding Fields to a Report

Then select just the heading and disable the Underline.

Adding Fields to a Report

Select just the field and drag it straight down to the Group Footer 1 section so that it’s next to LST COST. Dragging it straight down ensures that the heading stays “connected” to the field.

16

4/11/2011

Adding Fields to a Report

Now switch to the Preview tab so you can see data for the new field. Shrink the field to remove the time by dragging the resizing handle on the right.

Adding Fields to a Report

Switch back to the Design tab. If necessary, select the field again. Then use the right arrow key on your keyboard to move the field slightly to the right. Notice that the heading moves at the same time.

17

4/11/2011

Column Heading Macros

The new column heading is missing “%” at the start and end. When a report is run from CounterPoint, CP substitutes the Report Heading setting from the Data Dictionary for each column heading that uses the % macro.

Column Heading Macros

To allow Report Heading substitutions for the new field, edit the text of the column heading. Right-click the column heading and select Edit Text.

18

4/11/2011

Column Heading Macros

Add “%” to the start and end of the existing text. When done, click away from the heading. (Do not press Enter, as this will add a blank line to the heading.)

More on Sections

Have you wondered: • Why are there so many PH sections? • Why are some sections missing from the Preview tab?

19

4/11/2011

Why So Many Sections All CounterPoint reports contain at least 3 page header sections: Page Header a – Print date, Company name, Report title, Page number Page Header b-Page Header x – Parameter choices and special messages Page Header - Column headings for following section

Why So Many Sections

The first and last Page Header sections always appear on a report. The other Page Header sections appear only under certain conditions and, even then, only on page 1 of a report. You can tell that there are conditions because the sections are shaded. To see the conditions, you’ll need to use the Section Expert.

20

4/11/2011

Why So Many Sections

Right-click in the gray area of a shaded Page Header section (Page Header b in this example) and select Section Expert.

Using the Section Expert

The Common tab on the right side shows settings for the selected section. Notice that Suppress (No Drill-Down) is enabled for this section. The button to the right is red, indicating that there’s a formula controlling suppression. Click the formula.

button to see the

21

4/11/2011

Using the Section Expert The formula (@REPLCP_ViewCost)=True appears in the Format Formula Editor window. @REPLCP_ViewCost is a special formula used by CounterPoint that means “Is the user allowed to view costs?” So the section is suppressed if the user is allowed to view costs.

Another Formula

Close the Format Formula Editor and the Section Editor. The field in this section is named @ViewCostMsg. “@” is Crystal’s symbol for a formula field, so this field is a different formula named ViewCostMsg.

22

4/11/2011

Another Formula

On the left side, expand Formula Fields in the Field Explorer. Right-click ViewCostMsg and select Edit.

Another Formula

This formula tests to see if @REPLCP_ViewCost is False. If it is False, the “User not authorized to view costs…” message prints.

23

4/11/2011

Why So Many Sections? Combine the message formula • If the user is not allowed to view costs, the message “User not authorized to view costs…” prints in Page Header b

with the section suppression formula: • If the user is allowed to view costs, Page Header b won’t even appear on the report

This also explains why section PHb appears on the Design tab but is missing from the Preview tab!

Using the Section Expert Page Header c is another shaded section containing the fields %RP_INVENTORY.ORDER_BY% and %ORDERBY%.

Using the Section Expert, you can see it is also suppressed and has a red button. Click to see the suppression formula.

24

4/11/2011

Using the Section Expert

This section is suppressed when the report page number is greater than 1. The report label for “Order by” and the user’s Order-by choice appears only on page 1. Close the Format Formula Editor.

Creating a New Report Start from Template.rpt in CounterPoint / System / Reports ● Already includes

Page Header sections, with macros for standard fields ● Use Database/

Database Expert to connect to your database and select tables for the report.

25

4/11/2011

Multi-Table Reports • If multiple tables are used in a report, tables must be linked or “joined” with a common field Link tables on Links tab in Database / Database Expert

Linking Tables

• Link from “driving” table to common field in “to” table • Right-click field to Browse data • Use Auto-Link By Key to link to index (arrowhead) for performance

26

4/11/2011

Linking Tables

When you create a new report or add a new table to an existing report …

Linking Tables

…you’ll automatically be taken to the Link tab. Crystal will auto-link to the table using fields with the same name and data type (alpha vs numeric), which may or may not be correct. Browse data to verify.

27

4/11/2011

Linking Tables

Right-click one of the links to the new table and select Link Options.

Linking Tables

You may need to change the Join Type. By default, Crystal always uses Inner Join. Inner Join means there must be a matching record in both tables for the record to appear on the report.

28

4/11/2011

Linking Tables

In this example, an inner join means an item will not appear on the report if it does not have a matching record in the PO Vendor Item table.

No match

Use a Left outer join if you want the record to appear as long as it exists in the Item table, the left table in this case.

Linking Tables Use Right outer join if you want the record to appear as long as it exists in the right table, even if it doesn’t exist in the left table. That won’t occur in this example, as a Vendor Item record cannot be defined for an item that does not exist.

29

4/11/2011

Linking Tables Use Full Outer Join if you want the record to appear if it exists in either table, even if there is no match in the left or right table.

No match

In this example, either a Left Outer Join or Full Outer Join would work and show each item on the Item List - even if it has no Vendor Item record.

30