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.





Chelsea Technologies website

Friday 27 August 2021 @ 10:11 am

Some of you may have noticed that the Chelsea Technologies website is “under construction”.   If you are interested in any of their products you can Email them directly using this Email (put in the @ symbol):

mo/at/chelseatech.co.nz





My Crystal Reports Expert Series released as free downloads

Tuesday 24 August 2021 @ 10:05 pm

Last month I released my Intro and Advanced course books as free downloads. Tonight I released the entire Expert’s Series of guides as free downloads. All of these are all on the “Library” page of my site. You are free to use them and share them for free as long as they are not modified or sold.





Matching colors in your reports

Wednesday 11 August 2021 @ 6:19 pm

I had a project this week where I had to replicate the look of a very colorful spreadsheet. It featured 4 different shades of green. Matching an existing color in a Crystal Report usually involves a fair amount of trial and error, but I can usually get close.  This time I was having a hard time matching a light green and it dawned on me that there might be a web site to identify colors.  I had just gone to the paint store with a chip of drywall and came home with a gallon of paint that exactly matched a faded wall color, so I figured the odds were good.

I did a quick search and found many free sites where you can upload an image file and click on a color to get the RGB / HEX / CMYK codes for that color.  I took screenshots of several spreadsheet cells, uploaded them and got instant matches. The customer was impressed and I didn’t have to do any guess work.

You can find a page full of sites with a search on “image color picker”.  I like these two in particular because you can put in a URL for an online image file as well as uploading an image. Not all the sites had that part working well.

https://imagecolorpicker.com/en
https://www.ginifab.com/feeds/pms/color_picker_from_image.php

UPDATE 8/11/2021:

I just heard from Adam Butt of APB reports, who pointed out that there is a color picker tool in most image editing programs, including the classic MS Paint, Paint.net and Paint 3D. I have been using classic MS Paint forever and never paid attention to the eye dropper icon:

color picker icon

This lets you click anywhere in the image and gives you the color codes at that spot.





Using reserved characters in formula names

Monday 9 August 2021 @ 8:03 pm

I recently heard from Gordon Portanier (from the Crystalize consulting group in Canada). He was doing something I have seen several customer do over the years. He would start certain formula names with a symbol so those formulas would sort to the top of the field explorer list. Gordon happened to use the @ symbol.

When he upgraded to CR 2020 these reports ran fine, but he found that he could not create a new formula that had an @ symbol in the name. A message told him that four characters [ @ { } ? ] were now considered reserved characters and could no longer be used anywhere within the name of a formula field.

Existing formulas, however, are not affected unless you try to rename them. So Gordon found another report that had the formula names he needed and copied them to the current report. The formulas work fine.

Up through CRv12(CR 2008) a formula name could include ANY character in any position. But when I tested this in CRv14.2 (CR 2016) I got the same warning as Gordon. My guess is that this restriction started with CRv14 (CR 2011) and so it would also affect CRv14.1 (CR 2103) and all later versions.  If any of you are using CRv14 (CR 2011) or CRv14.1 (CR 2103) you can confirm this by trying to add a formula with an @ in the name.  Let me know what happens.

An interesting point. This rule only applies to formula names. It does not affect the names of SQL expressions, parameters or running totals.

Another interesting point is that the @ (which represents formulas) and the ? ( which represents parameters) are reserved. But you can still use the % symbol (which represents SQL Expressions) and also the # (which represents running totals).





RPT management utilities for 2021

Friday 30 July 2021 @ 9:39 pm

I have just updated my comparison of RPT management utilities for 2021. These are tools that allow you to scan, document, compare and in some cases batch update RPT files. The list includes 9 tools:

Report Runner Documentor by Jeff-Net
R-Tag Documentation and Search by R-Tag
CR Data Source Updater by R-Tag
Visual CUT and DataLink Viewer by Millet Software
Report Miner by the Retsel Group
Code Search Professional by Find it EZ Software Corp.
Dev Surge 365 by Find it EZ Software Corp.
.rpt Inspector 3 Professional Suite by Software Forces, LLC
.rpt Inspector Online by Software Forces, LLC





Crystal Reports 2016 date formatting issue

Wednesday 21 July 2021 @ 2:29 pm

When you set up Windows you get to select a Regional Format for things like dates and times. For instance, English(US) format will show today’s date as 7/21/2021 while English (UK) will show today’s date as 21/7/2021. In Crystal, certain date objects can be assigned to use these formats, so that they automatically change from one format to another based on the regional format setting of the PC running the report. This is handy if you have users in different countries. To use this feature, right-click on the date field, choose “Format Field” and on the “Date” tab select the “System Default Long/Short Format”.

However, I just had a user complain to me that he couldn’t get CR 2016 date fields to respond to the regional setting. So, I did a quick test in my own environment and found that changing my regional Format from English (United States) to English (United Kingdom) had no effect on date format in CR 2016. I closed Crystal and reopened it, created a brand new simple report and put the print date in the Page Header – and it still showed in US Format. To make sure I was using the right setting I tried the same test in CRv10 and the print date came up in UK format. I repeated the test in CRv8.5 and it also came up in UK format – but not in CR 2016.

I did an online search and found an SAP thread where someone else had the same issue when they upgraded to CR 2016. SAP’s response focused on the database client (?), but the issue didn’t appear to have been resolved.

I wonder if Crystal is somehow pulling the regional format correctly when it is installed, but then isn’t updating the region if it is changed after the install. If anyone is seeing different behavior or has insight to share, please let me know.





Download my advanced course materials

Saturday 10 July 2021 @ 7:50 pm

In April I made my Intro course materials available for free.  I mentioned this in my blog and newsletter and so far several hundred users have downloaded the material. This month I am going to make my Advanced course materials available in the same way.

Just like the Intro material, you are welcome to download the materials and use them. Share them with your friends. Please do not modify them or try to sell them.

Note that as a consultant, people often pay me to help them use or learn Crystal. You can, too. So if you have questions about Crystal Reports I am happy to schedule a short consult for you. This is explained further on these links:

https://kenhamady.com/faq.shtml
https://kenhamady.com/support/default.html





«« Previous Posts
Jeff-Net
Recrystallize Pro

Crystal Reports Server