Solved

Hide Null Values on SSRS Report

Posted on 2014-09-11
9
419 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 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

863 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

20 Experts available now in Live!

Get 1:1 Help Now