SSRS code examples

I use SQL Server 2005 Report Services (SSRS) quite a bit. I thought I'd share some code examples I keep handy.


First day of Month
To get the first day of the current month:
=CDate(Month(Today).ToString() & "/1/" & Year(Today).ToString())


Last day of Month
To get the last day of the current month (use first day of the next month and subtract one):
=DateAdd("d",-1,DateAdd("m",1,CDate(Month(Today).ToString() & "/1/" & Year(Today).ToString())))


Page number
Simple page count, easy to do but handy to have a copy of it around. I usually put this and the global execute time in the footer of the report:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages


Global Execution time
Good for displaying in the footer in case it gets printed - the person looking at the report then knows when the report was ran.
=Globals!ExecutionTime


Clean URL for a direct link to a report
If want to provide a direct link to a report most people usually end up copying it from the Address bar in their web browser. However, that address usually has a lot of extra characters such as %20. I've found an address that can provide a somewhat cleaner link. Use the example below (substitute your own servername and Report+Name.

Example of a easy to paste direct link to a report (notice no %20, etc).
http://servername/ReportServer/Pages/ReportViewer.aspx?/Reports/Report+Name


These are pretty simple examples, but I can't remember how many times I've thanked myself for keeping them around in a small text file.

Scripting Database Objects from MS SQL Server 2000 to 2005 for Subversion

Our development team keeps track of Database objects in our environment by scripting changes from SQL Server to a text file, then using a Subversion repository to keep track of changes. The process works pretty well for our team size, and Subversion provides us with a fairly good history of each database object.

We started this process back in SQL Server 2000 using the scripting utilities provided to generate the text files, using their file name convention. However, after we upgraded to SQL Server 2005, Microsoft changed the file naming convention of the text files. I’ve included a small table showing some of the differences.

Database Object	SQL 2000	SQL 2005
View	dbo.ViewName.VIW	dbo.ViewName.View.sql
Table	dbo.TableName.TAB	dbo.TableName.Table.sql
Table Trigger	dbo.TriggerName.TRG	TriggerName.Trigger.sql
Stored Procedure	dbo.StoredProcName.PRC	dbo.StoredProcName.StoredProcedure.sql

The filename change was a big thorn in our side. Subversion was tracking the files by filename and now all the filenames were going to change! At first we toyed with the idea of writing our own code to export the objects using the old naming convention, but we didn’t want to have to maintain yet another program. In the end we decided we were just going to have Subversion rename all the files accordingly.

Since we’re mostly doing our development on Windows, we use TortoiseSVN (http://tortoisesvn.tigris.org/), which is a great tool. However, we had around 600+ script files to rename and I didn’t feel like individually renaming each. Instead, I decided to write a small program to break down each file, determine what type of object it was, and then call “svn rename” with the appropriate change.

To do this, I had to download the Subversion client tools for Windows (http://subversion.tigris.org/project_packages.html). After I had it installed and the command line tools were working, I wrote a small C# program in Visual Studio to do the work. I never formally finished the program; instead I simply used it in debug mode so that I could step through to watch the process in detail (It only had to work once).

The end result was a bunch of renamed script files that had their revision history kept intact. I’m not sure if this was the best way to go about solving the problem, but it worked. I thought my code and comments might be useful for someone else who has a similar problem. This code is free and comes with no guarantees. Good luck.

Download the code here

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;
using System.IO;


namespace RenameSVNFiles
{
class Program
{
static void Main(string[] args)
{

Console.WriteLine("Begin SVN Rename Script");

//Set to your directory
String sDir = "D:/Projects/dbscripts";

DirectoryInfo sourceFiles = new DirectoryInfo(sDir);
FileInfo[] destFiles = sourceFiles.GetFiles("*.*");

String sOrigFileName = "";
String sNewFileName = "";
String strCmdLine = "";
String sOrigFileExt = "";
int iFileExtLength = 0;
int iModifiedCount = 0;

//Declare and instantiate a new process component.
System.Diagnostics.Process process1 = new System.Diagnostics.Process();
process1.StartInfo.WorkingDirectory = sDir;

foreach (FileInfo fi in destFiles)
{

sOrigFileName = fi.Name;

//Find extension length and File name (assumes syntax of filename.extension)
iFileExtLength = sOrigFileName.Length - sOrigFileName.LastIndexOf(".");
sOrigFileExt = sOrigFileName.Substring(sOrigFileName.LastIndexOf("."), iFileExtLength);

//change each file's extention to the new naming standard
switch(sOrigFileExt)
{
case ".VIW":
sNewFileName = sOrigFileName.Substring(0, sOrigFileName.LastIndexOf(".")) + ".View.sql";
iModifiedCount++;
break;
case ".TAB":
sNewFileName = sOrigFileName.Substring(0, sOrigFileName.LastIndexOf(".")) + ".Table.sql";
iModifiedCount++;
break;
case ".TRG":
sNewFileName = sOrigFileName.Substring(0, sOrigFileName.LastIndexOf(".")) + ".Trigger.sql";

//Triggers do not have the "dbo." at the beginning of the file anymore - remove them
String tmpStr = "dbo.";
char[] trimChars = tmpStr.ToCharArray();
sNewFileName = sNewFileName.TrimStart(trimChars);
iModifiedCount++;
break;
case ".PRC":
sNewFileName = sOrigFileName.Substring(0, sOrigFileName.LastIndexOf(".")) + ".StoredProcedure.sql";
iModifiedCount++;
break;
default:
//default - skip file
continue;
}

strCmdLine = "rename " + sOrigFileName + " " + sNewFileName;

try
{
process1.StartInfo.FileName = "svn.exe";
process1.StartInfo.Arguments = strCmdLine;
process1.StartInfo.CreateNoWindow = true;
process1.Start();
process1.WaitForExit();
}
catch (Exception ex)
{
Console.WriteLine(
"Exception Occurred :{0},{1}",
ex.Message, ex.StackTrace.ToString()
);
Console.WriteLine("While trying: " + sOrigFileName + " -> " + sNewFileName);
}

Console.WriteLine(sOrigFileName + " -> " + sNewFileName);
}


process1.Close();

Console.WriteLine("");
Console.WriteLine("");
Console.WriteLine("Number of files in directory: " + destFiles.GetLength(0));
Console.WriteLine("Number of files modified: " + iModifiedCount);
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
}
}
}