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.

Formatting Reports

Positioning and Sizing Report Objects

After you have completed your functional report design tasks—connecting to the data source, adding report objects, and structuring the report—formatting the various objects on a report is the natural next step in the report design process. "Creating and Designing Basic Reports," objects can be added to a report via a variety of methods—dragging and dropping objects from the design explorers or selecting objects from toolbar and menu commands and placing them in the desired locations within the report sections—for quick and intuitive report creation. Upon successfully adding objects to your report, each of the respective objects can be positioned, sized, and formatted for display purposes, as demonstrated in the following exercise.

Let's begin the design of our report. The following steps will enable you to create your own Customer Contact Listing report:

1. Open the Crystal Reports application and select to create a new report using the blank report layout from the Report Gallery dialog.

2. From the Database Explorer dialog, expand the Create a New Connection list, and then expand the ODBC (RDO) node to present the ODBC dialog window that lists the available data sources. Select the Xtreme Sample Database from the list of data sources and click Finish to continue to the Database Expert dialog.

3. From the Database Expert dialog, use the arrow (>) button to add the Customer table to the Selected Tables list on the right.

4. From the View menu, select the Field Explorer command to open the Field Explorer dialog.

5. From the Field Explorer, click and drag the Contact Last Name field onto the report's design view and place it to the far left of the Details section area.
   

6. Follow the previous step to add the Contact First Name, City, and Email fields to the Details section of the report.
   

7. From the Insert menu, select Text Object and drop the object in to the middle of the Report Header section and type Customer Contact List in the text field. Click anywhere outside the text object to remove the cursor focus from the text object.

Now that the report includes the field and text objects identified previously, it's time to focus on positioning and resizing these fields for display purposes.

8. you might not be able to see the entire text entered in to the report title text object because it is not wide enough to display your text entry. To resolve this, click once on the report title text object located in the Report Header section so that it becomes highlighted. Using the dark blue handles that encompass the objects perimeters, float over the handle located on the right side of the text object with the mouse pointer; then click and hold the mouse button while dragging the handle farther to the right to widen the text object's display area.

   Notice that when you float over the perimeter handles of an object with your mouse cursor (or pointer), the cursor icon turns into an alternative shape, such as horizontal or vertical arrows, to illustrate that you can modify the object if you click on the handle.

9. Now that you have widened the display area of your report title object using the concept of object handles, repeat this same step to modify the width of the field objects within the Details report section so that you can insert one additional object into the Details section of your report.

10. Using the Field Explorer (as discussed earlier), insert the Phone database field from the Customer table into the Details section of your report. Based on the previous steps, practice positioning and sizing the objects in the Details section to accommodate for all the database fields.

    As you might have noticed, the field sizes are often large enough to show the entire field name in the Design view of the report. But from the Preview tab view of the report, you see that fields (such as the Email or Phone fields here) are cut off from the display area. This is not unusual, and it might require you to resize the field objects to ensure that they are appropriate for the report display area. It's best to use the report's Preview tab as a guide to formatting the layout of your reports.

11. Now click the Preview tab to see a preview display of what the report will actually look like

If the Preview tab is not displayed in the application, you have not yet run the report against the database. To run the report, click the Refresh toolbar icon to execute the report to run—the Refresh toolbar icon is represented with a yellow lightening bolt.

Although it's important to understand the basics of report formatting, you will not necessarily have to go through the often arduous process of formatting reports every time. Report templates can be used to apply predefined and meaningful formatting characteristics in a very quick manner. "Designing Effective Report Templates," for more details on designing and using report templates.

Modifying Object Properties for Formatting Purposes

Now that the foundation of our report is complete, it is time to focus on how to improve the form of the report—the aesthetic characteristics.

We will concentrate on modifying various object properties to further improve the presentation value of the report. In doing so, we will be using the Format Editor to access a variety of specific properties, such as fonts, borders, colors, and alignment. The Format Editor is a commonly used dialog to quickly and easily modify all your report objects, and its contents are reflective of the specific object type being formatted (text, chart, database field, and so on).

1. From our earlier exercise, return to the Design tab of your report and right-click on the report title text object (located in the Report Header section) and select the Format Text option from the list to bring up the Format Editor dialog.

   The Format Editor dialog allows you to adjust a variety of properties of the object organized within various tabs.

2. Navigate to the Font tab of the Format Editor and select the Bold font style, a font size of 11, and a font color of Red.

3. Select the Paragraph tab of the Format Editor and choose Centered from the Horizontal Alignment drop-down list.
   

4. Now select the Border tab from within the Format Editor and then select Single from each of the four border Line Style drop-down lists (left, right, top, and bottom). Under Color, click the Background check box and select Yellow from the drop-down list as the background color. Based on all of your selected properties in the Format Editor, you should now see a representative example of the text object in the Sample area at the bottom of the dialog box. Click OK to save these settings and return to the Design tab on your report.

5. To improve the effectiveness of your report, you can modify the database field column titles to provide more meaningful descriptions for the business users of your report. Working within the Design tab of your report, double-click on the Phone object in the Page Header section of the report. When the cursor's focus is on this object, you can delete, append, or update the text as you wish. Modify this text to read Telephone Number and then click anywhere outside the object to remove the cursor's focus from the object.

As an alternative to the Format Editor, you can also use the toolbar and menu commands to quickly apply common formatting techniques, such as font and alignment characteristics.

6. From the View menu, select Toolbars to present the Toolbar dialog. Make sure that the Standard, Formatting, and Insert toolbar items are all selected and click OK.

7. Click on the Preview tab to see a preview display of what the report will actually look like. Again, if the Preview tab is not displayed in the application, click the Refresh toolbar button to execute the report. From the Preview mode, hold down the Shift key on your keyboard and click each of the five column titles so that they are all highlighted with a dashed perimeter. With all five columns title fields highlighted, click the Bold toolbar button, represented with a large bold letter B on the formatting toolbar.

8. 
   With the five column title fields still highlighted, click the downward arrow located on the Font Color toolbar button, represented with an underlined letter A on the formatting toolbar. Select the bright blue color from the available list.

9. 
   

In order to make the Email field appear more meaningful to the business users of the report, let's format the Email database field values to resemble and behave like standard hyperlink text.

9. To remove the cursor focus from the five column titles fields, click anywhere outside these field areas or press the Esc (escape) key on your keyboard.

10. Click any of the actual Email field values to highlight the Email database field objects and right-click on the same object to present the pop-up menu.
    

    You can use the Hyperlink tab within the Format Editor to create hyperlinks to a Web site, email address, file, or another crystal report. A hyperlink is saved with your report and is available to other users as a way of linking additional information as it relates to your report. Hyperlink definitions can also be defined by formulas, thus making data-driven hyperlinks a very powerful feature of Crystal Reports.

11. Select the Hyperlink tab after you have opened the Format Editor. From the available Hyperlink Types, select Current Email Field Value—this option will automatically create a hyperlink based on the values stored within this field in the data source, and these values must be formatted as proper email addresses in the data source.

12. Now let's make the Email field appear as a standard hyperlink value, commonly known to have a blue underlined font style. Select the Font tab within the Format Editor dialog to apply the blue font color and select the Underline check box. Click OK to return to the report Preview, and then press Esc to remove the cursor focus from all report objects.

13. Based on the completion of the previous step, your mouse pointer should now change into a hand icon as it floats over any of the Email field values on the report. This indicates that upon clicking on any of the Email values, you will initiate an email message to be sent to that address

Advanced Report Design Concepts

Introduction to OLAP Concepts and OLAP Reporting

OLAP is an analysis-oriented technology that enables rapid analysis of large sets of aggregated data. Instead of representing information in the common two-dimensional row and column format of traditional relational databases, OLAP databases store their aggregated data in logical structures called hypercubes—more commonly referenced simply as cubes. These OLAP cubes are created around specific business areas or problems and contain an appropriate number of dimensions to satisfy analysis in that particular area of interest or for a specific business issue. OLAP is a technology that facilitates data viewing, analysis, and navigation. More than a particular storage technology, OLAP is a conceptual model for viewing and analyzing data.

Business Areas and Commonly Associated OLAP Dimensions

Business Area	Associated Business and Common OLAP Dimensions
Sales	Sales Employees, Products, Regions, Sales Channels, Time, Customers, Measures
Finance	Company Divisions, Regions, Products, Time, Measures
Manufacturing	Suppliers, Product Parts, Plants, Products, Time, Measures

OLAP cubes pre-aggregate data at the intersection points of all of their associated dimension's members. A member is a valid field value for a dimension. (For example: Members of a time dimension could be 2000, 2001, Q1, or Q2; and members of a product dimension could be Gadget1, Gizmo2, DooDah1, and so on.) This pre-aggregation facilitates the speed-of-thought analysis associated with OLAP.

Precalculating all the numbers at the intersection points of all of an OLAP cube's associated dimension members enables rapid high-level analysis of large volumes of underlying data that would not be practical with traditional relational databases. Considering the example of analysis on several years of sales data by year, quarter, and month and by region, sales manager, and product, the preaggregated nature of OLAP facilitates quick speed-of-thought analysis on this data that otherwise would not be practical working with the phenomenal amount of storage space required in a traditional relational (OLTP) database system.

New OLAP Features in Crystal Reports 9 This section is specifically targeted for users of previous versions of Crystal Reports. lists the new OLAP oriented features of version 9 and their practical use or benefit. If you are a new user to Crystal Reports or you have not previously used the OLAP reporting features in the product, you might want to skip directly to the next section. OLAP Features in Crystal Reports 9 NEW OLAP Feature Feature Benefit and Value Filter/Page Dimension Parameter links This Productivity feature enables for the direct linking of report parameters to pages and filters in the OLAP grid. This enables the end user to dynamically specify the values of filters and pages in the OLAP grid. The feature is accessed in either the OLAP Report Creation Wizard or the OLAP Report Settings option under the Report menu. Interactive OLAP Worksheet (Analyzer) in new Cube tab The New OLAP Analyzer feature (a Cube tab in Crystal Reports Designer) is accessed by right-clicking on an existing OLAP grid object and selecting the Launch Analyzer option. The Cube tab provides a fully functioning drag-and-drop OLAP worksheet that enables rapid selection of the most appropriate OLAP viewpoint for the Crystal Report. All changes made in the Analyzer worksheet are reflected in the associated Crystal Reports OLAP grid, where advanced formatting can be applied. Interactive drill-down of OLAP grids in Preview Tab The OLAP grid presented in the Crystal Reports Preview tab has now been made more fully functional. In addition to having access to advanced OLAP grid functionality from the right-click button including calculations, exception highlighting, sorting, filtering, and member reordering, the OLAP grid now enables the report designer to expand (drill-down) and contract members directly from within the Preview tab. New and improved data sources At the time of writing, it was expected that Crystal Reports 9 would have improved and flexible OLAP connectivity to Hyperion Essbase and new connectivity to SAP BW. The following sections explore the creation of an OLAP report through the OLAP Report Creation Wizard, the added value of the OLAP Expert, and the advanced interactivity features of version 9. OLAP Report Creation Wizard and OLAP Expert Crystal Reports 9 provides two easy ways to create reports against OLAP data sources. As introduced in "Using the Default Report Wizards," Crystal provides several report wizards to step you through the creation of some popular types of reports—one of those is OLAP. The OLAP Wizard involves five steps/screens and walks you through the process of creating an OLAP grid and an optional supporting graphic based on an existing data source. The OLAP Wizard is accessible when you are creating a new report. The second method of creating an OLAP based report is through the OLAP Expert that is accessed from the Insert OLAP Grid on the Insert menu. This expert provides six tabs that step through the creation of an OLAP grid to be placed anywhere on a report. The two methods of creation offer very similar degrees of functionality, and their respective dialog screens and tabs are almost identical. The OLAP Report Creation Wizard does provide a built-in Charting screen not found in the OLAP Expert, whereas the OLAP Expert provides Style Customization and Label tabs not found in the OLAP Wizard. Advanced OLAP Reporting Up to this point, the OLAP Expert and OLAP Report Creation Wizard have demonstrated the capability of Crystal Reports to rapidly create OLAP based reports. More than these capabilities, Crystal Decisions has provided advanced analytic capabilities against OLAP data sources through some advanced OLAP-oriented features in Crystal Reports and through a sister product called Crystal Analysis. The last four sections of this hour introduce some of these advanced features. Interacting with the OLAP Grid Crystal Reports provides some powerful interactive OLAP features from directly within the Crystal Reports Preview and Design tabs. displays the right-click menu that appears when right-clicking on the year 1998 member in this hour's sample report. Introduction to Crystal Analysis Crystal Analysis Professional is a new reporting tool from Crystal Decisions that enables organizations to deliver action-based OLAP analysis to business users. It enables better insights to help decision makers affect business performance through interactive analysis. Crystal Analysis Professional takes OLAP reporting to the next level by allowing report designers to create intuitive and highly interactive reports that offer a guided analysis approach to business issues. Power users implementing Crystal Analysis can create analytical reports, based on OLAP data, using a powerful designer (similar in concept to Crystal Reports). Crystal Analytic Reports can contain many pages, each presenting a different predefined view of the OLAP cube. Data can be presented in tables or visualized through a wide range of charts, exception highlights, data sorts, filters, and/or analytic transition buttons. Business managers can use the resulting analytical reports to drive the business decisions they need to make every day. COM-based Data Sources Crystal Reports provides direct access or native drivers for some databases. These drivers are written specifically for a particular database and are often the best choice. However, because hundreds of types of databases exist, Crystal Decisions can't possibly write direct access drivers for all of them. So often, users turn to using standard data access layers such as ODBC or OLEDB to connect to their databases. Often, the vendor of a database will provide an ODBC driver or OLEDB provider so that other applications can access the vendor's database. Sometimes though, even this is not enough. Customers have data that they would like to report off of that is not accessible by any Crystal Reports data source driver or via ODBC or OLEDB. To this, customers often turn to the COM Data Source driver or the Java Data Source driver. This section describes the COM version of the driver, but much of the theory applies to the Java Data Source driver as well. Java-based Data Sources The COM Data Source Driver is targeted at Visual Basic and Visual C++ developers. Because Crystal Reports 9 has a full Java SDK, an equivalent Java Data Source driver provides equivalent functionality of the COM driver for developers using the Java platform. The process of creating a Java Data Source driver is conceptually similar to that of creating a COM Data Source driver. A Java class needs to be created that has a public function with a return type of java.sql.ResultSet. import java.lang.*; import java.sql.*; public class XtremeDataProvider { public ResultSet Employee() { // connect to the database Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String url = "jdbc:odbc:Xtreme Sample Database 9"; Connection con = DriverManager.getConnection(url, "", ""); // run a SQL query Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); String query = "SELECT * FROM Employee"; ResultSet rs = stmt.executeQuery(query); // return the results of the query return rs; } } To identify a Java class, instead of typing in its name, place the .class file into a given directory, and add that directory's name to the following registry key of your Windows operating system: HKEY_LOCAL_MACHINE\Software\Crystal Decisions\Crystal Reports 9\DatabaseOptions\JavaUserClassPath During the process of creating a report, Crystal Reports will search through all classes contained in the folder(s) specified in the registry key discussed previously. It will then provide a list of methods with return types of ResultSet. The same rules about function arguments apply. Any arguments to the Java method will be mapped to report parameter fields. Using Java code, you can control exactly what data comes back. XML as a Data Source With the emergence of XML as a data interchange format, many customers wanted to create reports on XML documents. So in Crystal Reports 8.5, a new driver was released that allowed just this scenario. This ODBC driver reads certain types of XML documents. A new feature of this driver in version 9 of Crystal Reports is the ability to read multiple XML files, most commonly a folder of XML files that have the same schema. When using this driver, you will specify either a folder name or a file path to an XML file. Once connected, XML elements at the first level will be represented as fields that you can place on a report. If you require more flexibility around reading XML files, a good approach to take is to write a COM or Java Data Provider to read the XML. This Data Provider can use one of the many readily available XML parsers to read in the XML and choose exactly what fields to return to Crystal Reports. A COM Data Provider that Reads XML Data ' Loads an XML document with the following structure: ' ' ' X ' X ' X ' ' Public Function SimpleXML(fileName As String) As ADODB.Recordset Dim rs As New ADODB.Recordset Dim xmlDoc As New MSXML2.DOMDocument xmlDoc.Load (fileName) rs.fields.Append "Name", adBSTR rs.fields.Append "Dept", adBSTR rs.fields.Append "Salary", adCurrency rs.Open ' Loop through each employee element Dim employeeNode As MSXML2.IXMLDOMElement Dim childNode As MSXML2.IXMLDOMElement For Each employeeNode In xmlDoc.documentElement.childNodes rs.AddNew For Each childNode In employeeNode.childNodes rs(childNode.nodeName).Value = childNode.Text Next rs.Update Next Set SimpleXML = rs End Function

Sharing and Distributing Crystal Reports

Distributing Crystal Reports

Reviewing Report Distribution Options

You might be wondering why an entire chapter would be devoted to report distribution. It seems logical that report files could simply be emailed or even printed and sent to whoever has a need for that particular information. This simplistic viewpoint however would overlook some important considerations when delivering reports to business users:

• Static Data vs Live Data— As soon as a report is printed to paper or exported to an external format like Adobe Acrobat (PDF) or Microsoft Excel (XLS), it becomes static and potentially out of date. Depending on the nature of the data being used in the report, this might be an issue. Even for situations in which having live data isn't important(for example, reports based on historical data), interactive features such as drill-downs are lost when printing or exporting a report.

• IT Maintenance— Because features such as drill-downs and hyperlinks are supported by the Crystal Reports Designer, it might seem logical to install the designer on each person's desktop who might need to view reports. This powerful, distributed report creation capability might be overwhelming to some end users and more importantly can grow into an IT maintenance headache when the audience for reports grows large.

• Report Maintenance— Distributing out reports and their associated files to the desktops of business users makes it cumbersome to make updates and additions to those reports because of the need to manually update each user's machine and associated reports.

• Security— Only certain business users should have access to certain reports, and even within this group of authorized business users, certain levels of restrictions should be in place. The average business user might be given the ability to change the data displayed in a report by entering parameter values, but should not be able to change core aspects of a report. This report design and modification capability should be reserved for a subset of the reporting user base.

• Presentation to a Variety of Formats— Often, reports need to be presented in various formats, whether it is being previewed onscreen using one of the Crystal Reports viewers, embedded into a Web-based portal, or even displayed on a wireless device (for example, PDA, Mobile Phone, and so on).

Although more factors that affect report distribution exist, those listed previously are important ones to initially consider. As stated earlier, there are various methods for distributing Crystal Reports. Each method addresses some or all of the preceding report distribution factors. Often, if one of the factors is not addressed, it can be addressed with some additional work or by using a different method that might cost more in licensing and development time. This build versus buy decision will be a factor in which report distribution system you choose to implement.

The main report distribution scenarios are as follows:

• Building a custom application that delivers reports. Sometimes the entire purpose of the application is to do just this—other times, reporting is just a component of a surrounding application. If the application is a desktop application, the Report Designer Component (RDC) should be used. If the application is Web based, the Report Application Server (RAS) should be used.

• Using a pre-built report distribution system— Crystal Enterprise. There are two editions of Crystal Enterprise: Standard and Professional. These two editions address different needs outlined later in this hour.

When distributing your Crystal Reports and depending on the method selected, additional licenses might need to be purchased beyond your copy of Crystal Reports 9. To determine requirements or to purchase additional licenses, a Crystal Decisions sales representative should be contacted through the www.crystaldecisions.com Web site or by calling 1-800-877-2340.

Table 23.1 presents an overview of the different methods of Web report distribution and of each method's distinct advantages and disadvantages.

Table 23.1. Web Reporting Distribution Methods

Crystal Reports Web Distribution Method	Advantages	Challenges	Software and Versions Required
Crystal Enterprise Standard— Unmanaged	Direct URL access to Crystal Report files in virtual directories. Flexible URL commands supported to control viewers, parameters, filters,exporting, and so on. Five free concurrent user licenses.	Unsecured reports. Scalability limitation—single server deployment. No scheduling capabilities. No personalization. No user-based security.	Crystal Reports v9 Professional, Developer, or Advanced Edition Crystal Enterprise Standard or Professional
Crystal Enterprise Standard—Managed	Secured Repository for Crystal Report files directly accessible from Crystal Reports Designer. Provided Crystal Management Console for Web-based administration. Sample e-Portfolio interface or flexible URL commands to access reports in a secured manner. Five free concurrent user licenses.	No user-based security. Scalability limitations—single server deployment. No personalization. No extensibility in object management (for example, Crystal Analysis).	Crystal Reports v9 Professional, Developer, or Advanced Edition Crystal Enterprise Standard
Crystal Enterprise Professional	User and User Group level security model—Personalization and Publish/Subscribe models. Horizontally and vertically scaleable server architecture. Advanced report scheduling capabilities, including event dependencies. Sample ePortfolio interface or flexible URL commands to access reports in a secured manner. Secured Repository for Crystal Report files directly accessible from Crystal Reports Designer. Extensible architecture to manage third-party objects in addition to Crystal Reports (for example, Crystal Analysis or Excel). Open Security model to enable integration with LDAP or NT authentication. Fault Tolerant and Seamless Fail-over architecture.	Increased administration commensurate with increased functionality. Increased licensing costs to match increased out of the box functionality	Crystal Reports v9 Professional, Developer, or Advanced Edition Crystal Enterprise Professional
Report Application Server	Ultimately flexible access to report creation, access, and viewing functionality. Multithreaded object model to facilitate scaling on Crystal Enterprise infrastructure. Available in both COM and Java object models.	Requires programming expertise to leverage power of object model. Requires Crystal Enterprise Professional to scale to large numbers of users. Still does not provide 100% of Crystal Reports Design functionality in the object model.	Crystal Reports v9 Advanced Edition Crystal Enterprise Professional for Delivering to Large User Bases
Report Design Component Advanced	Ultimately flexible access to report creation, access, and viewing functionality. Familiar to Crystal Reports designers who have used previous versions of the RDC.	DLL is required to be present on Web server—further limiting scalability and introducing Web server contention. COM object model only—No Java object mode available. Requires programming expertise to leverage power of object model.	Crystal Reports v9 Developer or Edition Crystal Reports v9 Report Creation API License for any Report Creation programming

Each of the distribution methods for Crystal Reports will be explored in more detail in the next three sections. With the exception of the RDC, each of the different methods provides a surprisingly simple migration to the next one up in the functionality ladder. "Crystal Reports in Applications—A Developer's Perspective," explores the advanced developer methods of report distribution in addition to the new Report Parts distribution options in Crystal Reports version 9.

Unmanaged Report Web Access via Crystal Enterprise Standard

The simplest (and perhaps most familiar to existing Crystal Reports users) method of making Crystal Reports available over the Web is through the use of virtual Web directories set up on your Web server. (On Windows IIS, these can be set up through Internet Services Manager accessed through the Control Panel.) To provide Web access in this manner, Crystal Enterprise must be installed on either the Web server or a separate machine: If the latter, the Web Connector component of Crystal Enterprise must be installed on the selected Web Server. The Crystal Enterprise installation guide or online help files distributed with the Crystal Enterprise components should be consulted for help on installing components of Crystal Enterprise.

At the time of this book's printing, the latest shipping version of Crystal Enterprise was 8.5. This version does not support the Crystal Reports 9 file format and therefore will not work in conjunction with Crystal Reports 9. Soon after the release of Crystal Reports 9, a new release of Crystal Enterprise is expected that will fully support Crystal Reports 9. However, the Report Application Server (RAS) method of Web distribution discussed later in the hour will be available concurrent with the release of Crystal Reports 9.

To "Web-enable" existing Crystal Reports using this method and with Microsoft Internet Information Server (IIS) on the same physical server as Crystal Enterprise, the following steps are required:

1. Copy all relevant Crystal Reports into a single physical directory accessible by your IIS Web server.

2. Create a virtual Web directory for that selected physical directory by right-clicking on the folder from within Windows Explorer and selecting the Properties menu option.

3. Click on the Web Sharing tab and select the Share This Folder radio button. displays the Web Sharing and Alias dialogs you will be faced with.

4. Enter an appropriate alias for your virtual directory (for example, Reports or CrystalWebReports).

5. Click OK until you have saved all your changes, and the Crystal Reports in the selected physical directory will now be available over the Web at http\\webservername\virtualdirname\reportname.rpt, where

   • Webservername is replaced with your Web server's name

   • Virtualdirname is replaced with the Web sharing alias you created in step 4.

   • Reportname is any of the reports you have placed in the physical directory in step 1. Also, note that you need to include the .rpt extension.

displays one of the Crystal Reports sample reports being accessed through this method in a previous version of Crystal Enterprise.

More than just basic and direct access to existing Crystal Reports through this unmanaged method, Crystal Reports and Crystal Enterprise provide numerous methods of controlling the delivery of reports. lists a number of additional URL-based commands that enable enhanced control of the Web delivery of the Crystal Reports.

URL Suffix	Suffix Description	URL examples
INIT	The INIT command specifies the report viewer that will be used to view the report. If the INIT command is not specified, Crystal Enterprise detects the type of browser requesting a report and provides a default viewer that is most appropriate for that browser. The default viewer for Microsoft Internet Explorer is the ActiveX viewer. The default viewer for Netscape Navigator is the Java viewer. Other options include actx = Active X Viewer java = Java Viewer html_frame = DHTML with frames html_page = HTML java_plugin = Java plug-in nav_plugin = Netscape plug-in	http://machine/virdir/rpt.rpt?init=actx
CONNECT	The CONNECT command is appended to the INIT command with a colon. It reestablishes a connection to the Crystal Report Processing Server. This allows the business user to reset the report's parameters and logon information, and reprocess the report if necessary without having to begin a new browser session.	http://machine/virdir/rpt.rpt?init=actx:connect
PROMPTEX	The PROMPTEX command specifies values for parameters on a report or any of the main report's subreports. The PROMPTEX command can be used to pass multiple values or a range of single values can be passed into a report by separating them with commas. Ranged values can use square and round brackets in the following flexible manner: Bounded intervals [""-""] (""-""] [""-"") (""-""] Unbounded intervals (""-) [""-) (-"") (-""] A square bracket indicates that the interval is closed at that end, and that the specified number is included in the range; a round bracket indicates that the interval is open at that end, and that the specified number is not included in the range. In order to pass Date or DateTime parameter values over the URL, use the single value or date range methods as specified here: For single value Date or DateTime parameters, the promptex- command requires double quotes. For passing date ranges, brackets must be used as shown previously.	Passing the country parameter "Ireland" into the sample report. http://machine/virdir/rpt.rpt?init=actx&promptex-country="Ireland" Passing the city parameter "Toronto" into the sample report's subreport called sub1. http://machine/virdir/rpt.rpt?init=actx&promptex-city@sub1="Toronto" Passing the region parameters "Asia" and "Europe" into the sample report. http://machine/virdir/rpt.rpt?init=actx&promptex-regions="Europe","Asia" The following URL extensions highlight the flexibility in the parameter range functionality: Passing April through May into the report: promptex-monthrange=("3"-"6") Passing March through May into the report: promptex-monthrange = ["3"-"6") Passing July through December into the report: promptex-monthrange =("6"-) Passing date range parameters through the URL should follow the pattern of the following suffix: promptex-DateRangeParm= ["date(2002,07,08)"-"Date(2002,12,10)"]
SF	The SF command allows for the specification of a selection formula for the report. Any selection formula passed via the URL using the SF command will be appended with the logical AND statement to any existing selection formulas already in the report.	http://machine/virdir/rpt.rpt?sf={Customer.Country}='Canada'
GF	The GF command specifies a group selection formula for the report. This command works in an identical manner to the selection formula SF command.	http://machine/virdir/rpt.rpt?gf=Sum({Customer.Sales},{Customer.Country})>1000000
PromptOnRefresh	The PromptOnRefresh command forces the involved report to prompt the business user for parameter field values when refreshed.	http://machine/virdir/rpt.rpt?promptOnRefresh=1
CMD and EXPORT_FMT	The CMD and EXPORT_FMT commandsenable the involved report to be exported to the any number of the different file formats outlined here: Adobe PDF - U2FPDF:0 HTML 3.2 - U2FHTML:2 HTML 4.0 - U2FHTML:3 XML - U2FXML:0 Excel 5.0 (XLS) - U2FXLS:3 Excel 5.0 (XLS) Extended - U2FXLS:4 Crystal Reports (RPT) - U2FCR:0 Rich Text Format (RTF) - U2FRTF:0 Word Document (DOC) – U2FWORDW:0	This URL will bring up the rpt.rpt report file in Adobe Acrobat format within a Web browser. http://machine/virdir/rpt.rpt?cmd=EXPORT&EXPORT_FMT=U2FPDF:0

The URL commands outlined in the highlight the breadth of flexibility available when delivering Crystal Reports over the Web through the unmanaged mode of Crystal Enterprise. When any report is accessed through this mode, a Crystal Enterprise guest license is used for the duration of the time the report viewer is active and (by default) until 20 minutes after the last sign of activity on a report viewer (for example, Next Page, Last Page, Drilling-down, and so on). It is important to forecast maximal concurrent usage before Web deploying reports to ensure that no business users are disappointed with a no-access notification message. User license requests above and beyond what is licensed on the Crystal Enterprise system will be rejected.

Managed Web Report Access via Crystal Enterprise Standard and Professional

Although the unmanaged method of deploying reports provides a quick and easy method of Web enabling reports, Crystal Enterprise provides additional rich scalability, scheduling, security, and personalization functionality as described in that can only be accessed through the managed method of report distribution. Publishing Crystal Reports over the Web using either version of Crystal Enterprise (Standard or Professional) can be accomplished in one of three ways:

• Use the File Save As Command in Crystal Reports and select the Enterprise icon. This will prompt for Crystal Enterprise Credentials and will require the administrator ID and password for Crystal Enterprise Standard or any valid user ID and password in Crystal Enterprise Professional.displays a Crystal Enterprise Logon screen.

• Use the Crystal Management Console provided with Crystal Enterprise. displays a previous version of the console in which reports can be added through the Add Objects function. The Crystal Management Console is actually the central point of all Web administration for Crystal Enterprise, but its detailed description is beyond the scope of this book. See the Crystal Decisions Web site for more information on Crystal Enterprise functionality.

  
  

• Use the Publishing Wizard provided with Crystal Enterprise. displays a previous version of the Publishing Wizard that is accessed through the Start menu and then the Programs and Crystal Enterprise menu options. The Publishing Wizard will step you through the report publishing process, which is the recommended process for beginners.

• Once published into Crystal Enterprise using any of these methods, a copy of the involved report is transferred into a secure file repository managed by Crystal Enterprise, and a record of the involved file is created in the Crystal Enterprise system database.

The published reports are now stored in a secured reporting system and can only be accessed by authorized Crystal Enterprise users.

Access to these managed stored reports is provided through two methods:

• Using the Crystal Enterprise client interface called ePortfolio, accessed from the Start menu under Programs, Crystal Enterprise and the Crystal Launch-pad. displays a previous version of ePortfolio with the default logon ID of guest.

• Using direct URL commands in an almost identical nature to that described for unmanaged report distribution with a slightly modified format. Instead of direct requests to the Crystal Report in a virtual directory, requests are made to the Crystal Enterprise system, and Crystal Reports are referenced through a Crystal Enterprise View Report command (viewrpt.cwr) and an Object ID that can be accessed from the Crystal Management Console. A sample URL could be http\\server\crystal\enterprise\viewrpt.cwr?id=1210.

All the URL suffix commands described can be applied to the end of these managed URL requests. The direct URL based report access method described previously is further extended within any managed Crystal Enterprise environment to include additional commands to leverage the additional security functionality that is provided.

These additional commands are described, but the full descriptions are beyond the scope of this book. For more information on direct URL access to Crystal Reports through Crystal Enterprise, the Crystal Enterprise Manuals should be consulted.

URL Suffix	Suffix Description	URL Example
ID	A required suffix for accessing reports in Crystal Enterprise through a URL. The ID is a unique identifier of current report within the Crystal Enterprise system. It can be discovered through the Crystal Management Console.	http://server/crystal/enterprise/viewrpt.cwr?id=510
APSTOKEN	The APSTOKEN provides one of two methods of passing user authentication information to Crystal Enterprise to enable the system to validate the credentials of a selected user. The APSTOKEN is actually a series of numbers and characters created by Crystal Enterprise when a business user logs on and facilitates license management by keeping track of which users are logged on at any given time. The Crystal Enterprise user guide will need to be consulted to fully understand how to access and use the APSTOKEN.	http://server/crystal/enterprise/viewrpt.cwr?id=510&apstoken=APSName:A5I5
APSUSER, APSPASSWORD, APSAUTHTYPE	The other method that allows Crystal Enterprise to authenticate users is with the APSUSER, APSPASSWORD, and APSAUTHTYPE commands. These commands allow the passing of the necessary values to authenticate the business user against the Crystal Enterprise system database.	http://server/crystal/enterprise/viewrpt.cwr?id=510&apsuser=Administrator&apspassword=pwd123&apsauthtype=secEnterprise

More than simply providing secure access to Crystal Reports, Crystal Enterprise provides a comprehensive set of enterprise reporting features, including those highlighted in . Although a detailed description of Crystal Enterprise features is beyond the scope of this book, additional system features and information can be found on the Crystal Decisions Web site. Crystal Enterprise is the Web distribution system that should be used whenever reports are mission critical, used when there is a large business user or report base, or used when security, personalization, or report scheduling are required.

Programmatic Report Distribution Using RDC or RAS

Crystal Decisions provides two software developer kits that enable report sharing via windows and Web-based applications. Both the RDC and RAS object models are available in the Developer or Advanced editions of Crystal Reports v9. RAS is also available in the Professional Edition. Both object models provide access to advanced Crystal Report capabilities for integration into custom developed applications.

The RDC components are fully compatible with the Microsoft Component Object Model (COM) and will be familiar to developers acquainted with previous versions of Crystal Reports. The RDC functionality can be accessed from any language that is COM-compatible—Visual Basic, Active Server Pages using VBScript or JavaScript, C++, Delphi, or the Microsoft Web Development Environments (InterDev or FrontPage). Three drawbacks to using the RDC in your Web applications are: (1) it must be present on your Web server to operate, (2) it is single threaded, and (3) it must run in process on your Web server. These three factors lead to challenges when trying to scale an application, and RDC-based applications do not provide a simple migration path to Crystal Enterprise. Because of these reasons, the RDC is targeted toward use in desktop applications only.

The RAS object model was created to address these concerns and has been designed to be multithreaded and server- based. Additionally, the RAS has been designed as a seamless component of the Crystal Enterprise architecture, providing developers with a smooth migration path from this server to large-scale deployment on Crystal Enterprise Professional. The final vote to support using RAS over RDC is that RAS has been designed to be both COM and Java compliant. These parallel versions of the object model access the same Crystal Reports functionality and open up the power of Crystal Reports and Crystal Enterprise to the Java development community. In summation, the arguments for using RAS over RDC in custom application design are

• It is multithreaded.

• RAS is server based (does not need to reside on a Web server).

• It integrates seamlessly into the Crystal Enterprise framework.

• It is scaleable.

• The RAS object model will be available in both COM and Java formats.

The next hour discusses the RDC and RAS components, as well as other Crystal Reports development aspects, in more detail. Whatever approach you select to distribute your Crystal Reports files, it is important to keep these in mind as options for your initial foray into Web distribution of your Crystal Reports.