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-4eea-b1d5-f3140a253db6&displaylang=en)
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 –
A few screen shots of ReportBuilder 2.0 are shown below. A look at the screen shots makes it clear that the tool is both easy and intuitive.
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. There are some screen shots here of reports being used within a CRM context.
Creating a report using report Builder 2.0 - Screen Shots
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. As you can see 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.(Not shown here)
Step 6 – Specify your component’s properties
Select the data from your dataset which should be fed into these components. Here, 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 are a new addition in SQL 2008 Reporting Services. Matrix reports allow you to depict a 2 dimensional view of your aggregate data. Following is a screen shot of a report that shows the Estimated revenue for each account, by the different pipeline stages that the account’s opportunities are in.
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. For example the following screen shot shows how a small report containing a Gauge, can be included on the Accounts form to indicate the Account’s profitability.
The richness provided by SQL Reporting Services and Report Builder 2.0 is evident from these screen shots. 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.
PingBack from http://www.tmao.info/reports-for-crm-40-using-sql-server-2008-and-report-builder-20/
Can you publish the IFrame sample in the last screen shot working in outlook offline mode ?
Quick question. Can customers leverage these capabilities by upgrading just SQL Server Reporting Services to 2008 or do they need to upgrade their database engine to 2008 as well?
I've always considered the Report Builder tool that ships with SQL Reporting Services to be a good
Please provide the details regarding how to upload RDL report to CRM
Have you tried taking reports written with 2008 offline - I can only get reports written in VS 2005 to work with Outlook client offline. Any report written with 2008 won't work..What am I doing wrong?
Dear Liam, I have the same problem, did you sort it out? The client likes gauges but they arent available in VS 2005, reports created in 2008 dont work offline...
Please do not report against anything but the FilteredViews. Anything else is not supported
Check you offline client version of sql express it is prob 2005.
I’m using Visual Studio 2008 with BIDS to design my Reports. I need to upload them to a CRM 4.0 application, but the Reporting Services backend of that CRM is SSRS 2005.
Is there any possibility of uploading those Reports to the said CRM?
Do I have to upgrade Reporting Services backend to SSRS 2008?
Thanks in advance!