Solved

Reporting Services - How to return a matrix correctly for multiple parts

Posted on 2010-08-22
17
319 Views
Last Modified: 2012-05-10
Following script can be run and will create two tables with the data:

DECLARE @AccPartNum      VARCHAR(1000)
set @AccPartNum='ABC-12345-001,  ABC-12345-002,  ABC-12345-003,  ABC-23456-001, ABC-23456-002'

DECLARE @TABLE TABLE
      (
            PartNumber VARCHAR(30)
      )

      INSERT INTO @TABLE
            SELECT * FROM [dbo].[fnSplit](@AccPartNum,',');


CREATE TABLE [dbo].[Parts](
      [PartID] [int] IDENTITY(1,1) NOT NULL,
      [PartNumber] [varchar](25) NOT NULL,
      [Desc] [char](40) NULL,
 CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED
(
      [PartID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

CREATE TABLE [dbo].[HWDetailsTest](
      [HWDetailsTestID] [int] IDENTITY(1,1) NOT NULL,
      [HardwareID] [int] NULL,
      [PhotoLink] [varchar](300) NULL
 CONSTRAINT [PK_HWDetailsTest] PRIMARY KEY CLUSTERED
(
      [HWDetailsTestID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


TRUNCATE TABLE Parts

INSERT INTO Parts VALUES ('ABC-12345-001', 'Desc 1')
INSERT INTO Parts VALUES ('ABC-12345-002', 'Desc 2')
INSERT INTO Parts VALUES ('ABC-12345-003', 'Desc 3')
INSERT INTO Parts VALUES ('ABC-23456-001', 'Desc 4')
INSERT INTO Parts VALUES ('ABC-23456-001', 'Desc 5')

--SELECT * FROM Parts

TRUNCATE TABLE HWDetailsTest

INSERT INTO HWDetailsTest VALUES (1, 'F:\Photos\ABC-12345-001 [HF].jpg')
INSERT INTO HWDetailsTest VALUES (1, 'F:\Photos\ABC-12345-001 [HB].jpg')
INSERT INTO HWDetailsTest VALUES (2, 'F:\Photos\ABC-12345-002 [HF].jpg')
INSERT INTO HWDetailsTest VALUES (2, 'F:\Photos\ABC-12345-002 [HB].jpg')
INSERT INTO HWDetailsTest VALUES (3, 'F:\Photos\ABC-12345-003 [HF].jpg')
INSERT INTO HWDetailsTest VALUES (3, 'F:\Photos\ABC-12345-003 [HB].jpg')
INSERT INTO HWDetailsTest VALUES (4, 'F:\Photos\ABC-23456-001 [HF].jpg')
INSERT INTO HWDetailsTest VALUES (4, 'F:\Photos\ABC-23456-001 [HB].jpg')
INSERT INTO HWDetailsTest VALUES (5, 'F:\Photos\ABC-23456-001 [HF].jpg')
INSERT INTO HWDetailsTest VALUES (5, 'F:\Photos\ABC-23456-001 [HB].jpg');

--select * from HWDetailsTest

WITH t AS
      (
      SELECT PartID, b.PartNumber, PhotoLink,
            dbo.udfRowNumberGrouping(ROW_NUMBER() OVER(PARTITION BY PartID
            ORDER BY HWDetailsID),2) as RowGrouping FROM HWDetailsTest a
            JOIN Parts b ON a.HardwareID=b.PartID
                  WHERE PartNumber IN (SELECT PartNumber FROM @TABLE)
      )
              SELECT *, ROW_NUMBER() OVER(PARTITION BY t.PartID, RowGrouping
              ORDER BY t.PartID) AS ColumnGrouping  FROM t

The data returned looks like this:

1      ABC-12345-001      F:\Photos\ABC-12345-001 [HF].jpg      1      1
1      ABC-12345-001      F:\Photos\ABC-12345-001 [HB].jpg      1      2
2      ABC-12345-002      F:\Photos\ABC-12345-002 [HF].jpg      1      1
2      ABC-12345-002      F:\Photos\ABC-12345-002 [HB].jpg      1      2
3      ABC-12345-003      F:\Photos\ABC-12345-003 [HF].jpg      1      1
3      ABC-12345-003      F:\Photos\ABC-12345-003 [HB].jpg      1      2
4      ABC-23456-001      F:\Photos\ABC-23456-001 [HF].jpg      1      1
4      ABC-23456-001      F:\Photos\ABC-23456-001 [HB].jpg      1      2
5      ABC-23456-001      F:\Photos\ABC-23456-001 [HF].jpg      1      1
5      ABC-23456-001      F:\Photos\ABC-23456-001 [HB].jpg      1      2

However, in this case there are different part numbers and the row grouping should be incremented and the data should look like this. Can you please help:

1      ABC-12345-001      F:\Photos\ABC-12345-001 [HF].jpg      1      1
1      ABC-12345-001      F:\Photos\ABC-12345-001 [HB].jpg      1      2
2      ABC-12345-002      F:\Photos\ABC-12345-002 [HF].jpg      2      1
2      ABC-12345-002      F:\Photos\ABC-12345-002 [HB].jpg      2      2
3      ABC-12345-003      F:\Photos\ABC-12345-003 [HF].jpg      3      1
3      ABC-12345-003      F:\Photos\ABC-12345-003 [HB].jpg      3      2
4      ABC-23456-001      F:\Photos\ABC-23456-001 [HF].jpg      4      1
4      ABC-23456-001      F:\Photos\ABC-23456-001 [HB].jpg      4      2
5      ABC-23456-001      F:\Photos\ABC-23456-001 [HF].jpg      5      1
5      ABC-23456-001      F:\Photos\ABC-23456-001 [HB].jpg      5      2

How should i tweak the following query to return the above desired results:

WITH t AS
      (
      SELECT PartID, b.PartNumber, PhotoLink,
            dbo.udfRowNumberGrouping(ROW_NUMBER() OVER(PARTITION BY PartID
            ORDER BY HWDetailsID),2) as RowGrouping FROM HWDetailsTest a
            JOIN Parts b ON a.HardwareID=b.PartID
                  WHERE PartNumber IN (SELECT PartNumber FROM @TABLE)
      )
              SELECT *, ROW_NUMBER() OVER(PARTITION BY t.PartID, RowGrouping
              ORDER BY t.PartID) AS ColumnGrouping  FROM t

Thanks for your help!

0
Comment
Question by:skaleem1
[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
  • 10
  • 7
17 Comments
 
LVL 10

Expert Comment

by:itcouple
ID: 33498862
Hi
Try removing 'Partition by PartID from RowGrouping'

Like this:

WITH t AS
      (
      SELECT PartID, b.PartNumber, PhotoLink,
            dbo.udfRowNumberGrouping(ROW_NUMBER() OVER(ORDER BY HWDetailsID),2) as RowGrouping FROM HWDetailsTest a
            JOIN Parts b ON a.HardwareID=b.PartID
                  WHERE PartNumber IN (SELECT PartNumber FROM @TABLE)
      )
              SELECT *, ROW_NUMBER() OVER(PARTITION BY t.PartID, RowGrouping
              ORDER BY t.PartID) AS ColumnGrouping  FROM t

I haven't tested it but it might work.

Regards
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33501326
Good, returns the following records:

PartID      PartNumber             Photolink                     RowGrouping ColumnGrouping
1      ABC-12345-001      F:\Photos\ABC-12345-001 [HF].jpg      1      1
1      ABC-12345-001      F:\Photos\ABC-12345-001 [HB].jpg      1      2
2      ABC-12345-002      F:\Photos\ABC-12345-002 [HF].jpg      2      1
2      ABC-12345-002      F:\Photos\ABC-12345-002 [HB].jpg      2      2
3      ABC-12345-003      F:\Photos\ABC-12345-003 [HF].jpg      3      1
3      ABC-12345-003      F:\Photos\ABC-12345-003 [HB].jpg      3      2
4      ABC-23456-001      F:\Photos\ABC-23456-001 [HF].jpg      4      1
4      ABC-23456-001      F:\Photos\ABC-23456-001 [HB].jpg      4      2
5      ABC-23456-002      F:\Photos\ABC-23456-002 [HF].jpg      5      1
5      ABC-23456-002      F:\Photos\ABC-23456-002 [HB].jpg      5      2

Is it possible to have the records for a single part only once while retaining the matrix row and column based groupings. I will post the way I want to see the results in the next posting:
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33501333
I am expecting the following results:

PartID      PartNumber             Photolink                     RowGrouping ColumnGrouping
1      ABC-12345-001      F:\Photos\ABC-12345-001 [HF].jpg      1      1
2      ABC-12345-002      F:\Photos\ABC-12345-002 [HF].jpg      1      2
3      ABC-12345-003      F:\Photos\ABC-12345-003 [HF].jpg      2      1
4      ABC-23456-001      F:\Photos\ABC-23456-001 [HF].jpg      2      2
5      ABC-23456-002      F:\Photos\ABC-23456-002 [HF].jpg      3      1
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 1

Author Comment

by:skaleem1
ID: 33501370
The query that only returns only the top part once is as follows:

SELECT * FROM
      (
            SELECT *, ROW_NUMBER() OVER(PARTITION BY PartNumber ORDER BY PartID desc) AS Serial
                  FROM Parts
      ) A WHERE Serial = 1 AND PartNumber in (SELECT PartNumber FROM @TABLE)

The results returned are:

PartID      PartNumber             Desc                                       Serial
1      ABC-12345-001      Desc 1                                        1
2      ABC-12345-002      Desc 2                                        1
3      ABC-12345-003      Desc 3                                        1
4      ABC-23456-001      Desc 4                                        1
5      ABC-23456-002      Desc 5                                        1

Is there a way we can incorporate/merge this query with yours to return the desired results?
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33501473
Hi

If I understand correctly you want to have only one photo for each part number? If yes that you need to work out the logic to chose correct photo. If you don't worry about which photo is displayed (random) then you can use MAX function on photo link and group by pardid, partnumber.


If you HWDetailsTest table contains photo links than I think you will have to change to something like this
(select hardwareID, MAX(PhotoLink) from HWDetailsTest  group by hardwareID)

Again this is guessing.... Unfortunatelly I cannot connect to the database where i have the test data from my current location (firewall).

Regards
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33501810
As you can see the desired dataset, this is true that I want one photo for each part number. For now, I am looking for the one where the PhotoLink has the value "[HF]", which can be handled by using the clause something like this in the WHERE Clause:

PhotoLink LIKE '%[HF]%'

Now the question is how can we incorporate the max(Photolink) logic in the following query:

WITH t AS
      (
      SELECT PartID, b.PartNumber, PhotoLink,
            dbo.udfRowNumberGrouping(ROW_NUMBER() OVER(ORDER BY HWDetailsID),2) as RowGrouping FROM HWDetailsTest a
            JOIN Parts b ON a.HardwareID=b.PartID
                  WHERE PartNumber IN (SELECT PartNumber FROM @TABLE)
      )
              SELECT *, ROW_NUMBER() OVER(PARTITION BY t.PartID, RowGrouping
              ORDER BY t.PartID) AS ColumnGrouping  FROM t

to achieve the following results:

PartID      PartNumber             Photolink                     RowGrouping ColumnGrouping
1      ABC-12345-001      F:\Photos\ABC-12345-001 [HF].jpg      1      1
2      ABC-12345-002      F:\Photos\ABC-12345-002 [HF].jpg      1      2
3      ABC-12345-003      F:\Photos\ABC-12345-003 [HF].jpg      2      1
4      ABC-23456-001      F:\Photos\ABC-23456-001 [HF].jpg      2      2
5      ABC-23456-002      F:\Photos\ABC-23456-002 [HF].jpg      3      1




0
 
LVL 10

Expert Comment

by:itcouple
ID: 33501904
Hi

If you know each part number has only ONE hf photo link then try this (I just added the filter) otherwise I will have to use group by with MAX at home (too complicated to work it out in my head :p) :
WITH t AS
      (
      SELECT PartID, b.PartNumber, PhotoLink,
            dbo.udfRowNumberGrouping(ROW_NUMBER() OVER(ORDER BY HWDetailsID),2) as RowGrouping FROM HWDetailsTest a
            JOIN Parts b ON a.HardwareID=b.PartID
                  WHERE PartNumber IN (SELECT PartNumber FROM @TABLE)  and PhotoLink LIKE '%[HF]%'
      )
              SELECT *, ROW_NUMBER() OVER(PARTITION BY t.PartID, RowGrouping
              ORDER BY t.PartID) AS ColumnGrouping  FROM t

Regards
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33502191
When I changed the query as you suggested:

WITH t AS
      (
      SELECT PartID, b.PartNumber, PhotoLink,
            dbo.udfRowNumberGrouping(ROW_NUMBER() OVER(ORDER BY HWDetailsID),2) as RowGrouping FROM HWDetailsTest a
            JOIN Parts b ON a.HardwareID=b.PartID
                  WHERE PartNumber IN (SELECT PartNumber FROM @TABLE)  and PhotoLink LIKE '%\[HF\]%' ESCAPE '\'
      )
              SELECT *, ROW_NUMBER() OVER(PARTITION BY t.PartID, RowGrouping
              ORDER BY t.PartID) AS ColumnGrouping  FROM t


The results are not as desired and the grouping is somehow distorted:

PartID      PartNumber             Photolink                     RowGrouping ColumnGrouping
1      ABC-12345-001      F:\Photos\ABC-12345-001 [HF].jpg      1      1
2      ABC-12345-002      F:\Photos\ABC-12345-002 [HF].jpg      1      1
3      ABC-12345-003      F:\Photos\ABC-12345-003 [HF].jpg      2      1
4      ABC-23456-001      F:\Photos\ABC-23456-001 [HF].jpg      2      1
5      ABC-23456-002      F:\Photos\ABC-23456-002 [HF].jpg      3      1
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33502400
The query with Column Grouping will have to be changed:

Try

WITH t AS
      (
      SELECT PartID, b.PartNumber, PhotoLink,
            dbo.udfRowNumberGrouping(ROW_NUMBER() OVER(ORDER BY HWDetailsID),2) as RowGrouping FROM HWDetailsTest a
            JOIN Parts b ON a.HardwareID=b.PartID
                  WHERE PartNumber IN (SELECT PartNumber FROM @TABLE)  and PhotoLink LIKE '%\[HF\]%' ESCAPE '\'
      )
              SELECT *, ROW_NUMBER() OVER(PARTITION BY RowGrouping
              ORDER BY t.PartID) AS ColumnGrouping  FROM t

Regards
Emil
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33502660
Thanks, this works great. Now the final thing is to determine how do we eliminate multiple records for the same part where the PhotoLink has the value "[HF]",  either using the Max(Photolink) or incorporating the query with your query that only returns the top part once. The query is as follows:

SELECT * FROM
      (
            SELECT *, ROW_NUMBER() OVER(PARTITION BY PartNumber ORDER BY PartID desc) AS Serial
                  FROM Parts
      ) A WHERE Serial = 1 AND PartNumber in (SELECT PartNumber FROM @TABLE)

Is there a way to merge/include this query with your query as below?:

WITH t AS
      (
      SELECT PartID, b.PartNumber, PhotoLink,
            dbo.udfRowNumberGrouping(ROW_NUMBER() OVER(ORDER BY HWDetailsID),2) as RowGrouping FROM HWDetailsTest a
            JOIN Parts b ON a.HardwareID=b.PartID
                  WHERE PartNumber IN (SELECT PartNumber FROM @TABLE)  and PhotoLink LIKE '%\[HF\]%' ESCAPE '\'
      )
              SELECT *, ROW_NUMBER() OVER(PARTITION BY RowGrouping
              ORDER BY t.PartID) AS ColumnGrouping  FROM t

Whenever you can get to the test data, may be it will be easier for you to answer this question I hope :-)



0
 
LVL 10

Expert Comment

by:itcouple
ID: 33504014
Try this

WITH t AS
      (
      SELECT PartID, b.PartNumber, MAX(PhotoLink),
            dbo.udfRowNumberGrouping(ROW_NUMBER() OVER(ORDER BY PartID),2) as RowGrouping FROM HWDetailsTest a
            JOIN Parts b ON a.HardwareID=b.PartID
                  WHERE PartNumber IN (SELECT PartNumber FROM @TABLE)  and PhotoLink LIKE '%\[HF\]%' ESCAPE '\'
GROUP BY PartID, b.PartNumber,
      )
              SELECT *, ROW_NUMBER() OVER(PARTITION BY RowGrouping
              ORDER BY t.PartID) AS ColumnGrouping  FROM t
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33504018
I forgotten to remove comma

Try this
WITH t AS
      (
      SELECT PartID, b.PartNumber, MAX(PhotoLink),
            dbo.udfRowNumberGrouping(ROW_NUMBER() OVER(ORDER BY PartID),2) as RowGrouping FROM HWDetailsTest a
            JOIN Parts b ON a.HardwareID=b.PartID
                  WHERE PartNumber IN (SELECT PartNumber FROM @TABLE)  and PhotoLink LIKE '%\[HF\]%' ESCAPE '\'
GROUP BY PartID, b.PartNumber
      )
              SELECT *, ROW_NUMBER() OVER(PARTITION BY RowGrouping
              ORDER BY t.PartID) AS ColumnGrouping  FROM t
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33504083
I get this error:

Msg 8155, Level 16, State 2, Line 95
No column was specified for column 3 of 't'.
0
 
LVL 10

Accepted Solution

by:
itcouple earned 500 total points
ID: 33504458
I'm actually getting the same error :) after MAX(field) specify column name
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33504542
OK that's great. Thanks a lot emil. I will accept this solution and will post a new question link here for you to answer another related question. This is regarding a further enhancement of the subReport that you helped me with.

Thanks a lot again.
0
 
LVL 1

Author Closing Comment

by:skaleem1
ID: 33504557
Thanks a lot emil. I will accept this solution and will post a new question link here for you to answer another related question. This is regarding a further enhancement of the subReport that you helped me with.
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33504915
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

617 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