ReportServer Forum

marcosfilho

Member

Registered: 2014-08-04

format data

Hello guys,

I have a dynamic list report and would like to some data formatting before genereting the report.

example:

my query is:

select name, gender from person

imagine that my database can return the following values for gender: 'M', 'F'

I'd like reportserver to show this information differently .. like 0 for 'M' and 1 for 'F'. Also I dont want to form this using sql conditions because i'll have many cases and it will be hard to maintain.

Reportserver can do some default formatting like Date, Currency, Percentage, Text and etc. But I dont see how to format the data the way I need(with iReport I could create a script for that field).

I would appreciate very much any help.

Regards,
Marcos

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: format data

Hi Marcos,

there are two ways you could handle this with ReportServer. The first is to go for computed fields and create a field as

CASE WHEN 'M' = gender THEN 0 ELSE 1 END

But then again you are back to writing SQL and having an extra field for each adapted attribute. The second and probably preferred option is to use the template formatting.
Besides Date, Currency, etc. ReportServer supports a format called "Template". This allows you to specify an expression that is used to compute the displayed value. Here
you can use the ReportServer Expression language where 'value' is the value coming from the database. So the following expression

${'M'.equals(value) ? 0 : 1}

checks whether the value is equal to 'M' and if so returns 0 and else returns 1.

Regards,
-Arno

marcosfilho

Member

Registered: 2014-08-04

Re: format data

Thanks, Arno.

Works beautifuly!