About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Monday, 26 November 2012

SSRS # How to achieve Dynamic Sorting in SSRS?

Introduction
I wrote a blog on “Dynamic Sorting” in SSRS sometime back. Even though I successfully achieved sorting the data dynamically, I failed to find out a way of applying the “Sort Direction” dynamically as the Sort Direction did not accept an expression to manage it.
Recently, I came across a trick which solves this very easily purely in SSRS. Let’s jump on to the implementation now without wasting much time.

Implementation
Continuing with the same example of sorting 3 columns – Col1,Col2 & Col3 in any preferred order, we can specify the Sort Direction dynamically in the following way -
1. Define 3 additional parameters - @Col1SortDirection,@Col2SortDirection & @Col3SortDirection of STRING type.

2. While setting the Sort Options for Tablix or Group, we have to define sorting for double the number the sorting columns as under -

Sort Direction












The Sort By expressions will be defined as under -
a. IIF(Parameters!Col1SortDirection.Value = "Ascending",Fields(Parameters!SortCol1Name.Value).Value,Nothing)
b. IIF(Parameters!Col1SortDirection.Value = "Descending",Fields(Parameters!SortCol1Name.Value).Value,Nothing)
c. IIF(Parameters!Col2SortDirection.Value = "Ascending",Fields(Parameters!SortCol2Name.Value).Value,Nothing)
d. IIF(Parameters!Col2SortDirection.Value = "Descending",Fields(Parameters!SortCol2Name.Value).Value,Nothing)
e. IIF(Parameters!Col3SortDirection.Value = "Ascending",Fields(Parameters!SortCol3Name.Value).Value,Nothing)
f. IIF(Parameters!Col3SortDirection.Value = "Descending",Fields(Parameters!SortCol3Name.Value).Value,Nothing)

Note: we can use any CONSTANT value to replace Nothing used in above expression.

3. Pass the required Sort Direction, say - @Col1SortDirection = “Ascending”, @Col2SortDirection = “Descending” & @Col3SortDirection = “Ascending” or in any preferred value.

4. Pass the values for other parameters as explained in my last post.

Conclusion
With this we can now easily control the dynamic sorting with full flexibility of specifying the Column Names & the Sort Direction both dynamically using pure SSRS.
Hope, this will help you a lot!
Posted by at 1 comment:
Labels:

Wednesday, 17 October 2012

SQL Server # Database Backup Report using T-SQL

Today, I am going to share few very useful scripts which will report us on Database Backup from different view points.

To get the List/History/Log of all the Successful Backups

SELECT 
  b.machine_name,
  b.server_name,
  b.database_name as DBName,
  b.backup_start_date,
  b.backup_finish_date,
  CASE 
    WHEN b.[type] = 'D' THEN 'Database'
    WHEN b.[type] = 'I' THEN 'Differential database'
    WHEN b.[type] = 'L' THEN 'Log'
    WHEN b.[type] = 'F' THEN 'File or filegroup'
    WHEN b.[type] = 'G' THEN 'Differential file'
    WHEN b.[type] = 'P' THEN 'Partial'
    WHEN b.[type] = 'Q' THEN 'Differential partial'
    ELSE b.[type]
  END Backup_Type,    
  b.expiration_date,
  b.[user_name],
  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
  b.recovery_model,
  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
  bf.physical_device_name as Location
FROM 
  msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
  ON b.media_set_id=bf.media_set_id
ORDER BY 
  b.backup_start_date DESC
GO


To get a list of all successful Backups taken till date for a particular Database



DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'
 
SELECT 
  b.machine_name,
  b.server_name,
  b.database_name as DBName,
  b.backup_start_date,
  b.backup_finish_date,
  CASE 
    WHEN b.[type] = 'D' THEN 'Database'
    WHEN b.[type] = 'I' THEN 'Differential database'
    WHEN b.[type] = 'L' THEN 'Log'
    WHEN b.[type] = 'F' THEN 'File or filegroup'
    WHEN b.[type] = 'G' THEN 'Differential file'
    WHEN b.[type] = 'P' THEN 'Partial'
    WHEN b.[type] = 'Q' THEN 'Differential partial'
    ELSE b.[type]
  END Backup_Type,
  b.expiration_date,
  b.[user_name],
  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
  b.recovery_model,
  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
  bf.physical_device_name as Location
FROM 
  msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
  ON b.media_set_id=bf.media_set_id
WHERE
  b.database_name = @DBName  
ORDER BY 
  b.backup_start_date DESC
GO

To get the List of all Databases which are not backed up till date



SELECT
  d.name [DB_Name]
FROM
  master.sys.databases d
LEFT JOIN msdb.dbo.backupset b
  ON b.database_name = d.name
WHERE
  d.database_id IS NULL


To get the List of all Databases which are not backed up since last X days



DECLARE @LastXDays AS INT = 1
;WITH LatestBackupSet AS (
SELECT 
  b.database_name as DBName,
  b.backup_start_date LastBackedUpOn,
  b.[user_name],
  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
FROM 
  msdb.dbo.backupset AS b
)
SELECT 
  lbs.DBName,
  lbs.LastBackedUpOn,
  lbs.[user_name]
FROM 
  LatestBackupSet AS lbs
WHERE
  DATEDIFF(DAY,lbs.LastBackedUpOn ,CURRENT_TIMESTAMP) = @LastXDays  
  AND lbs.Rnk = 1
ORDER BY 
  lbs.DBName DESC
GO


To get a list of the Latest successful backups of all Databases



;WITH LatestBackupSet AS (
SELECT 
  b.machine_name,
  b.server_name,
  b.database_name as DBName,
  b.backup_start_date,
  b.backup_finish_date,
  CASE 
    WHEN b.[type] = 'D' THEN 'Database'
    WHEN b.[type] = 'I' THEN 'Differential database'
    WHEN b.[type] = 'L' THEN 'Log'
    WHEN b.[type] = 'F' THEN 'File or filegroup'
    WHEN b.[type] = 'G' THEN 'Differential file'
    WHEN b.[type] = 'P' THEN 'Partial'
    WHEN b.[type] = 'Q' THEN 'Differential partial'
    ELSE b.[type]
  END Backup_Type,
  b.expiration_date,
  b.[user_name],
  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
  b.recovery_model,
  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
  bf.physical_device_name as Location,
  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
FROM 
  msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
  ON b.media_set_id=bf.media_set_id
)
SELECT 
  machine_name,
  server_name,
  DBName,
  backup_start_date,
  backup_finish_date,
  Backup_Type,
  expiration_date,
  [user_name],
  Total_Time_in_Minute,
  recovery_model,
  Total_Size_GB,
  Location
FROM 
  LatestBackupSet AS lbs
WHERE
  lbs.Rnk = 1
ORDER BY 
  lbs.DBName DESC
GO


To get the Latest successful backup of a particular Database



DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'
 
;WITH LatestBackupSet AS (
SELECT 
  b.machine_name,
  b.server_name,
  b.database_name as DBName,
  b.backup_start_date,
  b.backup_finish_date,
  CASE 
    WHEN b.[type] = 'D' THEN 'Database'
    WHEN b.[type] = 'I' THEN 'Differential database'
    WHEN b.[type] = 'L' THEN 'Log'
    WHEN b.[type] = 'F' THEN 'File or filegroup'
    WHEN b.[type] = 'G' THEN 'Differential file'
    WHEN b.[type] = 'P' THEN 'Partial'
    WHEN b.[type] = 'Q' THEN 'Differential partial'
    ELSE b.[type]
  END Backup_Type,
  b.expiration_date,
  b.[user_name],
  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
  b.recovery_model,
  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
  bf.physical_device_name as Location,
  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
FROM 
  msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
  ON b.media_set_id=bf.media_set_id
WHERE
  b.database_name = @DBName  
)
SELECT 
  machine_name,
  server_name,
  DBName,
  backup_start_date,
  backup_finish_date,
  Backup_Type,
  expiration_date,
  [user_name],
  Total_Time_in_Minute,
  recovery_model,
  Total_Size_GB,
  Location
FROM 
  LatestBackupSet AS lbs
WHERE
  lbs.Rnk = 1
ORDER BY 
  lbs.DBName DESC
GO


To get a list of Databases that were backed-up and do not currently exist



SELECT
  DISTINCT b.database_name
FROM
  msdb.dbo.backupset b
WHERE
  DB_ID(b.database_name) IS NULL

Hope, the above given script will be of help to you. Also, I would like to request you to please add any relevant script which you feel would be useful as a comment.

Posted by at 3 comments:
Labels: , , , , ,
Subscribe to: Posts (Atom)