Have you ever used the Highlighting Expert in Crystal Reports? Chances are your response is no, and you are wondering what it is. The Highlighting Expert is a button on the Experts Toolbar. The button looks like this: .

The Highlighting Expert is also on the shortcut menu of the right-click. Once you choose this Expert, this dialog box opens:

1. Click on the New button in bottom left corner.
2. On the right, choose the Operator for the criteria and the amount you want to highlight.
3. Now you can change the color of the Font, the Background, and/or the Border.
4. You can Add as many Highlights as you wish with various values, with the New button.
5. You are able to see how it will look in the Sample area.
6. You are able to change the Priority by selecting and using the little arrows to reorder.
7. To remove a highlight, simply select on the left and click Remove.

Try it out, the Highlighting Expert is fast and easy to use!! It gives the “wow” to your report.

Happy Reporting!

Let’s suppose that a third party designed the report you need, but you have to modify it. Where to begin? Follow this checklist to give you, as the report modifier, answers that you may need to go forward.

1. Save the report with a new name so you keep the original. (Work on the copy for your own mental health.)
2. Go to the Database Expert. Check the connection type on the Data Tab - that will tell you only the type of connection (e.g, local, OLE DB, SQL etc).
3. Still in the Database Expert, click on the Links Tab. Check to be sure the tables used for the report are linked correctly.
4. Now check with the Select Expert to see how the records are filtered.
   1. What fields did they use?? What restrictions did they put on the records? For example, did they filter for only records with an order date of 2009?
   2. If any or all of the filters are no longer valid, delete them by clicking on the tab and clicking on Delete.
5. Refresh the report. You probably will get more records than the original report as you have removed the filters.
6. Now, you may need to filter for the new report you are designing. You will use the Select Expert. Choose the database field you need to use and design the filter using the two dropdowns on each tab, the first is the operators and the second is the criteria.
   1. For example, your report needs to show the data for NM, ID, and AK. The operator: is one of and the criteria: NM, ID, AK.

   

7. If necessary, go to Print Preview.
8. Remember to save it as you work.
9. Refresh the report.
10. You would continue on in this manner until your report displays the data that you need, according to the request.

Remember, everything takes practice. The more you work in Crystal Reports, the easier it will become. Take your time and practice.

Happy Reporting!

Drill Down is one of the great features in Crystal Reports.  You have to group the report and hide the Detail Section. Now only the Group Names (the Group Header) are showing with any Summary information in the Group Footer. Double clicking on a Group Name generates a separate tab and a sheet will be created on only that Group.

So how do you do that?

1. Design you report as usual.
2. Insert a Group and summarized data by Group if desired.
3. In Design mode, right-click on Section Name Details:

   

4. On the shortcut menu-choose Hide (Drilldown OK).
5. Crystal Reports will put diagonal lines across the section. (See them above).
6. If you would like to hide more sections, repeat steps 3 & 4.
7. Also, you are able to move the Summary Data from the Group Footer up to the appropriate Group Header section and hide the Group Footer.
8. In Preview mode, the mouse will change to a magnifying glass as you mouse over the Group Header. When you see the magnifying glass—double click.
9. Crystal Reports has generated a separate tab and sheet of data for that Group only!! How cool!

Happy Reporting!

Drill Down is covered in our Introduction to Crystal Reports class and our Intermediate Crystal Reports class.

Hear about our latest Crystal Report blogs, classes, and webinars by signing up for our newsletter.

There are various ways you can distribute the reports you designed in Crystal Reports even if you aren’t using Crystal Reports Server or any other Enterprise Servers. Some of the ways are the old standby methods such as PDF and MSWord. These methods work fine, as they definitively meet the needs of the organizations report readers.

Some designers choose to Export the reports to Excel or other applications. There are two methods of exporting to Excel – one has the suffix ‘Data-only’ in the Export box.

This particular Export Format allows the designer to include “Worksheet Functions” to the Export allowing more calculations on the report data. The other Export Format to Excel does not offer that option: it is more like a photograph of the report.

Other Export options include:

• HTML
• ODBC
• Record Style (columns with or without spaces)
• Comma Separated Text
• Tab Separated Text
• Rich Text Format and XML

All of these are accessed through the Export dialog box above under the File Menu.

Aside from the Export, the report designer can assist a user in downloading the Crystal Reports Viewer. This is a free download sent to local machines. When the user wants to view the report, the user opens the Viewer and opens the report in the Viewer. The report is displayed as the designer intended it to be seen. The Viewer can also display any ‘drilldowns’ that were designed into the report, which many other formats cannot.

The last method I will mention here is crystalreports.com. This is a secure report hosting service available at crystalreports.com; for those that simply need to see reports. The user logs into specific URLs to view specific reports within the folders you have created. No need for hard copies or email attachments. This is available with free trial for three named users at above website. It offers several different options, with a monthly structured plan.

Did you know that in Crystal Reports you can design your own customized groups for your reports? All you need to do is to decide what the new groups will be called and what the rules or criteria are for the different groups. You are ready to go!

Let’s say that you have a list report containing 250 companies and the total sales amount for the year to date. The Sales Manager would like to see them grouped as follows:

1. Gold Star Customers (Total Sales Amount greater than $100,000)
2. Silver Star Customers (Total Sales Amount between $50,000. and $99,999)
3. Bronze Star Customers (Total Sales Amount between $10,000. and $49,999)
4. Must Contact Customers (Total Sales Amount less than $9,999)

Here’s how you customize the grouping in Crystal Reports:

1. Insert a group on the Total Sales Amount field.
   1. Select Group from the Insert menu
   2. In the order drop down (second drop-down on Common Tab)—choose Specified Order.
2. A New Tab will open with the label Specified Order.

   

3. Type the name of the group ie. ‘Gold Star Customers’ and click on the New button, which opens the following dialog for entering the criteria of this group:

1. Add your criteria and click OK.
2. Create the other two main groups in the same manner.
3. The last group, Must Contact Customers, will go on the tab for Others.
   
4. Select the tab Others – notice the radio button is already there, just change the name in the text box from ‘Others’ to “Must Contact Customers”.
5. You now click on OK and you are done!!

You have now made your first custom grouping for your report! Congratulations.

Happy Reporting!

When you design your reports in Crystal Reports, Crystal Reports uses the database format for the database fields.  It also uses the database fonts as I discussed in Setting Default Fonts in Crystal Reports. This, again, requires that you format these fields every time you want to use them in you report.

I recommend you take the time upfront to change these default settings in Crystal Report so that you won’t have to change the settings on each new report you create. Here’s how you change the default field settings in Crystal Reports:

1. From the File Menu, select Options.
2. Choose the Fields tab.
3. For each field category, select the format you would use regularly. (For example; for Numbers you may like the comma format and 2 decimal places.)
4. Currency could be the same as Number, but with the currency symbol:
   • Floating places the symbol directly to the left of the number, e.g.  $456.87.
   • Fixed places the symbol several spaces away from the number, e.g. $       456.87.
5. Date is often in the database as Date and Time, but your organization does not use the time part!  Format it so that Crystal Reports fixes that so it appears the way you want it on your report.  You can include the leading zero (05/05/2010) or not (5/5/2010.)  Pick the preferred format for your organization.
6. Date and Time should be the same as Date, if your organization does not use the Time part.  Otherwise, format according to the preference of your organization.

Keep in mind, Crystal Reports is considered a presentational report designing tool.  While your report should always look professional, it does not mean the report designer needs to spend hours formatting it.  The report designer needs to present the correct data in a professional manner, but with Crystal Reports, that processt does not have to be difficult!

Formatting using the Rounding Function

In general, Crystal Reports users set up the default formatting to display the data in the way it is to be used most often. However, when it is necessary to be able to switch quickly to another format – right-clicking to format the field is usually the solution.

If the data field comes down to Crystal Reports with several decimal points, one solution would be to use the remove decimal places button on the tool formatting bar. That button actually rounds up on decimal place numbers greater than 5 and rounds down on decimal place numbers less than 5.

If that data is included in a formula, the Round function could be used on the number to be formatted. To give the report designer more control there are actually two functions for rounding numbers: Round and RoundUp. Each of these rounding functions have two sub-functions, which we will see below.

Round Function

The syntax is simply Round (x), with x being the numeric or currency field to be rounded. Here are some examples of the Round function:

• Round(1.234) = 1
• Round(1.4999) = 1
• Round(1.5000) = 2

You can also add a second argument to the Round function to indicate how many places to be rounded to as represented by y here: Round(x, y). Some more examples:

• Round(1.234,2) = 1.23
• Round(1234.2345,2) = 1234.23
• Round(2345.23456,0) = 2345

RoundUp Function

The syntax is RoundUp(x), again with x being the numeric or currency field to be rounded. Here are some examples of the RoundUp function:

• RoundUp(4.234) = 5
• RoundUp(1.4999) = 2
• RoundUp(1.25000) =2

Again, you can also add a second argument to the RoundUp function to indicate how many places to be rounded to as represented by y here: RoundUp(x, y). Some more examples:

• RoundUp(1.234,2) = 1.25
• RoundUp(6.36521,3) = 6.366
• RoundUp(2345.23456,0) = 2345

If the second argument is left blank then the function rounds up to the nearest integer. In other words, 0 is the default for the second argument.

All this sounds complex, but it really isn’t. I would suggest that you try it on simple numbers with decimals first. Then, when you have the expected results, place it in your Crystal Report using it with the database field.

Taking it a step further, as in Arne’s comment – an if-then-else operator would have to be incorporated using syntax similar to this example:

If region in[CA, MA, FL] then Round({orderdetails.orderamount},2)

Else Round({orderdetails.orderamount},0)

Happy Rounding with Crystal Reports!!

Tired of constantly formatting your fields in Crystal Reports?  Frustrated because you  have to do it for every report?  Crystal Reports uses default fonts provided in the database and they are not usually what you would consider attractive for your report.  You can change the default fonts so that when you design a new report, Crystal will use your preferred fonts in the new report.

Before you start your next report, take the time to set your default fonts for all future reports by following these steps:

1. From the File Menu, select Options.
2. Choose the Font tab.
3. Select each field category, select the font face and size. (For example: for Group Names, you may like Arial 12 and Bold.)
4. Consider making the Summary Fields the same style, so the Subtotals will automatically match the Group Names.
5. Fields are the record data, so a smaller size would be appropriate (e.g, 10 or 11).
6. Field Titles are the Field Names or Labels that automatically go in the Page Header so you may want to Bold and Underline them.  Maybe make them a size larger than the fields.

Remember, Crystal Reports is a professional presentational reporting tool.  Your reports should appear professional, but they do not have to be difficult or time consuming for the report designer.  The main goal is to provide the correct data, but it’s nice if you can do it with a little style.

Hope this makes your reporting easier for you!!

Formatting Reports is covered in our Introduction to Crystal Reports 2008 class.

Hear about our latest Crystal Report blogs, classes, and webinars by signing up for our newsletter.

The Object Browser

In Xcelsius, the Object Browser is your new best friend.  It stores and displays all of the components that are on the canvas and allows you to hide them and/or lock them into position.  Xcelsius allows you to design a dashboard  by placing components on the canvas.  It adds exciting visualization to Excel data– trends, what-if, projections and much more.

To add a component to a canvas of the visualization:

1. Open the spreadsheet that contains the data you want to use.
2. Decide which type of component you need. ( Selectors, Containers, Charts, Gauges.)
   
3. Select the component and click on the canvas to insert the object.
4. Use the Object Browser to control the component.
5. Click on the eye to hide temporarily.
6. Click on the padlock to lock the  component position on the visualization.

Once you have completed the components layout, you save it. The process of saving converts your visualization of the Excel data to an .xlf file.  Click on preview and and a .swf file is created.   This file then can be placed in a web page, PowerPoint, Word, Excel and Acrobat.

Xcelsius creates a dynamic, interactive and visual presentation of Excel data and a wow factor to your presentations!

Learn more about the Object Browser and other cool Xcelsius features in our 2-day Xcelsius class.

What exactly is Conditional Formatting in Crystal Reports?

Conditional formatting, put simply, is formatting  (e.g, font color, font style, etc.) applied to data that meets certain rules or conditions.  For example, the person requesting the report would like the Total Sales field to have a green background if the amount is greater than $5000.

But how is this conditional formatting magic done?

1. Select the field to be formatted.
2. Right-click and choose the border tab – background color is in the center of that tab.
3. Do not click in the color checkbox, rather click on the X+2 box directly to the right of the background color. The Formula Editor will open.
4. In the Operators column choose Control Structures for the If Then Else.
5. Double-click to bring that down to the work area. At the top of the functions column, you’ll see two new categories associated with formatting text/numbers:
   1. Formatting Functions
   2. Color Constants
6. Now the tricky part: you have to construct a formula. I’ve included a couple of examples below.

if Sum ({Orders.Order Amount}, {@GroupSort})>5000
  then crGreen else crNoColor

Note that the colors are preceded by cr in lower case – Crystal Reports needs this, so do not enter a color name without it.  Also note that the else argument is followed by crNoColor.  The else could also be followed by DefaultAttribute, like so:

if Sum ({Orders.Order Amount}, {@GroupSort})>5000
  then crGreen else DefaultAttribute

The results would be the same but the second formula would revert back to whatever the formatting color was that did not meet the condition. Of course, the formulas could be much more complex but the logic remains the same.  If the formula returns true do this othewise do something else.

Happy formatting!

We cover conditional formatting in our Introduction to Crystal Reports class.

Hear about our latest Crystal Report blogs, classes, and webinars by signing up for our newsletter.

So, you’ve been told you need to start using Crystal Reports. It’s installed on your machine and you’ve opened it.  Now how do you get started?

The good news is that you don’t need to be a programmer or a database expert. However, you will need to be aware of the business requirements
for the report, how the report will be distributed, and be familiar with the data in the database.  Assuming you have this knowledge, you can jump right in:

1. To begin, within Crystal Reports, indicate to Crystal what kind of a database it is and where it is stored.  You will likely need to get this information from someone else in your organization.
2. The second step is to choose the tables that store the required data and be sure the tables are joined correctly to provide the correct records that will meet the requirements for the report.
3. Once that is accomplished, place the necessary fields on the report and manipulate those fields to design a professional,attractive and understandable report.

When you select Print Preview, Crystal Reports  will generate the report by retrieving the records from the  database and display the requested data on the report.  It then is the time to be sure that the newly designed report meets all the business requirements per the request.  Changes to the report, additions and deletions are simple to employ. If all is good, you can then export the report to several different Windows applications or to HTML.  There is even a free Crystal Reports Viewer for those who don’t have a copy of Crystal Reports.

We provide plenty of practice creating basic reports in our Introduction to Crystal Reports 2008 class.