Crosstab one vs. all problem 1

[mtessier]

Hello,

I'm using CR9 and I'm fairly new to crystal.

I have a crosstab on a report where I'm showing top 5 classes (sorted by the 'All' column) like so:

Pay Direct All
class1 6.6% 9.3%
class2 9.3% 8.8%
class3 8.0% 7.9%
class4 5.6% 6.7%
class5 7.8% 6.2%


This crosstab is used as a comparison between one client ('Pay Direct' column) and all clients. I accomplished this part using the following formula for column in the crosstab expert, sorted descending (Basic syntax):
@ClientCompare:
If {Command.INS} = {?a_client} Then
formula = "Pay Direct"
Else
formula = "All"
End If

For the summarized field in the crosstab expert, I'm using percentage of sum on the following formula:
@ClientCompareClassTotal:
If {@ClientCompare} = "Pay Direct" Then
formula = {@ClientClassTotal}
Else
formula = {@ClientClassTotal} + {@ClassPaidAmtNoBlanks}
End If

Here are the formulas that this formula references (complicated, but I found it necessary for filtering by client):
@ClientClassTotal:
If {@ClientCompare} = "Pay Direct" Then
formula = {@ClassPaidAmtNoBlanks}
End If

@ClassPaidAmtNoBlanks (Used to strip out unclassified rows by setting them to 0, since their amounts are big enough to appear in the top 5 which we don't want to see):
If Trim({Command.CLASS}) <> "Unclassified" Then
formula = {Command.PAID_AMT}
Else
formula = 0
End If

The problem is that the percentage in the 'All' column is inaccurate and changes based on the amount in the 'Pay Direct' column. The number displayed in the 'All' column looks to be 'all others'. This becomes obvious as I increase the time length of the report (Tested for 1 month, needs to run for 1 year).

More points of note:
- the report is based on a SQL query instead of table joins for performance and aggregation reasons, so SQL expression fields are not possible.
- This crosstab is one of many parts on this report. All of the data that I get from my query is needed somewhere on the report, so record filtering is not possible. I even need the amounts in the 'Unclassified' columns, which is why I'm filtering them at the crosstab level and not in the SQL query.
- Initially, I tried to do this crosstab in a subreport using the parameters from the main report, but for some reason it wouldn't show up in the main report for me. I'm open to trying it again if that's the solution.
- I tried the technique above using a specified order for the column and using the row total as the 'All' column. The total column would display the right amount, but I would need to figure out how to suppress the 'Others' column without affecting the total column for that solution to work. If this is doable, let me know.

Thanks in advance,

Mike

 

[lbass]

Instead of using a column field, use two conditional summaries for your summary fields:

//{@PayDirect}:
If {Command.INS} = {?a_client} and
Trim({Command.CLASS}) <> "Unclassified" Then
formula = {@ClientClassTotal}

//{@All}:
If {Trim({Command.CLASS}) <> "Unclassified" Then
formula = {@ClientClassTotal}

In the customize style tab, format the summaries to "horizontal" and "show labels".

-LB

 

[mtessier]

Hi LB,

Thanks for the quick reply.

Maybe it's because I didn't fully understand your response, but this didn't work for me. If I clear the Columns area from the crosstab expert and make the Summarized Fields area contain the @PayDirect and @All formulas, I get no data displayed. However, if I put something in the Columns area, I get data but I get extra columns. This is what it looks like when I put @ClientCompare back in the Columns area:

Pay Direct All
@PayDirect @All @PayDirect @All
class1 6.6% 6.6% 0.0% 9.4%
class2 9.3% 9.3% 0.0% 8.8%
class3 8.0% 8.0% 0.0% 7.8%
class4 5.6% 5.6% 0.0% 6.7%
class5 7.8% 7.8% 0.0% 6.2%

FYI I also changed my @PayDirect and @All formulas to return {Command.PAID_AMT} as this made more sense with these formulas.

Thanks,

Mike

 

[lbass]

Sorry, both of my formulas should have used {command.paidamount} not the formula {@ClientClassTotal}. Please try again--with no column field.

-LB

 

[mtessier]

I tried with no column name unsuccessfully.

I have attached a document with screen shots to show what my crosstab looks like.

Thanks,

Mike

 

[mtessier]

Sorry, screwed up attachment

Mike

 

[lbass]

Please show some sample data at the detail level, with the class field, the client field, the amount field, and the two formulas:

//{@PayDirect}:
If {Command.INS} = {?a_client} and
Trim({Command.CLASS}) <> "Unclassified" Then
formula = {command.paidamount}

//{@All}:
If {Trim({Command.CLASS}) <> "Unclassified" Then
formula = {command.paidamount}

-LB

 

[mtessier]

Hi LB,

I have attached a small sampling of data for 5 clients (219 records) in Excel.

Hope this helps.

Thanks,

Mike

 

[lbass]

But you didn't add the two formula fields to show how they display at the detail level.

-LB

 

[mtessier]

My bad

I created a simple report to demonstrate the data details and the results of these formulas. The results are as I expect them to be.

Thanks,

Mike

 

[lbass]

The crosstab should work fine without the column. Where are you placing the crosstab? It should work either in the report header or report footer.

-LB

 

[lbass]

Sorry, I just realized what you are doing wrong. You cannot suppress the row grand totals, since that is all you are now displaying with the two summary fields. So uncheck that on the customize style tab.

-LB

 

[mtessier]

This looks to be working.

Thanks very much for your help LB.

Mike

 

[mtessier]

Hello again LB,

Now I'm trying to put a border between those 2 columns. Is there a way to do it?

One thing I tried was to put borders on the right and left edges of the cells in the 2 columns themselves. This worked, but for some strange reason it stretches the "Column labels horizontal lines" line past the right edge of the crosstab. I have another header above the "Pay Direct" and "All" labels, so I do need a line there.

I also tried manually dropping a line at the appropriate spot on the crosstab, but the line hides behind the crosstab and can't seem to be moved to the top.

Another new requirement I've been asked for is a total of the percentages in these 2 columns. Unfortunately, enabling column grand totals displays 100% even though the crosstab displays only the percentages of the top 5 classes. Is there a way to display the total of only the 5 percentages that are displayed in the crosstab?

Thanks,

Mike

 

[lbass]

You should be able to insert a line and place it on top of the crosstab--I just tried it and it worked.

For the totals, allow the current columnn totals to display. Select the inner column and corresponding row total column->right click->format field->suppress->x+2 and enter the following:

whileprintingrecords;
numbervar x := x + currentfieldvalue;
false

Then select the column total for the inner column and the corresponding row total column->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar x;
totext(x,1)+"%"

While the same total fields are selected->right click->format field->borders->color->backgound->x+2 and enter a reset formula:

whileprintingrecords;
numbervar x := 0;
defaultattribute

Repeat for the other summary, but changing the variable name to y in each formula.

-LB

 

[mtessier]

The totals worked. Thanks!

I'm still having problems drawing a line on top of the crosstab. It will draw over the column headings and totals, but it will not draw over the cells themselves. I have attached a screenshot to demonstrate. I do have a workaround, but if there's a better way I'd rather do that.

Is there a property in the crosstab that I'm missing? It doesn't look like I'm forcing a background anywhere in the crosstab.

Thanks,

Mike

 

[lbass]

I cannot recreate this problem. How are you creating this line? You should be going to insert->line.

-LB

 

[mtessier]

I've tried Insert->Line and I've also tried copying and pasting an existing line. Same problem.

Mike

 

[lbass]

Maybe this is a problem with v.9. What happens if you use one component of a text box (one line with a border)?

-LB

 

[mtessier]

Looks like this is not as easily doable as I thought it would be. I'll just go with my workaround.

Thanks very much for your help anyways.

Mike