Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Targets Added to Cross-tab

Status
Not open for further replies.

GC2000

Programmer
Sep 19, 2008
27
CA
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?
 
Does anyone have any suggestions on this? After a couple of other reports and I am back to working on this one.
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top