Please HELP!

[Darrick]

I've been struggling with this problem for months now! I'm obvisouly overlooking something simple.

I have a report with all of my data on it. I'm trying to graph some informaton from the report. I'm using the Chart Wizard to format my report. When I try to the the wizard what to show on the report, or when I select a FORMULA field that I created to be my "Show Value", Crystal won't allow my to put it into the box. It will allow me to select other fields to be displayed, but it won't let me display the one that needs to be on the graph.

PLEASE HELP,

Darrick
darrick3@yahoo.com

 

[kenhamady]

Not all formulas can be used for totalling or charting. Your formula may use a summary operation or be set to be evaluated while printing.

If you post the formula we can tell you why and possibly suggest an alternative. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Darrick]

Thanks Ken,

Here is the formula from the sub report:

Shared NumberVar SubClockTime := {#JobClockTime};

and here is the formula from the main report:

NumberVar ConnectsPerHourTotal := {#AgentConnects}/{@JobClockTimeSub};

I hope this helps...

Darrick

 

[kenhamady]

Shared Variables are calculated WhilePrintingRecords, they can't be used in totals. If you bring the value to the subreport as a linked parameter, instead of as a shared variable, it will allow.

Of course you have another problem, the # indicates that these are running total fields. Running totals are also WhilePrintingRecords, so they can't be used for totals or charting. Do you need running totals? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Darrick]

I could simply do a formula where I calculate the sum instead of using the Running Total fields.

How can I bring the values into the main report as a linked variable? and how would I then use it?

Thanks again Ken,

Darrick

 

[kenhamady]

A formula that calculates the sum is still a summary operation. That may not get you where you need to go.

To move a value from main to sub create a paramter field in the Sub that has the appropriate data type. Use the parameter in your formula.

Then link this parameter to the appropriate field in the main report. (Right click on subreport and select "change subreport links&quot. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Darrick]

I have to have the sum or running total field to make the report woth anything. Is their any alternative to makeing this information to where I can graph it?

Darrick

 

[kenhamady]

You can chart on totals, and even some running totals, you just can't chart on formulas that use these totals if your chart is trying to summarize the summaries.

Describe the chart you are trying to do, and tell us what verison of CR you are using. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Darrick]

I wish you could attach reports to the message board!

I have a table that has all of my information regarding my agents. This is where I get the number of conncts do a sum by day for each agent.

Then I have another table that has all of my agent login and logout information. This is where I get the number of "Agent" hours for each day. I do a sum by agent and day to get my totals.

I then do a a division of the two results and I get my Connects per hour based on my agents.

I'm trying to chart the "Connects Per Hour" by agent.

Please note that I have a parameter query that is set up to ask the user for the date ranges that they want to see.

Thanks again Ken,

Darrick

 

[kenhamady]

You may not be able to do this because of the many to many relationship. Does the first table have one record for each agent each day, or does it have multiple records for one agent on the same day?

What would the chart look like?
(ie One bar per agent or per day?)

Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Darrick]

Yes, it is a many-to-many relationship. The first table has mutiple records per agent per day.

The chart is simply a bar chart. One bar per agent, per chart.

Thanks,

Darrick

 

[kenhamady]

You would have to get one of these tables summarized down to one record per bar somehow, or do some kind of union query that appended one set of records to the other. Possibly a stored procedured. Otherwise I don't see how you can do this chart.

The problem is that there is no way to do accurate summing in a many to many relationship and still keep the ability to chart the results. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Darrick]

What if I wrote an SQL statement to give me only the aggerates while pulling the information into Crystal? For example, modify the SQL statement in Crystal to do the sum's for me.

Darrick

 

[kenhamady]

If you create a view or SP in the database to do this, it might work. But I don't think you can modify the SQL in CR enough to do this.

You could also use the CR SQL designer for the SQL, and run the report off of the QRY file. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Darrick]

Where do I get the SQL Designer?

 

[kenhamady]

It comes with CR Professional Edition or CR Developer Edition. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Darrick]

Hmm.. I'll have to get my I.T. department to install it. Apparently they forgot.

Thanks for the tip...

I am able to get all of the total time and the total connects by agent in the graph. It's just a matter of being able to divide the two and display only that in the chart.

Darrick