Matrix column mapping
Hi There I am not sure why you are using dynamic SQL in your SQL script and using pivot. Please bring your data from the source system without pivoting it. SSRS matrix reports are quite powerful for doing this type of requirement. Please let me know if you have any questions Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful
May 16th, 2012 4:33am

Hi, I have an SSRS 2008 report with a matrix. The DataSource is constructed as follows: create table Risks ( Project varchar(100),ItemID int,Created datetime) Insert into Risks values ('A',12,getdate()), ('A',10,getdate()-14), ('b',12,getdate()-30), ('b',11,getdate()-70), ('c',12,getdate()-50), ('c',10,getdate()-45) declare @SQL nvarchar(max), @Cols nvarchar(max) select @Cols = stuff( ((select ', ' + quotename(Project) from (select distinct Project from Risks) X ORDER BY Project for XML PATH(''),type).value('.','nvarchar(max)')) ,1,2,'') set @SQL = ';with cte as (select Project, ItemID, LEFT(datename(month, Created),3) as [MonthName] from Risks) select *, ' + replace(@Cols, '], [','] + [') + ' as Total from cte PIVOT (count(ItemID) for Project IN (' + @cols +')) pvt' execute(@SQL) The output needs to be similar to the following: Project A Project B Project C Total JAN 12 1 3 16 FEB 4 3 5 12 MAR 4 4 4 12 So my question is how I get the matrix configured to work based on my example SQL - currently when applying the DataSource I can see a number of columns (total, monthname, project a, project b etc.) and multiple columns of my actual project names - what I want to see is a column called Project which I can map to one of the rows. Does this make sense.. can anyone help? Any advice appreciated.
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 6:47am

Hi There I am assuming that you have monthname, total and project column in your data source am I right. Month name include all the month name of the year and project column could have many projects say project A project b and so on So what you need to do just create a report and add a matrix on the report surface Add Monthname to your row group and Project to your column group and put total in the details field. After doing that you could click on the column group and right click and add subtotal. You could do it similarly with row group as well Please let me know if you have any questions Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote asHelpful" if you find it helpful
May 16th, 2012 7:08am

Hi There I am assuming that you have monthname, total and project column in your data source am I right. Month name include all the month name of the year and project column could have many projects say project A project b and so on So what you need to do just create a report and add a matrix on the report surface Add Monthname to your row group and Project to your column group and put total in the details field. After doing that you could click on the column group and right click and add subtotal. You could do it similarly with row group as well Please let me know if you have any questions Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote asHelpful" if you find it helpful Thanks - the problem is that I do not see Project as a selectable column - I just see a list of the actual projects, as though it is dynamically running the query when I'm trying to map the project to a field.
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 8:26am

Hello, I think that you can try to create a parameter named "Project" for the report, and then specify the "project" field as the parameter available values. Then, you can use the parameter to control the report data. Here are some articles for your reference, please see: Using Parameters to Control Report Data: http://msdn.microsoft.com/en-us/library/aa337287(v=sql.100).aspx Adding parameters to a reprot: http://msdn.microsoft.com/en-us/library/ms155917(v=sql.100).aspx Regards, Bin LongBin Long TechNet Community Support
May 17th, 2012 1:58am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics