Solved

Hide Null Values on SSRS Report

Posted on 2014-09-11
9
442 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 29

Expert Comment

by:Feridun Kadir
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 29

Accepted Solution

by:
Feridun Kadir 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
 

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 Kadir
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 Kadir
ID: 40316639
Great news.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part II
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 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