Category Archives: Reporting Services

Sorting on Multiple Columns in SSRS

Posted on by

There have been several cases lately where people have asked the question “Can I sort on multiple columns in my Table/Matrix report?” More often than not the answer given is an emphatic “No”. The fact is that you CAN sort on multiple columns by simply incorporating use of  the Shift key.

Assuming you have already set up interactive sorting on the columns you want to be able to sort by; run/preview your report. As you are already aware, the columns which can be sorted on have a two tiny arrows right-aligned in the column header. To sort on any one column just click on the arrows. Click on the arrow again to sort in the other direction.

MultipleColumnSorting

To sort on multiple columns, hold down the Shift key whilst clicking the arrows icons of the columns that you want to sort on. Start with the column you want to sort on first and then move to the next sort order, etc, etc. Click on any column arrow icon twice to change the direction (continue to hold down the Shift key until you have sorted on all the columns you wish to sort by).

Dataset sorted by Year (asc), Quarter (asc), Total (desc)MultipleColumnSorting_2

And there you have it. Multiple Column sorting in SSRS!

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

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

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

Posted on by

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.

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

Posted on by

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

Custom Visibility Toggling SSRS 2008

Posted on by

A while back I wrote a post about how to toggle the visibility of rows/columns in tables/matrices for SSRS 2005. In the comments of that article someone asked how this was done in SSRS 2008. The short answer was that the method is the same. However, setting all the properties can be confusing as their whereabouts is a little different. Hopefully this post will clear things up.

Let’s do this by way of example:

I created a project with a single report. The Data source is AdventureWorks2008 and we are looking at Total Sales figures by Year & Month (keep it simple right?). I have set up a matrix object and added the required fields and set subtotals for Month & Year. See Fig 1. below:

Objective: Enable Toggling of the Month group, using the Year Group as the Toggle Item

At this point I want to point out two key areas, as this is where we will be concentrating our efforts:

  1. The Row/Column Groups section at the bottom of the designer
  2. The Visibility Properties at the bottom right corner (Last item in the Properties list – but only when an object is selected)

Now, select the drop-down arrow for the Month Row group (See Fig 2.), choose Properties and then select the Visibility tab.

The objective is to enable toggling of the Month rows in the Matrix. We could choose to Show or Hide the Month rows when the report is initially run, or to make this decision based on an expression. Using an expression will determine which rows are expanded and which are collapsed when the report is run initially. I have chosen to use an expression:

=IIF(Fields!YEAR.value=2001, False, True)

This says that if the Year group value is ‘2001’ then show the Month Sales Total values (‘Expanded’), else Hide the Month Sales Total values (‘Collapsed’). See Fig 3. below:

Also note (in Fig 3. above) that I have set the YEAR cell as the Toggle Item. We’ll configure this next.

Once you have set the above properties go back to the designer and highlight the YEAR cell in order to view its properties. These should be on the bottom right, otherwise right-click the cell to bring up the properties window. Find the Visibility > InitialToggleState property and set it using the following Expression:

=IIF(Fields!YEAR.Value = 2001, True, False)

This says that if the Year group value is ‘2001’ then show the toggle icon as ‘Expanded’ (True), else show it as ‘Collapsed’ (False). This allows us to ensure that we are in sync with the expression we used to set the initial visibility of the Month rows. Fig 4. shows the end result of all this hard work:

The Visibility Properties

Understanding what each of the three visibility properties does will help to use them effectively.

Hidden – Can be set to either ‘True’ or ‘False’. This can be done by selecting True or False from the drop down, or by using an expression to set either of those values after evaluating a condition. True equates to the object being hidden and False to the object being shown (tricky eh?).

InitialToggleState – This is only applicable if the object that you have selected can toggle the visibility of other objects. It refers to the state of the toggle icon and can either be ‘Collapsed’ (‘+’) or ‘Expanded’ (‘-‘). As explained by way of example above, setting this to ‘’True’ sets the icon to the ‘Expanded’ state, and False to the ‘Collapsed’ state.

ToggleItem – Use this property to set the object that will enable the user to toggle the visibility of another item. In the example above, I have chosen YEAR as my toggle item for the Month Group.

Beware the Gremlins

You need to be aware of setting properties for the right objects or groups, or you may find that your report does not display correctly:

Fig 4. shows what happens when you set the Visibility properties for the Month & Data CELLS in the report instead of the Month Group properties. Setting the Month Group visibility properties means that you won’t get that nasty white space, and there’s no need to set the visibility of the cells (in this instance).

I have placed the example project in a nice little zip file for all you folks that might want such a thing.

Download Reports (.RDL) files for editing (SSRS 2005)

Posted on by

If you ever happen to be in a situation were you do not have access to or have ‘misplaced’ the SSRS report files (.rdl) and you need to edit one (or more) of the reports on your server, there is an easy way to download the .rdl files.

Follow these easy steps:

  1. Open SQL Server Management Studio,
  2. Log on to the Reporting Services Instance that contains the reports you are looking for
  3. Browser through the directory structure to the report that needs to be updated
  4. Right-click the report and choose Edit…
  5. Now follow the prompts to save the file to a destination of your choice
  6. Open BIDS & create a new project/solution
  7. Import the (previously) saved .rdl file into your SSRS project.
  8. Edit away to your hearts content….

 

I’m not going to ask why you don’t have access to or have misplaced the files in the first place (No source code management? Seriously?). I am sure there are a myriad of reasons that you can come up with. This post merely provides a quick way to recover your files and continue edits/updates that may be required.

The DatePart() Function in SSRS

Posted on by

Using the DatePart() Function in SSRS can be tricky, even at the best of times. I have pulled my hair out too often trying to remember what syntax should be used, so have resorted to writing down all my findings. This is so that you don’t have to go through the same agony & frustration as I did, as well as for my own benefit (my memory is shot…)

What it does: Returns an Integer containing the specified part of a given date

Syntax: DatePart(interval, date[,firstdayofweek[,firstweekofyear]])

There are two ways to use this function:

  • Using the DateInterval collection
  • Using a string expression
 
Using the DateInterval Collection

Let’s start with an example:

To return the current month: =DatePart(DateInterval.Month, Today())

Now, in the expression builder (in BIDS), it will look like there’s something the matter with what we have just written:

You can ignore this, the expression will work. An important point to note when using the DateInterval collection, is that you must use a date that is of type Datetime. If you use a string representation of a date (“2009/11/13”), then you’ll need to convert this to Datetime using CDate(“2009/11/13”) before the expression will work. Thus:

This will work:  =DatePart(DateInterval.Month, CDate(“2009/11/13”))

This won’t:  = DatePart(DateInterval.Month, “2009/11/13”)

When you use the DatePart function to identify the day of the week you can specify what the First Day Of The Week is (defaults to Sunday if this is not specified). You can do this by accessing the FirstDayOfWeek collection. Example – To Return the day of the week, specifying Monday as the first day of the week:

DatePart(DateInterval.Weekday, CDate(“2009/11/13”), FirstDayOfWeek.Monday) returns 5 (Friday)

All the possibilities for using the DatePart() function with the DateInterval Collection:

The following table shows all the possibilities for the DatePart Function using the DateInterval collection. All expressions use a Parameter called MyDate (type = Datetime), which has a value of “2009/11/13 12:34:23”

Expression Result
DatePart(DateInterval.Second, Parameters!MyDate.Value) 23
DatePart(DateInterval.Minute, Parameters!MyDate.Value) 34
DatePart(DateInterval.Hour, Parameters!MyDate.Value) 12
DatePart(DateInterval.Weekday, Parameters!MyDate.Value, FirstDayOfWeek.Monday) 5
DatePart(DateInterval.Day, Parameters!MyDate.Value) 13
DatePart(DateInterval.DayOfYear, Parameters!MyDate.Value) 317
DatePart(DateInterval.WeekOfYear, Parameters!MyDate.Value) 46
DatePart(DateInterval.Month, Parameters!MyDate.Value) 11
DatePart(DateInterval.Quarter, Parameters!MyDate.Value) 4
DatePart(DateInterval.Year, Parameters!MyDate.Value) 2009

 

Using a String Expression

Again, let’s start with an example:

To return the current month:  =DatePart(“m”, Today())

Using a String expression for your interval enables you to use a date that is of datatype String:

=DatePart(“m”, “2009/11/13”)

All the possibilities for using the DatePart() function with a String Expression:

As noted previously, using a string expression for the interval allows you to use a date value that is of the String datatype. You can of course use a Datetime value if you wish to.

This table shows all the possibilities for the DatePart function using a String Expression for the interval. All expressions use a Parameter called MyDate (type = String), which has a value of “2009/11/13 12:34:23”

Date Part Expression Result
Second DatePart(“s”, Parameters!MyDate.Value) 23
Minute DatePart(“n”, Parameters!MyDate.Value) 34
Hours DatePart(“h”, Parameters!MyDate.Value) 12
Day of Week DatePart(“w”, Parameters!MyDate.Value, FirstDayOfWeek.Monday) 5
Day of Month DatePart(“d”, Parameters!MyDate.Value) 13
Day of Year DatePart(“y”, Parameters!MyDate.Value) 317
Week of Year DatePart(“ww”, Parameters!MyDate.Value) 46
Month DatePart(“m”, Parameters!MyDate.Value) 11
Quarter DatePart(“q”, Parameters!MyDate.Value) 4
Year DatePart(“yyyy”, Parameters!MyDate.Value) 2009

 

Is there a preferred method?

Ul
timately, no. At the end of the day, all the results are the same. What is my preference? I use the DateInterval collection, mainly becuase I like that fact that you can’t get confused about which date part you are using (w/ww, y/yyyy, n….). It’s very clear to anyone who is reading the code that (for example) DateInterval.WeekOfYear refers to the week of the year and nothing else. It also forces you to use a Datetime Value, which safeguards against invalid dates (sort of).

I hope this clears things up a little!

Troubleshooting SSRS Data Driven Subscriptions (SS 2005)

Posted on by

Let’s just assume that…

You’ve created your data driven subscription (File share) for your reporting services report, and everything is as it should be. The subscribers table has been created, populated and correctly mapped to the required fields in the subscription (FileName, Path, Format, Parameters, etc), the schedule is set. And then you test your subscription.

The Error Message Status

The schedule has run and you find this very helpful error message status beside you subscription in Report Manager. Scratching your head you dive into the Error & Event logs searching (in vain) for those few lines that will shed some light on the question “Why did those 2 errors occur?” Nothing.

Found ‘em!

Hidden away in its own folder are the Reporting Services Log Files:

SQL Sever 2005 –> *C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles  

SQL Server 2008R2 –> *C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesLogFiles

*These may be different depending on your installation

More specifically, you’ll need to look at the ReportServerService__mm_dd_yyyy_hh__mm_ss.log files. These files will list, among other things, any errors encountered in your Data Driven Subscriptions. Here’s mine:

From this I could deduce that there was a problem with the filename that was being used for one of the reports. Once I had reviewed all the filenames being used and had updated those that contained the special characters (2 of them), the subscription ran without a hitch. Problem solved.

So the bottom line here is, if you have any issues with you reports look in the reporting services log file.