Resolved: Values are not rendered in exported excel chart

[SGD]

Hi,

I am generating bar chart which is month wise percentage of product sale. Month is on x axis and percent is on y axis.

Chart displayes expected results means proper bars when I run the report in HTML & PDF but it does not render any bar for excel output.

Product sale percentage value is very low e.g. 0.0038 but using below expression it properly shows bars in the chart as 3.98. I have also set minimum and maximum values of y axis as 0 and 10 respectively.

Code Select Expand

round ((([calc_count_oa_vehicles]/[calc_count_all_vehicles])*100),2)

Could please anyone help me to resolve this issue? Any idea?

Thanks in advance.

[SGD]

Any clues? I am still struggling to get the desired results...  :(

[Arsenal]

do the bars show up in Excel when you multiply by a larger number? Say 10,000 instead of 100?

[SGD]

Hi Arsenal,

As suggested below chart shows proper bars when I use '10000' instead of '100' in below calculation.

However, I need to display percentage of [dateitem1]/;dataitem2] hence I am using '100' in my calculation.

Can we conclude that if value is very small then Excel is unable to display it as chart?

P.S: When I run same data in list report and export in an excel format then it shows desired results i.e. 3.98

[SGD]

Not sure what is going wrong while exporting charts to excel. Still not found any resolution for this issue.

Gurus, your help is highly appreciated.

[Arsenal]

Since the chart shows up ok with large values, I would guess that your realtime values are so small for the percentage as compared to the other series, that the percentage series simply doesn't show up in Excel.

Here's one way to handle it in Excel:

http://peltiertech.com/Excel/Charts/BrokenYAxis.html

You MAY be able to manipulate the solution within Cognos so that the split occurs in Cognos itself but I'm not sure how HTML will handle that or if it can even be done within Cognos. My exposure to chart reports is limited

Here's some discussion on the topic but for Excel:

http://chandoo.org/wp/2010/08/20/charts-with-small-and-large-values/

Here's another way to do it in Excel:

http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html

[AussiePete2011]

Hi there,

I created a quick report and exported to Excel 2007 and 2002 and both show correctly

Environment tested
Windows 2003 Ent Server
Office 2007
Cognos 8.4 FP2
SQL Server 2008 R2

My chart shows values as 0.001 to 0.05 over 7 days and by all accounts renders just fine.
Could you provide more details.
Cheers
Peter

[SGD]

Hi Peter,

Thanks for reply.

Congos Server Environment:
WIn 2003 Ent Server
Cognos 8.4.1 FP3
Office 2003
Oracle 11g

However I am exporting excel report in Excel 2007 format due to file size and opening it on desktop machine in Office 2003.

Desktop machine configuration:
WIN XP SP2
Office 2003

P.S: I am not able to attach the screenshot of my PDF and Excel versions to this post otherwise it would have been more clear.

[SGD]

Hi Arsenal & Peter,

It seems problem is resolved since it seems that Y1 axis bar and Y2 axis bars were overlapping each other so I was not able to see Y1 axis bars in Excel format.

I have set Y1 axis chart type to 'Line' from 'Bar' and it worked fine.  :)