Archive for the 'Method' Category



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.




Setting section height to a specific number

Monday 31 December 2018 @ 12:26 am

The section height in Crystal is an analog setting, not a digital setting. In other words you can’t go into the section expert and set the height of a section to exactly 1.25 inches. You have to go by the ruler on the side and make the adjustments visually. But if you know the secret, there is a way to force the section to be exactly the size you want. It relies on the fact that field objects have both a size setting and a position setting that you can set digitally. Here are the steps:

1) Make the section smaller than your target size.
2) Place an extra field near the top of that section.
3) Right click on the field and select “Object Size and Position”.
4) Set the “Y” property for this field to zero which puts the field at the top of the section.
5) Set the height property for this field to the desired height for the section.
6) Click ‘OK’
7) Delete the field.

When you click OK, the object will grow vertically to the desired height, forcing the section to grow. And, since the object is starting at position zero the section will be exactly the same height as the object. This makes it easy to create many sections that are all exactly the same height.




Better SQL for showing the last record

Monday 24 December 2018 @ 6:25 pm

I recently wrote about the SQL to return the last record in SQL Server. Adam Butt of APB reports wrote to remind me that if you are using in SQL Server or Oracle you can do this more efficiently using a RowNumber “Over” a Partition. He also gave me an example which has helped me understand the pieces that go into this method. There are three components:
RowNumber() will number all the rows in your results, like a Crystal running total. However, it won’t work unless you give it an OVER clause to put the records in order. The OVER clause is just an “ORDER BY clause put in parentheses right after the word OVER. This statement would number all the records in your results from 1 to n:

ROW_NUMBER() OVER( ORDER BY Order.OrdDate DESC) AS RowNum

You add one more piece when you want the Row Number to start over with each group, the “Partition By”. Partition By is similar to Group By but happens inside the OVER clause. This statement would number the records in your results from 1 to n within each Customer:

ROW_NUMBER()
OVER( PARTITION BY Customer.Name ORDER BY Order.OrdDate DESC) AS RowNum

Notice that in both cases the ORDER BY is “DESC” or descending which means the latest record for each customer will be first or Row_Number 1. We can use that in the SQL below to make sure we only see the last record for each customer.

SELECT * FROM ( SELECT Order.OrdDate, Orders.OrderNo, Orders.Amount, Customer.Name,ROW_NUMBER() OVER(PARTITION BY Customer.Name ORDER BY Order.OrdDate DESC) AS RowNum FROM Orders Orders
INNER JOIN Customer Customer ON Orders.CustomerID=Customer.ID
) X
WHERE
X.RowNum= 1

By changing the final WHERE clause you can also do things like select the last 3 orders for each customer:

X.RowNum <= 3

And the OVER() can also be used without the row_number() to create running totals in your SQL results. For instance this expression would create a running sum of order amounts for each customer:

sum(orders.amount) OVER( PARTITION BY Customer.Name
ORDER BY Order.OrdDate DESC) AS RunningAmt




Using an asterisk for ALL in a parameter

Monday 17 December 2018 @ 11:43 pm

I recently wrote about using ALL when working with parameter fields. After my newsletter went out I got a comment from Bruce Ferguson of Crystal Kiwi in New Zealand (thanks Bruce!). He mentioned that he prefers to use an asterisk instead of the word ALL. This sorts to the top of the list automatically, and us old-timers are used to thinking of the asterisk as a wildcard.  It also doesn’t get confused with similar values, like the state abbreviation “AL”

This reminded me of a lesson from my advanced class. We create a string parameter and write a selection formula like this:

{Customer.PostalCode} like {?Zip}

This can be used three ways by the user.

1) If they enter the full code like 20176 they will get exact matches.
2) They could also do a partial match like 601*. This would give them all postal codes that started with 601. They could enter any number of characters to get a broader or narrower group.
3) If they enter just an asterisk, they would get ALL values, with one exception. They would get records that have a blank postal code, but they would NOT get records that have a NULL postal code.

So, if you want to use LIKE and still have the asterisk return all values, including NULL values, you would need to do something like this:

if {?zip} = "*"
then True
else {Customer.PostalCode} like {?Zip}




SQL to return the last record (SQL Server)

Friday 7 December 2018 @ 10:23 pm

A few years ago I wrote a post that showed a more efficient way to find a ‘last’ record in a SQL command, while pulling several values from that record.

The example I used then was written in the SQL Syntax for MS Access, but the same method can be used in other flavors of SQL. The main difference is the syntax for converting the date to a string. Since I just had to look up that syntax for MS SQL Server I decided to post this here so I can find it easily next time.  Here is a brief recap of what it does:

In SQL it is very easy to add a GROUP BY clause to a query and then calculate the Min() or Max() date for that group. For instance the last order date for a customer. What is not so simple is to return other fields from each last order record, like the Order Number and Amount. The typical solution is to find the maximum in initial query, and then link that to another query that matches that Max() value back to the matching record as an inner join. But this requires at least to queries, and can get complicated if the GROUP BY field and the Max() field are in separate tables.

I find that it is simpler and more efficient to do the Max() on a combined expression that includes all the fields you want. This works as long as you start the expression with the date converted to a string in “yyyymmdd” format. This format ensures that the Max() of the combined expression will be the date and values from the last record.

Take a simple example. We need to write SQL to return each customer’s last order. And from that order you also want to return the customer name, order date, order number and amount. The SQL would look like this:

SELECT
Customer.ID,
max(
CONVERT(varchar(16), Order.OrdDate, 121) + '*' +
Orders.OrderNo + '*' +
Orders.Amount + '*' +
Customer.Name
) as MaxSTring
FROM Orders Orders INNER JOIN Customer Customer ON Orders.CustomerID=Customer.ID
WHERE (Orders.OrdDate>={ts '2018-11-01 00:00:00'} AND Orders.OrdDate<{ts '2018-12-01 00:00:00'})
GROUP BY Customer.ID

This returns one row per customer and the row might look something like this:

20180630*12345*250.87*Joe Smith

You can then write a formula in Crystal to split the string back into pieces. For instance the Customer Name formula would look like this:

Split ({Command.MaxString}, '*') [4]

In English this says, split the MaxString into a list (array) based on the asterisks. Take the 4th element.

Some of you might be wondering why we don’t just do this in the report.  You have a point since this is simple in Crystal:

Group by customer, sort by date, hide the Details and Group Header. Show all the fields on the Group Footer.

This works great, but is not very efficient if you have a large volume of data, because it requires Crystal to read all the details and display the last one. The SQL version above would only return one record per customer to the report.




Selecting ALL with a parameter field

Sunday 25 November 2018 @ 9:52 am

The most common use for parameter fields is in the selection formula, where they are used to filter the records. So if I have a parameter called {?State}, I can use it in the selection formula like this:

{Customer.State} = {?State}

But what if the user wants to be able to select ALL states? If the user enters the word “ALL” in the State parameter with the selection formula above, the query would look for records with a state value of “ALL”, and likely wouldn’t find any. But you can modify the formula to read:

( if {?State} = “ALL”
then True
else {Customer.State} = {?State} )

With this selection formula, the user can enter the word “ALL” and will get ALL states (including those that are blank and null).
Note that this rule is contained in a pair of parens. Those are only strictly necessary when there are other rules in the selection formula but it is safer to add them so they aren’t forgotten. Without the parens a rule that follows the logic above will be considered part of the else and won’t be applied all the time.

One other note.  This works for string fields.  If your field is a numeric or date your options are to:

  1. Make the parameter a string so you can enter “ALL” and then convert entered values in the selection formula to the correct data type.
  2. Use a unique number (e.g. 99999)  or a unique date (e.g. 1/1/2001) as your “All” value.
  3. Make the parameter an optional parameter and then have the blank value represent “All”.



Setting default export options for a report

Wednesday 7 November 2018 @ 6:10 pm

All versions of Crystal Reports since 2005 allow you to save default export options for each report. These options are stored in each RPT file so they only apply to the report that is open when they are saved. Once these options are saved the user only has to remember which export format to use. They no longer have to remember all of the specific export options.  The saved options will be shown by default.

Here are the steps to save export options for a report:

1) Open the report
2) Go to File > Export > Report Export Options
3) Select a format and click “OK” (note – only formats that have options are listed)
4) Set the desired options and click OK
5) Save the report

The next time you export that report into that format, the options screen will already be filled in for you with the defaults that you saved. You are allowed to overwrite them at runtime, but overwriting them does not change the stored defaults. To change the stored defaults you repeat the steps above.

Also, one report can store export options for several different export formats at the same time. So if you have a report that is exported several different ways, you could store default settings for all 10 of the export formats that have an options dialogue.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server