Using Crystal Reports in SharePoint

Picture this:



You have a WSS 3.0 installation so you do not qualify for PerformancePoint to display your metrics. Is it a lost cause? Is there no way to easily display metrics for your company and deliver them to the people they need to go to?



It is not a lost cause. You can integrate Crystal Reports into SharePoint and using the drill down functionality in Crystal deliver in-depth information.



My most recent project was to create basically a dashboard to display important metrics for different departments in our company. The dashboard would be accessible to all people in the company (or as security allows) based on SharePoint permissions. The dashboard would be its own site in SharePoint and would also allow the user to drill down on some metrics.



So to start I created a SharePoint site and stripped out the Quick Launch and Top navigation as these were waisting valuable space.



Another of the requirements for this project was to match the functionality of an existing .Net windows app dashboard but to run it in SharePoint so that it could be accessed from anywhere. One of the pieces of the dashboard was rotating images that showed off new products, company functions, and other noteworthy items.



I was able to achieve this in SharePoint by using some Javascript and pulling all the photos from a Photo Library in SharePoint and flipping through them with the Javascript.



A second requirement was that the current dashboard refreshed the data every so many seconds to keep it "real-time". This was a little more tricky to achieve in SharePoint but not impossible. I simply used another piece of Javascript - history.go(0); to refresh the page. Ideally I would like to eventually try and integrate AJAX, so that the page does not need to refresh to load the new data.



So now the next thing was to integrate the Crystal Reports viewer to allow the reports to be shown in the web page. I found the CrystalDecisions.web.crystalreportviewer class and was able to integrate that right into SharePoint. You need to copy the aspnet_client folder that resides in teh web root and copy it into all of your SharePoint virtual directories, otherwise you run into broken images in the viewer.



Once I had the control in place I tried a report and was running into a login prompt asking for the username and password for that connection. I tried placing this information in the code and that did not help. So finally after doing some searching online I came across a post where the user had the same issue and he found that it was an issue with his data provider, so I though about it for a minute and realized that the web server did not have the data provider set up. So I set it up and the report worked like a charm.



Another issue I ran into was the number of controls I had on the page. In order to display all of the metrics the number of controls easily went into the hundreds. By default in the web.config SharePoint only allows for 200 controls on a page. I had to modify this to get my page to work. You will know if you hit this number as the error spells out that you are over the controls limit. Raising this limit can have performance issues so don't just change it without testing the performance of the page and the SharePoint installation.