Calculation % of columns in SQL Select Query

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

Transact-SQL (2008)

Calculation % of columns in SQL Select Query

Author

Topic

geethachetan
Starting Member

5 Posts

Posted - 2013-03-27 : 12:08:43

Hi,

am trying to build a report in SSRS and hence create a dataset for which i need to write a SQL Query to get the below output:

Sample data in the SQL Table (ProductDetails)

service request number Prod_desc fweek
873265935 Imaging 201406
873277276 Other Electronics 201405
873263225 Other Electronics 201403
873254864 Other Electronics 201406
873279246 Personal Desktops 201406
873278830 Personal Desktops 201406
873276953 Personal Desktops 201405
873265803 Personal Desktops 201404
872909823 Personal Desktops 201404
873280336 Personal Desktops 201404
873256076 Personal Desktops 201403
873253341 Personal Desktops 201402
873265308 Personal Desktops 201402
873259843 Personal Desktops 201402
873253448 Personal Desktops 201402
873274740 Personal Desktops 201401
873267614 Personal Notebooks 201405
873278679 Personal Notebooks 201406
873260294 Personal Notebooks 201404
873275117 Personal Notebooks 201404
873266262 Personal Notebooks 201404
873268197 Personal Notebooks 201403
873268197 Personal Notebooks 201403
873278883 Personal Notebooks 201403
873033875 Personal Notebooks 201402
873264483 Personal Notebooks 201401
873278028 Personal Notebooks 201401
873264834 Tablets 201406
873262506 XPS Desktops 201406
873265030 XPS Desktops 201406
872906708 XPS Desktops 201405
873279606 XPS Desktops 201404
873265908 XPS Desktops 201404
873270295 XPS Desktops 201404
873276334 XPS Desktops 201403
873267379 XPS Desktops 201401
873266191 XPS Desktops 201402
873265054 XPS Notebooks 201406
873253635 XPS Notebooks 201406
872905885 XPS Notebooks 201406
873254630 XPS Notebooks 201406
873274694 XPS Notebooks 201405
873264466 XPS Notebooks 201404
873250450 XPS Notebooks 201404
873278428 XPS Notebooks 201404
873263921 XPS Notebooks 201403
873259737 XPS Notebooks 201401
872530267 XPS Notebooks 201402
873263013 XPS Notebooks 201404
873274701 XPS Notebooks 201403
873267854 XPS Notebooks 201401
873259828 XPS Notebooks 201406
873275680 XPS Notebooks 201405

When i build an excel pivot on this data, the output that i view is like below. I need to build a sql query as the data that am referencing is of some million rows: (In the value field settings - i have chosen - Count & show as % of column in the pivot table settings)

Count of service request number Column Labels
Row Labels 201401 201402 201403 201404 201405 201406 Grand Total
XPS Notebooks 33.33% 14.29% 25.00% 30.77% 33.33% 38.46% 30.19%
Personal Desktops 16.67% 57.14% 12.50% 23.08% 16.67% 15.38% 22.64%
Personal Notebooks 33.33% 14.29% 37.50% 23.08% 16.67% 7.69% 20.75%
XPS Desktops 16.67% 14.29% 12.50% 23.08% 16.67% 15.38% 16.98%
Other Electronics 0.00% 0.00% 12.50% 0.00% 16.67% 7.69% 5.66%
Imaging 0.00% 0.00% 0.00% 0.00% 0.00% 7.69% 1.89%
Tablets 0.00% 0.00% 0.00% 0.00% 0.00% 7.69% 1.89%
Grand Total 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00%

How do i get similar output on my sql table without having to go to Excel...please help..

Geetha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-28 : 05:15:08

use matrix container in SSRS for above data. use row group as Prod_desc and column group as fweek . In data portion give expression like Count(Fields!service_request_number.value)/Count(Fields!service_request_number.value,"DatasetName") and give format code as P0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

geethachetan
Starting Member

5 Posts

Posted - 2013-03-29 : 08:08:44

Hi,

Thanks a ton for your solution. i did everything as specified below..however there is a error that i get as below:

"An error occured. the Value expression for the text box 'Texbox3'
has a scope parameter that is not valid for an aggregate function.
the scope parameter must be set to a string constanct that is equal
to either the name of a containing group, the name of a containing
data region, or the name of a dataset.."

any work around for this?

in the mean time i tried to write a sql pivot query to get this output but still figuring out how to get the % & not just the count..

SELECT * FROM
( SELECT FWEEK, PROD_DESC, [SERVICE REQUEST NUMBER] FROM [dbo].[GM_Logs_Wk05] where [Act_RevLOB] = 'S3R3') AS A
PIVOT
(
COUNT([SERVICE REQUEST NUMBER]) FOR [Prod_desc] in
([Personal Notebooks], [Personal Desktops],
[XPS Desktops],
[XPS Notebooks],
[Tablets],
[Imaging],
[Other Electronics],
[Fixed Workstations],
[OptiPlex Desktops],
[Latitude]
)
)as b

Again--thanks for your response.

Regards
Geetha

quote:
Originally posted by visakh16

use matrix container in SSRS for above data. use row group as Prod_desc and column group as fweek . In data portion give expression like Count(Fields!service_request_number.value)/Count(Fields!service_request_number.value,"DatasetName") and give format code as P0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Geetha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-29 : 14:04:53

show a screenshot of your matrix. Otherwise i cant understand where you've textbox3 etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources