Charlie Maitland’s Blog

A post from Chris Webb prompted me to answer a question that a number of people have asked; “Can I use Reporting Services to do…”

This is normally based around the need to either toggle a value or add a commentary to a value, usually something along the lines of “I couldn’t submit my budget because the dog ate my spreadsheet”.

Depending on the scenario my response is either:

No, its a reporting tool not an application platform

or

It can be done but not natively.

To do a simple action on data you can have a link to a sub report passing in parameters. The sub report can be based on the SQL Stored Procedure that, when it is run modifies data. The downsides to this are that the user cannot input any data and the user is taken from the master report to a sub report of limited value.

To do anything more complex I have added a link to an ASP.NET page, passing parameters. The ASP page can then do whatever you want it to do. This gives total power but requires more coding. It may also need consideration of your security model.

So in conclusion, it IS a reporting tool but with some hacking things can be done.

Sacha has done a very good explanation of how the “With Shares” calculation works in PerformancePoint.

I have to say I have had the privilege of working with Sacha and his team and they are awesome.  Anyone who is able to do a live model building in front of a client and senior Microsoft people with 18 hours notice really has to know their stuff. If you are looking for a PerformancePoint partner provider then head over to Adatis.

A conversation I have had recently has lead me back to a conversation I had with someone on the SQL stand at the Microsoft World Wide partner conference. The stand representative asked me “How do we get more people to buy SQL Server”

My answer then was “You dont, people buy applications” I think that the release of SQL Server Data services is just reinforcing this and Dave Campbell’s video shows this.

Where SQL Server has always scored and is continuing to score is in making it easy to build applications and people buy Applications. My contention is that business decision makers are far less interested in the supplier of the database than they are in the cost/functionality of the application. Where SQL has and is scoring is that it makes the building and running of these apps easier and cheaper.

The big question is how fast the SQL Data Services can grow against the Amazon/Google offering and how well this can be integrated and made transparent to the developer.

The whole data world has come out of the polarized  doldrums and is now a fully competitive world again.

Bring it on!

There has been a lot of chatter about the announcement by Microsoft of SQL in the Internet cloud.

I am watching an interview with Dave Campbell where he not only gives a great synopsis of the history of SQL Server and it’s historical architecture but goes on to describe the design decisions that the new “cloud” metaphor enforces.

This is well worth a watch

Dave Campbell- SQL Server Data Services and the Future of Data in the Cloud

I had reason to need to to be able to audit changes to a Reporting services site. Whilst there is an audit trail for tracking execution or reports there isn’t one for changes to the site. The sorts of changes I was interested in were moving reports or changing visibility settings.

These settings are predominantly held in the Catalog table in the ReportServer database so I decided to add an Audit trail and a trigger to the catalog to track the changes.

The explanation below is focused around this scenario but should be easily adaptable for any table where you want to audit changes.

First I created a table for my audit data. CatalogHistory with, in this instance, the following columns

• ItemID uniqueidentifier,
• ModifiedBy uniqueidentifier,
• ModifiedDate DateTime,
• [Name] Varchar(100),  –This is the name of the object that has been changed
• ColsUpdated VarBinary(100), –This is used to store the binary data returned by  Columns_Updated() function
• ColNames — This is the XML with the names of the columns where data has changed

The only way to find out which columns have had data in them changed is to use the SQL function Columns_Updated(). This however returns a binary number where the 1s and 0 identify which column has been changed which is not very useful.

So I then created a user defined function that will return an XML string listing the column names that have had data in them changed.

It comes out in the format

<C updated_column=”Description” />< <C updated_column=”ModifiedByID” /><C updated_column=”ModifiedDate” />

I am also storing the binary data as a secondary check of the XML, not that I am paranoid or anything!

The code for the Function is:

– Inputs 

– @Tablename the name to get the changed columns for

– @ColsUpdated the varBinary data that is produced by the Columns_Updated() function.

–    This needs to be passed in as the Columns_Updated() function wont return the right result in this scope

– Outputs

– An XML in the form <C updated_column=”Property” /><C updated_column=”Description” /> 

–    If the reult shows no records changed then the XML is <root></root>

Create Function ChangedColumns (@Tablename Varchar(255), @ColsUpdated VarBinary(100)) Returns XML

As

Begin

DECLARE @i AS int, @num_cols AS int

DECLARE @UpdCols TABLE(ordinal_position int NOT NULL PRIMARY KEY) –Decalre a table variable

Declare @FieldsXML XMl

--First get the number of cols in the table. This is needed because if there are more than 8 we need to

--do the binary changed comparison on each Byte at a time

SET @num_cols =

 (SELECT COUNT(*)

  FROM INFORMATION_SCHEMA.COLUMNS

  WHERE TABLE_SCHEMA = ‘dbo’

    AND TABLE_NAME = @Tablename)

SET @i = 1

WHILE @i <= @num_cols – Loop through the columns and do the binary comparison

BEGIN

  IF (SUBSTRING(@ColsUpdated,(@i - 1) / 8 + 1, 1))

       & POWER(2, (@i - 1) % 8 ) = POWER(2, (@i - 1) % 8 )

    INSERT INTO @UpdCols VALUES(@i) – Insert into the Table Variable the column numbers if it they have changed

  SET @i = @i + 1

END

--Populate the xml variable with the resulting names based on a join between our table variable and INFORMATION_SCHEMA.COLUMNS

Set @FieldsXML = SELECT COLUMN_NAME AS updated_column

FROM INFORMATION_SCHEMA.COLUMNS AS C JOIN @UpdCols AS U

  ON C.ORDINAL_POSITION = U.ordinal_position

WHERE TABLE_SCHEMA = ‘dbo’

  AND TABLE_NAME = @Tablename

ORDER BY C.ORDINAL_POSITION

FOR XML AUTO)

--Return the resulting XML

return isnull(@FieldsXML, ‘<root></root>’)

End

Having got the function I then created a trigger on the Catalog Table

Create  Trigger tg_ChangeHistory

on [Catalog]

After Update, Insert

as

Insert CatalogHistory (

ItemID, --GUID

ModifiedBy, --GUID

ModifiedDate, --DateTime

[Name], – Varchar(100)

ColsUpdated, --VarBinary

ColNames --XML

)

Select 

ItemID, – From the source table

ModifiedByID, – From the source table

ModifiedDate, – From the source table

[Name], – From the source table

– Call the SQL Function for the Binary data 

– this is not strictly needed but provides an additional checkpoint in case of attempted edits of the XML

Columns_Updated(), 

--Call the User Defined function passing the table name and the binary change result

--NB... NOTE THE DBO in front of the Function name - this is needed

dbo.ChangedColumns(‘Catalog’,Columns_Updated() )

 from Inserted

I must give credit to the following articles that gave me the pointers - and code to achieve this

http://www.sqlmag.com/Article/ArticleID/26629/sql_server_26629.html

http://articles.techrepublic.com.com/5100-9592-6175865.html#

Without them the whole process would have taken much longer.

One of the people who has been a key driver in both the delivery in SQL Server Reporting Services and has indirectly been a key in helping me, has decided to move on to the Microsoft Heath Solutions group.

Good luck Brian, and thanks for getting me the jobs that have got me where I am!

Ok I have to confess I HATE keeping track of my expenses. Every system I have had to use has been like pulling teeth. However I have found this site that does look really easy and simple and allows you to capture the information easily at or near time of the expense so there is less of the pain come submission time.

http://www.xpenser.com/

I will give it a go even though I suspect some of the features are US only.

There is an upcoming TechNet webcast on how to best use Perf counters for SQL Server. I hope to be able to make it or catch it as a recording as some recent events have shown the importance of this skill.

Nice post here from Will Riley on how to get a sorted list in MDX that is based on the member names and does not get impacted by the members’ position in a hierarchy.

For those of you who know about Channel 9 (and if you don’t you should) then there is a new sister site from TechEd called Edge.