Graphs - Using Several Fields as One Series

[MrTBC]

I hope this is not bad form to post this question more than once. Hopefully this is a clearer explanation of the problem I have.

I am using crystal 8.5 and reporting from a table on business costs containing these fields:

Cost Type - e.g. "Cleaning"
Year - e.g. "2003"
Period 1 Spend - e.g. "2,306"
Period 2 Spend - e.g. "1,239"
...... etc.
Period 12 Spend

I need to show this as a line graph for each type of cost, spread on the x-asis from periods 1 to 12. Crystal seems to want to actually graph all the different values for Period 1, or all the different values for Period 2, etc.
Basically, I need to do the equivalent of 'reversing the series' in Excel, but I am completely stuck!

 

[mwardle]

I don't think it can be done in the manner you wish. If you are using SQL Server, you can write a stored procedure that will can adapt the table into a suitable form. Eg

SELECT CostType, TheYear, Period1Spend AS AmtSpent, 1 AS ThePeriod
FROM tblData

UNION ALL

SELECT CostType, TheYear, Period2Spend AS AmtSpent, 2 AS ThePeriod
FROM tblData

UNION ALL

SELECT CostType, TheYear....

and so on.
You can use this as a reportsource for CR.
Then you can create a chart in CR on change of CostType and ThePeriod, showing AmtSpent.
If you want more description headers for your periods, use a formula as a group name.

Something similar can doubtless be done using Oracle. I don't know about other data sources.

 

[MrTBC]

Thank you. Unfortunately I'm not, I'm just using Crystal to report from a database that I can't manipulate. At least I can stop messing around trying to make it work now!