Solved

SSRS2008 - table details and pivot without aggrigating

Posted on 2014-11-10
9
220 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 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