Repeat header rows on each page fixed

You checked "Repeat header rows on each page" in Tablix Properties dialog box and it does not work as intended.
Here you can find explanation how to overcome this issue.

List, Table and Matrix controls are templates of the same underlying Tablix Data Region control that was introduced in SSRS 2008:

• Table template provides ability for displaying detailed or grouped data.
• List template provides ability to display detailed data. It also enable developers to customize layout that could look differently from the table's one. It could also use nested lists.
• Matrix template provides ability for displaying grouped data with summary information. Columns and Rows in matrix template could be considered as SSAS dimensions. But Matrix restricts them to two dimensions per Matrix only.

That's why we see Tablix properties dialog box each time when requesting properties dialog box on List, Table or Matrix controls.

In order to make row header repeatable on each page follow steps below:

• When in designer enter Advanced Mode

• After entering advanced mode you are now able to see and adjust static row's properties.

• The property you need to adjust is RepeatOnNewPage. Set it and static row (table header) will be repeated on every page.

• Now row header will appear on every page.

"Header should remain visible while scrolling" behavior is controlled by row header static row's FixedData property

You can also adjust group row header visibility settings which appears in the Row Groups section under corresponding group using KeepWithGroup property.

But wait a minute. What do those properties on the Tablix properties dialog control do?! Repeat row headers and repeat column header options on the tablix dialog only works if there is a row header area or column header area in your report. You can identify a row header area and column header areas by the double dashed lines that separate these areas from the body of the Tablix. Thus making them usable only when Matrix template is used.

PS: I read about manually updating report's underlying XML structure when you want to get the same functionality but I would not advise you to do it until you are aware of what you are doing.


Regards,
Oleh.

Report Parameter Default Values

While developing SSRS reports I almost always define default report parameters in case I need to fix some issue with layout or data itself. It saves some time as I don't have to fill report parameters each time I want to preview report with new changes applied.
But after report is deployed to production report server and users start using it default parameters are not that handy there. Especially with heavy statistics reports (that takes more than several seconds to process request) when report engine starts processing request at once using default parameters. Almost in all cases default parameters does not match user's. Nobody wants to wait extra before submitting desired input parameters especially when "extra" is several minutes long.


So don't forget to empty default parameters prior to deploying report or if deployed empty them and redeploy.

PS: And don't forget to update report subscriptions as well in case you have some.


Regards,
Oleh

Alternating background colors for odd and even tablix rows

In this blog post I will describe how you can add alternating background colors to SSRS tablix odd and even rows.

What do you need to know is that expression evaluation rules in RS 2008 have changed as a result of the new on-demand processing model. As a result, state in class-level instance variables is discarded as you page through the report which makes maintaining state trickier between page requests. However, RS 2008 introduces report variables that guarantee one-time evaluation semantics.

How we can reuse it to accomplish our goal:

• Create a function that will update _evenRow flag state each time it's called.

Open report properties box -> Code tab -> Add next code:

Public _evenRow As Boolean

Public Function EvenRow() As Boolean

 _evenRow = Not _evenRow

return _evenRow

End Function

• Create a variable within Row group and set the expression to call EvenRow function. Since it will be evaluated one time per each row it's what we need.

• Set row background color depending on variable state.

=iif(Variables!EvenRow.Value, "LightGrey", "Transparent")

• and finally the outcome

Regards,
Oleh

Report Localization

In case you are interested in localizing SSRS reports you should definitely take into account approach with using hidden report parameters as described in this blog post - Laser Guided Missiles (Report Localization Though Parameters) .


Happy localizing :)

Regards,

Oleh

SSRS Resources

Hi there,

In this post I'll provide a list of resources that I find helpful to learn and resolve SSRS issues.

Beginner guides:

• Beginning SQL Server 2005 Reporting Services Part 1 by Simple-Talk
• Beginning SQL Server 2005 Reporting Services Part 2 by Simple-Talk
• Beginning SQL Server 2005 Reporting Services Part 3 by Simple-Talk
• Beginning SQL Server 2005 Reporting Services Part 4 by Simple-Talk
• MSDN Webcast: Introduction to SQL Server 2005 Reporting Services (Part 1 of 6) (Level 200) - Introduction
  
• MSDN Webcast: Introduction to SQL Server 2005 Reporting Services (Part 2 of 6) (Level 200) - Delivering Reports
  
• MSDN Webcast: Introduction to SQL Server 2005 Reporting Services (Part 3 of 6) (Level 200) - Report Builder
• MSDN Webcast: Introduction to SQL Server 2005 Reporting Services (Part 4 of 6) (Level 200) - Report Design
• MSDN Webcast: Introduction to SQL Server 2005 Reporting Services (Part 5 of 6) (Level 200) - Extensibility
• MSDN Webcast: Introduction to SQL Server 2005 Reporting Services (Part 6 of 6) (Level 200) - Management and Security
• MSDN Webcast: Intelligent Reporting: Using the Visual Studio 2005 Report Viewer Controls (Level 200)
• MSDN Webcast: Intelligent Reporting: Charting in Depth (Level 300)
• www.gotreportviewer.com
  

SSRS Bloggers:

• SQL Server Reporting Services Team Blog
• Lukasz Pawlowski, PM SSRS Team
• Bob Meyers, Dev Lead SSRS Team
• John Gallardo, Dev SSRS Team
• Chris Hays, Arch SSRS Team
• Chris Baldwin, PM SSRS Team
• Brian Welcker, GPM SSRS Team
• Robert Bruckner, Dev SSRS Team
• Russell Christopher, BI
• Teo Lachev, MVP
• James Wu
• Lev Semenets
• Chaitanya Medikonduri
• Tudor Trufinescu
• Steve Wright

Other:

• SSRS Dev Center
• SSRS 2005 MSDN forum
• SSRS 2008 MSDN forum
  

Hope this helps somebody.
Regards,
Oleh

Printing ServerReport in background

Recently I wanted to print a server report presented with a ReportViwer component in a windows forms application without showing any GUI. Another thing was to investigate the feasibility of enabling automatic printing server reports capabilities in background from within an application providing printing preferences via ApplicationSettings.

ReportViewer source code investigation using Reflector showed that it provides only PrintDialog() public API that will show a modal PrintDialog dialog thus requiring user interaction.
Later I found a www.gotreportviewer.com site with an example of local report printing without showing any GUI using LocalReport.Render() method. ServerReport does not provide such method so I adapted the code for printing ServerReport in background.
First page is rendered using ServerReport.Render() public API. It will return streamIds list for each page that report consists of and that could be used to render all other pages via ServerReport.RenderStream() public API.

string sExtension;
string sEncoding;
string sMimeType;
string[] sStreamIds;
Warning[] warnings;
// render into EMF — one page per one stream
// render first page and get streamIds for the rest pages
byte[] bt = report.Render(”Image”
, GetDeviceInfo()
, out sMimeType
, out sEncoding
, out sExtension
, out sStreamIds
, out warnings);
SaveToStream(bt);
// iterate through each streamId and save
foreach (string sStreamId in sStreamIds)
{
bt = report.RenderStream(”Image”, sStreamId, GetDeviceInfo(), out sMimeType, out sEncoding);
SaveToStream(bt);
}

Image Device Info tips:

1. Try to provide paper dimensions that correspond to report’s or adjust reports one to papers’. Otherwise report will be stretched across the paper sheet.
2. StartPage default value is 1 and EndDate equels StartDate - so do not surprise is only first page is printed.

Everything above said developed into:

using System;
using System.Collections.Generic;
using System.Drawing.Printing;
using Microsoft.Reporting.WinForms;
using System.IO;
using System.Drawing.Imaging;

namespace ServerReportPrinting
{
class Program
{
#region Class Fields
///

/// Defines current page that is being printed.
///

private static int m_nCurrentPageIndex;
///

/// List of streams for storing rendered report pages.
///

private static IList<Stream> m_streams = new List<Stream>();
#endregion

#region Class methods
static void Main(string[] args)
{
PrintReport();
}
#endregion

#region Class Utility methods
private static void PrintReport()
{
ExportToEMF();
// export to EMF was successfull
// proceed with printing
Print();
}
///

/// Exports report representation into EMF format.
///

private static void ExportToEMF()
{
// initialize ReportViewer
ReportViewer rptViewer = new ReportViewer();
rptViewer.ServerReport.ReportServerUrl = new Uri(@"http://localhost/reportserver", System.UriKind.Absolute);
//rptViewer.ServerReport.ReportPath = @"Here goes server report path";
rptViewer.ProcessingMode = ProcessingMode.Remote;
ServerReport report = rptViewer.ServerReport;
// print
string sExtension;
string sEncoding;
string sMimeType;
string[] sStreamIds;
Warning[] warnings;

// render into EMF -- one page per one stream
// render first page and get streamIds for the rest pages
byte[] bt = report.Render("Image"
, GetDeviceInfo()
, out sMimeType
, out sEncoding
, out sExtension
, out sStreamIds
, out warnings);
SaveToStream(bt);

// iterate through each streamId and save
foreach (string sStreamId in sStreamIds)
{
bt = report.RenderStream("Image", sStreamId, GetDeviceInfo(), out sMimeType, out sEncoding);
SaveToStream(bt);
}

// set stream positions to 0
foreach (Stream stream in m_streams)
stream.Position = 0;
}
///

/// Creates a memory stream that will store rendered report definition.
///

private static void SaveToStream(byte[] bStr)
{
Stream fs = new MemoryStream();
fs.Write(bStr, 0, bStr.Length);
m_streams.Add(fs);
}
///

/// Create print document and print it.
///

private static void Print()
{
// print document
if (m_streams == null || m_streams.Count == 0)
return;

PrintDocument printDoc = new PrintDocument();
if (!printDoc.PrinterSettings.IsValid)
{
Console.WriteLine("Can't find printer");
return;
}

printDoc.PrintPage += new PrintPageEventHandler(PrintPage);
printDoc.Print();
}
private static string GetDeviceInfo()
{
return "<DeviceInfo>"
+ " <OutputFormat>EMF</OutputFormat>"
+ " <StartPage>1</StartPage>"
+ " <EndPage>1</EndPage>"
+ " <PageWidth>8.5in</PageWidth>"
+ " <PageHeight>11in</PageHeight>"
+ " <MarginTop>0.25in</MarginTop>"
+ " <MarginLeft>0.25in</MarginLeft>"
+ " <MarginRight>0.25in</MarginRight>"
+ " <MarginBottom>0.25in</MarginBottom>"
+ "</DeviceInfo>";
}
///

/// Handles page printing.
///

private static void PrintPage(object sender, PrintPageEventArgs ev)
{
Metafile pageImage = new Metafile(m_streams[m_nCurrentPageIndex]);
ev.Graphics.DrawImage(pageImage, ev.PageBounds);

m_nCurrentPageIndex++;
ev.HasMorePages = (m_nCurrentPageIndex  < m_streams.Count );
}

       #endregion

}

}

Ensure that you have a report deployed to your report server.
Hope this helps somebody.
Regards,
Oleh