If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Data Bars

Started by jguevin, 24 Mar 2008 10:31:04 AM

Previous topic - Next topic

jguevin

Anyone out there have code for creating data bars within a list report?  I know you can add spark lines and I have tried to do similar steps but changed the chart type from line to bar.  Reason I'm asking is that now Excel 2007 users can add data bars to there spreadsheet with a click of the mouse.  Our users like this new excel feature.  We don't want to encourage our users to run reports to excel just for this feature so any help would be appreciated.
thanks
Jeff

Sunchaser

Hello,

I don't know if I understand clearly what you are trying to do, so first forgive me if ever my answer is 'out'.
Are you trying to have a bar (in a column of a list) which the width will increase regarding to the importance of some values or some percentage ?
If so, the only idea I can give you is the use of a RPAD function: the length of the object is given by a calculation and the RPAD (I work with native SQL from Oracle at 90%, so it is not hard to do there), and I just have to give a good background and foreground color to the object.

Hope it could help you
++
;)

almeids

If Sunchaser's assumption is correct about what you are trying to do you could also do it with master-detail queries (or equivalent C8 concept??) where your list is the master and a bar chart the detail, turn off everything you can on the bar chart (legend etc.) and if that's doesn't take off enough maybe stick it in a block with content clipping.  I experimented with this once and as I recall it yielded fairly decent looking results.

jguevin

thanks for the responses.  You both are correct, I am attaching a picture (.bmp) of a basic data bar from excel 2007, this is done with a click of the mouse.  I am familiar with the master detail example and have used it for adding spark lines.  I have tried the same steps to add the bars and it is very trial and error in getting it to look like the attached example.  My question is whether there might be code already out there, either html or java that could be inserted into an unlocked list cell to do the same thing.
Regards
Jeff

Sunchaser

Hello everybody,

@jguevin: funny ... I took a look at your picture and It looks like the result I have with my 'RPAD solution'. Of course, It's not a sophisticated technique, It's more a 'work around' but It's (in my opinion) flexible: for example you can have several bars of different colors on the same line, put them in a table cell, organise the layout with text or numerical value around it with no real problem, etc and as It is not using any javascript, it is still available on PDF reports.
But it's not a 'classical practice', just a short 'wok around'.

@almeids: do the use of a bar chart isn't too hard to insert in a list, a cell or something like that ? Is it easily possible to hide every things that is 'around' the bar in a 'chart object' ?

++
;)

(sorry for my poor English...)

newmans_99

There are definitely multiple ways of doing this and each have some pros and cons, I am listing the one that I use the most.

First, if you want something flashy like Microsoft's Data Bars (http://msdn2.microsoft.com/en-us/library/bb229709(office.11).aspx) then I would recommend using something like CSS and/or IMG files and then repeat them based on the row's percentage of the total. The only warning on this is that HTML items do not show up PDF output.

Second, if you want very simple then you can repeat something like a bar "|" or a plus "+" for the same affect.

The easiest way to repeat something is to use a database function, here is one that I used in MS SQL Server:
replicate('|',cast_integer([Gross Profit]/100))

I divided by 100 to normalize the data into the screen space I had available, but you could use other calculations to do the same. Here is the results:





However, this topic actually brings up some important things to consider which is accuracy vs pretty.
If you look at the Microsoft link above the way that Microsoft implemented Data Bars can actually mislead the reader into making bad decisions unless they know what they are looking at.

For example, if you look at row 6 in the figure below you will notice that the value is 3,498 and that row 5 is 9,456. If your starting point is 0, then the data bar for row 5 should be approx 3x that of row 6, but Microsoft Data Bars makes it appear much greater (visually I would say approx. 6x or more).



For more information on misleading charts, I have a favorite web site: http://junkcharts.typepad.com/junk_charts/ and this baseball post shows this particular item very well: http://junkcharts.typepad.com/junk_charts/2008/03/amazing-basebal.html

almeids

The RPAD/replicate approach is definitely the most easily implemented, and arguably my master-detail suggestion is the most complicated.  Sunchaser, it's definitely trial-and-error to get the appearance correct as Jguevin notes but it is possible and will give you the solid bar look that the other approach lacks (though that might be possible using ASCII block/line drawing characters rather instead of sticking to what's available on the keyboard, I never tried that).  It's definitely a matter of how much effort you want to make for "prettiness", but as Newmans suggests I'd strive for accuracy in any case and avoid the MS solution.


njexpat

There is a supportlink article on doing inline bar charts with Java and it does work pretty well. All of the code that you will need is inside the article.

http://support.cognos.com/supported/supportlink/multimedia/mm_0704_02.html