Monthly Archives: July 2009

Let the games begin!

Posted on by
Reply

If you haven’t noticed from my Twitter feed, I am a Formula 1 fan. I wasn’t always a fan but had followed it on and off for a few years – those years where a certain Ferrari driver won race after race, championship after championship, and it got a little boring. Then, in 2007, a young upstart entered the fray for McLaren and really stirred things up a bit. Heck, he almost won the world championship in his debut year. Lewis Hamilton came in 2nd to Ferrari’s Kimi Raikkonen.  Without the red cars winning every race, a great deal of tension throughout the season, and some awesome competition, I was taken with a sport that now entertains me every couple of weeks from March to November each year.

I like the way Lewis drives. I like the aggressiveness (although sometimes reckless) nature in the way he overtakes and defends for position. Yes, having a good car to race in helps a hell of a lot, demonstrated perfectly by Brawn GP, Red Bull & Toyota earlier this season.  (Perhaps I should have said “having Ross Brawn helps a hell of a lot”). Just look at the results. Now, however, the cars seem to be on a more level playing field. The Qualifying times for the Hungarian GP show that the top 10 times for Q1 were within just 0.1 seconds of each other. And just 1 second spread all 20 drivers. That’s a remarkably small amount. Compare this to last year’s qualifying in Hungary – the top 10 times for Q1 were spread over 1.2 seconds, with all 20 times being spread over nearly 3 seconds.

I have often wondered, & am probably not the only one, how Hamilton (or indeed Vettel) would compare to Michael Schumacher. As Schumacher retired from Formula 1 at the end of 2006, we have not had the opportunity to find out, and have had to rely on imagination and speculation. Until now.

With Felipe Massa’s horrible accident during qualifying at the Hungarian GP ruling him out of the European GP, and possibly the rest of the 2009 season, there is a need for another Ferrari driver. Speculation had been rife that Michael Schumacher may ask be asked to come back for a short time until Massa returns. This announcement confirms that he will indeed be Massa’s substitute.

I am thoroughly excited by this. It will shake things up a bit and we will finally get to see a number of great drivers (Schumacher/Alonso/Raikkonen/Hamilton/Button/Vettel/Webber among others…) competing with cars that are currently performing on a very similar level. I can’t wait. And I am sure that there are thousands more that feel the same way. If you weren’t enamoured by F1 before, this may change that.

A few quotes from the intertubz:

The BBC: “Lewis Hamilton against Michael Schumacher for the first time ever? Jenson Button up against Michael Schumacher? How will Kimi Raikkonen respond? It’s going to be fascinating.”

Eddie Jordan – “..I’m sure he [Bernie Ecclestone] has a little magic wand somewhere involved in this, because things just don’t happen…he has made this happen…”

The Telegraph: “Old green eyes is back. And boy is Formula One pleased to see him. Never mind Ferrari, Michael Schumacher is digging the sport out of a hole following BMW’s withdrawal from Formula One.”

The New York Times: “To return as a fill-in and not commit for a full season and run for the title could be something incredibly appealing to him”

Are you into Formula 1? What are your views on Schumacher’s return?

.

The CHOOSE() Function in SSRS

Posted on by
4

BOL’s description of the CHOOSE() Function: “Selects and returns a value from a list or arguments”. The syntax of the function is as follows:

=CHOOSE(index, expression_1 [, expression_2] [, expression_3] [, expression_4] …[, expression_n])

So, the Choose() function uses an Index (of type Double) to ‘choose’ one of a list of supplied values/expressions. For example;

=CHOOSE(Index, “Red”, “Yellow”, “Green”, “Grey”, “Black”, “White”)

When Index = 3, then the result is “Green”. When Index = 5, then the result is “Black”

Let’s look at a practical example:

I have a report (a simple chart at this stage) that relies on any one of a number of values to decide which metric to display – these values are provided to the user via a parameterized drop down list in the report, they simply choose which metric they want and the appropriate data is displayed. In the chart, I use an expression to decide on which metric is to be used based on the value returned by the parameter. Previously, I would have used an IIF() statement to do this, although I now have 7 values and this makes the IIF() statement far too ‘involved’.

Figure 1. shows the Metrics parameter and some of the values used:

Fig 1. Setting the Metrics Parameter Values

Fig 1. Setting the Metrics Parameter Values

The expression that is used in the chart (to get the data) is as follows:

=CHOOSE(Parameters!Metrics.Value, Fields!TotalItems.Value, Fields!OpenItems.Value, Fields!ClosedItems.Value, Fields!AveragePrice.Value, Fields!GrossProfit.Value)

Fig 2. Setting the Expression for the Data Values

Fig 2. Setting the Expression for the Data Values

When the user selects Total Items from the Parameter drop-down, a value of ‘1’ is passed to the CHOOSE() function which in turn selects Fields!TotalItems.Value as the data to use in the Chart Object. I have also used this expression to set the appropriate Series Label value.

.

Designing SSRS Reports that are Optimized for Printing

Posted on by
6

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
29

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.

.

The DateAdd() Function in SSRS

Posted on by
1

Getting acquainted

The DateAdd() function. According to BOL this “Returns a date to which a specified time interval has been added”. The structure of the function is as follows:

DateAdd(interval, number, date)

It’s a Date!

Like most first dates, getting to grips with this function and understanding its ‘needs’ is a tricky process. Initially, you may have attempted to use the tried-and-trusted methods (aka ‘syntax’), DateAdd(mm, 1, Now()). No. Ok, let’s try DateAdd(‘mm’, 1, now()). Nope. What about DateAdd(month, 1, Now()). Good luck with that…Yes. It’s a little mystifying.

Score!

There are a couple of ways to achieve the desired result with this function. 1.) use the DateInterval collection, or 2.) use the date part abbreviations. My recommendation? Use the DateInterval collection. Using the abbreviations, there is a chance that something will be calculated differently to what you’d expect (I explain a little further on). The DateInterval collection can be found in the Object Browser and looks like this:

A list of all the Intervals in the DateInterval collection

Fig 1. A list of all the Intervals in the DateInterval collection

“So how do you use this in the function?” I hear you ask.

=DateAdd(DateInterval.Month, 1, Today()) which gives you a date 1 month from today.

This will work for all the intervals listed in Fig 1. above.

If you don’t want to use this method and instead want to use the abbreviations then there are a few things to be weary of. For instance, using DateAdd(“w”, 1, Today()) does not yield a date 1 week from today, but rather 1 day from today. This happens with a couple of other abbreviations as well. The reason is that “w” represents “weekday” not “week”. Here is an shortlist of the abbreviations that do work:

  • Days ["dd"] – DateAdd(“dd”, 1, today())
  • Weeks ["ww"] – DateAdd(“ww”, 1, today())
  • Months ["mm"] – DateAdd(“mm”, 1, today())
  • Years ["yyyy"] – DateAdd(“yyyy”, 1, today())

For a full list of abbreviations and further explanation, msdn delivers here.

Hopefully this clears up a little of the confusion surrounding the DateAdd() function and the intervals that need to be specified. Of course, if there are other methods that can be used or I’m all ears.

I like keep an open mind. When working with windows, one must.