Get distinct count subtotals to add up to the grand total

Tuesday 8 September 2020 @ 11:36 pm

I just had a customer ask me about their distinct count grand total and why it didn’t match the sum of the subtotals. This is common. When you add up distinct counts at the group level they often won’t add up to the grand total. This is because of what a distinct count is designed to do.

A distinct count summary will count how many different values there are in a column. So if I group patient visits by doctor and then want CR to tell me how many different patients each doctor saw in that period, I would use a distinct count at the doctor group level. And if I also wanted to know how many different patients were seen across ALL doctors I could create another distinct count of the patients and put it in the report footer. But it is very likely that this grand total is not going to match the sum of the doctor subtotals. This is because any patient who saw more than one doctor in the period will show up once in each doctor’s group, but then will only be counted only once in the grand total.

But there are special cases where the grand total needs to be the sum of the subtotals. If you find yourself with this requirement the solution is simple. You create a formula that concatenates the group value with the field used in the distinct count. (In the example above you would combine the doctor with the patient.) Then you do a distinct count of that formula. This will give you the same values at the group level, but it will prevent the grand total from combining values from different groups.

This technique is article #25 in my Expert Techniques volume 1.





Why would you “Perform grouping on server”?

Monday 31 August 2020 @ 9:34 pm

I was asked about this feature today and noticed that I had never mentioned it in my blog. It is a strange feature because you can turn it on in most reports and yet in most reports it will do absolutely nothing. But when you get it to work it can greatly improve the performance of the report.  You will find this option in the database menu.

So what does it do? It tells the database to do the grouping and subtotals. The database will only return one summary record for each group. Check the SQL statement and you will see a GROUP BY clause at the end.  This is useful when you have huge amounts of data to process and when you don’t need to show any detail level values.  But, this feature only works in reports that meets some very specific criteria.

Things you must do:

Group on a database field or a SQL expression (not a formula)
Hide or suppress the details
Make all of the visible fields either a group field or a summary field
Limit the summaries used to those supported by your Database
(Sum, count, minimum and maximum should be safe in all databases)

Things you must NOT do:

Use specified order grouping.
Add running totals using detail fields.
Add summaries using formulas fields.

If your report meets these criteria, Crystal should be able to add the GROUP BY to your SQL statement, get the server to group the records  and calculate all of the summary fields. Use the Show SQL option to confirm the GROUP BY appears.





How to remove the SAP BW toolbar in Crystal Reports 2020

Friday 21 August 2020 @ 10:13 pm

If you have installed the new Crystal Reports 2020 you may have noticed a new menu option and toolbar for SAP BW.

Gordon Portanier of ReCrystalize decided to do some experimenting to see if he could get CR to run without those options appearing. He found that when he renamed these 4 dll files, both the menu option and the toolbar didn’t appear :

crdb_bwmdx.dll
crdb_bwmdx_res_en.dll
BWQueryReportWrapper.exe
BWQueryReportWrapper_res_en.dll

He found the dlls in this folder:

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64

The program seemed to run normally without these dll files. So if you decide to upgrade and don’t have a use for the extra menu options, this is something you can try.





Converting String and Numeric Dates to DateValues (part 2)

Sunday 16 August 2020 @ 10:02 pm

Last week I wrote about using IsDate() and DateValue() to convert common date strings into true date values. This week I am using that same method to update some formulas on my formulas page. The advantage of this method over the original formulas is the extra validation provided by the IsDate() function.

But there are one or two extra steps needed to use DateValue() to replace the original logic in Formula #2 and Formula #3. The DateValue() function needs separators like dashes or slashes. When character strings have no separators you need to add them using the Picture() function. If the field is a numeric, you need to convert the field into a string first, and then use Picture() to add the separators.

If the field we are converting is a string with values like this: 20201231

Then the formula would look like this:

Local stringvar z := {Table.TextDate};
z := Picture (z, 'xxxx-xx-xx');
If IsDate (z)
then DateValue(z)

But if the value looks the same but is stored as an 8-digit numeric, then we need to add an extra step:

Local NumberVar y := {Table.NumericDate};
Local StringVar z := Totext (y,0,"");
z := Picture (z, 'xxxx-xx-xx');
If IsDate (z)
then DateValue(z)





Converting a character date to a true date

Monday 10 August 2020 @ 11:50 pm

I run into many situations where dates are stored as numbers or strings. These need to be converted to true date values before they can be used in date calculations. I have posted formulas on my site that convert common numeric and string values into true dates.

One of the challenges when converting dates is dealing with invalid values. These will generate an error when you attempt to convert them into dates. One way to validate a string input is with the IsDate() function. This checks and see if a string value can be converted into a date before doing the conversion. It is usually partnered with the DateValue() function which converts any valid date string into a true date value. The formula would look like this:

If IsDate ({String.Field})
then DateValue({String.Field})

I did some experimenting with this today and I was surprised at how many different patterns can be converted using these functions. The general rule is that the string needs 2 or 3 separate parts in one of these 5 patterns:

Month-Day-Year
Year-Month-Day
Day-Month-Year
Month-Day (assigns current year)
Month-Year (assigns day as 1)

The year can be 2 or 4 digits, except for Month-Year pattern which MUST have a 4 digit year.
(2 digit years >= 50 are assumed to be 19xx, while those < 50 are assumed to be 20xx)
The month can be a number, the full month name or the 3-character abbreviation.
The separators can be a slash, a dash, a space or a comma (or any combination).

This gives you over 400 different patterns that can be directly converted into a date. Here are just a few common string examples that will all return the date value 12/1/2020 once converted:

12/1/2020
December 1, 2020
2020-Dec-1
1-12-2020
Dec 2020
12/1

For my next post I am going to redo formulas #2 and formula #3 on my formulas page so they use this method.





RPT management utilities for 2020

Thursday 30 July 2020 @ 3:40 pm

I have just updated my comparison of RPT management utilities for 2020. 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





Objects highlighted, but only in design view

Saturday 25 July 2020 @ 6:16 pm

I like to highlight key objects in my reports. For instance I highlight small subreports that might be confused with regular fields, or that might not be noticed at all. I also highlight formulas that involve variables as a reminder that they shouldn’t be deleted or moved. (Usually a specific location is required for variables to work correctly.)  I will sometimes highlight several formulas with the same color so that I can quickly see which formulas work together. This is simple when the formulas are suppressed or when they are in a suppressed section, but sometimes the object is visible. In that case the object highlighting should only be visible in design mode and not in preview mode. Here’s how you do that.

Most Crystal formatting properties have a formula condition button [x+2] to the right of the normal controls (check mark, drop down, etc).  This allows you control that property based on parameter values or data values. When you use a condition formula there is no need to set that same property using the normal controls.  If you set a formatting property both ways the normal control setting will apply to design view while the formula will apply to preview.

So say I would like a field to have an aqua background in design view but no color in preview. I would first format that field by selecting aqua from the drop down. Then I would go into the condition formula and type: CrNoColor

The field will have an aqua background color in design view but no background color in preview.





Updating a database table from a Crystal Reports formula

Friday 17 July 2020 @ 8:44 pm

One of the things that makes CR such a powerful and flexible tool is the ‘ecosystem’ of third party tools that has grown up around Crystal Reports. I was reminded of this last week as I worked on a project using the Cut Light UFL by Millet Software. The Cut Light UFL allows your formulas to do more than simply add columns to a report. It includes 130 additional functions that let you interact with the operating system, other applications and even SQL databases (among other things).

In this case I was helping a customer create a report that reads data from one database, does some complex calculations and then writes the results of the calculations to a table in a data warehouse. The function we were using lets our formula launch a SQL INSERT statement to add a row to the table. We had done this before with smaller tables, but this time the INSERT involved a table with dozens of fields, and we ran into a limitation on the size of the query that Cut Light could process. I asked Millet software about the limitation and within 2 hours we had an updated UFL that could handle a SQL statement up to 250K characters.

If you want to learn more about giving superpowers to your formulas, you can see my annual review of User Function Libraries (UFLs). If you want to get an overview of ALL of the third party products available for Crystal Reports, you can see my product LINKS page.





Free lesson in Cross-tab grid functions

Wednesday 8 July 2020 @ 8:57 pm

I just got a note from Bruce Ferguson, the developer of the Crystal Kiwi line of viewers and schedulers. He sent me a chapter from his Crystal Reports course materials dealing with the the “new” grid functions available for cross-tabs. He said I could share it with my readers.

Bruce and I have been corresponding for nearly 20 years and we were both working with Crystal Reports for several years before that. So to us, a feature that has been around only 12 years or so counts as a new feature.

So, if you want to learn how to do Cross-tab calculations that reference other cells in the cross-tab, you can download the 8-page PDF and give the lessons and exercises a try.

And thanks, Bruce.





Crystal Dispatch site license for $5

Tuesday 30 June 2020 @ 12:03 am

I have written before about Crystal Dispatch, a client based viewer put out by APB Reports. It is a simple no frills viewer that covers all the basics and lets you run reports with one click. You can read more about how it works in my previous article, and even watch a video of how it works.

But what is new this year is the price. APB Reports is now offering an unlimited site license to a company for $5 (yes, five US dollars).  Adam Butt, the owner of APB Reports, said, “I want as many companies as possible to start using Crystal Reports, so hopefully this is a move in the right direction”.

So if you are thinking about deploying a locally installed viewer program in your company, this is one very low cost option to consider. For other options you can check out my annual comparison of client based viewers.





Next Posts »» «« Previous Posts
Jeff-Net
Recrystallize Pro

Crystal Reports Server