How to Force Zero Data Points in Date Charts (SSRS)

My team was recently challenged with producing a line chart that uses CRM data to provide counts of records over a period of time. What we found is that if there is not data for a given day, it is quite hard to force the line to have a zero data point instead of completely ignoring the given day. This post explains one of the solutions we found.

It is quite natural to want to report in chart data such as “Show me per day the count of cases that are open on that specific day”. However, the requirement was also that if there are no cases open on a given day, the X axis should still contain the label for that day but the data point should be zero.


The problem: If you don’t have any records for a given date, the report will automatically exclude the date from the X axis as there is simply no datapoint (notice that empty or null datapoint is not the same as not having a datapoint at all!). This is what out chart looked like initially:





Solution Attempt #1: We played with the chart properties and the X axis interval to force the chart to show the X axis per day, even if no data exists for a particular day. This was quite simple, but the result is not as expected:


Note that the line above is extrapolated over 4-Mar-12, but we expected the line to go to zero on that day so this solution did not meet our requirements.



Solution Attempt #2: Now we played with the EmtpyPoint and EmptyPointValue properties of the chart as follows:



However, it unfortunately did not have any effect on our chart because we simply did not have data points on 4-Mar-12, which is not the same as having a null or empty data point. Thus, this solution attempt did not help!


Solution Attempt #3 (CRM Only): Since this report is inside CRM, we tried to register a plugin on RetrieveMultiple which would generate some empty records for ech missing day in the date interval. However, we soon learned that CRM reports execute SQL directly and bypass the CRM pipeline which means that no plugins are ever triggered when retrieving CRM data for reports! Even though we were using FetchXML as the data source for the reports, the plugins were not triggered on either the RetrieveMultiple or the Execute message.



Solution Attempt #4: At this point it was clear that we needed a datapoint for each possible day, and if we want to line to go to zero we would need an empty/null data point for those days for which there are no records. We were using FetchXML data source for this report (CRM) which was creating some restrictions because we could not manipulate the data retrieved. So we had to change the data source to SQL to be able to build a smarter and more complex query which could “generate” the empty data points for those days for which there were no records. After applying this solution, we finally got the expected result:

Solution details:

So how do we generate empty data-points when there is no record for that particular day? We do this in 2 steps:

1. Generate a “DateRanges” table that contains a record for each day in the interval that the graph wants to display:

-- Select the Begin Interval based on Data
DECLARE @DateFrom DATETIME
SET @DateFrom = (select top(1) ava_date from ('+@CRM_FilteredIncident+') AS ED order by ava_date asc)

-- Select the End Interval based on Data
DECLARE @DateTo DATETIME
SET @DateTo = (select top(1) ava_date from ('+@CRM_FilteredIncident+') AS ED order by ava_date desc)

-- Generate a table with one entry per day
;WITH DateRanges AS
(
SELECT @DateFrom AS 'DateValue'
UNION ALL
SELECT DATEADD(DAY, 1, DateValue)
FROM DateRanges
WHERE DateValue < @DateTo
)

2. Do an outer join with your data, so then for each possible day, there is going to be at least one record. These commands should be the query in your report DataSet.

SELECT
ED.ava_date,
ED.ava_datetype,
ED.ava_datetypename,
DateRanges.DateValue
FROM
DateRanges
left outer join ('+@CRM_FilteredIncident+') AS ED on ED.ava_date = DateRanges.DateValue

It is important when you define your chart’s YValue that you use Count on a field that is not the date field generated (since the count will never be zero). In the Category Groups you would need to select the generated dates so each day will have an entry in the X axis:





Also note that we are using parameterized query that makes use of @CRM_FilteredIncident which is used for supporting pre-filtering data in CRM reports. Otherwise you would just have your table name instead of ('+@CRM_FilteredIncident+').

CRM 2011: Share specific privileges via Workflow

Some business processes require that you share a record with another CRM user automatically. However, there is no “Share” step in the workflow designer. This post introduces a utility that can be used for that purpose.

If you are facing a scenario for which you need to automatically share privileges to a specific record in CRM given a specific trigger then this tool can be quite useful. For example, let’s say you want to share “Read” access to your opportunities with your manager whenever they are marked as “Won”, but you don’t want to share Write or Delete privileges.

I have made an enhancement to the CRM 2011 Workflow Utilities project to be able to address that scenario. When you define a workflow, you can simply insert the “Share” step that comes with the utility and configure who you want to share the record with, and which record you want to share (can by dynamic or static values):




And additionally, now you can also select which specific privileges you want to share:




You can read the entire documentation in Codeplex.

How it works? You need to download the managed solution and install it in CRM, after you do this, you will notice that there is a “Share” step available in the workflow designer in CRM. The rest is history, have fun!

CRM 2011: Deleting Attributes, Entities, Relationships or OptionSets from Managed Solutions

Once you install a managed solution you are not allowed to delete any of its entities, attributes, relationships, reports and OptionSets. This post provides a simple and supported workaround if you absolutely need to delete one of these components from a deployed managed solution.

Working with managed solutions in production environments bring multiple advantages (see previous post on managed vs. unmanaged solutions), however, one of the disadvantages is that once you deploy a managed solution, you are no longer able to delete some of its components. A typical example is that you deploy a solution with a custom entity and you figure out that you must change the data type of an attribute. CRM in general will not allow you to change the data type of an attribute, you will need to delete the attribute and re-create it with the new type (even if you use unmanaged solutions); however if your managed solution is already deployed you cannot delete the attribute, so what do you do? I will offer 3 different alternatives I have used in the past:


1. Hide instead of delete. The easiest solution is to simply create the new attribute with the correct data type and completely hide the old attribute from all views, forms, reports, etc. In many cases, deleting an attribute in production is unacceptable because of data loss so this alternative is the safest and most conservative as you get to keep the old data. The disadvantage is that you will have useless columns in your database and the old attribute will continue to appear in Advanced Find.


2. Use a holding solution to delete any component from your deployed managed solution. This is my favourite approach as it has the advantage of completely deleting from the system the unwanted components without losing any data. This is how it works: Assume you have a solution XXX which contains a custom entity and one of the attributes is of type “Single Line of Text” but you want to change it to “Multiple Lines of Text”. In your target environment you should have solution XXX as managed and in your development environment you should have solution XXX as unmanaged.

1. In your development environment create a new solution “XXX_holding”. The publisher must be the same as the publisher of your XXX solution.
2. Add all the components of the XXX solution to your XXX_holding solution*.
3. Export XXX_holding as managed.
4. Import XXX_holding to your target environment.
5. Delete XXX solution from your target environment. No data is lost because all the customizations remain in the holding solution.
6. In your development environment delete the attribute (you will need to adjust views, forms, etc.).
7. Export XXX as managed.
8. Import XXX to your target environment
9. Delete XXX_holding from your target environment. The attribute will now be deleted from all environments. There is no data loss except for the data that was saved in the old attribute which was deleted.
10. Now that the attribute is deleted, if you want the attribute back but with a different data type you just need to re-create it in dev and promote the solution to your target environment.

* Note: You can avoid steps 1-3 if you simply open your XXX solution zip file and in the solution.xml file you update the solution unique name to “XXX_holding”. This way you guarantee that you have all the components of XXX also in XXX_holding.


3. Uninstall and re-install solution. If you absolutely need to wipe out the old attribute from the system, you could also uninstall the entire managed solution, make the appropriate changes in your development environment and then re-deploy a clean and correct version of your managed solution. The big disadvantage here is that you will lose your data if it relies on the customizations of your solution, which is usually unacceptable unless you have no data to retain or the data is not important.


4. Use unmanaged solutions instead of managed solutions. While it is true that with unmanaged solutions it is simpler to manually delete any component, it is also true that deploying unmanaged solutions to a production environment has multiple disadvantages and is not a good practice in my perspective. It might also be too late for you to switch to unmanaged solutions. You can read more on best practices around managed vs. unmanaged solutions here. There’s also an interesting white paper from Microsoft for building ISV solutions.


Some posts in the MSDN forums additionally suggest that Microsoft is aware of this limitation and might consider a better solution in the future. For the time being I have found the holding solution approach to work well, please let me know if you have other experiences or suggestions!