The Crystal Reports® Underground

I resolved another minor mystery recently after a question from a customer. It has to do with the behavior of variables whenever you open a new table by doing a drill-down on a group. The same thing can happen when you double click on a subreport in preview mode to open the preview of that subreport.

What I had seen was that some variables would carry to the new tab others would reset to zero/blank. So when a customer explained that this was causing a problem in their report I did some digging and experimenting. I discovered that this problem only occurs with shared variables, which pass to and from subreports. Shared variables will not carry from the preview into the new tab, while default (global) variables carry just fine.

I did some more digging to find a workaround and only found a partial one. I say partial because it will work for group drill-downs but not subreport drill-downs.  If you drill down on a group, typically you will see the Group Header, the Group Footer and the details for that group. The workaround is to make a second copy of the subreport that generates the shared variable, and put it into the group header of that group. To make sure that subreport doesn’t run repeatedly you can either hide the group header, or suppress the subreport with a condition like this one:

DrillDownGroupLevel = 0

Either of these will keep the extra subreport from firing until you do the drill-down. Then one instance of the subreport will run inside the drill-down and the shared variable will carry to the details and group footer inside that drill-down tab.

The downsides are

1) The subreport will have to run again if you do another a drill-down.

2) It won’t work for a subreport because you can’t put one subreport inside another.

If you have any questions about using variables, call to schedule a session.

I was recently reminded of how handy it can be to create a “NULL” value in a formula. I had to use this in two recent situations and thought a description of these two uses would make a great blog post. But, as happens so often, I looked at my past posts and found I had written about both of these before, one in 2016 and one in 2007. So rather than write them up again I am just going to link to those posts with some brief comments.

1) If you ever need to do a distinct count of values that meet a condition. This usually means an IF THEN ELSE formula and if you don’t use a NULL as your ‘ELSE’ your distinct count is likely to be inflated by one.

2) If you want to do a minimum or maximum of values that met a condition. Again you would need an IF THEN ELSE formula and the simplest approach I have found is to use a NULL as your ‘ELSE’ value.

Note 1: Don’t think that skipping the ELSE altogether will generate a NULL value.  It will generate a default value (empty string, zero, etc.).

Note 2:  These NULL formulas are strings, so if you need an else that is a NULL numeric you can use:

Val({@Null})

If your else value needs to be a date you can use:

Date (Val({@Null}),1,1 )

These will still be null values, but they will have the correct data type for your formula.

I have been writing lots of SQL commands for Crystal Reports recently. One of them required me to link a sub query to itself, repeatedly, to get multiple levels. Fortunately I had recently read about Common Table Expressions (CTEs) which simplify SQL commands in cases like this.
A CTE allows you to write a query, give it a name, and then refer to it throughout your SQL command as if it were a table. You would start your command with something like this:

with trans as (
select Item, Date, Amount, CustName
from Items
inner join customers on Items.CustID = Customers.CustID
Where Items.Type = 3)

You put your subquery in the parens and Trans becomes the name of the results.
After that you start your SELECT and use Trans as if it were a table with those 4 columns.

This is especially helpful when you have to use the same results multiple times, because you don’t have to repeat the SQL like you normally would with a normal subquery. If you have to change that subquery you can make the change in one place, so the SQL is more efficient to write and read.

While CTEs make writing more efficient, they don’t make the SQL processing any more efficient. Each time you reference a CTE in your SQL it has to repeat the CTE query to get results. If the subquery is complex you want to have it processed only once and then be able to refer to the results several times without having the database repeat the query. For that you switch from a CTE to a Temp table.

With a temp table the results of the query are written to an actual table (in a tempDB). Temp tables are deleted when you are done with your DB session. And because temp tables are written to tempDB you don’t even need to have “write” permissions for the main database. You can even add an index to the temp table if it is large enough to need one.

With a temp table your SQL command can read that table as many times as necessary without having to generate the data again. This can make a significant difference in the processing time of a query, especially when you have to refer to those results multiple times. It is a good idea to start the SQL by checking to see if the temp table already exists and deleting it. Otherwise you will get an error when you try to create it.

Below is an example of a command that shows how to do the CTE above as a temp table. First it drops the temp table and then it recreates it:

if Object_ID (N'tempdb..#TempItems') IS NOT NULL
BEGIN
DROP TABLE #TempItems
END;

select Items.Item, Items.Date, Items.Amount, customers.CustName
Into #TempItems
from Items Items
inner join customers customers on Items.CustID = Customers.CustID
Where Items.Type = 3 ;

If you start your command with the SQL above you can follow that with a SELECT that references the table #TempItems (all temp tables start with #). You treat a temp table just like any other table. Note that there are semicolons after each step which we didn’t need with the CTE.

If you think your reports might benefit from these techniques, call to schedule a session.

This week I had to do something new in SQL.  I needed to take a list of multiple route records for each driver and append the routes into single string for that driver.  This is relatively simple in a Crystal report, but not so simple in a SQL Query.  After I realized how complex this would be in a normal SQL query I searched to see if there was a simpler way.  What I found was that several databases have a function specifically designed for this task.

In SQL Server and Postgre SQL it is called String_Agg().
In Oracle and DB2 it is called ListAgg().
In MySQL it is called Group_Concat().

Even though SQL is considered a “standard” language, some function names and syntax will vary from one database to another. That is why I created a “cheat sheet” a few years ago that lists the most common SQL functions (with their correct syntax) for the 8 SQL flavors I encounter the most:

• • • SQL Server
    • MySQL
    • Oracle
    • MS Access
    • Pervasive
    • Progress
    • PostgeSQL
    • DB2

Today I added these “aggregate” functions to the cheat sheet along with a few other updates.  The cheat sheet is now also available on the library page of my web site.

My customers often use large, high-resolution monitors.  Sometimes the icons and menu options are microscopic – even to the local user. It is even worse for me since I am seeing their screen through GoToMeeting which reduces the screen even further.

But recently, one of my colleagues pointed me to a video that might help. It shows how to change a compatibility setting specific to high-resolution monitors.

If you are seeing microscopic icons on your Crystal toolbar, check out the video and see if it helps. And thanks to Gordon Portanier with the Crystalize consulting group in Canada for sharing this tip.  And also to Mike Pieczynski of OnPoint Consulting for sharing the video.

If you want to change the properties of a section, you go into the Section Expert(“Report” menu). You can also get to the “Section Expert” by right-clicking on a section name and selecting the option “Section Expert” from the “Flyout” menu. This menu also gives you a few properties you can change for a section without going into the Section Expert, like hiding, suppressing or deleting a section.

But I’ve noticed something strange in recent versions of CR. This flyout menu doesn’t always respond to user choices. I have noticed it both in my own environment and also when working remotely with several customers. It can be annoying to repeatedly right-click on a section name and have your choice ignored. One customer just stopped using the flyout menu altogether and just started navigating the main menu to open the Section Expert.

Now I think I have figured out a pattern for this behavior. If I right-click on a section quickly, without moving the mouse at all, the flyout menu will appear but it won’t respond to my choice. If, however, I right-click on a section and hold the cursor while dragging down a bit before I (left) click on my choice,  then the menu will respond to my choice.

I am guessing this is a bug, since I have not seen this problem with any other flyout menu in Crystal.

Crystal Reports is a great tool, but it isn’t perfect. One of my long standing complaints is how hard it can be to grab the sizing handles on an object using the mouse. The active zone around the sizing handle is tiny, so you have to be very precise or you end up dragging the object around instead of sizing it. You would think that zooming in would make the active zone bigger, but it doesn’t.

So I was very happy when I discovered, years ago, that you could size objects with the keyboard. If you hold down the shift key and hit the left/right arrow keys you can make the object’s right side move in and out. If you hold down the shift key and hit the up/down arrow keys you can make the objects bottom edge move up and down. If instead you hold the CTRL key while you hit the arrow keys, the entire object moves in one of the 4 directions but the size doesn’t change.

But soon after I started using Shift-Arrow I found that it didn’t work half the time. It was just this past week when I figured out the two situations where you can’t use Shift -Arrow to size objects:

1) When you are in Preview mode. For some reason using Shift-Arrow only works in Design mode. This is odd because using CTRL-Arrow to move objects can be done in both design and preview. That may be a bug but I found it true in all the versions I tested.

2) When the edge of the object snaps to a guideline. Once an object is ‘snapped’ to a vertical or horizontal guideline, the snapped edge can’t be moved using Shift-Arrow.  But following the pattern mentioned above, guidelines snapped to the top or left side do not prevent using CTRL-Arrow to move objects.

I was working with a customer’s report on my PC and trying to change a parameter. Instead of showing me a list of values I was presented with an unusual dialogue, looking for credentials. This dialogue didn’t look familiar and it took me a few minutes to figure out why it was there. The parameter I was using was a dynamic parameter and it was trying to pull a list of values from the database. That database didn’t exist on my PC, of course, so this is the window I saw:

As you can see it doesn’t allow you to change the name of the server or the DB. It does allow you to log in but that won’t help because I suspect you will only see this when the DB doesn’t exist. To get by this you have cancel and then change the dynamic parameter to pull form a local source.

So if you see a strange login that has “Enter Values” at the top and “Use Single Signon Key” at the bottom, check to see if your dynamic parameters can find their data.