Solved

SSRS2008 - table details and pivot without aggrigating

Posted on 2014-11-10
9
216 Views
Last Modified: 2014-11-11
Hi,

How can I create a report and write a query in ssrs 2008 with this format:

customer ID    account#   count   bicycle $        bike $               total
1                         1293          1           200.00          
1                         1239          1                                 1000.00
1                          1239          2          200.00          1000.00            1200
2                          1333          1          100
2                          1333          1             100                                         100

bike and bike and totals can be hardcoded in query.

thanks
0
Comment
Question by:shmz
  • 6
  • 3
9 Comments
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40434183
That all depends on what your data sources look like. Without your table definitions and some sample data we won't be able to help you. Please provide the table names, table structures, and some sample data for each, as well as how that sample data should combine into your pivot table.
0
 

Author Comment

by:shmz
ID: 40434203
Koen, Thanks for your response:

create table mypivottest (
  customerId  int
, accountNbr int
, productName nvarchar (100)
, purchasePrice int
)

insert into mypivottest values
(1, '1239','Bicycle',200),
(1, '1239','Bike',1000),
(2, '1233','Bicycle',500),
(2,'1233','Boat', 1000)

the expected result(slightly different from initial sample in the Q):

customer ID    account#   count   bicycle $        bike $           Other $       total
1                         1239          1           200.00          
1                         1239          1                                 1000.00
1                          1239          2          200.00          1000.00                             1200

2                          1333          1          500
2                          1333           1                                                       1000
2                          1333           2          500                                       1000          1500

- 'bikes' and 'bicycle' can be hardcoded in query. (In real query I have only 4 columns)
Thanks
0
 

Author Comment

by:shmz
ID: 40434212
(Note that count per row will always be 1 and added in final rows)
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:shmz
ID: 40434250
(note that it doesn't have to be done with pivot)
0
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40434278
Ok, took a bit of puzzling but I think I have more or less what you're looking for. Give this a whirl:

create table #mypivottest (
  customerId  int 
, accountNbr int
, productName nvarchar (100)
, purchasePrice int
);

insert into #mypivottest values 
(1, '1239','Bicycle',200),
(1, '1239','Bike',1000),
(2, '1233','Bicycle',500),
(2,'1233','Boat', 1000);


with pvt as 
(
Select		customerId
		,	accountNbr
		,	1 as 'ItemCount'
		,	[Bicycle]
		,	[Bike]
		,	[Other]
		,	(select SUM(S)
				from	(values	([Bicycle])
							,	([Bike])
							,	([Other])) as T(S)) as 'Total'
from	(select		purchasePrice
				,	customerId
				,	accountNbr
				,	ROW_NUMBER() over (PARTITION by customerId, accountNbr
								order by customerId, accountNbr) as 'ItemCount'
				,	case
						when productName in ('Bicycle', 'Bike')
						then productName
						else 'Other'
					end as productName
		from	#mypivottest) as s
pivot (sum(purchasePrice)
		for productName	in ([Bicycle], [Bike], [Other])) as p
)

select *
from
	(	select		ltrim(rtrim(str(customerId,3,0))) as 'customerId'
				,	accountNbr
				,	itemCount
				,	isnull(Bicycle,0) as 'Bicycle'
				,	isnull(Bike,0) as 'Bike'
				,	isnull(Other,0) as 'Other'
				,	isnull(Total,0) as 'Total'
				--,	0 as 'Total'
		from	pvt
		
		union all
		
		select		ltrim(rtrim(str(customerId,3,0))) + ' Total'
				,	accountNbr
				,	SUM(itemCount)
				,	SUM(isnull(Bicycle,0))
				,	SUM(isnull(Bike,0))
				,	SUM(isnull(Other,0))
				,	SUM(isnull(Total,0))
		from	pvt
		group by customerId, accountNbr
		) as Result

order by customerId, ItemCount

drop table #mypivottest

Open in new window


The pivot statement creates the pivot you want obviously, but doesn't yet have the totals in it. This is done by putting the pivot result in a CTE statement first. After that a union statement adds the sums grouped by the customerId. I've converted the customer ID column to strings so you can add the word "total" for clarity. The only differences with your example is that the totals column on the right displays the totals for all the rows. If you don't want this, comment out the line isnull(Total,0) as 'Total' from the first select statement in the union query and uncomment 0 as 'Total'. That will give you 0's for the item lines and only the sum for the customer total.
Thanks for the question, was a nice exercise :D.
0
 

Author Comment

by:shmz
ID: 40434460
Thank you for the beautiful solution! I am jelous! Can you also explain what was your thought process in resolving it?
The other problem I have is that I have a pre existing report with detailed section which displays the records with subtotal section and I am asked to modify this report and produce the remarkably fast result you have achieved. :)
0
 

Author Comment

by:shmz
ID: 40434462
By detailed section I mean there are grouping already. Their query is bunch of inner, left , outer joins....
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40434532
Regarding the solution, I just started off by creating the pivot layout you wanted first, then worked out how to add the subtotals later. This was mainly achieved through google searches :p.
For your report, that's a bit hard to answer without further information (report design software, layout, example, etc).
0
 

Author Closing Comment

by:shmz
ID: 40436487
Thank you
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question