Recurring subreports cause “cascading” failure

Tuesday 23 May 2017 @ 1:49 pm

I have seen a specific Crystal Reports failure with three different customers in the past few months and several other times before that. It happens whenever Crystal has to run “too many” recurring subreports instances. This results in a slow cascading failure. The most obvious symptom is the status bar along the bottom of the screen.  It starts to print new text over the existing text like this:

This gets worse for a few seconds and eventually Crystal locks up and has to be shut down in the task manager. Sometimes there is no error message and it just freezes, sometimes a message says “A required resource is unavailable”.  But the one consistent symptom is the status bar that overlaps while the subreports are running.

It is also an inconsistent problem, in that the same report might might fail earlier or later, and will sometimes run without an issue. You can stop the report when it is part way done with no problem, but once the status bar starts to print overlapped text, there is little you can do.

I have found a few forum threads that discuss similar problems, but the solutions are all over the place and nothing has solved this for any of my customers. So far, the only solution is replacing the subreports with SQL Commands or complex arrays, but I would like to understand the failure. If you are experiencing the same issue or have any insight, please let me know.

Here are the threads I have found that might be related.

https://archive.sap.com/discussions/thread/1824033

https://archive.sap.com/discussions/thread/1899387

https://apps.support.sap.com/sap/support/knowledge/public/en/1768189





How to “break” specified order grouping

Tuesday 16 May 2017 @ 2:00 pm

The “specified order” feature in Crystal is useful when you need to group on a field, but you don’t want the values in normal ascending or descending order. With specified order you can place some or all the values for a field into the exact order that you want them to appear. Specified order is available for groups, for the rows and columns of a cross-tab, and for the ‘on change of’ field in advanced charts. You can even use it if you are grouping on a formula field.

But what happens if you do specified order grouping on a formula field, and then change that formula to be a different data type? You will get a formula error – but not in the formula you created. It will be in the internal formula that creates the specified order, and will look like this:

If this happens to you just click [Save and Close]. Then go back into the Group Expert and change the group to Ascending order. This should clear the error and you can then go back and set up specified order for the new formula, if needed.





Creating a master “dates” table on the fly.

Tuesday 9 May 2017 @ 10:32 am

In some environments, developers have to maintain a master “dates” table with a complete list of dates. If used correctly, it can allow a report to fill in date gaps where there are no transactions. You would have the report start with the master dates table and then link to the date field in your transaction table using a Left Outer Join. The challenge is maintaining the master dates table with current dates.

Today, Adam Butt of APB reports (Norway) helped me create a SQL View (.SQL) for SQL Server  that generates a master dates table. It is based on the SQL for an inflation table (version B)  submitted last week by Michael Hurwood, combined with the SQL DateAdd() function. He also created a second SQL View (.SQL) for Oracle.

I see two ways to deploy this.

1) Run Adam’s .SQL file in your database to save a view. You can adjust the Begin and End dates as needed and then reference that view in your reports.

2) Extract lines 6 to 28 and insert it directly into a report as a command object. You can replace the Begin and End dates with command date range parameters if needed.

With either option, the Begin and End Dates could be replaced with calculations relative to today’s date, something like this:

WHERE
X.COL_DATE >= GetDate () - 366 AND
X.COL_DATE <= GetDate () + 366;

So if you find yourself needing a master “Dates” table, you can use this method to avoid maintaining a physical table.





Adding an inflation table (Part 2)

Friday 28 April 2017 @ 5:16 pm

My last post was about inflation tables and included a quick and dirty method for deploying one using a spreadsheet.  I started with that method because anyone can use it and it should work in any report.  But there are better methods you can use most of the time.  The ideal method is to create an actual table in the same database as your report data, but not everyone has the ability or permissions required to create tables. So here are two alternates.

Re-purpose an existing table.
Any table that has a field with unique consecutive numbers can be used.  For example, in the Xtreme Sample Database that came with older versions of Crystal Reports there is a customer table that has consecutive Customer IDs from 1 to 270.  If I wanted an inflation table of 150 records I could add the Customer table to the report, make sure it is not linked to any other tables and then add a criteria that says “Customer.ID is between 1 and 150”.  After adding this to the report I would get 150 copies of every original record.

It often takes a bit of hunting to find a suitable table for one of my customers.  Here are characteristics of the ideal inflation table and the field withing it that holds the consecutive numbers:

1)The table doesn’t not change much.  I want a range of records that do not change so I don’t have to worry about my report not working in the future. I find code tables for decoding required fields often work well.

2) The table is not much bigger than the number of records you need.  You don’t want the database to have to select 50 records out of thousands every time you use the inflation table.

3) They field is indexed.  One way to tell if a field is indexed is to look at the table in the Crystal Reports linking window.  Indexes are shown as little colored tabs.  An indexed field will select records faster.

4) The field contains unique values (no duplicates).  If there are duplicates you will need to write a command to “SELECT DISTINCT” and use the command as an inflation table.

5) The field has consecutive numbers starting at 1.  If the field has consecutive numbers but they start at another number you have to write a formula in the report to adjust them.  For instance if the consecutive numbers went from 1000 to 1150 then the formula would subtract 999 from each value.  The formula would give you a series of consecutive numbers from 1 to 150.

Use a SQL Command:
You can also create an inflation table with a SQL command.  Here are two SQL commands that work in Microsoft SQL Server using different techniques. The first generates up to 2000 rows in any SQL Server database.  It uses an undocumented table that is currently found in all SQL Server installations. I found a great article comparing this and several similar methods.

SELECT Distinct Number
FROM master..spt_values
WHERE Number between 1 and 2000

The next two SQL commands were submitted by Michael Hurwood, a Crystal Developer at Manufacturing Software.  These commands create a temporary table in memory and don’t reference any existing tables. Version A generates a sequence from 1 to 256, while version B generates a sequence from 1 to 65536 (the square of 256).   You can trim the results down to a specific number by changing the number in the WHERE clause.

Version A:

;WITH
N1 AS (SELECT 1 AS x UNION ALL SELECT 1),
N2 AS (SELECT A.x FROM N1 AS A, N1 AS B),
N3 AS (SELECT A.x FROM N2 AS A, N2 AS B),
N4 AS (SELECT A.x FROM N3 AS A, N3 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY x) AS n FROM N4)
SELECT *
FROM Nums
WHERE n <=256

Version B:

;WITH
N1 AS (SELECT 1 AS x UNION ALL SELECT 1),
N2 AS (SELECT A.x FROM N1 AS A, N1 AS B),
N3 AS (SELECT A.x FROM N2 AS A, N2 AS B),
N4 AS (SELECT A.x FROM N3 AS A, N3 AS B),
N5 AS (SELECT A.x FROM N4 AS A, N4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY x) AS n FROM N5)
SELECT *
FROM Nums
WHERE n <=65536

There are probably equivalent commands in other flavors of SQL.  If anyone wants share a version for Oracle, MySQL or some other flavor I will post them here.





Adding an inflation table (Part 1)

Saturday 22 April 2017 @ 10:23 am

I have a couple of really handy calendar report templates that I have sold to several of my customers. They are mostly simple to deploy but there is one tricky part, and that is that they require an “inflation table”.  This is a table that is added to the report to force each record in the original report to create a series of numbered duplicates.  The only reason that I consider it a challenge is that there are several ways to create/find an inflation table and some work better than others in different environments.  The ideal solution is to create a new table within your database, and index it on the inflator field, but not everyone is allowed to do that, so here is one quick and dirty method that uses a spreadsheet as the inflation table:

1) Create a simple spreadsheet that has one column with a heading of “inflator”
2) Type the numbers from 1 to 20 on the 20 rows below the heading
3) Save the spreadsheet
4) Follow the steps in this article to add the spreadsheet to the report
5) Make sure there are NO links between the spreadsheet and any of the existing tables
6) Add the new field “inflator” to the details band of the report

When you run the report Crystal will take every record in the report and create 20 duplicates of it, and these will be numbered from 1 to 20.   If you want only 9 duplicates you can go into the selection formula and use the inflator field in a rule something like this:

{Sheet1.Inflator} <= 9

Now you will have 9 duplicates of each row.

You can also vary the number of duplicates based on a field or calculation in the report.  My calendar reports use the number of weeks for each event.  The selection formula would say:

{Sheet1.Inflator} <= {@Weeks}

Events that are 3 weeks long will have 3 duplicate records in the report, one for each week.

There are two primary downsides of using a spreadsheet for inflation. You might see poor performance when dealing with large volumes of data.  And if you have to move the report to another environment you have to move and reconnect to the standalone spreadsheet.   That is why my next article will show you how to identify existing tables in your database that can serve as inflation tables.





Using OLEDB to read MS Excel or MS Access files

Thursday 13 April 2017 @ 9:20 pm

Occasionally I have customers that need to query MS Excel or MS Access files for their Reports. Crystal Reports has always provided a DAO connector that allows you to read the classic XLS and MDB files directly. But starting in 2007 Microsoft introduced the XLSX and ACCDB formats and these can’t be read using the DAO connector. If you need to read one of these more recent formats then you will need to use either ODBC or OLEDB to connect. [Note – I try to avoid storing data in Excel. This article explains why.]

I have written several articles about setting up ODBC connections and the challenges of creating a 32-bit ODBC connections on a 64-bit PC. But I have been reminded by several folks that it is simpler to skip over ODBC and create the connection using OLEDB. OLEDB uses the same driver but doesn’t require that you go through the ODBC Administrator to create a Data Source.  [Note – one quick workaround for XLSX files is to do a “Save As” and select “Excel 97-2003 Workbook” format which allows you to continue using DAO. Most spreadsheets used to store data will work just as well in XLS format, unless you have more than 65,536 rows or more than 256 columns.]

To connect to an OLEDB connection open the database expert (or the “Data” step while creating a new report) and open the folder/node called “Create a new connection”. Below that you should see a node labeled OLEDB. Open that and you should be asked to select an OLEDB provider. If there is already another OLEDB connection open you can double click “Make a new Connection” right below the OLEDB node label to open the list of providers.

Look through the list of OLEDB providers until you see “Microsoft Office 12.0 Access Database Engine”. [Note – if you don’t see that provider listed then you need to download and install the driver. See this article for the download link.]

Highlight that provider and click “Next”. On the second screen you select either Access or Excel as the “Office Database Type”. Then click the button to the right of the “Data Source” box to browse to the folder that contains your file. Highlight your file and click “Finish” to make the connection. The connection should now display either the list of tables (MS Access) or the list of spreadsheet tabs (MS Excel) available to use in your report.





Reports on a phone with no server to setup

Sunday 2 April 2017 @ 1:45 pm

The team at SaberLogic has just created a new way to deploy Crystal Reports on the web. It is a SaaS tool called Bezlio. Bezlio allows users to run reports on demand from the browser on any device (including your phone). But the difference is that you don’t have to set up a web server to provide access. Instead you install something called the Bezlio Remote Data Broker (BRDB) anywhere inside your network firewall. It is the BRDB that actually runs the reports, which are delivered as PDFs back to the user’s browser. Between the user and the BRDB is the Bezlio cloud service.

A user opens a browser and logs into Bezlio like they would with any web portal. Bezlio notifies the BRDB which is listening for requests, and the BRDB sends a list of reports approved for that user. The user selects a report and the BRDB asks the user to fill in any parameters needed for that report. The BRDB then runs the report inside your system and generates the result as a PDF which is sent back to the browser through Bezlio’s server. No data is stored in the cloud and no ports need to be opened in your firewall. All the transfers are token based and encrypted.

For example, I could set up the BRDB on my office workstation and run reports from my phone without having to maintain my own web server. The setup sounds simple and the cost for me as a single user would be $35 a month. The cost for 10 users is $25 per user per month with a 20% discount for buying a full year. The price drops as the user count goes up.

Each Bezlio user license includes a free copy of Logicity Professional so that you can push scheduled reports out if needed, but scheduling is done within the local network, not through the browser.

Two other things to keep in mind. First, the SaaS model is more expensive than buying a portal product in the long run.  This is because you are paying someone to maintain the infrastructure for you. Second, a PDF isn’t interactive.  If you rely on things like drill-down or on-demand subreports you won’t have these in the Bezlio model. But I can see this being popular with a certain segment of users.  For a full list of competitive products in this family you can read my annual comparison of web deployment options.





Comparison of desktop-based schedulers (2017 update)

Monday 27 March 2017 @ 9:22 pm

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





Save money on rePORTAL

Friday 24 March 2017 @ 10:29 am

Reportal Software has just released rePORTAL 6.1, a new version of their web portal for Crystal Reports.  They are offering a 10% discount on all server license fees through April 15, 2017.  I don’t see the server price on their web site but the price was $3,000 per server in January when I did my latest annual review.  That link is also a great place for more information on rePORTAL and the other third party web portals.





“Poor man’s” row level security

Tuesday 21 March 2017 @ 2:49 pm

One of my customers has a report that he can run for one Sales Rep or all. He now wants the Sales Reps to run the report themselves, but doesn’t want them to be able to run each other’s reports. He wanted the report to be restricted based on the user’s windows login. The term for this is row-level security, because certain users are allowed to access only certain rows of data.

There are a few different ways to do this. Unfortunately, none of them would work in this environment:

1) You can set this up in some databases, but this client doesn’t have access to the database to make changes.
2) You can set this up in some web portals and viewers, but this customer runs all of their reports from an integrated viewer built into their software application.
3) There are even ways to do this using formula functions, but because their integrated viewer is installed at the client level it would have been difficult to deploy and maintain the required dll’s.

So I suggested a method that I will call “poor man’s” row level security, and that is the option the customer chose. Each of his sales reps and their managers got to pick a password specific for this one report. Each time they run the report from the integrated viewer they select their name in the original parameter and enter their password in a new parameter. These values are checked in a formula called {@criteria} that is part of the report’s selection formula. The {@criteria} formula looks something like this:

if {?User} = “Tom”  and  {?pw} = ‘cats’ then true else
if {?User} = “Kelly” and {?pw} = ‘dogs’ then {rep} = [‘Joe’, ‘Sam’] else
if {?User} = “Joe”   and   {?pw} = ‘bugs’ then {rep} = ‘Joe’ else
if {?User} = “Sam”  and  {?pw} = ‘mice’ then {rep} = ‘Sam’ else
false

Tom with his password sees all Sales Reps. Kelly with her password sees the two reps that she supervises. Joe and Sam only have access to their own data.  The selection formula ends with:

……. and {@criteria}

This may not seem very secure but is protected by two factors in their environment.

  1. The users don’t have a copy of Crystal
  2. The connection to the database is made only from within the application.




«« Previous Posts
Jeff-Net
Versa Reports

remiCrystal reporting solution