Contents for July 2013:

** Report management utilities comparison (2013)
** Crystal Reports 2013
** My library of Crystal Reports materials
** Update to Crystal Delivery scheduler
** Web dashboard without a web application
** Let me create your Crystal Reports
** Crystal Reports vs SSRS #2
** No tables shown in the Database Expert
** Open enrollment Crystal classes in Frederick, MD
** Formula tips from a reader
** Data-type changes in Excel

Gems from the Archives (May 2002):
** Adding a specific number of business days to a date
** Finding the last Friday (or any day) of month  

Report management utilities comparison (2013)

I have just updated my comparison of RPT management utilities for 2013. These are tools that allow you to scan, document, compare and update RPT files.

Two products have been upgraded significantly in the past year. Find It EZ has added many new features in the past year and Report Miner has just recently been completely rewritten. The complete list of tools is below:

Report Miner by the Retsel Group
Find it EZ by Find it EZ Software Corp.
Report Analyzer by Cortex Systems
Crystal SpellChecker by Redeemed Software.
Search and Replace by Redeemed Software.
Mass Verify by Redeemed Software
.rpt Inspector 3 Professional Suite by Software Forces, LLC

Crystal Reports 2013

The future of Crystal Reports looks a lot like the past. According to a presentation at the SAP Users Group (ASUG), all new features in CR will go into CR for Enterprise, the new version of CR that is found only within the Business Objects Enterprise environment.  Standalone Crystal Reports is briefly mentioned and shows up again in the Q&A section at the end. The good news is that they are going to continue to provide and support a standalone product. There is even a plan for CR 2013. However this is described as a 're-branding' which means that the primary change will be the name.

My complete library of Crystal Reports materials:

Do you want to really understand Crystal formulas?  Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides.  Each guide comes with clear explanations and sample files to illustrate the concepts.

    Expert's Guide to Formulas  ($36)
    Expert's Guide to Subreports, Parameters and Alerts ($28)
    Expert's Guide to SQL Expressions, Options and Commands ($26)
    Expert's Guide to Totals ($24)
    Expert's Guide to Cross-Tabs ($22)
    Expert Techniques Vol. 1 - 4  ($19 each)
    Quick Reference to Crystal Reports in Visual Basic ($16)
    Quick Reference to Crystal Reports in .NET ($14)

You will find these on the LIBRARY page of my site. 

Update to Crystal Delivery scheduler

Groff Automation has just released Crystal Delivery 10, an update to their free scheduler. It has been completely rewritten in C# using the .NET runtime engine from CRv13. The user interface is similar, but other things have changed:

Report information is stored in a Microsoft SQL Compact database
It uses the Quartz.NET scheduling engine with Cron format input
It supports sending Email through Gmail, and also HTML formatted emails
It can export your report as a PDF in multiple locations
It can "Start with Windows" and/or "Start to Tray"

Up till now Crystal Delivery has been free, and it is still free if you are scheduling up to 6 reports. If you need to schedule more than 6 reports there is an unlimited version for $30.

See my blog post for installation details and support information.

Web dashboard without a web application

Ido Millet of Millet Software has posted an interesting demo on his site. It is a web dashboard that is automatically refreshed from live data. What makes it unique is that it doesn't require an application running on the server. The update work is all done using Visual Cut on a local machine.

At the core of the process there are two reports which are scheduled to be run and exported to HTML. One provides the main page while the other is burst to provide the drill-down pages. After the export there are some Visual Cut commands that perform some 'replace' operations and tweak the files. The modified files are then uploaded automatically to the web server, via SFTP.

He combines all this with some clever iFrames and adds web folder passwords deployed using the htaccess file. It provides an excellent example of what can be done using relatively simple and inexpensive tools.

The  demo login UID/Password are both "demo"

Let me create your Crystal Reports

There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert?  Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done".  And since I am also a teacher I am happy to explain to you how the techniques work together.

I can also review existing reports that break, or run slowly, or seem overly complex.  Let me have a look at them and see if there is a more elegant solution.

Crystal Reports vs SSRS #2

And now, another post in my series Crystal Reports vs SSRS:

As part of my research, I am recreating one of my own reports in SSRS. I can already see several things that will frustrate a Crystal Reports user. I have listed the first three below. It may be that my lack of experience in SSRS is showing, but to me these seem like true limitations:

1) When I start a new report in Crystal I can use the Database Expert to quickly explore the data structure. I can log into an ODBC or OLEDB connection and instantly get a list of the tables it has available. Then I can select one or more tables and see the list of fields in each table. I can even browse sample data from any field in these tables. I can do all this without writing any SQL or knowing anything about the structure of the data. I can quickly learn the structure of an unknown database.

SSRS Report Builder has something similar called a Query Designer, but it is only available if you are connecting directly to SQL Server.  If you are using another ODBC or OLEDB data source you have to start by writing some SQL.  This means you have to know the exact table and field names before you start. Even in the data I use everyday I don't bother to memorize the table and field names. I found myself going into CR to generate quick SQL statements and then copying those into SSRS, just to get started. And in SSRS, to get even a peek at the actual data you need to create the connection, write the SQL, insert a table object, drag fields onto the object and preview the report.

Butch Adams of Conexus SG in Dallas helped me test the Query Designer and pointed out that within BIDS (BI Dev. Studio) you get the query designer with ODBC as well, but not in Report Builder.

2) Once I got some data on the screen I noticed that I can't make any changes to the layout while in preview. All changes are made in design mode. That might not be so bad in itself, but then every time I went back to design to make a change, I had to rerun the entire query to get back to preview. And, if I wanted to see how my change looked on page 25 I had to go back to that page from page 1.

In Crystal you can make changes in preview. You can also switch back and forth from design to preview mode using the data that Crystal holds in memory. You can refresh any time you want, but Crystal will not force you to refresh if you simply make layout changes. Only certain things will require a refresh in CR, like adding a new field that wasn't already in the report dataset or changing the linking options between the tables.  

3) Many of my own production reports join tables from two different ODBC connections (SQL Server for my contacts application and MS Access for my billing application). I also have several customers that need to link spreadsheet data into reports. Granted, this is not an ideal configuration, but it works pretty well on small and moderate volumes of data.  This is very simple to do in the CR Database Expert and it even lets you specify INNER and OUTER joins across the connections.

Nothing equivalent in SSRS.  I suppose some users could go into their database, create a link to the external table and crate a view within the database to combine the two.  Unfortunately, not all users have the skills or permission to use a solution like this.

If those of you who know more about SSRS want to comment, please feel free.

No tables shown in the Database Expert

I was helping a customer set the location of several tables from one database (catalog) to another identical database on the same server. We tried to set the location at the ODBC connection level, but because the two catalogs were on the same server nothing was changed. We then tried logging out and logging back into the ODBC connection so that we could select a different catalog on the login screen. This changed the catalog shown in the properties of the connection, but the table properties still showed their original catalog. When we refreshed the report the data still came from the original catalog.

So we figured we would have to set the location table by table. That would allow us to specify the catalog for each table, but that is when things got weird. We exploded the dbo node and we couldn't find any of the tables. Instead we found a single list with thousands of object names – none of them being the tables we needed. What was more confusing was that there were no sub nodes to indicate if we were looking at tables, views or stored procedures. We knew that the tables existed because they were used by other other reports.

Because the list was so long I went into Crystal's "Database Options"
(File > Options > Database Tab). There is a place that lets you specify a filter for the list of tables. I was looking for a table that started with IV so I used %IV% as my filter. When we re-opened the dbo node the sub nodes for Tables and Views appeared. Each had a handful of entries with "IV" in their names, including the tables we needed. But when I took the filter out, the nodes went away and there were no IV entries in the list.

Someone familiar with the database recognized the long list as being all stored procedures. So we went back to the "Database Options" tab. Here you can decide to show or hide each of the three object types. When we took the check mark out for Stored Procedures we saw the expected nodes for Tables and Views.

With a little more research I learned:

1) Crystal's Database Expert can only show up to 8,000 objects.
2) This can be changed in the registry – see SAP tech note 1215994.
3) When Crystal hits that limit there is no warning, it simply ends the list.
4) If everything in the returned list of objects is the same object type (tables, views, SPs) then no nodes are generated.

I was surprised by items 2 and 3. If Crystal had told us that we had hit the limit we would know to filter the list. Or if they generated the SP node for the long list we could have skipped searching the list to find tables.

Open enrollment Crystal classes in Frederick, MD

Stop struggling with Crystal Reports and learn how to use it fully.  Come join me in one of my August classes and learn how to make Crystal work for you.  My Intro class makes sure you know all of the basics.  We even include material on cross-tabs, charts and formulas.  The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands.  The material is good for any version.  See my web site for course outlines and dates.

So what makes my classes different?  I have written my own course materials and have used them to teach over 2,500 satisfied students.  And, I give you a toll-free number so you can call me after class with questions at no charge.

Or, if you want to schedule a class at your office, using your data, that is my specialty.  I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price.  They are based near Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston, Vancouver and Montreal.  Call for details. 

Formula tips from a reader

After my last newsletter I received a note from James Flowers with some formula suggestions. I thought his comments, along with my own, might be helpful:

1) James likes to number the formulas in sequence to show which formulas depend on other formulas (feeder formulas). So if the Tax formula relies on the Sales formula he would number them 01_Sales, 02_Tax, etc. Of course there would have to be a longer sequence to make this worthwhile.

I have done this a few times when I had a complex sequence of calculations and it helped. But usually my formula relationships look more like a family tree than a straight sequence. When I need to change a complex formula hierarchy I have on occasion resorted to making a visual map of field dependencies. We are talking about a handful of reports in my entire career, and the maps are always made after the fact, after months go by and someone finds a data combination that we didn't take into account. Making the maps help me to spot the best place to make a change to the logic or fix an error.  

You can also read my other tips on naming formulas.

2)James also suggested adding an extra formula called "Info" to provide a single place to put all of the miscellaneous background that helps you understand a report. These might include the design history, ideas for future changes or some alternate formula logic that you don't want to completely lose. In my reports, I am more likely to put comments in individual formulas. But in rare cases I have created formulas like this that give more general information about the report.

If you do create an "Info" formula, you might want to put it on the report and suppress it. I sometimes use the "report definition" export to document all the formulas in a report. But the report definition feature will not export the text of a formula if it is not used at all in the report. So if you want these formulas to export as part of a report definition, you should place it on the report somewhere, even if you have to suppress the field or the section.

Data-type changes in Excel

People love to work with Excel, and Crystal does a decent job of reading Excel tables. But there is one huge headache that I have had repeatedly over the years. Excel columns don't have a data type so Crystal (or more specifically the Excel driver) has to guess the data types of each column. The problems start when you have columns with mix of data types, usually a string column that has some numeric values mixed in. Crystal has to decide (guess) the data type of the column based on the data that it sees. Once it decides on a type, all values that are NOT that type become null.

The problem for me has been that I could never find the rules for the decision. But with a bit of research and testing I now feel I have a handle on it. The Excel driver (by default) will scan the first 8 rows of the spreadsheet and each column will be assigned the data-type of the majority of the values in those 8 rows (excluding the header row). Ties go to numeric. So if the first 8 data rows have 5 numbers and 3 strings, the column will be called a numeric and all strings are NULL.

The problem is usually that people need ALL the values and they want them all to be strings. Unfortunately the only way to get this is to change the data in the column. I did some testing today and found that you can't simply format the column to TEXT format. The format doesn't change the value and the Excel driver ignore the format. What worked consistently for me was to write the following formula:

=""&A2

In other words I took the problem column and created a new column that appended an empty string to the front of it. This fixes the numerics by converting them to a string but doesn't change the strings that are already working as strings.

Another method was submitted by Bob Antaki of the Bay Area News Group:

- Select the whole column in Excel (or select cells in one column).
- In the Data tab or menu, select "Text to Columns".
- In the Wizard window step 1, accept the default and click Next.
- In the Wizard window step 2, click Next.
- In the Wizard window step 3, for "Column data format", select the "Text" radio button, and click Finish.

I use this so often, the shortcut keys are in my finger memory:
Alt-D, E, Enter, Enter, T, Enter

Text files:
The same problem comes up when reading text files. To fix these you can pull the file into Excel and use the methods above, or you can set up an ODBC connection using the Microsoft TEXT driver. When you use ODBC you can create a schema.ini file and place it in the same directory as the text file.  You can define the data types of the fields within the schema.ini.

My blog post has some related links for working with Excel, schema.ini and Bob's full comments.

Gems from the Archives (May 2002):
** Adding a specific number of business days to a date
** Finding the last Friday (or any day of week) of a month 

Contact Information: