Archive for the 'Method' Category



Table filters in the database expert

Tuesday 8 October 2019 @ 9:06 pm

This week I worked with two different customers, helping them locate data buried in a large system with hundreds of tables. In both cases I had to use the table filter method to help me find the correct tables. For instance, when we needed to find the vendors table we looked first in the V section of the table list. However, in the system we were using all of the tables names had a 2 character prefixes for a dozen or more different modules. It wasn’t clear which module might contain this table. So I added a filter to the list of table names shown in the Database Expert. This way only table names that contain VEND somewhere in the name would be displayed. It made it easy to spot the correct table.
To add a filter to the table list you need to go into File > Options > (Database Tab).

In the middle section you will see two boxes on the right, and the top one is for table filters. It is labeled [Table Name LIKE:]

In this box you enter the characters you are looking for with a percent sign before the characters and/or after the characters. This percent sign is a wild card symbol. So since we only wanted to see tables that contained VEND anywhere in the name we put in a filter like this:

%VEND%

If I wanted all tables that have TMP on the end I could enter a filter with only one wildcard, like this:

%TMP

The only downside I have seen when using these filters, is that it is easy to forget about the filters. It then takes a bit to figure out why the table list is missing or incomplete. So don’t forget to take the filter out when you are done.




Suppress column headings when there are no details on the last page

Sunday 22 September 2019 @ 10:38 pm

Sometimes there are details printing on the last page and sometimes the last page is just the Group or Report Footer.  You may not want column headings on the last page if there are no details.  I have seen and tried several methods to accomplish this but not all are reliable. For instance using “OnLastRecord” as a suppress condition will work most of the time, but will also suppress the headers when the last page still contains exactly one detail record. I have found the approach below to be the most reliable.

First you split the Details band into A and B subsections.  You suppress Details B and use Details A for your visible fields.  Then you create the following formula field and place it in Details B:

WhilePrintingRecords;
BooleanVar Ending := OnLastRecord

Last you use the following formula to suppress the Page Header, or the part of the Page Header with column headings:

WhilePrintingRecords;
BooleanVar Ending

A Boolean variable is False by default.  The variable will only be True once the report has passed the last Detail A, which means that the last record has printed.  If there is a page header after that point it will be suppressed, because the “Ending” variable is now True.




How to turn records/rows into columns

Tuesday 27 August 2019 @ 7:32 pm

A customer recently requested an unusual report layout. They wanted the field labels in the first column on the page and each record to be a new column on that page. I remembered struggling with a similar layout request years ago and pulled up an old report from 2009. What I came up with then worked fine if you only needed one set of labels on each page, meaning that the columns were so tall that there wasn’t room to fit a second set below the first one each page. In 2009 I had half-height columns which meant two sets of labels along the left side of the page. That got very complicated.

Fortunately my current project had full page columns, which meant my 2009 solution was a perfect fit. Here are the steps:
1) Create a static value formula that can be used as a group. I usually use something like this:

WhileReadingRecords; 1

2) Group on this formula, and in the group options check “repeat Group Header on each page”
3) Make the group header about 6 inches deep and type all the field labels along the left side.
4) Make your details section about 6 inches deep and put the corresponding fields in.
5) Go into the section expert for the details section and check “Format with multiple columns”.
6) When the layout tab appears set the width of the column to an appropriate number.
7) Check the layout options: “Across then down” and “Format Groups with multiple columns”.

Note – you can do something similar with a cross-tab, but the additional pages for cross-tabs are all to the right on page 1 when in preview.  And the layout isn’t as flexible with a cross-tab.




Finding groups where the last record meets a criteria

Monday 15 July 2019 @ 9:52 pm

One of my students presented me with the following challenge. Their address records were stored in a table that keeps an address history. That means that new addresses don’t replace the old addresses. Each new address is a new record with a time stamp. The current address is the record for that customer that has the latest timestamp.

To display the current address for each customer is fairly easy and can be done in one of two ways:

1) Group by Customer and sort by time stamp. Hide the details and place the address fields in the Group Footer. This would display the last address for each Customer.

2) Group by Customer and put in a group selection formula that says:

{Address.TimeStamp} = maximum ( {Address.TimeStamp} , {Address.CustomerID} )

Either of these will work to show the current address for each customer. But if you want to select only current addresses in a particular state, like NY, you have to be careful. If you put the State criteria in the record select expert or record selection formula the criteria will be applied before the grouping happens. Crystal will start by selecting only New York records regardless of how old the timestamp is. Then it will do the grouping and show the last NY record in each group. You would end up with the last New York address for each customer, rather than getting the accounts that have New York in their last record.  Anyone who had moved of NY to somewhere else would still show up.

My original solution involved a formula that combined the Date and the State into a single string field. Then I used a complicated group selection formula to find the right records. You can read about it here and it works fine.

But today I realized there is a simpler approach. The key is putting the State rule into the group selection formula, so it is applied after the grouping is done. So your Group Selection would look like this:

{Address.TimeStamp} = maximum ( {Address.TimeStamp} , {Address.CustomerID} )
and {Address.State} = "NY"

As long as the last line stays in the group selection formula this will return the desired records.




Moving cross-tab numbers to Excel

Thursday 27 June 2019 @ 9:00 am

If you are trying to move cross-tab numbers into a spreadsheet, there is a short cut. You can simply copy and paste the entire cross-tab into your spreadsheet. Right-click in the upper left (empty) cell of the cross-tab and select “copy”.  Then switch to the spreadsheet and right-click in a cell and select “paste”. The cross-tab numbers should appear in the spreadsheet.

The only limitation is that the cross-tab has to fit on one page in Crystal. Fortunately, all recent versions of Crystal allow the page to be as large as needed. Go into “File > Page Setup” and check the option called:

“Disassociate Formatting page size with Printer Paper size. “

Then set the height and width to whatever you need to accommodate your cross-tab.

If you have an older version of CR (before CR 2008) the option above is missing. Instead you can use a PDF virtual printer like Cute PDF, and set a custom paper size so you have enough room.




How to reorder tables to improve performance

Monday 17 June 2019 @ 12:20 pm

So say you have four tables A, B, C and D. A joins to B, B joins to C and C joins to D. If all the tables are required in the results (i.e. you are using inner joins) you can theoretically use 4 different link configurations that should give you the exact same output. If you start with A or D the joins would be in a straight line (ABCD or DCBA).  If you start with B or C you would get a fork, like B to A and B to C with C linking to D. But even though the results will be the same, the performance could be dramatically different. So how do you decide which pattern is most efficient?

There isn’t a simple answer that works in every case, so testing is important. However, there are two places I look that often help: the indexed fields and the WHERE clause fields. You can often see the indexed fields in the linking window (colored tabs) or you can ask someone who knows the database what the indexes are on each table. To see the WHERE clause fields go to the database menu in Crystal and select “Show SQL Query”. The fields mentioned in the WHERE clause should match your record selection formula.  If they don’t you may need to tweak the formula so that the criteria can translate into SQL.

Indexed fields:
When linking you want your join to go TO indexed fields and ideally to ALL the fields in that index. So say Tables A and B are linked on two fields from each table. And say that these four fields all have red index tabs. But table B has a third field with a red tab and that field isn’t part of the join. That would mean you should link from B to A.  This uses the complete index in A which is the more efficient than linking to the partial index in B.

And don’t assume that because B is sitting on the left that the join starts at B and goes to A. I always hit the “auto-arrange” button in the links window to confirm the direction of the joins. After hitting “auto-arrange” all the joins flow from left to right. If a join is backwards, you can right-click that join and select “reverse join”, then click “auto-arrange” again to confirm the new direction.

Here are some other posts where I discuss the affects of linking on indexes:
https://kenhamady.com/cru/archives/2923
https://kenhamady.com/cru/archives/2653

WHERE clause fields:
Now lets also say that most of the WHERE clause criteria applies to the C table. I try to take the table with the most restrictive criteria and put it all the way to the left (or as far left as possible). That way they query starts out with the smallest data set possible and each subsequent join has fewer matches to find.

If the primary field in the WHERE clause is found in more than one table you get some flexibility. You can select the table that works best for indexing and then use the field from that table in the criteria.

So based on the above scenario I would recommend starting with table C. Then forking from C to both B and D, with a final link from B to A.

In some rare cases the indexed fields  and the WHERE clause fields can’t both be optimized at the same time because they point in opposite directions. When that happens you have to test different join patterns to see which works best.

One last note. In most reports the order of the joins is obvious from the link pattern.  But if you look at the SQL and the links aren’t in the order you want, you might have to use the “order links” feature of the database expert.

 




The last resort when you need an extra pass

Saturday 8 June 2019 @ 2:07 pm

I had a request this week that sounded relatively simple on the surface. The data was a list of people with from 1 to 10 characteristic rows. The wanted me to assemble all the characteristics for each person into a single alphabetized string, and then count how many people had each string combination. This meant that I had to Group [by person] and then Sort [by characteristic] then Group again[by the combined string]. That is one pass more than Crystal Reports can do.

My normal solution for this would be to do the first pass in the database using a SQL command. And I would have succeeded if the data had been in SQL Server or Oracle. The RowNumber () and Partition functions I wrote about recently would have been part of the solution. But, alas, the data was a classic MS Access MDB file.

After quite a bit of research I found a way to write a SQL command for MS Access that would do the job.  It worked in my test data, but it took hours to run on a normally sized sample of data.

So I offered the customer a relatively fast two-step approach, which is my last resort for getting an extra pass. This involves writing one report to do part of the work, then exporting the results to a spreadsheet, and finally creating a second report to create the final output from the spreadsheet data.

In this case the first report groups, sorts and assembles the string of characteristics for each person. This is exported to a spreadsheet as one column of data, with one row per person. Then the second report reads this spreadsheet and groups on that column and counts occurrences of each value. The process takes a few minutes.

One thing to note, this process is very simple if your export can use classic (XLS) spreadsheets. Crystal includes a native driver that can read tables in XLS files. But XLS files are limited to 64K rows.

The newer XLSX files can hold up to one million rows, and versions of Crystal since 2011 can export to XLSX files. But reading an XLSX file with Crystal requires that you have newer MS Office drivers that don’t come with Crystal Reports. To see if you have these drivers you can create a new OLEDB connection and look in the providers list for:

“Microsoft Office 12.0 Access Database Engine OLE DB Provider

If you don’t see this provider listed you can download and install the drivers.




Solving problems when reporting on CSV files

Monday 8 April 2019 @ 6:39 pm

You can create Crystal Reports that directly read CSV files using the Access/Excel(DAO) connection.  Just keep in mind that CSV files don’t always make an ideal data source. Like spreadsheets, CSV files don’t have set data types for each column. This can cause data type ambiguity which might cause you to lose some data. And in some cases the report will read the first record of the CSV file as the column headings, removing the first record of data from the dataset.

But here CSV files have one advantage over XLS files. CSV files allow you to introduce a Schema.ini file to define the data type for each column in the CSV. This is something you can’t do with spreadsheets. The schema.ini file is a simple text file that sits in the same folder as your CSV. There are many attributes available in schema.ini, but you only need to use the attributes that you need.  The other attributes will be set based on defaults stored in the registry.  Here are the most common problems I find that can be solved with a schema.ini file.

  1. The CSV is reading the first row of data as column headings
  2. Columns read as the wrong data type
  3. Character column is read as numeric and shows only the numeric values
  4. The columns are parsed using the wrong character

Here is an example of a schema.ini that defines two different CSV files in the same folder:

[sample1.csv]
Format=CSVDelimited
ColNameHeader=False

[sample2.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=OrderDate date
Col2=Amount long
Col3=CustID text
Col4=CustName text
Col5=CustCategory text

As you can see, a single INI file can define multiple CSV files when they are in the same folder. Each file gets it’s own section of the INI file. Both files are set to be read as comma delimited.  Both files are set to NOT treat the first row as column headings. In the first file we allow the driver to name the fields (usually A, B, C, etc) and determine the data type automatically. In the second file we name each column and assign each a data type.

So if you are reporting on a CSV file and running into issues, using a Schema.ini file may help solve the problem.




A simpler approach to address blocks

Thursday 14 March 2019 @ 9:33 pm

One of my favorite parts of writing this blog is when people read a post and then send me an alternate approach that teaches me something new.  Like today.

Last month I shared a formula for creating an address block that will automatically remove blank lines. Today one of my readers showed me how he does this with a text object. He uses a formatting property called “Suppress Embedded Field Blank Lines”.  I had never seen this option before so I quickly checked my version of Crystal. There it was in the formatting properties of text objects (not fields). I thought I might have missed this because it was a recent feature, so I started working backwards through the different Crystal versions to see when it appeared. I stopped when I found it in CRv8.5 which is nearly 20 years old. So much for missing a recent feature.

To use this feature you add a blank text object to your report. You then ’embed’ fields by dragging each field over the text until you see a hash mark. This indicates where the field will be embedded in the text, even in the middle of a sentence.  When the hash mark is in the right spot, you release the field and it becomes embedded into the text object at that point.

To create an address block you would add all the address fields into a text object and hit <Enter> between each one so that each field is on it’s own line. At first, any empty fields will create a blank line in the block. But if you go into Format > Text> [common tab], and check the property mentioned above, these blank lines go away automatically.

This may not work in every situation, but it is much simpler then the formula approach I posted last month.  And thanks to Duane Fenner, an Accounting Support Specialist at LTi Technology Solutions for sharing this with me.




Applying a formatting condition to multiple fields

Thursday 28 February 2019 @ 9:59 am

If I want to remove the decimals of several fields at once you can use CTRL-click or a cursor lasso to select all the fields, then go to the toolbar and hit the “reduce decimals’ button. Each click will remove one decimal from all of the selected fields.

You can do something similar when you want to apply a formatting condition formula. For example, if you want to turn negative numbers red while leaving positive numbers black. The font color condition formula looks like this:

if currentfieldvalue < 0
then CrRed
else CrBlack

To apply this formula to one field you select that field and then select the menu items “Format > Field”. On the “Font” tab you click the condition formula button next to font color. It will usually look like the top one of these buttons:

Once inside you paste in the formula above and then click “Save and Close. The formula button should turn red and look like the middle button above. This means the condition has a formula. When you click OK the negative values for that field will turn red.

Note that the formula doesn’t refer to a specific field, but to the function “CurrentFieldValue”. This function is only available when you do condition formulas and refers to the value of the field you are formatting. The advantage is that the same logic can be used on any numeric field and the condition will be exactly the same, rather than each field having to have a different formula that mentions a specific field.

If you want to apply this formula to several fields at once you could select that group of fields and then select the menu items “Format > Objects”. Like above, you go to the “Font” tab, click the [x+2] and paste in the formula. When you click “Save and Close, then then click OK all of those objects should have that property.

One thing to look out for when you are formatting multiple fields at once is a purple condition button (bottom example in the picture above). This only appears when you try to format multiple fields at once. This tells you that some/all of these fields already have a condition and that not all of them are the same. If you click a purple condition button it will show you a blank formula. If you put in a new formula you will overwrite any existing logic and all of the selected fields will end up with the new condition.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server