SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Percent Sysmbol not display
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 11/08/2013 :  00:54:20  Show Profile  Reply with Quote
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

Edited by - Villanuev on 11/08/2013 01:01:55

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/08/2013 :  01:02:59  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 11/08/2013 :  02:44:00  Show Profile  Reply with Quote
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.
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 11/08/2013 :  04:35:43  Show Profile  Reply with Quote
Got it and it's working.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/08/2013 :  04:44:39  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 11/11/2013 :  02:27:18  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/11/2013 :  02:46:32  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 11/11/2013 :  04:57:58  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/11/2013 :  06:29:12  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 11/11/2013 :  22:03:21  Show Profile  Reply with Quote
THanks Visakh for the info.
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 11/14/2013 :  04:53:12  Show Profile  Reply with Quote
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





Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/14/2013 :  05:32:52  Show Profile  Reply with Quote

;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

Assuming you want multiply over YIELD_PERCENT with rownumber denoted by Wrkctr

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 11/14/2013 :  07:16:48  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/14/2013 :  07:30:54  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 11/16/2013 :  07:17:16  Show Profile  Reply with Quote
Thanks Visakh. Try this when i return to work.

Edited by - Villanuev on 11/16/2013 07:17:58
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/16/2013 :  10:15:55  Show Profile  Reply with Quote
cool
let me know how you got on!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000