(none) in filters, what does it actually do?

#1

Hi,


I have some strange behaviour in my dashboard related to filtering. Using the standard collection member filters it is possible to select a value of "(none)" by selecting "All" and then deselecting "All". This value should in theory cause the results to be filtered in order to show only those records that have no value for the filtered field (to my mind anyway, please correct me if I'm wrong). As an example, "Department" is an optional field on one of our records. There are records that have Departments selected and there are records that don't. I would assume that selecting "(none)" for the Department field ought to show me only those records with no Department selected (I'd expect the where clause to look something like "where IdDepartment is null").


Unfortunately for me, that isn't what is happening, and instead the dashboard renders completely empty. Every single chart that is connected to the filter in question shows No Data (or would if we weren't hiding the No Data message, I've tested this). There are some charts that should never show the No Data message, because they always return something, even it's a bunch of zeroes. Even they don't show anything. I find this bit particularly odd.


I decided to do some testing and profile my database while I ran the dashboard, to see what SQL was actually being run. The SQL that is run returns rows. It is actually identical to the SQL that is run when the filter is set to All (which is still wrong, but in a different way). So it seems I should be getting data back from the cubes, but I'm not. It looks to me like something is coming along after the data is retrieved and essentially telling the dashboard "don't bother showing any of this." Why that would happen I have no idea.


My question is what does "(none)" in this context actually do? I would expect to be able to deal with it the way I deal with the "All" token, which is to test for it in the bridge parameter script and replace it with "All" as a string in the manual query (making my placeholder $phDepartment$='All', basically), but I can't figure out what to test for. When I test for All, I check that the input value is equal to PredefinedToken.All, but PredefinedToken.NoSelection, which I thought might be the answer, does nothing.


Below, I've pasted the script from one of our Bridge Parameters, and some sample SQL from one of our cubes that shows how we use the value within a query, in case this helps to explain. 


CollectionMemberValue memberValue = (CollectionMemberValue)$input$.Resolve();
var commaDelimitedString = string.Empty;
//Pass "All" string if user selected "All" checkbox or "All" token
if(memberValue.Token != null && memberValue.Token == PredefinedToken.All ) {
  commaDelimitedString = "All";
}
else {
  for (int i = 0; i < memberValue.Values.Count; i++) {
    if (commaDelimitedString.Length == 0) {
      //Need to strip off appended level unique name... see Example 1
      commaDelimitedString = memberValue.Values[i].UniqueName.Replace(".A","");
    }
    else {
      //concatenate all selected values to a comma-delimited string
      commaDelimitedString = string.Format("{0},{1}",commaDelimitedString, memberValue.Values[i].UniqueName.Replace(".A",""));
    }
  }
}
return new SingleStringValue($id$, commaDelimitedString);



AND ( $phDepartment$ = ‘All’

OR CONVERT (VARCHAR, deptlink.OrganisationalScopeOthersId) IN ( SELECT * FROM dbo.split ($phDepartment$, ‘,’) ))


In this example, the values from the filter are converted to a comma delimited string of Ids, which we then use in our where clause to filter the results. If $phDepartment$ is “All”, which it will be if the All token is selected, we essentially ignore this filter and allow all values through. For the “(none)” case, I would expect the value of $phDepartment$ to be null, or to have some way to make it null, so that the where clause doesn’t pick up anything where the Id is not null. There is a left join in the query that ensures that rows with no departments are returned, so filtering on null is not pointless here.


I’ve rambled a lot, but I’m hoping in that ramble I’ve explained what I’m seeing and what I’d like to be seeing. If anyone can help me figure this out, I’d appreciate it.

#2

I think you will need to do a null replacement to make it be none and then you can filter on none.

(looking for an example I have.....)

In the metric set you edit the Row measure and under Metric Set Default Valuse fill in the "Unkown Member Caption" make sure "Retrieve Unknown Members" is checked.


section 2.3

https://www.dundas.com/support/support-center/support-articles/data-visualizations/formatting-text


Edit:

Just so happens I am doing something very simular right now and after I set it up right it works. I did do the null replacement in the data cube, not sure if that make a sdifference.

#3

Hi Adam,



I’ll try to address both of the concerns you expressed:

  1. As per my understanding of your use case, you have a hierarchy “Departments” and this has some null values in it. Now, you want to filter out those “Departments” that have this NULL values using a “Collection Member” filter.
  • In order to explain you the issue here I tried creating a similar set up, that has a SQL table with some NULL values for the column name:

Image title

  • Dundas BI shows these NULL values as (Empty String) values in a data visualization. I have taken an example of Table Visualization below:

Image title


  • Now, when you add a collection member filter to this table it will show you the (Empty String), but on filtering using this member you will get a blank table since this value is Empty:

Image title

  • However, in our recent revision, Dundas BI 5.0.1 (release date not yet scheduled), when you use the same use case you will get results corresponding to your (Empty String):

Image title

  • Meantime, you can either use NULL Replacement or Calculated Element transform in your Dundas BI data cube to handle the NULL department values.
  • Regarding checking the (none) values in a bridge parameter by first selecting and then de-selecting the values in the filter, I tried creating a bridge parameter similar to one that you had mentioned and then tried the exact same script with a couple of additions/modifications and now I am able to return a string “NULL” when none is selected just like we return a string “ALL” when all is selected:

Image title
59kdxSNmRFOnJvXCXOUH_5.png756×130

  • Here is the script withmodifications/additions in Italic:


CollectionMemberValue memberValue = (CollectionMemberValue)$input$.Resolve(); 
var commaDelimitedString = string.Empty; //Pass "All" string if user selected "All" checkbox or "All" token 
if(memberValue.Token != null && memberValue.Token == PredefinedToken.All ) 
{ 
  commaDelimitedString = "All"; 
} else if(memberValue.Values.Count > 0) { 
  for (int i = 0; i < memberValue.Values.Count; i++) 
  { 
    if (commaDelimitedString.Length == 0) 
    { //Need to strip off appended level unique name... see Example 1 
      commaDelimitedString = memberValue.Values[i].UniqueName.Replace(".A",""); 
    } 
    else { //concatenate all selected values to a comma-delimited 
      string commaDelimitedString = string.Format("{0},{1}",commaDelimitedString, memberValue.Values[i].UniqueName.Replace(".A","")); 
    } 
  } 
}
else{
      commaDelimitedString = “NULL”; 
}
return new CollectionStringValue($id$, commaDelimitedString);


  • The reason why PredefinedToken.NoSelection is not working in his case is because when data retrieval service receives this token, all the queries are abandoned and the user receives an empty cellset.



I hope this answers your questions.

#4

Thanks, that looks like it'll solve my problem. I'll give it a try now.

#5

Thanks. Makes sense. Between your answer and the previous one, I think I can get something working now.

#6

Hi,


I tried editing my script to match yours (I hope you can see the changes in the screenshot), but I still get No Selection when I select (none) in the test filter:


Image title
BsuyVaO6TLm21RZXUUY5_NoSelection.PNG695×552

I did a bit of further debugging by putting return statements like "return new SingleStringValue($id$, "in null branch");" in various places, so I could see which path was taken. I never got the results I expected. In the end I even put a return statement right on the first line, and even that didn't get run. I still get No Selection when I select (none).


Finally I decided to try making "return "test";" the only line in the script, and even that didn't work. I still get No Selection. It seems that, for me at least, whenever (none) is selected, the result is always "No Selection", regardless of the code in the bridge parameter script.


Is this maybe something that's changed in a recent version? We're running 4.0.2.1000 at the moment. I don't think we're particularly out of date, but I thought it was worth checking.

#7

Have you put in a support ticket? I not use bridge parameters yet but for the simple null replacement I know that will give you someting in the filter drop down to select on.

Image title

I suggest asking support at this point for the issues with the bridge parameter.

#8

did you get it working?

if so what was the fix?

#9

Ah, ok, that's interesting. Sorry, I didn't see this until now for some reason. That looks potentially helpful, I'll give it a try.

#10

No fix as yet, still trying things. About to try your suggestion below. Not sure if it makes sense with the way our cubes are working, but anything is worth a go. If that doesn't get something working I'll email support and see if I can get someone to look at it with me.

#11

Having taken a quick look, I've realised that the issue is at the wrong end. I need something that will turn the parameter coming in into something I can work with, rather than something that will replace nulls in my data. I'll write to support and see what I can sort out. There must be a way to make it work. It doesn't seem like a particularly unreasonable thing to want to do.

#12

You can always just use your favorite sql null replacement in the sql to fill nulls with something that way Dundas never sees any nulls.

#13

Yes, that part is fine, it's the other end where the problem is. When I select (none) in the filter, I get the No Selection token, which I can't compare with my data, regardless of whether the field is NULL or 'empty' or '' or whatever. If I could replace No Selection with NULL or really anything else, I could get it to work, I think. It's just baffling me at the moment. I've emailed support. If I get a good solution I'll post it here, in case anyone else ends up in the same position.

#14

Hi Adam,


I have seen your email and will be helping you with it. Meantime, I would like to make a note that the bridge parameter script that I gave in my reply only works in v5 and above.
I guess the problem, in this case, is related to the version of Dundas BI.

Because, there was a behaviour change how “All” and “None” parameter value is handled in Dundas BI v5 i.e. the bridge parameter script gets executed even if the linked parameter has “All” or “None” value set. However, in Dundas BI version 4.x.x.x the bridge parameter script is completely skipped in such cases. So irrespective of the script we write for the bridge parameter it will be skipped in versions below Dundas BI version 5 if it encounters a condition where “ALL” or “None” is selected.

**Note - here "ALL" and "NONE" is in reference to selecting and deselecting the member values and not in context to the tokens.

#15

Ok, thank you, that explains what I'm seeing. At the moment it appears that I can get the behaviour I want by selecting All and then inverting the filter, but I think that option is less intuitive than selecting (none), and our customers are going to expect to use both interchangeably, I think.


Thanks, I'll wait to hear from you regarding the support request.