0

SSRS 2008 R2: I have created a report(.rdl) using Shared datasource. I am following the tabular approach to generate the report. I need an extra column in my report which uses the excel formula to calculate the date based on two existing columns.

The formula which I am using for the new column MMM/YYYY is =DATE(A2, B2, 1)

Expected Report

I have tried creating the expression for the new column, but it is not purely giving me the desired output.

RahulGo8u
  • 148
  • 2
  • 19
  • Can you achieve this output by changing the query of the data source? – Pelin Mar 05 '18 at 08:28
  • Yes, I am querying from the datasource for now. But here we require the dynamic column whose value can be changed by applying the formula to it. I am trying to create the expression based column but not able to achieve the desired result. – RahulGo8u Mar 05 '18 at 08:38
  • You can try cDate() function to convert string to datetimeobject, I hope it helps =Format(CDate(Fields!A.Value), "MMMM yyyy") – Pelin Mar 05 '18 at 08:46
  • It is not working, I guess it will work for the column that already has date value as a string. But here I have Month and Year in two columns. How can I use cDate() function here to append these two column values and then convert it into datetime. – RahulGo8u Mar 05 '18 at 09:30
  • 1
    Before using cDate function can please you try to concat month and year? CDate(Fields!A.Value& "-"Fields!B.Value& "-"Fields!C.Value) – Pelin Mar 05 '18 at 09:42
  • @Pelin Yes, I have tried this way and managed to get the desired output. **=Format(CDate(Fields!Year_Name.Value & "-" & Fields!Month_Of_Year.Value & "-" & "1"), "dd-MM-yyyy")**. Thanks for your time and quick response. – RahulGo8u Mar 05 '18 at 10:09

1 Answers1

0

The equivalent expression in SSRS to =DATE(A2,B2,1) would be

=DateSerial(Fields!Year.Value,Fields!Month.Value,1)

Then format the relevant textbox using the same format code you would use in excel.

RET
  • 861
  • 6
  • 15