SSAS – Report Server Action

I was creating a demo for Actions in SQL Server Analysis Services 2005 (SSAS) and was havingg a problem getting the “Server Name” and “Report Path” properties correct.

I finally got a combination that worked. Here are the values I used for a report named “EmployeeReport” in a folder named “Report Sample” on the local server (localhost)

Server Name: localhost/ReportServer/Pages/ReportViewer.aspx?
Report Path: Report Sample/EmployeeReport

To make this work in your situation you should enter the correct server name instead of “localhost”, annd the appropriate folder path on the report server to the report you want to render.

Cool script to document table column usage in SQL 2005

Hey,

While running the Microsoft course “2784 – Tuning and Optimizing Queries using SQL Server 2005″ I ran into a need to list all table columns that were referenced by the various code objects (Procs, View, etc.) in the database.

After some poking around I decided to use a few features of SQL Server 2005 to get my results; The sys.sql_dependencies system catalog view and the PIVOT statement.

The result I desired would show every column in every table that is referenced by a programming object, and document the usage (Select or Update) of the column by the programming object. For Example:

Table    Column     pInsertEmployee  pUpdateEmployee vOrders<br />Employee EmployeeID U                U               S<br />Employee DateAdded  U    <br />Orders   EmployeeID                                  S<br />

Using the sys.sql_depencies system catalog view, the PIVOT operator and some dynamic sql code generation I was able to get what I needed. Following is the script I used.

-- =============================================================================<br />-- Title: SQL Server 2005 Column Usage<br />-- Author: Bret Stateham<br />-- bret@pingit.biz<br />-- Created: 08/01/07<br />-- Description: Sample script to show table column usage by server objects<br />-- =============================================================================<br />SET NOCOUNT ON<br /><br />IF OBJECT_ID('TempDB..#Dependencies') IS NOT NULL<br />DROP TABLE #Dependencies<br /><br />CREATE TABLE #Dependencies<br />(<br />  ReferencingObject nvarchar(256),<br />  ReferencingColumn nvarchar(256),<br />  ReferencedObject nvarchar(256),<br />  ReferencedColumn nvarchar(256),<br />  Usage nchar(256)<br />)<br /><br />INSERT INTO #Dependencies (ReferencingObject,ReferencingColumn,ReferencedObject,ReferencedColumn,Usage)<br />SELECT<br />  object_name(object_id) AS ReferencingObject<br />  ,IsNull<br />   (<br />    (<br />     SELECT name <br />     FROM sys.columns AS c <br />     WHERE c.object_id = d.object_id <br />       AND c.column_id = d.column_id<br />    )<br />    ,''<br />   ) AS ReferencingColumn<br />  ,OBJECT_NAME(referenced_major_id) AS ReferencedObject<br />  ,ISNULL<br />   (<br />    (<br />     SELECT name <br />     FROM sys.columns AS c <br />     WHERE c.object_id = d.referenced_major_id <br />       AND c.column_id = d.referenced_minor_id<br />    )<br />    ,''<br />   ) AS ReferencedColumn<br />  ,CASE<br />    WHEN is_selected = 1 and is_updated = 1 THEN 'SU'<br />    WHEN is_selected = 1 and is_updated = 0 THEN 'S'<br />    WHEN is_selected = 0 and is_updated = 1 THEN 'U'<br />    WHEN is_selected = 0 and is_updated = 0 THEN ''<br />   END AS Usage<br />FROM sys.sql_dependencies AS d<br /><br />DECLARE @PivotStatement nvarchar(max)<br />DECLARE @PivotColumns nvarchar(max)<br />DECLARE @SelectColumns nvarchar(max)<br /><br />SET @PivotStatement = ''<br />SET @PivotColumns = NULL<br />SET @SelectColumns = ''<br /><br />SELECT @PivotColumns = <br />  COALESCE(@PivotColumns + ',[' + Referencing + ']','[' + Referencing + ']')<br />FROM<br />(<br />  SELECT DISTINCT<br />    CASE<br />      WHEN ReferencingColumn <> '' THEN<br />        ReferencingObject + '.' + ReferencingColumn<br />      ELSE<br />        ReferencingObject<br />    END AS Referencing<br />  FROM #Dependencies<br />) AS DistinctReferencing<br /><br />SELECT <br />  @SelectColumns = ISNULL(@SelectColumns,'') + <br />                   ' ,ISNULL([' + Referencing + '],'''') AS [' + Referencing + ']' + CHAR(13) + CHAR(10)<br />FROM<br />(<br />  SELECT DISTINCT<br />    CASE<br />      WHEN ReferencingColumn <> '' THEN<br />        ReferencingObject + '.' + ReferencingColumn<br />      ELSE<br />        ReferencingObject<br />    END AS Referencing<br />  FROM #Dependencies<br />) AS DistinctReferencing<br /><br />SET @PivotStatement = '<br />  SELECT<br />    ReferencedObject<br />    ,ReferencedColumn<br />' + @SelectColumns + '<br />    FROM<br />    (<br />      SELECT<br />        ReferencedObject<br />        ,ReferencedColumn<br />        ,CASE<br />           WHEN ReferencingColumn <> '''' THEN<br />             ReferencingObject + ''.'' + ReferencingColumn<br />           ELSE<br />             ReferencingObject<br />         END AS Referencing<br />        ,Usage<br />      FROM #Dependencies<br />    ) AS d<br />    PIVOT<br />    (<br />      MAX(Usage)<br />      FOR Referencing IN (' + @PivotColumns + ')<br />    ) AS PivotedDependencies<br />    ORDER BY ReferencedObject, ReferencedColumn<br />'<br /><br />EXEC (@PivotStatement)<br /><br />DROP TABLE #Dependencies<br />SET NOCOUNT OFF<br />