Suppress Row in Cross Tab

[IanWaterman]

CR10 on SQL Server 2008

I have a number of Cross tabs all lining up nicely from an aesthetic point of view.

In one I want to show pass/Fail for an SLA, I have grouped the Cross tab on pass and Fail and expressed as a %age of total calls and suppressed the column totals. I only really want to see the Pass %age but can not work out how to suppress the Failed Row. I tried specified order but when I discarded the failed row Pass became 100%.

I know I can do this with manual cross tab but would prefer to avoid that as it would make ruin the layout.

Ian

 

[Madawc]

If you put the crosstab in a subreport, you might be able to line things up so that the 'fail' column was not displayed. This would probably involve changing the names so that 'Pass' comes first.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

What is the name of your passfail field? Let's say it is called {table.passfail}. Then to suppress the related row, select the label and inner cell->right click->format field->suppress->x+2 and enter:

gridrowcolumnvalue("table.passfail") = "Fail"

In the customize style tab->check "suppress blank rows". Not sure this will work--maybe it will in CR2008.

-LB

 

[IanWaterman]

LB

gridrowcolumnvalue is a CR10 function

I have used

gridrowcolumnvalue("@Calls ans 1st SLA") = 0

{@Calls ans 1st SLA} returns 0 if SLA failed.

to format Label, Cell and Row Total and entire row is now blank.

In CT Expert I have checked suppress blank rows but whilst the row is totally blank its still there.

Any suggestions

Ian

 

[lbass]

Gridrowcolumnvalue has been around for longer than that. What I meant is that the "suppress empty row" doesn't consistently work. Try the following. Create two formulas:

//{@passcalls}:
if {@Calls ans 1st SLA} = 1 then
{table.calls}

//{@totalcalls}:
if {@Calls ans 1st SLA} = 1 then
sum({table.calls})

Add {@totalcalls} as your first summary and {@passcalls} as your second summary. Then right click on {@totalcalls} in preview mode->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := currentfieldvalue;
true;

Then right click on {@passcalls} in preview mode->format field->suppress->x+2 and enter:

gridrowcolumnvalue("@Calls ans 1st SLA") = 0

(Suppress the label with with the same formula.)

Still for the {@passcalls} summary, go to the 'display string' formula area (format field->common->display string->x+2) for the inner cells, and enter:

whileprintingrecords;
numbervar tot;
if tot > 0 then
totext(currentfieldvalue%tot,1)+"%" //1 for one decimal

Then in the customize style tab, check "suppress empty rows".

-LB

 

[IanWaterman]

Can't get this to work. I do not really understand what you are doing so difficult to fault find.

For example I am not sure why this is being summed

//{@totalcalls}:
if {@Calls ans 1st SLA} = 1 then
sum({table.calls})

I have even tried Count({table.calls}), but that does not work either. The row is now suppressed but the value in the Pass Row shows 0.1% for all columns!!

Finally if I do get the columns correct, how would I derive a row total.

I have fudged an answer by using TopN sort and showing Top 1, pass rate is usually 90% so should always be correct.

Ian

 

[lbass]

Please clarify what your summary field is--is it a count? What is the field? Does {@Calls ans 1st SLA} = 1 when it isn't 0?

-LB

 

[IanWaterman]

{@Calls ans 1st SLA} returns 1 or zero. I did this originally as I thoughta number would make things easier to compute.

Being lazy (this could be the root of the problem), I use the above formula as my row and then count records for each group and express these as a %age of the total call count.

In group options I use a formula to change the group name, using '' for fail.

This works fine until I come to the point of suppressing the Failure summary row.

Ian

 

[lbass]

Change the formulas to:

//{@passcalls}:
if {@Calls ans 1st SLA} = 1 then
1

//{@totalcalls}:
if {@Calls ans 1st SLA} = 1 then
count({table.field}) //any recurring field--an ID field, eg.

Then follow the previous post. You would use "sum" as the summary for {@passcalls} and "maximum" as the summary for {@totalcalls}--which I neglected to say in my last post.

If you want this same comparison for the row total, select the row total cells along with the corresponding inner cell when you add the formulas into the suppression and display string areas.

I tested this and it worked.

-LB