?
Solved

SSRS2008 - table details and pivot without aggrigating

Posted on 2014-11-10
9
Medium Priority
?
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

764 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