CR9 Charts: Formula to set axis scale 1

[crnine]

Can you use a formula to set the maximum value for the scale in a chart in Crystal Reports 9?

In my example I have a chart for each record returned. I might want to set the maximum value to be the maxium of ALL the records (a summary function) so that the scale is consistent between records, and it is also appropriate in that it fits all the data.

I can only see options to have an auto-scale (not what I want as the scale is different from record to record) or set an arbitrary maxium (not what I want as I don't know what the data will be).

Please help, thanks!

 

[lbass]

No, I don't think you can.

-LB

 

[crnine]

Thanks LB. At least I know the limitations now.

 

[SkipVought]

Laurie,

As you probably know, I'm a big Excel/VBA user. However, our company is moving toward SAP/BO/CR et al.

I have interactive Excel applications that use controls to plot selected data, often with primary and secondary axes that need programic synchronizing of scales when a control is clicked.

Is such functionality available in CR?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lbass]

Skip--do you mean me (Linda)?

CR can handle dual axes, but I don't believe there is the functionality you describe to syncronize scales programmatically. There are add-ons that would expand functionality, e.g., through threedgraphics, but I haven't used these myself.

-LB

 

[hilfy]

One of the ways I've handled it is to do used multiple charts in Xcelsius, over-lay them and set the scales programatically. I then embed the flash from Xcelsius in a Crystal report and use Crystal to provide the data using Flash variables. In Crystal 2008 and above there are some Xcelsius "helper" functions that work together to build Flash variables needed for this.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[SkipVought]

Linda,

Yes, I meant you. For some reason, I thought your name was Laurie. Please accept my sincere apology for my blunder.

Thank you for this information. I am building a list of deficiencies that I'll need to address with the powers that be. I suppose that I have been spoiled by Excel's build-in VBA capability that enables me to make it do extra-ordinary things and develop such 'wonders' fairly rapidly. I will have crawling and walking to experience again, before I begin running in this new environment.

Thanks again, and know that I am learning from many of your posts.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lbass]

Skip, Did you see Dell's post?

-LB

 

[SkipVought]

I missed Dell's post.

multiple charts in Xcelsius, over-lay them and set the scales programatically

In some cases I have a list of CostCenters and a dependent list of Machines contained in any selected CostCenter. I can display a plot based on ONLY a single CostCenter or a plot based on a singel CostCenter and a singel Machine within the CostCenter.

There may be some 200 CostCenters and as many as 100+ machines per CostCenter. In Excel, I can create ONE chart, maintain ONE chart, manipulate ONE chart and view all these various selections of data.

Is that possible?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hilfy]

Possibly. It depends on how your data is set up.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[SkipVought]

My data is in a table

When The user selects a control, the value is assigned to a Parameter in a query, that returns the chart data and fires the plot and reformatting of axes if necessary.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...so tomorrow I may get a differnt plot for the same selected control value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hilfy]

Is there a maximum number of values? A max number of series of values? What type of a chart are you currently using?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[SkipVought]

I'm usually using a Stacked Column Chart, often with 2 y axes. The Category axis is often date (week or month) and/or usually limited to less than 30 values.

Example
[tt]
DT IV RP RQ Net Short Surp
PD 2 1 -33 -30 -30 0
22-Aug 0 -3 -33 -33 0
29-Aug 0 -1 -34 -34 0
5-Sep 0 -3 -37 -37 0
12-Sep 4 -1 -34 -34 0
19-Sep 10 -6 -30 -30 0
26-Sep 20 -1 -11 -25 0
3-Oct 22 -4 7 0 7
[/tt]
IV, RP, RQ are stacked column plotted in Pri axis, and width is 1/4 the category value width.

RP is dark green, RQ is dark red.

Short & Surp are also stacked plotted on secondary axis, and width has no gap, so it appears to be like an area chart.

Short is shaded light red, Surp shaded light green.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hilfy]

Yes, you can probably do this in Xcelsius. Even though Xcelsius uses an Excel data model to pull its data from, it doesn't recognize some Excel formulas and I don't think you can use VBA with it. However, I've been able to do some pretty cool stuff with it despite those limitations. My latest use of it was to provide a stacked bar chart of resource allocations over time that has two lines - one for target headcount and one for actual headcount. I had to use two graphs (a stacked bar chart and a line chart), format and overlay them in such a way that everything lined up, including the legends and set the scales based on the data so that both charts have the same scale. It's pretty powerful stuff!

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[SkipVought]

I've done some overlays as well. Can be a bit tricky.

I'm mostly concerned about on the fly querying/charting in this environment.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hilfy]

It all depends on how you set up your data model. If the chart type itself doesn't change and you have limits on the number of rows and columns, it should be doable. If you need different types of charts based on user selections, you can overlay charts and use data to control which one is visible. It all just depends on how you set up your data model.

Another thing to look at if you're providing the reports through BO is an add on for Xcelsius called Antivia. I've been able to do some really cool things like dynamically changing the date scale on a chart from week to month to year based on user selections.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[SkipVought]

Thx Dell!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]