August 31, 2016 at 8:53 am
Hi, I have a SQL Server scalar-function that determines the number of working days between two dates. It works as it is supposed and has been in use for years
This function is included in a procedure that provides the data-set for a report created and published using Report Manager 3 (SQL Server 2008 R2 SP1). The column is simply the number of working days
One text-box on that report exposes the value derived from the number of working days column. The dates passed to the function are part of the query, although the report has date parameters these have no bearing on the function
Testing in SQL Server returns the correct values from this function. Testing using the Query Designer in Report Builder returns the correct values from this function
However the text-box on the report face does not return the correct value. In some cases in adds a day in others it reduces the value by a day. There's no reason I can find for this; has anyone come across this behaviour?
I have re-written the stored procedure as a table-value function. Same result. I edited the procedure to write the result-set to a temporary table and then return the result-set as a select from this temporary table. All produced the same result as the original procedure
All the databases use the same language; us_english and the report language property is set to en-GB
Any help or ideas on how to solve this conundrum will be appreciated
August 31, 2016 at 9:00 am
This almost sounds like possibly a rounding issue. What sort of value is your sp returning? If it's a decimal, is the format SSRS no set to display decimals, and so is rounding depending on the value?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 31, 2016 at 9:10 am
Thom hi thanks for the quick reply
Following your thoughts I changed the text-box properties to decimal to two pleaces, each and every value returned a whole number
I cast the column that calls the function as an int (although I know the function returns integer values) and altered the procedure. No change, still odd values in the text-box
I created two further data-sets; both call the function and pass it two dates
In both cases in Query Designer the right result is returned but on the report face one value is over stated by a day and the other understated by a day
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply