Solved

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

Posted on 2010-08-22
17
312 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
  • 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
 
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
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 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

708 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

13 Experts available now in Live!

Get 1:1 Help Now