After my last newsletter I received a note from James Flowers with some formula suggestions, and 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 Continue Reading »
Formula tips from a reader

I was helping a customer set the location of several tables from one database (or catalog) to another identical database on the same server. We tried to set the location using the ODBC connection, but they are on the same server so it didn’t change the catalog. We also tried logging out and logging back into the ODBC connection so that we could select a different catalog on the login screen. But this only changed the catalog shown in the connection properties. The table properties still showed the 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 a table within the new catalog. But that is when things got weird. We Continue Reading »
No tables shown in the Database Expert

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 Continue Reading »
Crystal Reports vs SSRS #2

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 Continue Reading »
Data-type changes in Excel.