Formula Help 1

[northw]

Hi Guru's,
I need your help in writing a formula.
I have attached the screen shot, where one of the report is using Highlighting expert to Bold part of the values from the database field, and the requirement has changed, Where I want this highlighting to work for certain locations, So I can't write a formula in highlighting expert to work accordingly(i.e. If user selects Location abc then bold else regular), Which I thing can't be achieved using Highlighting expert.
Is there any work around for this issue or Do I have to write a formula If so, could some one help me with the formula?
Thanks in Advance.

 

[lbass]

Right click on the field->format field->border->color->background->x+2 and enter a formula using a format like this:

if {table.location} in ["ABC","CDE"] and
{table.value} in 1 to 5 then
crYellow else
crNocolor

-LB

 

[northw]

Hi,
Thanks for the reply, But it is returning an error.
(String is required here)

 

[fisheromacse]

can you verify that your {table.value} field in the database is numeric, and not a string?

 

[northw]

Its a universe object(Dimension), Character.

Thanks

 

[northw]

I have written this formula, I am not sure whats wrong with but this one is not working, I don't see any syntactical error.

If {Query1.Location} = "ABC"
and {Query1.Display String} in ['11.0-12.0', '200-500', '501-800', '>=20%']
Then crbold
else If {Query1.Location} <> "ABC" and {Query1.Display String} in ['10-10.9', '11.0-12.0', '200-500', '501-800', '>=20%']
then crbold
else crregular

 

[lbass]

Please show a sample of how {Query1.Display String} displays and then also explain the conditions under which a result should be bold.

-LB

 

[northw]

I am sorry, I think I have missed some thing here.

Actually its a crosstab report. Where the columns are
{Query1.Display String} in ['10-10.9', '11.0-12.0', '200-500', '501-800', '>=20%'] and the row data is SUM of person who met the goal({query1.people met goal}), In when a user selects {Query1.Location} = "ABC" then the row values of {Query1.Display String} in ['11.0-12.0', '200-500', '501-800', '>=20%'] should be highlighted, else if user selects any other location then these ROW values have to be Bold {Query1.Display String} in ['10-10.9', '11.0-12.0', '200-500', '501-800', '>=20%'],
They are using highlighting expert and highlighting the row data as shown in the screen shot.

So the column values are {Query1.Display String} in ['10-10.9', '11.0-12.0', '200-500', '501-800', '>=20%']
and row data is SUM of person who met the goal({query1.people met goal}).

Thanks alot.

 

[lbass]

Not mentioning the crosstab is a pretty big omission, since formatting crosstabs require specialty functions.

In the crosstab, what field is entered as a row--location? I'm assuming that you mean you are using a formula for the column field that breaks out values into ranges.

Also, in describing the highlighting, you seem to be omitting the range 10-10.9. Do you really mean this? By highlighting, you mean you want some background color, correct? Or do you just want the bold on or off. It sounds like you want the highlighting on and bold off if the value = "ABC"; otherwise, bold on, highlighting off. Is that correct?

-LB

 

[northw]

Hi,
Oh, I am sorry,
There is no formula used, in the row or column field.
{Query1.Display String} is a Database field('10-10.9', '11.0-12.0', '200-500', '501-800', '>=20%', etc) and Row values is {Query1.SubLocation} and the summary field is {query1.people met goal} (They have summarized this value(Sum)), You are right(They are using an Java app, where the user selects LOcstion in the drop down, and all the related sublocations will the displayed, If the location is 'ABC' then certain values should be bold, and for all other locations different set of values should be bold)
If the location = "ABC" then it should bold these values('11.0-12.0', '200-500', '501-800', '>=20%'), for all other locations it should bold these values('10-10.9', '11.0-12.0', '200-500', '501-800', '>=20%'), There is no coloring used here. The screen shot attached above shows that they are using highlighting expert to just bold the fields, With out this Locations based Bold, it was same for all the location, so I think they were using Highlight expert.

Thanks.

 

[lbass]

Then select an inner cell->right click->format field->font->style->x+2 and enter:

if
(
gridrowcolumnvalue("Query1.SubLocation") = 'ABC' and
gridrowcolumnvalue("Query1.Display String") in ['11.0-12.0', '200-500', '501-800', '>=20%']
) or
(
gridrowcolumnvalue("Query1.SubLocation") <> 'ABC' and
gridrowcolumnvalue("Query1.Display String") in ['10-10.9','11.0-12.0', '200-500', '501-800', '>=20%']
) then
crBold else
crRegular

-LB

 

[northw]

Thanks alot for your efforts.
The formula doesn't work.

 

[lbass]

Please explain what happens when you use this formula. Did you get an error message? Were the incorrect cells bolded? When a suggestion doesn't work, you should explain your results and how they differ from what you would like to achieve.

-LB

 

[lbass]

PS. I tested this and it worked fine. I think you should copy your final formula into the thread. A

Also note that the field names within the quotes in gridrowcolumnvalue must be the fields used for the column or row in the crosstab, and these should correspond to the values shown in the customize style tab->alias for formulas when you highlight the row or column. You basically take the field and replace the curly brackets with double quotes.

-LB

 

[northw]

Hi,
I forgot to post it.
Here is the formula, which I have made some modifications.
And Please find the attached screen shot of cross tab.

if
(
{Query1.Corporate Acronym} = 'DMG' and
gridrowcolumnvalue("Query1.Display String") in ['11.0-12.0', '200-500', '501-800', '>=20%']
)Then Crbold
Else If
(
{Query1.Corporate Acronym} <> 'DMG' and
gridrowcolumnvalue("Query1.Display String") in ['10-10.9','11.0-12.0', '200-500', '>=20%']
) then
crBold else
crRegular

The Level2 value is something like sublocation.

Thanks.

 

[lbass]

You can't reference {Query1.Corporate Acronym} as it is not a field in the crosstab, and you have to use the gridrowcolumnvalue function. You should be referencing {Query1.Level2 Value} as in:

if
(
gridrowcolumnvalue("Query1.Level2 Value") = 'DMG' and
gridrowcolumnvalue("Query1.Display String") in ['11.0-12.0', '200-500', '501-800', '>=20%']
) or
(
gridrowcolumnvalue("Query1.Level2 Value") <> 'DMG' and
gridrowcolumnvalue("Query1.Display String") in ['10-10.9','11.0-12.0', '200-500', '501-800', '>=20%']
) then
crBold else
crRegular

If {Query1.Level2 Value} does not result in a value 'DMG', then you should plug in the value it does return that corresponds to 'DMG.'

-LB

 

[northw]

Thanks alot LB,
I have to make some changes for the level three values.
I have tested the formula and it works fine.

thanks