Percent Sysmbol not display - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Percent Sysmbol not display

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-08 : 00:54:20

Hi Guys,

I have a fields with values 80, 83, 99.33.
im my report, the output is incorrect. it shows 8000%, 9933%
even tried this in custom ##%, format(fields,"P") but does not work. It should be 80%, 83% and 99.33%.
Your help is very much appreciated. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-08 : 01:02:59

if you're using standard % format you can just bring it as decimal
ie .8 for 80 % no need to multiply 100 in expression

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-08 : 02:44:00

Actually this is the result of my query, When transfer to SSRS
the percentage value does not correct.

This is my query for percentage..
Select
ROUND(NULLIF(CAST(PROD_FG_TOTAL_FAIL AS DECIMAL(16,6)),0)/ CAST(PROD_FGSCRAP_TOTAL_FAIL AS DECIMAL(16,6))*100,2) AS ACTUAL_PROD_YIELD
(ACTUAL_PROD_YIELD-PROD_YIELD_PLAN) AS PROD_VARIANCE
From Temp

ANother query, if ok with you if i will use this instead of making a new one.
need to get the average but not successful.

WrkCtr--Total Yield
A-98.39
B-97.86
C-100.00
D-100.00
E-98.82
F-97.21
G-100.00
H-95.45
I-97.76
------
98.39 --expected result.

THanks.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-08 : 04:35:43

Got it and it's working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-08 : 04:44:39

quote:
Originally posted by Villanuev

Got it and it's working.

you just have to remove * 100 from percentage calculation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-11 : 02:27:18

Hi Visakh,

Need your advice if this is statement is reliable to get the average. When i tried the other statement (old) getting the average i'm not successful. Thanks.


Create table #sample
(Wrkctr varchar(1), Total Int, Passqty int)
Go
Insert into #sample( wrkctr, total,Passqty) values('A',249,245) 
Insert into #sample( wrkctr, total,Passqty) values('B',234,229) 
Insert into #sample( wrkctr, total,Passqty) values('C',252,252) 
Insert into #sample( wrkctr, total,Passqty) values('D',271,271)
Insert into #sample( wrkctr, total,Passqty) values('E',255,252)
Insert into #sample( wrkctr, total,Passqty) values('F',251,244)
Insert into #sample( wrkctr, total,Passqty) values('G',14,14)
Insert into #sample( wrkctr, total,Passqty) values('H',264,252)
Insert into #sample( wrkctr, total,Passqty) values('I',268,262)
Insert into #sample( wrkctr, total,Passqty) values('J',100,98)
Insert into #sample( wrkctr, total,Passqty) values('K',150,140)
Insert into #sample( wrkctr, total,Passqty) values('L',35,30)

select * from #sample


--this is the revised statement
;With CTE AS
(
SELECT
     Wrkctr,
     ROUND(CAST(PASSQTY AS DECIMAL(16,6))/ CAST(TOTAL AS DECIMAL(16,6))*100,2) AS YIELD_PERCENT
FROM #sample
)
SELECT
     SUM(CASE WHEN Wrkctr IN ('A','B','C','D','E','F','G','F','I') THEN YIELD_PERCENT ELSE NULL END)/
     COUNT(CASE WHEN Wrkctr IN ('A','B','C','D','E','F','G','F','I') THEN YIELD_PERCENT ELSE NULL END) AS ACTUAL_RTY_YIELD
FROM CTE



--old, the result display in not correct.

;With CTE AS
(
SELECT
     Wrkctr,
     ROUND(CAST(PASSQTY AS DECIMAL(16,6))/ CAST(TOTAL AS DECIMAL(16,6))*100,2) AS YIELD_PERCENT
FROM #sample
)
SELECT
     AVG(CASE WHEN Wrkctr IN ('A','B','C','D','E','F','G','F','I') THEN YIELD_PERCENT ELSE NULL END) AS YIELD
FROM CTE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 02:46:32

for me both of them are giving same result


ACTUAL_RTY_YIELD
-------------------------
98.755000000000000000

YIELD
-------------------------
98.755000000000000000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-11 : 04:57:58

I'll try again these statemnet.
Btw, is there a limitation of CTE's, i mean how many should CTE's use in a select statement.
right now i'm using 6 CTE's but compiling in SSRS the dataset doest not change the fields from the last CTES i declared.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 06:29:12

quote:
Originally posted by Villanuev

I'll try again these statemnet.
Btw, is there a limitation of CTE's, i mean how many should CTE's use in a select statement.
right now i'm using 6 CTE's but compiling in SSRS the dataset doest not change the fields from the last CTES i declared.

sorry didnt understand your problem here
the number of ctes doesnt affect resultset
however if your code returns multiple resultset ssrs will only take first resulset whether or not you used CTE. thats the default behaviour of SSRS dataset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-11 : 22:03:21

THanks Visakh for the info.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-14 : 04:53:12

Hi Visakh,

What is the equivalent in of this formula in Excel to SQL.

=Product(E21;E31)

[Code]

;With CTE AS
(
SELECT
Wrkctr,
ROUND(CAST(PASSQTY AS DECIMAL(16,6))/ CAST(TOTAL AS DECIMAL(16,6))*100,2) AS YIELD_PERCENT
FROM #sample
)
SELECT

-- i will place it here the SQL command ( In excel =Product(E21;E31]

FROM CTE[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 05:32:52

[code]
;With CTE AS
(
SELECT
Wrkctr,
ROUND(CAST(PASSQTY AS DECIMAL(16,6))/ CAST(TOTAL AS DECIMAL(16,6))*100,2) AS YIELD_PERCENT
FROM #sample
)
SELECT

EXP(SUM(LOG(CASE WHEN Wrkctr BETWEEN 21 AND 31 THEN YIELD_PERCENT END)))
FROM CTE
[/code]
Assuming you want multiply over YIELD_PERCENT with rownumber denoted by Wrkctr

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-14 : 07:16:48

Thanks for the reply.

what if this is the result of the CTE.
There's a different wrkctr. what if ineed only EOL up to CCL in the calculation. How to do this base on your sample. thanks.

WrkCtr--Total Yield
EOL-98.39
Data-B-97.86
Data-R-100.00
FGL-100.00
RMA-98.82
CCL-97.21
G-100.00
H-95.45
I-97.76

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 07:30:54

quote:
Originally posted by Villanuev

Thanks for the reply.

what if this is the result of the CTE.
There's a different wrkctr. what if ineed only EOL up to CCL in the calculation. How to do this base on your sample. thanks.

WrkCtr--Total Yield
EOL-98.39
Data-B-97.86
Data-R-100.00
FGL-100.00
RMA-98.82
CCL-97.21
G-100.00
H-95.45
I-97.76

if you've an associated id field its easy. otherwise you need to add an explicit condition like below as your WrkCtr values are not in any definite order.


;With CTE AS
(
SELECT
     Wrkctr,
     ROUND(CAST(PASSQTY AS DECIMAL(16,6))/ CAST(TOTAL AS DECIMAL(16,6))*100,2) AS YIELD_PERCENT 
FROM #sample
)
SELECT

  EXP(SUM(LOG(CASE WHEN Wrkctr  in ('EOL','Data','FGL','RMA','CCL') THEN YIELD_PERCENT END)))
FROM CTE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-11-16 : 07:17:16

Thanks Visakh. Try this when i return to work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-16 : 10:15:55

cool
let me know how you got on!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources