September 26, 2008 at 4:51 am
Hello
I have the following statement within my SQL script:
100.*(ISNULL(sum(case datepart(yy,t.orderdate) when 2008 then 1. else 0. end) * 1.0 /
NULLIF (sum(case datepart(yy,t.orderdate) when 2007 then 1. else 0. end)* 1.0, 0), 0)) -100. as '% Change 07-08'
When i run the query on SSRS i get the following output which is right #.#### (eg 5.6818), but when i depoly the report it and view the report it changes to 10568.18%!!!
Can anyone tell me what am doing that is wrong and how to correct it
September 26, 2008 at 6:57 am
Without seeing the report definition it is very difficult to determine the issue. If SQL Server is returning the data correctly and you have no calculations in the report then it should just display it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 26, 2008 at 7:14 am
Hello
How can i send you the report definition, for you to check?
September 26, 2008 at 7:26 am
You can zip it or rename the file to .txt and attach it to the forum.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 26, 2008 at 7:56 am
Hello
Here is the RDL of the report...hope it helps
September 26, 2008 at 8:06 am
I wish I had an answer for you. If you run the query in SSMS do you get the correct results?
Could you try changing your aliases to use "acceptable" column names like pct_change_07_08?
Realize I am just tossing out things I'd attempt at this point.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 26, 2008 at 8:13 am
When I run it in both SMS and the Query designer in BIDS, i get the right result set, but when i deply it, it appears in a different format.
September 29, 2008 at 3:19 am
Any answer to this question?
September 29, 2008 at 3:55 am
Finally found the answer, one will need to use the following in the as the format code:
##0.##,"%"
September 30, 2008 at 8:09 am
While you have a solution, you may not be aware, but to accurately display percentage values, you need to ensure that the raw number you compute is between 0 and 1, where a value of 1 is equivalent to 100%. SSRS will then handle a percentage display correctly. If you compute an actual value of 92.654, then SSRS would correctly display that as 92,654.00%. Thus all you might have needed to do is avoid the multiply by 100 at the beginning of your calculation.
The reason for this is that 90% of a given value is equal to that value multiplied by 0.9, and would NOT be equal to that value multiplied by 90. Keep this in mind with percentages, as you'll find that most applications that work with displaying percentages operate on this same basis, and that includes Excel and Access. What your solution does is just use formatting to overcome the oversized value.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 1, 2008 at 4:21 am
Hi there
I agree with you, I later used the following query:
(ISNULL(sum(case datepart(yy,t.orderdate) when 2008 then 1. else 0. end) * 1.0 /
NULLIF (sum(case datepart(yy,t.orderdate) when 2007 then 1. else 0. end)* 1.0, 0), 0)) *100. as '%
Change 07-08'
which worked out fine for me, thanks for your contribution though.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply