A picture is worth a thousand words. I realized this the other day when I gazed upon an image of the new Alfa Romeo 8C Competizione, ( although I won’t mention which words entered my head at that point ), but anyway, with this in mind, it brings us to Reporting Services 2008, and the new data visualization elements available to us.
Knowing full well that dashboards and scorecards are becoming more and more popular, the team have added Dundas gauges to the mix, and I suspect that this object will be used extensively. ( If you consider the cost of competing reporting tools with features like these, a few more choice words enter the mind ). But how easy is it to use ?
The short answer is VERY. We have 2 types of gauges, radial and linear. Each gauge has a number of sub-areas, as you’ll no doubt notice from the diagram above, but the ones to be interested in initially are the scale and the pointer.
The scale defines what the gauge measures, and by default it is set to a minimum of 0 and a maximum of 100. If you’re using the gauge to display KPI’s , you might want to reset the scale to a range of 0-3 or 0-5.
The pointer is the next thing to focus on. Not because of the possibility changing the physical pointer to a wide range of different types ( and you can ) , but because this is where you link your data to the gauge itself. So lets assume we had the following query in the dataset, running against the Adventure Works database.
|SELECT ST.Name, Sum(SOH.TotalDue) AS Sales|
FROM Sales.SalesOrderHeader SOH
INNER JOIN Sales.SalesTerritory ST
ON SOH.TerritoryID = ST.TerritoryID
GROUP BY ST.Name
Create a simple matrix of sales per territory ( with Territory as the Row Group, SalesAmount as the data field and no Column group ). It should look like the one below. Remember that although you’re starting off with a matrix, it’s actually a tablix underneath.
1) Right-Click anywhere on the report and select Insert – Gauge.
2) Select the very first Radial gauge from the list. Leave it on the canvas.
3) Right -Click inside the header of the second column and Select Insert Column – Inside Group Right.
4) Drag the gauge object into the new cell. You may have to increase the row height in order to see the gauge clearly.
What needs to be done now, is to calculate what percentage of the overall sales constitutes the sales for a particular territory. To do this we divide the sales for a territory ( in other words the sales for every row ) by the total sales amount. This is achieved by using the following expression :
A simple expression. Select the pointer of the gauge, Right -Click and select Pointer Properties. On the Value field, you can select Fx and input the above expression. Now when you run the report, you will find that the gauge indicates this percentage quite nicely, for example the Southwest Territory was responsible for about 22% of all sales.
This example illustrates how to use the gauge within a tablix, but remember, you don’t have to use it like this. You can use it as a standalone object , but then it has to bind to a single data value. Also, remember that if the data value is greater than the maximum value of the scale, the gauge will read as the maximum possible value of the scale.
You see, Reporting Services doesn’t need IIS anymore. The guys at Microsoft have decided to end the reliance on IIS due to deployment and management headaches. Reporting Services can now interact directly with HTTP.SYS and respond to web requests sent to the server.
This does not mean that Reporting Services is now also a web server – it simply means that it doesn’t need IIS anymore. Now the thing is to keep this in mind when you’re installing Reporting Services 2008 on a server that already has IIS.
First , go to the new Reporting Services Configuration Manager Screen. This has changed since RS 2005.
You will notice a link called Web Service URL. Here you can set up the name of the Report Manager Virtual Directory. I normally leave this as is. The second section is more interesting.
The first setting, IP Address, determines how the incoming requests are handled. If you set it to “All Assigned”, which is the recommended setting, it means that any request coming in on port 80, and that uses the virtual directory name of Report Server ( or the same name as set in the first field ), will be sent to Reporting Services instead of IIS.
The second setting , “All Unassigned”, means that if no other service has registered a URL reservation for this virtual directory on this port, then give Reporting Services the request.
Now for the fix : some people have changed the setting to “All Unassigned” and found that Report Manager comes up. However, what I would is leave it to “ All Assigned “ and change the port to 8080.
The reason for this is because you cannot share port 80 with IIS. According to Microsoft , this is the case on Windows XP but I have also had this problem with Windows Server 2003.
Drill down reports are very useful for data analysis. They allow a form of interactivity for the report viewer and make it easier to comprehend the information in the report, especially when the said report is displaying a large amount of data. Although creating drill down reports in Reporting Services is quite easy, I have decided to outline the steps here for the benefit of those who’ve never created them before. It’s also an opportunity for me to talk a bit about the new Report Designer and Tablix in Sql Server 2008 !!
Report Designer ships with SQL Server 2008, and in the November CTP ( the last at the time of writing ), they’ve jazzed up the interface, giving it a look and feel similar to Microsoft Word. It’s also just as easy to use now as Microsoft Word, so easy in fact , that I think business users will soon be able to create most reports themselves and won’t need us developers !!
I’ve created a simple query using AdventureWorks that we can use :
|SELECT Sales.SalesTerritory.Name as Region,|
Sales.SalesTerritory.[Group] as Territory,
Year(Sales.SalesOrderHeader.DueDate) as DueYear,
Month(Sales.SalesOrderHeader.DueDate) as DueMonth,
INNER JOIN Sales.SalesOrderHeader
ON Sales.SalesTerritory.TerritoryID = Sales.SalesOrderHeader.TerritoryID
When you start Report Designer, it will ask you to create a Data Source Connection. This is quite straight forward ( Click on the link in the body of the report to start ). Once thats done , you can create datasets. An improvement in Report Designer is the Data Pane on the left of the screen. It allows you to manage Data Sources, DataSets, Images and Parameters and also lists the “Global” fields for quick reference.
It should let you create a dataset when creating the data source. You can always edit it later and set the properties. When you right-click and edit the data source , you are shown the dataset properties box. You can setup parameters here as well , so take note of the options.
Once the dataset is created you’re ready to proceed. The steps are as follows :
1) Click [Insert] from the top menu , then double-click the [Matrix] button to insert a Matrix into the report. Note that whether you use a table or matrix it’s still the tablix control that you are using.
2) One of the new features of the tablix is that there is a button on each field that you can click and then select the required data field.
3) If you assign Territory to the Rows Group , DueYear to the Columns Group, and TotalDue to the Data textbox, you should end up with a simple matrix that resembles the following ( note I have done some simple formatting ) :
4) Now we need to add the sub-groups. You will notice that there are 2 task panes at the bottom indicating the row and column groups of your matrix. You can right click the Row Group ( Territory ) , Select Add Group , then select Child Group, since we want to add a group at a level below Territory. You will also notice the Adjacent option, this is merely to extend your matrix to add another group at the same level.
Name the group Country and select CountryRegionCode as the field to group on. Now when you run the report you will see the second Row Group created and the figures spilt by Country.
The next step is to make it interactive. To make it easier to do this, make sure that you name all the cells in your matrix. For example, when you’re back in design mode, click in the cell that holds the Territory value and name it Territory. Do this for the other cells as well ( eg. DueYear, and CountryRegionCode ).
We now need to edit the group properties for the child group called Country. On the Row Groups task pane at the bottom, right click the Country child group and select Edit Group. The Tablix Group Properties box comes up. Select Visibility on the left and select Hide to hide the group. We then have to allow it to be toggled by another cell, and the Territory cell which you renamed earlier is the one we use. Check “Display can be toggled by report item” and select the Territory text box.
When you preview the report again , you will see that you now have a drill down function on the row groups.
Therefore “to create drill down reports, all you have to do is hide a row or column group and set it to be toggled by the cell before it” In the same vein, you can now add a Month child group to the columns and make it such that Year will drill down into Month. Once this is done , I like to merge the unused cells on the top left so that it looks neater.
You can now play with things like adding sub-totals ( hint : Right Click on the cell and you can add totals for the level you’re on , either before or after the current cell ).
Although creating these drill down reports was just as easy in RS 2005, managing things such as groups and sub-totals are much improved with the Tablix, and I feel that once you’re used to the new control it will be a pain to go back to the old Table / Matrix.
…and here is the new dataset properties window…
…The new Query Editor Window …
…and the chart data options window
Check back for more in-depth articles on each topic. In the mean time , CTP 5 is now available for download !!
|d||The day of the month. Single-digit days will not have a leading zero.|
|dd||The day of the month. Single-digit days will have a leading zero.|
|ddd||The abbreviated name of the day of the week, as defined in AbbreviatedDayNames|
|dddd||The full name of the day of the week, as defined in DayNamesTD>|
|M||The numeric month. Single-digit months will not have a leading zero.|
|MM||The numeric month. Single-digit months will have a leading zero.|
|MMM||The abbreviated name of the month, as defined in AbbreviatedMonthNames.|
|MMMM||The full name of the month, as defined in MonthNames.|
|y||The year without the century. If the year without the century is less than 10, the year is displayed with no leading zero.|
|yy||The year without the century. If the year without the century is less than 10, the year is displayed with a leading zero.|
|yyyy||The year in four digits, including the century.|
|gg||The period or era. This pattern is ignored if the date to be formatted does not have an associated period or era string.|
|h||The hour in a 12-hour clock. Single-digit hours will not have a leading zero.|
|hh||The hour in a 12-hour clock. Single-digit hours will have a leading zero.|
|H||The hour in a 24-hour clock. Single-digit hours will not have a leading zero.|
|HH||The hour in a 24-hour clock. Single-digit hours will have a leading zero.|
|m||The minute. Single-digit minutes will not have a leading zero.|
|mm||The minute. Single-digit minutes will have a leading zero.|
|s||The second. Single-digit seconds will not have a leading zero.|
|ss||The second. Single-digit seconds will have a leading zero.|
|f||The fraction of a second in single-digit precision. The remaining digits are truncated.|
|ff||The fraction of a second in double-digit precision. The remaining digits are truncated.|
|fff||The fraction of a second in three-digit precision. The remaining digits are truncated.|
|ffff||The fraction of a second in four-digit precision. The remaining digits are truncated.|
|fffff||The fraction of a second in five-digit precision. The remaining digits are truncated.|
|ffffff||The fraction of a second in six-digit precision. The remaining digits are truncated.|
|fffffff||The fraction of a second in seven-digit precision. The remaining digits are truncated.|
|t||The first character in the AM/PM designator defined in AMDesignator or PMDesignator, if any.|
|tt||The AM/PM designator defined in AMDesignator or PMDesignator, if any.|
|z||The time zone offset ("+" or "-" followed by the hour only). Single-digit hours will not have a leading zero. For example, Pacific Standard Time is "-8".|
|zz||The time zone offset ("+" or "-" followed by the hour only). Single-digit hours will have a leading zero. For example, Pacific Standard Time is "-08".|
|zzz||The full time zone offset ("+" or "-" followed by the hour and minutes). Single-digit hours and minutes will have leading zeros. For example, Pacific Standard Time is "-08:00".|
|:||The default time separator defined in TimeSeparator.|
|/||The default date separator defined in DateSeparator.|
|% c||Where c is a format pattern if used alone. The "%" character can be omitted if the format pattern is combined with literal characters or other format patterns.|
|\ c||Where c is any character. Displays the character literally. To display the backslash character, use "\\".|
- Right click on your data value field and select properties ( or on the chart properties box go to the Data tab , select a value and click Edit )
- Select the Point Labels Tab and click Show Point Labels.
- Choose the data label and the formatting style.
You can even customise the font attributes and point label positioning. However I stumbled accross a problem recently where if the values of the bars were significantly different from each other , some bars would not display the point labels.
The first thing I tried was to remove the Auto option and position the point label myself manually. This didn’t really help. I then tried adding Side Margins to the chart in order to make it display properly. This is useful if you sometimes have only a few range values and the chart does not quite look right. For example , if you have a chart of sales per month and you only have a months worth of data in the database, you get one large ugly bar the width of the chart. Adding side margins will always add some space on either side and make it look much nicer. The upshot is that adding the side margins to my chart cured the problem of the point labels not rendering on certain bars !! Go figure that one out. To add them in do the following :
- Go to the chart properties window
- Select your Axis and select Side Margins
Now the thing to remember here is that if you want margins on the left and right you have to enable it on the Y-Axis, and for the Top and Bottom do it on the X-Axis. ( I would have thought it would be the other way around ). So the default charts in Reporting Services do have a lot of functionality – you just need to play with all the properties to see what’s really possible. Of course , we’re getting Dundas charts in RS 2008 , and that’s something to really look forward to…..:)