Cross-tab field not dynamically formatting on current data set row

[edoherty]

I've got a cross-tab containing retail pricing by retailer (columns) and commodity (rows). In the data set, each row for a retailer and commodity also has flag to denote that price as an "ad" price or not. I have a simple IF statement checking that value to change the background color of the field, but the value of the 1st row in the data set is used for the entire cross-tab.

I've found references that say the CurrentFieldValue function as necessary for formatting cross-tab fields but because I need to base the background color on a different value that want's displayed I can't figure out how to make it work for me.

Any tips??

 

[edoherty]

It looks like it's not doable the way I was hoping too:

http://support.businessobjects.com/library/kbase/articles/c2011156.asp

Anybody have any tricks to get around this? I'm going to see if I can provide the ad pricing as negative #'s from the stored procedure but display as positive #'s and use the negative as the flag to drive the background color.

 

[lbass]

I think it can be done. Try the following, which assumes that the flag is unique to the cell. First, add the flag as your topmost summary value. Then add the price field, if that is your current summary, as your second summary.

Select the flag field->format field->common->suppress->x+2 and enter:

whileprintingrecords;
numbervar x := currentfieldvalue;
false

Then select the price field->format field->border->color->background->x+2 and enter:

whileprintingrecords;
numbervar x;
if x = 1 then //change to your flag value
crYellow else crNoColor

I tested this, and it works. I guess Business Objects doesn't know the power of its own program.

-LB

 

[edoherty]

That's beautifully done. Although now the new summary field, even though "suppressed" is requiring additional row heigth and page real estate is very important for this report. Any tips for hiding the value all together in the cross tab?

 

[lbass]

You should be able to suppress the cell, totals, and label the usual way. Then drag the borders until the field is of minimum height. This won't totally eliminate the row though. You could also uncheck "show cell margins" in the customize style tab to eliminate more white space.

-LB

 

[edoherty]

thx for the info. and prompt responce! I've already unchecked "show cell margins". I couldn't drag the lower border up to reduce the size of the field much. I could reduce the field height using the Object Size and Position screen but only to .05 and it still pushes 3 rows on the full report to the next page which is a big deal unfortunately to the users and customers who will receive the report. Currently it's manually created so they can tweak to alawys make it on one page and this new .05 over the 30 or so rows pushes a few over. It would be so nice if I could reduce the field height completely!

 

[lbass]

I was able to get the field down to .04 in height. You could also format the summaries (in XI) to show horizontally (customize style tab->show summaries->horizontally), and then narrow the column--this might work better.

-LB