SSRS Reports in MSCRM 2011

Following are some general questions about SSRS development to see if general working knowledge of SSRS is known.

      Different kinds of SSRS Reports?

 

• Reports can be categorized into operational and analytical reports. The distinction is based on the source of data and level of analysis facilitated by any particular report. Operational reports are based on OLTP sources and are static reports and Analytical reports are based on OLAP sources and generally facilitate drill-down and drill-through for analysis. Technically, SSRS reports can be categorized into parameterized, linked, snapshot, cached, etc...  

Pre-filtering in SSRS reports:

You can use CRMAF_ prefix or CRM_FilteredEntity parameter in report to enable data pre-filtering. Using pre-filtering concept, you can enable the user to run report only for the selected records in MS CRM.

·         CRMAF_ prefix on filtered views in sql query, makes the report Pre filterable in MSCRM.

·         The CRM_FilteredEntity parameters are used in a query expression to enable data pre-filtering (through Advanced Find) on a filtered view.

Group By and Having function in SQL

Group By clause

Group By is used in conjunction with aggregate functions to collect data across multiple records and group the results by one or more columns.

Example: The below query returns the departments and the number of employees working in the respective departments of the company.

Select departments, count(employees) from company group by departments

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

Having clause:

The SQL HAVING clause is used in combination with the Group By clause. It can be used to filter the records that a SQL GROUP BY returns

Example: The following SQL statement retrieves the employees who have registered more than 10 orders:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

Reports:

The report definition (data and layout) of Microsoft Dynamics CRM reports are contained in an .rdl file, and the contents of the .rdl file conform to the Microsoft SQL Server Report Definition Language Specification.

Microsoft Dynamics CRM provides many out-of-box reports for viewing your business data. You can create custom reports using one of these reports as templates or can create a custom report from scratch.

There are two types of reports in Microsoft Dynamics CRM:

SQL-based

These reports use SQL queries to securely retrieve data for reports from filtered views defined by the system. These are the same reports that have been available for previous versions of Microsoft Dynamics CRM. The default reports that are shipped with Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online are SQL-based reports.

For security reasons, you cannot deploy custom SQL-based reports to Microsoft Dynamics CRM Online.

Fetch-based

These reports are introduced in Microsoft Dynamics CRM 2011 and use FetchXML queries to retrieve data for reports. You can deploy custom fetch-based reports to Microsoft Dynamics CRM Online and to on-premises Microsoft Dynamics CRM 2011. All reports that are created using the Report Wizard in the Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online are Fetch-based reports.

Reports in Microsoft Dynamics CRM Online run in sandbox mode, and this is done by enabling RDL sandboxing in Microsoft SQL Server Reporting Services. As a result, certain features might not be available in Microsoft Dynamics CRM Online. For example, custom code in your report definition will not work.