CFChart Displaying False Data

[LaoD]

I posted a question previously about grouping CFChart output that was answered by MikeRoo. I thought it was solved and marked it as such. However, this morning I discovered that the Chart contained invalid data -- data that is not in the database. I did not see a way to change the previous thread to "Unanswered" so I'm posting this new thread -- for which I apologize ahead of time if I'm not supposed to do it this way. But this is more of a problem than the grouping was because it is showing false results.

I want to display a Bar graph with TQs along the X axis (bottom) and Percentages along the Y axis (side). But I need a percentage of each InsideTQ and OutsideTQ for each Originator.

What is happening is that I'm getting InsideTQ and OutsideTQ values for Originators that do not have that TQ. For example:

Originator 00021
TQ 3
InsideTQ 40 percent
OutsideTQ 60 percent
TQ 6
InsideTQ 44 percent
OutsideTQ 56 percent

But what I'm seeing on the graph is that Originator 00021 shows a TQ 5 with an InsideTQ and OutsideTQ value, but there isn't a TQ 5 for this Originator. This particular Originator should only have 5 values on the chart, yet there are 13 being displayed for it. I've double checked the tables, I've created temp tables from my queries to step through each query and query of query. My queries are correct and they display the data correctly on the report table. But the CFChart is showing false data. The good news is that all of the data that is really is in the database is shown correctly on the chart...just seems to make up other data.

Attached are my queries, chart, and table. I have a jpg of the chart if anyone thinks that might help...

I REALLY appreciate your help.

[MikerRoo]

I don't see anything obviously wrong.

Please post a link to the graph output and a text display (not graphic snapshot) of the InOutAccuracyQuery table.

That way, we can play with the data set and see what's up.

Also, is the code you've attached here inside a <cfoutput> tag? This can sometimes cause unexpected results due to cfoutput's grouping functionality.

Finally, if you PM me, also post a reply here so that I will know to check my PM's. (PM's do not send emails and are not visible via the newsgroup interface.)

[LaoD]

MikeRoo,

The report resides within a closed network with resticted access due to the nature of the data we process...so I can't link you directly to the site. I develop and maintain a more public site but the folks that post my updates seem to have started their weekend early. So...I'm posting my code here, it will be long and most likely pretty messy.

Four main sections make up the report: Main, Report, Queries, Title. I'm not posting all of the code becuase it would be a mess to read. Look for the following in the attached code.

<CFSWITCH> is where the queries are located that I use for displaying the report based on previously selected filters.
<TABLE> is where the main output for the report is located.
<CFELSEIF Action EQ "ShowReport"> pulls the pieces of the report together to display it to the user.

Last but not least the output of the table: See next message for the table contents.

If you have a place I can send email attachments, I will gladly send the files I have so you can read them better.

Again --- THANKS so much for your help! I'm trying to get that charting software approved, I hope that will solve some of these problems I keep running into.


Fair Warning: Due to the filters the code is a bit cumbersome. But hopefully this helps. I'm not sure how to post the image of the chart...

[LaoD]

MikeRoo,

The table output at the bottom of the previous message isn't going to help because it is part of a roll up of TQ counts by Originator and TQ. Here is a better selection of the data causing the problem.

In this data Originator 34 does not have any TQ 6 values, but in the chart that Originator has values for TQ 6.

[MikerRoo]

Ok, that list of data should be enough to see if I can duplicate the problem.

I'll play around with it this weekend in between chores.

[LaoD]

MikeRoo,

Something I noticed trying to figure this out...when I select only 1 Originator from my filter list, the problem does not occur. If I select 2 or more, it occurs. Additionally, some of the "false" data that the chart is displaying is not as made up as I thought. It appears to use some of the roll up data that I use in the textual table of the report but that I do not want in the chart. For instance, if Originator 21 does not have a certain TQ value the chart is displaying a TQ value but when I look into the values sometimes it is the roll up for a different Originator or it is the roll up of a TQ. I use the roll up counts and percentages for the table below the chart. It seems to be pulling data from the previous queries even though I'm filtering and narrowing down the data I want by using the Q of Q for the chart data. But I can't figure out why??

[MikerRoo]

I'm sorry LaoD, but the table of data listed has columns that do not correlate with any of the queries used and vice versa.

Given that, and the fact that the posted code is long and complex, and has apparent edits; I could spend a lot of my free time trying to make sense of it without solving your problem -- because of some missing or edited piece.

Can you post a complete, self contained, set of data and code that illustrates the problem?

Finally, there are nested and unnecessary <cfoutputs>. This often leads to unexpected behavior due to some features of cfoutput. For now, go through the code and remove the inner cfoutputs.

[MikerRoo]

quote:

---

Originally posted by: MikerRoo ...
Can you post a complete, self contained, set of data and code that illustrates the problem?

---

Ideally something we could just drop onto a server and run.
PS. the exercise of generating such an example often helps you uncover the problem. ;-)

Good luck.

[LaoD]

MikeRoo,

Here is the table (TrackCorrelationAccuracy).

Debra

[LaoD]

MikerRoo,

All of the reports are made up of 4 sections: Here is the main section of the report. This section contains filters at the top for the user to specify which data he/she wants to view. It also contains the tags to pull all of the pieces together to display one "seamless" interface.

- Debra

[LaoD]

MikerRoo,

Here are the queries for the filters and the report.

- Debra

[LaoD]

MikerRoo,

Here is the report.

- Debra

[LaoD]

MikerRoo,

Not sure you need it but here is the title.

- Debra

[MikerRoo]

Lordy, LaoD**!

That's a lot of code and data to sort through. Since I am doing it in my spare time, may not be able to get back to you before next weekend.

Regards,
-- MikerRoo

** Forgive the pun, just wanted to say that.

[LaoD]

MikerRoo,

Yeah it's a LOT of code...that is why I was trying to give it to you in pieces, didn't want to scare you off ;-)

Something I tried yesterday: I pulled the CFChart out and opened it in a new window with just the attached code and it still displays incorrect data. My best guess on where the data is coming from -- it is some how ignoring my more specific filters and returning data for rows where the Originator or TQ is NULL though I specify not to include those. I use the NULL Originator and TQ fields for my roll up counts but I don't want them in the chart. However, when I've found strange values in the chart, they all point back to a row in the table with a NULL Originator or TQ. Now on my table, those values are being filtered correctly. So, I'm still blaming the chart and not my queries...just wish I knew why!?

Again, any time you have when you can help, it is appreciated. I'm going to lock down the filters so that the users can only select 1 Originator and/or 1 Object. This way the report in production will display correctly until we can solve the problem in the development report.

Side Note: I downloaded a trial version of Chart Director. I just started looking at it yesterday, spent a few hours trying to figure out how I would get it to work with my CF site. I'm such a CF geek that I forget about those other languages...we have .NET, we have VB, and other assorted development tools so it shouldn't be too difficult to figure out how to fit it into the current reporting tool. I'll let you know how it goes...also the book you suggested "Visual Display of Quantative Information" does that cover Chart Director? Trying to get our Project Lead to purchase Chart Director and a few books so I don't bang my head against the wall on these charts...

Thanks again and hope to hear that you had an epiphany...I'll let you know if I have any!

[LaoD]

MikerRoo,

I don't think you need to wade through all of my nasty code. I've been troubleshooting this problem and though I don't have a solution yet, here is what I've found:

When I commented out the CFChart and stuck a CFDump in its place the output was correct.
When I uncommented the CFChart and removed the CFDump I get the wrong output, specifically Originator 00021 TQ 7 and TQ 9.
When I moved the CFOUTPUT to outside the CFChart I get the correct values in the chart but I get multiple copies of the chart depending on the number of Originators I select.

All of this code is contained in its own file and opens its own window so the Outputs within the report don't affect it. So...I really think the problem in the in chart and not in my nasty code.

OK -- I won't post anything else until I hear back from you or until I get a breakthrough!!!!

[MikerRoo]

quote:

---

Originally posted by: LaoD
... ...
also the book you suggested "Visual Display of Quantative Information" does that cover Chart Director? Trying to get our Project Lead to purchase Chart Director and a few books so I don't bang my head against the wall on these charts...

---

That book does not cover any software, it is a high-level guide to information presentation.
We found the Chartdirector help files to be good and their support staff was very responsive.

Anyway, you do not need to buy the SW until you get the trial version working to your satisfaction.

-- MikeR

[LaoD]

Hi MikerRoo,

Happy Monday! Hope you had a nice weekend.

Just wondering if you have had a chance to look over my chart code? I'm still not able to select multiple Originators and have the chart work correctly. I'm starting to wonder if there isn't a bug in the CFChart tag because this really doesn't make any sense why it works one way but not the other.

I'm attempting to use ChartDirector JSP/Java with CF MX7 to solve this chart issue in another way but I'm having a problem getting that page to display properly. Which version of ChartDirector are you using with your CF?

[MikerRoo]

Hi LaoD,

I have not forgotten about this problem but I did have to work this weekend. It may be be next weekend before I can get back on it.

We use version 4.0 of ChartDirector. We wrap it in C++ CFX's since we do a lot of number crunching in conjunction with most of our plots and C++ is way better at that than Java or CF.

[LaoD]

MikerRoo,

Thanks for the reply. I'm sorry you had to work over the weekend...that's no fun!

But you are using ChartDirector with CF, right? I'm trying to figure out CD and how to get it to tie into CF. I don't know C++. I don't know Java either. I'm a CF and SQL coder and unfortunately I haven't made time to learn a lot of other languages. My entire application is in CFML/SQL . Too many things to learn, not enough time to learn them.

Any way, I appreciate your help and your time. If you discover any thing please let me know and I'll do the same.

LaoD

[MikerRoo]

Unfortunately, to use ChartDirector -- or any chart engine with real power -- you have to know enough Java or C++ to tie it to CF. If you know exactly what you want, you may be able to hire the integration task out on rentacoder.com or guru.com.

[MikerRoo]

Haven't finished sorting this out. Still working on it in my spare time.

[LaoD]

MikerRoo

Any chance you ever got any "spare time" to work on the chart? I still can't find anything wrong in the code but it will not work correctly with multiple Originators/Objects. I'm stumbing through ChartDirector but haven't had a break through there either. Anyway, thought I'd touch base with you since it had been 4 weeks or so since I bugged you last. Thanks!

LaoD

[MikerRoo]

Sorry, LaoD, I wasn't able to confirm the problem and the posted code is devilishly complex (and maybe incomplete?).

It uses state flags and many blocks of near identical code -- which makes analysis, modification, and test much more tedious.

The code should be normalized but I estimate it might take me a day to normalize what I see here.
Normally, I limit forum problems to no more than an hour unless it's a particularly interesting or fun thread.

I might make one more crack at this issue but I ask that you email me all of the files that feed into these charts -- so that I can be sure that all possible states are accounted for.

That is, the search/report form files, the form action pages, the report queries, and the report generation.

I'll PM you with a temporary email address.

Regards,
-- MikeR

[MikerRoo]

After analyzing the files and data, sent by LaoD, established that the problem is actully a pretty serious bug in CFChart.
See http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=7&threadid=1193856&ente...