Web based deployment options compared (2019)

Saturday 26 January 2019 @ 10:13 pm

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

Monday 21 January 2019 @ 9:02 pm
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.





Downloading the Xtreme sample data

Sunday 13 January 2019 @ 11:19 pm

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

Monday 7 January 2019 @ 11:54 pm

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.





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.





Crystal Reports formula function libraries (2018)

Tuesday 27 November 2018 @ 10:38 pm

It is time for my annual comparison of formula function libraries. If you aren’t familiar with User Function Libraries (or UFLs) they are DLL files that add new formula functions to your Crystal Reports formula editor. With these functions your formulas can do some pretty amazing things like:

1) Carry values from today’s report to tomorrow’s report
2) Carry values from one report to another.
3) Append lines of text to an external text file.
4) Automatically copy a value to the clipboard.
5) Check the user name of the user running the report.
6) See if a file or folder exists (on your network or on the internet).
7) Rename/copy/delete a file on your hard drive or network drive.
8) Launch an application or run a batch file.
9) Execute a SQL statement (Select/Insert/Delete).
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Generate bar codes without having to install any fonts

If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL. The five UFL providers are:

Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (CUT Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)

The only product that has changed since last year is CUT Light, which can now generate 3 types of charts, crop images and process text through Google’s “sentiment analysis” engine.

If you need help deploying one of these functions in a project let me know.





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”.




Next Posts »» «« Previous Posts
Jeff-Net
Recrystallize Pro

Crystal Reports Server