May 10, 2009 at 4:42 am
Hi all,
Can anyone please help me in having more than one row group in matrix.
Available Data :
ITEMS Item Name Uom GrpId GrpIdVal
101001 Item-A kg pkz1 23
101001 Item-A kg pkz2 0.1
101001 Item-A kg arm1 0.2
909090 Item-B kg pkz2 10
909090 Item-B kg arm2 12
Output using matrix should be:
Items Item Name Uom pkz1 pkz2 arm1 arm2
101001 Item-A kg 23 0.1 0.2 0
909090 Item-B kg 0 10 0 12
The columns are dynamic can increase.
I tried this giving spaces and align accordingly to columns the preview looks good but when deployed , the column header of the row groups are concatenated as below
Items Item Name Uom pkz1 pkz2 arm1 arm2
101001 Item-A kg 23 0.1 0.2 0
909090 Item-B kg 0 10 0 12
Exporting to excel is another problem.
I pray to god for the person who gives me the solution.;-)
May 10, 2009 at 7:03 am
Not sure if this is what you meant
-- Create And Populate Test Table
CREATE TABLE dbo.TestTable (ITEMS int, [Item Name] varchar(20), Uom varchar(4), GrpId int, GrpIdVal decimal(9,1))
INSERT dbo.TestTable VALUES (101001, 'Item-A kg', 'pkz', 1, 23)
INSERT dbo.TestTable VALUES (101001, 'Item-A kg', 'pkz', 2, 0.1)
INSERT dbo.TestTable VALUES (101001, 'Item-A kg', 'arm', 1, 0.2)
INSERT dbo.TestTable VALUES (909090, 'Item-B kg', 'pkz', 2, 10)
INSERT dbo.TestTable VALUES (909090, 'Item-B kg', 'arm', 2, 12)
-- Actual Query
DECLARE @sql nvarchar(max)
SET @sql = N'SELECT ITEMS,[Item Name]'
SELECT @sql = @sql + ',SUM(CASE WHEN Uom+CAST(GrpId as varchar)='''+a.Uom+CAST(a.GrpId as varchar)+''' THEN GrpIdVal ELSE 0.0 END) AS ['+a.Uom+CAST(a.GrpId as varchar)+']'
FROM dbo.TestTable a GROUP BY a.Uom,a.GrpId
ORDER BY a.Uom
SET @sql = @sql + N'
FROM dbo.TestTable
GROUP BY ITEMS,[Item Name]
ORDER BY ITEMS,[Item Name]'
-- PRINT @sql -- For Debugging
EXEC sp_executesql @sql
Far away is close at hand in the images of elsewhere.
Anon.
May 14, 2009 at 8:02 am
Sorry mate i was looking for SSRS matrix solution...the data i had given is a test data, als the columns are dynamic..
Thanks for the effort....:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply