Fundamentals of the Crystal Reports Design Environment

Creating and Designing Basic Reports

Planning a Report

Using the Report Wizard makes the process of creating a report incredibly easy. Now we're going to build a similar report without the wizard. We'll have more control over the look and feel of the finished report and learn a lot of new Crystal Reports features along the way.

Effective Report Design Considerations

The most common mistake made by report designers is forgetting to plan the creation of a report. Some of the issues to consider might seem quite basic, but it's good practice to walk through the process of validating some key report design considerations before starting.

The basic questions to ask before starting your report are Why? What? How? When?

They might seem simple, but there's nothing worse than finishing a report only to find out that it isn't what the person who requested it wanted. We'll now walk through each of these questions in detail.

Why Do They Need This Report?

As a report designer, you'll never run out of unique requests from end users and report consumers. Some requirements will undoubtedly be

• Including summary Numbers (Quarterly reports—enough said).

• Making a collection of numbers make sense. Business users are rarely familiar with the nuances of the database environment.

• Needing key business metrics yesterday. (There is no time like overdue to ask someone to create a report, right?)

• Converting old legacy reports to a new system.

• Providing consumers with information on the Web.

Of course, the person requesting the reports might not really know what he wants, but the common thread throughout each and every report request is the need to bring "people and information together."

What Do They Need?

Perhaps a more appropriate title for this section is "What do they tell you they need?" Ever notice that when someone requests a new report, he starts telling you what he wants, but not what he needs? Those types of requests usually consist of statements like

• Just a final number (I don't care how you get it)

• A map

• A pretty chart

• An Executive Summary

• The Quarter End Summary

• The ROI

It's ultimately the job of the report designer to find out what the real business requirements are for any report that must be created. It's not enough to just prioritize which reports are created first, second, third, and so on. The design requests within the reports themselves are ultimately what drive the complexity and, hence, design longevity.

How Will We Judge the Successful Completion of This Report?

Experience has shown that to get good answers, it works best to make the business user requesting the report feel useful and needed through interaction during the report planning phase. Starting with simple questions, such as

• What features are required for this report?

  Most reports require some graphical representation of the data within, which means charts or a geographic map potentially.

• Are there other reports I can work from?

  If there are sample or existing reports (maybe it's a older, legacy report you need to recreate, such as a mainframe report), it's always faster to work with something than nothing, especially if you're trying to duplicate the look or functionality of an existing report.

• Where is the data coming from?

  This is an important step. If you can't connect to the data that you need to see, you're stuck. Start with a successful database connection, and you'll be off on the right foot. We will be focusing more on this in a future hour. Another follow-up question would be, "Who can I ask to help me get connected to this data source?" Chances are, it's someone in IT.

• What level of detail do you want to see?

  Focus of vision and what level of detail the final results should be are key. If the end result is just a final number, don't spend a lot of time on making the details look pretty. Take the same perspective as the person asking for your help.

• What help or resources can I work with?

  No one is an island in life or in work. Ask colleagues for help and ask a lot of questions. Don't assume anything. Remember, there are no such things as dumb questions.

When Is This Due?

As previously mentioned, make sure to discern the want from the need. Try to get the requesting business user to understand your perspective, abilities, and the features of Crystal Reports available to them. After all, time is precious. Find out what the key drivers are for the business user and the priority placed on each requirement. Meeting somewhere in the middle is usually a good idea.

Now that we've covered some of the typical and most resourceful questions to get you started in writing a report, let's look the physical process of report design.

Mapping Out a Report

A report can be considered a book. It tells a story in words as well as pictures. The complete story needs to be told to get everyone reading it to understand the message it is delivering. The person who has the ultimate idea of how the story should end is the person requesting the report—the requestor. He has in his mind that the report should have certain information on it and should look a certain way. He might even come to you with a drawing or picture. To organize your thoughts, be prepared to take notes.

As you begin to write down your thoughts, you may notice that you are writing out the story that the requestor is asking for. So, if the requestor is the illustrator in our example here, you could be considered the author. As an author, you can follow the general rules of writing that you learned in school. Start with an outline and build from there.

Write a paragraph or two in your own words to tell the story that the requestor has relayed to you. Don't worry about missing information. It's a work in progress, and you can ask questions later. After you've finished the first draft, you'll notice that editing the story becomes essential. That's when the real facts about the story, or report, will surface.

Creating a Report Storyboard

Before opening the report designer, create a report using the effective report design techniques covered in the previous hours of the book.

Imagine the following end-user requirements, received in an email.

Report Design Request Memo To: Report Designer From: Bob, the sales manager Subject: Sales Report Hey report designer, here are the requirements… Sales managers need an Employee Productivity Report. The sales managers want to see the status of all orders for an employee by the account that he or she works on. The order status will need to highlight late orders and the size of all orders. The managers will need to see what courier company is used for each order. Thanks Bob

The previous request seems pretty straightforward. Although the requirements seem sensible, try applying some of the questions from the previous section to them. Clearly, more information is required to complete the report. After leveraging some of our newfound report design questions, the requirements end up looking more like the following:

Response to the Report Design Request Memo To: Bob, the sales manager From: The report designer Subject: Sales Report Bob, After speaking with you, I found the following key requirements for your report. Please confirm these to be the case, along with the specified timeline for delivery. You need to see the order status for all orders for X employee. Each account and its total value that each employee works on must be represented and shown separately. Late orders (any order not shipped on the day of order) must be shown in red. Courier companies for each order must be seen. Also, my understanding is that the report needs to be completed one month before the end of this quarter, so you can use it to help wrap up account issues. Thanks, Report Designer

Notice that this more personalized and direct list of requirements is much more like a database schema. That's because report designers tend to be "data-type" people and need to be more focused on this to make a report successful.

Now that we have a more polished set of requirements, we can look at dividing up the tasks of creating the report in to its core pieces. This will help identify what information needs to go on the report and then translate this into specific fields and tables in the database. Some of them might seem obvious, but let's look at them in order:

• "all orders" = an Orders table

• "X employee" = an Employee table (with probably some user input to find out which employee to look for)

• "account"= a Customer table

• "must be shown separately" = Indicates a level of grouping that is required

• "its total value" = Indicates a summary will on the account is required

• "red" = Indicates a status level or condition that needs to be applied for bringing the reader's eye to pertinent information

We have a list of basic tasks that need to be done to create the report successfully. Dividing the tasks into logical groupings would help us progress faster.

Now let's categorize our findings:

• Tables— Customer, Orders, Employees

• Groups— Account

• Summaries— Total orders for each Customer

• Parameters— Employee's Name

• Condition— Late Status

Now that the tasks for creating this report are laid out, the process of report design can begin.

Designing a Crystal Report After the requirements for creating a Crystal Report have been determined, the actual report can be designed. This involves mapping the gathered business user requirements to the technologies available within the Crystal Reports design application. Using the Crystal Reports Design Framework With years of experience in producing reporting software, Crystal Decisions has built Crystal Reports around a design framework, intended to organize reports in a logical manner. Those framework items include Tables/Links Grouping Summaries Formulas Parameters Text objects Special fields Special formatting Basic Items Used to Create a Report No two reports look alike, nor are they designed in the same way. However, some basic report features must always be present: Tables/Links— This is the foundation for your reports. If you need to report on data that resides in multiple database tables (Orders and Customer), without the links to make them work properly together, you will not be able to successfully complete a report. Groups— These will be the logical breaking points to support your story. Details— Putting the data on the report that will be needed to calculate and tell the rest of the story. These are the records of the report. Summaries— To make the details more useful, summarizing them into the basic facts that the business user is looking for is key. There should be no need to grab a calculator or open up Excel to get the numbers. Subtotals and Summaries in Crystal Reports can handle that for you. Additional Report Components Beyond these features, Crystal Reports offers major benefits that will enhance the report viewing experience for the business end user: Parameters— These are rich business user-focused features that will allow the report consumer to make the reports act differently depending on the user's input. For more information on parameters. Formulas— Selection formulas, data manipulation, and complex calculations can be handled with formulas. Sort Order— Ascending and descending order are the most common for reporting, but they are not the only options. You could sort based on group contents. For more information on sorting. Formatting/Pictures/Hyperlinks/Charts/Maps— These are the items that really make the report grab the business users' attention of the. The cool visualizations make it presentable and flashy! Now that you know what the requirements are for the sales management report, as well as some high-level features of what Crystal Reports offers, let's begin designing the report.