By Mackenzie Anne Platten – Senior Financial Accounting Systems Analyst – TD Carpenter & Associates
As reporting needs are migrating to applications that can easily integrate with multiple platforms and databases, we often come across the need to take a look at our current set of reports and determine what is the best platform for a consistent continuously evolving reporting system. Microsoft SQL Server Reporting Services seems to be the latest reporting solution for Microsoft ERP systems as it is free with SQL Server, reports are fairly easily managed, they have a more intuitive report builder, and they can use various underlying code such as SQL or ASP .NET.
The dilemma we often face is “When are we going to have time to migrate our reports”?
This article describes the “Super Easy” process of taking Crystal reports and easily copying code over to SSRS reports. Using the report wizard, the process takes minutes and you can have a set of reports ready for your report server in less than an hour. This article does not go into detail on how to install Visual Studio, setup Report Server, or how to load reports.
My Crystal Reports were maintained using Visual Studio 2005, but you may use the Crystal Reports application to do the same thing. Let’s open Visual Studio to take a look at one of our Crystal Reports.
I have a Crystal reports solution that I created to make edits on all of our canned and customized reports. Here is a report entitled “Applied Credits by Customer”:
In order to easily Migrate this report to SSRS. I’ve created a new SSRS Solution in Visual Studio 2010 (2010 is SSRS compatible) that contains all of my converted SSRS reports. In the Solution Explorer, right click on the Reports folder and select “Add New Report”.
Select Next on the Welcome to the Report Wizard
You have the option of using a Shared Data Source or you can specify the connection string.
Click the Next button and you will see the Design the Query Window:
This is the fun part…
Go back to Visual Studio 2005 and select Crystal Reports – Database – Show SQL Query
Copy the code from this screen:
Go back to Visual Studio 2010 and paste the code into the Design the Query Window:
Click the
Select the Tabular option and click the Next button.
Highlight your available fields and assign them to the Page header area, the Group area or to the Details area:
Click the Next button and select the table layout preferences in the Choose the Table Layout window. Notice that you can include subtotals and drilldowns. These options will save you time from having to add them to the report as calculate fields.
Click the Next button and select one of the Table Style layouts. Slate is my favorite.
Click the Next button and make sure to replace the Default Report Name with your actual report name:
Click Finish! It will show you the Design area first so you can add your company logo and other formatting options.
Click the Preview Tab to see what it looks like! Here is a sample of a report I took a few extra moments to format and to add filter parameters. I removed the customer name for confidentiality purposes:
For more information designing SSRS reports using Visual Studio, refer to: Click here
Contact me directly if you have any questions: mackenzie@tdcarp.com
Check us out at www.tdcarp.com