This post is another in our series on advanced Crystal tips and tricks. We’ve gotten great feedback when we’ve included these tips in our presentations, and we hope to have these not only as regular posts but also as videos.
Why Cross Tabs?
Crystal is a great tool for creating detailed operational reports that are formatted just so. These reports tend to resemble traditional reports built in COBOL or other server languages. The reports look good and are easy for end users to comprehend. For example, below is a simple Crystal report listing invoices totaled by line of business and customer:
This is typical of what most people start with – a list of transactions and related data. But while this type of report is great for operational folks who want to see all the detail just so, it’s not so great for analysts. It’s time consuming to build. And sometimes, you just want a quick look at the data as a starting point for analysis. Or you want to group and total different ways, multiple times. This is where Cross Tab shines.
Let’s start with a basic report like the one above. It’s summarized by customer and line of business. So, let’s build a simple Cross Tab report that will show data summarized by customer down the side and line of business across the top.
Start by going to the footer section of the report in design view.
Now go to “Insert-> Cross Tab.”
An orange block will appear which you should position in the report footer.
Your footer should now look something like this:
If you worked with Excel Pivot tables in the 2007 and later versions, the next steps may seem familiar.
Right click on the cross tab you’ve inserted and select “Cross-Tab Expert.” You will come to this screen:
Let’s look at the four key boxes on this form. On the left side, we have fields we can use. This is similar to the listings you see when creating formulas.
On the right side, there are three boxes. The “Columns” box will show what we want to group by in our columns or “across the top.” The “Rows” box will show what we want to group by in our rows or “down the side.” The “Summarized Fields” box will show what value we’re calculating.
In this case, let’s say we want line of business across the top, customer down the side, and summarize by amount.
Let’s start with customer. Highlight “CUSTOMER” under “Report Fields” and click the arrow by the “Rows” box.
Customer will now appear under rows.
Let’s now setup our columns. Highlight “LOB” (line of business) and click the arrow by the “Columns” box:
And finally, we want to summarize amount. Highlight amount and click the arrow by the summarized field box.
Now, click “OK” to run the report.
Go to the report footer. You’ll see your new cross tab nicely summarized. It’s easy to get totals when you don’t want all the transactional detail.
The real beauty of cross tabs is what happens next. If your users are like our customers, they’ll ask for changes after you deliver the report. For example, they might want to see customer by period for the entire year for a particular line of business. It’s easy enough to add this parameter to the report. If you were to do it with regular Crystal formatting, adjusting all the groups and totals would be time consuming. With Cross Tabs, it just takes a few clicks.
To demonstrate, let’s replace line of business with month on our report. Simply go to “Cross-Tab Expert” and replace line of business with month.
Do this by highlighting “LOB” in the “Columns” box, and click the left facing arrow to remove the value.
Now highlight “MONTH” and click the right facing arrow. You should be here:
Click “OK” and you’ll see your revised report.
While we chose to modify an existing report, you don’t have to. You could create a new Cross Tab report (“New-> Cross-Tab Report”). But often people find it easier to modify an existing one.
You’ll probably find your totals aren’t where you want them, and the formatting isn’t perfect. Also, you may want to see more than one level of totals. We’ll cover how to solve these issues in our next post in this series.
If you like this type of thing, we’re building a set of examples and sample data. Let us know if you’re interested, and we’ll let you know when they’re ready for prime time.