SQL Serverous

SQL Serverous

Alan Wood  //  Professionally: SQL Server consultant
Personally: Geek, musician, sports fan (avid follower of Motherwell in the SPL and the 49ers and Dolphins in the NFL. Also follow the SF Giants in MLB).

Jan 7 / 7:18am

Column headings do not repeat in SSRS 2008

I've just stumbled upon an odd issue with the Tablix control in SSRS 2008.  I deployed a report on a server a few weeks ago and it is now reporting on enough data to make it span multiple pages.  I wanted the column headings to appear on all pages, not just the first.  However, when I set the RepeatColumnHeaders property on the Tablix to be true, the headings still did not appear on the next page.  After a bit of playing around I enabled Advanced Mode in the Grouping Pane, and had a look at the properties for the top (Static) group in Row Groups.  Under Other, there is a property called RepeatOnNewPage.  Setting this to True made the column headers appear as required.  OK, so that's fine, but I can't help but think that this a a bug in SSRS 2008, and should be fixed by MS.  Looking through the RDL file itself, I can't find anything that would cause the RepeatColumnHeaders property not to behave as expected.

The "Header should remain visible while scrolling" option does not appear to work either; I haven't yet found a workaround for this.

Loading mentions Retweet
Filed under  //  bugs   Reporting Services   SSRS  

Comments (5)

Jan 7 / 4:19am

Formatting Dates in SSRS

In Reporting Services, formatting dates can become a headache for the developer, often due to differing regional settings on servers, development boxes etc.  Therefore I've found it much more useful to force the report to use a particular format, rather than allow the server settings to decide for me.  Previously I would have used FormatDateTime in the expression, as shown here:

=FormatDateTime(Fields!DateStamp.Value, DateFormat.ShortDate)

Depending on the regional settings, this could return either 12/3/2008 or 3/12/2008.  When designing the report, I want to know exactly what the date will look like, no matter where it is deployed.  So I instead use the Format function to allow me to choose the format I require.  Some examples:

=Format(Fields!DateStamp.Value, "dd-MMM-yy")
returns 03-Dec-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy")
returns 03-Dec-2008

=Format(Fields!DateStamp.Value, "dd-MMMM-yy")
returns 03-December-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy H:mm")
03-Dec-2008 16:44

This method removes a lot of the issues regarding date formatting, and means there are no shocks or questions when it comes to deployment time!

Edit: Setting the Language property of the report to your locale will also ensure that dates in your report parameters will have the correct format.  This is something that is often overlooked.

Loading mentions Retweet
Filed under  //  Dates   Formatting   Reporting Services   SSRS  

Comments (0)

Dec 23 / 3:45am

New SQL Server blog

I'll be posting any SQL Server related stuff in this new blog.  I may even try to make it vaguely interesting!  And I promise not to post my daily "I hate SSIS!"/"I love SSIS!" thoughts here.  :)

Don't expect any content in the next two weeks, as I'll not be working and I hope to have a wee break from databases for the duration.

But let's make the intro post useful; go get the new SQL Server Cribsheet from Red Gate.  It's a free e-book, well worth the download.  Link below...

SQL Server Cribsheet

Loading mentions Retweet
Filed under  //  books   sql server  

Comments (0)