A customer asked if there was a way to read a Google Sheets spreadsheet from Crystal so I did some research for her. If you do a web search for “ODBC Google Sheets” you might be fooled into thinking that there are several options. However if you follow them they all point to the same ODBC driver from CDATA. This has a free trial download and costs $200 for a perpetual license or $79 per year for a subscription license.

We downloaded the trial and found that setting it up was pretty straightforward. There was only one thing that wasn’t very clear at first. Google says to register your “application” and get a security token for it. I assumed that this was only if we were coding an application wanted do authentication in the code, so we skipped that step. From Crystal we were able to connect successfully to Google, but we could not see the sheets. All we could see was a list of actions.

After a few more experiments we went back and created an application entry called Crystal in the customer’s Google account. That allowed us to generate a token and a ‘secret’ (their name for a password). Once those were entered in the local ODBC config, Crystal was able to see the tables and things worked normally.

SQL expressions don’t get used very often. After all, if you know how to write a SQL expression, why not write the whole query as a command?  But this week a customer asked me if using a SQL expression could help them look up values in a master code table.  It was a great idea and we developed a handy SQL expression that they can use in many different areas.

This customer’s database stores all of their code descriptions in a single master lookup table. I see this quite often.  The code table will have different groups of records for different code fields.  There might be one set of records to decode the product category, another set to decode the customer type, and maybe even a set to decode the state field for addresses.  When you have multiple code sets in the same table you will usually have three fields:

• {Code} – the code value
• {Desc} – the description that goes with that code
• {Field} – the name of the field that is being decoded (e.g. State, ProdCat , CusType)

The last field is necessary when the codes are not unique. For instance a code of 007 might represent Ohio in the “State” list, while further down code 007 might represent ‘filters’ in the ProdCat list.  So once you link on the code you also have to add a record selection filter to specify the field.  Things get complicated if the code field is optional because Crystal doesn’t support filters on outer join fields.  But even when the code field is a required field, retrieving a description involves five steps for each code:

1) add a new instance of a table
2) link the new instance to the correct code field
3) change the alias of the table to make it clear which field is being decoded
4) add the appropriate filter for that field in the selection formula
5) place that field on the report.

With a SQL expression all of the above is contained within the expression. Here is an example:

(SELECT max(code.desc)
FROM code
WHERE code.field = 'ProdCat'
and po.ProdCat = code.code)

Once you verify that it works you can simply copy the object from the layout of one report and paste it in another report and you are done.  All the steps above are accomplished in that one step. And if you need another field decoded you just duplicate the field and change the value ‘ProdCat’ to another code.  Best of all you can use it with optional codes, because the filter is in the expression, not in the overall report query.

And, thanks to Laurie Weaver, a developer at Wyse Solutions, for initiating this idea.

Note –  in certain reports and in certain versions of Crystal a SQL Expression will error with:

“The multi-part identifier [your field] could not be bound.”

This is a Crystal bug and here is the work around.

When CR 2011 was released SAP also introduced Crystal Reports for Enterprise. SAP stated at that time that they were going to focus their future development efforts on the newer product. They also stated that they would continue to support the standalone product, but the wording left some of us wondering how long that support would last.

Recently one of my newsletter subscribers sent me a link to an SAP wiki page. The page explains the difference between Crystal Reports for Enterprise and “classic” Crystal Reports. He found this statement in the overview:

“We will continue to support the Crystal Reports 2011/2013 line of products after Crystal Reports for Enterprise has caught up [with the features in CR].” It isn’t a promise of indefinite support but perhaps they won’t discontinue the stand alone version of Crystal reports any time soon. I certainly expect to be supporting Crystal for many more years.

And thanks to Joe Gaietto of Ohio MHAS for sending the updated link.

I received an Email from a desperate customer this weekend. They are on the last few months out of a legacy system and had to improve security to the database by requiring a password for running Crystal reports. That  isn’t normally a problem because Crystal will open the User/PW dialogue.  But they run their reports through the legacy application. When this application tries to run a report that requires a password dialogue, the report viewer crashes.  I am told that they can’t use integrated authentication and the also can’t rewrite the legacy application.  So they were looking for a way to store the passwords in the report so that there wasn’t a password dialogue.  I have been asked this before  and thought there was a way to store the password in the connection, so I did some research on their options:

1) No, there is no way that an RPT will store a password.  This is has always been true of Crystal, and it is by design.

2) It appears that in the past you could create an ODBC DSN that stored passwords. This is no longer allowed for security reasons.  To be sure I created a file DSN and specified the user, password and database.  At runtime the login dialogue showed the stored user and database, but ignored the password.  Most of my customers people solve this problem with integrated authentication, where your DB login is tied to your network login.

Actually, this was the first time I have created a File DSN so I learned something new.  A File DSN is a small text file with the extension DSN.  You write it in any text editor. It has the same information that you use when you create a system DSN.  The difference is that a normal DSN’s info is stored in the registry, while a file DSN is kept in a text file.  It can therefore be copied from one PC to another or stored on a central server to be read by many different workstations.  It reminds me of a UDL (see below)

3) If you use OLEDB instead of ODBC you can create a UDL file.  This is a small text file that has the extension UDL.  You create it as a new text file and change the extension. When you right-click on a UDL file you see a “properties”  option that opens a special dialogue where you can enter connection information. Using a UDL file is one option for this customer to get up and running.  This is because a UDL can remember the password for the database, unlike a File DSN.  But it isn’t an ideal solution because the password is stored in plain text.

4) A last resort option is for this customer to deploy a client based viewer. The down side is that they would have to run the reports outside of the application.  With this option they can either have the user enter the password, or they can use a viewer that can encrypt and remember the password.

This customer chose to start with a UDL, because it got them up and running immediately.  If they later decide it is too much of a risk to store the passwords in the file they will move to a viewer.

Related Links:
DSNs and UDLs
Creating a File DSN
Creating a UDL
Microsoft on creating UDL files

It seems just about any time I am shopping for something I find a larger selection and lower prices through Amazon. Competing with Amazon is an obvious challenge for any business.  So when Amazon announced their own cloud based BI product called QuickSight it got my attention. QuickSight will allow users to visualize their Amazon Web Service (AWS) data as well as data from other sources. Amazon claims that their price is 1/10th the cost of local BI options. Their Standard Edition is $108 per user per year, with the Enterprise Edition being twice that.

QuickSight is not limited to reading AWS cloud data. They are providing outbound connectivity so you can also analyze data that you store locally and data in other cloud services like SalesForce. All of the data is then processed by a new calculation engine they have created named Spice.  QuickSight has it’s own visualization designer, but Amazon also provides inbound connectivity so users can tap into the Spice engine from partner tools like Tableau and Qlik.

According to an article on VentureBeat.com, cloud based BI is on the upswing from many vendors:

The rollout of [QuickSight] comes a couple of months after Microsoft’s cloud-based business intelligence service, Power BI, became generally available. And last year IBM brought its Cognos business intelligence software to its SoftLayer public cloud. Salesforce came out with its comparable Analytics Cloud last year, while startups like BIME, Birst, Domo, and GoodData offer standalone cloud BI tools.

They don’t mention that SAP has put their analysis engine, Lumira, in the cloud.  You can get a free (trial) Lumira account just by registering.  So it sounds like the future of both large scale data storage AND large scale data analysis may be in the cloud, with the biggest cloud provider of them all taking the lead.