Archive for the 'Method' Category



SQL to create data dictionary reports

Tuesday 8 December 2020 @ 10:13 pm

I was recently trying to create a report and was having trouble finding a specific table. I knew the field name, but it wasn’t in any of the tables where I expected it to exist. Lets just say the table names in this database are cryptic.

Fortunately, most of the mainstream databases allow you to query the system tables to list all the tables and fields. I found some great SQL online and created a report to read the table structures and search for the field I needed. The query allowed me to create a searchable data dictionary report, or schema, for this database.

The SQL example I found is on a site that lists similar SQL for other databases as well:

This example is for MS SQL Server:
https://dataedo.com/kb/query/sql-server/list-table-columns-in-database

This example is for Oracle:
https://dataedo.com/kb/query/oracle/list-table-columns-in-database

This example is for MySQL:
https://dataedo.com/kb/query/mysql/list-table-columns-in-database

So if you want to generate some quick searchable documentation for databases in any of these formats you can use the links above.




Using a custom group name

Monday 23 November 2020 @ 11:12 pm

Whenever you add a group to a Crystal Report, Crystal creates and inserts a field called the “Group Name” as a heading for each group.  These values are also what you see in the group tree along the left of the screen. In most cases the group name simply shows the values of the group field, but there exceptions. For instance, when you group on a date field you can choose to group “for each month” or “for each year”. The group name would display only the month or year from the field. If you do a TopN or use Specified Order you may get a final group called “Others”.

Crystal also gives you the ability to customize the group name. You can either pick a different database field to display as the group name, or you can write a formula to be the group name. For instance you might group by a Customer ID, but choose the Customer Name field as the group name. Or you might group by Employee ID, but create a formula that combines First Name and Last Name to be the Group Name.

To make this happen you go into Group Options (Report > Group Expert, Options button then Options tab) and check “Customize Group Name Field”.  You will be given the choice of selecting another database field or creating a formula expression using the X+2 condition button.

Here’s a tip – when I want to use a formula expression for a group name, I typically write the formula as a separate formula field. Once that is saved I will go into the group options and put my formula field into the condition formula. If the logic ever needs to be changed it is simpler to modify a formula field than it is to get into the expression editor within the Group Options.

Note that you can also customize group names in cross-tabs, using the “Group Options” button for either the row or column fields. The interface is the same.




Running totals vs regular summary fields

Monday 16 November 2020 @ 11:48 pm

There are several ways to create totals in Crystal. This week I solved problems for two different customers by changing the type of total they were using. I will give a short explanation here, but if you want to really understand this topic you should download the Expert’s Guide to Totals in Crystal Reports from my website ($12).  It comes with example reports and exercises.

The primary method for creating totals in Crystal is to add summary fields. In the menu use Insert > Summary or find the Sigma symbol on the tool bar. Some users default to using running totals because they see them listed in the Field Explorer. But summary fields have several advantages over running totals. Summary fields can be:

1) placed in the Group Header as well as the Group Footer
2) used in the Group Selection formula as a group level filter
3) used in Group Sorting to rank the groups in order based on a subtotal value
4) Copied to other group/report sections to create additional summaries

You can’t do these with running totals, so my first choice for creating any total is to use a summary field.

But there are specific situations when a running total will solve a problem that you can’t solve with a regular summary field:

1) If you want to watch the value change, row by row, like the balance in a checkbook
2) If the column you are totaling has duplicates and you need to skip the duplicates
3) If you use Group Selection and then want a total of just the groups that meet the criteria
4) If you do a TopN without others and need a total that doesn’t include the others

Some users also use running totals because you can apply a condition directly to the total. This way the total only includes records that meet a specific criteria. But if you write an If-Then formula with your condition you can use a regular summary field and get the same result. This gives you the conditional total without giving up all the advantages of summary fields.

And, if you get really stuck on a total issue, you can call me for a short consult.




One more note about Group Selection

Tuesday 10 November 2020 @ 11:16 pm

My last post talked about Group Selection and showed off some quirks. For instance adding a Group Selection criteria might cut your report in half – but the record count stays the same. So I was curios to find out how the functions Previous() and Next() are affected by Group Selection.

First, what do these functions do? Normally, when you refer to a field in a formula the values for that field will come from the current record. These two functions allow you to refer to the record before or after the current record. So, if I wanted to know the number of days between one order and the next I could write the following formula:

{Orders.Order Date} - Previous ({Orders.Order Date})

This would tell me the number of days between the two dates. But what if you are using Group Selection. Do these functions use all the records from Record Selection, or just the ones that meet the Group Selection criteria?

Say I wanted to limit a report to the records that were above average. I could calculate the average order amount for the entire report then use that total in the group selection formula like this:

{Orders.Order Amount} > Average ({Orders.Order Amount})

Adding this rule would cause about half of the orders to be eliminated from the report. But as we mentioned above those records are still in memory. They need to be because they are used to determine the overall average. So what happens if you write a formula like the one above that refers to the previous order date? Will it see all records, like the record count does, or only ones that meet the Group Selection criteria?

My gut told me that this formula would include records that didn’t meet the Group Selection criteria, but I was surprised. The Previous() and Next() functions are apparently evaluated AFTER Group Selection. So the formula above will always pull values from the records that meet the Group Selection Criteria.




Quirks of Group Selection

Saturday 31 October 2020 @ 7:48 pm

When you add a filter to most reports you create a rule that applies to each record and determines if that record qualifies or not. Examples would be:

{table.state} = 'PA'  and  {table.Amount} > 50

This is called record selection and is stored in the Record Selection Formula. You might enter the rules using the Select Expert, but Crystal will convert those rules into the Record Selection Formula.

But sometimes you need to add criteria that applies to an entire group based on a group subtotal. For instance you might want to limit the report to states where the total amount for the state is over $5,000. This is called group selection and these rules are stored in the Group Selection Formula. The rule I just mentioned would look like this:

Sum ( {table.Amount} , {table.state}) > 5000

When the subtotal already exists on the report you can create the rule using the the select expert. You would choose the “Group” option instead of the “Record” option of the select expert. Crystal will add the rule to the Group Selection Formula.  Any type of summary operation can be used for Group Selection (Sum, Count, Average, Minimum, Maximum, etc).

Just keep in mind that using Group Selection comes adds a few quirks to your report.

1) Adding a group selection rule will shorten the report but the record count (shown in the status bar) won’t change. That is because the record count is determined by Record Selection before Crystal applies the Group Selection Formula.

2) Groups that don’t meet the Group Selection rules are eliminated from the report but will still show up in the group tree. The group tree is created before the Group Selection Formula is applied. So clicking some values in the group tree will not take you to that group.

3) Grand total summary fields will not be reduced by Group Selection. The Grand totals are created before Group Selection. This issue can be solved by re-creating your grand totals as Running Total Fields rather than as regular summary fields. Running Total Fields are evaluated AFTER Group Selection so they will be lower than the original grand totals.




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.




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.




Fun with looping logic

Wednesday 17 June 2020 @ 11:59 pm

I had a fun challenge today. A customer had a table with an odd structure. One column was a payment amount. It was followed by 40 different columns for various fees. He wanted the payment amount to be applied to the fees from smallest amount to largest amount until the payment was used up. The report was to show the balance for each fee after the payment was applied.

Normally this type of data would be vertical. There would be a separate row for each fee and only 2 columns (fee name and the fee amount). With that structure you could sort by fee amount and use a variables to apply the payment to the records in ascending order. But with 40 fixed columns I had to load the values into an array, and then put the values in the right order.

I loaded the fees into the array as strings, combining the amounts and fee names into one element looked this:

24.50=xyzFee

This allowed the fee name to follow the amount through the sorting process.

Once I loaded all these strings into the array I used my bubble sort formula to re-sort the array from lowest amount to highest amount. In the bubble sort comparison I used the Val() function to convert the strings to numbers. This put them in order by their true numeric value.

Then I wrote a second loop to apply the payment. It steps through the array, which is now in the right order, and applies the payment to the fees, one at a time. Each fee amount is reduced to zero while the payment value is reduced by the amount of each fee.  If  the payment’s remaining value drops below the amount of the next fee the remainder is subtracted from that fee and the payment is reduced to zero. Any fee amounts in the array beyond that point stay the same.

Last, to display the results, I wrote 40 separate formulas, one for each fee. Each of these formulas loops through the array looking for its specific fee description. When it finds the element with a matching description it uses the Val() function to convert that string to a numeric value and displays that value.  This is the relatively simple loop formula:

EvaluateAfter({@Build Fees Array});
stringVar Array Fees;
Local numberVar i;
Local numberVar fee;
FOR i:=1 to ubound(Fees)
DO (
if 'xyzFee' in Fees[i]
then Fee := val(Fees[i])
);
Fee

Not many people would think this process was fun, but I did enjoy mapping out a creative approach to a unique requirement. And it was less than 2 hours from the requirements to a validated report. The next time your report requirements are a bit unorthodox, keep  me in mind.




Giving the database what it needs

Monday 8 June 2020 @ 8:35 pm

Today a user asked me how to automatically select the prior month’s transactions from the database. The transactions didn’t have dates, but instead had two numeric fields for Period (month) and Year. They were using a formula to convert these two numbers into a date and then using that formula as part of the selection formula. That approach will work but it is usually slow. This is because the database (or the SQL Query) doesn’t know anything about the date formula in the report, so it can’t apply any date criteria before sending back the data.  Crystal gets all years and periods and then has to apply the criteria in the report.

A more efficient approach is to start at the other end. Instead of converting the DB fields to match the criteria values, we convert the criteria values to match the the database fields and let the database do the heavy lifting. In this case created three formulas:

// Prior Pd End Date
Maximum (LastFullMonth)

This gives us the last date of the prior month. From there I created two formulas that extract the year and the month from that date:

// Prior Pd Month
Month ({@Prior Pd End Date})

// Prior Pd Year
Year ({@Prior Pd End Date})

Then the selection formula can use these values to filter the DB fields, something like this:

….
and {Trans.Year} = {@Prior Pd Year}
and {Trans.Period} = {@Prior Pd Month}

This should allow the criteria to be passed to the WHERE clause of the SQL, and maybe even hit an index to make short work of finding the correct records.

The principle works the same if you are using a Date Range parameter.  You could use formulas to convert the parameter values so they match the database fields.  I wrote about this a few years ago in a similar case where the dates were stored as strings.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server