Exclude Null Values from a Chart

[jenschmidt]

Once again I come begging the expertise of the Tek-Tips community for help!!! I sincerely appreciate all the help I've received here!

Anyway, down to business! I have a very simple chart that is evaluating on the change of a formula and printing in the group header. In the formula, if the field being evaluated is null, it returns a string "No Diagnosis Recorded" otherwise I get the diagnosis description. Can I set the chart to not record the "No Diagnosis Recorded" value (or null value if I use the field instead)?

There are so many null / No Diagnosis Recorded values that it's skewing the chart - but I can't exclude these records in the selection criteria because of other data on that same transaction that is needed.

Any ideas would be appreciated!
Thanks!

jennifer.giemza@uwmf.wisc.edu

 

[Turkbear]

Hi,
Try creating a formula that only produces output when the
field in question is not null - use the formula in your chart instead of the actual value of the field.

 

[jenschmidt]

You were right - THank you much!!!!

jennifer.giemza@uwmf.wisc.edu

 

[jenschmidt]

I lied - it didn't actually work. I still get a large bar of null values and then the rest of the bars look really tiny. Any other ideas?

jennifer.giemza@uwmf.wisc.edu

 

[Turkbear]

Hi,
Can you post the formula you used?

If it does not produce any output, then no bar height should be generated..

I may be missing something, so the formula may help me understand it better..

 

[jenschmidt]

Here's the formula I'm using in the chart:
if not isnull({TABLE1.DX_ID}) then {TABLE2.DX_NAME}

It seems like even though it's not generating data because it's a null value, it's still counting the nulls - there's no label at the bottom of the chart for this bar height.

Thanks for the help!


jennifer.giemza@uwmf.wisc.edu

 

[lbass]

In the chart expert, select the "on change of" field and then choose "Order"->specified order. Add the non-null fields using the dropdown list, and then choose the "Others" tab and select "discard all others." This will give you all but the null results for your x-axis.

-LB

 

[jenschmidt]

That's what I was thinking but there are over 15,000 diagnosis codes - I'm only displaying the top 10 used in a given time period, but I wouldn't be able to tell which ones to choose. Two questions on that note: is there a way to import a list into the "Order" tab, is there a limit on the number of values you can enter there?

Thanks again!

jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

Guys,
why not to show the Chart from/as subreport for which we suppress nulls in SQL (i.e.selection expert?)

 

[lbass]

If you are using topN, in the chart expert, when you set up the topN = 10, uncheck "Include all others".

-LB

 

[jenschmidt]

Thank you guys so much for your help!

I can't uncheck the "Include all others" for two reasons: 1, they want to see everything else that's not null lumped into an "others" bucket, and 2, because the null value is the largest of all the buckets, it doesn't go away when that box is unchecked.

I'm still working on the subreport idea.

jennifer.giemza@uwmf.wisc.edu

 

[lbass]

Sorry, I wasn't thinking. The subreport idea might be the best approach.

-LB