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

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!

LVL 1
skaleem1Asked:
Who is Participating?
 
itcoupleCommented:
I'm actually getting the same error :) after MAX(field) specify column name
0
 
itcoupleCommented:
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
 
skaleem1Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
skaleem1Author Commented:
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
 
skaleem1Author Commented:
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
 
itcoupleCommented:
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
 
skaleem1Author Commented:
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
 
itcoupleCommented:
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
 
skaleem1Author Commented:
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
 
itcoupleCommented:
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
 
skaleem1Author Commented:
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
 
itcoupleCommented:
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
 
itcoupleCommented:
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
 
skaleem1Author Commented:
I get this error:

Msg 8155, Level 16, State 2, Line 95
No column was specified for column 3 of 't'.
0
 
skaleem1Author Commented:
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
 
skaleem1Author Commented:
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
 
skaleem1Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.