Setting page name in report

One of the advantages of using Reporting Services with SQL Server 2008 R2 or later is that you can get sheet names for Excel when exporting.
In my case I needed to export an excel file with different companies' names on the sheet name.

For this you first need to create a row group on the field you need. Then go to Row Groups, select the group like in the picture below:

Go to the properties of that group. You should see something like this:

Set the BreakLocation as you need it and the field name for the PageName.

Run your report and export it to excel. The sheet names will no longer be Sheet1, Sheet2, etc.

Render file from ReportViewer connected to Reporting Services

In this post I will show how you can use a report viewer control to connect to a Reporting Services server and call the Render function on the ReportViewer.
This is useful when we don't want to show the control, but we need to get the rendered report in different formats.

Step 1: download the Report viewer runtime from here.

Step 2: Install the runtime.

Step 3: Add the reference to your project. The dll should be in \Program Files (x86)\Microsoft Visual Studio 11.0\ReportViewer\Microsoft.ReportViewer.WinForms.dll

First we create a method that will return the report from the report viewer as an array of bytes.

public byte[] RenderFromServer()
{
ReportViewer rv = new ReportViewer();

// Set the processing mode for the ReportViewer to Remote
rv.ProcessingMode = ProcessingMode.Remote;

rv.ServerReport.ReportServerUrl = new Uri("Http://mypc/ReportServer);
rv.ServerReport.DisplayName = "New report.pdf";
rv.ServerReport.ReportPath = "/Reports/MyReport";

//set credentials
ReportServerCredentials rsCredentials = rv.ServerReport.ReportServerCredentials;

NetworkCredential nc = new NetworkCredential();
nc.Domain = "myDomain"; //can also be the computer name for local access
nc.UserName = "myUser";
nc.Password = "12333";

rsCredentials.NetworkCredentials = nc;

//set parameters
List<ReportParameter> paramLst = new List<ReportParameter>();
ReportParameter rp = new ReportParameter(ParamName, ParamValue);
paramLst.Add(rp);
                        rv.ServerReport.SetParameters(paramLst);

//get the bytes from the report viewer
Warning[] warnings;
string[] streamids;
string mimeType;
string encoding;
string extension;

byte[] bytes = rv.ServerReport.Render
("PDF", null, out mimeType, out encoding, out extension, out streamids, out warnings);

rv.Dispose(); //very important for memory management!!
return bytes;
}

Notice that I called the Render function with the "PDF" paramater. The full list can be found at http://msdn.microsoft.com/en-us/library/ms154606.aspx.

The possible values are:

• PDF
• CSV
• IMAGE
• PDF
• EXCEL
• WORD
• HTML 4.0
• MHTML

When exporting to MHTML and you need to embed the report in another html document, or the body of an email, you have to specify the DeviceInfo details:

<DeviceInfo>
   <HTMLFragment>True</HTMLFragment>
</DeviceInfo>

As stated in MSDN, "When a report is rendered as an HTML fragment, the content of the report is contained within a TABLE element without the use of an HTML or BODY element. You can use the HTML fragment to incorporate the report into an existing HTML document."

Link: http://technet.microsoft.com/en-us/library/ms155397.aspx

Then we can write the bytes to a file:

byte[] bytes = RenderFromServer();
File.WriteAllBytes("C:\\MyReport.pdf", bytes);

Or, make a memory stream from them:

byte[] bytes = RenderFromServer();
MemoryStream ms = new MemoryStream(bytes);
ms.Seek(0, SeekOrigin.Begin);

Default values for date parameters in SSRS

I will create a report that will have 3 parameters:

Interval: type: int with the following available values:

StartDate: type: DateTime; this will have different default values depending on the value of the Interval parameter.

The default values for the StartDate are:

For this week (first day of current week, Monday)
DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())

For last week (first day of last week, Monday)

DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

For current month
DateSerial(Year(DateTime.Now),Month(DateTime.Now),1)

For last month
DateSerial(Year(DateTime.Now),Month(DateTime.Now)-1,1)

The DateSerial function automatically generates the correct date also in January. If the value for the month = 0 then it sets month 12 of the previous year (year is also changed).

For last quarter: 
DateAdd("q",DateDiff("q","1/1/1900",today)-1,"1/1/1900")

For last year
DateSerial(Year(DateTime.Now)-1,1,1)


We set the default values using the switch function:


switch(Parameters!Interval.Value=1,DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())
Parameters!Interval.Value=2,DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today)),
Parameters!Interval.Value=3,DateSerial(Year(DateTime.Now),Month(DateTime.Now),1),
Parameters!Interval.Value=4,DateSerial(Year(DateTime.Now),Month(DateTime.Now)-1,1),
Parameters!Interval.Value=5,DateAdd("q",DateDiff("q","1/1/1900",today)-1,"1/1/1900"),
Parameters!Interval.Value=6,DateSerial(Year(DateTime.Now)-1,1,1)
)

EndDate: type: DateTime; this will have different default values depending on the value of the Interval parameter.

For this week (last day of current week, Sunday)
DateAdd("d" ,7- DatePart(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today())

For last week (last day of last week, Sunday)
DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

For current month
DateSerial(Year(DateTime.Now), Month(DateTime.Now)+1,0)

For last month
DateSerial(Year(DateTime.Now), Month(DateTime.Now),0)

If the day = 0, the date will become the last day of the previous month.


For last quarter: 
DateAdd("s",-1,DateAdd("q",DateDiff("q","1/1/1900",Today),"1/1/1900"))

For last year
DateSerial(Year(DateTime.Now)-1,12,31)


Again we set the default values using the switch function:


switch(Parameters!Interval.Value=1,DateAdd("d" ,7- DatePart(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today()),
Parameters!Interval.Value=2,DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today)),
Parameters!Interval.Value=3,DateSerial(Year(DateTime.Now), Month(DateTime.Now)+1,0),
Parameters!Interval.Value=4,DateSerial(Year(DateTime.Now), Month(DateTime.Now),0)
Parameters!Interval.Value=5,DateAdd("s",-1,DateAdd("q",DateDiff("q","1/1/1900",Today),"1/1/1900"))
Parameters!Interval.Value=6,DateSerial(Year(DateTime.Now)-1,12,31)
)

Alternating color for rows or columns in SSRS

I needed to display some reports with alternating colors for columns today. The report was a matrix report that had a column group that showed the months of the year.

After selecting the column go to properties and click on Fill -> Background color -> Expression



In the expression box type the following:

=iif(Fields!MonthNumber.Value mod 2,"Transparent","LightGrey")

 The same thing can be achieved for the rows. Instead of selecting the column, select the row of the table and follow the same steps.

Hope it will be usefull!