July 5, 2011 at 4:49 pm
OK.
This has actually got 3 of us at work puzzled. We can do it in Excel quite easily, but they want it done in SSRS.
I have a table with hundreds of rows for customer repairs.
Each Item has had multiple repairs done on it on various days.
Sample Data
<Customer>,<RepairDate>,<Item Desc>,<Serial>,<Repair>,<Labour>,<Parts>,<ReplaceDate>
CustA,01/04/2011,DellPC,A4B3222,Hard Drive,20.00,56.00,01/02/2013
CustA,01/04/2011,DellPC,A4B3222,Clean Mouse,5.00,0.00,01/02/2013
CustA,06/05/2011,HPLaptop,ZZZZ,Replace LCD,40.00,125.00,01/12/2014
CustB,05/02/2011,OEMPC,HHHHH,MemUpgrade,20.00,75.00,01/09/2011
Etc
Simply each record relates to an item of equipment and what was done to it , there is a limit to 10 repair items in a day.
So What I need is a report that looks like
CustA 01/04/2011 DellPC Hard Drive Clean Mouse 25.00 56.00 01/02/2013
CustA 06/05/2011 HPLaptop Replace LCD 40.00 125.00 01/12/2014
etc
i.e. A horizontal list of upto the 10 repairs in a day across the page with a total for the day and other information.
I'm using SQL Server 2008 R2 SSRS.
I've tried a matrix on the report,but this creates an individual column for each type of repair, thus there can be thousands on columns. All I need a horizontal list for each item on a day with a total value.
Has anybody got any ideas
Thanks in advance
July 5, 2011 at 6:57 pm
Sure... take a look at the following article. And, no... not limited to 10 items. 😉
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2011 at 10:08 pm
Hey Jeff... I came into this thread expecting to point to your dynamic cross tab article... after reading the thread, I come to find you're recommending my article (which does meet the requirements). Neat! 😎
And thanks for the plug!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 6, 2011 at 7:20 am
Brill,
I'll give it a go and see if it makes sense to me. I've never used to stuff command before.
Thanks again.
July 6, 2011 at 9:10 am
OK,
So I've had a go with the routine that you suggested, but it's doesn't quite do what I want.
I may not of explained myself very well in the first place.
Same initial table data as before. i.e. 1 row for each repair done on a day, with date, repair, serial number etc.
I need the data to be returned in a table, where the Repair Descriptions are stored into upto 10 columns.
The table will look like
[Seria]l varchar(10),
[Repair Date] DateTime,
[Item Desc] Varchar(50),
[Repair 1] Varchar(25),
[Repair 2] Varchar(25),
[Repair 3] Varchar(25),
[Repair 4] Varchar(25),
[Repair 5] Varchar(25)
[Repair 6] Varchar(25),
[Repair 7] Varchar(25),
[Repair 8] Varchar(25),
[Repair 9] Varchar(25),
[Repair 10] Varchar(25),
[Total Cost] Numric 18.2
I would then use this table to produce my report.
Thanks again
July 6, 2011 at 10:36 am
First, make that sample data usable.
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
DECLARE @Repairs TABLE (
Customer VARCHAR(50),
RepairDate DATETIME,
[Item Desc] VARCHAR(50),
Serial VARCHAR(10),
Repair VARCHAR(25),
Labour NUMERIC(8,2),
Parts NUMERIC(8,2),
ReplaceDate DATETIME);
INSERT INTO @Repairs
VALUES ('CustA','01/04/2011','DellPC','A4B3222','Hard Drive',20.00,56.00,'01/02/2013'),
('CustA','01/04/2011','DellPC','A4B3222','Clean Mouse',5.00,0.00,'01/02/2013'),
('CustA','06/05/2011','HPLaptop','ZZZZ','Replace LCD',40.00,125.00,'01/12/2014'),
('CustB','05/02/2011','OEMPC','HHHHH','MemUpgrade',20.00,75.00,'01/09/2011');
Now that we have consumable sample data, here's the query to return your desired results:
;
WITH cte AS
(
-- Assign a row number to each row; restart numbering whenever Serial or RepairDate changes
-- If ordering is needed to determine what the first 10 records are,
-- then change the order by clause in this over clause.
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY Serial, RepairDate ORDER BY (SELECT 1))
FROM @Repairs
)
-- Cross-tab the first ten repair records; add up the total cost.
SELECT Serial,
RepairDate,
[Item Desc],
[Repair 1] = MAX(CASE WHEN RN = 1 THEN Repair ELSE NULL END),
[Repair 2] = MAX(CASE WHEN RN = 2 THEN Repair ELSE NULL END),
[Repair 3] = MAX(CASE WHEN RN = 3 THEN Repair ELSE NULL END),
[Repair 4] = MAX(CASE WHEN RN = 4 THEN Repair ELSE NULL END),
[Repair 5] = MAX(CASE WHEN RN = 5 THEN Repair ELSE NULL END),
[Repair 6] = MAX(CASE WHEN RN = 6 THEN Repair ELSE NULL END),
[Repair 7] = MAX(CASE WHEN RN = 7 THEN Repair ELSE NULL END),
[Repair 8] = MAX(CASE WHEN RN = 8 THEN Repair ELSE NULL END),
[Repair 9] = MAX(CASE WHEN RN = 9 THEN Repair ELSE NULL END),
[Repair 10] = MAX(CASE WHEN RN = 10 THEN Repair ELSE NULL END),
[Total Cost] = SUM(Labour + Parts)
FROM cte
WHERE RN <= 10
GROUP BY Serial, RepairDate, [Item Desc];
Edit: you might want to read the two Cross-Tab / Pivot Table articles in my signature below.
Edit2: you might want to add an order by clause to the final query to order the results.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 6, 2011 at 4:25 pm
Thank you again for all your help, it is very much appreciated.
I have taken note of your comments and in the future if I have any more questions to ask I'll make sure I provided all the information needed with a small sample database as you described.
July 6, 2011 at 4:46 pm
itwhiz (7/6/2011)
OK,So I've had a go with the routine that you suggested, but it's doesn't quite do what I want.
I may not of explained myself very well in the first place.
Same initial table data as before. i.e. 1 row for each repair done on a day, with date, repair, serial number etc.
I need the data to be returned in a table, where the Repair Descriptions are stored into upto 10 columns.
The table will look like
[Seria]l varchar(10),
[Repair Date] DateTime,
[Item Desc] Varchar(50),
[Repair 1] Varchar(25),
[Repair 2] Varchar(25),
[Repair 3] Varchar(25),
[Repair 4] Varchar(25),
[Repair 5] Varchar(25)
[Repair 6] Varchar(25),
[Repair 7] Varchar(25),
[Repair 8] Varchar(25),
[Repair 9] Varchar(25),
[Repair 10] Varchar(25),
[Total Cost] Numric 18.2
I would then use this table to produce my report.
Thanks again
That's better clarification. I couldn't tell you wanted a columnized result set from...
A horizontal [font="Arial Black"]list[/font] of upto the 10 repairs in a day across the page with a total for the day and other information.
Wayne's code should do nicely for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2011 at 4:23 pm
{edit} Commented on wrong post...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply