Using “Order Links” to improve performance

Wednesday 21 March 2018 @ 9:19 pm

I have written once before about using the “Order Links” feature of the database expert. In that article I used “Order Links” to help me prevent a SQL error in the pervasive database engine. This past week I used the same feature to improve the performance of a report.

This report had many tables but there was one, the Dept table, that provide the primary filter for the report. The user would select a specific department each time they refreshed. But when I looked at the SQL generated by CR, I noticed that the Dept table was the last table joined into the data set. In my mind, that meant that Crystal was bringing in thousands of linked records that would eventually be discarded because they were linked to the wrong department. If the SQL could apply the filter up front we would greatly reduce the number of linked records we would generate, and that would speeds things up.

So I suggested to the user that they go into the “Links” tab of the Database Expert and right-click to find the “Order Links” option. I had them move the Dept join to the top of the joins list and they immediately saw a dramatic improvement in performance. It may not work in every report or with every database (this report was Oracle based), but when there are lots of tables and the report is slow, it is another option to try.

Printing “group continues on next page”

Tuesday 13 March 2018 @ 6:03 pm

Many Crystal course books will have a lesson on printing a “Continued” message whenever a group takes up more than one page.  This message usually prints at the top of each page in the group, except the first.  But below is a different approach.  It prints “Continued on the next page” at the bottom of pages when a group is going to go beyond the current page.  There are actually several ways to do this, but the one that works in almost all situations is below.  It uses 3 formulas, and these can be cut and pasted into your report:

The first formula goes on the Group Header (and can be suppressed):

BooleanVar Continued:= True

The second formula goes on the Group Footer (and can be suppressed):

BooleanVar Continued:= False

The third formula goes on the page footer:

BooleanVar Continued;
if Continued
then "Group continues on next page"
else ""

Trouble with the suppress condition for a line

Thursday 8 March 2018 @ 5:50 pm

I had a bit of a mystery this past week. A customer wanted to suppress a series of vertical lines in the page header, based on a condition (a field being null). SAP just added a suppress condition button for lines and boxes in the latest version of CR, CR 2016, so I figured it would be simple.

But when I put the condition formula in for one of the lines, it did not suppress when it should. I applied the same condition to the next vertical line and it worked as expected, but the first line did not. If I deleted and redrew the line it worked fine, but nothing I could do would get the original line to work.

So I spent a few minutes trying to figure out what was different about the line that didn’t work. I found that while the top end of this line looked like it was in the page header, it was actually a hair higher in the report header. The report header was suppressed.

Normally, suppressing or hiding a section has no affect on formulas. It does affect some other things like subreports and cross-tabs. A hidden/suppressed section will prevent subreports in that section from running. To get a shared variable from a subreport without seeing the subreport requires that we use a ‘stealth’ subreport.  Also, a hidden/suppressed section will prevent a cross-tab from doing any of it’s calculations. But formulas are often put in suppressed sections and they are not normally affected. The suppress formula for a line or a box is an exception, apparently.

So, if you enter a condition formula to suppress a line or a box, and if that line/box has it’s top in a suppressed or hidden section, then the suppress condition will be ignored.  This isn’t true at the bottom of the line/box, only at the top.

One more mystery solved.

Lasso multiple objects without selecting lines and boxes.

Monday 26 February 2018 @ 1:02 am

One method for selecting multiple objects in Crystal (like most Windows programs) is to “lasso” them. To lasso objects in Crystal you click and hold your mouse button in an empty spot and then drag the cursor to expand an orange rectangle. When you release the mouse button, all of the objects inside (or touched by) the orange rectangle become selected objects.

The problem I often have with this method is that drawing objects (lines and boxes) often get selected as well. For instance, if you lasso a group of objects that are inside a box object, that box will often be selected. Usually this happens if you select the highest object in the box, even if you don’t touch the box itself. If your lasso box touches or surrounds line objects those lines are also selected.

Often I want to select a column of numbers for alignment or formatting and I want to skip over the lines. Before now my options have been to lasso the objects and then carefully deselect the lines and boxes by holding my [ctrl] key and then clicking those objects. Or I could skip the lasso method altogether and select that group of objects by clicking on them individually while holding down the [ctrl] key.

But this past week I discovered something I had never noticed before. If you use the lasso while you are in preview mode, the lines and boxes are NOT selected. I had created a grid of 40 numbers (4 across and 10 down) and had separated the rows and columns with horizontal and vertical lines. Plus there was a box around the perimeter. To select all the numbers individually in design mode would have taken 40 clicks. The lasso would take 13 clicks to deselect all the lines and the box. But while in preview I could select all the numbers in one lasso and none of the drawing objects were selected.

This made it easy to align the columns or change the format for the numbers as a group.

Adding info to dynamic parameters

Thursday 22 February 2018 @ 5:26 pm

I created an unusual dynamic parameter for a customer this week. They wanted to select from a list of customers, but their customer list included lots of dead weight. To shorten the list they decided to include only customers who have sales of over $10K in the past 2 years.

To filter the results of a dynamic parameter you need to base it on a SQL command, rather than pulling it directly from a table. And because this filter involved a total our command needed a nested subquery. The inner query took all orders in the past two years, and did a sum of the order amount, grouped by CustomerID.  Then the outer query selected records from the inner query when that sum was greater than $10K.

The field for our parameter was the Customer ID but Crystal parameters allow you to bring in a second ‘description’ field along with the filter field. The Customer Name was our description field.

This worked fine, but we noticed that if a customer had multiple accounts, the customer name appeared multiple times with slight variations. I thought it would help if the user could see the sales along with each name. Since the SQL we had written already included the total sales for each customer, I figured we could make that part of the description field. In the outer query I replaced the original description field (customer name) with an expression like this:

A.CustName + '($' + Ltrim(Str(A.TotalAmt/1000,5,0)) + 'K)' As CustName

This generated a parameter list that looked something like this, with total sales shown as part of the parameter description:

AMCB3 – AMC Bonding Co., Inc.($15K)
BKDA1 – BKD Associates($10K)
CRSU2 – Credit Suisse AG ($13K)
DEVG2 – Deveraux Foundation($25K)
FOSE1 – Four Seasons($19K)

I have done something similar when the description field needed to combined  multiple fields, like FirstName and LastName. It can be done with any combination of fields.

Blocking drill-down based on a condition

Friday 16 February 2018 @ 10:52 pm

Here is a new twist to an old trick. I wrote several years ago about preventing users from drilling down on a section by putting a text object over the other objects in the section. I was recently asked if there was a way to have this happen only on certain groups. My initial reaction was no because a text object is either there or it isn’t, but then I realized that more recent version of CR allow you to add to the width of an object with a condition formula. And, when you make the text grow based on a condition it still blocks the drill down for any objects that it grows to cover.

So say you want to allow drill-down on accounts that are open, but not on accounts that are closed. You could put a very narrow text object on the far left end of the group section for the account. Then you could go into “object size and position” and put the following formula into the width condition:

if {table.status} = "Closed"
then 1440 * 8.5 //1440 twips per inch
else 0

This formula calculates the number of twips to ADD to the existing width. There are 1440 twips in an inch, so I multiply the number of inches I want by 1440.  This makes it clear how much it will add and makes changing the value easier.

Titles for Cross-tabs that spill onto horizontal pages

Sunday 11 February 2018 @ 5:02 pm

When a cross-tab gets too wide, it generates a ‘horizontal’ page to the right of the original. If you were to print the report to paper, these horizontal pages would print as separate pieces of pages. However, page header objects do not automatically repeat on these pages. So, any titles that you have in your page header will be missing on these horizontal pages. You have two options to get the titles to repeat:

1) If you want the fields to appear outside of the cross-tab, you can format them to repeat on each horizontal page. Right-click on the field, select “Format Field” and go to the common tab. You should see a property called “Repeat on Horizontal Pages”. Checking this property will cause the object to print again automatically for every horizontal page.

2) If you want the title to be part of the cross-tab, you can put your title into a formula and use that as your first column field. This title can incorporate parameter fields and can also include a carriage return character to print on multiple lines. An example formula might be:

"Sales by Region and Shipping Mode" & Chr(13) &
"Orders From: " & ToText ( Minimum ( {?Order Range} ) , 0 , "" ) &
" To: " & ToText ( Maximum ( {?Order Range} ) , 0 , "" )

The key is that the formula has to start with WhileReadingRecords. This makes it eligible to be used as a cross-tab column. You make this field the first “Column” field in the cross-tab.  You should check the “suppress subtotal” property for this group level so that it doesn’t generate duplicate totals (cross-tab Expert, customize style, highlight this formula and check the property on the left).  And if the formula includes Chr(13) (to generate a second line) you will have to adjust the height of that cell to show the second line.  It won’t grow automatically.

Finding tables and fields in SAP B1, JD Edwards and Great Plains

Monday 29 January 2018 @ 3:16 pm

I was looking for a list of the tables and fields in SAP B1 and found a great site:

The person that maintains this site also maintains a similar site for the JD Edwards application

So while I was at it I thought I would see if anyone had done something similar for Great Plains. I found a few sites. (some info members only)

This type of information is usually available directly from the vendor, but often those resources are restricted to licensed users with a support account. The sites above are open to the public. If you know of has a similar site for another application, let me know and I will add it here.

Finding changes in report output

Thursday 25 January 2018 @ 4:23 pm

I often work with large and complex reports. Sometimes making a minor change can have unexpected consequences. I like to be able to see that the only things that changed are the things I intended to change. If the report is long or dense it can be a challenge to identify changes. But I recently worked out a relatively simple way to identify all of the values on a report that have changed as the result of my formula changes. I use the “compare” add-on in NotePadd ++.

So the first thing I do is refresh the ‘before’ report so I know that I have up to the minute data. Then I export the entire report into TXT format, creating the file before.txt. Then I make my change(s) and export the entire report a second time into TXT format, creating the file after.txt. Now I open these two files in open NotePad++ and run a compare.  All of the differences will be highlighted and it is easy to see all the changes.

For example, one of my upcoming assignments is to simplify the formulas in a complex report without changing the output. So my plan is to use this method after each round of changes. Since nothing should be different, any changes I find in the compare process will be a sign of a mistake.

If you haven’t tried NP++ (which is free) you can read more about it here, including the best place to download it (see update below).

Update – I just tried to install NP++ for a customer and I couldn’t add the Compare plugin because the plugin manager was completely missing. After some research I found that the developer of the plugin manager module added a sponsorship image (an ad) and so the plugin manager is no longer included in new downloads. If you already have the plugin manager upgrades will not affect you, since this only affects new installs.  The developer of NP++  is working on a replacement plugin manager.

Since Ninite always installs the latest version I recommend that, for now, you download NP++ version 7.4.2 directly from the NP++ site.  This was the last version that came with the plugin manager. It should automatically update itself to the latest version of NP++.

Web based deployment options compared (2018)

Wednesday 17 January 2018 @ 10:59 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, one of which is new since last January:

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
Bezlio – a SaaS Web viewer
ReCrystallize Pro – a launch page generator for the web
ReCrystallize Server – a server-based 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 (PDF) that shows some of the specifics for comparison, including prices. This year there are several new lines in the matrix. They show which tools encrypt credentials, provide system monitoring and allow you to launch reports from an external URL. If you have any feedback to share on these tools I would be happy to hear from you.

«« Previous Posts
Recrystallize Pro

The Expert Series