Comparison of desktop-based schedulers (2019 update)

Tuesday 26 March 2019 @ 11:15 am

How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more. So every March I go through the list and publish a feature comparison on my blog.

There are 11 active products in the list this year. The page linked above provides a brief description of each product and lists the features that set it apart. Then there is a detailed feature matrix that shows the key specifics for comparison, including prices. To clarify the matrix terminology I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product. In May I will be updating a separate article that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.





Another option for selecting ALL in a parameter

Saturday 23 March 2019 @ 11:04 am

I wrote last fall about selecting “ALL” with a string parameter field. My comments at the end list options for making the technique work for numbers and dates. Recently I received a note from one of my colleagues on a better approach, using optional parameters.

Starting with CR 2008 (v14) Crystal has allowed us to define a parameter as “optional”. This allows the user to not enter a value at all in a parameter. However, when you use an optional parameter in a formula you always have to test for the existence of a value in the parameter field before you use. The test often looks like this:

if HasValue ( {?Tax Rate} )
then {Orders.Order Amount}*{?Tax Rate}
else 0

This way the report knows what to do when the parameter is skipped.

Another way of creating an ALL option is to tell the report that any time there is no value entered in the parameter, the user wants ALL values. The formula would look something like this:
(not HasValue ( {?State} ) or {Customer.State} = {?State})

Note that the HasValue() test has to come before any other test that uses that parameter. If you reverse the two tests in this formula it will generate an error whenever you don’t fill in the parameter.

And thanks to Luc Rascar, a Crystal Reports/Business Objects consultant in France, for pointing this out.





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.





Carriage returns in a formula that will survive a text export.

Friday 8 March 2019 @ 12:40 am

There are several common uses for exporting to text format.

I use text format whenever I need a Fixed Length export file. These are files where the exported record doesn’t have a comma or pipe delimiter. Each field in the string is identified by its character position in the string, since each field is a specific number of characters. This means that all records end at the same position, regardless of how long individual field values are.

I also use text export format for some CSV exports, because there is more flexibility.  For instance, when I need to generate two CSV rows from the same detail row in the data I find it easier to structure the CSV rows in a formula  and export as text.

And that brings me to what I learned last week. If you are exporting to “text” format and the formula you are exporting has carriage returns in it, you might find that they don’t work after the export. For instance the formula below would show 3 rows in the preview of Crystal Reports:

{@String1} & CHR(13) &
{@String2} & CHR(13) &
{@String3}

The function element CHR(13) creates a carriage return between the different elements of the string. But if you export this formula using “text” format you will find that the carriage returns don’t survive the export. The text file would not have the three rows that you see in preview. But with a little experimenting I found that adding a second related function in the formula works better:

{@String1} & CHR(13) & CHR(10) &
{@String2} & CHR(13) & CHR(10) &
{@String3}

In CR preview both formulas will appear the same. However, the second formula will provide carriage return that survives into the text export, while the first one will not.

Within a week of making this discovery for one customer, I found I needed the same thing for a second customer. I probably should have figured this out even sooner. When working text files and hidden codes I have seen that you usually need both a carriage return, Chr(13), and a line feed , Chr(10) to start a new line. But since it only takes one of these in Crystal preview, it is easy to forget that they work together.





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.





How link direction can affect performance

Saturday 23 February 2019 @ 1:33 pm

I have written before about links that tap into indexes and how they can speed things up. Especially when you can hit ALL of the fields in the index.

This week I was troubleshooting a report that took 2 hours to run and found a similar case. The report was from a Sage/MAS accounting application. I saw a link between invoiceHistoryHeader and InvoiceHistoryDetails where it took 2 fields to make a unique match.  I checked the index tags for the primary key (red colored tabs) and and found that there were 3 fields in the details table primary index while the header table had only 2. Since we only had two of those fields to use for linking I wanted to make sure the link went from the details to the header, so that the link would completely hit the index. From the arrangement of the tables that appeared to be true, but when I hit “Auto Arrange” I could see that the join actually started with the header and went to the details.

Reversing the join allowed the report to complete in 7 minutes. Still slow, but a huge improvement over 2 hours.





Add an address block without blank lines

Friday 15 February 2019 @ 12:21 am

I often find one or more “address blocks” at the top of form reports, like invoices or purchase orders. These are blocks of text that typically show a customer name, two or three lines for address info, and a last line for City/State/Zip. The simple approach is to arrange the individual fields on the report, but if some address lines are blank you get empty rows in the block. Here is the approach I use to make sure that there are no empy lines in the block.

First you write a formula that combines the city/state/zip into one row like this:

//{@City, State & Zip}
{Cust.CITY} & ", " & {Cust.STATE} & "  " & {Cust.ZIP}

Then you write a formula that combines the first formula with the other potential rows of the address, like this:

{CONTACT1.Company} &
(if {Cust.ADDRESS1} > "" then CHR(13) & {Cust.ADDRESS1} else "") &
(if {Cust.ADDRESS2} > "" then CHR(13) & {Cust.ADDRESS2} else "") &
(if {Cust.ADDRESS3} > "" then CHR(13) & {Cust.ADDRESS3} else "") &
CHR(13) & {@City, State & Zip}

The formula above assumes that every record will have a Company value and a City/State/Zip value. The address lines are added if they have a value. And when they are added a carriage return is also added, using CHR(13). This way each line appears on its own row, but only when it has data. There are no blank lines when a field has no data.

Two things to watch for.
1) You should set both of the formulas above to use “Default Values for Nulls”. Otherwise a Null value for one of these fields will cause a blank address block.
2) Make sure you format the address block with “Can Grow” and then don’t put anything right below it in the same section.  Otherwise the address field might grow right over the object below it.





Windows update breaks Raiser’s Edge reports

Saturday 9 February 2019 @ 6:20 pm

I have several customers that use the donor tracking software Raiser’s Edge(RE) and pull data out of it with Crystal Reports. To run CR against RE data usually involves exporting the data to an MS Access (MDB) file and then reading that MDB with Crystal. The challenge is that Microsoft doesn’t really support the classic MDB format anymore.

This week I heard from several RE users that a recent Windows update has broken the process. They go to run the report and get an error that the file format is in an “unrecognizable database format”.

One customer was able to resolve the problem by changing the export from “Blackbaud Report Writer Database (MDB)” to “MS Access 2000 Database” but there is some concern about making this change. Some users have said that these exports work fine when you run a report from Crystal, but that these reports will not always run fine from within the RE application menu. Others have had success running reports from these exports in both environments. I haven’t found the specific difference but I suspect that it may have to do with the version of the ODBC driver being used.

I will post more information as it comes in.





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.





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

Crystal Reports Server