Report Builder – Replace Empty Table Values with a zero

If you are creating a highly customised report you may come across a situation where your dataset may not return any results.

If this happens your table with render with blank.  I have come across a solution to enable these empty / null values to be replaced with zeros if the dataset query returns no results

Consider the following example report

The Total column uses the following query to sum the number of records from the dataset results where the prioritycodename = 2

 

=Sum(IIF(Fields!prioritycodename.Value = "2", 1, 0))

If the dataset returns no results this field will be blank.  On an SLA report this is not desirable.  I would prefer to see a 0 instead.  To achieve this I used the following query

 

=IIF(ISNOTHING(sum(Fields!prioritycodename.Value)),0,Sum(IIF(Fields!prioritycodename.Value = "2", 1, 0)))

 

In English the above query reads as,

If sum of prioritycodename equals zero, display a zero in the table, otherwise count the instances of prioritycodename where prioritycode = 2

I think you will agree the report looks a lot better with zeros than blank rows / lines

Dynamics CRM ActivityPointer Master / Detail Report

In the following post I am going to explain how I created a Master / Detail Report Using Report Builder 3.0 and Dynamics CRM 2011.  This report is also known as a Header and Detail Report.  I am going to use the FilteredActivityPointer (ActivityPointer) table to display all activities for each Incident (Case).

1)      Start by creating a blank report in Report Builder.  Create your Data Source and then your Data Set

I have used the SQL following for my dataset

SELECT top 100

FilteredActivityPointer.activitytypecodename,
FilteredActivityPointer.subject,
FilteredActivityPointer.description,
FilteredActivityPointer.statuscodename,
FilteredActivityPointer.createdbyname,
FilteredIncident.ticketnumber,
FilteredIncident.title,
FilteredIncident.customeridname,
FilteredIncident.responsiblecontactidname,
FilteredIncident.createdon
FROM         FilteredActivityPointer INNER JOIN
                      FilteredIncident ON FilteredActivityPointer.regardingobjectid = FilteredIncident.incidentid
where activitytypecodename != 'Case Resolution'

2)      Add a List to the report

3)      In the Tablix Properties page.. select the dataset to be used

4)      At the bottom of the Report Builder Applciation window, Right Click Details and Select Group Properties…

5)      On the Group Properties window, choose to Add a Group Expression on the general tab.

I have chosen to add ticketnumber

6)      You are now free to add your Text Fields and your corresponding Tablix.

The Name, Date, Case Number and Case Title are Text Boxes.

The table was created using a Matrix.

The final result should be similar to the following.  Here you can see the Activities for each Case where the Case details are the Master and the Activities are the Slave / Detail information.

 If anybody wants to know how to do the Activity Icons – Leave a comment or contact me on twitter and I will be more than happy to oblige.

Tony