Re-add Grouping to Tablix After Deleting it in SSRS

If you’ve ever accidentally deleted the default row grouping on a tablix in SSRS, you have two options to get it back.

1. Recreate the tablix. (Which isn’t fun if you’ve have several columns or lots of formatting)
2. Recreate the grouping that’s done by default during the wizard. (You used the wizard, didn’t you?)

Rather than starting from scratch with a new tablix, I going to show how to get the default grouping back up and running.

The steps are as follows:

1. Right click on the detail line that needs the grouping re-added.
2. Select “Add Group … Adjacent Above…”
3. Then select the first column in the detail line as the “Group By”
4. After the grouping is created, cut and paste each of the columns to the newly create line above it. This will keep the formatting and formulas intact. After all the columns have been moved, you can delete the old detail line.

SSRS Error: An item with the same key has already been added

This typically means that you have an column in your dataset query that is specified twice. For instance, SQL will let you return "Order Number" twice in a query, but if you try to use that query in SSRS, it will error with "An item with the same key has already been added".

Remove the second instance of the column or alias the column to a different name to keep the error from coming back.

SQL Reporting Services (SSRS) 2008 R2 Administration Scripts

Below are a few SQL Reporting Services administration scripts that I've created to help me audit usage of the system.

1. Report Usage Count By User For The Previous 30 Days

SELECT  UserName
       ,COUNT(*) AS TimesExecuted
FROM    ReportServer.dbo.ExecutionLog3
WHERE   ItemPath LIKE '%<ReportName>%'
        AND TimeStart > GETDATE() - 30
GROUP BY UserName
ORDER BY 2 DESC

2. Report Usage With Parameters, Row Count, Execution Time, Format and User Name

SELECT  TimeStart
       ,Source
       ,[RowCount]
       ,Parameters
       ,UserName
       ,RequestType
       ,Format
       ,TimeDataRetrieval + TimeProcessing + TimeRendering [TimeInMilliseconds]
FROM    dbo.ExecutionLog3
WHERE   ItemPath LIKE '%<ReportName>%'
        AND TimeStart BETWEEN '3/10/12' AND '6/11/12'
ORDER BY 1 DESC

3. Report Location with Creation Date, Last Execution Date, and Usage Count

SELECT  Name
       ,CreationDate
       ,Path
       ,E.TimesExecuted
       ,E.LastExecutedFROM    Catalog
        INNER JOIN (SELECT  ItemPath
                           ,COUNT(*) AS TimesExecuted
                           ,MAX(TimeStart) AS LastExecuted
                    FROM    ExecutionLog3
                    GROUP BY ItemPath) E
        ON CATALOG.PATH = E.ItemPathWHERE   Path LIKE '/Reports%'
        AND TYPE = 2ORDER BY 2 DESC

SQL Reporting Services 2008 Execution Log History

In SQL Reporting Services 2008, you can set the Execution Log retention to keep more than the default 60 days by updating the ConfigurationInfo table inside the ReportServer database. Inside that table is a value labeled ExecutionLogDaysKept which can be updated to any value up to 2,147,483,647 , with 0 meaning that all history is kept indefinitely. You can update the value to keep 1 year's (365 days) worth of history with the script below.

UPDATE  ConfigurationInfo
SET     Value = '365'
WHERE   Name = 'ExecutionLogDaysKept'