Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

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
0
Mike Denovellis
Asked:
Mike Denovellis
  • 5
  • 4
1 Solution
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now