Archive for the 'Method' Category



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.




Using HTML interpretation

Wednesday 30 June 2021 @ 2:31 pm

Sometimes database fields store their data with HTML formatting tags. When you put that field on the report it will display the raw HTML tags. However, if you format the field to use “HTML interpretation” Crystal will try to use the HTML tags to format the text. Crystal doesn’t support ALL HTML tags and properties, but there is a list of the ones that are supported on this page.

Another way to use HTML in Crystal is to include HTML tags in your formulas. For instance I could use a few HTML tags to highlight only a portion of a formula field’s output. Below is the formula for a 3-line address with the City name in bold blue text:

{Customer.Customer Name} & '<br>' &
{Customer.Address1} & "<br>" &
"<b><font color='#00559c'>" &
{Customer.City} &
'</font></b>, ' &
{Customer.Region} & ' ' &
{Customer.Postal Code}

The formula uses 4 different tags:

The tag <br> after the first 2 lines creates a new line.
The tags <b> </b> mark the beginning and end of the bold text.
The tags <font color=’#00559c’> </font> mark the beginning and end of the blue text.

You can look up other color code using a color picker site.

When you first put the formula on the report it looks like the left example below. You then use the menu options “Format > Field” and select the “Paragraph” tab to see the “Text Interpretation” option at the bottom. Select “HTML Text” and the formula will look like the right example below.




The wrong way to design a report

Friday 21 May 2021 @ 11:59 am

Anyone looking to test the limits of Crystal Reports might be interested in the stats for the report I was sent recently. It has over 1,200 Report Footer subsections that covered 30+ pages.  There were 2,200 running totals with conditions and 7,500 formula fields. I have never seen anything like it.

Surprisingly, it ran fine – but making changes to the layout was very slow and sometimes crashed Crystal. The customer wanted me to add two more columns. If I followed the existing model it would have taken another 250 running totals and another 750 formulas.

The reason the numbers were so high came down to an odd choice made by the original developers. They created the 30+ pages using only the Report Footer.  Every visible number is a unique formula or a running total. Each running total has a condition that assigns specific account numbers. There is no grouping even though the report has recurring sections that would lend themselves well to grouping.

I convinced the customer to let me rebuild the report from scratch using grouping. I have already redone 18 pages and needed only 100 formulas an no running totals. The account number mapping that was scattered in thousands of running total conditions is now consolidated into one formula, making the report much easier to maintain.

So, if you have a beast of a report that needs taming you might want to let me have a look.




I finally found a use for the Crystal Reports Workbench

Tuesday 18 May 2021 @ 7:40 pm

I finally had a use for a feature in Crystal Reports that I never use. It is called the Workbench. It is a place where you can create shortcuts to rpt files, and then organize them into projects. I was working on a report that was similar to some other recent projects and I wanted to keep the example reports handy (but not all open). By adding all the reports to the Workbench I could open and close them as needed without having to hunt for them each time. And these shortcuts didn’t roll off like files in the recently used file list.

To activate this feature you go to the VIEW menu and select “Workbench”. You can right-click to add a new project, or to add reports to an existing project. You can also move report shortcuts from one project to another by dragging them up or down. To open a report you right click on the shortcut and select open. The interface is simple and intuitive.




One way to exceed the table limits in MS Access

Monday 10 May 2021 @ 6:40 pm

A few years ago I wrote about a limit on the number of tables you can use when connecting to MS Access. The limit is 31.  If you add table #32 to a report it will crash with no error message.  Even using 32 tables in an MS Access query will generate an error message. I see this occasionally when dealing with reports for Raiser’s Edge.

Today we needed table #32 and I found a way to get it. I made a duplicate copy of the MDB and connected to the first instance to get 30 tables. Then I connected to the duplicate MDB to get the other two tables. I selected those two because their joins made up a separate branch from the other 30.

This worked because Crystal does two separate queries, one for each MDB, and then merges the two result sets locally. Crossing connections a last resort, and is never very efficient.  The report was a bit slower but at least it would run.




Improved formula for elapsed business hours

Saturday 24 April 2021 @ 9:50 am

Last summer I posted a draft of an improved formula for calculating business hours between two dates. It was meant to replace formula #13 on my formulas page, but I was waiting to do some additional real-world testing. Yesterday I got a good test case when a customer asked me to tweak the original formula to deal with start and end times outside the business day. I cleaned up a few typos and updated my formulas page.

This new version lets you separately specify a business start time and business end time for each of the 7 days of the week. It also includes additional logic to deal with events that start and/or end outside the business day (e.g. on a weekend, a holiday or after hours). The formula is now about 70 lines long. Fortunately, you only need to make changes in the first 20 lines or so. Here you specify:

  • The DateTime fields to use for the Begin and End of each event
  • The business start and end times assigned to each day of the week
  • The list of holiday dates, which you can enter for multiple years

The output is a numeric value in hours as a decimal. If you want to show the value in “HH:MM” format you can use the “Elapsed Time String” formula on my site to convert this value into that format. Remember to multiply this formula’s result by 3600 since the input for the “Elapsed Time String” formula is seconds.

If you need help implementing this formula or any of my formulas you can always call to schedule a short consult.




More uses for the Report Explorer

Thursday 22 April 2021 @ 10:16 am

I work with many different CR users. It seems that whenever I open the Report Explorer view in Crystal Reports, the user is a bit surprised. I get the impression that not many people use or know about this feature. I wrote about it once before (a decade ago) but since then I have found two more uses that I tap into regularly.

1) Selecting one of several superimposed objects.
One report I created for an educational assessment company had 4 superimposed picture objects in different colors. They were all in the same spot, but had suppress conditions so that only one would appear at a time. Trying to select a specific one of these objects is a challenge. But when you open the Report Explorer for that section, the objects are listed separately. You can select the object in the list of the Report Explorer and it behaves the same as when it is selected in design mode. You can also right-click on the object in the list and get all the same options you would get if you right-clicked the object in design mode.

2) Locating subreports
I recently had a very crowded report and was having trouble with a shared variable, that came from a subreport. The trouble was that the subreport was small and I was having trouble finding it. One of the features of the report explorer is that you can decide which of three object categories to have it show (Grids and Subreports / Fields / Graphic Objects). By turning off Fields and Graphic Objects the list showed only Grids (cross-tabs) and Subreports. This made the one lone subreport simple to find.

So if you haven’t ever used the Report Explorer, go into the View menu to activate it. You might find it useful.




Inflation table query for Oracle

Thursday 25 March 2021 @ 3:53 pm

I have written several times about using inflation tables to force duplicate data. I even posted some SQL queries that generate inflation tables in Microsoft SQL Server. Today I needed an inflation table for an Oracle based report and didn’t have one in my library. So I did a bit of research and found this one mentioned frequently. It worked well for my needs today:

SELECT ROWNUM FROM DUAL
CONNECT BY ROWNUM <= 100;

The “100” value can be replaced with whatever number you need.

So when do we use inflation tables? I use them whenever I need to turn a single record into multiple records. Here are the common uses I have seen:

  • Print multiple labels for a single row based on the quantity value in that row.
  • Splitting some orders into multiple records for shared commissions.
  • Creating a series of dates from a single date record.
  • Separating a multi-value field into separate single value records.
  • To repeat all records several times, grouped differently each time.

If you have a task like this and want some help, give me a call.




Max rows/columns of a cross-tab

Sunday 7 March 2021 @ 5:05 pm

I found a limit that exists in Crystal that I didn’t know about before. Even if I had known about this limit I wouldn’t have ever expected to exceed it.  But it just popped up in one of my reports.

I created a report related to donors for a large organization. One part of the report needed to show the top 10 donors from a very large pool of people. I decided to use a cross-tab with a “Group Sort” so we wouldn’t need another subreport.  It tested just fine on the sample data set.

But, when they ran the report on the full table they received an error saying a cross-tab couldn’t have more than 65K rows or 65K columns. Apparently, the data set included over 100K donors.  To find the top 10 donors, the cross-tab would have to initially create a row for every donor. Even though I only needed to see the top 10, the cross-tab needed to see ALL of them, and that exceeded the limit.

Instead I created a subreport and grouped by donor. Then used the “Group Sort” on the actual groups, rather than a cross-tab. Fortunately this limit doesn’t apply to groups in the report, only to groups (rows or columns) in cross-tabs.




Displaying the report processing time

Sunday 14 February 2021 @ 1:17 pm

One of my readers recently stumbled onto a trick and decided to share it with me. He wanted to display how long his reports took to run. He knew he could tap into the DataDate and DataTime to get the time the report started. His recent realization was that he could put a subreport in the report footer and get a second DataDate/Time at the end of the process. By comparing the two he would know the time it took for the report to run. By using DataDate and DataTime the values wouldn’t change, even if he saved the report with data and opened it a few days later.

The subreport can be completely blank other than this formula:

WhilePrintingRecords;
Shared DateTimeVar EndDT

Then in a separate report footer (below the section containing the subreport) we add this formula:

WhilePrintingRecords;
Shared DateTimeVar EndDT;

NumberVar TotalSec := DateDiff ('s', DataDate + DataTime, EndDT);

NumberVar H := Truncate(Remainder(TotalSec, 86400)/3600);
NumberVar M := Truncate(Remainder(TotalSec, 3600)/60);
NumberVar S := Remainder(TotalSec , 60) ;

Totext ( H, '0' ) + ':' +
Totext ( M,'00' ) + ':' +
Totext ( S,'00' )

The end result uses one of my web formulas to generate a string formatted as h:mm:ss, but there are other format options.

And thanks to Mark Edwards at DataReport Consulting for sharing his discovery.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server