Crystal Reports / SSRS

[s1monb]

Hi

 

We are currently looking into using Crystal Reports to give a wider audience access to some headline data from our MIS.

 

I would really like to hear from anyone who has done the same and wonder if they could share success stories/potential pitfalls. (We currently use Serco Facility)

 

Thanks

 

Simon

[Pico]

I guess Crystal Reports are much like SSRS? I'm using SSRS and staff access the reports online via the MIS. The reports use SQL as the query language. The reports use drill-down and drill-through, have breadcrumb trails to aid navigation and use the staff login to work out what data they should be allowed to see. (Any parameters in reports are adjusted accordingly.) Overall, I'd say this has worked pretty well.

 

I used do the analysis in Excel using a custom-built add-in. That was pretty powerful, but the code had to both process the data and format the spreadsheets. With SSRS, the presentation side is handled in a drag-and-drop environment and I can pretty much think about the data and calculations independently of this. Much more convenient!

 

Here are the main areas I found myself concerned with. I apologise for the length, but I hope you find it useful for thinking about what you want to do with your reports. I guess that really depends upon the scope of your project.

 

* Advanced planning is essential. You'll want to classify the types of reports you need and whether apparent differences can be handled by varying report parameters. You can't do this without knowing who your stakeholders are and what they want to be able to use the reports for.

 

* Types of calculation required. If you can create views or share data sets between reports, then you can share calculations and data between reports. The same principle applies to custom code in your reports. In Reporting Services you can create assemblies that can be shared between different reports.

 

* Availability of data required. In my case, I had to supplement the MIS with a database that contained additional data. I needed to do this because although there is scope within the MIS to add custom fields and groups, I needed to define internal relationships within that custom data and between that custom data and other data already held in the MIS. (Synonyms took care of cross-database queries.)

 

* Report data access restrictions. You'll need to ensure that stakeholders only see the appropriate kinds of data (if any!). You may also need to think about server load, e.g. your report might use a data snapshot, rather than pulling live data. (Alternatively, you may be able to set up subscriptions that save the report to a location according to a set schedule.) On the subject of server load: the report format may affect whether or not the rendering is processor or memory intensive.

 

* Report design. I regard this as pretty important, because I'm not good at this. If the reports are designed to be viewed on screen, they need to work within the restrictions of the screen. Modulo whether staff understand what the data and analysis means, the way in which that information is presented can make it easier or harder to understand what the report is telling them.

 

* Report interactivity. If you want to have drill through reports, you'll need to think about which parameters need to be exchanged between reports. If reports have user-selectable parameters, then the back button on the browser might not work as expected. (I got round this by placing a breadcrumb trail in the report itself.) You'll also need to make sure that your parent report passes sufficient parameters to the drilled-to report so that when the user does go back, those parameters are returned back to the parent report so that it displays correctly. You may also need to think about how to control such links if your reports can be exported to different formats. I made the decision to only allow the links to be usable if the reports are viewed on screen, e.g. if the report gets exported to Excel, they are either removed or become plain text. You'll also need to check whether report parameters (internal or otherwise) get exposed, e.g. via URLs if you are providing direct URL access, and are manipulable by the user.

 

* Future requirement changes. There'll be some things that you will work on now, leaving others until later, and other things that nobody has suggested yet, but might do in the future. You'll need to approach things so that you have that flexibility without having to keep starting from scratch. Omniscience would be a desirable skill

[vikpaw]

Out of interest why Crystal? I've heard it can be difficult to get your head around. I'm sure for a techy it's fine, but i'd be tempted to use SSRS as well. Plus i'd imagine it's easier as serco is SQL based and gives you full access to it all right?

[Steven_Cleaver]

Have used Crystal a bit at my previous school as it was the backend of the Reporting system we used and as vikpaw has said can be a lot to get your Head round as calculations and structuring the data are very differen't. Thanks to Pico and Vikpaw for SSRS bit looks interesting might have a look at this.