SQL Snippets

I’ve been doing quite a lot of report authoring recently, which has meant I needed to get to grips with some FetchXML for creating the reports in CRM 2011, but also dust off some old SQL and SSRS expressions.

Thought I would put a few of these expressions together in one place, mostly for my own reference, but if anyone finds them useful you’re more than welcome to use them.

None of them are radical new ways of doing things, but when you don’t use a function regularly it’s easy to forget the syntax etc. They are:

FormatNumber
Alternating Row Colours in a Table
Colour Change
FetchXML Report Parameters
Display Chosen Parameters
Contextual FetchXML Reports

FormatNumber

One of my reports needed to have a calculated field, which was then rounded and a currency symbol inserted. I wasn’t able to strictly format the field as a number, with a coma for the thousands, so just added FormatNumber before to give me the commas.

Example:

= Fields!currencysymbol.Value & FormatNumber(round( Fields!CalculatedSum.Value,Fields!currencyprecisionValue.Value))

Alternating Row Colours in a Table

I needed to show each row in alternating White and Grey for easier reading.

Example:

=IIf(RowNumber(Nothing) Mod 2 = 0, "#f2f2f2", "White")

Colour change

In this case, the expression is checking a value for the field 'enteredon' and if the difference between that date and the current date (Now) is greater than (>) 48 hours (The choice of checking against hours is set by the 'DateInterval.Hour' part of the expression), then the colour is Red, otherwise if it's less than 12 hours it's green, else DarkOrange.

= iif (DateDiff(DateInterval.Hour,Fields!enteredon.Value,Now()) >48 , "RED",iif (DateDiff(DateInterval.Hour,Fields!enteredon.Value,Now()) <12 , "Green","DarkOrange"))

FetchXML Report Parameters

Adding a parameter in a FetchXML report is quite easy:

Example:

<filter type ="and">
<condition attribute="fieldname" operator ="in" value="@parametername" />
</filter>

Then call it as you normally would in a SQL report

Display chosen parameters

Had a report that allowed the user to stipulate several parameters……

I then wanted to show those chosen as a comma separated list as part of the sub title.

Example:

=join(Parameters!ParaName.Label,", ")

Contextual FetchXML Reports

I am really starting to get in to FetchXML for reporting, mostly if I’m honest adapting wizard reports in BIDS to tweak.

In the past I’ve blogged about making SQL reports contextual in CRM using the CRMAF_ prefix.

In FetchXML the following will give you a contextual report against , for example a Contact, pulling in Sales Order details:

Example:

<fetch distinct="false" no-lock="false" mapping="logical">

<entity name="contact" enableprefiltering="1" prefilterparametername="CRM_FilteredContact">
<attribute name="contactid" />
<attribute name="fullname" />
<link-entity name="salesorder" to="contactid" from="customerid" link-type="outer" alias="salesorder1" enableprefiltering="1" prefilterparametername="CRM_FilteredSalesOrder">

Then your FetchXML would continue pulling in all the required Sales Order fields.