Archive for September, 2021



Adding an “All” option to a dynamic parameter

Sunday 19 September 2021 @ 10:05 am

If you want your parameter’s list of values to be pulled from the database you can use a dynamic parameter. But one of the down sides of a dynamic parameter is that you can’t type additional values to include in the list.  A dynamic parameter can only show values pulled from the data source. So if, for instance, you are pulling in a list of products and you want the list to have an “All” option at the top, you can’t simply add the word “All” to the list like you could with a static parameter.

My preferred way for adding an “All” option to a dynamic parameter is to use a SQL command as the source for the dynamic parameter. Using a SQL command gives you several other advantages as well, such as allowing you to filter your the list of values. Here is an example of a SQL command that will add an “All” option to the list of values (incorporating suggestions from MHurwood below):

Select Items.ID, Items.Desc
From Items
Where Items.Status = ‘A’

UNION ALL

Select ‘…All’, ‘…All’

The part above the UNION creates a list of all the active items, showing both the ID and the description of the items. The part below the UNION adds one row to the results of the query with the “All” option. Notice that “All” entry has several periods in front of it. This is one way to sort that value to the top of the list. You can use this method to add several values to your dynamic list, if needed.

Note that you wan to avoid using the fields from this command in other parts of the report. It should be used only for the dynamic parameter.

One of my colleagues, Angela Meharg of Datisfy, reminded me that you can use optional parameters to do something similar.  Instead of explicitly selecting a word like “All”, you can skip over the parameter.  Then you can program the selection formula to say that when the users doesn’t select a value they get all values.  The formula would look something like this in the Crystal selection formula:

and (if not (HasValue({?Items})) then True else {Table.Item} = {?Items})

In English this means, if there are no values in the items parameter, then every record qualifies. Otherwise the items that qualify are the ones that match the parameter.

If you have trouble with one of these options, you can schedule a short consult and I can give you a hand.




Hot-swapping fields

Saturday 11 September 2021 @ 8:27 pm

I just started a project of reading through my old blog posts from the very beginning. I am deleting things that are obsolete and updating posts with newer links and information. I even expected to find a few useful things that I had written and forgotten.  I found one in my 3rd post.

In August of 2006 I wrote about some new features introduced in Crystal Reports v11.5 (XI r2). One of them I don’t remember at all: hot-swapping fields.

Say you have a field on your report.  It is the right size, in the right position and with all the right formatting. Then you realize that you should have used a different field. Crystal allows you to hot-swap another field into that place. All you have to do is drag the field onto the report from the field explorer and hold down the shift key.  When the new field gets to be over the old field the new field will snap into place and you will see two gray arrows pointing in opposite directions. If you release the mouse at that point the new field will take the place of the old field, keeping the same size, position and most formatting properties. You can swap any type of field with any other (but not text objects).

I say most formatting properties because it will not keep conditional formatting formulas. But it will the formatting properties you can set from the toolbar (font, size, underline, color, etc.).   If you want to apply the conditional formatting properties to the new field, you can copy the original field and paste it somewhere else before you swap it out. Then after the swap you can use the “Format Painter” (paint brush) to apply all the original properties to the new field.  Then delete the extra copy of original field.

One other issue has to do with column headings. Some fields are tied to a column heading that were automatically generated by Crystal Reports. This happens when you start a report using the report wizard or if you place a new field on the details band. When you hot-swap a field tied to a column header the header will automatically change to the field name of the new field.  If you prefer the original column heading you can make a copy of the heading before you hot-swap and place it somewhere else on the page. Then after the hot swap you can delete the new column heading and add the original one.




“Column mode” in the Crystal formula editor

Sunday 5 September 2021 @ 11:31 pm

I have written several articles about using Notepadd++ for writing long formulas or SQL statements. I just found out that one of the features I use in Notepad++ has been (partially) available in Crystal Reports forever.  I never noticed. The feature is called “column mode” and allows you to select text in a column without selecting the entire row.

Lets say you start a formula with DateTime variables like this:

WhilePrintingRecords;
DateTimeVar DateA;
DateTimeVar DateB;
DateTimeVar DateC;
DateTimeVar DateD;
DateTimeVar DateE;

Then you realize that you want them to be Date instead of DateTime. You can highlight a “column” made up of the word “Time” on all 5 rows at once, and then hit delete. To select a column you hold down the ALT key and then click the mouse in the upper left corner of rectangle and drag to the lower right corner. In this case you would start just before the “T” in Time in the first row, and drag down and across until your cursor was just after the “e” in Time in the last row. By using the ALT key you will highlight a rectangle of 4 characters across and 5 rows down. Then you hit delete and just those 20 characters are deleted.

You can also copy and paste a rectangle. Say you have to write a formula that is something like this:

If month({Trans.Date}) = 01 then {Balance.Pd01} else
If month({Trans.Date}) = 02 then {Balance.Pd02} else
If month({Trans.Date}) = 03 then {Balance.Pd03} else
If month({Trans.Date}) = 04 then {Balance.Pd04} else
If month({Trans.Date}) = 05 then {Balance.Pd05} else
If month({Trans.Date}) = 06 then {Balance.Pd06} else
If month({Trans.Date}) = 07 then {Balance.Pd07} else
If month({Trans.Date}) = 08 then {Balance.Pd08} else
If month({Trans.Date}) = 09 then {Balance.Pd09} else
If month({Trans.Date}) = 10 then {Balance.Pd10} else
If month({Trans.Date}) = 11 then {Balance.Pd11} else
If month({Trans.Date}) = 12 then {Balance.Pd12}

Normally I would start by typing the first row and then copy it 11 more times. Then I would change each row to use a different number from 1 to 12.  Once I have changed the column of values after the ‘=’ sign I could copy that 2-digit column and past it over the values in the other 2-digit column in one step.

To do this you select the 2 digit column as a rectangle using the ALT key. You can right clock in the column to copy (or use Ctrl-C). Then you select the other 2-digit column and right-click to paste (or use Ctrl-V).

Note, in Crystal you should select these rectangles starting in the upper left corner, especially if you plan to copy and paste.  You can get inconsistent results if you start in one of the other corners.

NotePad++ has a much more sophisticated column mode, allowing you to:

  1. Paste a single word or character value into multiple rows at once.
  2. Select a column and start typing. The new text is added to ALL the rows at the same time.
  3. Use Shift-Alt to mark the column using arrow keys , instead of the mouse.
  4. Select the column of text starting in any corner.

Unfortunately, these don’t work in Crystal.




Jeff-Net

Recrystallize Pro

Crystal Reports Server