Crystal Reports vs SSRS #2

Tuesday 11 June 2013 @ 1:48 pm

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.

In SSRS you have to start by writing some SQL, which means you have to know exact table names and field names before you start. For me this is frustrating, because 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.

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. If you add a new field that wasn’t already in the dataset, or change linking options, etc Crystal will then force a refresh.

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.  It is very simple to do in the CR Database Expert and even allows for specifying INNER and OUTER joins across the connections.

I can’t see any equivalent feature 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.

2 Responses to 'Crystal Reports vs SSRS #2'

  1. Butch - June 20th, 2013 at 3:12 pm

    Sorry I didn’t see this post sooner, I’m a little behind on my RSS reading.

    I work with MS Dynamics SL so I use both quite often as both are integrated into the product. The two definitely have a different development model, but I can usually solve reporting needs with either solution. CR might be slightly more end user friendly – hard to say.

    To answer you points above:
    1. When you created a new Dataset and select a Data Source, you can open the built-in query designer and visually build and test your query.
    2. You are correct. No tweaking in preview mode.
    3. There is no joining across servers for sure. Creating a linked server is probably preferred for the scenario you describe. There is also the possibility to solve it by creating subreports. Subreports can take parameters from the parent and query info from a separate server set up by adding a second Data Source.

  2. Ken Hamady - June 24th, 2013 at 4:33 pm

    I traded some Email with Butch about the Query Designer, because in my install of Report Builder all I get is a blank box for typing SQL. After comparing notes we discovered that Report Builder only provides the graphic Query Designer if you connect directly to SQL Server. If you use ODBC or OLEDB you have to write SQL to get started. Butch later told me that if you are in BIDS you get the graphic Query Designer for ODBC as well.

Leave a Reply

Recrystallize Pro