Customer Effective Blog

Best Practices for Activity Reporting In Microsoft Dynamics CRM 2011

So you want to report on your activities in CRM—and that is a great idea.  Activities such as appointments, phone calls, tasks, and e-mails are core CRM functionality and a great indicator of how your users are interacting with your customers, and how actively your users are adopting CRM.

For basic activity reporting, such as activity lists or viewing a count of activities by type by user, consider using the out of the box views and charts. 

For example, if I want to see the number of activities by type and priority, I can just go to the Activity view in CRM and select the “Activities by Type and Priority” chart.

If you are not familiar with creating charts in CRM 2011, check out Kevin Wessels’ Introduction to charts in CRM 2011.

I recommend using the views and charts whenever possible, because it is fast, it is dynamic, and it can easily be added to your CRM 2011 dashboard.

However, if you find you want a report that requires more advanced formatting or that can be published to additional formats, such as Word or PDF documents, another option is to create a SQL Server Reporting Services (SSRS) report.

The point of this post is not to introduce you to SSRS.  If you don’t have experience creating SSRS reporting, I recommend checking out resources like the report development section of the CRM 2011 SDK.

The point of this post is to discuss the unique challenges of working with activity data in reports.  To understand how to work with activities in SQL queries, you need to first understand how activity data is stored.

Activitypointer – The “Anchor”

In the Microsoft CRM SQL databases, activity records are stored in an anchor entity view called filteredactivitypointer.  This view contains the fields that are common to all activity types.  This includes fields such as activityid, scheduled start, scheduled end, actual end, actualduration, subject, and description (the “body”).  This entity also includes a field called Activitytypecode.  This indicates what type of activity the record is.

 Activityparty – The people

Any people, leads, or accounts linked to an activity will be stored in the filteredactivityparty view.  This is a related entity that enables you to add multiple recipients/attendees/customers to activities.  When you create an appointment, for example, and have 3 required attendees, 2 optional attendees, a regarding company, an owner, and a organizer, a total of 8 activity party records are created.  Activity party includes fields like partyid (the GUID of the record related to the actity), partyobjecttypecode (the entity type code of the related record), and activity id.  The filteredactivityparty view also includes some of the fields from the filteredactivitypointer view, such as scheduledstart, scheduledend.

One important thing to understand about activities and activity parties is that some activity parties are stored in both filteredactivitypointer and filteredactivityparty.  The ownerid and the regarding objectid are fields in filteredactivitypointer, but they also are stored as activity parties related to the activity.

This is important to understand, because many people over-complicate their activity reports and views because they misunderstand this—if you want to see any activities where you are the owner, the regarding object, or a recipient/attendee, don’t do something like this:

select distinct a .activityid from filteredactivitypointer a inner join filteredactivityparty p on a.activityid = p.activityid where a.ownerid = @user or a.regardingobjectid = @user or p.partyid = @user

instead, do this:

select distinct activityid from filteredactivityparty where partyid = @user.

Since all people or accounts related to an activity are stored in the filteredactivityparty view, you can limit your query person filter to the activityparty entity, and make your query more efficient.  This is why the default “My Activity” views in CRM filter the activity list by the related activity parties, and they get not only the records owned by the selected user, but also the records where that user is an attendee or recipient or regarding object.

The specific activity type views

As we discussed earlier, the fields that are common to all activity types are stored in the FilteredActivityPointer Anchor entity view.  These fields are also available from the specific entity type views, such as FilteredEmail, Filtered PhoneCall, Filtered Task, Filtered Appointment (and the poor lonely FilteredFax entity view).

These views also contain the fields that are unique to a specific activity type, such as custom fields you might have added to the appointment entity, and status reasons that are unique to the activity type (for example, there is a “sent” status on e-mails, but not appointments).

Planning your report

So as you begin to design your report, there are a couple of questions you should ask:

1.  What records do I want to return?  Is this a report covering all activity types, just one type of activity? Should this show all activity statuses, just open/scheduled, just closed, or both?

2.  How should this data be filtered?  Do I want to see it for a single user, a group of users, a set date range, or a date range selectable by user?

3.  How will this data be consumed?  Is somebody going to look at it on the screen, in a dashboard, export to a spreadsheet, or print out a PDF?

Common Mistakes

1.  Over complicating the query.  If you just want to have a count of activities by person, there is no reason to join activity party to activity pointer and union together the filteredemail, filteredtask, filteredphonecall, and filteredappointment views.  Overcomplicated queries are the main reason why activity reports are frequently slow or time out.

2.  Misunderstanding how dates in activities work.  There are  a number of date fields in filteredactivitypointer—scheduledstart, scheduledend, actualstart, and actualend.  No one of these date fields are consistently populated on all activity types.  For example, tracked emails will typically just have an actualend date, and not a scheduledend or scheduledstart, and only closed appointments have an actualend date.  This is one of the main reasons why dates in activity reports and views do not work as expected.

To account for this, I recommend using a case in your SQL query to accommodate various date fields being null.  For example:

(case when actualend is not null then actualend

when actualend is null and scheduledend is not null then scheduledend

else scheduledstart

end) as date

This approach puts priority on the actual close date, so if the activity is closed, it will count that as the date of the activity, but if not, will use one of the scheduled dates. 

It is also very important to understand what your audience expects to see as the date of an activity—if they expect the date of an activity to be what it was scheduled as on their calendar, but they close the activity two weeks after the appointment, if you select actualend as the date of the activity in the report, the results won’t match their expectation.

3.  Misunderstanding how dates in SSRS report parameters in CRM work

Consider this common scenario—the user wants to enter a start date and an end date and show all activities that fall between @start and @end.  So, at first glance, you assume that the report query should say something like this:

“where date between @start and @end”

Not so fast.  You first need to understand that when you populate a date parameter in an SSRS report in CRM, it assumes the time of day when you are running the report.  So, for example, if you run the report at 3:30 PM on January 5, 2011 and say start date is January 2 and end date is January 4, you are really saying between January 2 at 3:30 PM and January 4 at 3:30 PM.  Any activities earlier in the day on the second and the end of the day on the fourth will not be returned.

The right way to do it is to adjust your parameter values so the start date is the beginning of the day and your end date is the end of the day.  For example:

“Where date BETWEEN DATEADD(dd, DATEDIFF(dd, 0, @start), 0) AND DATEADD(minute, 1438, DATEADD(dd, DATEDIFF(dd, 0, @end), 0)))”

In this example I am using dateadd to set the @start parameter to the beginning of the day specified, and using dateadd to add 1,438 minutes to the start time of the @end parameter.  All activities between these dates will be returned.

4.  Lacking a clear focus

Once you start building your report, it can sometimes grow beyond the original scope and purpose of the report.  Users will want to add additional fields and be able to group or sort by different fields to use the report to answer different questions.  This is fine, but be careful that in doing that you don’t over-complicate the report.

The best reports serve a specific purpose and are consumed in a specific way.  Reports that are exported to PDF should not have more fields than will fit in one page width.  If you keep adding fields to the report, your query will run more slowly, and you may exceed the number of rows that will fit on one page.

Formatting of reports is driven by the way the report will be used.  Column layouts that look good in PDF don’t often look good when exported to Excel, and Excel optimized layouts may not look good when exported to Word.  If you try to make the report exportable in all formats, you frequently will wind up with a report that doesn’t look very good in any format

Also, many times the users want the report to handle every niche scenario, so, for example, if there is a custom field that just exists on appointment records, they will want to include it in an activity report.  This small change can make a big difference in report execution performance, as it means having to join or union in the filteredappointment view in your query.  If you have additional custom fields on other specific entities, that makes it even slower.

Best practice is to limit your fields in the report to just the minimum necessary for the main use case for the report, and if there are any entity specific fields needed for other scenarios, you may want to build additional reports that just focus on those activity types.

Best Practices

1. Whenever possible use views and charts for activity reports
2. Whenever possible, report from the filtered views
3. Whenever possible, use the FilteredActivityPointer entity view for activity reports rather than unioning the specific entity views.
4. Build report people filters from the FilteredActivityParty entity view
5. Create your report with a clear focus, not an open-ended “all things to all people” activity report

del.icio.us Tags: SSRS,Microsoft Dynamics CRM,reports,activities

CRM 2011 and Restoring the SSRS server

To make a long story short, if you aren't careful, bad things can happen when you lock down a CRM IIS server. There are many articles on the internet that cover this topic, but changes to IIS range from local security permissions to registry hacks. We learned the following details as a result of an IT department hardening IIS. The one item a local IT department did in this situation was to remove “Domain Users” from the local users group.

I wanted to validate the SSRS connector installation on a fresh SSRS box installation. After building the base Windows 2008 server, installing and configuring SSRS, I thought it would be pretty straight forward to reinstall the Microsoft CRM 2011 SSRS connector. I was surprised when I got an error message and then noticed that “no default reports” existed on the new SSRS server. It turns out that Microsoft Dynamics CRM stores information in the config database that indicates that the SQL Server Reporting Services connector was previously installed. In the end, this makes sense because if you edited a “default” CRM report, you would not want a new CRM installation to write over the top of it. Although, it would be nice if when you uninstalled the last SSRS server that the bit would be reset to the default value so when you reinstall the SSRS connector it would reinstall the reports.

Here is how we came to understand the details on how Microsoft Dynamics CRM tracks information related to SSRS reports being installed.

• After we rebuilt the SSRS server, we ran the SSRS connector install on the SSRS server and no reports were installed and I received a new error message
• Since this was a development environment, we uninstalled CRM (since our deployment was all on one box except for SSRS) and reinstalled it to the exiting configuration and org databases and still no reports. My theory was that there must be a flag in the DB that tells the install not to republish reports because of it being an “existing” installation. You would not want to overwrite any customizations that you might have made to an existing MS report.
• We also found an our old friend “publishreports.exe” still included as part of the CRM installation on the IIS web server and not the SSRS server. If you recall this utility allows you to republish all the standard reports back to your SSRS installation hosting your CRM reports in CRM version 3 and 4.

We opened up a case with Microsoft support, and they clarified how it works. It stores a flag in the “MSCRM_Config” database that prevents the install from redeploying the reports when the SSRS connector is reinstalled. The following screenshots were provided by Microsoft Support and our direct relationship with them (Thanks Arpita!)

In the MSCRM_CONFIG database the Organization table now includes a value called “AreReportsPublished.”  If this flag is flipped, the installation of the Microsoft CRM 2011 SSRS Data Connector will take no action.

The following are the steps to redeploy your reports:

1) Please check to verify that the flag has indeed been flipped (Value 1). The following SQL Statement should show you the info needed.  AreReportsPublished should be the first column returned for each record.

Use [MSCRM_CONFIG]

Select * From Organization

2) If the value is 1, run the following update script to reset the flag back to 0……Be sure to set the proper Unique Name for the customers Org

Use [MSCRM_CONFIG]

Update Organization set AreReportsPublished = 0 where UniqueName = 'CRM'

3) Once the AreReportsPublished value is confirmed to be 0, you can run a repair on the Data Connector and it should read that value and import and publish the CRM standard reports.

The other option you have is to redeploy the reports using the “publishreports.exe” here’s how…

On the CRM server (not the SSRS server) change to the tools folder under CRM directory and run “PublishReports.exe “NameOfYourOrg”

Hope it helps!

FetchXML Report Record Count Limits Demystified

I was under the impression myself, and have seen it on the web, that there is either a 5,000 or 10,000 record limit for FetchXML reports. This is obviously a big deal when working with CRM 2011 Online. However, I am happy to report that this is a myth, and I have seen the light. Here is a screen shot of a Fetch report I did for an online environment:

When my good friends at Microsoft told me this I had to stop them and say: “It’s on the internet. It HAS to be true.” But the proof is in the pudding.

Now even though there isn’t this record limit, it still begs the question of whether it is appropriate to use reports to return so many records, or perform aggregations in the RDL file with this dataset. Does one really want to work with 893 pages worth of data? My recommendation is if it gets to that point, one should consider using rollup entities or in certain cases, using a data warehouse. Obviously every situation is different, and there are always trade-offs.

If you want our expertise in determining the best solutions, please contact us at 864.250.2170 or info@customereffective.com. Special thanks to my good friends at the Microsoft CRM team for showing me the light.

Cases and Resolution Activities in Microsoft Dynamics CRM 2011

Many CRM users use the Case entity in CRM 2011 to track issues. These issue range from customer complaints to tracking actual software development bugs.  The Case entity does a good job of allowing users to track a wide variety of issues and work on them them through resolution. What seems odd though is that when a user closes a case he/she cannot see those “resolution” activities anywhere.

The funny part of this problem is that a “resolution” is nothing more than another type of activity. Microsoft Dynamics CRM is just filtering out these activities from the activities views (see example 1) by setting the “Is Regular Activity” to “Yes.”

Example 1

You can probably see where we are going with this, but one of the easiest ways that we have found to show these on cases is to create a new view called “Resolution Activities” (See examples 2) and set the type of activity to resolution.

Example 2

As a result of those changes (Example 3), it is then very easy, especially under CRM 2011, to show the new view in a sub-grid on the case form.  Users can now view a closed case and see what the resolution. 

Example 3

Plain English Reports for Microsoft CRM: Removing HTML Tags Using the Placeholder

If you’ve ever used the FOR XML construct to include many child records in an SSRS report in one cell, you may have run into something that looks like this in your results:

James Diamond & Co., Some Other Firm

At that point you may fret about that “&” being tucked in there. Well fret no more, provided you use SSRS 2008. There is a nice little gem in there that allows for things like this to look like plain English, exactly as you intended. It’s called the Placeholder and here’s how you use it.

1. Set up your report with the table and dataset as normal.

2. Instead of adding the Fields!Whatever.Value to the table, right click in the cell. You’ll see the Create Placeholder option in the menu:

3. Set up the Placeholder dialog box as follows:

4. Save and run your report. That same problem cell from before will look like this:

James Diamond & Co., Some Other Firm, Inc.

Notice that nice little ampersand, instead of that HTML.

If you’ve never used FOR XML to bring in many child records into one cell or record, here is a snippet for you as a bonus:

SELECT

DealName

,(SELECT distinct name + ', ' FROM FilteredViewABC fa WHERE fa.accountid=d.cei_companydealid and fa.statecode=0 FOR XML PATH ('')) as 'Deal_Sources',

FROM FilteredViewXYZ d

I’ve run into this a couple of times, and I always forget where I saw this originally (or even where I have the bookmark). My compliments to those in the blogosphere first shared this.

CRM 2011 Dashboard: Sorting a Chart On the Aggregate Value

The other day I created a basic chart in CRM 2011 which showed the total portfolio value for each portfolio manager. The result was here (redacted):

 

 

As you can see, the values were all over the place, so naturally I wanted to sort it from highest to lowest.  Fortunately it was a quick matter of updating the chart XML with one line in the fetchcollection section after attributealias:

 

The end result was here (again redacted):

 

This was much better and easy to do.  Yet another example of how to refine the out of the box charts within CRM 2011 to visualize data.  If you’re interested in more examples, I’ve found this link from Microsoft to be quite helpful: http://msdn.microsoft.com/en-us/library/ee704599.aspx.

SSRS Charting: How to Align the Scale on Primary and Secondary Axes Using the Invisible Series

I’m writing this to help the poor person who might get stumped when putting together a fancy chart involving a primary and secondary axis where they just don’t seem to line up.  The intended audience is people who are not faint of heart and have done a report or two in their day.   I’ve come across this problem a couple of times now.

The example here is a chart with (originally) 3 different series, two of which were calculated values.  The problem was that the red bar chart representing actual sales should be lower than the combined value of Tier 1 and Tier 2. 

 

Tier 1 and Tier 2 are plotted on the Primary axis and Invoiced is plotted on the Secondary axis, which is not shown by design. It looks like Invoiced outperforms goal, which is not the case here.  So how do I align the values?  The answer was adding another series which would also be plotted on the secondary axis.  The formula here, in hopefully plain English, is: If gold – red  is less than 0, then use 0, otherwise return gold – red.  (Note that the IIF or SWITCH functions could be used here). That will add the difference on top of the red bar.  Here’s the trick: make this series invisible. This is done by not showing a legend and making the color value “No Color” in the series properties. The result is here for the same data with this invisible series:

 

Take Control of Your CRM 2011 Dashboard With the Report Control

The more I see and work with the CRM 2011 Dashboards, the more I like them.  One thing that I was curious about upon the first release was how to embed an SSRS report into a Dashboard, and of course our good friends on the Microsoft CRM Team had an answer for that here.    I’ve always been a big fan of the integration with SSRS reports and used it extensively in version 3 to extend the application in a seamless way for users.   The dashboarding capabilities in 2011 have been lauded by 2011  customers, developers, and consultants (or in our case “all of the above”) for good reason, and Microsoft having made the process of adding a report to a dashboard extremely easy is a nice touch.  I have a customer dashboard now which uses embedded SSRS reports, out of the box charts, and also custom charts (more on that in a separate post).

With all this said, the dashboarding and charting capabilities within CRM highlight the need to ask the question of when or whether  to use a report in the first place more than ever.  Given all the tools in 2011, reports should be a last resort.  SSRS is powerful, but the out of the box charting capabilities powered by Advanced Find make it overkill in many situations.  The rule of thumb is if you can put together the data in Advanced Find, use the out of the box charting tools over SSRS. 

Snazzy SSRS Reports in Microsoft Dynamics CRM

A client recently commented to me, “It would be nice to include our logo at the top of the report, along with maybe a date and time of when the report ran.”  - Great comment.  Being new to Microsoft SSRS (SQL Server Reporting Services), I had to do some investigative work in order to find what I needed.

Microsoft SQL Server 2008 in and of itself can be intimidating.  Luckily, I had some background in SQL, which enabled me to get my data set up, aggregated and grouped how I needed it to appear in my report.  For this demonstration, I used Microsoft Visual Studio 2008 (Version 9.0.30729.1 SP) to create my report.  Once you launch Microsoft Visual Studio, select File –> New –> Project:

Select the type of project you would like to create.  In my case, I am interested in a new report, so I selected “Report Server Project Wizard” and provided a name for my report, as depicted here:

Once you have your report project named, click “OK”.  This will then launch the Report Wizard, which will walk you through the connection information for the data source that you are using for the report.  From there, you can use the Query Editor to build your SQL query, or you can cut/paste your query into the Query String window, as depicted here:

So back to the question:  “How do you add a Logo and a Date/Time stamp in a report?”  Well, the answer lies in what SQL Server Business Intelligence Development Studio refers to as “Report Data”.  It can be found under View –> Report Data, as depicted in the following screenshot:

If it is not visible on the root menu list, go through “Other Windows” and it should be located in there.  This opens up another window in Visual Studio, which has the following options:

Under “Built-In Fields” are a number of handy fields that can be used in your reports.  For the report my customer was looking for, I used Page Number, Execution Time, Total Pages and Report Name.  I simply clicked on the “Page Header” button and dragged these fields onto my Header.  I also added a corporate logo by right-clicking on the header and selecting "Insert –> Image”.  After a little organization and formatting for font sizes and colors, my report was finished.

Within MS Dynamics CRM, go to Reports and click “New” to open up the New Report form.  From the drop-down menu, select “Existing File”.  Then, navigate to the folder location of your .RDL file.  This is depicted here:

Once the report runs, you can see your new header, title, page and date information:

Note:  You can also open a .RDL file from CRM in Microsoft Visual Studio 2008, edit the title or header/footer and then re-import it back into CRM.

For further information on writing reports, you can also refer to the Report Writer’s Guide in the Microsoft SDK, located here.

I hope this article helps you make your MS Dynamics CRM reports more informative and appealing.  Good luck with your configurations!

Reporting On Reports

I recently needed to catalog a lot of reports I had done on a project.  I would have just used Advanced Find, but unfortunately that did not give me access to the file name of the RDLs I had authored which was a key part of the catalog. Perhaps I missed something, but in any event I came across yet another benefit of the metadata driven architecture of Dynamics.  If you have looked under the covers of the CRM database, you no doubt know that there is plenty of information on entities and objects in the system.  Reports are no exception. 

I was able to come up with this catalog by querying the FilteredReport view.  There are a couple Report based views in the database, and I was able to pull together information such as the name, parent report, and file name, which was my original goal. Additionally, I noticed the bodytext field:  on a whim I copied and pasted a value from this field at random into Visual Studio. While it is an ntext field, it referenced XML.  In copying it to Visual Studio and viewing the code in the designer view, I was able to view a report I had written without having to download it from CRM.  (I’m curious to see how this works with very long reports, but this was a nice gem I had not anticipated).

Advanced Find would have gotten me most of the way to where I needed to go, but I’m glad I came across this as it was easy to use and had some nice unexpected benefits.

Using a Custom Entity to Define SSRS Cascading Parameters

If you have ever used cascading parameters in SSRS reports you know how useful they can be.  The problem I find is that setting them up can require the dependencies to be hard-coded in the SQL.   One of the great things about the Dynamics platform is the ability to quickly generate a custom entity, and this comes in handy for cascading parameters.  By using a custom entity, the SQL in the SSRS report would need not be modified, but instead the SQL could read the combinations in the custom entity you’ve set up.  Here’s a hypothetical list of combinations:

 

Color – Object

Blue – Car

Blue – Crayon

Blue – Pen

Blue – Widget

Red – Car

Red – Crayon

Red – Apple

 

So your Object list would be the child picklist based on whatever was selected in the parent Color list.  Easy enough, but inflexible in that it could only be modified in the SQL.  So instead just use a custom entity which holds these values, in this case the two fields of Color and Object.   As Colors and Objects change, the custom entity can be updated rather than SQL.  Make sure to give users at least the ability to read these, but certainly not add to the list.  That should be left only in the hands of someone who knows the reasons behind using this entity in the first place. 

The premise behind this idea is nothing new, as without Dynamics the same thing could be applied to SQL tables, but the beauty of Dynamics is that much of the work that would heretofore be delegated to a DBA can now be handled from within CRM.

For more information on setting up a cascading parameter to begin with, see this article.

Conditional Formatting And Me

If you write reports in SSRS and grew up writing reports in Access, you might be interested to know that you can reference report items in SSRS as you might have done in Access.  Specifically, you can use my favorite identifier: “Me”.   This comes in handy for conditional formatting where using the IIF function (or even better the Switch function, again an oldie-but-goodie from Access) you could do something like this: iif(Me.Value<0,"Red","Black") in the Color property of a field.

Someone out there reading this blog is no doubt saying “You could do the same thing using the built in formatting in SSRS!”. This is quite true, but for the sake of brevity just work with me. And just for those of you out there not in a giving mood consider this then: Switch(Me.Value<0, “Red”, Me.Value>0 AND Me.Value<100,”Purple”, True, “Aqua”).

The nice thing about using Me is that you can then copy this statement in the Color property of other fields that have the same formatting rules, and this is what really makes it useful. If I didn’t use the Me identifier, I would have to change the IIF or Switch statement to reference the particular field for which I’m the introducing this conditional format.  This came in handy for me quite recently, and I confess I didn’t realize it existed.  For more information, see this article from TechNet.

What to do if you can’t publish a report in Dynamics CRM

Sometimes when writing SSRS reports for CRM, you will find that the report works fine in Visual Studio, but will fail when you upload them to Dynamics CRM.  This can be frustrating, because it can sometimes be difficult to identify the cause of the issue—it works fine when you preview it in Visual Studio, but when you upload it, the upload will fail, and typically the error message created will be very generic—something to the extent of “Call your System Administrator.”

Typically when you see this problem, it has to do with SSRS functionality specific to CRM reports.  Here are the top 3 causes that I see reported by users:

1.  Data source—When you create an SSRS report, you have the choice of using an Embedded data source or a shared data source.  For CRM reports, you must use an embedded data source.  If you use a shared data source, your report will preview in Visual Studio, but you will not be able to upload it.

2.  Hidden parameters—If you use the special hidden CRM parameters, such as the CRM_URL parameter to build dynamic hyperlinks to CRM records, if you don’t set them up properly, the report upload to CRM will fail, but it will still preview OK in Visual Studio.  Make sure that the parameters are set to hidden and allow null values.

3.  Pre-filters—When using CRM Report Pre-Filtering, if you use pre-filtering incorrectly, sometimes the report may fail to upload correctly.  In this case, like the others, the report will preview without error in Visual Studio, but will have issues uploading to CRM.

Troubleshooting

So now that we’ve talked about what some of the common causes for the “works in Visual Studio, but not in CRM” issue, how do you troubleshoot what is causing your report issue?  The best place to start is the event log of the CRM Server.  If you get a “Contact your Administrator” error message, frequently you will see a more detailed error message.  Recently I had a user experiencing this issue, and I checked the event log on the CRM server.  I found the following error message captured in the event log:

The parameter ‘CRM_URL’ has no default. A default is required for all non-nullable parameters without a prompt or the valid values list has to contain Null.

After modifying the SSRS report CRM_URL parameter to accept null values, the report could be successfully uploaded.
                     

del.icio.us Tags: CRM,reporting errors

SSRS: Working with Multi-Value Parameters

An SSRS feature that was introduced in Visual Studio 2005 is the ability to add multi-value parameters (MVPs) or basically parameters that allow the user to choose from a set of values and apply them to a report. Here I will describe how to setup an SSRS report with multiple MVPs, along with tips that will assist you to utilize this functionality in Visual Studio 2008.

1. Setup your main dataset that will populate your report.

Here is an example statement that returns a few different values from the FilteredOpportunity view:

SELECT new_saleschannelname, accountidname, owneridname                                  FROM FilteredOpportunity

2. Utilize separate datasets for each Multi-Value Parameter.

After setting up the main SQL dataset, create additional datasets to return a list of values to represent each individual MVP.

TIP: When possible use the DISTINCT command to pass only the values that will be available to the main SQL dataset.

Here are the three datasets used for this example:

A. Sales Channel:                                                                                                       SELECT DISTINCT new_saleschannelname                                                                FROM FilteredOpportunity                                                                                       ORDER BY new_saleschannelname

B. Account:                                                                                                                   SELECT DISTINCT accountidname                                                                             FROM FilteredOpportunity                                                                                      ORDER BY accountidname

C. Owner:                                                                                                              SELECT DISTINCT owneridname                                                                              FROM FilteredOpportunity                                                                                         ORDER BY owneridname

3. Setup the Multi Value Parameter(s)

Add each MVP to the list of report Parameters as follows:

General Tab: Set the Name and Prompt for the parameter. Check the Allow blank value (""), and Allow multiple values. Set visibility to (Visible).

Available Values Tab: Select 'Get values from a query' and utilize the corresponding query for your MVP. Select the correct dataset and set the Value and Label fields from your query.

TIP: Default Values Tab - If you want to have all values for an MVP selected by default you can set the default value of the parameter to the same Value field used in the 'Available values' tab.

4. Use the "IN" statement to call the MVP from the WHERE clause in the main SQL dataset.

The statement (FieldName IN (@Parameter)) tells SSRS that @Parameter is a multi value parameter and to act accordingly.

SELECT saleschannelname, accountidname, owneridname                                           FROM FilteredOpportunity                                                                                     WHERE (new_saleschannelname IN (@Channel))AND (accountidname IN (@Client)) AND (owneridname IN (@Owner))

Also, be sure that each parameter is properly declared in the Properties of the Main SQL dataset:

TIP: If you don’t properly declare each parameter using the IN statement or in the dataset parameters you will receive the error “Must declare the scalar variable “@Parameter”.”

5. The result is a clean report that gives users the power to filter the results of a report to very specific data points:

CRM Pre-Filtered Reports and SSRS P1 Parameter Error

A few weeks ago, we upgraded some environments to SQL 2008 R2.  After that upgrade, we started to see a strange issue when uploading a report with pre-filtering to CRM.

The report will upload fine, but when you run the report, you get an error regarding parameter P1.

When you upload pre-filtered reports in CRM, CRM adds a parameter called P1 to the report, which is used in pre-filtering.  This parameter is a hidden parameter used behind the scenes by CRM.  It appears that since my upgrade to 2008 R2, the parameter is not being correctly set.  I’m not sure if this is a bug, or a quirk with this environment, but if you have a report that has this issue, I have a way to fix it.

The fix for affected reports

If you have reports that are returning this error, here’s how you can get them to work correctly:

1. Go to SQL Server Reports Manager.  This url is typically something like http://yoursqlserverurl/reports.  You will need to be logged in as a user that has Content Management permission in SSRS.
2. You will see a folder for each crm organization.  Click the folder for the affected organization.
3. If the folder is empty or says “there are no items to display,” click the “Details View” button on the right side.
4. You should now see a subfolder called “4.0.”  Click it.
5. In the 4.0 folder, you will see a list of the reports deployed in the selected organization.  You can use the search to search by report name.

6.  Hover your mouse by the desired report.  A yellow drop-down arrow will appear.  Click the arrow and select “Manage.”

 

7.  Go to the Parameters link on the left side of the Manage report form.  You will see a row for the P1 parameter.  Check the box for “Hide” to make the parameter hidden.

The report should work now.

del.icio.us Tags: SQL 2008 R2,Pre-filtereing,Microsoft Dynamics CRM

Embedding CRM Pre-filtered Reports in CRM Iframes

Sometimes you want to enhance a form beyond the standard form components.  For example, you want to display a graph of related records, or show a custom block of text, or data from a different system, not stored in CRM.

One of the easiest way to do this is by displaying a SSRS report in an iframe.  There are several blogs that include instructions for doing this, but many are not done in a way that really works well in Dynamics CRM 4.0.  Most of the posts I’ve seen detail embedding a report URL directly from the SSRS server, not through the CRM Report Viewer.

While these approaches can work with CRM 4, they have several disadvantages:

1. They don’t take advantage of pre-filtering
2. If it is a report that you also run through the report menu, it requires that you maintain two versions of the same report (one in CRM, one External to crm).
3. Embedded reports are not available to IFD users (or on-prem users without direct access to reporting server)
4. Embedded reports are not available offline

I prefer to do things in a way that works well no matter how you access CRM—on premises, IFD, offline, etc.

Fortunately, it is possible to embed a report in a form iframe in a way that harnesses the standard CRM report viewer, and also harnesses crm reporting functionality, such as the CRM_URL parameter and pre-filtering.  Here’s how to do it:

1. Upload your pre-filtered report to CRM, just like you would for reports run via the CRM report menu, selecting the entity to which you want to relate the report
2. Add an iframe to the form that you want to embed the report.  In this example, I call it IFRAME_report.  Give it a URL like “about:blank.”
3. Run your report for a specific record.  Open a record from the desired entity, click the reports button.  Select “Run on Current Record” and select the desired report.

5.  When the report runs, copy the URL of the Internet explorer window.  It will look something like https://crmserver/crmreports/viewer/viewer.aspx?action=run&id=%7b315C371A-4693-DF11-A498-000C29F9FFEC%7d&context=records&recordstype=10007&records=%7bDE38FBCE-79D0-DE11-A9E1-000C29F9FFEC%7d&helpID=CPUH.rdl

You will notice that the URL contains two GUIDS.  The first(colored RED in the example) is the ID of the CRM report record, the second (colored GREEN in the example) is the ID of the record to which the report is pre-filtered.

6.  Using CRM onload jscript, change the URL of your iframe to the URL of the report, replacing the ID of the record with the ID of the record displaying the iframe.  Here’s a code snippet—replace the URL in the example with the url copied in step 5:

var newTarget = "/crmreports/viewer/viewer.aspx?action=run&id=%7b315C371A-4693-DF11-A498-000C29F9FFEC%7d&context=records&recordstype=10007&records=%7b" + crmForm.ObjectId.replace('{','').replace('}','') + "%7d&helpID=CPUH.rdl";
   crmForm.all.IFRAME_report.url = newTarget ;
   crmForm.all.IFRAME_report.src = newTarget ;

The result will be a report embedded in an Iframe which will be available to all CRM users, no matter their connection type.

del.icio.us Tags: Microsoft Dynamics CRM,CRM 4,Reports,prefiltering

Microsoft CRM 4.0 database structure (to find your data and report on it)

Having become so familiar with the CRM database structure I almost take it for granted.  There is great value in how Microsoft designed and built the database and how it handles creation of new entities.  I found myself explaining the benefits of how the database is structured twice this week so I figured that warrants a post.
 
The CRM database is structured with views that prevent the need to query the tables directly.  Each entity in CRM consists of two tables in the database.  For example, the account entity consists of the AccountBase and AccountExtensionBase tables.  The AccountBase table contains the out of the box attributes and any custom attributes are in the AccountExtensionBase table.  The database has a view named Account that automatically joins those two tables so when querying for data you would query the view and use a query such as ‘select * from account’.  This view also has joins to related tables based on foreign key ids to retrieve the name text value of the related entity.  For instance, it contains the ownerid but also contains the owneridname which returns the name of the owner.  This can prove handy when creating reports.
Another view also exists named Filtered<entity name> (i.e. FilteredIncident).  This view has the same structure as the Incident view yet also applies the system security to that view.  This allows someone to write a report using the FilteredIncident view named ‘My Cases’ and distribute it to the entire organization.  When executed, the report is filtered to only show Cases that the executing user has access to.
Most tables and views in CRM bear the same name as it is labeled in CRM yet a system customizer can rename an entity.  Case is one object out of the box that has underlying tables with a different name.  The Incident tables and views represent the Case entity in CRM.  Any custom entites you create contain the customization prefix in the table and view name as defined in the CRM System Settings.  Once the new entity is created, the supporting tables and views as I defined above are automatically created for you and no additional work is needed on the database end.
If ever you are unable to locate the tables and views that support an entity, you can locate that in the Microsoft Dynamics CRM interface.  In CRM, click “Settings” in the Wunderbar, click “Customization” in the navigation links on the top left of the screen, and then select “Customize Entities”.  Find the entity you are interested in by viewing the “Display Name” column as that represents the name used in the user interface.  The “Name” column will show you what the underlying table/view name is in the database.
When you open an entity in the CRM customization area, you can locate the 1:N, N:1, and N:N relationships for that entity in the left navigation area.  It will display the name of the foreign key id field in the related entity if you are viewing a 1:N relationship for instance.
This post should give you an idea of the database structure in Microsoft Dynamics CRM and the value it provides for reporting or other purposes.

Invalid Argument Error When Posting A CRM 4.0 Report

Just in case you make a whole bunch of changes to a report and then get an “Invalid Argument” error when you finally post it to CRM, try the following first.  If the report used to have a parent report but no longer does, be sure to remove the value from the Parent Report field.  I came across this situation and thought it was something to do with the changes in the RDL file I had just done.  It was actually a change I made in the Parent Report (making it no longer the Parent Report).  Either way, removing this value cleared up the error and I was on my way. 

CRM Picklist Values for Your Report Parameter

It’s pretty common to have a pulldown menu for your report parameter, but what if you wanted to use the same picklist value that’s in a CRM form?  Here’s one way I go about it:

SELECT DISTINCT crm_picklistvaluefield, crm_picklistvaluefieldname FROM filteredcrm_entity

Or

SELECT crm_picklistvaluefield, crm_picklistvaluefieldname FROM filteredcrm_entity GROUP BY crm_picklistvaluename

The net result is that this will give you the picklist values, but there can be a performance cost, especially if either of those SELECT statements are going across a large recordset. So we know that the picklist values are stored in the CRM database somewhere.  The question then becomes, how do we get our report access to them in a way that CRM security allows? The answer is the FilteredStringMap view. 

In lieu of the above, I would instead use the following SQL statement:

SELECT  AttributeValue, Value

FROM FilteredStringMap

WHERE FilteredViewName='filteredcrm_entity’

and

AttributeName=’crm_picklistvaluefield’

Of course, by all means test this against your existing set-up before you start incorporating this into your reports.  As a start, I recommend taking the user security role with the lowest level access to insure that it works for them.

Securing Sensitive Reports in Microsoft Dynamics CRM

A question that I get from time to time is how to be sure that certain reports are only visible for certain people.  This can be for security reasons—for example, a sales manager report that shows activity levels of employees may deal with publicly available data, but graph or aggregate it in a way that may be embarrassing to some users.  Other times it can be that an administrator wants to simplify the list of reports available to end users, and not have a bunch of reports that are not relevant for a user to wade through to get to the reports that matter to him.

I occasionally get this question from some users who have made a report that they believe to be private, only to realize later that other users can view the report. 

Making a report private requires two components:

1.  The report “Viewable by” setting:  When you select a report and click the “edit report” button and go to the “Administration” tab, you will see a radio button called “Viewable By” with the choice of Organization or Individual.  This report determines if the record should function as an organization owned record, or as a user owned record. 

The fact that the field is called “Viewable By” has frequently lead to the misconception that if I set a report to be viewable by Individual, only the owner will be able to see it.  That is not necessarily true.  It all depends on #2.

2.  The user’s Report entity Read permission:  Keep in mind that a report is a record in CRM, just like any other record, and the same rules apply for security.  A user’s read permissions for Reports in their security role determine which reports they will see.

• All users will see Organizational Viewable Reports
• All users will see User Viewable reports that are owned by them
• All users will see User Viewable reports owned by other users within the scope of their Read permissions.  For example, if a user has Business Unit read permission for Reports, they will see reports owned by other users in their business unit, even the ones set to viewable by individual, such as “junk” reports created by other users with the report wizard.

Most of the time I see users who inadvertently give other users access to private reports, it is due to the report read permission being set too high.  Some have done this because they think that a user has to have Organizational read access to read Organization Viewable reports—this is not the case.

So if you really want to have private reports, I recommend the following best practices:

1. Set all security roles except for system admin to user level read access.
2. Only give organization-level “create Report” permissions to users whom you wish to be able to deploy organization viewable reports .  Give everyone else user level create permissions.  Otherwise, users will make their private reports Organization Viewable.
3. Rather than making reports that are only needed by a handful of users organization viewable, make them user owned and use CRM’s sharing functionality to expose these reports to the other users who need them.
4. Have a user or group of system administrators who can promote user viewable reports to organization viewable.

This will keep your private reports private, and make the report menu more navigable for the majority of your users by removing the clutter.

del.icio.us Tags: Microsoft Dynamics CRM,reports,security

Simplified Dynamic Drill-Through to Microsoft CRM Using Logical Entity Name

Previously on the CEI Blog, we have covered using the CRM_URL hidden parameter to dynamically build hyperlinks in SSRS reports.  For those of you not familiar with CRM_URL, a little background:

CRM_URL

One of the things that makes the combination of Microsoft CRM and SQL Server Reporting Services great is drill-through.  A report, like a sales pipeline report, becomes much more usable if you can click on the opportunity name and pull up the CRM record for that opportunity.  An easy novice report-writing mistake, when building reports with hyperlinks to CRM records, is to hard code the hyperlink URL using the URL of your CRM server.  This approach will work; however, the hyperlinks will only work in that one environment, and only for users who access CRM that way.  For example, if you want to take the report and upload it to another environment, the hyperlink expressions will need to be re-written, and if you have users who access CRM in other ways, such as offline, the report hyperlinks will not work.

That’s why Microsoft included the CRM_URL hidden parameter.  This allows you to build a hyperlink to CRM that dynamically changes based on the environment and how the user is accessing it.  If a user accesses a CRM report offline, if you use the CRM_URL parameter in your hyperlink, the links will point to offline environment while offline.  Bigger benefit is that the report can also be moved between environments without having to reconstruct the jump-to hyperlink expressions, which is essential to proper change management.

So instead of your hyperlink expression looking like this:

=”http://crmserver/orgname/sfa/conts/edit.aspx?id={“ & Fields!contactid.Value.ToString() & "}"

It should look like this

Parameters!CRM_URL.Value & "?OTC=2&ID={"& Fields!contactid.Value.ToString() &"}" 

For more information on how to set up the CRM_URL hidden parameter in your SSRS report, see the dynamic drill-through section of the Dynamics CRM SDK.

Dealing with Custom Entities

In the above example, we’re using the CRM_URL parameter to drill-through to a CRM Contact record.  The “OTC=” part of the expression defines what entity the hyperlink should link to, using the Entity Type Code.  Each entity in CRM has an Object Type Code.  For system entities, these codes are always the same.  See a list of system entity type codes in Dynamics CRM.  So if you are drilling through to a standard system entity, the above example will work fine.  The Contact Entity is always OTC 2.

But what about custom entities?  Custom entities also have an entity type code; however, the type code for the same custom entity may be different between two environments.  The reason is that the entity type code is set sequentially based on the order the entities in an environment are created.  If you are drilling through to a custom entity, the example above will still work for you if you specify the OTC of the custom entity in the environment to which you are publishing the report; however, when you move that report from your test environment to your production environment, there’s a good chance that the hyperlinks may have to be re-written.

Fortunately, there is an easier approach than the example specified in the SDK.  By replacing “OTC=” with “LogicalName=,” we can define the drill-through entity using the entity name rather than the entity type code.  For example, say we have a custom entity named “Truck,” we can drill through to it using the following expression:

=Parameters!CRM_URL.Value & "?ID="& Fields!new_truckid.Value.ToString & "&LogicalName=new_truck"

And we can now move the report between environments without having to re-write the report hyperlinks.

del.icio.us Tags: Microsoft CRM,reports,ssrs,drill-through

Truth In (SSRS) Labeling

If you’ve ever used a drop-down list in an SSRS report to pass parameters, you may have wondered how to reference the label of the parameter in the report.  Let’s set up an example to illustrate the problem.  Say I wanted a drop-down list to reference colors.  The parameter selected is already in my SQL WHERE clause (i.e. SELECT widgetname, widgetplant FROM widgets WHERE widgetcolornumber = @color).

So I’ve configured my parameter to look like the following (For illustrative purposes only, among other things it’s almost always a bad idea to hard code like I’m doing below: what if someone wants blue? And remember, someone will always want blue.):

I’m passing the number 1, 2, or 3 (see Value column). I could reference this in a text box or table header by using the following syntax:

=Parameters!color.Value

But how would I reference the label, which is the name of the color itself?  If you’re like me, you probably typed right past the answer when you were building that syntax, so let’s go to the instant replay slo-mo of what happened when you started typing =Parameters!color:

There are a couple other options there.  The one we’re interested in is the Label function.  If you used that, your syntax would be:

=Parameters!color.Label

If you were to create two text boxes in this example, and set the drop-down to “Red” the text boxes would read 1 (Value) and Red (Label).

The other thing to keep in mind here is that you can pass this Parameter label as a parameter value to another report.  This is the parameters dialog box from the Jump To Report selection:

This can come in handy in drilling through to other reports.

The alternative to using the Parameter Label construct is to set up a set of cascading parameters, which if you’ve had some practice at doing it’s not that big of a deal.  But those machinations just aren’t worth it when you use this.   IDs, like CRM GUID’s or picklist values, are almost always (and almost always should be) passed as parameters, and the need to know the text associated with that ID and display it in a report comes up constantly.

Finding Your Inner Report Server

Ever wonder where your reports go when you use the Publish Reports for External Use menu item?  Ever need to know what the URL for the report server for the CRM reports is but your friend in IT isn’t available?  Fear not, SSRS has a handy little function designed just for these situations.  It’s a function called ReportServerURL and if you use Visual Studio to develop your reports it has been right under your nose.

To show how to use this, let’s take an existing CRM report (or create a brand new one) and add a text box to it. Add a border to it as well, as that will be important later. Right click and from that menu click Expression.  

The Expression Editor will appear next. Click to Globals à ReportServerURL. Notice the red arrow that points to some nice descriptive text. Don’t look for the red arrow in Visual Studio though, that’s just me being extra helpful.

Click OK and your text box should look something like this:

Now run the report locally.  Your textbox, provided you added that border, will look like this:

 
Nice function, huh?  The reason is blank is because you’re not running it from the Report Server yet.  So let’s remedy that by posting the report to CRM and the running it. Your textbox should look something like this, with names changed to protect the innocent:

 

Copy and paste that address into IE and you should see into the report server itself. 

So if you’re ever in a pickle (how did that expression ever come about and under what scenario?), and you need to know the report server URL you are in luck.  Look for a future blog on other reasons why you would want to know it and take a look at the sister functions here called ReportFolder and ReportName to get a better idea of some not so popular but useful SSRS functions.

 

 

 

 

 

 

SSRS List of Month Numbers Using Common Table Expressions

One common task in report writing is to give users a list of months to select from and use it as a parameter.  There are a couple of ways to approach this from using a cursor/loop to even doing a series of UNION statements.  However, one tool that came around with SQL Server 2005 is the Common Table Expression (CTE).  CTE's are great for a lot of reasons including their usefulness in tidying up long SQL statements.  (I say that, but I won't be talking about how here). Notwithstanding, this quick CTE can provide you with the numbers one through twelve:

You probably recognize this screen shot from Visual Studio where I'm using it as a dataset. I in turn use this dataset in the Available Values section for a parameter and set the default value of the parameter using =Month(Now()).

 
I could also do something similar to get the past 5 years:

 
These just small examples of what CTE's can do.  If you're not using them, definitely check them out. I hope you find this useful.

CRM 4.0 Publish Report for External Use Puts Default Filter In Report

The other day I was working on a few custom SSRS reports for a client. They need these reports viewable both inside and outside of CRM. Sounds easy enough. After I built the first report I logged in to CRM and went to create/upload it as an existing file. The file uploads great and I was able to relate the report to similar record types and choose where the reports would be available. The report works great!

Here comes the part where I got stumped. When I went to publish the report for external use and went to test the report on our report server, the data returned was incomplete. For some reason the report kept returning me a smaller portion of the data without me choosing any parameters. I was worried that the filtered views were not working correctly when I published the report externally. They worked fine in CRM? So for the next hour I did everything by profiling the database when the report runs to changing permissions in SSRS to try and be able to view all the records for the report. I then started testing the report that was in CRM and noticed every time I went to run my new report it had a default filter of modified on 30 days. Could that be the issue? So as a test I decided to remove the report from SSRS and then remove the default filter in CRM for the report. Once the filter was removed and the report was saved I published it back for external use and went back to the report server to test. Ding Ding Ding! The report returned all the data now. Microsoft probably does this to prevent the database from returning too many records when the report is ran and just copies the default filter over to SSRS when it publishes it.

So for a potential gotcha that you might have in publishing report for external use, make sure to remove the default filter Microsoft puts on all uploaded RDL files in CRM, unless you want a filter added to your external report.

CRM Data Integration Considerations

UK MVP David Jennaway has a fantastic post on the CRM Team Blog today about the considerations when integrating external data with Microsoft Dynamics CRM.  It is a great overview of the various approaches—when to move data into CRM, and when to integrate data by leaving the data in the external system and displaying it in CRM via a report.

I find that frequently integrations can be simplified using the report method.  if no CRM processes are driven by the data, and the data does not need to be displayed in CRM views, a report can be a quick way to make data available to CRM users without having to move the data into CRM.

Worth the read--Check out the article here.

Technorati Tags: Microsoft Dynamics CRM,data integration

Including External Data In a Microsoft Dynamics CRM 4.0 Report

So you want to create a report that references data from another database.  Perhaps you want to correlate your CRM data with your ERP data to see how CRM activities are affecting your orders in financials.  Of course, you could do an integration and bring the ERP data into CRM.  However, integration can take a big investment of time and money, and a report can be an affordable alternative if you don’t need the data for other purposes.

Another reason for doing this is if you want to have reports reference a mirrored copy of your CRM database, so heavy reporting doesn’t impact CRM performance.

The Challenge

To understand the challenge of reporting from external databases from CRM, we need to discuss the way that CRM reports data sources work.  When you upload a SSRS report to CRM, the data source of the report is modified to point to the CRM environment to which the report is uploaded.  For example, if your datasource referenced in your SSRS report is Contoso_MSCRM and you upload the report to a different environment with database ACME_MSCRM, when you upload the report, the data source will be changed to ACME_MSCRM.\

This is typically a good thing, as it enables us to write a report for one environment and upload it to multiple other environments without having to modify the data source.  However, in this case, it creates some challenges.

Say I have a report where my Microsoft Dynamics GP database is the data source.  If I upload the report to CRM, it will change the data source to CRM, and the report will not work.

One option is to upload the report directly to the report server, bypassing CRM reporting.  When you upload the report directly to the report server, the report retains the original data source.  You can then make the report accessible to CRM users by creating a new report record and selecting “Link to Web Page” and adding the URL of your report on the report server as the “Web Page URL.”

This is not always a great option.  You lose certain CRM report functionality, like prefiltering, and users who don’t have access to the Report Server (such as users who access CRM via Internet Facing Deployment) will not be able to run the report.

The Solution

In this example, my crm database is called CRM_MSCRM, and I have a copy of my CRM database called Mirror_MSCRM

1.  Create SSRS report, selecting your production MSCRM database as the data source.  In this case I selected CRM_MSCRM as my data source

2. Write your query referencing the external database.  For example, in this case, I want to select my accounts from the mirrored database.  This is how I would write the query

SELECT     * FROM MIRROR_MSCRM.DBO.ACCOUNT

The result is that our report data source is our production database, but the query will return data from the external database.  This is a good start, but there is one more thing we will want to do.

3.  Join your query to the Filtered Views of your CRM database.  This will allow us to use prefiltering, and in the case of your mirrored database, this will prevent security issues, as users will only return records that they have a right to view.

SELECT     * FROM MIRROR_MSCRM.DBO.ACCOUNT inner join
FilteredAccount AS CRMAF_FIlteredAccount ON Mirror_MSCRM.DBO.Account.accountid=CRMAF_FilteredAccount.accountid

Now you can upload your report through CRM, use CRM prefiltering, reflect data from your external database, and your CRM data will be secure. 

One consideration is that the user that is running the report will need to have database read permissions on the external database to be able to read the report.  Also, if you are running the SRS Data Connector, you will need to give the NT AUTHORITY\NETWORK SERVICE read/select permissions on the external database.  After you do that, IFD users will be able to run the report.

Filtered Views and Picklist Labels

When you do a SQL query of the CRM database, such as for a report, the recommended, supported way to query is using the Filtered Views.

By default, these are the only database views to which CRM users have access.  The filtered views have several benefits:

1.  They support Dynamics CRM security.  When running a query of the filteredaccounts view, for example, users will only be able to see the records that they have permission to view.

2.  They retrieve the metadata for CRM picklist labels, which will make your job much easier than the alternative, working with the stringmap table.

When writing reports for CRM, it is important to use the filtered views whenever possible to avoid giving crm users access to data that they shouldn’t have.  For more details about the filtered views, recommended reading is one of the first posts on the CRM Team blog, Why Filtered Views Are Cool.

Considerations

I had an interesting experience recently where we added a user to an environment to be a report writer.  We added this user to CRM and gave her a security role.  She could query the filtered views; however, when she got the results of her query, all of the picklist names were null.

I tested the other users, and they could query the filtered views and retrieve the picklist names.

I knew that she had never logged in to CRM yet, so on a whim, I asked her to log in to CRM.  After that she tried again, and this time, the query retrieved the picklist names.

Turns out that  when you create a user in CRM, the UILanguageId in the usersettingsbase table is set to 0 for that user. When the user logs in the first time, CRM sets the uilanguageid based on the Internet Explorer language settings. This is for the case where you have users in another language that may not be able to read the base language to change their language settings. 

The picklist names you see are based on the language code the user has set.  If the uilanguageid isn't set then it could cause the picklist names not to appear.

Lesson learned:  Have users log in to CRM at least one time before they write queries/reports.

Technorati Tags: Dynamics CRM,reports,filtered views

Preventing users from executing subreports

Recently a reader of the CEI blog posted a question.  Her question was how to deploy subreports through Microsoft Dynamics CRM and prevent users from running them outside of the parent report.  For example, if you have an account summary report with an embedded contact subreport, you don't want users running the subreport outside of the context of the master account summary report.

The first thing to understand is that you do have to publish the subreport through CRM, if the subreport is embedded in the report.  For more information on uploading subreports and linking them to their parent, see this post.

If your subreport is launched from a hyperlink in the parent report, you do not have to upload the subreport through CRM--you can upload it directly to the report server and link the hyperlink to the URL of the report on the report server.  You will need to use the "jump to hyperlink" option instead of the "jump to report" option in SSRS 2005.

For the context of this post I am assuming an embedded subreport that is uploaded via CRM.  How can we upload subreports through CRM and make them unavailable for users to run outside of the context of the parent report?

1.  Remove the subreport from the main report menu.  In Microsoft Dynamics CRM 4.0, the default view for reports is "Available Reports."  If you change the view to "All Reports, including subreports," you will see a much larger set of reports.  This view includes subreports for the canned CRM reports, such as the "Account Distribution Detail" subreport, which is the subreport for the standard Account Distribution report.  By selecting to not display the report in the "Available reports" view, the majority of users will never find it, and you won't have to worry about them running the report outside of the context of the parent report.

To prevent a report from being displayed in the "Available Reports" view, clear the "Display in" lookup field on the report information record when you upload the report (or click "edit report" from the report entity view and clear it out for an existing report).

2.  Strategically name the subreport.  When you create a subreport for a complex report, naming of the subreports can be an important decision.  You want a name that makes sense to you, but not one that will be confused for a different report.  For example, in the Account Summary report, name the contact summary subreport something like AS-CS subreport.  Then in the description provide full details of what the purpose of the report is.  Anyone who was updating the report from visual studio would be able to see what the name of the subreports in the report are; however, an end user would probably not select this report outside of the context of the parent report.

3.  Disable the subreport.  In the context of our account summary report, we are embedding the contact summary subreport in the parent account and passing the GUID of the account to a parameter in the subreport.  Let's call that parameter "accountid."

One trick to make the subreport unusable unless executed from the parent report is to make the contents of the subreport hidden when executed outside of the parent report.  Here's how I do it:

a.  Set the parameter to "allow null values."  In Visual Studio on the Layout tab, click the Reports menu and select "Report Parameters."  Select your report parameter from the left.  From the report parameters screen, click the "Allow Null Values" checkbox, and under "Default Values" select "Null."

b.  Set your report table or matrix to hide if parameter is null.  Select a cell in your table, then right click on the outer border of the table and click "properties" to open the table properties. Go to the "Visibility" tab.

Under "Initial Visibility:" select "Expression:," then enter an expression to hide the table if the accountid parameter is null.  It is important to remember that in the case of SSRS visibility expressions, True=hidden, and False=visible.

For example, the following expression will hide the table if the accountid parameter is null:

=iif(Parameters!accountid.Value is nothing, true, false)

The result will be that the subreport will be unusable by most users if not executed from the parent report.

Summary

I have described three approaches to insuring that subreports are used in the correct context. The first option will work in most cases, the second will further obscure subreports from availability to end users, and the third should only be necessary in extraordinary circumstances.

Technorati Tags: reports,subreport,Microsoft CRM,Dynamics CRM,user management

Automate Report Delivery in SQL Server Reporting Services Using Subscriptions

One question that I get asked fairly frequently is how to set up automated SSRS report delivery with e-mail subscriptions.  This capability is great for reports that get run on a regular basis, such as a sales pipeline report for a weekly sales meeting.

Microsoft TechNet has posted a great video showing the process of setting up e-mail subscriptions.  Some CRM specific consideration to setting up e-mail subscriptions with CRM reports:  report subscriptions require stored credentials, so you have to publish the report for external use through CRM or publish the report directly to the report server.  Functionality such as CRM prefiltering and CRM hidden parameters such as CRM_URL will not work, and the data included in the report will be based on the security role of the stored user, so e-mail subscriptions should not be used for sensitive data.

Technorati Tags: SSRS,SQL Server Reporting Services,Microsoft CRM,reporting