CRM SSRS Reports Enabling Data Pre-Filtering on Reports

Enabling Data Pre-Filtering on Reports
There are two ways that you can enable data pre-filtering on Microsoft Dynamics CRM reports: automatic and explicit. The following sections explain these options.

Automatic Pre-Filtering

To enable automatic data pre-filtering on a report, you can alias entity tables in queries by using an alias name that starts with "CRMAF_".
Automatic data pre-filtering is suited for simple queries. For example, the following table shows a simple query modified to enable pre-filtering on the Account entity.
Query without pre-filtering Modified query with automatic pre-filtering enabled

SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount;

SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount;

When you enable automatic data pre-filtering functionality using the "CRMAF_" prefix, Microsoft Dynamics CRM modifies the query to include a parameter (for example, P1) when it is uploaded to Microsoft Dynamics CRM, as shown in the following table.

Query with automatic pre-filtering Modified by Microsoft Dynamics CRM
SELECT <column1>, <column2>, <columnN> FROM FilteredAccount AS CRMAF_FilteredAccount; SELECT <column1>, <column2>, <columnN> FROM (@P1) AS CRMAF_FilteredAccount;

Microsoft Dynamics CRM will pass a query to the P1 parameter depending on how the report is being filtered. In other words, automatic data pre-filtering acts as a sub-query within the existing query.

The following examples illustrate how Microsoft Dynamics CRM passes queries to the parameter (P1) as per different filtering requirements. In these examples, it is assumed that you are running the report from the Reports area in Microsoft Dynamics CRM, and are using the data filtering option.

Example 1: If you want to view only active accounts, the resulting query would be as follows:

SELECT <column1>, <column2>, <columnN>
FROM (SELECT FilteredAccount.* FROM FilteredAccount WHERE statecode = 0)
AS CRMAF_FilteredAccount

Example 2: If you are within a specific account and run the report, the resulting query would be as follows:
SELECT <column1>, <column2>, <columnN>
FROM (SELECT FilteredAccount.* FROM FilteredAccount WHERE AccountId = '<CurrentAccountId>')
AS CRMAF_FilteredAccount

Example 3: If you are looking at a list of three selected accounts and you choose the option to run the report against the selected records, the resulting query would be as follows:

SELECT <column1>, <column2>, <columnN>
FROM (SELECT FilteredAccount.* FROM FilteredAccount WHERE AccountId in ('<1stAccountId>', '<2ndAccountId>', '<3rdAccountId>') 
AS CRMAF_FilteredAccount

When any entity table names are aliased, the Advanced Find user interface is automatically included in the deployed report when it is run from Microsoft Dynamics CRM.

To alias an entity table name in Query Builder, right-click each table in your report, click Properties in the shortcut menu, and enter the alias value in the form CRMAF_FilteredEntity, for example, CRMAF_FilteredAccount.

Limitation of Automatic Pre-filtering

When you use the "CRMAF_" prefix to enable automatic pre-filtering, Microsoft Dynamics CRM has to add a parameter in the query. With a more complex query such as a query using UNION statements, this can lead to unexpected results because Microsoft Dynamics CRM might only add the parameter to the first query.

For example, consider the following query containing UNION statements:

SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'FL'
UNION
SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'CA'
When you upload the report, Microsoft Dynamics CRM might filter only the first query using the parameter. This leads to the filtering not being applied to the second query:
SELECT <column1>, <column2>, <columnN>
FROM (@P1) AS CRMAF_FilteredAccount WHERE address1_stateorprovince = 'FL'
UNION
SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'CA'
In the above example, while running the report from the Reports area in Microsoft Dynamics CRM and choosing the filter as annual revenue greater than 1,000,000, Microsoft Dynamics CRM will pass a query to the P1 parameter as follows:
SELECT <column1>, <column2>, <columnN>
FROM (SELECT FilteredAccount.* from FilteredAccount where AnnualRevenue > 1000000) AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'FL'
UNION
SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'CA'

This implies that the query would return only those accounts in Florida with an annual revenue greater than $1,000,000 and all the accounts in California, which is not what you intended. You wanted to view all the accounts in Florida and California with annual revenue greater than $1,000,000.

If you download the report from Microsoft Dynamics CRM and open it in Visual Studio, you will see the original version of the report that you uploaded into Microsoft Dynamics CRM. If you download the report directly from SQL Server Reporting Services, you will notice that Microsoft Dynamics CRM had modified the query but did not place the parameter where you wanted it to be.
For complex queries like this, you must use explicit pre-filtering.
Explicit Pre-Filtering

For complex queries such as queries using UNION statements, you might need to use explicit pre-filtering for your data. Unlike automatic pre-filtering, Microsoft Dynamics CRM does not rewrite the report query by passing values to the parameters during explicit pre-filtering when such a report is uploaded to Microsoft Dynamics CRM. You have to explicitly make the required changes to the report by adding the pre-filtering parameter to the report, and then referencing the parameter in the query. You can then execute the query using dynamic SQL.

When you use dynamic SQL, filtering through Advanced Find is enabled by creating a hidden parameter named CRM_FilteredEntity, for example, CRM_FilteredAccount, and by using this parameter in a dynamic SQL query expression. This parameter enables filtering on the table data obtained from the specified filtered view.

Using the same example as discussed earlier to highlight the limitation of automatic pre-filtering, the following table shows a query with automatic pre-filtering modified to use explicit pre-filtering using dynamic SQL. It is also assumed that while running the report from the Reports area in Microsoft Dynamics CRM, the filter has been applied as annual revenue greater than 1,000,000.

Microsoft Dynamics CRM Online 2015 for developers

Calculated Fields:
 You can create a calculated field for any data type, except Multiple Line of Text, Image, or Lookup fields

Rollup fields benefits

• Visual editing is easy. You can create rollup fields by using the Field Editor, just like you do when you create a regular field.
• Wide selection of aggregate functions. You can aggregate data by using the following functions: SUM, COUNT, MIN, MAX and AVG.
• Full filter support for aggregation. You can set various filters for the source entity or related entity while setting multiple conditions.
• Seamless integration with the user interface. You can include the rollup fields in forms, views, charts and reports.
• Rollup fields are solution components. You can easily transport the rollup fields as components between organizations and distribute them in solutions.
• Rollup fields and the calculated fields are complementary to each other. You can use a rollup field as a part of the calculated field, and vice versa.

Some examples of rollup fields include:

• Total estimated revenue of open opportunities of an account
• Total estimated revenue of open opportunities across all accounts in a hierarchy.

Rollup fields Limitations:

Workflows aren’t triggered by field updates
If you have created workflows in the past, you are aware that a workflow can be triggered on the update of a field. This is certainly true…with the exception of calculated and rollup fields.

Latest values not available in plugin create/update pipeline
A similar, yet certainly different, limitation is that plugins won’t trigger off of the update of a calculated or rollup field. While neither the workflow or plugin limitations are all that significant (at least in my mind) it is certainly something to be aware of as you are designing your solutions.

Can only have all ANDs or all ORs in Conditions
When creating conditions, you have the ability to use “and‘s” or “or’s”. However, if you need to use multiple “and’s” or “or’s”, you cannot combine them. For example, if you look at the screen shot below, you’ll see the logic of this condition is using only ‘and’s’. If you were to try and change one of them to an ‘or’, it would automatically change the other to an ‘or’.

Not available for offline
When operating Microsoft Dynamics CRM through Microsoft Outlook, you have the ability to operate it either connected (online mode) or disconnected (offline mode). When operating in offline mode, calculated fields do not calculate. Once you go online, however, they certainly will.

Can only go 1 level up in N:1 relationships
As was discussed in an earlier article, when using lookup fields in your calculated field scripts, you can only go up one level to pull data. For example, if I had an account named Company Z and its parent account was Company Y, I could grab data on the Company Y record. If, however, I wanted to grab a value from Company Y’s parent account while I was on the Company Z record, I could not capture any of that particular data in my calculated field script.


Rollup fields Considerations:

• You have to save the record before calculated field is updated.
• Only Calculated fields using all simple fields can be sorted.
• You cannot subtract two dates and determine the number of days, months, year’s difference they have between them.
• cannot use “+” or “&” when doing concatenations.
• can only use string values in my concatenations.
• Not all lookups available when looking across entities in calculated fields.
• Floating point numbers cannot be used in calculated fields.

Rollup Fields:

Rollup fields are used to perform record level aggregation from related records.  For example, if you wanted to have a field on the Account that rolled up the Estimated Revenue for all Open Opportunities related to the Account, you could easily identify at the Account level what the Total Open Revenue was and then know what Accounts you should focus most of your time and energy on.

Similar to Calculated Fields, Rollup Fields do not support all data types.  Rollup fields are only available for Whole Number, Decimal Number, Date & Time, and Currency fields.  The table below shows how you can rollup child data using the rollup fields for the different data types.

Data Type	COUNT	MAX	MIN	SUM
Whole Number	x
Decimal	x	x	x	x
Currency		x	x	x
Date & Time		x	x

Similar to Calculated Fields, when you’re creating your field, you’ll see an Edit button appear if you choose Rollup for Field Type.
 

Rollup field Limitations & Considerations

• Rollup fields cannot be used as workflow trigger fields or in wait conditions.
• Modified on/Modified by fields do not get updated each time the rollup fields are re-calculated.
• Rollup fields cannot be audited.
• No more than 100 rollup fields per organisation and 10 per entity may be added.
• Child fields cannot also be rollup fields
• Child fields if marked as calculated cannot reference fields outside of the entity on which they reside.
• Roll-ups are not available for N:N relationships, they will only work with direct 1:N relationships.
• When a rollup field is added, two additional fields are added to store the ‘state’ and ‘last calculated’ date.
• Adding a rollup field will execute the calculation for all records in the system the first time the system job runs. This should be considered & tested of millions of records exist.
• If a hierarchical field is selected for the rollup field, it will calculate to a maximum depth of 10.
•  Once a field is created, you cannot change the “Field Type”. This must be set at creation of the field.
• Roll-ups operate on the asynchronous process, and the roll-up is performed every hour. However, you can also trigger them via pressing the “refresh” icon in the actual roll-up field on the form:

RollUP Field Status

State Value	Description
0	NotCalculated: Attribute value is yet to be calculated.
1	Calculated: Attribute value has been calculated per the last update time in <attribute SchemaName>_Date attribute.
2	OverflowError: Attribute value calculation lead to overflow error.
3	OtherError: Attribute value calculation failed due to an internal error, next run of calculation job will likely fix it.
4	RetryLimitExceeded: Attribute value calculation failed because the maximum number of retry attempts to calculate the value were exceeded likely due to high number of concurrency and locking conflicts.
5	HierarchicalRecursionLimitReached: Attribute value calculation failed because maximum hierarchy depth limit for calculation was reached.
6	LoopDetected: Attribute value calculation failed because a recursive loop was detected in the hierarchy of the record.

 
Alternate keys

Alternate keys enable data integration in an efficient manner. Users can now define an attribute in a Microsoft Dynamics CRM entity to correspond to a unique identifier (or combination of columns) used by an external data store. Use this alternate key to uniquely identify a record in CRM in place of the primary key. This feature enhances the developer and customer experience by:

Reducing roundtrips to look up record IDs from other unique columns.
 
Increasing overall throughput of bulk data processes, especially with CRM Online.
 
Simplifying programming from external systems without CRM record IDs.
 
Change tracking
You can greatly improve efficiency of code that monitors changes to CRM data by using the RetrieveEntityChangesRequest message included in this release. The new change tracking feature provides a way to keep the CRM data stored in an external data source, synchronized in a performant way by detecting what data has changed since the data was initially extracted or last synchronized.

Upsert for updating CRM with external data
The new UpsertRequest message reduces the complexity involved with data integration scenarios by doing an update if the record already exists or a create if the record doesn’t exist. This is especially useful when you want to set the state of a record in CRM and don’t know if it already exists.

Optimistic concurrency
On a multi-threaded and multi-user system like Microsoft Dynamics CRM, operations and data changes often happen in parallel. A problem arises when two or more update or delete operations on the same piece of data happen at the same time. This situation could potentially result in data loss. New in this version is the ability for your applications to detect whether an entity record has changed on the server from when your application retrieved the record to when it tries to update or delete the record. Attempting to update or delete a record that has been changed by another user results in an error condition.

Custom actions in workflows or dialogs
Developers can still call custom actions using code, but now actions can also be called directly in workflows or dialogs.



 

Improved Business Rules In CRM 2015

Introduced in Dynamics CRM 2013, Business Rules enable logic execution to be applied to databases using in built tools which avoids the need, and expense, of writing custom scripting.

By configuring Business Rules, CRM is more prescriptive for users by
dynamically enforcing conditions on forms and process flows based on the data entered in other fields.

Popular examples for Business Rules include controlling which fields and actions
are shown on Cases record based after an initial product selection guiding users to a resolution. Also, Rules can be set to fire error messages on Lead records if invalid field entries are entered during the qualification process.

However, in the 2013 release these were subject to a few limitations.

Firstly, Rules would only support simple business logic. As a result, for many
conditional logic rules administers would still need to resort to external coding.

Also, in CRM 2013 Business Rules were restricted to running on the client side
only. Business Rules which fixes this these restrictions and adds wider functions.

This includes:

• Support for enriched business logic including if / else
• Combining expressions using and / or conditional rules
• Using Rules to set default field values
• Synchronous server side Business Rule logic execution
• Visual editing
 

To illustrate how the ramped up Business Rules work in CRM 2015 let’s take an example using the new if / else logic.

 In this scenario we want to set a Business Rule on a Quote record that will automatically set the  Quote Discount Value to 10% if the total quote amount is greater than £10,000. For all quotes below this threshold the default discount will be 5%.

To achieve this we’ll set a new Business Rule that will apply to the Quote entity:
Firstly, a condition has been set if the total quote amount exceed £10,000.
 

 

 Next, an action needs to be defined that will be enforced for all Quotes that trigger this

condition.
 In CRM 2015 there are 6 Business Rule operations available in the 'Actions' lists.

• Show Error message: Field validation check alerting the user with an error message if an
entry breaches defined conditions
• Set Field Value: Automatically setting the value of the field on the form
• Set Business Required: Used to set a field as mandatory
• Set Visibility: Show / hide fields based on conditions
• Set Default Value: Applying a default field value
• Lock or Unlock Field: Setting field write permissions

In this example we will select ‘Set a Field Value’


We can now go ahead and select the ‘Quote Discount %’ field and apply our defined 10% mark  down that will automatically be applied for all quotes that match the above condition.

Using the new CRM 2015 functions a further ELSE condition can be set that will apply to all records which don’t trigger the previous rule.

In this example, an ELSE condition has been configured to set a 5% discount for all quotes that total less than £10,000.

CRM 2013 lacked support for IF…THEN…ELSE logic so for this particular example it would require 2 separate Business Rules to be defined for both eventualities.

 

 SERVER SIDE BUSINESS LOGIC EXECUTION

Another improvement to Dynamics CRM Business Rules is server-side execution that enables a synchronous workflow to be executed.
 As well as being a real-time operation this offers greater control by defining the scope that will be set to the entity.

 This also ensures your rule will fire no matter how CRM records are updated, even if fields are automatically updated via integrated solutions.

To demonstrate, let’s take an example where you have one synchronous workflow that controls the process to automatically create contacts when an account is created. Then we'll create a business rule that will prevent users from creating contact records when the parent Account

record has a Country field set to “US”.
 
Applying this scenario to CRM 2013 using a workflow and business rules, Dynamics would still create a contact record and only when this record was opened would an error message be seen.

 

In contrast, by using server side Business Rules in CRM 2015 far greater control can be applied which will immediately roll-back by firing an error message on the parent entity if the Business Rule is violated at the child entity.

To demonstrate this feature, we’ve outlined the steps needed.

 1. Firstly, we’ll create a synchronous workflow that will automatically create a new contact when an account is created.
 

 

2. Next, a contact based Business Rule will be added with the Scope set to the whole ‘Entity’ i.e. ‘Contacts’ that will execute the business rule on server side.
 If required, the scope could be narrowed to selected forms. This offers greater precision in controlling how the Rule will be applied so can prove useful if a Business Rule will only apply to a specific team who work with their own dedicated form.

 3. Now the scope has been set we need to specify the condition and error message that will fire if this condition is violated. Using this basic rule a condition has been set to check the contact ‘Country’ field for entries that

equal ‘US’.
If a match is found it will fire an error message.

 

 
4. After activating the Workflow and Business Rule, if a user attempts to create a new parent account with the Country set as ‘US’ CRM fires the following error.

In comparison to CRM 2013 neither the account, nor a contact record will be created.