Calculated Column error

Question

Hello all

I have a report in which some of the columns have calculated dates based on parameters. ie: Lets say the parameter is 7 Nov - the report will then show the previous 4 weeks results in 4 columns. Each of those column headers have calculated dates : 17 Oct ; 24 Oct ; 31 Oct and 7 Nov. So far so good.

I tested the report in Visual Studio preview mode - all good.
I then tested the report in the dev web server -  all good.
I then deployed the reports to the report server and published the web pages that have a report viewer control connecting to the report (IIS): BAD

All the columns with calculated dates are now showing #ERROR

Any one have an ideas or suggestions?

Thanks

Answer 1

Hi:
Do you have other report in this production configuration?
If no, ie first then which SSRS 2005/2008, and what configuration, ... where is IIS in relation to SSRS and SSRS db's?
Phil

---

PHuhn

Answer 2

Hi:
Do you have other report in this production configuration?
If no, ie first then which SSRS 2005/2008, and what configuration, ... where is IIS in relation to SSRS and SSRS db's?
Phil

---

PHuhn

Yes, I have several reports and all the reports that use calculated colum headers are showing errors... so yes the error is consistent.

Im using SSRS 2005. I have web pages displaying the reports via report viewer controls and these web pages are deployed to my local IIS

Answer 3

Hi Geeee,

From your description, I assume the issue is caused by the format of date in the expression.

So, please check if the format of date is valid in the report server.
Or, please give us the expression.

Here is a sample for your reference:
=DateAdd("d", -7, CDate("7 Nov " + CStr(DatePart("yyyy",Today()))))

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 4

Hi Jin

Thanks for your reply.

As I mentioned , the date calculation is correct in the Report Preview, and the VS Developement Web server. It only shows an error when the Web Page is deployed to IIS.

Please see the date calculation :

=FormatDateTime(DATEADD("ww",-4,Parameters!ReportWeek.Value),2)

Thanks

Answer 5

Just a further note:

SSRS 2005 IIS are all installed on the same machine. I am running the project inside Microsoft Virtual PC 2007 

Answer 6

Hi G4*e:
What is the data type of Parameters!ReportWeek.Value.  You can set them in the parameter.  If string then change  to datetime or apply CDate(Parameters!ReportWeek.Value)
Phil

---

PHuhn

Answer 7

There is a database column called WEEKNO and it is a concatenation of the year and week for the date in that column = example 200932 being the 32nd week in 2009 : Its a integer data type.

The parameter value that is passed is a integer. 

Answer 8

Hi PHuhn

I think you have raised a very good point. Thinking about it now, how can that datatype be translated back to a date - I surprised now that it worked in the non IIS scenarios - I think what I need to do is translate the week no back into a date ( Like the first day of that week)

Any idea how I can do that?

Answer 9

Hi - apologies, I have just checked the ReportWeek parameter and it is made up of a MAX(date) value field and the WeekNo field 200932 as described above - so the parameter getting passed to the database is the 200932 integer LABEL value and the date calculation in the is using the VALUE field -

Because the reporting week starts on a Sat and ends the following Friday , I am taking the MAX (day) in the week - so the dataset that holds the values for the paameters would look like this:

14 August 2009 : 200932

So as far as see this is correct and should work!

Answer 10

Ive added a globalization section to the web config and this has fixed all the date errors