Cross Tab: substituting values if 0

[RWahlert]

I'm working on a cross tab report that shows the average number of members for a union on a monthly basis. Some groups of the union are only counted quarterly and for the months where they are not counted my report shows 0. I would like to display the value from the months where there is a count.

An example of the current and desired output might help:

Current Output

1/2006 2/2006 3/2006 4/2006 5/2006 6/2006
0 0 4 0 0 5


Desired Output
1/2006 2/2006 3/2006 4/2006 5/2006 6/2006
4 4 4 5 5 5

Does any one have any ideas how I can do this? I've been thinking about using the display string setting in the format field window.

Thanks.

Ralph

 

[Madawc]

Which Crystal? In Crystal 10, it should be possible to do a crosstab on a formula field that uses one or other of the types of totaling

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Ralph, If you are willing to change the display to show the most recent months first (choosing descending order for the column based on the datefield per month), you could do the following:

You would need a separate variable per union group, and in the following example I am showing variables for only two union groups. Let's say that your row field is {table.uniongrp}, and that you have a set of 6 union groups, but groups B and E are only counted quarterly, while the others are counted monthly. Your crosstab would be set up with {table.uniongrp} as the rowfield, and {table.date} as the column->group options->descending order->print for each month. We'll say that the summary field is count of {table.ID}. Select the summary in an inner cell->right click->format field->common tab->suppress->x+2 and enter:

whileprintingrecords;
numbervar cntB;
numbervar cntE;

if gridrowcolumnvalue("table.uniongrp") = "B" and
remainder(month(gridrowcolumnvalue("table.date")),3) = 0 then
cntB := currentfieldvalue;
if gridrowcolumnvalue("table.uniongrp") = "E" and
remainder(month(gridrowcolumnvalue("table.date")),3) = 0 then
cntE := currentfieldvalue;
false

Then in the same screen go to display string->x+2 and enter:

whileprintingrecords;
numbervar cntB;
numbervar cntE;

if gridrowcolumnvalue("table.uniongrp") = "B" then
totext(cntB) else
if gridrowcolumnvalue("table.uniongrp") = "E" then
totext(cntE) else
totext(currentfieldvalue)

When using gridrowcolumnvalue, use the field that represents the row or column in the crosstab, and replace the curly brackets with "".

The above only works for descending month order. If you have to use ascending order, then you would need to create a manual crosstab, where you group on {table.uniongrp} and then use formulas like:

//{@Jan}:
if {table.uniongrp} in ["B","E"] and
datepart("q",{table.date}) = 1 then 1 else
if not({table.uniongrp} in ["B","E"]) and
datepart("m",{table.date}) = 1 then 1 else 0

//{@Feb}:
if {table.uniongrp} in ["B","E"] and
datepart("q",{table.date}) = 1 then 1 else
if not({table.uniongrp} in ["B","E"]) and
datepart("m",{table.date}) = 2 then 1 else 0

//{@Mar}:
if {table.uniongrp} in ["B","E"] and
datepart("q",{table.date}) = 1 then 1 else
if not({table.uniongrp} in ["B","E"]) and
datepart("m",{table.date}) = 3 then 1 else 0

Then you would insert sums on these formulas and suppress the detail sections.

-LB

 

[RWahlert]

LB:

Thanks for the reply! This is great stuff and I'm making forward progress. I am running into an issue where the variable is not resetting and I am getting odd results.

This is my formula in the supress field:

whileprintingrecords;
numbervar Cycle;

if gridrowcolumnvalue("Cycle") = "Quarterly" and remainder(month(gridrowcolumnvalue("Month")),3) = 0 then
Cycle := currentfieldvalue;
false

This is the formula in the display string field:

whileprintingrecords;
numbervar Cycle;

if gridrowcolumnvalue("Cycle") = "Quarterly" and remainder(month(gridrowcolumnvalue("Month")),3) <> 0 then
totext(Cycle)
else totext(currentfieldvalue)

This is sample of the result:

12/05 11/05 10/05 9/05 8/05 7/05
Q 1006 1373 1373 1014 1364 1364

1006 and 1014 are the correct values, but I not sure where the 1373 and 1354 are coming from. Is there any way I can reset the variable before the next row?

Thanks again for your help.

 

[lbass]

The display formula should be:

whileprintingrecords;
numbervar Cycle;

if gridrowcolumnvalue("Cycle") = "Quarterly" then
totext(Cycle)
else totext(currentfieldvalue)

-LB

 

[RWahlert]

LB:

I've tried both ways, with the same repsults. I have figured out what is happening, but don't know why it's happening.

If the cycle is quarterly for a row, and it's the last row of quarterly records on the page then that row works correctly. If there is a second, or third row that is quarterly then those rows will dislay the values from the last row on the page.

Here is an example:

12/05 11/05 10/05 9/05 8/05 7/05
Q 1006 1373 1373 1014 1364 1364
Q 4 1373 1373 5 1364 1364
Q 1373 1373 1373 1364 1364 1364

On the next page the value saved resets and the last row displays properly.

 

[lbass]

Please notice that in my first post I indicated that you have to use a separate variable for each particular row label. In the crosstab, you would not have multiple rows with the same row label. In your sample you are showing all with the row "Q"--crosstabs don't work like this, so what are the actual row labels?

-LB

 

[RWahlert]

There are other row values between these rows on the actual report. I was trying to give an example of what I am seeing for rows with the same value, i.e. Q. I have row values of Quarterly (Q), Monthly, 28 Days, and Bi-Weekly.

I am only concerned about row values of Quarterly. The other values will populate the cross tab correctly without additional formulas.

Example:

12/05 11/05 10/05 9/05 8/05 7/05
Q 1006 1373 1373 1014 1364 1364
M 1234 1237 1236 1239 1234 1235
Q 4 1373 1373 5 1364 1364
Q 1373 1373 1373 1364 1364 1364


Do I have to create a variable for each of the row values mentioned above?

 

[lbass]

You are missing the point. Please clarify what your crosstab row is, and what the values of that can be. The "rowfield" is the field you enter as the row in the crosstab expert. The only way a row value would repeat is if you have an outer row field. If you have more than one row field, please specify.

-LB

 

[RWahlert]

LB

Sometimes I need a hit in the head with a seldge hammer to see the light. Yes I do have other rowfields, three others in fact for a total of four. The inner most being the cycle. Do I have to create a variable for each rowfield? Is that what I am missing?

Thanks for your patience.

Ralph

 

[lbass]

Yes, exactly, you would have to create a separate variable for each specific instance of the row field, and with so many row fields, I think you would probably be better off using the solution for the manual crosstab.

-LB

 

[RWahlert]

Yes, I agree. Plan "B" is looking pretty good to me. I know what I have to do for this.

Thanks for all your input and patience.

Ralph