Archive for the 'Method' Category



Inflation table query for Oracle

Thursday 25 March 2021 @ 3:53 pm

I have written several times about using inflation tables to force duplicate data. I even posted some SQL queries that generate inflation tables in Microsoft SQL Server. Today I needed an inflation table for an Oracle based report and didn’t have one in my library. So I did a bit of research and found this one mentioned frequently. It worked well for my needs today:

SELECT ROWNUM FROM DUAL
CONNECT BY ROWNUM <= 100;

The “100” value can be replaced with whatever number you need.

So when do we use inflation tables? I use them whenever I need to turn a single record into multiple records. Here are the common uses I have seen:

  • Print multiple labels for a single row based on the quantity value in that row.
  • Splitting some orders into multiple records for shared commissions.
  • Creating a series of dates from a single date record.
  • Separating a multi-value field into separate single value records.
  • To repeat all records several times, grouped differently each time.

If you have a task like this and want some help, give me a call.




Max rows/columns of a cross-tab

Sunday 7 March 2021 @ 5:05 pm

I found a limit that exists in Crystal that I didn’t know about before. Even if I had known about this limit I wouldn’t have ever expected to exceed it.  But it just popped up in one of my reports.

I created a report related to donors for a large organization. One part of the report needed to show the top 10 donors from a very large pool of people. I decided to use a cross-tab with a “Group Sort” so we wouldn’t need another subreport.  It tested just fine on the sample data set.

But, when they ran the report on the full table they received an error saying a cross-tab couldn’t have more than 65K rows or 65K columns. Apparently, the data set included over 100K donors.  To find the top 10 donors, the cross-tab would have to initially create a row for every donor. Even though I only needed to see the top 10, the cross-tab needed to see ALL of them, and that exceeded the limit.

Instead I created a subreport and grouped by donor. Then used the “Group Sort” on the actual groups, rather than a cross-tab. Fortunately this limit doesn’t apply to groups in the report, only to groups (rows or columns) in cross-tabs.




Displaying the report processing time

Sunday 14 February 2021 @ 1:17 pm

One of my readers recently stumbled onto a trick and decided to share it with me. He wanted to display how long his reports took to run. He knew he could tap into the DataDate and DataTime to get the time the report started. His recent realization was that he could put a subreport in the report footer and get a second DataDate/Time at the end of the process. By comparing the two he would know the time it took for the report to run. By using DataDate and DataTime the values wouldn’t change, even if he saved the report with data and opened it a few days later.

The subreport can be completely blank other than this formula:

WhilePrintingRecords;
Shared DateTimeVar EndDT

Then in a separate report footer (below the section containing the subreport) we add this formula:

WhilePrintingRecords;
Shared DateTimeVar EndDT;

NumberVar TotalSec := DateDiff ('s', DataDate + DataTime, EndDT);

NumberVar H := Truncate(Remainder(TotalSec, 86400)/3600);
NumberVar M := Truncate(Remainder(TotalSec, 3600)/60);
NumberVar S := Remainder(TotalSec , 60) ;

Totext ( H, '0' ) + ':' +
Totext ( M,'00' ) + ':' +
Totext ( S,'00' )

The end result uses one of my web formulas to generate a string formatted as h:mm:ss, but there are other format options.

And thanks to Mark Edwards at DataReport Consulting for sharing his discovery.




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.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server