Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

July, 2008

  • Reporting Services drill-through for a matrix report

    One of the great things about working in Microsoft is the huge e-mail threads with where we share problems and resolve them, as you get to learn loads of tips and tricks. One of the nightmares of working at Microsoft is the huge e-mail threads with where we share problems and resolve them, as you have to wade though loads of stuff to find the good stuff.

    So this is one little problem  I came across from Mike McGeehan a Technology Solutions Specialist in New York, which was then fixed by Robert Bruckner in the SQL Server product team.

    Top Tip. Mike was able to show us the problem he was having by using adventure works and then enclosing the report files based on that so we can all reproduce the problem.

    Mike was trying to create a drill-through report whereby report A has a matrix on it and when you click on a cell it takes you to report B passing parameters on the way so that the right context comes up in report B.

    Report A is a modified version of the Company sales report, to include a subtotal in its matrix format:


    The report produces a total column as shown below:


    When a user clicks on the detail for “Mountain Frames” and “Q2” (the blue circle), a second report is called and results in the following:


    This was done by passing the values from the Company Sales report to the subreport:


    However, when the user clicks on the subtotal column in the Company Sales report (the red circle), it seems to arbitrarily select “Q3” when passing parameters to the subreport.


    Mike’s question was “Is there a way to pass all quarters to the subreport (wildcard?) in order to get all four quarters in the subreport?

    Robert fires up the report on his machine does some digging around and comes back with an answer in 2 hours

    Currently, your drillthrough actions on the matrix cells pass a single value for Year, Qtr, etc.

    First you will need to modify your drillthrough target report so that a NULL value can be passed as Qtr etc.  The meaning of passing a NULL value is that you want the data for all quarters – and you will need to write the dataset query accordingly in your drillthrough target report.

    Then, in the main report, modify the drillthrough action by using the InScope function to determine whether you want to pass a specific value (i.e. detail cell) or a NULL (i.e. subtotal cell).

    For example (YearGroup is the name of the matrix group that groups by OrderYear, etc.):

    • pOrderYear:    =iif(InScope(“YearGroup”), Fields!OrderYear.Value, Nothing)
    • pOrderQtr:      =iif(InScope(“QtrGroup”), Fields!OrderQtr.Value, Nothing)

    His parting shot is also important -

    “BTW, Note that this kind of report layout is simplified in RS 2008 due to tablix – you have separate cell definitions for detail cells and subtotal cells and can then setup the drillthrough actions without the need to use the InScope function.”

    I posted this for two reasons:

    1. This might be something you are struggling with , but more importantly,

    2. If you are stuck don’t struggle on alone, even if you are an ‘expert’ and work for Microsoft you don’t have all the answers, there’s always someone else who might have the answers.

    Of course not everyone works in a multi-billion dollar software company with technical resources coming out its ears, and this is why I am really keen on the many forums, and community sites out there such as the UK SQL Server community as they can give you the same kind of access to really good advice.

  • SQL Bits - Don’t be square be cubed

    The UK SQL community are doing their next SQL Bits cubed on Saturday 13th Sept at Hatfield University.  Possible excuses for NOT attending would be:

    • You are reading this blog not because you are a highly skilled DBA but:
      1. You like my witty style
      2. You are one of my 2 friends
      3. You have been bribed by me to keep my hits up.
    • You are a vet in your spare time and will be spending the day at Haydock Park for the British Equine Vetinary Association Congress
    • You need a replacement set of forks for your Vincent Black Shadow and will be at Netley Marsh for the Classic Bike Eurojumble
    • You are so clued up on SQL Server that you have digitised all your comic collection and stored it all using Filestream, allowing you to spend the day at the Movie Comic Media Expo in Telford buying more comics.
    • You have your life savings down on Lucky Lad in the St Ledger at Doncaster, and you need to be there to nobble the competition

    So assuming you are none of the above then you can choose one the 4 tracks to suit your particular passion for SQL Server:

    • Developer
    • DBA
    • BI
    • SQL Server 2008

    The individual sessions have been recommended by those already registered and lovingly prepared by MVPs and other experts in the field as well as a couple of Microsofties like Mike Taulty and me. 

    SQL Bits is also a huge opportunity to network whether you are after an answer to a problem or a change of role, so don’t be square be cubed

  • SQL Server Under New Management

    I am now hard at work on the events that will occur around the release SQL Server 2008. This time around I will be presenting:

    SQL Server Under New Management

    The ratio of DBA’s to the size of data being  managed is increasing all the time as is the burden of compliance regulation ,so what can be done to square this circle?  This TechNet event will show that many of the answers to these issues are included in the new management features in SQL Server 2008, none of which require a change to existing applications:

    • Policy Management allows fine grain control and audit of the state of all of the servers in the organisation.
    • Configuration Server allows policies and SQL to be evaluated against a group of servers.
    • Resource Governor in Enterprise edition dynamically mediates workloads when the server is under pressure.
    • The Performance Data Warehouse allows historical statistics to be analysed.
    • Change Data Capture and audit provide a rich source of compliance data without adversely impacting performance.
    • Replication is much simpler to configure and maintain.
    • PowerShell support allows repetitive tasks to be scripted and integrated into other administrative tasks in Windows Server 2008.

    The accompanying PowerPoint deck for this event is there as an aide memoire for you, as it’s going to take all morning to show all of this in action, and to answer your questions.

    This will be run in Manchester on the morning of 7th October (register here) and then again down in Exeter in the afternoon of 23rd October (register here) and in Manchester.

    There will be another event at the Microsoft campus in Reading on 10th of September, but the details of this have not been finalised yet. 

    BTW Don’t infer the release dates of SQL Server from these dates as these events are driven by availability of venues and speakers and not by any cunning marketing plan. when I know you’ll know.

    Technorati Tags: ,,
  • Oh no it’s the BI Acquisition..

    My first experience of ‘proper’ databases was using Ingres back in 1994 while working for an obscure part of Customs & Excise (as it was).  We could get a soundex person search from Inverness to return probable matches in under 8 secs from the central server in Southend which had 48 million records in, so not too shabby. Anyway outsourcing arrived and I departed for the real world and never saw Ingres again .. until last week!

    Now I see that Microsoft have bought an all in one BI appliance company DATAllegro which is essentially BI in a box capable of handling hundreds of terabytes of data. The current solution is based on good old Ingres. Of course this will be re-engineered onto the Microsoft platform, without losing the scale as this would be missing the point of the acquisition. 

    In an another SQL Server related acquisition a small Israeli start up Zoomix brings in dedicated data quality tools like automated in line data correction.  Zoomix also has a master data management tool, however Microsoft acquired an MDM specialist, Stratature, last year and plans to incorporate this into SharePoint 14. 

    IMO this is the way to go, don’t be too proud to admit you may not have all the answers but pick up niche applications that augment a strategic direction. Equally Microsoft does still write the odd bit of code in the BI space such as SQL Server itself and Performance Point Server, and hasn’t really been involved in the huge acquisitions that have taken place in the last couple of years.  

  • Evangelism in Eden

    Evangelism is a pretty unusual job title, inspiring comments such as 'Only at Micrsoft'. This evangelist has been off  to the Eden Project and  I got chatting to one of their pollinators, Martin, and discovered that his role is to seed ideas and win hearts and minds of the visiting public, so that would be evangelism as well. 

    Martin, like all of the team working on the Eden project is passionate about the environment and sustainability, while he is also very keen on music.  The picture below is his take on the mp3 ghetto blaster.  The rig would do credit to any episode of pimp my ride but atop it all are some photovoltaic solar cells and a wind turbine so it is self powered. 


    (Apologies to Martin as the only snap I have of his creation is this one and he's out of shot!). His green machine is largely recycled like this sculpture featuring a lot of early PC components amongst the other sorts of junk we all discard.



    I  am not sure anyone is doing enough in this space, and most of the good stuff is hidden away amongst a lot of hype. IMO we should just ensure that we and the stuff we use travels a little less and that there are quite a lot less of us on this small fragile boulder.

    IT and software allows ideas to move without people needing to do so and that's where Microsoft comes in ..

    change the world

  • Install SQL Server 2008 Advanced Services

    Having got past the pre-installation issues in my last post I am now ready to install SQL Server 2008 Express with Advanced Services..


    I ran the System Configuration Checker and got a set of green lights, except for PowerShell...


    and the install won’t get past that so I am going to have to find that because it’s not part of the install (which it is with Developer, Standard, Enterprise etc.)  If you are still with me you can get PowerShell here. A small but good feature is that at least I can rerun the test from here especially as this installation doesn’t require a reboot. So at last we are good to go …

    If I go back to the main screen I now have an installation option so I select this and agree the license and now I get to run some (more) setup rules from which I get a warning that the firewall is on :


    Now I can select which bits I want in the usual way ..


    and then the instance, which is how you can have multiple or similar independent versions, of SQL Server on the same machine…


    I am going with the default named instance of SQL Express.  Having checked the disk space, I now have to choose the accounts and in this case I am going for NT AUTHORITY\ LOCAL SERVICE..


    The next screen is important as you need to get into SQL once it has installed to I am going for mixed mode, the sa password should be as strong as your administrator and don’t forget to add yourself into the SQL Server administrators as I have done here..


    I have installed advanced services which comes with reporting services so there is an extra configuration screen for this..


    Finally you get the inevitable screen to participate in the program to report errors and usage, followed by the rules being rerun, followed by a confirmation of your settings.

    The only good thing about this tortuous process is that it is very thorough and so if you have got this far it should all work and now I can show you what it look like.

  • SQL Server 2008 RC0 Express on Vista

    I wanted to get Express installed on my machine and so I downloaded the RC0 (of Express with Advanced Services) and ran it.

    BTW there is is this Akamai download manager behind all of the RC0 downloads which you have to allow to run to get the download to start. If all is well you should have this download client running (and let me know if this is giving you grief):


    The first hiccup you might hit is that the install requires the Windows Installer 4.5 which you can get here.

    When I eventually ran the install it told me I needed the .net framework 3.5, however if you follow the link from the dialog it points you at the wrong download.  So having installed that I got the same message again.

    It turns out that you need actually need .Net framework 3.5 sp1 beta.  Hopefully this will all get sorted out in the final version, as is the case with the other editions of SQL Server 2008.

  • SQL Server 2008 End User Reporting

    I have been seeing a fair bit of confusion about the new end user reporting tools in SQL Server 2008, so I thought it would be good to try and clear it up.

    In SQL Server 2005 there is a Report Builder tool that relies on a Report Model.  The report Model is created by an IT guy and what the end user can report on.  There are two ways to create a model:

    • In the BI dev studio suing the Report model project add-in. Here a model defines the relationships between a set of relational tables, as well as the ability to add derived columns and give existing ones friendly names.
    • In Report Manager (the portal used to control reporting services) a model can be created directly on top of an analysis services cube, from the properties of an analysis services data source.

    The end user can then go to report manager and download a click once application which is now called Report Builder v1.  This has quite limited functionality compared to the tools available in BI Dev Studio, but does create a valid report file which the user can publish to reporting services. 

    In SQL Server 2008 Report Builder V1 will still be there as will the tools to create models.  However there is also Report Builder v2, the tool formerly known as the Report Designer Preview and Blue (because of it’s blue office toolbar).  Currently in RC0 the tool is not in the SQL Server 2008 install, it is in the separate feature pack here.  Unlike v1 Report Builder v2 has the full feature set of the designer inside BI Dev Studio so users are no longer limited to just using a model.

    The confusion is that both versions will be supplied with SQL Sever 2008, but v1 won’t be changed at all so you can’t use the new shiny charts or any of the other new stuff in SQL Server 2008 reporting services. 

    I do think Report Builder v2 is a big step in the right direction as it will allow proper collaboration on reports between the techies and the users.  For example I could:

    • Create the model as before but now the users can us all  of the power of reporting services
    • Setup a blank report with a data source in it and let the user do their own thing
    • Give them more help by providing a blank report with a data source and a result set.
    • Help them get a report working because I can fix it and give back to them for further design work without breaking it as would happen in V1.

    Therefore I can concentrate on the data elements of the report and leave the user to the look and feel. 

  • SQL Server 2008: where’s my stuff?

    For those of us who have been tracking the evolution of SQL Server 2008 through it’s long gestation it can be a bit annoying when things get moved around.  In RC0 odds and ends seem to have disappeared and the more sceptical DBA might assume the features have been pulled prior to release, however what has usually happened is that they have been moved to somewhere more logical.

    Here’s a few casualties of the redesign work in later CTPs and RC0, that have tripped me up:

    Where did SQLCMD mode go in the management console?

    it’s no longer on the toolbar, but it is there and you can get it back by customising the toolbar …


    BTW it’s also the last option in the query menu option

    What happened to all the best practices policies that were in the declarative management framework in earlier CTP’s ?

    The new term is policy management , but if you expand this in tree in RC)0 those policies aren’t there..


    You need to import them from here…

    \program files(x86)\Microsoft SQL Server\100\Tools\Policies\Database Engine\1033

    Where has the Activity Monitor gone?

    It has moved and you can either get to it form the main toolbar…


    Or by right clicking on the server node.

    Of course if you have only just started to evaluate SQL Server 2008 then, it’s a bit like going on holiday while the decorators have been in, it’s all vaguely familiar but there’s a lot of new stuff to get used to and some of your old favourites have been thrown out or given a makeover.

  • Business Intelligence 101

    Despite the Business Intelligence being the top priority for for CIO’s for the last three years, and it’s long history their are many people in business who have no idea what it is.  So I was delighted to be asked to present to the executive MBA class at Cranfield School of Management yesterday as part of a series on the key technologies business should invest in.

    It was interesting the my co speaker Rob Todd of Adventus came out with very similar advice around enterprise systems: one version of the truth, the need for really good cooperation between the business, the software vendor and the system implementation team for example. 

    I also got a lot of good questions & comment such as

    ”If the algorithm for a KPI is only held in one place and the whole business relies on it and it turns out to have an error in it it will affect the whole business”

    Absolutely right, but what’s the alternative to have that KPI embedded in reports and spreadsheets throughout the business , which would lead to multiple versions only some of which might be correct.  If the rules change all of these would have to be changed so far better to have the one source and make very sure it is correct through thorough testing and change control.

    You say that BI evolved form Executive Information Systems and Decision Support Systems, what will be the new name for BI”?

    I think that BI is a good term for what it does, and it hasn’t had the bad press overall that some of the component parts have such as data warehouses, so I don’t see it changing anytime soon.  There is a lot of talk about Business and Enterprise Performance Management, but this is one use of BI, namely as part of a structured process to monitor analyse and plan change to a business.  There is also personal BI , so I keep track of how I am doing at work and at home, and there is team BI for the group or division you’re in.

    I also think business likes stability and not changing names or coming out with new terms for the same old stuff, could win the IT industry a few friends and remember these are the people who are signing the cheques!

    I also found it interesting that thee were only a couple of students who had come across the balanced scorecard before, given that so many vendors, Microsoft included extensive support for this in their products. IMO this is a simple but effective technique for aligning everyone’s efforts to the corporate strategy.

  • Licensing SQL Server Client Tools

    Ever since I have been using SQL Server you have had the ability to manage SQL Server from another machine, so back in the day I used enterprise manager and query analyzer on my PC to do stuff on a server hidden in a basement somewhere. 

    At some sites an enterprising software compliance guy would quiz me on whether or not it was OK to install the client tools on my machine.  I would point out that what matters is the license to connect to the server SO provided that the server I was connected to had a CAL assigned to me or it was CPU licensed then all is well, as the client tools aren’t licensed themselves. That usually sent him away, and I had a smug look on my face until the server team applied a service pack to SQL and then I needed to service pack my PC/laptop to get working properly again.

    But  is this correct? In a word yes, the EULA you agree to when you download SQL Server has this clause in it:

    a. Running Instances of the Additional Software. You may run or otherwise use any number of instances of additional software listed below on any number of devices. You may use additional software only with the server software directly, or indirectly through other additional software.

    • Analysis Services Shared Tools
    • Business Intelligence Development Studio
    • SQL Server 2005 Books Online
    • Connectivity Components
    • Legacy Components
    • Management Tools
    • Notification Services Client Components
    • Reporting Services Report Manager
    • Reporting Services Shared Tools
    • SQL Server 2005 Shared Tools
    • Software Development Kit
    • SQLXML Client Features
    • SQL Server Mobile Server Tools
    Technorati Tags: ,
  • SQL Server Consolidation Options

    Consolidation of server is a very hot topic as server rooms fill up and cooling becomes ever more expensive. Virtualization is the current darling in this space and indeed it is fantastic for many application workloads.  However it is not the only solution to the problem and this is particularly true for SQL Server. There are several tools available form Microsoft as well as para virtualization solutions like HP’s Polyserve.  Looking at the Microsoft solutions in a little bit more detail:

    • Consolidate databases using SQL Server instances.  This has been a popular approach because you can assign CPU and memory to an instance to guarantee the resource each database will get.  The down side is that if you split your machine into instances like this and one instance is not working that hard the other instances can’t get at the unused resources even if they are under pressure.   You also have to patch both instances here and so the maintenance can be a bit tricky.
    • Consolidate databases into one instance of SQL Server.  This is not so popular as up until now you have no control over resource contention when the server is under pressure.  However with SQL Server 2008 there is resource governor in the enterprise edition which allows for fine grain control of memory and CPU, (but not IO yet). 
    • And then of course there is virtualization, which is being widely used, but here again you are carving up a physical machine in terms of cores, RAM etc. so a virtual machine can only ever use what is allocated to it no matter how busy the other parts of it are.

    One thing to note on all of these approaches this is network performance. If you had 2 servers before and you only have 1 now then unless you have multiple network card in the new box the two instances will have contend for the network card. 

    There is a white paper on SQL Consolidation here

    Finally the licensing of all of these options can affect your decision. Enterprise edition makes this quite simple as you license the physical machine per CPU, so you can have as many SQL Server instances, SQL Server virtual machines (any vendor not just Hyper-V) as you can cram on there.

  • SQL Server 2008, no definite release date

    There is some buzz around about SQL Server 2008 being out in August e.g. on SQL Server Central, however that’s more than I know.  I do know that it is listed in the August price list and that pricing is no different form SQL Server 2005, which is more than I can say for some other database products or the price of fuel. 

    As far as release dates I know it will be out by the end of Q3 this year but that could be 11:59 pacific time on 30 September, although I am hoping it will be out before SQL Bits so I can demo against the release rather than RC0.

    However I am always curious about who really cares about precise dates apart from journalists who will flame Microsoft if 'it’s late, but what about the DBA who wants to use it? IMO all I think you/they  need is a plan date and to know what’s in it that justifies an upgrade or migration. For example McLaren Electronic Systems are on the SQL Server TAP Program because they want to use Filestream,  are very interested in resource governor, and there is a lot of early adoption activity around spatial data.

    Bottom line -  It will be out sometime in Q3 when it’s ready

  • SQL BITS Cubed

    Planning for SQL Bits cubed (13th September in Hatfield) is moving along but the organisers need your help to make the day successful. The simplest thing you can do is to register on the sort stuff you want to here about.  For the extrovert and dedicated among you register as a speaker and submit your own session(s).  I have submitted a couple of sessions myself but this is not a Microsoft event it is your event and I will only present because I have been asked.

    Please register and submit your sessions  here and I look forward to meeting you all there.

  • Vista Chick Magnet

    I got one of my neighbour’s sons onto our one week work experience program this week, so I thought I would drop in to watch the presentations they had to do.  Everyone was genuinely impressed with the quality of the work, the imagination and professionalism they showed, and their presentation skills would put many of us to shame as they showed no fear and no preconceptions. One team from the FunTech program went for the north face and chose to compare XP vs Vista.  Their presentation started off with a simple scientific approach by simply opening  calculator in XP and Vista:

    • It takes 12 seconds on XP to hit start –>programs –> accessories –> calculator, assuming you know where to go.
    • Same hardware now running Vista search calc… click on calculator 7 seconds and no a priori knowledge needed.

    This is such a simple message, but Microsoft has simply not articulated the argument as simply as this.  They wanted to bring back Bob as well and showed us a few sketches..


    However the best bit was when one of the team Emily (You know who you are!) concluded the presentation by simply saying Vista is a chick magnet”.   What she meant was that girls like her think it is cool to use, but I am sure most people seeing the title of this post are going to assume that Vista has the same kind of pulling power as a Ferrari.

    Thanks to the new vista squad for letting me use their stuff…

    • Angus Rigby
    • Stuart Shepherd
    • Emily White

    ..and to the rest of the team’s for all their hard work.

    Technorati Tags: ,,,

  • Computer Weekly Blogging Awards

    Vote for the best programming and technical blog

    Somebody out there is reading this, and thinks that my stuff is worth voting for and so  I have been short listed for a Computer Weekly blog award. Thanks very much whoever you are! 

    If anyone out there is of the same enlightened opinion please vote for me in the programming and technical blogs category. It only takes a couple of seconds to do and there is no registration involved.

  • Beyond relational data in SQL Server 2008 Express

    I was in a customer meeting today with David Portas of Conchango who is hard at work doing battle with the new FileStream data type in SQL Server 2008.  He is developing a solution where large filestream objects are being pushed around a site from central servers to roving laptops.  Curiously, he’s using SQL Server 2008 Express on the laptops for a number of reasons:

    • There is the same FileStream support in express as there is standard, enterprise etc.
    • Although there is a 4gb database limit, this does not apply to the FileStream objects so these can be as large as required.
    • SQL Express 2008 can run on a 64 bit edition of windows as a 64 bit application albeit only using one physical CPU and 1Gb RAM.
    • SQL Server  Express can take part in messaging from other editions of SQL Server, so David has his master database in enterprise edition and hopes to use service broker to push the FileStream objects down to the laptops.
    • SQL Server Express is free and can be distributed for free as part of an ISV solution to customers

    I could also add spatial support to this list but while there is also support for all the new 2008 data types in Express, in this solution would spatial analysis not be that much useful.

    I cannot be more specific about the actual application at the moment but it does give you an indication of the sort of use to which SQL Server Express can be put in an enterprise solution.

  • SQL Injection

    Fuel injection is generally considered to be a good thing for cars, while IT professionals consider SQL injection to be a bad thing.  The technique does not exploit weaknesses in the the database , but how it and the relevant web server are configured.  In the Microsoft world a lot of work has been done about this based on the following principle included in its Trustworthy Computing Initiative:

    • Secure by Design.  All the Microsoft product teams go to a lot of trouble to make each new version secure and SQL Server and Windows Server are no exceptions. By the same token the bespoke applications you develop should follow these principles and part of this is ensure that there are no vulnerabilities in your code that a SQL injection attack could exploit.  Help is at hand here in the shape of a special tool available here that does that analysis for you.
    • Secure by Default.  All new Microsoft products are shipped with what James T Kirk and James O’Neill refer to as shields up; all the security features are turned on and all of the connectivity features are turned off. You then have to explicitly turn on features open ports etc. to get started and you should only turn on what you need.   To get advice on what to configure check this blog post on SQL Injection attacks from the Microsoft Security Vulnerability & Defense Team.
    • Secure by Deployment.  The process of keeping all of the installed products patched and up to date to meet new threats as they arise.

    Stating the obvious here, but Microsoft applies this guidance internally and applies it to all the sites including  MSDN  & TechNet which all use IIS and SQL Server. If these sites were compromised then this story would be all over the cloud in seconds, so the advice works, so please use it as well.