Targets Added to Cross-tab

[GC2000]

Does anyone know of a way to add targets to a crosstab. I am using Crystal 2008. Currently the cross-tab looks like

Service1 Metric 1
Metric 2
Metric 3
Service2 Metric 1
Metric 2
Metric 3
Service3 Metric 1
Metric 2
Metric 3

What I would like to do is add a target column which is different for each service.

Service1 Metric 1 TargetService1Metric1
Metric 2 TargetService1Metric2
Metric 3 TargetService1Metric3
Service2 Metric 1 TargetService2Metric1
Metric 2 TargetService2Metric2
Metric 3 TargetService2Metric3
Service3 Metric 1 TargetService3Metric1
Metric 2 TargetService3Metric2
Metric 3 TargetService3Metric3

I can't hard code the values because they are different per service. I also want to avoid doing a mock crosstab because the data is spread across months and I would have to create suppression conditions for everything. Any ideas?

 

[GC2000]

Does anyone have any suggestions on this? After a couple of other reports and I am back to working on this one.

 

[lbass]

You say the crosstab looks like the above but also say it is spread across months--where are the months? Are the targets found in a database table? What are the crosstab row, column, and summary fields?

-LB

 

[GC2000]

Hi Lbass

The months are the columns. So you would see

Jan09 Feb09

Service1 Metric 1 TargetService1Metric1 Result Result
Metric 2 TargetService1Metric2 Result Result
Metric 3 TargetService1Metric3 Result Result
Service2 Metric 1 TargetService2Metric1
Metric 2 TargetService2Metric2
Metric 3 TargetService2Metric3
Service3 Metric 1 TargetService3Metric1
Metric 2 TargetService3Metric2
Metric 3 TargetService3Metric3


The targets are not found in a DB table, they are hard coded. The rows are Service, Columns are date and the summary fields are a count of orders, Average of orders, Avg time orders take. I tried to do it in a formula and got close but the alignment did not match in the cross tab. Let me know if this gives you enough information.

Thanks

 

[lbass]

If the data is not in a table, you have to hard code the targets. You should be able to create a formula like this:

if {table.service} = "Service1" and
{table.metric} = "Metric1" then
500000 else
if {table.service} = "Service1" and
{table.metric} = "Metric2" then
350000 else
if {table.service} = "Service1" and
{table.metric} = "Metric3" then
400000 else //etc.

Then add this as a summary field, and insert a maximum on it. Format the summaries to display horizontally with a summary label.

I guess you could also try to create a table in Excel or Access and link it to your existing table on Service and Metric.

-LB