Tag Archives: SSRS

T-SQL Tuesday #016 – Aggregations

Posted on by
3

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

Template Locations for SSRS/SSIS files for SQL Server 2008

Posted on by
Reply

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.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems\

SSRS Templates go here:

%Program Files%Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\

Reporting Services (2005) “http://localhost/reports” authentication error

Posted on by
5

I have encountered this issue on two separate occasions now. The first was during a report deployment to a new Report Server Virtual Directory and the second was trying to access the report manager via the URL: http://localhost/Reports. Each time I was presented with the same "Authentication Required" "Enter username and password for "" at http://localhost" screen:

On both occasions I searched high and low for a solution to the problem using a few trusty searches , but could not come up with an answer that worked for me. To cut a long story short the simple solution was to ensure that the URL was a) correct and b) fully qualified.

In the first instance (report deployment) the name of the Report Server Virtual Directory was a Named instance and so trying to deploy to http://localhost/reports was never going to work. Ensuring the right virtual directory was in the URL made all the difference. Go figure.

In the second instance (accessing the Report Manager) I had installed a default configuration of Reporting Services and therefore the setup had created the Virtual Directories required (Reports & ReportServer). However, having typed in the URL http://localhost/Reports I got the Authentication Required screen (as above). I found numerous solutions (supposedly) to this problem, all of which are listed at the end of the post, but I eventually just tried the full path of the home page: http://localhost/Reports/Pages/Folder.aspx and it worked like a dream. Again. Go figure.

Why the full path had to provided I don’t know. On previous occasions I have not have to do so and have been able to connect to the Report Manager using the abbreviated URL (http://localhost/Reports). If anyone does have a clue as to why this is I would gladly welcome the comments.

Things to note:

I am running Windows XP Pro SP2 with SQL Server 2005 Developer Edition.

————————————————————————

Other possible solutions, or at least other areas to check when experiencing this issue:

1. If you are using Firefox and still have problems with Authentication (whilst there are no issues with IE) try this very quick and easy fix.

2. Checking the security settings in Control Panel>Administrator Tools> IIS> Default website and right clicking Reports, choose Properties>Directory Security>Anonymous Access & Authentication control. A number of people suggest using the Anonymous setting only, although I have found that although you can then navigate to http://localhost/reports, no folder structure is available to you. It is therefore suggested that you use the Integrated Windows Authentication only.

3. Ensure that all the settings in Reporting Services Configuration are green. If not, attend to each of the areas in red.

4. Any other ideas? Post a comment that I can include here.

Hopefully, you haven’t had as painful a journey as I have in getting this exceedingly annoying issue (but scarily easy fix) sorted out.

SQLBits VI – The 6 Sets.

Posted on by
Reply

It’s almost time for another SQLBits, and in my [humble] opinion, it’s not soon enough. These free conferences are fantastic and if you do have the opportunity to attend I suggest you do so. Whilst I have only a small idea as to what goes into organising a conference, I am pretty sure that it is a major undertaking. One that I am glad Simon Sabin (Blog|Twitter) and the rest of the SQLBits team (and let’s not forget volunteers here) are handling, and handling rather well. If you are keen to help out, in any way, contact Simon and offer your services.

I have only been to one SQLBits Conference (SQLBits V, Newport, Wales) and have talked about it here, here and here. I liked it. I liked it a lot (as if you couldn’t tell, right?). I got to meet loads of sqlservery people and had a number of good conversations, attend several brilliant presentations and a number of good ones (most of which I learned a bunch of new things), won a Redgate SQL Compare license (which I am still waiting for btw, Redgate, nudge nudge) and generally had a great time. While SQLBits V was a 3 day affair, SQLBits VI will be 1 day only and the theme is Performance Tuning and Scalability. Oh, and it’s free.

Conference Details

SQLBits VI will be held in London, and more specifically, at the Church House Conference Center, Westminster, London, UK on 16th April 2010. Starting at around 8am (for registration, swag-bag pick up and of course a few obligatory bacon baps) the conference kicks off with sessions across several tracks:

  • Database Administration
  • Development
  • Business Intelligence

With the focus being on performance tuning and scalability for all tracks, this promises to be a great conference for learning some interesting things.

Registration is open (and has been for a little while, if I’m being honest), but you will have to be quick. Becuase it’s only a 1 day conference, but mostly because it’s such a great conference and it’s FREE, places tend to be snapped up pretty darn quick. Also, if you register for SQLBits VI before 30th March 2010 you stand a chance of winning registration to the European PASS Summit (Germany), if you attend SQLBits VI then you stand a chance to win a registration to the PASS Summit (Seattle, USA).

One point on registration. As it is free, people tend to register even if they are not sure if they will actually attend. While this is not a bad thing in itself, if you are one of those people AND it turns out that you cannot attend then please cancel your registration. This frees up a place for someone else that can make it (but was too late [initially] for registration). There’s a lot that goes into planning a conference and most of this is done around the number of attendees. If a number of those registrants don’t pitch up, then there is a not only a whole lot of wasted money but a whole lot of wasted opportunity. Don’t be selfish, think of the community and act accordingly. Remember, it’s free!

Remember the after party

The sessions are bound to be fantastic, and you are likely to learn a great deal. But go home early at your peril. The prize giving, party and social after all the day’s events is not something you will want to miss. Why? Well, this is a prime opportunity to really get to know more SQL Server people. People that may well become your friends down the line, or at the very least be a contact point should you need assistance on a specific topic (the SQL Server Community is pretty awesome that way). It is also the best time to find those elusive speakers and ask them the questions that you have prepared. It’s a prime time to do this as the speakers are not stressed out about their presentations and are in a more relaxed and approachable state. They are much more likely to entertain your <ahem>query </ahem>, especially if you hand them a beer before you do so.

So, stay out a little longer than 6pm and make the most of a wonderful opportunity.

And finally…

I’m looking forward to catching up with some old acquaintances and learning a lot of new tricks on how to tune SSIS Packages and Scale Reporting Services Solutions. I’ll also be taking some notes on presentation styles and on what makes a speaker/session a great speaker/session, as I’m thinking getting on to the presentation bandwagon….in the hopefully not-too-distant future.

 

See you at SQLBits VI!

Creating Subtotals in a Matrix in SSRS (2005/2008)

Posted on by
10

This is a quick look at how to create and use subtotals in your Matrix objects.

Subtotals 101

SQL Server Reporting Services makes it relatively easy to add totals to your columns and rows in a matrix object. In short, it’s as easy as right-click>Subtotal. Seriously, that’s it. Well, unless you want to format them, but that’s another issue entirely (an not quite as straight forward).

Let’s do this step-by-step :

  1. Open or create a new SSRS project
  2. Add a report item to your project.
  3. Add a dataset
  4. Add a matrix object
  5. Configure matrix to desired specification
  6. Add totals to outer groups by right-clicking the outer column/row group for which you require the subtotals and select subtotal.
  7. Add subtotals for inner groups by right-clicking the inner column/row group for which you require the subtotals and select subtotal.
  8. Tidy up by hiding the inner group line items initially – by right-clicking the inner group and selecting Properties>Visibility and then setting the properties accordingly.

Here’s a quick 2 minute screen cast of how to create subtotals in a Matrix object in SSRS 2005. It illustrates creating row group Totals & subtotals, but applies to column groups as well:

Here’s a link to another version if you are having any trouble: http://www.screencast.com/t/OGE1MmI4