Reports for CRM 4.0 using SQL Server 2008 and Report Builder 2.0
Full article link: http://blogs.javista.com/2008/11/12/reports-for-crm-40-using-sql-server-…
SQL 2008 Reporting Services
SQL 2008 Reporting Services introduces a brand new set of components to be used in reports. A wide variety of chart types, gauge types and matrix reports have been introduced to provide the Dynamics CRM user extremely useful reporting tools and a fantastic user experience. For example – You can use a sales funnel report in SQL 2008 to show the distribution of sales opportunities in a funnel chart, and use a gauge to track the overall effectiveness of your sales team.
Report Builder 2.0
Another powerful tool that ships with SQL 2008 is the Report Builder 2.0. The tool is a boon to report writers who wish to create powerful reports. Personally, it took me, not more than 10 minutes to create my very first report using Report Builder 2.0. Once created using the Report Builder, these reports can be uploaded into CRM. Report Builder 2.0 can also be used for creating CRM reports by a CRM administrator having access to the SQL database. (For information on ReportBuilder 2.0 see http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4…)
General approach for creating a Report
If you want to build a report using ReportBuilder 2.0 the following is the general approach that you should follow –
1. Define the Data Source – ReportBuilder 2.0 provides a wizard that allows you to build your data source.
2. Define the DataSet – Select the tables/columns from this data source that you wish to include in your report. The QueryDesigner in ReportBuilder 2.0 allows you to select your columns, specify the necessary joins between the tables, and specify the filtering criteria for your query. After defining, you are ready to build your report.
3. Select and customize the components that you would like to include in your reports. The following components are supported in reportBuilder 2.0 – Charts, Gauges, Tables, Matrix, and List reports. A wizard is provided for customizing each of these components. Each component specifies the dataset that it will use for fetching its data. Once you specify the dataset for the component, the wizard asks you to select the various pieces of data that will be included in your report. For example, while creating a matrix report, the matrix report prompts you to select the rows, columns as well as the aggregate fields.
4. After you have defined your report by selecting the various report components, you can run and preview your report with Live Data.
Using reports within CRM
After defining the reports using ReportBuilder 2.0 you can upload them into CRM using the standard process of uploading RDL files. If you are a CRM customizer you can also go ahead and customize IFRAMES to show these reports.
Creating a report using report Builder 2.0
Step 1 – Start with a Blank Report
This is the blank slate from where you start. This screen by itself is helpful to get you started on creating a table, matrix or chart report.
Step 2 – Define the Data Source
You now define your data sources. The wizard here lets you construct your data source.
Step 3 – Define the DataSet
You now define your dataset. Select the CRM entities which your report will be based on. In most cases the DB name of an entity is close to the CRM name of the entity – for example CRM Accounts entity is called AccountBase and CRM Opportunities entity is called opportunityBase. The Query Designer automatically infers the Join type between these entities, but you can always override this and choose your own join. You can also define your filter here for the query. After these you can preview the data from this query using the Run Query feature.
Step 4 – Select the components that you want to include in your report
The Insert tab within ReportBuilder 2.0 shows the components that can be added to the report. The components supported by reportBuilder 2.0 are Table, Matrix, Chart, gauge and List. You can drag and drop multiple such components on your report. Almost everything about these components is customizable by the user – the size, component type, colors, font etc.
Step 5 – Define your components
A wizard helps you define your report components. The first step in most components creation is to choose the Dataset.
Step 6 – Specify your component’s properties
Select the data from your dataset which should be fed into these components. Since we are creating a chart component I have selected the Account’s name as the category and the Sum of Estimated Revenue for that account as the Measure. Multiple categories, and series can be used in a chart.
Step 7 – Finish adding all components and complete the report
Customize the component to your needs. As mentioned before almost everything is customizable.
Step 8 – Run and preview your report
Run the report and see how a real report will look. You can seamlessly move between the Design and Preview modes.
Some Examples of ReportBuilder 2.0 reports for CRM Users
Creating Sales Funnel Reports using Report Builder 2.0
One of the most frequently used reports in the CRM context is the Sales Funnel report that shows the opportunities by their stage in the sales pipeline. Creating a Sales Funnel report using ReportBuilder 2.0 is fairly simple..
Matrix Reports
Matrix reports are a new addition in SQL 2008 Reporting Services. Matrix reports allow you to depict a 2 dimensional view of your aggregate data.
Gauges
A gauge allows you to depict Key Performance Indicators(KPI’s) . A report with the right collection of charts, matrices and gauges can act like a dashboard.
Integrate Reports into CRM
Any report created using report Builder 2.0 can be uploaded into CRM since it is an RDL report. Reports uploaded into CRM can either be individually owned or Organization owned. Only CRM administrators/customizers You can create an Organization owned report . Once uploaded into CRM these reports can be used like any other CRM report. Additionally CRM customizers also have the flexibility of embedding these reports into IFRAMES and using including them inside CRM pages for a more contextual reporting experience.
Conclusion
The richness provided by SQL Reporting Services and Report Builder 2.0 is evident. CRM users and customizers will not take long to figure novel and innovative uses of these reports within CRM. These two tools together promise a great end user value both for report users and report authors, and at the same time add a great coolness factor to their experience of using reports.