Monday, February 15, 2010

SSRS Page Sizes and Orientations

One of the things I found odd about Reporting Services when I first used it was the lack of an option to set the paper/page size to common paper sizes such as Letter, A4, A3, etc. This is common in some other reporting tools such as Crystal Reports.

In SSRS page sizes are set by PageSize property of the Report object. You can get to this property from the Report Properties option in the Report menu. From here just look on the Layout tab and you'll find what you need. The measurements will either be in cm or inches (depending upon the Regional Settings of your computer). The most common paper sizes you will need to print to are Letter and A4. The values for these two (in potrait orientation) are

Letter: 8.5in, Height 11in
A4: Width 21cm, Height 29.7cm

If you need to use Landscape orientation simply swap the width and height values.

You can also set the size of the four margins here as well.

In order to maximise the amount of report real estate you have to work with you should also set the Size property of the [Report] Body object to the following:

[Body] Width = [Report] Width - Left Margin - Right Margin
[Body] Height = [Report] Height - Top Margin - Bottom Margin

Accurately setting these values will allow you to make the most of the available space without getting print overruns on to the next page or finding every second page blank due to a page size which has been set slightly too wide.

Friday, February 12, 2010

Querying Data for Report Parameters

Often there's a need to dynamically get data to populate the list of options for a report parameter. There are a couple of ways to go about this. One I often see is to query a transaction table to get a distinct list of items to appear in a drop down parameter. Something along the lines of

SELECT DISTINCT city FROM orders
ORDER BY city

In my opinion this is a bad idea for two reasons:

  1. It places unneccessary load on the database. Querying thousands, maybe even millions, of rows to return a handful of rows might not be the best idea if there is a reference table (perhaps just listing cities or even listing all customers - with city information as part of the customer's address). The situation is even worse if the column being queried from the transaction table isn't included in a suitable index.
  2. Looking at a transaction table for this information will only return a resultset for those items which have been involved in transactions. If it's important for the report user to be able to query against any city then there's a problem!

So, whereever possible I advocate querying small reference tables to populate report parameters. Look at the data model as a whole to try and find suitable tables and ask questions of key super-users, DBAs, application developers if need be - you might be suprised to find there's a table you can use - it just may be that it was named in a way that didn't make its purpose obvious.

Friday, February 5, 2010

The Logical Operator Less Used

Most people who need to work with SQL Server databases will be familiar with the basic logical operators such as AND, OR, BETWEEN, IN. A good number might also be across the use of the EXISTS operator. Perhaps less known are three others ALL, ANY and SOME.

ALL will return true if all of a set of comparisons are true.
ANY will return true if any of a set of comparisons are true.
SOME will return true if some of a set of comparisons are true.

There are numerous areas where these operators can be used. One case is where there is the need to compare a value against a range of other values from . It can take away the need for a string of ANDs and ORs in WHERE clause.

The syntax is along the lines of:


IF @valueToCheck <> ALL (SELECT referenceValue FROM referenceTable)
-- do something
ELSE
-- do something else

In this case the first code branch will be executed only if the valueToCheck is less than all of the values in the reference table.

Monday, February 1, 2010

New Behaviour When Modifying SQL Server tables from Management Studio

Today I struck some behaviour I'd not seen before. I was attempting to change the nullability of a column from inside Management Studio when up popped a message with the following text:


Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that requie the table to be re-created.

Hmm, now I realise that this type of change needs the drop and re-create of the table, but I'm sure in earlier versions of SQL Server this type of change was possible from the designer with the extra work being taken care of silently in the background. Now, in 2008, it seems to be blocked entirely - ideally I'd have liked to be able to make the change in the designer and then generate the change SQL script from there rather than having to write the script myself from scratch.

So, I did a little digging looking for the option mentioned in the error mesage. I found it under the Tools->Options menu in the Designers section.




Simply uncheck the box and you'll be able to make these types of table changes and / or generate the change scripts from Management Studio.