Alternating row colors in crosstabs 2

[MSBrady]

CR 11 Dev.
Crosstabs

Is there a way to alternate row colors in crosstabs so they are easier to read?

Thanks,
.bat man

 

[synapsevampire]

No, there isn't much control over a cross-tab, hence many will roll their own with what's termed a manual cross-tab.

This means emulating a cross-tab using groups, fields, formulas, grid lines, etc.

-k

 

[MSBrady]

Unfortunately that's not an option as this report is repoting on live data that updates every 30 minutes.

What about something in the Formula Editor? I'm not a programmer as such so I'm not familiar with the syntax I've read in other posts and off of Google.

Any ideas?
TAM
.bat man

 

[lbass]

You can do alternate rows of color in an inserted crosstab by doing the following.

1) If your row label happens to be a consecutive number:
First select a cell while in design or preview mode->right click->change border->background->x+2 and enter:

if remainder(GridRowColumnValue ("table.rowfield"),2) = 0 then crYellow else nocolor

...where you place your row field within the quotes. If it is a database field or formula, remove the curly brackets, so that the row field looks like "table.rowfield" or the formula looks like "@rowfield", substituting your actual row field name, of course. Repeat this formula for the row total column.

Then select the row label->right click->change border->background->x+2 and enter:

if remainder(val(CurrentFieldValue),2)= 0 then crYellow else crNocolor

2) If your row label isn't a number and you don't have many rows, you could create a formula to add a leading number to your row label, as in the following example, where the {Orders.Ship Via} was the original row field:

//{@shipper}:
select {Orders.Ship Via}
case "FedEx" : "1-"+"FedEx"
case "Loomis" : "2-"+"Loomis"
case "Parcel Post" : "3-"+"Parcel Post"
case "Pickup" : "4-" + "Pickup"
case "Purolator" : "5-"+"Purolator"
case "UPS" : "6-"+"UPS"
default : "0"

Then you would change the cell and the row total column conditional color formula to:

if remainder(val(GridRowColumnValue ("@shipper")),2) = 0 then crYellow else nocolor

The row label conditional color formula would be:

if remainder(val(CurrentFieldValue),2)= 0 then crYellow else crWhite

If you have too many row fields to hard code the leading number, you can also use a variable. First you need to either group on or sort your main report by your row field value, even if you are not displaying anything other than the crosstab in the main report. Let's assume that shipper is your row field again. Sort by {Orders.Ship Via} and then create a formula {@shipper}:

whilereadingrecords;
numbervar cnt;
stringvar shipper;
stringvar rowlabel;

if instr(shipper,{Orders.Ship Via}) = 0 then
(shipper := shipper + {Orders.Ship Via};
cnt := cnt + 1);
rowlabel := totext(cnt,0,"") + "-" + {Orders.Ship Via};
rowlabel

Use {@shipper} as your row field in the crosstab. Then you can format your cells, your total column, and your row labels using the earlier mentioned formulas.

-LB

 

[MSBrady]

WOW! Thanks a million! I am getting so close.
Just a couple issues-
First, I had to go with your 3rd suggestion b/c there are too many rows to hard code. After I implement your code replacing your hypotheticals with my actuals and insert it into the Row Field and run it I get the following: "A print time formula that modifies variables is used in a chart or map Detailsshipper" Of course @shipper is a representation of my actual formula.
Secondly, when I place your first formula into the border background X-1 area it highlights GridRowColumnValue and displays the error message "This function can only be used in field formatting formulas." Which seems rediculous b/c that is where it is being used.

Thanks again!

 

[lbass]

Are you sure you used "WhileReadingRecords" instead of "whileprintingrecords"?

Which formula are you referring to when you say my first formula? Please copy it into your post and also identify what column you are using this on. It would also help if you provide your version of my row formula.

-LB

 

[MSBrady]

Yep you were right! I was using whileprintingrecords. changed that and no more error.

In regards to the second error, I was placing your
if remainder(GridRowColumnValue ("table.rowfield"),2) = 0 then crYellow else nocolor
formula in the crosstab border area rather than the cell border area. Fixed that, and now that I'm placing my
if remainder(GridRowColumnValue ("@CPTDesc"),2) = 0 then crGreen else nocolor
formula in the cell border area it returns
"A number, or currency amount is required here.

 

[lbass]

It should be:

if remainder(val(GridRowColumnValue("@CPTDesc")),2) = 0 then crGreen else nocolor

Please also provide the contents of your formula {@CPTDesc} if you have any continuing problems.

-LB

 

[synapsevampire]

Nice *LB*!

-k

 

[MSBrady]

LB, you are the bomb! Thank you so much for your time.
One remaining issue. My crosstab has {Charges}, {Receipts}, percentage fields and some other formula calculations. I need the report sorted based on Descending {Charges}, so that they can see which product is making them the most money. That's done in Group Sorting no problem. Now, is there a way to alter your count formula to account for changing {Charges}? One month the {CPTDesc} field may be {product1} with {Charges} of $50,000 the next it may be {product2} with {Charges} of $55,000. Once the data are refreshed the coloring scheme changes and no longer alternates.

 

[MSBrady]

@CPTDesc:

//formula used to alternate row colors in crosstab

whilereadingrecords;
numbervar cnt;
stringvar CPTDesc;
stringvar rowlabel;

if instr(CPTDesc,{@frm_CPTDesc}) = 0 then
(CPTDesc := CPTDesc + {@frm_CPTDesc};
cnt:=cnt+1);
rowlabel:=totext(cnt,0,"") + "-" + {@frm_CPTDesc};
rowlabel

 

[lbass]

I'm not sure I understand. Are you applying the topN/group sort to the crosstab itself? Or to the main report?

And what is the content of {@frm_CPTDesc}?

-LB

 

[MSBrady]

Yes I use the TopN/Group to filter to top 80% of products. @frm_CPTDesc is a simple concatination of the product name (Description)and the associated product number (CPT).
{ActivityReport.Description} & " " & CHR(10) & "CPT" & " " & {ActivityReport.CPT}

 

[lbass]

You didn't really answer my questions about to what you were applying the topN/group sort.

-LB

 

[MSBrady]

it is applied to {charges} so that it sorts the most profitable products first. it constrains the Row Field and tells it to supply the {@frm_CPTDesc} with the highest charges first in the crosstab. {@frm_CPTDesc} is used so that I don't have to enter {ActivityReport.Description} and {ActivityReport.CPT} seperately. it's just cleaner that way.

 

[lbass]

Just to clarify, my question was whether the group sort was applied to the crosstab itself or the main report. You seem to be saying it is applied to the crosstab. Unfortunately, the above method will not work if topN/group sort is applied to the crosstab, since the count formula necessary executes whilereadingrecords, i.e., BEFORE the group sort. The numbers would thus be out of order and would not be useful for the alternate coloring formula.

If the group sort had been only on the main report, I would have suggested creating the crosstab in a subreport where topN was not applied to the subreport group sections. But I don't have a solution in your case. If I think of something, I'll let you know. Sorry.

-LB

 

[MSBrady]

My apologies for not being clear.
Yes the topn sort is happening on the crosstab itself. The crosstab is the only object in the report (except the group which is suppressed.)
It wouldn't be much trouble to make it a subreport, however this particular report is one of 12 crosstabs inserting into the Main Report. Because of the complexity of the data the clients wish to see and the nature of the report it was necessary to create several different SQL views and then several crosstabs based on those SQL views, and finally insert them as sub in the Main Report. So I would have to apply you logic at the top most Main Report level and then insert this crosstab into it.
I'm pretty sure there are better ways to accomplish the end result, however being so green in programming and never having to get to this extreme in Crystal Reporting and the fact that I'm up against a deadline, I have just get it to work and then later make it more efficient.
You have aided me very much and I have notified others about this post so too can benefit from it.
If you have the time and inclination please let me know if you have come up with a solution to my other main quandary under the Thread heading "one report, choose from multiple data sources.” I know it’s going to take a form or something on the front end as Crystal doesn’t do this on its own (so says BO Tech Support.)
If I could I would pay you for your help.
Cheers,

.bat man

 

[MSBrady]

LB,

When I remove all sorting and the CPTDesc goes back to the sequential numbering scheme you so ingeniously came up with, it's now highlighting every other row and occassionally skipping 1 or 2 sequentially, or coloring 1 or 2.
ProductCPT Charges Receipts %
ProductName $50,000 $30,500 40 <---(Lime Green)
ProductName $30,000 $20,500 80 <---(nocolor)
ProductName $50,000 $28,000 60 <---(Lime Green)
ProductName $50,000 $25,000 50 <---(no color)
ProductName $100,000 $30,500 30 <---(Lime Green)
ProductName $50,000 $30,500 90 <---(Lime Green)ProductName $50,000 $30,500 40 <---(nocolor)ProductName $50,000 $30,500 40 <---(Lime Green)ProductName $50,000 $30,500 40 <---(nocolor)ProductName $50,000 $30,500 40 <---(Lime Green)ProductName $50,000 $30,500 40 <---(nocolor)ProductName $50,000 $30,500 40 <---(nocolor)ProductName $50,000 $30,500 40 <---(Lime Green)

 

[lbass]

With further testing, this method seems to work when using only one simple row field, not with concatenated fields. Sorry.

-LB

 

[MSBrady]

It's still a very nice piece of work and will be usefull in the future.


"Try not; Do, or do not. There is no try"
-Yoda