Calculation

[McChops]

Crystal Reports v9
SQL 2000 Server

Need to find a solution for the following;

Every record has an 'SLA' column with a value of either

1. No Breach
2. Restore Breach
3. Fix Breach
4. All Breach

Every record also has a 'Region' value (lets just say North, East, West, South)

The business prefers the info to be populated into a matrix (I've started going down the path of a cross tab, but will happily change direction if a better solution is suggested).

The data returned should detail;

1. totals for each region (eg, Region West had a total of 20 records of which 5 were 'Restore Breach', 10 were 'No Breach', 5 were 'Fix Breach' and 0 were 'All Breach')

This I can easily get.... However

Using the example above the business wants the data displayed like...

Region West had a total of 20 records of which 15 did not breach "Restore Breach", 15 did not breach 'Fix Breach' and 20 did not breach "All Breach".

Hope this makes some sense.

Thanks in Advance

 

[MisterMo]

You could create a group and region level and then add running totals set them to count and on the evaluate section add the a formula for every option you need

eg.

{YourTable.SLA} <> "Restore Breach"

reset count on change group.





Mo

 

[McChops]

Thanks Mo. Do you know if a Cross Tab will support this solution?

 

[MisterMo]

Don't think so because you are using more than one running total and Cross Tab due to its flexibility only offer limited options

There is nothing stopping you to create a manual Cross Tab.

Provided you have a fixed number of options on the orizontal axys

Mo

 

[McChops]

No unfortunately I don't, but thanks for your advice anyway Mo.

 

[lbass]

You might be able to use a crosstab if you use region as the row, use no columns, and then use formulas for your summaries, inserting SUMS as the summaries, as in:

//{@dnbrestorebreach}:
1-{@restorebreach}

//where {@restorebreach}is:
if {table.sla} = "Restore Breach" then 1

//{@dnbfixbreach}:
1-{@fixbreach}

//where {@fixbreach}is:
if {table.sla} = "Fix Breach" then 1

//etc.

-LB