Hide Null Values on SSRS Report

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
Mike DenovellisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Feridun KadirPrincipal ConsultantCommented:
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
Mike DenovellisAuthor Commented:
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
Mike DenovellisAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Feridun KadirPrincipal ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike DenovellisAuthor Commented:
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
Feridun KadirPrincipal ConsultantCommented:
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
Mike DenovellisAuthor Commented:
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
Mike DenovellisAuthor Commented:
Tell a lie, I spotted an error, have corrected it....and all looks good! Thanks for your help.
0
Feridun KadirPrincipal ConsultantCommented:
Great news.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.