Tag Archives: formatting

Simple Field Level Formatting in SSRS

Posted on by

Sometimes

Sometimes, just having the right information in a report doesn’t completely fill the requirement. You can enrich the content by formatting items in your report using some simple formatting codes. Follow these steps:

- Right-click the field(s) you want to format
- Choose Properties & navigate to the Format tab. **
- Click on the ellipsis (…) on the right of the Format code text box.

Setting Formatting properties

Setting Formatting properties


- Click the Standard radio button and select a basic format from the list available – an example will be provided in the right-hand box.

Choose the required format

Choose the required format


If you need to further tweak the format, click on the Custom radio button and enter you can use the following codes to do so:

Formatting Codes and examples

Formatting Codes and examples

Example below:

Formatting a Date & time field

Formatting a Date & time field


** Or select the cell you want to format and then edit the Format property in the Property pane (usually located on the right of the design environment).


Create Your Own Reporting Services Templates

Posted on by

Haven’t I done this before?

As a Reporting Services Developer you may have found yourself completing the same tasks over and over for each new report that you create. To alleviate this somewhat, you may have just done a quick ‘Copy & Paste’ of an existing report, renamed it and then modified all the elements within it. The trouble with doing this is that there are always a number of properties/expressions that may have been used in the copied report that tend to be obscure (such as dynamic sorting on columns in a table) and you end up having to hunt down a number of errors and fix them one by one.

It is much better/easier/faster/less frustrating (& should probably be labelled under ‘Best Practices’) to create a number of SSRS templates of your own that you can use over and over again in each of your projects. It may be a simple template that includes a header & footer formatted to fit either landscape or portrait page layouts (optimally sized for printing), with text/backgrounds formatted to the way you want then. It may also be as complex as you like, adding in pre-formatted tables/matrices, charts, etc. I have found creating my own SSRS templates to be incredibly useful and highly recommend it. I have made two basic templates available here (right-click>save as…). It is a zip file containing two .RDL files, 1 Landscape template, 1 Portrait template. Unzip the file to the directory indicated in the steps below to have access to them through BIDS. Note – these are for SSRS 2005.

Here’s how to create your own templates:
1. Open or create a new project and create a new report item.
2. Design the report to your specification, as described above (& see Fig 1. below)

Fig 1. Standard Design Template

Fig 1. User Designed Template

3. Save the report
4. Navigate to the folder where the .rdl file (that has just been saved) is stored.
5. Cut/Copy the file to the following location (giving the file a useful template-friendly name):

C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesProjectItemsReportProject

This is the default location of the templates folder. If you have installed Reporting Services on a different drive/directory update these references to reflect the different Drive/Directory. Now, to access the report template from any project, go back to BIDS, right-click the ‘Reports’ folder (in the Solution Explorer) in your project and choose Add>New Item

Fig 2. Add New Item Menu

Fig 2. Add New Item Menu


and you will see your new templates under the heading ‘Visual Studio installed Templates’.

Fig 3. Select the New User Designed Template

Fig 3. Select the New User Designed Template

Centralized Style Sheets for your SSRS Reports

For those of you who would like to take the standardization of Report Design further, there is a great article over at SQL Server Central that describes how to set up Style Sheets for Reports. This means that if a change needs to be made to the design (formatting) of an entire suite of reports, then it can be done in one place and propagated across all of your reports. I highly recommend this article.

Have you found any other innovative ways of reducing the time it takes to produce reports?

Let’s hear from you.


Designing SSRS Reports that are Optimized for Printing

Posted on by

Designing a report so that, when printed, is not spread across multiple pages is a matter of setting your page properties and laying out the report within the ‘page’ boundaries. Follow these simple steps to ensure that your runtime report is always just right for printing. Note – the examples below are settings for A4 printing.

  1. With the design tab open, right-click the white space outside of the design area. This is raise the menu in Fig 1.

    Fig 1. Selecting Page Properties

    Fig 1. Selecting Page Properties

  2. Choose Properties
  3. This will open the Report Properties dialog box. Choose Layout (Fig 2.)

    Fig 2. The Page Properties Dialog box

    Fig 2. The Page Properties Dialog box

  4. Default layout is portrait. Switch the Page Width & Page Height values for Landscape mode.
  5. If you want more real estate to work with on the page I suggest decreasing the margins to 0.5cm
  6. Click OK.
  7. Now resize the design area to the limits you have set in the properties dialog box. I.e. Pull the right edge of the ‘page’ to the 20 (Fig 3) tick mark and the bottom edge to the 28 tick mark. Note – if you are including a Header/Footer, then you will need to cater for these by decreasing the bottom edge to about 25 (Fig 4). These settings are for Portrait mode, switch the height and width for Landscape.
    Fig 3. Setting the design surface width

    Fig 3. Setting the design surface width

    Fig 4. Setting the design surface height

    Fig 4. Setting the design surface height

Inserting Page-breaks

To create a page break manually in a report:

  1. Place a rectangle item onto the design area at the place where you would like to insert a break.
  2. Right click on the rectangle item and choose Properties.
  3. Check the appropriate box on the General Tab for the Page Break settings (Fig 5)

    Fig 5. Setting the page break property

    Fig 5. Setting the page break property

This will create a page break in the report at the point you specify. If you do not want to see the rectangle in you report, set the border properties to None.

.

Managing Reporting Services Layouts using the Rectangle in SSRS

Posted on by

Left a bit, right a bit

When it comes to designing one page summary dashboards for print (A4), you need to be able to ensure that the layout of your report is constant. Even with fluctuating columns & rows – care of Tables & Matrices. Initially this sounds fairly simple, add the report items, lay them out according to the design requirement and off you go. Preview. Whoa. WTH!

The problem arises when position of items on the report is affected by the growth of either a table or a matrix, either on the horizontal or vertical axis. Specifically, if the top one of your report items (the chart in Fig 1.) is below the bottom of another item (that can grow vertically), then it will be pushed out at run time. See fig 2.

Fig 1. The Design time Layout

Fig 1. The design time layout

Fig 2. The Runtime Layout

Fig 2. The run time layout

Have no fear, the rectangle is here!

Enter the simple rectangle report item. By using rectangles to layout your report and group certain items, you can retain the layout you worked so hard to achieve. How does it does this? The rectangle groups report items within itself, i.e. the growth of tables & matrices are relative to the rectangle and not the page itself. By adding two rectangles to the report I have grouped the Pie chart title and the pie chart, and have isolated the table. The table’s rectangle is also extended to the approximate depth of table at run time, thus ensuring that the planes of the report items relative to the design surface (the two rectangles) are the same.

Let’s have a look at the difference from Fig 1. & 2. In the Figs. below.

Fig 3. The design time layout using the rectangle report item

Fig 3. The design time layout using the rectangle report item

Fig 4. The runtime layout using the rectangle report item

Fig 4. The run time layout using the rectangle report item

But beware…

One important bit of information. You must ensure that the report items you wish to control are placed ‘into’ the rectangle. What I mean here, is that you cannot simply place a rectangle object over the report items you wish to constrain – this will give rise to overlapping objects and may result in error. The trick is to either place the rectangle on the design surface first and then drag-and-drop report items ‘into’ it, or for existing report items, place the rectangle onto the design surface and then cut the report items you wish to constrain, select the rectangle, and then paste the report items ‘into’ it. You can test that the report items are ‘inside’ the rectangle by selecting it (the rectangle) and moving it. If the report items (inside the rectangle) all move together, you have constrained them correctly.

Another note. The rectangle will not stop the growth of the tables & matrices, what it does is ensure that the layout of the rest of the report items are not thrown out at run time.

The data in the report is from the AdventureWorks database and is for illustration purposes only.

.