The Crystal Reports Underground News - Volume 2019.01
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for January 2019:
** Web based deployment options compared (2019)
** Connecting to QuickBooks data from Crystal Reports
** My library of Crystal Reports materials
** Setting section height to a specific number
** Using an asterisk for ALL in a parameter
** Let me create your Crystal Reports
** Downloading the Xtreme sample data
** Minor changes can have a major impact on performance
** Individual Training with an expert.
** Better SQL for showing the last record
** Even Better SQL for showing the last record
Gems from the Archives
Using AutoComplete in Crystal Reports formulas (January 2009)
Ranking based on a ratio (January 2009)
Web based deployment options compared (2019)
There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output. But there are environments where a web based option is necessary. The “official” options from SAP are Crystal (Reports) Server and BO Enterprise. But there are other, less expensive products out there that also web delivery of Crystal Reports. These third party products allow your users to run and view reports from a browser. You can also centrally manage your report deployment from a browser.
I have created a page on my blog that lists and compares these products, and I update it every January. This year the list features 10 products:
Crystal Reports Server – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
IntelliFront BI – a traditional Web portal
Ripplestone – a traditional Web portal
rePORTAL CR – a traditional Web portal
ReCrystallize Server – a traditional Web portal
ReCrystallize Pro – a launch page generator for the web
Bezlio – a SaaS Web viewer
Report Launch – a bridge between BO server products and server based applications
RapidStack – Web Portal service built around Business Objects Enterprise
The blog page mentioned above contains a brief rundown on what each product does and provides links to all of the product web sites. I have also posted a feature matrix (xls) that shows some of the specifics for comparison, including prices.
If you have any feedback to share on these tools I would be happy to hear from you.
Connecting to QuickBooks data from Crystal Reports
QuickBooks is a popular accounting package and it is used by several of my customers. But the QB database is not an open database. This means that connecting Crystal Reports to QB data is not simple or free. Here are the main products that allow Crystal Reports to connect to a local or online install of QuickBooks.
QODBC by FlexQuarters is an ODBC driver. It reads the data from the QB database in real time. It works, but I have found that is often slow depending on the tables used. The price ranges from $149 for a single user to $499 for a server license. I have written about QODBC before.
QODBC for QB online by FlexQuarters is similar to the ODBC driver above, but it allows you to connect to QB Online data. It is priced by month, $14.95 for a single user.
QuickBooks Online ODBC Driver by CData. Connects directly to QB Online data. This driver costs $299 per year, which covers a single workstation.
QQube by Clearify extracts a “data warehouse” copy of the QB data. You run your reports against the extracted data which is organized into logical components. This approach makes for fast running reports, but the data is only as up to date as the last extract. The price ranges from $450 for a single user to $1,595 for a server that supports up to 10 concurrent users. QQube does not currently support QB Online data.
FinJinni by GypsiBI extracts either a local or cloud “data warehouse”. Again this is fast but the data is only as up to date as the last extract. You run your reports against the extracted data which can include both desktop and online QB data. The price starts at $999 for a single user and up to 5 companies. Another $500 will expand to either 10 companies or 6 users.
My complete library of Crystal Reports materials:
Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($18)
Expert's Guide to Subreports, Parameters and Alerts ($14)
Expert's Guide to SQL Expressions, Options and Commands ($13)
Expert's Guide to Totals ($12)
Expert's Guide to Cross-Tabs ($11)
Expert Techniques Vol. 1 - 4 ($10 each)
Quick Reference to Crystal Reports in Visual Basic ($8)
Quick Reference to Crystal Reports in .NET ($7)
You will find these on the LIBRARY page of my site.
Setting section height to a specific number
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.
Using an asterisk for ALL in a parameter
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}
Let me create your Crystal Reports
There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.
I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.
Downloading the Xtreme sample data
Starting with CR 2008, the Crystal Reports sample data was changed from an MDB to several XML files. Fortunately, the Xtreme MDB was still available for download, along with the associated sample reports. I linked to this download from my blog and website so that students with newer version of CR could still do the lessons in my course materials. That link worked for about 10 years.
Today I learned that this link has finally died. I will try to find another link to download the file. Until I find one the link will point to a copy on my own site.
Minor changes can have a major impact on performance
Over the holiday break I had a customer contact me about a report that had just started taking a very long time to run. The first place I looked was in the record selection formula where I found this in the second line:
{Orders.OrderDate} -1 in LastFullWeek
I suspected that this was the problem. To confirm I had him send me the original report that ran in the normal time. Here is what the original said:
{Orders.OrderDate} in LastFullWeek
Apparently the requirement for the report had changed from the prior week starting on Sunday to the prior week starting on Monday. That minor change causes Crystal to completely drop the date rule from the automatically generated SQL. This means the database will send back ALL dates and Crystal will have to apply the date filter locally. I had him try this instead:
{Orders.OrderDate} in Minimum(LastFullWeek) +1 to Maximum(LastFullWeek) +1
Both version 1 and version 3 return the same results but version 1 adjusts the field while version 3 adjusts the comparison values. Version 3 will make it into the SQL WHERE clause while version 1 will not.
The same problem happens when you use a function on the field. Here are two common examples I see:
Date ({Orders.CreateTimeStamp} ) in ...
Round ( {Orders.Amount} ) = ...
If the report performance is fine than these examples can stay, but if you need to speed up the report then these should be written without the functions, so that they are incorporated into the automatically generated SQL.
Individual Training with an expert.
Do you have gaps in your Crystal Reports knowledge that are slowing you down? The most effective way to fill in those gaps is to have individual training sessions with an expert. I have done Crystal Reports full-time for over 20 years.
This type of training is ideal for people who:
Need to cover only a few topics.
Want to use their own data and reports as examples.
Want to learn from someone who has taught over 2500 satisfied students.
This is one-on-one, hands-on training - not a webinar. Start with a purchase of only 2 hours and get my course material with exercises for free. Do as much of the work as you want on your own, then use your prepaid time to work with me by phone and remote connection when needed. We can review lessons, discuss questions or even troubleshoot existing reports. For more details see the "Individual Training" page on my web site.
Better SQL to return the last record (SQL Server)
(See the next article for an even better approach)
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.
Even Better SQL for showing the last record
After I wrote the article above, 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
Gems from the Archives
Using AutoComplete in Crystal Reports formulas (January 2009)
Ranking based on a ratio (January 2009)
Removal instructions
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2019 by Ken Hamady
All rights reserved - Republishing this material requires written permission