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.
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 MVPhttp://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 equalto either the name of a containing group, the name of a containingdata 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 APIVOT(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 bAgain--thanks for your response.RegardsGeethaquote: 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 MVPhttp://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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|