Reporting Services Configuration settings

Question

Post reply

Reporting Services Configuration settings

Paul Rawlings

Old Hand

Points: 311
More actions
November 20, 2007 at 2:46 am

#119482

Hi All,
Background Info:
Last week we had a problem with our SQL Server 2005. We did a reinstall. Some one then put the patches on incorrectly i.e. SP2 on before SP1, which means SP1 hasn't been insatlled. At the moment we're still waiting for a window when to take the server back down.
I need a bit of a pointer. I have a report that isn't working correctly.
One of the parameters is "Date"
This is the code....
SELECT RIGHT('00' + CAST(DatePart(day,[Date]) AS VARCHAR(2)),2) + ' ' + DateName(month,[Date]) + ' ' + CAST(YEAR([Date]) AS Varchar(4)) AS [WeekDate] from AllDate
WHERE
DatePart(WeekDay,[Date]) = 6 -- 6 = Friday
AND
DateDiff(Week,[Date],getdate()) BETWEEN 1 AND 52
ORDER BY
[Date] desc
(the table alldate is just a list of consecutive dates up to the end of 2010)
as you can see it produces every FRIDAY for a year e.g.
16 November 2007
09 November 2007
02 November 2007
26 October 2007
...
...
24 November 2007
Straight forward so far right?
ok, if i run the code in SQL 2005 it works as above, if i run it on my PC in visual studio it works as above.
When I deploy the report and put it in Reporting Services it seems to be set up to think that the number 6 is a SATURDAY rather than a FRIDAY. So in Reporting services it produces the result set...
17 November 2007
10 November 2007
03 November 2007
27 October 2007
...
...
25 November 2006
I think there must be a configuration setting in Reporting services somewhere where you have to set the days to a number.
When you set up RS, dose anyone know if there are any configuration settings like this?
Thank you for any help
Kind regards
Paul

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

I don't know about your date issue - that is weird.

As for the service pack - SQL Server service packs (and all MS products for that matter) are cumulative.

So, if you installed sp2, you do not need to install sp1.

Paul Rawlings Old Hand Points: 311 More actions · Answer 2

Thanks very much for the reply, it is much appreciated. I'm still racking my brains over what o do.

Can you think of any tests I can do.

So far I have created aa new data set copied written the SQL (tried and tested on SQL Server) and created a new table in the body of the report and put the value in there so the value is completly segregated. It was comming up with the same answer.

I have a now at least 5 reports that are acting strangly.

Another of my reports feed a graph, which data for every quarter from 2002. In RS it stops in 2006, where as in visual studio and sql server it it run up until q3 2007.

Getting desperate now, if anyone else has any more suggestions they would be greatly recieved.

DaveItz SSCrazy Points: 2146 More actions · Answer 3

Check the DATEFIRST variable of the server you deployed it to.

WHERE

DatePart(WeekDay,[Date]) = 6 -- 6 may not really be Friday

Be careful, something may depend on it being what it is; it might be set that way for a reason.

There are ways to make the logic work independently of @@DATEFIRST

For some examples of compensating for it, check out this post from a while back: Get Date & DateName Help - newbie questions.

It has been a while since I looked it over (and I don't have SQL installed on this computer to double check) but take a look at this code snippet, from the thread I linked, for an example of taking DATEFIRST out of the equation:

declare @startdate datetime, @dw_startdate int

select @startdate = getdate()

select @dw_startdate = ((datepart(dw, @startdate) + @@DATEFIRST -1)%7)+1

Paul Rawlings Old Hand Points: 311 More actions · Answer 4

Thanks for the article. It was very interesting.

I believe that the DATEFIRST varible is set correctly:

1) when i run the following code select datepart(weekday, getdate()), i get the expected answer. In todays case it's wednesday and i get 4.

2) when i copy and paste the SQL from RS into sql server and run it directly i get the correct result set.

so i'm quite certain it's not anything to do with the SQL code, also the code hasn't changed since the re-install.

Paul Rawlings Old Hand Points: 311 More actions · Answer 5

Hi All,

ok what i have now done is create a data set in reporting services and put the following code in it

select DATEPART(weekday,getdate())

then create a table in the body of the report and put the value in there. When I run it in Visual studio i get the correct value (today is wednesday so i get 4). I get the same result when i run it on the SQL Server.

When I deploy it into Reporting services i get the value 3. I have tried setting the datefirst to 1.

Anyone got any ideas?

DaveItz SSCrazy Points: 2146 More actions · Answer 6

As to why it's happening... no.

I would try the code I put above to nullify @@DATEFIRST

These all return the same value in the second field:

set datefirst 1