Friday, December 19, 2008

Useful Out-of-the-Box SQL Functions for MSCRM

There are some very useful SQL functions that are built into the MSCRM database that are available to be used in the select queries to write reports, pull data into Excel, or just to check the data in your database. Some of the more notable functions, at least from what I think, are the following:

1. F_START_OF_WEEK (gets the first day of a week, and you can specify which day you want to be the start of your week, Monday, Tuesday, etc)
2. fn_LastXMonth (this is what Advanced Find uses, this is fairly easy to replicate with SQL but it gets even easier if there is a function to be used)
3. fn_FindUserGuid (this gets the logged in user's GUID, this is how Advanced Find figures out "Equals Current User")
4. fn_GetMaxUserPrivilege (tells you whether a user has sufficient rights to a specific privilege,)
5. fn_RptBracket (puts numbers in groups, for instance fn_RptBracket(75, 50) would return 50 - 99 . This translates to placing the number 75 into numbers 1-49, 50-99, 100-149, etc. Basically the first number is the number you want to group and the second is the interval at which you want your groupings. Play with it a bit to see how it works. It could be very useful for reporting)

To find all of the functions open up SQL Management Studio and expand the MSCRM database, then expand "Programmability", expand "Functions", and finally, expand "Scalar-valued Functions" to see the full list. Right click a function and choose "Script Function As", "Create To", "New Query Editor Window" to look at the code behind the function.

Some very cool things to find and use, hopefully this makes some report queries much more simple and other data check queries easier to write.

David Fronk
Dynamic Methods Inc.

No comments:

Post a Comment