Author Archives: Luke Hayler

T-SQL Tuesday #016 – Aggregations

Posted on by

It’s T-SQL Tuesday time again! If you are not sure what T-SQL Tuesday is about then go and read this article written by Adam Machanic (Twitter | Blog). This month’s T-SQL Tuesday is brought to us by Jess Borland (Blog | @grrl_geek) and the topic is “Aggregation”. So let’s get started.

Matrix objects in SQL Server Reporting Services lend themselves to aggregation. With row and column groups prevalent in so many report requirements, it is inevitable that there will be subtotals at many different levels. Understanding Scope will enable you to refine your matrix object by applying different aggregate functions (or formatting options) to specific cells, or set of cells.

This is a post that I have been meaning to write for quite some time. In fact, ever since I did my original post on The InScope() Function and the key to formatting Matrix objects, I’ve been threatening to do it. This stems from all the comments and questions (via email) that I get relating to that post. Most of which are due to a lack of understanding of “scope”. So, while this may seem a trivial subject to a few of you, there are a lot of folks who just aren’t quite there yet. Also, this post is aimed at Reporting Services 2005 – I know that we are well into 2008 R2 by now, but I am aware that there are still many many companies out there who have yet to make that leap. Thankfully Reporting Services 2008 R2 deals with matrix subtotals in a much more user-friendly way.

 

Scope can be defined as (care of the Free Dictionary):

The area covered by a given activity or subject

For example: Applying this to a Matrix object, we can say that all the cells covered by a particular Column (or Column Group) header are in-scope of that particular Column (or Column Group) header.

I’ll use the matrix layout below as a template to attempt to explain this in a little more detail. This matrix has the following details:

  • A row detail [group] named “Countries”
  • A row group named “Regions”
  • A subtotal on the row details (“Region [1/2] Subtotal”)
  • A [Sub]total on the row group (“Grand Total (Region)”)
  • A column detail [group] named “Quarters”
  • A column group named “Years”
  • A subtotal on column details (2009/2010 Subtotal)
  • A [Sub]total on the column group (“Grand Total (Years)”)

image

Fig 1. Matrix object

For each of the figures below I have included a description of the scope that is being highlighted as well as an expression that can be used to generate the background colour for the cells that fall in-scope of the named group.

 

Fig 2. below shows the scope of the Row Details Group (“Countries”). All cells that fall within these rows (including the subtotals & grand totals) are within the scope of the Row Details Group.

image

Fig 2. Matrix object – Row Details (“Countries”) Scope

=IIF(InScope(“Countries”), “Green”, “White”)

 

Fig 3. below shows the scope of the Column Details Group (“Quarters”). All cells that fall within these columns (including the subtotals & grand totals) are within the scope of the Column Details Group.

image

Fig 3. Matrix object – Column Details (“Quarters”) Scope

=IIF(InScope(“Quarters”), “Green”, “White”)

 

Fig 4. below shows all cells that fall within the scope of both the Row & Column Detail Groups only.

image

Fig 4. Matrix object – Cells “in-scope” of both Row Details (“Countries”) & Column Details (“Quarters”)

=IIF(InScope(“Countries”), IIF(InScope(“Quarters”), “Green”, “White”), “White”)

 

Fig 5. below shows the scope of the Row Group (“Regions”). All cells falling within these rows are within the scope of the Row Group (“Regions”). Notice how this includes  the Region 1/2 Subtotals – these fall outside of the scope of the Row Detail Group (“Countries”), see fig 2.

image

Fig 5. Matrix object – Row Group (“Regions”) Scope

=IIF(InScope(“Regions”), “Green”, “White”)

 

Fig 6. below shows the scope of the Column Group (“Years”). All cells falling within these columns are within the scope of the Column Group
(“Years”). Notice how this includes  the 2009/2010 Subtotals – these fall outside of the scope of the Column Detail Group (“Quarters”), see fig 3.

image

Fig 6. Matrix object – Column Group (“Years”) Scope

=IIF(InScope(“Years”), “Green”, “White”)

 

Fig 7. below shows all the cells that fall outside of the scope of the Row Detail Group (“Countries”), but within the scope of the Row Group (“Regions”)

image

Fig 7. Matrix object – Cells “out-of-scope” of Row Details (“Countries”), but “in-scope” of Row Group (“Regions”)

=IIF(InScope(“Regions”), IIF(InScope(“Countries”), “White”, “Green”), “White”)

 

Fig 8. below shows all the cells that fall outside of the scope of the Column Details Group (“Quarters”), but within the scope of the Column Group (“Years”)

image

Fig 8. Matrix object – Cells “out-of-scope” of Column Details (“Quarters”), but “in-scope” of Column Group (“Years”)

=IIF(InScope(“Years”), IIF(InScope(“Quarters”), “White”, “Green”), “White”)

 

Fig 9. below shows all cells that fall outside of the scope of both the Row Details Group (“Countries”) and the Row Group (“Regions”)

image

Fig 9. Matrix object – Cells “out-of-scope” of Row Details (“Countries”) AND “out-of-scope” of Row Group (“Regions”)

=IIF(InScope(“Regions”), “White”, “Green”)

 

Fig 10. below shows all cells that fall outside of the scope of both the Column Details Group (“Quarters”) and the Column Group (“Years”)

image

Fig 10. Matrix object – Cells “out-of-scope” of Column Details (“Quarters”) AND “out-of-scope” of Column Group (“Years”)

=IIF(InScope(“Years”), “White”, “Green”)

 

Fig 11. below shows all the cells that fall outside of the scope of both the Row Details Group (“Countries”) and the Column Details Group (“Quarters”), but inside the scope of both the Row Group (“Regions”) and the Column Group (“Years”).

image

Fig 11. Matrix object – Cells “out-of-scope” of Row Details (“Countries”) AND Column Details(“Quarters”), but “in-scope” of Row Group (“Regions”) AND Column Group (“Years”)

=IIF(InScope(“Regions”), IIF(InScope(“Countries”), “White”, IIF(InScope(“Years”), IIF(InScope(“Quarters”), “White”, “Green”)), “White”)

 

Fig 12. below shows all the cells that fall outside of the scope of the Row Details Group (“Countries”) and the Column Details Group (“Quarters”) and the Column Group (“Years”), but inside of the scope of the Row Group (“Regions”).

image

Fig 12. Matrix object – Cells “in-scope” of Row Group (“Regions”), but “out-of-scope” of Row Details (“Countries”) AND Column Details (“Quarters”) AND Column Group (“Years’”)

=IIF(InScope(“Regions”), IIF(InScope(“Countries”), “White”, IIF(InScope(“Years”), “White”, “Green”)), “White”)

 

Fig 13. below shows all the cells that fall outside of the scope of the Row Details Group (“Countries”) and the Row Group (“Regions”) and the Column Details Group (“Quarters”), but inside of the scope of the Column Group (“Years”).

image

Fig 13. Matrix object – Cells “in-scope” of Column Group (“Years”), but “out-of-scope” of Column Details (“Quarters”) AND Row Details (“Countries”) AND Row Group (“Regions”)

=IIF(InScope(“Years”), IIF(InScope(“Quarters”), “White”, IIF(InScope(“Regions”), “White”, “Green”)), “White”)

 

Fig 14. below shows the cell (there can be only one) that falls outside of the scope of any named group in the matrix object

ef="http://lukehayler.com/wp-content/uploads/2011/03/image13.png">image

Fig 14. Matrix object – Cells “out-of-scope” of all named Row/Column Groups

=IIF(InScope(“Regions”), “White”, IIF(InScope(“Years”), “White”, “Green”))

 

Using the knowledge & understanding of the above combinations with SSRS expressions (to define values/formats) will enable you to refine your matrix-based reports and give the end user the required information.

Not sure how to get subtotals in your matrix objects? See my post on Creating Subtotals in SSRS 2005

Refresh Intellisense in SQL Server Management Studio 2008

Posted on by

SQL Server Management Studio 2008 has been around for a little while now, but I still hear loads of people complaining that intellisense does not update often enough (or at all in some cases). This is pretty frustrating. I should know, I battled with this until I found the Intellisense menu and the shortcut keys.

Go to the SSMS Menu and click on Edit:

image

Now click on Intellisense> to view the Intellisense options

image

The Refresh Local Cache will do exactly that and then you’ll be able to see those changes that you have just implemented.

The shortcut (if you are a little visually impaired) is Ctrl + Shift + R

 

Now go refresh your cache.

Create an Environment Variable (for use in SSIS Packages)

Posted on by

To create an environment variable on your PC/Server follow these simple steps:

  1. Click Start
  2. Right-click Computer and select Properties
  3. Click Advanced System Settings
  4. Under the Advanced Tab, click Environment Variables
  5. Click New…
  6. Enter a Variable Name and Variable Value
  7. Click OK, and you’re done!

The Environment Variable Editor

Fig 1. The environment variable editor

 
Updating Environment Variable Values

To update the environment variable that you have just created, you can redo the steps above and choose Edit… instead of New.. in step 5. Or, You can do the following:

  1. Bring up a Command window (Start>Run>”cmd”)
  2. Type SET, and press Enter
  3. This will give you a list of all the Environment Variables available to you.
  4. Find the one you want to update, type SET YourEnvironmentVariableName=YourVariableValue
  5. Type SET, and press Enter to check the new value
  6. Done.

Note – you’ll need to have administrator privileges to perform the above actions.

New PASS Data Warehousing and Business Intelligence Virtual Chapter Events

Posted on by

So I finally got round to reading the latest PASS Newsletter and have seen that there are a number of great events coming up soon for the PASS BI VC.

The Data Warehousing and BI Virtual Chapter was started up Amy Lewis (@amy_lewisAZ), Tim Mitchell (Blog | @tim_mitchell) & Adam Jorgensen (Blog | @adam_jorgensen). If you are not already signed up for PASS, and in particular this VC, then shame on you. You should be. There are a ton of great resources and they are there for YOUR benefit!

The BI Virtual Chapter can be found here: http://bi.sqlpass.org/. Bookmark it now!! There is also a Twitter account for the BI VC (@PASSBIVC)

There are loads of great events coming up too. Here is a summary of what is on offer:

Date Name Links
2011/02/14 Master Data Services Development with Office 2010 Live Meeting
2011/02/23 Multidimensional Reporting: MDX Essentials for Report Design Live Meeting
2011/03/09 Introducing Microsoft’s Enterprise Data Information Management Products Live Meeting
2011/03/10 Denali Column Store: Data Does the Twist and Analytics Shout Live Meeting
2011/03/14 Governance Program using Master Data Services Live Meeting
2011/03/16 Working with Cube Writeback in Analysis Services Live Meeting
2011/03/23 SSRS & Report Builder: Now that it is Self Service Live Meeting

All events start at 12pm EST

Each of these are described in more detail on the BI Virtual Chapter website

 

I am thoroughly looking to each of these and want to express my thanks to the organisers for pulling this Virtual Chapter together.

Template Locations for SSRS/SSIS files for SQL Server 2008

Posted on by

I like to try and create templates for each type of project that I am working on. This takes the work out of having to do the same tasks over and over again when it comes to creating new items (Reports Services Reports, Integration Services Packages). So where do you place those templates that you have created? I’m glad you asked:

 

SSIS Templates go here:

%Program Files%Microsoft Visual Studio 9.0Common7IDEPrivateAssembliesProjectItemsDataTransformationProjectDataTransformationItems

SSRS Templates go here:

%Program Files%Microsoft Visual Studio 9.0Common7IDEPrivateAssembliesProjectItemsReportProject

Cloning VirtualBox Virtual Disk Images (vdi files)

Posted on by

I have recently starting using VirtualBox as my medium of virtualisation on my laptop and it works pretty well – I’ve even got Mac OSX working. But one thing I struggled with at first is the cloning of the VirtualBox Disk Images – the hard disk files that the Virtual Machines use. Once I had found out how to do this without encountering any errors I figured I would make a note of it so that I always have a referral point – and now so do you.

Copy/Paste Anyone?

No. That won’t work. Trust me I’ve tried…What you will end up with is two .vdi files with conflicting UUID’s. This means that you won’t be able to run them side by side, or indeed have them on the same physical machine.

Using the Command Line tool VBoxManage

You’re getting warmer now. This is the tool to use to clone your existing .vdi files. But you can’t simply bash out your script and expect it to work straight away. There’s a little bit of prep to do. So take note of the following steps and, if you follow them, you’ll have as many copies of your .vdi files as you want.

Assumptions:

  • VirtualBox is installed
  • You already have a base VM set up, complete with Virtual Hard Disk (vdi) – i.e. you have installed an OS

 

 

  1. Open VirtualBox
  2. Click on the Virtual Machine you want to clone
  3. Now click File>Virtual Media Manager image
  4. When the Virtual Machine Manager appears, click on the .vdi file that you want to clone and then click Release – this will release the .vdi from the VM. THIS IS IMPORTANT. If you miss this step you will encounter UUID conflicts. image
  5. Fire up command prompt (Start>Run>cmd [enter])
  6. Enter the following command:

< File path of VBoxManage> clonehd “<file path of the source .vdi file>” “<file path of the target (cloned) .vdi file>”

For me this command was:

“C:Program FilesOracleVirtualBoxVBoxManage” clonehd “D:Virtual MachinesVirtualBox VMsWindows7Windows7.vdi” “D:Virtual MachinesVirtualBox VMsWindows7SQLServer2005Windows7SQLServer2005.vdi”

If you see the following, your .vdi base file is being copied correctly. Check your target directory for the cloned .vdi file.

image

Once the clone is complete you should see the following:

image

The key thing to note in this screen is that a New UUID has been assigned to the cloned .vdi file. Remember, if you hadn’t released the base .vdi file from its VM, then you’ll get an error when executing the clonehd command – See step 5.

 

  1. Go Back to Virtual Box
  2. Click the New icon, to create a new VM
  3. Enter a Name for your new VM and select the Operating System that you’ll be using.
  4. Adjust the amount of memory that you need for the VM
  5. Now, on the Virtual Hard Disk screen select Use Existing Hard Disk.image
  6. Click on the folder icon and navigate to the .vdi file that you have just cloned and then click Next.
  7. Review the summary, then click the Finish button and you’re done. The new VM should now be available in your list of VMs

image

 

That’s it!

Set Parameter Defaults for Stored Procedures

Posted on by

Did you know that you can set defaults for your Stored Procedure parameters? No? Well, it turns out that you can. And it’s pretty straight forward too.

To set a default to a stored procedure parameter all you have to do is add “= <value>” when you declare your parameters in your CREATE PROCEDURE statement.

CREATE PROCEDURE usp_MyStoredProcedure (@Parameter1 DATE = GetDate(), @Parameter2 VARCHAR(10) = ‘MyValue’)

That’s it!

TSQL Tuesday #014 – Resolutions

Posted on by

It’s that time again. Time for another TSQL Tuesday and this round, brought to you by Jen McCowan (Blog | @MidnightDBA), is all about Resolutions. Since I’ve not mentioned T-SQL Tuesdays on this blog before here’s a quick rundown of what it’s all about, as described by its creator Adam Machanic (Blog | @AdamMachanic:

T-SQL Tuesday is the SQL Server blogosphere’s first recurring, revolving blog party. The idea is simple: Each month a blog will host the party, and about a week before the second Tuesday of the month a theme will be posted. Any blogger that wishes to participate is invited to write a post on the chosen topic. The event is called "T-SQL Tuesday", but any post that is related to both SQL Server and the theme is fair game. So feel free to post about SSIS, SSRS, Java integration, or whatever other technologies you’re working with in conjunction with SQL Server. Even if your post includes no T-SQL we still want to see it. 

Read more about T-SQL Tuesday…

image

This T-SQL Tuesday (and its particular topic) couldn’t have come at a better time. I have only just posted about activity on the blog. In it I mention that my next post will list my goals for 2011. So I guess I get a 2-for-1 with this post!

So what am I resolving to do, or do better (or not do) in 2011? Well, my theme for this year is ‘more’ (it’s also my 2 year old son’s favourite word. Well, that and a very emphatic NO!):

Blog ‘more’

In early 2010 I set myself the goal of blogging at least once a week. I started fairly well and even got to scheduling posts as opposed to just hitting ‘publish’ once I was happy with what I had written. But life happened. I got to working crazy hours for about 6 months and then moved our family from London, UK to Melbourne, Australia. This effectively put a damper on writing for me and I finished the year with a measly 21 posts for 2010.

While the above resolution does not have an actual figure committed to it, I definitely need to do better than 21! And I am already making progress. I have posts scheduled through to mid-March, so I have a better feeling about this one already.

Learn ‘more’

Since the middle of last year I have been meaning to take the MCITP exams (70-452 & 70-448). But since I failed to plan for them then, they are now a 2011 challenge. I’m resolving to get these ‘done and dusted’ in Q1, and I already have drawn up a schedule to get this done.

I also am resolving to learn more about the ‘soft skills’ so often overlooked by technical professionals, but so important to the successful outcome of a project. Communication (with clients, staff members & the SQL Community), personal effectiveness, strategic thinking, team building and problem solving are some of the areas I’ll be looking at building and fine tuning during the course of the year.

Project Management – a new area for me, but one that I am starting to get very interested in. I’ve seen first hand how NOT having the necessary PM skills can lead to a project being pulled. So I’m taking steps to ensure that it doesn’t happen to any project that I lead.

Attend ‘more’ SQL Server Events

2010 was a busy year. So my attendance at various events was sporadic to say the least. I did get to SQLBits V in 2009 (Post 1, Post 2, Recap 1, Recap 2), but missed out on attending either of the events in 2010. These are such great events and if you get the chance to go you shouldn’t hesitate – I believe that the next one is to be held in Brighton, on the South Coast of England.

imageSo this year I have diarised the Local User Group events and aim to attend each of them. On the conference side of things, I may have cheated a little, as just before the end of last year I confirmed my booking for the PASS Summit 2011!! I am properly stoked about this and will be doing as much as possible to ensure that my first Summit experience is a great one. Which leads me to my next resolution…


Interact ‘more’ with the SQL Community

Twitter, blogging, comments, forums, user groups, conferences, email, Skype, and of course, meeting more people. Since my days are pretty busy during ‘working hours’, I’m going to try setting aside some time before the day really begins to get back in touch with the SQL Server Pulse.

 

Sleep ‘less’

Something has to give. Always.

In order to achieve what I have set out here I need to give up on something that I hold dear. Precious sleep. I used to think know that I needed at least 9 hours sleep a night. Then I became a dad. Now I know that I will get by on 7 hours.  And when a new bundle of joy (in the form of a daughter) joins us in about 6 weeks, I’ll ‘know’ I can get by on less than 5.

 

I’m looking forward to a great year. A year of family expansion, personal growth and community involvement. What do you have lined up?

Blog Stats – 2010 in a nutshell

Posted on by

Now that 2010 is done and dusted, I figured it was time to have a look at how the blog has done over the last year.

Before I even had a look at any of the numbers I knew that there was one area that would be way below the level that I had hoped for. This number refers to the number of blog posts that I put out during the year. I had started the year with a view to post at least once a week – 52 blog posts. Well that didn’t happen… only a paltry 21 made it online last year. This needs to change this year.

Here’s a look at what took place:

 

Overall Stats
Blog Posts 21
Comments 216
Total Site Visits 34,618

 

Traffic Sources
Source Visits
Search Engines 28,880
Site Referrals 2950
Direct 2786
Other 82

 

Top Content
Blog Post Pageviews
The DatePart() Function in SSRS 5,540
The InScope() Function and the key to formatting subtotals in SSRS Matrix Objects 5,421
Custom Visibility Toggling in SSRS (2005) 4,967
Custom Visibility Toggling in SSRS 2008 2,819
Managing Reporting Services Layouts using the Rectangle in SSRS 2,778
The Join() Function in SSRS 2,415
The DateAdd() function in SSRS 1,908
The Choose() function in SSRS 1,614
Creating Subtotals in a Matrix in SSRS (2005/2008) 1,663
Homepage 1,536

 

Top 5 Locations
Country Visits
United States 15,380
United Kingdom 4,013
India 3,383
Canada 1,438
Australia 1,414

 

I’m pretty happy with these numbers and they certainly give me a benchmark for this year. If you have any feedback on these numbers let me know, I’m always interested to hear your views.

In my next post I’ll outline my goals for the blog and what I hope to achieve for 2011.

De-cluttering the SSIS Toolbox

Posted on by

If you are developing solutions in SSIS and are having to deal with limited screen real estate, here’s a really quick tip to de-clutter that SSIS toolbox and hide some of the Control/Data flow components that you don’t use.

With BIDS (that would be Business Intelligence Development Studio…) open and an SSIS project loaded, bring up the toolbox and right-click it. You will see the following menu:

image

Select the Choose Items… option. Now, click on the SSIS Control Flow Items tab and deselect all the components that you are not using.

image

Repeat the above step for the SSIS Data Flow Items and Maintenance Tasks tabs.

Click OK and you are all done. The components listed should now reflect just those that you will actually use and should mean less vertical scrolling.

Isn't that better...

 

But wait, there’s another option…

If you would prefer to have all your components immediately available, but still want to organise the toolbox, then follow these easy steps:

 

image

Right-click the toolbox and choose Add Tab. Give the Tab a nice descriptive name.

image

Now click on and drag into that group/tab all the components that you are not using regularly, or indeed the ones you are (if that’s what you would prefer).

All that’s left is to apply a Sort Items Alphabetically and collapse the groups you are not concerned with and you have a nice tidy toolbox.

 

Happy days!