Formatting to the Currency

n_srikanth4
n_srikanth4 used Ask the Experts™
on
Hi Experts,

Requirement:
1.Format the decimal field "A_LMT" to the Currency Field  as shown below.
(example: $20,000.00)
2.Format the decimal field "R_INT_ACM" with  decimal Precision 2 followed by % like below
(example: 3.50%)

SQL Condition:

CASE
     when ID_SYS_MTCHD like 'ACM%' AND DS_TAG_ISS_FEE = 'Limit does not match' then A_LMT
     when ID_SYS_MTCHD like '%ACM' AND DS_TAG_ISS_FEE = 'Interest Rate Type does not match' then R_INT_ACM
   else 'N/A'
END as ACMValue

Please provide me the expression to achieve this in SSRS 2005.
Regards,

Sreekanth.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi

I have couple of questions

Did you want to do this in SQL or Reporting Side?
What are the data types in the database for the A_LMT and R_INT_ACM?
sample values for the ACMValue

If it is in Reporting Side how will you know that the value is from A_LMT or R_INT_ACM

Author

Commented:
Hi

1. Want to format on the Reporting Side.
2. A_LMT and R_INT_ACM are decimal data types
3. ACMValue  sample values are $20,000.00  for A_LMT and 23.59 % for R_INT_ACM

Regards,

Sreekanth.

Commented:
Thanks for input sorry for the delay.

Here the ACMValue will return 20000.00 or 23.59 or N/A based on the case statement.
How does the case containing both the String and Decimal values combination. Is your query is in working condition?

There will not be any indication that this value is from the A_LMT or R_INT_ACM

$ and % in the values will actually will not be there in the SQL side.

How will you know that the value 20000.00 is for the A_LMT or 23.59 is for the R_INT_ACM
 to format in the report?.
Commented:
Directly you can not achieve this format option.

Alternatively you can do some modification to the script and the Dataset values in the report.

CASE
     when ID_SYS_MTCHD like 'ACM%' AND DS_TAG_ISS_FEE = 'Limit does not match' then A_LMT
     when ID_SYS_MTCHD like '%ACM' AND DS_TAG_ISS_FEE = 'Interest Rate Type does not match' then R_INT_ACM
   else Null
END as ACMValue

I modified the Else Part from N/A to Null. This is because the combination of the string and decimal in same column is not allowed. To show N/A in the report we will have the expression in the value.

I made this using addition of dummy column. This is used to know where from the value came.

CASE
     when ID_SYS_MTCHD like 'ACM%' AND DS_TAG_ISS_FEE = 'Limit does not match' then 1
     when ID_SYS_MTCHD like '%ACM' AND DS_TAG_ISS_FEE = 'Interest Rate Type does not match' then 2  
     else 3
END as ACMValueFlag

This will tell me What format I have to take.

Have this expression for the ACMValue column value
=Switch(Fields!ACMValueFlag.Value = 1,Fields!ACMValue.Value,Fields!ACMValueFlag.Value = 2,Fields!ACMValue.Value/100,Fields!ACMValueFlag.Value = 3,"N/A")

I am dividing the ACMValue by 100 because percentage format is multiplying the value by 100.

Select the ACMValue and go to properties.

Under Format Expression Apply this Expression.
=Switch(Fields!ACMValueFlag.Value = 1,"'$'#,0.00;('$'#,0.00)",Fields!ACMValueFlag.Value =2 ,"#,0.00%",Fields!ACMValueFlag.Value=3,Nothing)

Here 1 is for the Currency, 2 is for the Percentage, 3 is for the N/A

Author

Commented:
good

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial