[Obsolete] SSRS in MS Dynamics CRM

Working with SSRS in MS Dynamics CRM

Her you can hind some small tips about SQL based reports, which will save your time and will help you to extend quality of your reports.

Common principles of building SQL datasets

Minimization

1. Do not use *. List only columns which are required for the report.
2. Get rid of useless JOINs and subqueries.
3. Understand your JOINs. Make sure that using JOINs is really needed in each case.Use a joined entity, use INNER JOIN.

Valid concatenation

While calculating and concatenating, take notice if a field can be NULL.

Remember that: NULL + 'Value' = NULL 

Use IsNull or Coalesce  functions

e.g. select lastname + IsNull(firstname, '') from ...

or    select Coalesce(lastname ,' ' , firstname) from ...

CRM specifics in reports

Using filtered views

Use Filtered Views in queries to be sure that a user who runs a report has required permissions for requested data. By the way it's only supported way of designing SQL based reports

Using CRMAF pre-filtering

1. Do not use CRMAF prefix in sub-reports, because it will be changed to default filter (e.g. “modified on the last 30 days”)
2. Do not use CRMAF prefix in queries with Union, because only first occurrence will be changed.
3. Use it in case of oriented for a particular type of entity reports.
4. There is one and only one dataset where CRMAF is used and applied for the single filtered view.

Common report design problems

Working with parameters

Use parameters wisely. Take notice that if the parameter value is calculated based on query result, this query will be run before report generation. So if N parameters are based on query result, the query will be run N times. Even if parameters are calculated based on the same dataset, query will be executed separately for each parameter.

Report layout

Sometimes possible that additional empty pages will be generated while exporting a report to Word, PDF etc. The main problem of empty pages is setting the wrong report size. Use the following formula to avoid such a situation:

 

Report.Width = Report.Left + Report.Right + Body.Width 

 

Use the same measurement (cm or in) in the whole report, to prevent unexpected behavior due to conversion issues (roundings).