Solved

Hide Null Values on SSRS Report

Posted on 2014-09-11
9
412 Views
Last Modified: 2014-09-11
Hi, apologies in advance as I'm very new to SSRS reports. I have created one yesterday which pretty much does what I need it to do, except I need to hide null values, and can't quite figure out how/where to be doing this.

We're using Microsoft Dynamics CRM 2013, and every case that is created has a "responsible contact" field which is the user that raised the case with us. What I want the report to show is the number of cases logged, per responsible contact, per account. The problem I have got is that we didn't start using the "responsible contact" field until just recently, therefore all the historical cases do not have any data in this field. When a report is now generated, the first column is just blank and really skews the report itself. (see attached screenshots)

Now I know that I need to add some sort of NOT NULL, or ISNOTHING into the report, but I'm not too sure where to be doing this? Any help you can offer would be much appreciated, and apologies in advance if I ask any daft questions.
Chart.png
MissingContact.png
0
Comment
Question by:Mike Denovellis
  • 5
  • 4
9 Comments
 
LVL 29

Expert Comment

by:feridun
ID: 40316506
Do you want to exclude the cases where responsible contact is null completely from the report?  

I would do this in the SQL for the datasource. If you are using the report wizard, you can set the default filter to exclude cases where responsible contact is null.
0
 

Author Comment

by:Mike Denovellis
ID: 40316515
Hi,

Yes I would like to completely exclude cases from the report where the responsible contact is null. The current data source settings are in the attached file.
 
I have tried changing the default filter within the report wizard to:
'Responsible Contact' Contains Data

However, this doesn't exclude the cases. I presume what I need to do is hide cases where the responsible contact = ""  ?
DataSource.png
0
 

Author Comment

by:Mike Denovellis
ID: 40316519
I should probably add the Dataset details too - do I put the exclusion in here somewhere?

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="incident" >
      <attribute name="title" alias="aa_title"/>
      <attribute name="ticketnumber" alias="aa_ticketnumber"/>
      <attribute name="createdon" alias="aa_createdon"/>
      <attribute name="responsiblecontactid" alias="aa_responsiblecontactid"/>
      <attribute name="incidentid" alias="aa_incidentid"/>
      <attribute name="sysit_resolutiontitle" alias="aa_sysit_resolutiontitle"/>
      <attribute name="sysit_resolutiondescription" alias="aa_sysit_resolutiondescription"/>
         <link-entity name="account" from="accountid" to="customerid" alias="ab" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount">
      <attribute name="name" alias="name"/>
    </link-entity>
  </entity>
</fetch>
0
 
LVL 29

Accepted Solution

by:
feridun earned 500 total points
ID: 40316534
Ah you must be using CRM Online given the use of FetchXML.
If you use Advanced Find to construct the query by adding a line Responsible Contact Does Not Contain Data you can then use the Download FetchXML button to see the FetchXML query. I think you need to add a filter to the dataset (I've copied the lines from your query to show where the filter should be added.

Add the filter after this line:
    <attribute name="sysit_resolutiondescription" alias="aa_sysit_resolutiondescription"/>


   
 <filter type="and">
      <condition attribute="responsiblecontactid" operator="null" />
    </filter>

Open in new window


and before this one

     <link-entity name="account" from="accountid" to="customerid" alias="ab" enableprefiltering="1"

In the condition attribute you might need to use aa_responsiblecontactid because of the alias in your query. Try both to see which works.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Mike Denovellis
ID: 40316562
Thanks, this has almost worked perfectly, except the first column still has a single entry (see attached file)..any ideas?

I changed the filter to be:

<filter type="and">
      <condition attribute="aa_responsiblecontactid" operator="not-null" />
      <condition attribute="aa_responsiblecontactid" operator="ne" value="" />
    </filter>

But this didn't appear to make any difference.
Chart1.png
0
 
LVL 29

Expert Comment

by:feridun
ID: 40316575
I would look at your cases to see if you have a case linked to a contact with no name and perhaps amend the case.

I would remove the second line of your filter because it is looking for contact ids equal to blank which will never be true. If responsible contact in the case is linked to a contact then responsible contactid will have a value even if the contact name is blank because contactid is the GUID of the linked record. To search for contacts where the name is blank will make the FetchXML query more complicated. Given there is only one record I would recommend you find the "offending" case and either remove the contact from the Responsible Contact field or fix up the contact record.
0
 

Author Comment

by:Mike Denovellis
ID: 40316622
Hi, unfortunately it's not only one record. That was only 1 specific account I ran the report for. If I run the report for other accounts the same applies. We have no contacts on the system that have no name, which is what is confusing me.
0
 

Author Comment

by:Mike Denovellis
ID: 40316634
Tell a lie, I spotted an error, have corrected it....and all looks good! Thanks for your help.
0
 
LVL 29

Expert Comment

by:feridun
ID: 40316639
Great news.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now