Solved

SSRS2008 - table details and pivot without aggrigating

Posted on 2014-11-10
9
196 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 12

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
 

Author Comment

by:shmz
ID: 40434250
(note that it doesn't have to be done with pivot)
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 12

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 12

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now