Tim Mitchell tdmitch

## ssrs_dynamictitle.vbs
=Parameters!SchemaName.Value & "." & Parameters!TableName.Value

## ssrs_visibility.vbs
=(Max(Fields!ID.Value, "ReportSchema") < 2)

## ssrs_lookup.vbs
=LOOKUP("Column001", Fields!ColumnID.Value, Fields!ColumnName.Value, "ReportSchema")

## GetGenericReportMetadata.sql
CREATE OR ALTER PROC [dbo].[GetGenericReportMetadata]
(
	@Schema VARCHAR(50)
	, @Table VARCHAR(50)
)

AS

SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) [ID]
	, 'Column' + RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY c.column_id) AS VARCHAR(10)), 3) [ColumnID]

## GetGenericReportData.sql
CREATE OR ALTER PROCEDURE [dbo].[GetGenericReportData]
(
	@Schema VARCHAR(50)
	, @Table VARCHAR(50)
)

AS

BEGIN

## ct_enable_db.sql
ALTER DATABASE [ChangeTracking] SET change_tracking = ON (change_retention = 14 days)

## ct_showchanges.sql
SELECT *
FROM CHANGETABLE(CHANGES dbo.Emp, 478)

## ct_delete.sql
DELETE dbo.Emp
WHERE  FirstName = 'Joey'
AND LastName = 'Tribbiani'

## ct_isinmask.sql
SELECT *
  , CHANGE_TRACKING_IS_COLUMN_IN_MASK (COLUMNPROPERTY(OBJECT_ID('dbo.Emp'), 'LastName', 'ColumnId'), ct.SYS_CHANGE_COLUMNS) AS MiddleName_Changed
FROM CHANGETABLE(CHANGES dbo.Emp, @ver) AS ct

## ct_blog_updates.sql
UPDATE dbo.Emp
SET MiddleName = 'Muriel'
WHERE FirstName = 'Chandler' AND LastName = 'Bing'

UPDATE dbo.Emp
SET LastName = 'Bing'
WHERE FirstName = 'Monica' AND LastName = 'Geller'
	CREATE OR ALTER PROC [dbo].[GetGenericReportMetadata]
	(
	@Schema VARCHAR(50)
	, @Table VARCHAR(50)
	)

	AS

	SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) [ID]
	, 'Column' + RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY c.column_id) AS VARCHAR(10)), 3) [ColumnID]
	CREATE OR ALTER PROCEDURE [dbo].[GetGenericReportData]
	(
	@Schema VARCHAR(50)
	, @Table VARCHAR(50)
	)

	AS

	BEGIN
	DELETE dbo.Emp
	WHERE FirstName = 'Joey'
	AND LastName = 'Tribbiani'
	SELECT *
	, CHANGE_TRACKING_IS_COLUMN_IN_MASK (COLUMNPROPERTY(OBJECT_ID('dbo.Emp'), 'LastName', 'ColumnId'), ct.SYS_CHANGE_COLUMNS) AS MiddleName_Changed
	FROM CHANGETABLE(CHANGES dbo.Emp, @ver) AS ct
	UPDATE dbo.Emp
	SET MiddleName = 'Muriel'
	WHERE FirstName = 'Chandler' AND LastName = 'Bing'

	UPDATE dbo.Emp
	SET LastName = 'Bing'
	WHERE FirstName = 'Monica' AND LastName = 'Geller'