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.

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.

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.

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.