Using built-in CRM functions when writing SQL Reports

Posted on February 20th, 2009 mitch  Print 1 comment

If you've ever worked with CRM's Advanced Find you know what an amazing number of built-in date query parameters there are.  It turns out, that those query parameters have corresponding user-defined SQL functions that you can use in your own queries for operations such as custom reports.

Here is a list of some of the more common date functions:

fn_BeginOfDay
fn_BeginOfHour
fn_BeginOfLastMonth
fn_BeginOfLastSevenDay
fn_BeginOfLastWeek
fn_BeginOfLastXDay
fn_BeginOfLastXHour
fn_BeginOfLastXWeek
fn_BeginOfLastYear
fn_BeginOfMonth
fn_BeginOfNextMonth
fn_BeginOfNextWeek
fn_BeginOfNextYear
fn_BeginOfThisMonth
fn_BeginOfThisWeek
fn_BeginOfThisYear
fn_BeginOfToday
fn_BeginOfTomorrow
fn_BeginOfWeek
fn_BeginOfYear
fn_BeginOfYesterday
fn_EndOfLastMonth
fn_EndOfLastWeek
fn_EndOfLastYear
fn_EndOfNextMonth
fn_EndOfNextSevenDay
fn_EndOfNextWeek
fn_EndOfNextXDay
fn_EndOfNextXHour
fn_EndOfNextXWeek
fn_EndOfNextYear
fn_EndOfThisMonth
fn_EndOfThisWeek
fn_EndOfThisYear
fn_EndOfToday
fn_EndOfTomorrow
fn_EndOfYesterday
fn_FirstDayOfMonth
fn_LastXMonth
fn_LastXYear
fn_LocalTimeToUTC
fn_NextXMonth
fn_NextXYear
fn_NTDayOfWeek

If you would like to see these functions in action, copy the following script into SQL Management Studio and run it ( after connecting to the CRM database ):

select 'fn_BeginOfDay' as "Function",  dbo.fn_BeginOfDay(GetDate()) as "Value"
union
select 'fn_BeginOfHour',  dbo.fn_BeginOfHour(GetDate())
union
select 'fn_BeginOfLastMonth',  dbo.fn_BeginOfLastMonth(GetDate())
union
select 'fn_BeginOfLastSevenDay',  dbo.fn_BeginOfLastSevenDay(GetDate())
union
select 'fn_BeginOfLastWeek',  dbo.fn_BeginOfLastWeek(GetDate())
union
select 'fn_BeginOfLastXDay',  dbo.fn_BeginOfLastXDay(GetDate(), 1)
union
select 'fn_BeginOfLastXHour',  dbo.fn_BeginOfLastXHour(GetDate(), 1)
union
select 'fn_BeginOfLastXWeek',  dbo.fn_BeginOfLastXWeek(GetDate(), 2)
union
select 'fn_BeginOfLastYear',  dbo.fn_BeginOfLastYear(GetDate())
union
select 'fn_BeginOfMonth',  dbo.fn_BeginOfMonth(GetDate())
union
select 'fn_BeginOfNextMonth',  dbo.fn_BeginOfNextMonth(GetDate())
union
select 'fn_BeginOfNextWeek',  dbo.fn_BeginOfNextWeek(GetDate())
union
select 'fn_BeginOfNextYear',  dbo.fn_BeginOfNextYear(GetDate())
union
select 'fn_BeginOfThisMonth',  dbo.fn_BeginOfThisMonth(GetDate())
union
select 'fn_BeginOfThisWeek',  dbo.fn_BeginOfThisWeek(GetDate())
union
select 'fn_BeginOfThisYear',  dbo.fn_BeginOfThisYear(GetDate())
union
select 'fn_BeginOfToday',  dbo.fn_BeginOfToday(GetDate())
union
select 'fn_BeginOfTomorrow',  dbo.fn_BeginOfTomorrow(GetDate())
union
select 'fn_BeginOfWeek',  dbo.fn_BeginOfWeek(GetDate())
union
select 'fn_BeginOfYear',  dbo.fn_BeginOfYear(GetDate())
union
select 'fn_BeginOfYesterday',  dbo.fn_BeginOfYesterday(GetDate())
union
select 'fn_EndOfLastMonth',  dbo.fn_EndOfLastMonth(GetDate())
union
select 'fn_EndOfLastWeek',  dbo.fn_EndOfLastWeek(GetDate())
union
select 'fn_EndOfLastYear',  dbo.fn_EndOfLastYear(GetDate())
union
select 'fn_EndOfNextMonth',  dbo.fn_EndOfNextMonth(GetDate())
union
select 'fn_EndOfNextSevenDay',  dbo.fn_EndOfNextSevenDay(GetDate())
union
select 'fn_EndOfNextWeek',  dbo.fn_EndOfNextWeek(GetDate())
union
select 'fn_EndOfNextXDay',  dbo.fn_EndOfNextXDay(GetDate(), 1)
union
select 'fn_EndOfNextXHour',  dbo.fn_EndOfNextXHour(GetDate(), 1)
union
select 'fn_EndOfNextXWeek',  dbo.fn_EndOfNextXWeek(GetDate(), 1)
union
select 'fn_EndOfNextYear',  dbo.fn_EndOfNextYear(GetDate())
union
select 'fn_EndOfThisMonth',  dbo.fn_EndOfThisMonth(GetDate())
union
select 'fn_EndOfThisWeek',  dbo.fn_EndOfThisWeek(GetDate())
union
select 'fn_EndOfThisYear',  dbo.fn_EndOfThisYear(GetDate())
union
select 'fn_EndOfToday',  dbo.fn_EndOfToday(GetDate())
union
select 'fn_EndOfTomorrow',  dbo.fn_EndOfTomorrow(GetDate())
union
select 'fn_EndOfYesterday',  dbo.fn_EndOfYesterday(GetDate())
union
select 'fn_FirstDayOfMonth',  dbo.fn_FirstDayOfMonth(GetDate(), 10)
union
select 'fn_LastXMonth',  dbo.fn_LastXMonth(GetDate(), 1)
union
select 'fn_LastXYear',  dbo.fn_LastXYear(GetDate(), 1)
union
select 'fn_NextXMonth',  dbo.fn_NextXMonth(GetDate(), 1)
union
select 'fn_NextXYear',  dbo.fn_NextXYear(GetDate(), 1)

Customization, Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

500 views

Using Filtered Views with CRM 3.0

Posted on September 7th, 2006 mitch  Print 4 comments

Some of my customers use Microsoft Excel or Access to pull data from the CRM system in order to produce custom reports, or in one instance, to interface with a third-party bulk-email application.

CRM 3.0 has a set of SQL Database Views, called Filtered Views, that are constructed in such a way that CRM Security roles are maintained and you can retrieve nothing using one of these views that you would not ordinarily see while using the CRM application.

One aspect of using filtered views that you must take into consideration is the fact that all data ( that you have access to ) will be retrieved from the specified Entity.  This means both Inactive and Active records.

If you don't take Inactive records into consideration your result set could be drastically different than what you expected and  you may inadvertently expend resources on inactive customers.

Luckily, CRM provides two data fields that can be used to filter your query:

So, here is how you would use the above:

--


-- Show only active Accounts


--


select * from FilteredAccount where statuscode = 1


 


--


-- Show only active Contacts


--


select * from FilteredContact where statuscode = 1


 

This code instructs SQL Server to return all rows where the statuscode is equal to 1, which is an Active Account or Contact.

Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

2,765 views

Dynamics CRM 3.0: Error Accessing Reports

Posted on July 6th, 2006 mitch  Print 2 comments

I ran into a very strange issue last week at one of our local customers.

One of the users was receiving an error when she attempted to access the Workplace Reports group.

It turns out, that for some unknown reason, she was not part of the Active Directory security group ReportingGroup, which is used by CRM to control access to the reporting server, etc.

Adding her to this group corrected the issue.

What I don't understand, is how this happened. This user was added to the system after the initial installation and Automatic Group Management was enabled so she should have been added to the ReportingGroup automatically.

Anyway,if you ever run into Report access errors, check the user's membership into the ReportingGroup.

Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

2,009 views

Creating an Address Block Without Blank Lines in a CRM 3.0 Report

Posted on March 26th, 2006 mitch  Print No comments

When you are creating a CRM report involving an address block, you will need to take into account the fact that most of the time, the additional address lines will be blank.  Since it is generally bad form to have extra blank lines in your address block, we need a way of only adding those fields with data.  Take a look at the following, pretty normal address block layout with examples:

Address Block Layout:

Customer Name
Address Line 1
Address Line 2
City, State Zipcode

Example 1:

Bob Smith
123 South Center Street
Apt. 3A
Dallas, TX 00111

Example 2:

Bob Smith
123 South Center Street, Apt 3A

Dallas, TX 00111

Notice how the second example's Address Line 2 is blank, and therefore we have that ugly blank line in the middle of our address?  Well, I don't know about you, but most of my customers ( and me ) find this unacceptable and want the extra blank line removed.

To accomplish this, we will use the Visual Basic IIF function.  This function performs a test that you specify and returns one value if true, and another value if false.

So, here is how we would create the expression to populate a text box within a SQL Reporting Services report that will contain our customer's mailing address:

=Fields!name.Value & vbCRLF &
Fields!address1_line1.Value & vbCRLF &
IIF(IsNothing(Fields!address1_line2.Value), "", Fields!address1_line2.Value & vbCRLF) &
Fields!address1_city.Value & ", " &
Fields!address1_postalcode.Value & " " &
Fields!address1_stateorprovince.Value

Let's break down some of the more special features of this calculation:

• vbCRLF is the Visual Basic pre-defined constant that contains the values of a carriage return and a line feed – what is sometimes called a "New Line" character.
• IsNothing is a Visual Basic function that returns TRUE if the variable being tested is blank.
• The & sign is used to concatenate two strings.

The pseudo code for the IIF statement looks like this:

1. Check to see if the address1_line2.Value Field is blank.
2. If it is blank, return a blank ( "" ).
3. If it is not blank, return the address1_line2.Value Field with a carriage return, line feed character appended to it.

Note: You will need to substitute the field names found in your report for the ones listed in the expression.

References:

Visual Basic Language Reference: IIf Function.

Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

3,068 views

Interesting Dynamics CRM 3.0 Reporting Issue

Posted on March 1st, 2006 mitch  Print No comments

So this afternoon, as we're going our customer a demo of their system ( of course ), we noticed that two of our reports had magically disconnected themselves from their data sources.

I have no idea how this happened, but when you ran the report, you got an SRS error mentioning an invalid the data source or something.

Editing the reports via http://crm/reports (report, properties, data sources ) allowed me to reconnect the report to a pre-defined, shared system datasource and things were back to normal.

What I find alarming is the fact that these two reports have not been touched since Feb 23rd and we've made no configuration changes regarding reporting services or anything.

Anyway, if this happens to you, just go reset the data source connection and you should be back in business.

Oh, this is SRS 2000.

Later, Mitch

Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

1,302 views

Combining MSCRM 3.0 data fields in SQL Reporting Services

Posted on March 1st, 2006 mitch  Print 2 comments

If you are working with SRS and need to combine two CRM fields together, but having each on its own line, use the following expression:

=Fields!ShipToName.Value & vbCRLF & Fields!ShipToAddress.Value

vbCRLF is a special system constant that contains the values of carriage return and line feed characters.

Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

1,593 views