SSRS -How to get month name from integer value when connecting to IBM-DB2(AS400)

Question

I am using SSRS to create a dataset from a query having a column in AS400 with month number which i get based on a condition .i am able to get the month number . But when i try to get month name SSRS does not accept the query .However if i run the same query on AS400 the query successfully runs

To test my query i ran it in AS400 using MONTHNAME(MONTH) it runs successfully however SSRS does not accept the same query as correct.Below is my query.

SELECT DISTINCT SLMONTH, MONTHNAME(SLMONTH) AS Expr1
FROM            VEHICLE.VHTSALSUM
WHERE        (SLMGCD = ?) AND (SLMODLCD = ?) AND (SLMODLYR = ?) AND (SLYEAR = ?)

Answer 1

Instead of doing this in on the server, you could just return the month number to SSRS and use an expression to convert it to the month name there.

The expression would simply be

=MonthName(Fields!SLMONTH.Value)

This assumes AS/400 returns month numbers 1 - 12

Personally I think this is a better approach anyway as it means you have the month number to sort by. It is usually better to do your presentation in the report itself.

Edit based on feedback: To do this for use in a parameter..

1. Create a dataset that returns just your month numbers.
2. Right-Click the dataset name and go to properties
3. On the Fields Tab, add a new field called MonthName for example
4. Click the function button for the field source type the expression =MONTHNAME(Fields!MonthID.Value)
5. The month name will then be available directly in your dataset for use in your parameter