Formating Percentage on Report

[OasisUnknown]

Hello All,

I need some assistance.

I have a calculated field on a query that reads as follows.

ASGSL1%: Format(IIf([AUTHSL1]=0,[ASGSL1],IIf([ASGSL1]>0,[ASGSL1]/[AUTHSL1],"0")),"0%")

Note: I have also tried this following line

ASGSL1%: FormatPercent(IIf([AUTHSL1]=0,[ASGSL1],IIf([ASGSL1]>0,[ASGSL1]/[AUTHSL1],"0")),0)

The skipping over the theory of my iif statements end result of the field is a percentage that is displayed

examples would be
75%, 85%, 125%, 0%
depending on the variables.



The specific problem that I am having is getting those fields to conditionally format based on the percentage.

on my report I click on my field

ASGSL1% > go to format tab > conditional format

and I am doing a conditional format the same as if I would do it in excel.


So I have a statement as follows in the conditional format.

Field value is | Less than or equal to | .75

if the value check meets that criteria I am just trying to highlight the text red.

but its not actually highlighting anything. Sometime if I change things around to try a different conditional format it will highlight all the 0%'s but not any 25%



From my research it may be because when I use the Format() on my calculated field that is converting the number to a string and in my mind you cant check a string for a percent.

but I have no idea how to proceed forward.

can anyone see where I am going wrong in this process?

 

[apr pillai]

As you have rightly guessed the format() function will convert the value into string. Remove the Format function from the IIF() function and get the result in decimal format. Set the Query Column/Report Field Format Property with the "percent" format to display in percentage format.

For conditional formatting criteria you can use decimal value for comparison. Since the value remains as decimal internally, setting the format property of the field will not change the type of value of the field.

 

[OasisUnknown]

Thank you Apr Pillai,

you have solved my issue. I knew it was something simple but I just needed another perspective to solve it.