SSRS2008 - table details and pivot without aggrigating

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
shmzAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Koen Van WielinkConnect With a Mentor IT ConsultantCommented:
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
 
Koen Van WielinkIT ConsultantCommented:
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
 
shmzAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
shmzAuthor Commented:
(Note that count per row will always be 1 and added in final rows)
0
 
shmzAuthor Commented:
(note that it doesn't have to be done with pivot)
0
 
shmzAuthor Commented:
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
 
shmzAuthor Commented:
By detailed section I mean there are grouping already. Their query is bunch of inner, left , outer joins....
0
 
Koen Van WielinkIT ConsultantCommented:
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
 
shmzAuthor Commented:
Thank you
0
All Courses

From novice to tech pro — start learning today.