Reporting Services Win/Lose Sparkline - Setting the Axis Line

Win/lose sparklines are a useful way of displaying performance over time, providing you can boil-down performance to a binary 'win or lose' result.

Sean Boon has a good example of doing this here but I struggled to get the Axis line to display in the right place. I thought I'd share what worked for me.

What I found was after setting the axis to visible, as per Seans blog post, that initially the Axis is drawn at the bottom like so:

This just looks daft and gives the incorrect or at least confusing impression of where '0' is.

The answer to this is buried well within the SSRS interface.

1. First select the sparkline chart. Then press 'F4' to see the Properties grid.

3. Find the ChartAreas property and select the tilde to the right:

4. Within the 'ChartArea Collection Editor' select the 'CategoryAxes' property and click the tilde to the right of that:

5. Within the 'ChartAxis Collection Editor' make sure that the 'CrossAt' property is set to '0':

6. Click OK to get back to the 'ChartArea Collectino Editor' and then click the tilde on the right of 'ValueAxes' and repeat step 5.

7. You should now find that the Axis for your win/lose sparkline goes through the middle of your chart where you'd expect. You'll probably also want to remove the tick marks from the Axis by ticking the 'Hide major tick marks' box in the horizontal axis properties.

Splitting dates over monthly periods using a 'numbers table'

One of the things you may find yourself having to do is to take a single record with a start and end date and subdivide it into monthly or weekly periods. Typically this is so that you can create a record for each month or week in a data warehouse, and have a data structure thats easy to report from. There are many ways of doing this of course, but an elegant set-based solution involves using a 'numbers table'.

Numbers tables are well documented elsewhere, however they can now be created in a trivial fashion in Denali using a Sequence, with the following script.

USE adventureworks2008r2
SET nocount ON

IF EXISTS (SELECT 1
           FROM   information_schema.tables
           WHERE  table_name = 'Numbers')
  DROP TABLE numbers

GO

CREATE TABLE numbers
  (
     NUMBER INT NOT NULL,
     CONSTRAINT pk_numbers PRIMARY KEY CLUSTERED (NUMBER) WITH FILLFACTOR = 100
  )

IF EXISTS (SELECT 1
           FROM   sys.objects
           WHERE  name = 'SeqNumbers'
AND type_desc = 'SEQUENCE_OBJECT')
  DROP sequence dbo.seqnumbers

GO

CREATE SEQUENCE SeqNumbers
AS INT
MINVALUE 0
GO

INSERT Numbers(Number) VALUES(NEXT VALUE FOR SeqNumbers)
GO 10000

This will give you a table of numbers from 0-10000.

Then a simple query will split a table into monthly records - or any other time period - by joining to the numbers table using the difference between the start and end dates of the rows you want to split. An example will probably make it clearer - we'll use the Production.BillOfMaterials in the AdventureWorks2008R2 database.

Firstly lets make the dates a little less uniform:

UPDATE  [Production].[BillOfMaterials]
SET    EndDate = '20040405'
WHERE  BillOfMaterialsID=271

UPDATE  [Production].[BillOfMaterials]
SET    StartDate='20040731', EndDate = '20040801'
WHERE  BillOfMaterialsID=1950


UPDATE  [Production].[BillOfMaterials]
SET    StartDate='20041031', EndDate = '20051130'
WHERE  BillOfMaterialsID=2899

Querying the Production.BillOfMaterials for an Id and start and end dates and quantity looks something like this:

Taking the difference between the start and end dates and joining to the numbers table gives one row per month that exists between the start and end dates. A little jiggery-pokery with the date functions gives you the start and end of each month over which the bill of materials spanned, like so;

SELECT [BillOfMaterialsID],
       [StartDate],
       [EndDate],
     DATEDIFF(m, StartDate, EndDate) NumberOfMonths,
     CAST(CONVERT(CHAR(6), DATEADD(m, Number, StartDate), 112)+'01' AS DATETIME) MonthStartDate,
     DATEADD(dd, -1, (CAST(CONVERT(CHAR(6), DATEADD(m, Number+1, StartDate), 112)+'01' AS DATETIME))) MonthEndDate,
     CAST(PerAssemblyQty/(DATEDIFF(m, StartDate,  EndDate)+1) AS DECIMAL(5,2))PerAssemblyQtyMonthly,
     PerAssemblyQty
  FROM [Production].[BillOfMaterials] b
  INNER JOIN dbo.Numbers n ON DATEDIFF(m, StartDate, EndDate)>=n.Number
  WHERE [EndDate] IS NOT NULL

You'll note that I also divided the PerAssemblyQty by the number of months (adding one because the DATEDIFF on two dates in the same month returns 0). Finally lets see the result set

Denali CTP 3 is out!

Not exactly a big fanfare but a story in The Register reveals that the SQL Server Denali Community Technology Preview 3 is available for download here: (it weighs in at about 2GB). More details from Microsoft here and what is included here. From what I understand this is the last CTP/beta before it goes RTM.

MDS - Access Denied

We're having a play with SQL Server Master Data Services at MITIE with a view to using them to manage some of our analytical Master Data - currently done via scripting. One of the first challenges was security. There is a set of instructions here but we still managed to get stuck. We configured a user as an administrator for all models as described here but still got an "Access is Denied" message when trying to log in with that user. Note that the system administator has to be a user and not a group. The answer is elsewhere in the MSDN documentation;

Step one
Under User and Group Permissions click the down arrow > Edit > Functions. Here I'm showing the group page, but the user page works the same way:

Step two
Click the pencil icon to allow for editing

Step three
Add the functions as appropriate. These map onto the 'buttons' that are rendered in the MDS home-page, and should be self-explanatory. To add all of them simply click the button highlighted below:

Your user or group should now be able to log into MDS, with no "Access is Denied" message. Other permissions can be established as per the MSDN documentation.

'Invalid characters' in Analysis Services cube

We ran into the following error whilst trying to query our development cube today;

The server sent an unrecognizable response.
' ', hexadecimal value 0x19, is an invalid character.

Rather peculiar I think you'd agree! Turns out the data in question was a Unicode field with a ASCII control character in it. 0x19 is ASCII character 25 (19 in hexadecimal) - which stands for EM or End of Medium apparently.

Analysis Services quite happily processed and stored the data - however any attempt to query it was defeated despite the fact that the associated dimension attribute was also Unicode (ie a WChar). This, I realised, was due to the fact that clients communicate with Analysis Services using the XML for Analysis (XMLA) protocol. As the name suggests its based on XML which cannot encode some unicode characters - including ASCII 25!

How on earth it got into our source system was a mystery - but we had to do something about it.

Fortunately there's a very useful option in Analysis Services for dealing with this sort of issue; the InvalidXMLCharacters property. You can find it by right-clicking on an attribute and selecting Properties:

Then expand the 'NameColumn' property and under that you'll find the InvalidXMLCharacters property:

Essentially it tells Analysis Services what to do with characters that aren't supported by XML

It has three settings:

Preserve	Keep invalid characters. The default
Replace	Replace with a '?'
Remove	Remove the character from the query results

We opted for 'Remove' which resolved our problem. Its worth considering setting this on all unicode and non-unicode text attributes as potentially any off these could include invalid XML characters. It really depends on how well you know - and trust - your data source...

Configure Reporting Services to use proxy for Bing maps

If you work somewhere that uses a proxy server to connect you and your servers to the internet you will need to configure Reporting Services to use said proxy in order to use the Bing maps layer. Otherwise you will see something along the lines of the following message in your report, instead of a map:

The remote name could not be resolved: 'dev.virtualearth.net'

The solution is easy;

Step one
Navigate to the webconfig file for the instance of Reporting Services you want to proxy out to the internet. Typically for 2008 R2 this will be:

<drive name>\Program Files\Microsoft SQL Server\MSRS10_50.<instance name>\Reporting Services\ReportServer\web.config

Step two
Add the following xml fragment to the file - I usually add it in-between the</runtime> tag and the </configuration> tag, at the end of the file:

<system.net>

        <defaultProxy enabled="true" useDefaultCredentials="true">

            <proxy bypassonlocal="True" proxyaddress="http://<proxy server>"  />

        </defaultProxy>
    </system.net>

Save the file.

This of course assumes your server is allowed to proxy out in the first place (which it will need for Bing maps integration). You can test this by logging onto the server and firing up a browser or by asking your friendly local sysadmin...