How do you keep from charting NULL values?

[HamotIntern]

This is a complicated thing to explain, but I’m going to give it a try. I have two columns of values that I would like to chart. One has a value in each record where the other one has a value only for one type of record (BP). This chart uses filters to show the values for each type of record (BP, Heart Rate, O2 Levels….). The catch is when I preview the chart; the legend shows two values for each record type. The only one that actually has a second value is BP. All the others are NULL/Blank. Is there a way to show the second value ONLY for BP and ONLY show one value for all the others?

 

[ookete]

I think I have an idea, but could you please give us a snippet of a table sample? Like:

ColA    ColB
BP      60
Heart   55
.
.
.

 

[HamotIntern]

ClassLL expn 1 expn 2

Heart Rate 76.00
Other BP 133.00 69.00
Other MAP 90.00
Temperature F Manual 97.90
Resp. Rate 83.10
Other MAP 16.00
Other BP 110.00 58.00
Heart Rate 69.00
Heart Rate 65.00
Other BP 135.00 71.00
Other MAP 93.00

 

[ookete]

Hmm, no success so far. I tried doing:

=IIF(Parameters!@class.Value = "BP", Fields!expn2.Value, System.DBNull.Value)

But this gave the same empty legend series as you had before. I am not sure of a way to dynamically hide a data series. I think there must be a way, though.

 

[Catadmin]

What about using the NOTHING value in place of the System.DBNULL.Value?

I'm pretty sure I read somewhere that the System.DBNull.Value doesn't actually work in the current iteration of RS.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

I use DBNull all the time and it seems to work fine in many cases, like when you want to dynamically assign which columns to group by.

Regardless, I tested my sample using Nothing instead of System.DBNull.Value and I had the same problem. The NULL series still shows up in the legend. It would be cool if you had a way to actually remove or add a series through code dynamically. Perhaps there is a way to do this but I doubt it, as that would probably alter the .rdl file.

 

[Catadmin]

What about writing Functions in the CODE part of the report properties that dynamically create what legends pull up in the chart?

I'm just throwing out ideas at random. Please don't hit me unless I actual suggest something useful. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

Don't worry, I won't hit you (yet). Actually, this isn't even my post but it is an intriguing question, and something I will probably need to figure out myself someday.

I have created a few functions in Code before with success. They are great, but as far as I can tell they are only useful in returning a value to put in an existing property. What I mean is that all a Code function would do is spit something into the Value property of our Data series... and it doesn't matter what that value is (NULL, Nothing, "Expn 2", etc), the series will still show up on the Legend even if it's completely empty.

If a series has been "added" under the Values list of the report properties Data tab, it will show up period... at least from what I can tell so far. We need a way to "un-add" the series from the Values list. The only way I could think of doing this is by getting into the behind-the-scenes code... perhaps changing the .rdl dynamically if that's possible. Since it is essentially an XML document perhaps it is possible after all.

 

[HamotIntern]

No success here. I’m new to Reporting Services so I’m kind of relying on you guys for the answer, though I will continue to work on it.

 

[xlbo]

Not really an answer - more of a workaround.

Could you have 2 seperate charts in the same location - 1 with one series on it and one with two. Then use an IIF() formula in conjunction with the visibility property to dynamically decide which chart is visible ???

Guess it depends on how your report is structured and whether you could test something to decide what to show.....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[HamotIntern]

The main media that I am creating this report for is print. If I could make one of the charts’ backgrounds transparent, then this could be an acceptable solution.

 

[Catadmin]

Hey, xlbo, that is a really good idea. The only problem I see with it is a white space issue. And if HamotIntern can position them over each other, that should kill any white space issues.

Just make sure that one isn't bigger than the other.





Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

If you are using your own interface with a reportviewer/web browser control then you could create these as two separate reports. The single control would then call by url the different reports depending on which you would need and it would be seemless to the user. I think xlbo's idea is better, but if you get some unexpected results with overlayed graphs then perhaps double reports will work for you.

 

[HamotIntern]

Is it possible to make the background of the top chart transparent? Right now, the top chart completely covers the one beneath it.

 

[Catadmin]

Look at the properties pane for the chart. There should be a "Background color" choice. Is it not on Transparent?

If not, try going into Background Color, Expression, and putting NOTHING on it.

Since Transparent really isn't a color, RS won't let you use it.

Alternately, use an IIF() function which checks whatever param or field you need to see if the chart is being used.

IE: =IIF(Param = "A", White, Nothing) as the Background Color expression.

Fill in the Param = "A" part with your own code.




Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Catadmin]

Found an interesting tidbit in HitchHiker's Guide to SQL Reporting Services that I thought I'd pass along. Page 391 for those who have the book.

We think it would be wise to touch on this nothing issue; that is, what should the Chart control do if there is no data? You can set the NODATA property on the Chart control to display a string if your DataSet returns no rows. You'll also discover that if a column returns NULL values, the Chart control either skips over the value or...substitutes a value on its own. One way to control this behavior is to add NOT IS NULL to the criteria on those columns where we suspect data won't be returned...However, the Chart control always replaces label values that are NULL or contain empty strings with the auto-generated labels.

Customization of empty point handling is something that the Dundas Chart upgrade will eventually provide....

I did snip the quote here and there where you see '...'. There's more about making NULL = 0 or keeping NULL to show that data is missing, but the last comment is the important part, IMHO.

I don't know if the info in the first paragraph helps or not, but I thought I'd include it just in case.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

I think this phrase back up our decision that you can't hide a NULL series: "However, the Chart control always replaces label values that are NULL or contain empty strings with the auto-generated labels."

 

[Catadmin]

<noddles> Yep. But I also think this statement indicates the possibility that it could be changed in a future release.

One hopes MS gives us that kind of flexibility.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

HamotIntern,

Don't worry about transparency. Just set the chart's Hidden property under Visibility in the property pane.

=IIF(Parameters!class.Value = "BP",False,True)

Then reverse the True/False for the second overlayed chart.

 

[HamotIntern]

It’s not that I am ungrateful for suggestions on how to make overlapping charts work if they were going to stay electronic, but I need this for print as in output from a printer. Therefore, both parts of the chart will need to be visible at the same time. The page that this chart is part of is densely populated, so there is only room for one chart to show this data, rather than two side-by-side or placed vertically. I am not trying to discourage help, far from it. I just wanted to clarify the requirements for this chart.