I enjoy working with SSRS reports and learning new things to help me build out reports that meet our clients’ needs. When a client comes to me with an interesting way they want something displayed or a request for a feature that is unknown to me, I jump at the chance to do it. I love a new challenge and it acts as a learning experience as well. A recent request came to me that required a little creativity on my part. It was to modify the default value of certain report parameters based on the security role the User had in CRM.
I had found information on how to identify the User running the report in CRM and how to provide parameter options from a query, but nothing that exactly matched what I needed. By modifying each of these a bit and combining them, I was able to get the result I needed. From there, I was able to display the report in the way that was requested.
As a User in CRM, you can have multiple security roles. The first thing you need to do is to query if the User running the report has the Role required. The key piece to do this will be through finding the CRM GUID of the User running the report which can be done using ‘dbo.fn_FindUserGuid()’ in the query. Since we can get the User’s GUID, we can then look at the associated security roles to find the one we need.
First, create a dataset that will return the results of your security role query.
In the example, there are two Security Roles I need to set the parameters for, Customer Support and CS Manager. So, my query will return a value greater than zero if either of the roles are found to be associated with the User. Even if you only want to set the parameters for one role, I would suggest returning the value through a count as we really only need to know if the role exists for the user or not.
Next, create a Parameter that will hold the results of your query. We must create a parameter to show the results, because you cannot reference a dataset within a parameter. You can reference another parameter, though.
You will want to make sure that this parameter is the first in your list of parameters so that it can be referenced in others. Make the parameter visibility ‘Internal’ and in the Default Values you will want the option to get values from a query. Here, you will choose the Dataset we previously created and the field that returns us our value, ‘IsCS’.
Finally, in each of the parameters where you want the default value modified based on the Security Role, you will want to build a formula in the Default Values section.
In this example, if the parameter that receives its value from my query is zero than the default should be false; otherwise, the default is true. This is basically saying that if the query does not return a result when looking for the Security Role, then the default should be false.
Want more “How To” tips? Ask our consultants! Contact KTL Solutions by calling 301.360.0001 or emailing sales@ktlsolutions.com
SCOTT FLORANCE |CRM Business Software Consultant
Scott Florance is one of the CRM Consultants at KTL and has proven his value as a member of the team since September 2013. Whether implementing a new CRM organization or adding to existing configurations, Scott has engaged clients with a positive and enthusiastic demeanor to help them meet their organizational needs. With four plus years of experience, Scott is familiar with CRM as both a power user and administrator. Scott received his bachelor’s degree in business administration from the University of Central Florida. He is a Microsoft Certified Technology Specialist for Dynamics CRM as well as a Certified Scribe Technician.
