Targets on graphs

[waldopepper]

Hi

Here's a question I think I know the answer to but I thought I would double-check.

I think I've found the only way to put a 'Target' line on a bar chart (Grid, Custom line) but you can't label it so it's a bit useless for my needs.

What would be really great is if I could put more than one line with labels on - is there any way of doing this?



This is the simple part of my question.

Our client has also asked if there is a way of putting a different target on the graph for each element (e.g. week number along the x axis) that would be read in from an Excel spreadsheet - I assume this isn't possible in Crystal Reports as you can't 'import' info into reports apart from labels or data?

Week number Target
-----------------------------
1 80
2 70
3 90


So 3 target line would appear above the individual bars weeks 1,2 and 3 (from the actual data).

Is this just too crazy to do in Crystal Reports?

thanks



Crystal Reports v10
Crystal Enterprise v10
Windows 2000

 

[lbass]

I think you could simply create a formula:

select {@weekno}
case 1 : 80
case 2 : 70
case 3 : 90
default : 0

Then add this as a second summary field, and check "do not summarize". This line will be identified in the legend, too.

If you have a lot of target numbers, you can write a quick CR that will create the formula for you, by creating a formula like this:

"Case " & {@weekno} & ":" & {table.target}

Add "Select" in the report header and "default : 0" (or your default value) in the report footer. Export this to a text file and then copy the text into a new formula in your CR report containing the chart. This isn't dynamic, but it is pretty easily done.

-LB

 

[waldopepper]

OK great - thanks I'll give this a go. It's actually date's that will go along the x axis and each bar will have a combination of 2 figures (stacked bar chart) but this should still work ok.

So I guess there's no way I could get these figures from reading in from Excel dynamically?

thanks!

 

[lbass]

If you are unable to link to the Excel spreadsheet (as I think is the case), you could use the spreadsheet in a subreport, but then you'd have to use shared variables, and use the charting method described in the BO whitepaper "Charting on print-time formulas".

You haven't identified your datasource, but the only other alternative might be a method I've never used, so couldn't help you with, which involves either importing into Access or linking the tables in Access (preferred method?), so you are working with one datasource. You could do a search on this.

-LB

 

[IdoMillet]

It's not clear if you are trying to connect to the Excel data source and failing.

To use Excel as a data source for a Crystal report, set the desired tabular area as a Named Range. If you then create an ODBC DSN, the Named Range will behave as a Table in Crystal.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[waldopepper]

Ah sorry I'm not being that clear.

The data source is an Oracle database (view) - I'm connecting to that and displaying figures in my graph just fine.

The stacked bar chart shows 2 groups of figures for each line of data (date, but I've used weeknumber above to make it simpler).

What the clients wants to be able to do is have a spreadsheet sitting on the network somewhere that also contains each weeknumber and a 'target' number for each weeknumber. They want Crystal to display the targets from the Excel file against the actual data from the Oracle view.

 

[IdoMillet]

Still not clear if you are experiencing problems getting the data from excel.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[waldopepper]

I haven't tried yet - I'm just trying to work out if I went this route if I could actually take data (targets) from Excel and display them on a graph which is based on the other data source.

 

[Dan3D]

Just have to mention that there is a 3rd-party charting replacement library called CRChart that easily solves this issue. You can create static or parameter driven "reference lines" and label them with text and also append the current value.


-Dan

Disclosure: This product costs money. I work for the company that makes this product.


Dan Weingart
OEM Technical Manager
Three D Graphics, Inc.

http://www.threedgraphics.com