Hi,
In our CRM application, we are upgrading from CRM 2011 to CRM 2016 online. The thing is we have 2 very complex SSRS reports which will have to be converted to FetchXML for the online version. We know that FetchXML has a lot of limitations. SQL queries are
much easier to use for data retrievals across entities.
Below are some cases and code chunks for which we want to know the best way to implement them with the Fetch XML queries. Let us know.
- Couple of datasets with more than 10 linked entities – FetchXML queries have a limit of 10.
- SELECT
case
ISNULL(T1.ava_street1,'')
WHEN ''
THEN '' ELSE
ISNULL(T1.ava_street1,'')
+ ', '
END,
case ISNULL(T1.ava_street2,'')
WHEN ''
THEN '' ELSE
ISNULL(T1.ava_street2,'')
+ ', '
END,
case ISNULL(T1.ava_street3,'')
WHEN ''
THEN '' ELSE
ISNULL(T1.ava_street3,'')
+ ', '
END,
case ISNULL(T1.ava_city,'')
WHEN ''
THEN '' ELSE
ISNULL(T1.ava_city,'')
+ ', '
END,
case ISNULL(T1.ava_postcode,'')
WHEN ''
THEN '' ELSE
ISNULL(T1.ava_postcode,'')
+ ', '
END,
case ISNULL(T1.ava_countryidname,'')
WHEN ''
THEN '' ELSE
ISNULL(T1.ava_countryidname,'')
END
+ '; '
AS [text()]
- If else statement as below
IF
(@MASTERPOLICYLIMITSEXISTS
> 0)
BEGIN
INSERT INTO @LIMITS
(
MasterPolicyId
,PolicyID
…..
END
ELSE
BEGIN
INSERT INTO @LIMITS
(
MasterPolicyId
,PolicyID
- There is a temporary table for most of the Datasets for which ‘Insert’ and ‘Update’ statements are being used.
- There are lot of subqueries being used.
Thanks,
Anwesha
