HI, I am trying to ascertain if it is possible to build a report in either BIDS (VS2010) or Report Builder 3.0 that would make it possible for users running the report to 'hot-swap' rows and columns in a matrix placed on the report based on one of the datasets within the report. This is to happen without forcing the user to 'edit' the report, i.e. the report is uploaded to a SharePoint 2010 site and I don't want the user to have to download and run Rpeort Builder 3.0 to then chnage the rows and columns in the matrix (or any other elements present on the report). If this is not possible straight out of the box, I don't mind creating scripts for it - e.g. user presses button and list of available fields 'pop-up' allowing user to choose a different row or column. Report example: number of events per year_month (column header) per city (row header) .. user wants to swap city with organisation to view number of events per year_month per organisation. I hope the above is clear and that someone can tell me if this is even remotely possible, Kind regards, B

You can set the grouping on matrix, chart etc. to work from a parameter; i.e. user can chose to view the report grouping by either department ID or staff ID. It might not be exactly what you're after, but since I don't think that's possible without developing your own application, it might suffice. See http://blogs.msdn.com/b/chrishays/archive/2004/07/15/dynamicgrouping.aspx for more details. This doesn't necessarily have to apply to groups, you could do something similar for un-grouped columns.I work to UK time. If this post, or another, answers your question, please click the 'Mark as answer' button so others with the same problem can find a solution more easily.

Hi Rapier, thanks for taking the time to read and answer my post. By the looks of it, your approach may indeed be a good solution for my problem - the 'hot-swapping' would be limited to what's available in the dataset and it is also accepted that there would be a limited (and predefined) number of row headers. I read your blog article and downloaded your report example - again, thanks a lot for sharing this with everybody - and then tried to make the report work for my purposes. Please note that I had to slightly change the SQL to work with AdventureWorks2008 (now with schemas Product, Sales etc) SELECT a.Name AS Product , b.Name AS SubCategory , c.Name AS Category , SUM(d.OrderQty) AS Quantity , DATEPART(yyyy, e.OrderDate) AS Year , f.Name AS Territory FROM Production.Product a INNER JOIN Production.ProductSubCategory b ON a.ProductSubCategoryID = b.ProductSubCategoryID INNER JOIN Production.ProductCategory c ON b.ProductCategoryID = c.ProductCategoryID INNER JOIN Sales.SalesOrderDetail d ON a.ProductID = d.ProductID INNER JOIN Sales.SalesOrderHeader e ON d.SalesOrderID = e.SalesOrderID INNER JOIN Sales.SalesTerritory f ON e.TerritoryID = f.TerritoryID GROUP BY a.Name, b.Name, c.Name, e.OrderDate, f.Name I also then went on to delete the third row header (Territory) in the report and then making the parameter (@Group3) visibility = Internal - then ran the report again and it allowed me to 'swap' the first grouping from Category to Territory and display appropriately - this was a better reflection of what my final result should be, i.e. potential to choose from say 15 fields but no desire to actually have 15 groupings on display. I still need to understand if and how I can apply this to the columns and value area, i.e. being able to swap any bit of the matrix (yes yes the value bit too - user to choose wether percentage, count or sum ... ** sigh **). Last but not least, the performance of the report doesn't seem to be great but I guess the large amount of calculations do not help. Again, thanks a lot for sharing this, Kind regards, B

No worries. It's not my blog, it's just the one I was pointed to, so I can't take credit for it! You're right that it can get complicated, especially if you need to change between aggregate types, but with enough conditional logic (Iif) it can be done. Performance will be an issue though; the more conditional logic you use the worse it will get. One way to minimise this is to make sure you're not working with any more data than you need to (include filters in your SQL rather than the report, if you can). You might also be able to shift some of the computational burden into the SQL if you use an expression to generate appropriate SQL. Happy coding!I work to UK time. If this post, or another, answers your question, please click the 'Mark as answer' button so others with the same problem can find a solution more easily.