CRM Online Reporting FetchXML (Handy SQL Reporting)
CRM Online Reporting with FetchXML Series
Intro: Intro to Series
Part I: How to Set up a Data Source to Access CRM Data in SQL Reporting Services
Part II: How to Use FetchXML Data in the Report
Part IIa: CRM FetchXML Operators
Part III: Handy SQL Reporting Services Functions for CRM Online Reports
When creating a report in SQL Server Reporting Services, there are a number of tweaks you can make to a report to make it look professional. Here are a few of my more common changes I like to make to reports.
Create "Banded Rows" - a different background color for each report
This is a way to make your report much easier to read, so I like to do this with all of my SQL Reporting Services reports. To do this, first you need to highlight the row in the design view:
Then click down in the lower right in the Properties box:
Enter an expression in the Background Color drop arrow to change colors for every other row. You can substitute in the color of choice for Lemon Chiffron here:
=IIf(RowNumber(Nothing) Mod 2,"LemonChiffon","White")
Date Functions - DateAdd
I like to be able to default in start and end dates for parameters when I do time-based reporting, so I end up using the DateAdd function quite a bit to set the Start date parameter to 7 or 30 days before today. The DateAdd works like this:
=DateAdd(DateInterval.[Span],[First Value],[Second Value])
So I use it like this:
=DateAdd(DateInterval.Day,-7,Today())
This gives me a start date of 7 days prior to today in the ShortDate format (with no time stamp). To factor in the hour of the day, use the Now() function instead.
The [Span] values you can use for the DateInterval options are as follows: Year, Quarter, Month, Week, Day, Hour, Minute, Second. You can sub in short values, such as "hh" for hour, but I figure it's just easier to use the full name.
Formatting Values with 2 Decimals
In my report I was putting the value for the hours we spend on each task, and our smallest increment of time that we'd record is 15 minutes. I like to represent the time in hours, so that means the max number of decimals in the report is 2. I want consistency in the report, so I want all values to have two decimal places. To make sure this works, you can put an expression around your value to make sure the value has two decimal places:
=FormatNumber(Fields!Data.Value,2)
I hope you find these functions helpful - it had been a year or so since I created a report, so it took a while for it all to come back to me. Now I have a place to look the next time I get stuck.
