One method for selecting multiple objects in Crystal (like most Windows programs) is to “lasso” them. To lasso objects in Crystal you click and hold your mouse button in an empty spot and then drag the cursor to expand an orange rectangle. When you release the mouse button, all of the objects inside (or touched by) the orange rectangle become selected objects.

The problem I often have with this method is that drawing objects (lines and boxes) often get selected as well. For instance, if you lasso a group of objects that are inside a box object, that box will often be selected. Usually this happens if you select the highest object in the box, even if you don’t touch the box itself. If your lasso box touches or surrounds line objects those lines are also selected.

Often I want to select a column of numbers for alignment or formatting and I want to skip over the lines. Before now my options have been to lasso the objects and then carefully deselect the lines and boxes by holding my [ctrl] key and then clicking those objects. Or I could skip the lasso method altogether and select that group of objects by clicking on them individually while holding down the [ctrl] key.

But this past week I discovered something I had never noticed before. If you use the lasso while you are in preview mode, the lines and boxes are NOT selected. I had created a grid of 40 numbers (4 across and 10 down) and had separated the rows and columns with horizontal and vertical lines. Plus there was a box around the perimeter. To select all the numbers individually in design mode would have taken 40 clicks. The lasso would take 13 clicks to deselect all the lines and the box. But while in preview I could select all the numbers in one lasso and none of the drawing objects were selected.

This made it easy to align the columns or change the format for the numbers as a group.

I created an unusual dynamic parameter for a customer this week. They wanted to select from a list of customers, but their customer list included lots of dead weight. To shorten the list they decided to include only customers who have sales of over $10K in the past 2 years.

To filter the results of a dynamic parameter you need to base it on a SQL command, rather than pulling it directly from a table. And because this filter involved a total our command needed a nested subquery. The inner query took all orders in the past two years, and did a sum of the order amount, grouped by CustomerID.  Then the outer query selected records from the inner query when that sum was greater than $10K.

The field for our parameter was the Customer ID but Crystal parameters allow you to bring in a second ‘description’ field along with the filter field. The Customer Name was our description field.

This worked fine, but we noticed that if a customer had multiple accounts, the customer name appeared multiple times with slight variations. I thought it would help if the user could see the sales along with each name. Since the SQL we had written already included the total sales for each customer, I figured we could make that part of the description field. In the outer query I replaced the original description field (customer name) with an expression like this:

A.CustName + '($' + Ltrim(Str(A.TotalAmt/1000,5,0)) + 'K)' As CustName

This generated a parameter list that looked something like this, with total sales shown as part of the parameter description:

AMCB3 – AMC Bonding Co., Inc.($15K)
BKDA1 – BKD Associates($10K)
CRSU2 – Credit Suisse AG ($13K)
DEVG2 – Deveraux Foundation($25K)
FOSE1 – Four Seasons($19K)

I have done something similar when the description field needed to combined  multiple fields, like FirstName and LastName. It can be done with any combination of fields.

Here is a new twist to an old trick. I wrote several years ago about preventing users from drilling down on a section by putting a text object over the other objects in the section. I was recently asked if there was a way to have this happen only on certain groups. My initial reaction was no because a text object is either there or it isn’t, but then I realized that more recent version of CR allow you to add to the width of an object with a condition formula. And, when you make the text grow based on a condition it still blocks the drill down for any objects that it grows to cover.

So say you want to allow drill-down on accounts that are open, but not on accounts that are closed. You could put a very narrow text object on the far left end of the group section for the account. Then you could go into “object size and position” and put the following formula into the width condition:

if {table.status} = "Closed"
then 1440 * 8.5 //1440 twips per inch
else 0

This formula calculates the number of twips to ADD to the existing width. There are 1440 twips in an inch, so I multiply the number of inches I want by 1440.  This makes it clear how much it will add and makes changing the value easier.

When a cross-tab gets too wide, it generates a ‘horizontal’ page to the right of the original. If you were to print the report to paper, these horizontal pages would print as separate pieces of pages. However, page header objects do not automatically repeat on these pages. So, any titles that you have in your page header will be missing on these horizontal pages. You have two options to get the titles to repeat:

1) If you want the fields to appear outside of the cross-tab, you can format them to repeat on each horizontal page. Right-click on the field, select “Format Field” and go to the common tab. You should see a property called “Repeat on Horizontal Pages”. Checking this property will cause the object to print again automatically for every horizontal page.

2) If you want the title to be part of the cross-tab, you can put your title into a formula and use that as your first column field. This title can incorporate parameter fields and can also include a carriage return character to print on multiple lines. An example formula might be:

WhileReadingRecords;
"Sales by Region and Shipping Mode" & Chr(13) &
"Orders From: " & ToText ( Minimum ( {?Order Range} ) , 0 , "" ) &
" To: " & ToText ( Maximum ( {?Order Range} ) , 0 , "" )

The key is that the formula has to start with WhileReadingRecords. This makes it eligible to be used as a cross-tab column. You make this field the first “Column” field in the cross-tab.  You should check the “suppress subtotal” property for this group level so that it doesn’t generate duplicate totals (cross-tab Expert, customize style, highlight this formula and check the property on the left).  And if the formula includes Chr(13) (to generate a second line) you will have to adjust the height of that cell to show the second line.  It won’t grow automatically.