Crosstab - Extra Total Columns without Extra Rows

[bernie321]

Hi

Im new to cross tabs, we have created one using the Wizard, it is a Month By Month Target Report and looks kind of like this:

Staff ID, Month1Billed, Month2Billed, ...., TotalBilled

We want to expand it:
ID, M1Billed, M2Billed, ..., Total, TargettoDate, LastYearTotal

The problem is that adding the target to the CrossTab will give another row, also adding last years Target to Date will also add another row.

What is the best way of adding these Total Columns without a new row of figures.

Any help will be much appreciated.

Thanks
B

 

[lbass]

What version of CR?

-LB

 

[bernie321]

Hi

Thanks for your post CR XI.

B

 

[lbass]

Add two additional summaries. One would be a maximum of {table.targettodate} and the other would be a sum of a formula:

if year({table.date}) = year(currentdate)-1 then {table.amt}

Go to the customize style tab and check "horizontal" and "show labels" for the summaries. Then select the inner cells, the column labels, and the column totals for the two new summary columns->right click->format field->suppress. Then resize the columns by dragging the left or right border to minimize the width. This should result in the desired display.

-LB

 

[bernie321]

Thanks Lbass

We are almost there, the only problem now is that I needed to add 7 extra fields in the end and I have made them as small as possible but Crystal will not make them any smaller than "0.150".

Therefore there is a large gap between the two records.

Is there any way of fixing this as I cannot get a legible report onto one page.

Thanks

B

 

[lbass]

I was able to get them down to .05 width in format->size and position. You could also uncheck "show cell margins" in the customize style tab to see if that display suits you.

-LB

 

[bernie321]

Hi

Thanks for your post:

Even using Size and Position one of them it refuses to go below ".18" the rest ".15"

Show cell margins is already unchecked.

Thanks
B

 

[lbass]

I am referring to the width, as I'm assuming you are displaying the summaries horizontally. Try going to options->layout tab->grid options->grid size and change the value to .01. Then select the crosstab column->format->size and position and enter .05 in the width box.

-LB

 

[bernie321]

Hi

Yes both are correct: i'm referring to width and displaying smmaries horizontally.

The minimum grid size is .026, which I have already, and typing 0.05 does not appear to have an affect.

Thanks
B

 

[lbass]

I don't know why your minimum grid size is higher than mine, but mine is .01, and my column width is .05. I don't know what else to suggest.

Depending upon your fields, i.e., if there is one value per ID for each of the seven fields, you might try making a formula that concatenates the seven values, and add these as one summary, instead of seven.

-LB

 

[bernie321]

Hi Lbass

As you say its weird that our settings are different.

How do you mean "concatenate the seven values"?

Thanks
B

 

[lbass]

Create a formula that adds them together, as in:

{table.string} + " "+totext({table.number},0)+" " + //etc.

Then use this as one summary, instead of the seven--but as I said earlier, whether this works depends upon whether the fields are the same in all rows for a particular ID. You might be able to use this to eliminate at least a few summaries.

-LB

 

[bernie321]

Hi

Thanks for your post.

First in the string appears correctly but the second third and forth incorrectly appear as zero, my code is below:

totext({@LastYear},0) + " " + totext({@AverageMonthlyBilling},0)+ " " + totext({@YearlyBillingBasedOnAverage},0)+ " " + totext({@TargetForYear},0)

Thanks
B

 

[lbass]

What are in the nested formulas? They shouldn't be summaries, just the fields themselves.

-LB