Issue found while upgrading reports from SQL 2005 to SQL 2008

Posted on July 1st, 2010 Mitch Milam  Print No comments

One of my local customers recently moved from Small Business Server 2003 to Small Business Server 2008.  Among other things, this required a move from SQL Server 2005 to SQL Server 2008 as well.

The movement of the databases went pretty much without issue, but we ran into some strange behavior with some of the custom CRM reports they had created.  The error looked something like this:

Not very helpful at all.

After reviewing the report in Visual Studio and still not finding any issues, I finally started looking at the system itself.  That’s when I found this in the SQL Server’s Event Log:

Report data set execution failure. Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near ','.

Hmm. It keeps getting better and better. Now what exactly does that mean? 

Background

It turns out that when the SQL commands they had written to populate the report were sent to the SQL server, they were combined with other SQL commands.

Unfortunately, SQL didn’t like the way the commands were combined.  It wanted you to use a semi-colon ( ; ) to separate the commands so it could determine how the commands should be run.

Which is exactly what the error message states.

The Solution

To fix the issue, all I did was to add a semi-colon ( ; ) to the beginning of the custom SQL statement, which looked something like this:

;WITH FilteredAccount AS

And that solved the problem and the reports ran successfully.

Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

255 views

CRM 4.0’s Built-in SQL Functions (Part 5 of 5)

Posted on August 21st, 2009 Mitch Milam  Print No comments

The final installment in this series contains miscellaneous SQL functions.

You may wish to review the following articles before proceeding:

CRM 4.0’s Built-in SQL Functions (Part 1 of 5)

CRM 4.0’s Built-in SQL Functions (Part 2 of 5)

CRM 4.0’s Built-in SQL Functions (Part 3 of 5)

CRM 4.0’s Built-in SQL Functions (Part 4 of 5)

dbo.fn_FindBusinessGuid

This function will return the ID for the business unit for the currently logged-on user.

select dbo.fn_FindBusinessGuid()

dbo.fn_FindUserGuid

This function will return the CRM ID for the currently logged-on user.

select dbo.fn_FindUserGuid()

Conclusion

I hope you enjoyed this series which explored the built-in user-defined functions included with CRM 4.0.  Hopefully, it will make your report writing quicker and more powerful.

I have a couple of additional articles planned that will cover some of these functions in more detail in the future.

Customization, Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

604 views

CRM 4.0’s Built-in SQL Functions (Part 4 of 5)

Posted on August 20th, 2009 Mitch Milam  Print No comments

In this section, we’ll cover year-related functions.

You may wish to review the following articles before proceeding:

CRM 4.0’s Built-in SQL Functions (Part 1 of 5)

CRM 4.0’s Built-in SQL Functions (Part 2 of 5)

CRM 4.0’s Built-in SQL Functions (Part 3 of 5)

dbo.fn_BeginOfLastYear

Returns the beginning date and time for last year.

This function requires a valid datetime value.

select dbo.fn_BeginOfLastYear(GetUTCDate())

Returns: 2008-01-01 06:00:00.000

dbo.fn_BeginOfNextYear

Returns the beginning date and time for next year.

This function requires a valid datetime value.

select dbo.fn_BeginOfNextYear(GetUTCDate())

Returns: 2010-01-01 06:00:00.000

dbo.fn_BeginOfThisYear

Returns the beginning date and time for this year.

This function requires a valid datetime value. It is assumed you will specify today’s date and time.

select dbo.fn_BeginOfThisYear(GetUTCDate())

Returns: 2009-01-01 06:00:00.000

dbo.fn_BeginOfYear

Returns the beginning date and time of a year.

This function requires a valid datetime value.

select dbo.fn_BeginOfYear(GetUTCDate())

Returns: 2009-01-01 06:00:00.000

dbo.fn_EndOfLastYear

Returns the ending date and time for last year.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_EndOfLastYear(GetUTCDate())

Returns: 2009-01-01 06:00:00.000

dbo.fn_EndOfNextYear

Returns the ending date and time for next year.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_EndOfNextYear(GetUTCDate())

Returns: 2011-01-01 06:00:00.000

dbo.fn_EndOfThisYear

Returns the ending date and time for this year.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_EndOfThisYear(GetUTCDate())

Returns: 2010-01-01 06:00:00.000

dbo.fn_LastXYear

Returns the date from X number of years ago ( from today ).

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_LastXYear(GetUTCDate(), 1)

Returns: 2008-08-16 05:00:00.000

dbo.fn_NextXYear

Returns a date that is X number of years in the future ( from today )

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_NextXYear(GetUTCDate(), 1)

Returns: 2010-08-17 05:00:00.000

Customization, Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

554 views

CRM 4.0’s Built-in SQL Functions (Part 3 of 5)

Posted on August 19th, 2009 Mitch Milam  Print No comments

Today we are continuing on with part three of our series on using CRM 4.0’s built-in SQL functions.

You may wish to review the following articles:

CRM 4.0’s Built-in SQL Functions (Part 1 of 5)

CRM 4.0’s Built-in SQL Functions (Part 2 of 5)

Note: The date used in the following example is 2009-08-16.

dbo.fn_BeginOfLastMonth

Return the beginning date and time of last month.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_BeginOfLastMonth(GetUTCDate())

Returns: 2009-07-01 05:00:00.000

dbo.fn_BeginOfMonth

Return the beginning date and time of a specified month.

This function requires a valid datetime value.

select dbo.fn_BeginOfMonth(GetUTCDate())

Returns: 2009-08-01 05:00:00.000

dbo.fn_BeginOfNextMonth

Return the beginning of next month.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_BeginOfNextMonth(GetUTCDate())

Returns: 2009-09-01 05:00:00.000

dbo.fn_BeginOfThisMonth

Return the beginning of this month.  This function has the same functionality as dbo.fn_BeginOfMonth.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_BeginOfThisMonth(GetUTCDate())

Returns: 2009-08-01 05:00:00.000

dbo.fn_EndOfLastMonth

Returns the ending date and time of last month.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_EndOfLastMonth(GetUTCDate())

Returns: 2009-08-01 05:00:00.000

dbo.fn_EndOfNextMonth

Returns the ending date and time of next month.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_EndOfNextMonth(GetUTCDate())

Returns: 2009-10-01 05:00:00.000

dbo.fn_EndOfThisMonth

Returns the ending date and time of this month.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_EndOfThisMonth(GetUTCDate())

Returns: 2009-09-01 05:00:00.000

dbo.fn_FirstDayOfMonth

Returns the first date of the month for a specified month.

This first parameter is a valid datetime value.  The second parameter is the month you wish to check.

select dbo.fn_FirstDayOfMonth(GetUTCDate(), 8)

Returns: 2009-08-01 00:00:00.000

dbo.fn_LastXMonth

Returns the date and time from one month ago today ( assuming 30 days ago ).

This first parameter is a valid datetime value.  The second parameter is the number of months in the past.

select dbo.fn_LastXMonth(GetUTCDate(), 1)

Returns: 2009-07-16 05:00:00.000

dbo.fn_NextXMonth

Returns the date and time from one month in the future – from today.

This first parameter is a valid datetime value.  The second parameter is the number of months in the future.

select dbo.fn_NextXMonth(GetUTCDate(), 1)

Returns: 2009-09-17 05:00:00.000

Customization, Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

614 views

CRM 4.0’s Built-in SQL Functions (Part 2 of 5)

Posted on August 18th, 2009 Mitch Milam  Print No comments

As we continue onto the second article in our set, you may wish to review the previous article:

 CRM 4.0’s Built-in SQL Functions (Part 1 of 5)

Note: The date used in the following example is 2009-08-16.

dbo.fn_BeginOfLastWeek

Returns the beginning date and time for last week.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time

select dbo.fn_BeginOfLastWeek(GetUTCDate())

Returns: 2009-08-23 05:00:00.000

dbo.fn_BeginOfLastXWeek

Returns the beginning date and time for a week X weeks ago.

The first parameter is the date in question, the second parameter is the number of weeks before that date.

select dbo.fn_BeginOfLastXWeek(GetUTCDate(), 1)

Returns: 2009-08-09 05:00:00.000

dbo.fn_BeginOfNextWeek

Returns the beginning date and time for next week.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_BeginOfNextWeek(GetUTCDate())

2009-08-23 05:00:00.000

dbo.fn_BeginOfWeek

Returns the beginning date and time for a specified date.

This function requires a valid datetime value. 

select dbo.fn_BeginOfWeek(GetUTCDate())

2009-08-16 05:00:00.000

dbo.fn_BeginOfThisWeek

Returns the beginning date and time for this week.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_BeginOfThisWeek(GetUTCDate())

Returns: 2009-08-16 05:00:00.000

dbo.fn_EndOfLastWeek

Returns the ending date and time for last week.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_EndOfLastWeek(GetUTCDate())

Returns: 2009-08-16 05:00:00.000

dbo.fn_EndOfNextWeek

Returns the ending date and time for nextweek.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_EndOfNextWeek(GetUTCDate())

Returns: 2009-08-30 05:00:00.000

dbo.fn_EndOfNextXWeek

Returns the beginning date and time for a week X weeks in the future.

The first parameter is the date in question, the second parameter is the number of weeks after that date.

select dbo.fn_EndOfNextXWeek(GetUTCDate(), 1)

Returns: 2009-08-24 05:00:00.000

dbo.fn_EndOfThisWeek

Returns the ending date and time for this week.

This function requires a valid datetime value.  It is assumed you will specify today’s date and time.

select dbo.fn_EndOfThisWeek(GetUTCDate())

Returns: 2009-08-23 05:00:00.000

dbo.fn_NTDayOfWeek

Returns the day of the week. 0 = Sunday, 6 = Saturday.

select dbo.fn_NTDayOfWeek(GetUTCDate())

Returns: 0 ( for Sunday )

Customization, Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

952 views

CRM 4.0’s Built-in SQL Functions (Part 1 of 5)

Posted on August 17th, 2009 Mitch Milam  Print 4 comments

As I mentioned in a previous article, CRM 4.0 includes a group of user-defined functions that are commonly used within SQL Reporting Services reports to query CRM data.  I first noticed these functions when modifying a report created using the CRM Report Wizard.

That got me to thinking that I needed to explore these functions in more detail to determine if they would be of use to me when producing my own reports or simply when creating custom SQL queries to query CRM data.

Since there are so many functions, I’ve decided to break our discussion into five articles so as not to overwhelm you with data.

Overview

The first thing you will notice is that most of the functions work with dates and times.  This is great for us because most date math can be one of the hardest topics to master and it also makes your SQL queries difficult to read.

The second point that requires attention is the fact that all dates within CRM are stored in Coordinated Universal Time, or UTC, as it is called.  This was done ( I’m sure ) to allow for a consistent method for performing date calculations without requiring the developer to take time zones into account.  The only issue is when a human looks at the dates, they look funny because they are not reconciled with the local time zone and don’t match what a person thinks the date and time should be.

So, let’s get started with our review.

Notes:

I will be using Eastern Daylight Savings Time for these articles.  This is UTC –5 hours.

You should also note that UTC times are in 24-hour format, not 12-hour format.

In my examples, I’ll be using the SQL function GetUTCDate() which returns the current date and time in UTC format.

It would also appear that the beginning of one day is actually the same as the ending of the previous day.

Today’s date is 2009-08-16

dbo.fn_BeginOfDay

Returns the date and time of the beginning time for a specified day.  The day starts at midnight ( 00:00 ).

This function requires a valid datetime value.

select dbo.fn_BeginOfDay(GetUTCDate())

Returns the value: 2009-08-16 05:00:00.000

dbo.fn_BeginOfLastXDay

Returns the beginning of a day, X number of days in the past.

The first parameter is the date in question, the second parameter is the number of days prior to that date.

select dbo.fn_BeginOfLastXDay(GetUTCDate(), 1)

Returns: 2009-08-15 05:00:00.000

dbo.fn_BeginOfToday

Returns the beginning of today.

This function requires a valid datetime value.

select dbo.fn_BeginOfToday(GetUTCDate())

Returns: 2009-08-16 05:00:00.000

dbo.fn_BeginOfTomorrow

Returns the beginning of tomorrow.

This function requires a valid datetime value.  It is assumed you will pass GetUTCDate() to properly retrieve today’s date.

select dbo.fn_BeginOfTomorrow(GetUTCDate())

Returns: 2009-08-17 05:00:00.000

dbo.fn_BeginOfYesterday

Returns the beginning of yesterday.

This function requires a valid datetime value.  This function is actually the same as dbo.fn_BeginOfDay().  It is assumed you will pass GetUTCDate() to properly retrieve today’s date..

select dbo.fn_BeginOfYesterday(GetUTCDate())

Returns: 2009-08-15 05:00:00.000

dbo.fn_EndOfNextSevenDay

Returns the beginning of the day for a date 7 days into the future.

This function requires a valid datetime value. 

select dbo.fn_EndOfNextSevenDay(GetUTCDate())

Returns: 2009-08-24 05:00:00.000

dbo.fn_EndOfNextXDay

Returns the beginning of the day for the day after a day X days into the future.

The first parameter is the date in question, the second parameter is the number of days after that date.

select dbo.fn_EndOfNextXDay(GetUTCDate(), 1)

Returns: 2009-08-18 05:00:00.000

dbo.fn_BeginOfLastSevenDay

Returns the beginning of the day, for a day 7 days into the past.

This function requires a valid datetime value. 

select dbo.fn_BeginOfLastSevenDay(GetUTCDate())

Returns: 2009-08-09 05:00:00.000

dbo.fn_EndOfToday

Returns the end of today.

This function requires a valid datetime value.  It is assumed you will pass GetUTCDate() to properly retrieve today’s date.

select dbo.fn_EndOfToday(GetUTCDate())

Returns: 2009-08-17 05:00:00.000

dbo.fn_EndOfTomorrow

Returns the end of tomorrow.

This function requires a valid datetime value.  It is assumed you will pass GetUTCDate() to properly retrieve today’s date.

select dbo.fn_EndOfTomorrow(GetUTCDate())

Returns: 2009-08-18 05:00:00.000

dbo.fn_EndOfYesterday

Returns the end of yesterday.

This function requires a valid datetime value.  It is assumed you will pass GetUTCDate() to properly retrieve today’s date.

select dbo.fn_EndOfYesterday(GetUTCDate())

Returns: 2009-08-16 05:00:00.000

dbo.fn_BeginOfHour

Returns the beginning of the current hour.

This function requires a valid datetime value.  It is assumed you will pass GetUTCDate() to properly retrieve today’s date and time.

select dbo.fn_BeginOfHour(GetUTCDate())

Returns: 2009-08-16 17:00:00.000

dbo.fn_BeginOfLastXHour

Returns the beginning of the last hour, X hours in the past.

The first parameter is the date/time in question, the second parameter is the number of hours previous to the current time.

select dbo.fn_BeginOfLastXHour(GetUTCDate(), 1)

Returns: 2009-08-16 16:00:00.000

dbo.fn_EndOfNextXHour

Returns the end of the next hour, X hours into the future.

The first parameter is the date/time in question, the second parameter is the number of hours previous to the current time.

select dbo.fn_EndOfNextXHour(GetUTCDate(), 1)

Returns: 2009-08-16 19:00:00.000

All of this UTC stuff is giving me a headache!

Welcome to the club.  Luckily, the CRM team gave us a function to covert everything to the local time zone:

dbo.fn_UTCToLocalTime

Converts a UTC datetime value into a local date and time value.  Using modifications of our examples above:

select dbo.fn_UTCToLocalTime(dbo.fn_BeginOfHour(GetUTCDate()))

select dbo.fn_UTCToLocalTime(dbo.fn_BeginOfLastXHour(GetUTCDate(), 1))

select dbo.fn_UTCToLocalTime(dbo.fn_EndOfNextXHour(GetUTCDate(), 1))

Returns:

Beginning of Hour: 2009-08-16 12:00:00.000

Beginning of Last X Hour: 2009-08-16 11:00:00.000

Beginning of Next X Hour: 2009-08-16 14:00:00.000

dbo.fn_LocalTimeToUTC

Just the opposite of dbo.fn_UTCToLocalTime, this function converts local time to UTC time.

select dbo.fn_LocalTimeToUTC(GetDate())

Returns: 2009-08-16 20:56:56.827

Conclusion

Today we started covering the user-defined SQL functions found inside CRM 4.0 database.  Granted, sometimes the data that is returned doesn’t quite look right because all dates and times are in UTC format.  I just have to say that you need to trust the results because this is what CRM uses on an everyday basis.

If you are ever confused by the results, I would suggest wrapping your query in a dbo.fn_UTCToLocalTIme() function to see what is returned.  Like this:

select dbo.fn_UTCToLocalTime(dbo.fn_BeginOfLastSevenDay(GetUTCDate()))

Returns: 2009-08-09 00:00:00.000

Good luck.

Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

1,640 views

Using built-in CRM functions when writing SQL Reports

Posted on February 20th, 2009 Mitch Milam  Print 2 comments

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 ...

961 views

Using Filtered Views with CRM 3.0

Posted on September 7th, 2006 Mitch Milam  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

(1 votes, average: 5.00 out of 5)

 Loading ...

3,811 views

Dynamics CRM 3.0: Error Accessing Reports

Posted on July 6th, 2006 Mitch Milam  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

(1 votes, average: 5.00 out of 5)

 Loading ...

2,428 views

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

Posted on March 26th, 2006 Mitch Milam  Print 1 comment

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

(1 votes, average: 5.00 out of 5)

 Loading ...

3,973 views