Dynamic Average LINE on Bar-Chart (CR-2013)...?

[former_member183781]

One of those things that is EASY in Excel (and other ChartingTools)....but appears to be out-of-scope in CR-2013.

Looking to create a Dynamic Average LINE on a Bar-Chart. I can quickly create the simple Chart - and calculate the Average on the Values...

...however, in the end-state we would also like to be showing the AVERAGE value on the Chart Y-Axis in a different colour...and have that AVERAGE Line move Dynamically when then data-set changes (*more Departments / Different values).

...neither the DYNAMIC position, DYNAMIC Label, nor the ALTERNATE Colour options appear to be available in the "out-of-the-box" Options on the Y-Axis in CR-2013...and "Draw Custom Line" doesn't fit the requirement...

Thinking that my options are maybe [A] Try to create a CUSTOM Xcelsius (Dashboard) FLASH Object - and try to Bind it within the CR-2013 report design, or [B] Try to create the report in CR4E format to see if there are better Bar-Chart options.

Has anybody solved this Charting issue before...? Let me know your suggestions. Thanks.

[abhilash_kumar · Accepted solution]

Hi Mark,

Let me if this works:

1) Insert a Line Chart and set it to print horizontally from the 'Chart Expert'.

2) Under 'On Change Of' choose the 'Eng' column

4) Under 'Show Values' first add the 'Answer' column and check 'Don't Summarize' if you don't wish to summarize it

5) While in the Preview Mode, Highlight the line > Select Series Options > For 'Selected Series', choose 'Riser'.

6) Go back to the Chart Expert and add the Answer field to the Shows Values list and this time set the summary function to Average

The only issue with this workaround is that the line doesn't stretch from border to border. It starts from the center of the line bar to the center of the last bar.

Hope this helps.

-Abhilash

[former_member183781 · Accepted solution]

Abhilash :

Thanks for the Advice...but think I am missing a STEP on the section 6.) ...?

The 'Answer' field AVERAGE is just creating a 2nd line that is the Average for each Dept_ID, rather than the Average over the whole data-set (*on change of YEAR, etc)..

I want to chart two (2) different assessment options on a single LINE-Chart...and not sure if that is possible here...?

Thanks again for any suggestions on this.

Mark

______________

[abhilash_kumar · Accepted solution]

Hi Mark,

I knew what I was thinking but I really didn't type what I wanted .

After step 5, go ahead and create a formula with this code:

Average({Answer})

Step 6 would then be : "Add this formula field to the Show Values list and check the option 'Don't summarize'.

Let me know how this goes.

-Abhilash

[former_member183781 · Accepted solution]

Abhilash :

Thanks - that gave me an "end-state" much closer to the SPEC while still using the out-of-the-box CR-2013 tools.

Going to see if we can get something that is a 100% match to the spec - by looking at using -

1. 3rd Party Chart add-on Tools for CR-2013.
2. SAP Dashboard (Xcelsius) FLASH Object insertion into CR-2013.
3. CVOM Custom-Chart options in Crystal Reports for Enterprise 4.1.

Will post our progress here.

Thanks for all the advice..!

[ido_millet · Accepted solution]

In Design View, right click one of the bars,
select Trendlines, Mean,
and turn off the 'Show Color same as Series' checkbox.

An average line would be added to your bar chart.

Click to select the line, right-click, select 'Format Trendline...' and change the color... 

Here is what the end result looks like in one of my reports:

http://screencast.com/t/KFJdX6Gyo

[former_member183781 · Accepted solution]

...and Professor Ido Millet for the WIN...!

I'm still going to play around to see if this works the SAME in Crystal Reports for Enterprise 4.1 format...but using CR-2013 is still a solid solution.

Thanks, Ido..!