Using Actions Within SSRS Maps

In my monthly SSRS 101 class ( one of the topics we cover is using the map in report builder to show sales concentrations in the United States. In the example we use, our final report looks similar to the image below:


In the class we use “Actions” to allow the user to click a state name in the table and navigate to a different report that shows state specific sales information. I’ve had multiple students ask if it’s possible to enable the same type of navigation within the map itself. So many people have asked actually, that I thought I’d do a quick write up on how you can implement it.

Implementing the actions within the map component is basically the same as implementing action within the Tablix. We still use the “Action” option, and choose which report we would like to link to, and then pass in the appropriate parameters. The only difference with the map is that the correct action settings are somewhat hidden and a little difficult to find if you haven’t worked with the map much. To implement this functionality in the map you’ll need to do the following:

  1. Open your report in Report Builder or BIDS/SSDT
  2. Click twice on the map object until you get the “Map Layers” window
  3. In the “Map Layers” window click the dropdown triangle to the right of the “creepy eye”
  4. From the available options select “Polygon Properties…”CreepyEye2
  5. From here you can select the “Action” option and start to link your report as you normally would. In our case we’re linking to a report that is currently deployed to SharePoint and accepts parameters for the StateProvinceName, EnglishProductCategory, and EnglishProductSubCategory.


That really all there is to it. If we run the report now, we should be able to click on individual states within the map and be redirected to our “detail” report (which we would have to build separately). In the example report you saw earlier in the post, if we click on California in the map we’ll be redirected to a report that looks similar to the following:


That’s it for this post, hopefully you found it useful, and as always….happy reporting!

Introduction To Indicators With Reporting Services

In this walkthrough, we’ll take look at using the Indicator visualization in SSRS. Indicators are a great tool to have at your disposal, particularly if you’re creating “dashboard” reports. The general idea of a dashboard is to give the user a high level overview of how areas of interest area performing, and indicators are a really great visual aid to help users quickly identify the current performance of a metric. Let’s take a look at setting up a simple indicator in SSRS report builder. (This walkthrough does assume some general knowledge of SSRS and how to create a data source and data set)

  1. Open Report Builder, and create a new blank report
  2. Create a data source connected to a SQL Server data source (any database will do, we’ll actually be hardcoding our data in the data set)
  3. Create a data set in report builder and paste in the following SQL into the query window:

    SELECT 'Category A' 'Category', 33 'Value' UNION SELECT 'Category B', 66 UNION SELECT 'Category C', 99 UNION SELECT 'Category D', 20

  4. Add a table to the body of your report
  5. Drag “Category” and “Value” from the dataset you created in step 3 to the first two columns of your new table. If you run/preview your report at this point, your table should look like the image below


  6. Back in the Report Builder design view, navigate to the insert tab, and select the Indicator Visualization


  7. Place the Indicator into the 3rd column of your table. This should bring up a new window that allows you to choose from a variety of different visualizations. In this example we’re going to use the classic red, yellow, green circles from the “Shapes” category. Make this selection (shown below) and then click “OK”


  8. Left click twice into the table cell that contains the Indicator to select it. The “Gauge Data” window should appear once it’s been selected
  9. Click the “Gauge Properties” button on the “Gauge Data” window.


  10. Click on the “Values and States” menu option
  11. In the dropdown labeled “Value” select [Sum(Value)] from the available options
  12. In the dropdown labeled “States Measurement Unit” select “Numeric” from the available options


  13. Click “OK”
  14. Preview the report.

At this point your table should look like this:


Note that if we look back to our “Indicator Properties” screen and compare the “Start” and “End” values (often called thresholds) for each differently colored indicator, we can see that the red indicator has an end value of 33. Since “Category A” falls into the range of 0-33 it’s marked with a red indicator in our table. One important thing to note about the list of start/end values for each indicator listed on the “Indicator Properties” screen is that they’re evaluated in order from top to bottom, so the order that they appear in is important. If there happen to be ranges that overlap, SSRS will only look as far as the first matching range and then look no further (sort of similar to a switch statement). To further illustrate this point, we could set the “Start” value to zero for all three indicator colors in the properties window and our table would look exactly the same when the report is run. The order of the indicators can be easily modified by simply using the up and down arrows found on the “Indicator Properties” screen.

The “Indicator Properties” screen also allows you to modify just about everything else you might want to modify about your indicator states, such as the color and shape that is presented. If you’re feeling really feisty, you can even add additional indicator states by simply clicking the add button. Who knows, maybe a blue sad face emoticon will be a really useful range/state to add. While you can go kind of crazy with the colors and shapes if you want, make sure to keep the overall goal of your report in mind. Indicators are really good at enabling users to quickly identify good/mediocre/poor performing areas “at a glance”, but you need to keep the number of states low. Also red/yellow/green are pretty much universally accepted as bad/ok/good. Creating an indicator that has 20 states and every color under the rainbow will probably be pretty useless to the report user.

I think that about wraps up our intro to indicators, hopefully you found it useful. Until next time, happy reporting!

Formatting a Map Legend to Currency (Or Whatever) in SSRS

In this quick example we’re going to take a look at how you can format the numbers in a map legend to be currency, or whatever you want. By default the numbers appearing in a map legend are formatted to numeric with zero decimal places. Often times you might want this to be currency, or perhaps even percent instead. For whatever reason, several of the options with the map are fairly well hidden and a little difficult to find, particularly for people new to SSRS. Follow the steps below to achieve legend formatting nirvana:

  1. Open your report in Report Builder or BIDS/SSDT
  2. Click twice on the map object until you get the “Map Layers” window
  3. In the “Map Layers” window click the dropdown triangle to the right of the “creepy eye”
  4. From the available options select “Polygon Color Rule…”
  5. Select “Legend” from the menu options on the left
  6. In the textbox labeled “Legend Text” you should see something similar to the following “#FROMVALUE{N0} - #TOVALUE{N0}”. This is what SSRS uses to format the legend. The “N0” portion of this is telling Reporting Services to format the legend as Numerical with zero decimal places. To have it format as currency, simply change the N0 to C0 like so “#FROMVALUE{C0} - #TOVALUE{C0}”. Note that you need to change the N to a C in both the FROMVALUE and the TOVALUE portions.


  7. Click OK

That should do the trick! Now if you run the report you should see the dollar sign in your legend.


If you want to format the legend as something other than dollars, there’s a pretty easy way to figure out what you should use to replace the N0 value. The easiest way is probably to place a textbox on the report, manually type some numbers in it and format it however you desire using the textbox properties window (available via right clicking on the textbox). Once the textbox is formatted to your liking, you can open the properties window (the full report properties window, available under the view tab in Report Builder) and then looking at the Number Format script that was generated. You’ll want to make sure you have the textbox you just created selected when you’re looking through the properties, as the properties window is context sensitive. In the example below you can see I’ve formatted a textbox to show pounds instead of dollars.


In this example I would replace N0 with '£'0 to achieve my desired formatting. Now go forth and format map legends to thine hearts content! Happy reporting!

Adding an Expandable Report Details Window In SSRS

One of the most challenging things report writers face is getting all the elements that users want to actually fit on a report. I know I’m constantly battling for more space by trying to make other things smaller. Since report real-estate is somewhat precious, having a “Report Details” section that tells the user about the report they’re looking at often falls by the wayside. A report Details section could contain things like who actually owns the report, details about the calculations used in the report, and who they can contact if they have questions. One way to add this information to the report without having to take up a bunch of space is to put it in a collapsible window within the report, and have it hidden by default. Below we’ll walk through the steps of how we might accomplish this.

  1. Open your report and add a textbox to your report
  2. Type in the text “Report Details” into the new textbox
  3. Note the name of the textbox that you just added….you’re going to need it later. You can find the name of the textbox by clicking on it and then finding the name property in the properties window, or right clicking on the textbox and then going to “Text Box Properties…” in my case the textbox is named “Textbox3”


  4. Add a “Rectangle” next to your textbox
  5. Add as many textboxes inside of your rectangle as you want and type in whatever information you deem important for the users to know. In my quick example I’ve mocked up the following information:


  6. Now that you’ve got your info added, right click on the rectangle object you added and select “Rectangle Properties” from the dropdown
  7. Select “Visibility” from the options on the left
  8. In the section labeled “ When the report is initially run” select “Hide”.
  9. Place a check in the box labeled “Display can be toggled by this report item”
  10. In the dropdown below the checkbox select the name of the textbox that you typed “Report Details” in. In my case it was “Textbox3”


  11. Click “OK”
  12. Run the report

The report should run and automatically hide the rectangle and everything that was inside of it. You should also see a “+” next to the “Report Details” textbox that will allow you to unhide the rectangle if you click on it. The finished example should look similar to the following when initially run


And then once expanded:


You would probably want to add some additional formatting like perhaps a border around the rectangle or maybe even change the background, but I’ll leave the formatting choices up to you. This information can be invaluable to users viewing the report, particularly if the report involves complex calculations. After all, a report that is being misinterpreted can be even worse than having no report at all, and adding a details section to your report can go a long way in helping to clarify what it being shown. Happy reporting!

Enabling Interactive Sort in SSRS Reports

When using any reporting tool, I’m always on the lookout for features that add value/functionality, and are quick/easy to implement. Interactive sorting within SSRS is one of those things that’s so quick and easy to implement at a basic level, that I usually add it to reports without it even being request because it will literally only takes seconds, and I’ve never had anyone come back and say “Hey, we don’t want the ability to sort the data in our report!” With that in mind, let’s take a quick look at how we can implement interactive sorting on a table within SSRS. (This walkthrough does assume some general knowledge of SSRS and how to create a data source and data set)

  1. Open Report Builder, and create a new blank report
  2. Create a data source connected to a SQL Server data source (any database will do, we’ll actually be hardcoding our data in the data set)
  3. Create a data set in report builder and paste in the following SQL into the query window:

    SELECT 'Product A' 'Product', 3000 'SalesAmount' UNION SELECT 'Product B', 10000 UNION SELECT 'Product C', 5000 UNION SELECT 'Product D', 1000 UNION SELECT 'Product E', 7000 UNION SELECT 'Product F', 2000

  4. Add a table to the body of your report
  5. Drag “Product” and “SalesAmount” from the dataset you created in step 3 to the first two columns of your new table. If you run/preview your report at this point, your table should look like the image below


  6. Back in the Report Builder design view, on the header row of the table, right click the cell containing the “Sales Amount” title, and then select “Text Box Properties”


  7. On the left hand side of the “Text Box Properties” window select “Interactive Sorting”
  8. Place a checkmark in the checkbox labeled “Enable interactive sorting on this text box”
  9. In the dropdown box labeled “Sort by” select “[SalesAmount]”


  10. Click “OK”
  11. Preview your report

If all has gone well, you should now see two up/down triangles in the Sales Amount header as shown below highlighted in yellow.


While the graphic actually looks like two different arrows that you can click, it’s actually just one button that will order the column you configured from low to high (or A-Z) on the first click, and then subsequent clicks will reverse the current order. While having this functionality might not be all that useful when there’s only 6 rows of data, it can be fairly useful to the report user when dealing with a small/medium result set of a few hundred or thousand rows. Note that you do want to be mindful of picking the correct column to sort when you’re on the “Textbox Properties” screen, it would be fairly easy to pick a different column and end up with a sort button on “Sales Amount” that actually sorts the “Product” instead. While this functionality is very easy to add, it does require some additional space lengthwise for the header cell you place these in, so if you’ve got a table that is a tight fit width wise that might be a concern. I will also say that I typically only put these on columns where it makes sense that a user might want to sort. For instance sales amount is a pretty good candidate for sorting, but if I had a table with customer contact info in it, I probably wouldn’t put a sort button on the phone number column, as I doubt anyone would ever want to sort on the phone number. It’s also a good Idea to educate the report viewer on use of sorting as well. It’s my experience that some users will sort a table to try and locate something alphabetically because they’re unaware that SSRS has a “Find” function they can use (The find textbox is literally at the top of every report).

If you look back to “Textbox Properties” screen you’ll also notice that the interactive sorting can be applied to groups within the table as well, along with some additional options I’ll leave you to play with on your own. Until next time, Happy reporting!

PerformancePoint and ADOMD.NET

I was recently configuring PerformancePoint in SharePoint 2013 and had deployed the SQL Server 2012 AdventureWorksDW Multidimensional cube for testing purposes. It had been a while since I configured PerformancePoint so thankfully I had written this post previously. As luck would have it I ran into pretty much every roadblock mentioned in that post……and one additional “detour” that I had almost forgot about (that also had a new twist).

If you’re in PerformancePoint Dashboard Designer and trying to create a data source that connects to SSAS, but seeing nothing show up in your Database dropdown (as seen below…..sorta) you might need to install ADOMD.NET 11.


Not only install it, but install it on all servers that run PerformancePoint AND you’ll need to adjust the Web.config file to tell PerformancePoint to use it.  This is of course assuming you’ve set everything else up properly. I’m taking for granted that you’ve already done stuff like

  • Associate an Unattended Service Account to the PerformancePoint Service Application and granted the appropriate users the right to use the unattended account in the Secure Store Service Application
  • Double checked that the service account you’re using for your Unattended Service Account actually has access to at least one db on the SSAS instance you’re trying to connect to

If none of that sounds familiar, check out my post on secure store for Visio Services or my posts on using data refresh with Excel Services…configuring PerformancePoint should be pretty similar to those.

Note that I wasn’t getting any error messages, but instead the database dropdown was just empty. If you’re getting any type of error on the screen shown above, your problem is most likely something else and not ADOMD.NET. Still with me? Is your database dropdown still mocking you at the very moment with its emptiness? Hopefully this will cure your PerformancePoint woes.

So….let’s do this

  1. Go download ADOMD.NET 11 already will ya?….at the time of this writing you can find it here:
  2. Install it on all the first farm devices that’s running PerformancePoint. (something like next, next, finish)
  3. Once ADOMD.NET 11 is installed, navigate to \Program Files\Microsoft Office Servers\15.0\WebServices\PpsMonitoringServer
  4. Locate and make a backup copy of the web.config file found there. (Just in case you mess this all up…..I certainly don’t trust you.)
  5. Open the web.config file and scroll on down to the very bottom. Once you get there you should see something like this:
  6. Change the oldVersion from to
  7. Change the newVersion from to
  8. Save that bad boy
  9. Repeat for every server running PerformancePoint

Completing the above had my database dropdown populating with an abundance of easily selectable db’s for as far as the eye could see. Some say it’s still populating new and wonderful connection options to this very day (results may vary).

I don’t recall having to do an iisreset or anything…although I think I might have closed Dashboard Designer and reopened it after making the changes. Hopefully this helps!

Creating a Heat Map/Color Analytical Map in SQL Reporting Services

In this walkthrough we’ll be creating a heat map/color analytical map in reporting services to display which states in the US have the highest amount of sales. In this example I’ll be using the AdventureWorksDW2012 database for sample data. The AdventureWorks sample database can be downloaded from CodePlex at Simply download the AdventureWorksDW2012_Data.mdf and attach it to your SQL Server instance if you’d like to recreate this example in your own environment. In this example we’ll complete the following steps using report builder.

  1. Create a Data Source that points to our Adventure Works database
  2. Create a Dataset to pull our state and sales example data
  3. Create a map that visually displays which states have the highest sales
  4. Format map key to show currency

Create a Data Source that points to our Adventure Works database

The first thing we need to do in our report is create a data source to tell our report where we’re getting data from. With report builder open, complete the following steps to create a data source for your report:

  1. In the “Report Data” window, right click on the Data Sources folder and then click “Add Data Source”
  2. Name the Data Source “AdventureWorks”
  3. Select “Use a connection embedded in my report”
  4. Select “Microsoft SQL Server” for the connection type
  5. Click the “Build…” button on the Data Source Properties window
  6. Enter the name of your SQL Server or named SQL instance in the “Server name” textbox
  7. Select the database you wish to connect to (in our case the AdventureWorksDW2012 DB)
  8. Click “OK” to return to the “Data Source Properties” page
  9. Click “Test Connection” to ensure your connection is properly setup. If the connection does not succeed, you may need to configure the credentials that your report uses to connect to the database. Please see my blog post here about how to configure credentials
  10. Click “OK” on the “Data Source Properties” window to finish creating the data source

Create a Dataset to pull our state and sales example data

Next we want to create the query/dataset that will get the data for our map. To create the dataset follow these steps:

  1. In the “Report Data” window, right click on the Datasets folder and then click “Add Dataset”
  2. Name the dataset “SalesByState” without any spaces
  3. Select “Use a dataset embedded in my report”
  4. For “Data source” select “AdventureWorks” from the available dropdown
  5. For the “Query type” select “Text”
  6. Paste the following SQL into the query window:

    SELECT DimGeography.StateProvinceName, SUM(FactInternetSales.SalesAmount) AS Sum_SalesAmount FROM DimCustomer INNER JOIN FactInternetSales ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey INNER JOIN DimGeography ON DimCustomer.GeographyKey = DimGeography.GeographyKey WHERE DimGeography.CountryRegionCode = N'US' GROUP BY DimGeography.StateProvinceName

  7. Click “OK” on the “Dataset Properties” window to finish creating the dataset


When finished you should see your new “SalesByState” dataset in the Report Data window. When expanded the dataset should show that it has two columns named “StateProvinceName” and “Sum_SalesAmount” respectively as shown below.


Create a map that visually displays which states have the highest sales

Now that we’ve got our data in order, it time to build or map. In this example we’re going to use the map wizard to build our map for us, however if you’re familiar with ESRI shapefiles you can also build your own maps. To create a map using the map wizard follow these steps:

  1. In report builder click on the “Insert” tab at the top left of the screen
  2. In the “Data Visualizations” grouping select the dropdown arrow under “Map” and then select “Map Wizard”
  3. Select the “Map gallery” radio button if it’s not selected by default
  4. In the “Map Gallery” window select “USA by State Inset” and then click “Next”

  5. The 2nd screen of the wizard has options for adjusting the positioning, zoom (both of which can be adjusted later). It also has options for adjusting the resolution and even adding a map Bing maps overlay onto your map. In our example we’re just going to accepts the defaults and click "Next".

  6. On the 3rd screen of the wizard we get to choose the visualization type of our map. In our case we want each state within the map to change color based on the total sales number for the state. This can be accomplished with the color analytical map. Select ”Color Analytical Map” then click the “Next” button.

  7. On the 4th screen of the wizard, ensure “Choose an existing dataset in this report or a shared dataset” is selected and then select the “SalesByState” data source and click “Next"

  8. On the 5th screen of the wizard you need to map which data goes with which state shape. This is done by using the first section of the screen. The 2nd section of the screen (labeled “Spatial data”) shows examples of valid matching data, while the 3rd section (labeled “Analytical data”) contains all of the data currently in your dataset. To properly map the data do the following
    1. Place a check in the “Match Fields” column next to “STATENAME” (since we have the full state name in our data set)
    2. Select “StateProvinceName” from the “Analytical Dataset Fields” column for the same row.
    3. Click “Next”

  9. On the 6th screen of the wizard, the “Field to visualize” dropdown determines what numerical value will drive the color of each state. In our case this will be the Sum_SalesAmount column from our dataset. if you click the dropdown for this field you will notice that there has been an additional “Sum” added to the column name. The wizard will always add this extra and re-aggregate the data by state for the map. This is a nice feature if you had a dataset where state wasn’t the lowest level of granularity in your dataset. This screen also contains options for changing the color ranges of your map as well as an option to display labels on each state that are sourced from your dataset. To finish our map complete the following steps:
    1. In the “Field to visualize” dropdown select “[Sum(Sum_SalesAmount)]”
    2. In the “Color rule” dropdown select “White-Blue”
    3. Click “Finish”

Once we click finish we should now be able to see our new map in the body of our report. Several of the items in the map can be individual selected to have their properties adjusted, including individual state shapes. If you click on the square that the state shapes are located in you should be able to adjust the zoom by using your mouse wheel, as well as adjust the position of the entire map by left clicking on an open space within the square and dragging. In the example below I’ve retitled the map by simply double clicking on the “Map Title” and “Title” textbox near the top and top left of the map and typing my desired title. I’ve also removed the 2 keys for near the bottom of the report to give me a better view of the map as a whole by left clicking on them and then pressing the delete key.


It’s pretty easy to see that our largest amount of sales are in California and Washington.

Format map key to show currency

Notice how the “Sales Amount in Dollars” isn’t showing up as currency. Formatting this area as currency is possible, but it’s definitely a little tricky to do if you don’t know where to go. I remember it taking me longer than I care to publically admit to figure out how to format this area the first time I played with maps. Not sure why though, when it’s as obvious as these simple steps:

  1. In Report Builder while in design mode click on the report item until the “Map Layers” window appears. (this usually takes 2 click if you had something else selected prior to clicking on the map)
  2. From the “Map Layers” window look for a creepy looking eyeball that’s just glaring at you like you have no right to even want your map key formatted as currency….let alone GET it formatted as currency. Click the little dropdown next to that creepy eye.
  3. From the resulting menu select “Polygon Color Rule”

  4. From the resulting “Map Color Rules Properties” window select “Legend” from the available options on the left
  5. Locate the “Legend text” textbox which should contain something like “#FROMVALUE{N0} - #TOVALUE{N0}”. To change our formatting from numeric to currency we’ll need to change the {N0} parts of this to {C0}. The number after C determines the number of decimal places in the event you want to change that value as well while you’re in here. Ultimately we’ll want end up with something like this: “#FROMVALUE{C0} - #TOVALUE{C0}” (without the quotes)

  6. Click “OK”

You should see your change immediately, even when in the design area. Now wasn’t that easy?


It should be noted that you can also customize the color distribution for different ranges or the number of ranges used and what their thresholds are from the same window. By clicking on the “Distribution” menu. You can also access many of the original settings we configured plus several additional ones by digging around in the options that appear under that creepy eye.

I think that about wraps it up for maps. I might do another post on maps later that shows how you can make individual map items clickable so you can navigate the user to a detail report for each state. But that’s a story for another time. Until then, happy reporting!

How to connect Reporting Services (SSRS) to a SharePoint List Using the “Microsoft SharePoint List” Connection Type

SharePoint Lists, they’re in every SharePoint site, and at some point someone is going to want a report about the data they contain. I’ve covered how to pull SharePoint list data into Excel for reporting purposes in previous posts, but that’s not your only option. In this walkthrough we’ll take a look at how you can leverage Reporting Services (SSRS) to report against your SharePoint lists. In this walkthrough we’ll be using SharePoint 2013 (not o365…SSRS is not available in o365 at the time of this writing) and Report Builder 3.0. This walkthrough does assume some basic knowledge of/about SSRS.

The first thing we’ll need to do is open up report builder in our environment. Once you’ve got that open, follow these steps to connect to your SharePoint list.

  1. 1. In the Report Data window, right click on the “Data Sources” folder and select “Add Dataset..”

  2. In the “Name” textbox type “SP” for the name.
  3. Choose “Use a dataset embedded in my report” from the radio button options
  4. For the connection type select “Microsoft SharePoint List”
  5. In the “Connection String” box, you’ll need to enter the URL to the site or sub-site that contains the list you want to report off of. For example: the list I want to report off of is in a sub-site and the full URL to my list is In this case I would only want to enter into the connection string box.

  6. Click on “Credentials” on the left hand side of the Data Source Properties window.
  7. Select “Use current Windows user. Kerberos delegation might be required.”

  8. Click OK.

You should now have a data source named “SP” showing under your Data Sources folder in the Report Data window. Next we’ll want to create a Dataset to tell reporting services specific data we want to pull. To create a dataset, follow these steps:

  1. Right click the “Datasets” folder and select “Add Dataset”
  2. Name your dataset something appropriate
  3. Select “Use a dataset embedded in my report” from the available radio button options.
  4. In the “Data Source” dropdown select “SP” (this is the data source we created in the previous set of steps
  5. Click the “Query Designer…” button near the bottom of the screen.
  6. With the Query Designer open, locate the list you wish to report off of and click the “+” next to it.
  7. Place checkmarks next to the fields you would like to have in your report. As you select items they should also appear in the “Selected fields” section of the Query Designer window. It is important to note that you can only select items from one SharePoint list. If you wish to report off of multiple lists, you’ll need to create additional data sets. On the bright side, if the list you’re reporting off of has a lookup to another list, those values should also appear in the list that has the lookup. In the image below, my Trouble Ticket list has a lookup to the Store list, so it brings over the associated values from the Store table and shows them as Store:ColumnName.

  8. Click “OK” on the Query Designer window once you have selected all the fields you need.
  9. Now that you’re back on the Dataset Properties window, make sure your screen looks something similar to the one below, and then click ok.


You should now see your new data set under the “Datasets” folder. From here you can simply expand your data set and drag your values onto your report or into a table or matrix just as you would any other data set values.

If you happen to be reporting off of a document library and have folders to contend with, you might want to take a look at my previous post about dealing with Folders.   That post can be found here:

Hopefully this helps unlock some of your data kept in SharePoint lists…until next time, happy reporting!

Using SSRS with SharePoint Library Folders


About once a year someone asks me about pulling data from a SharePoint library when folders have been created within the library to help organize the content. In Reporting Services, if you connect to a SharePoint list by creating a data source using the connection type of “Microsoft SharePoint List” you will only be able to see the items that exist outside of any folders that have been created in the library. The names of the folders that exist within the library may also be visible, but returning the items inside a given folder will require a different approach. It’s certainly not difficult to imagine scenarios were you might want to do either of the following:

  • Pull only items from a specific folder in the library
  • Pull all items in the library regardless of if the item is in a folder or not.

In this walkthrough we’ll be taking a look at how we can accomplish both of those goals by abandoning the “Microsoft SharePoint List” connection type, and leveraging the “XML” connection type within SSRS and the “GetListItems” web service within SharePoint.

Example Data

Our example data for this walkthrough will consist of a SharePoint document library named “FolderTest”. This document library will have two folders in it named “Folder A” and “Folder B”. Folder A will have 2 documents in it, and folder B will have 1 document. There will also be 1 document that exists outside of the folders. Examples of the document library can be seen below.




Pulling items from a specific folder

For this example we’re going to pretend we have a scenario where we only want to pull items that are in “Folder A” of our “FolderTest” SharePoint Library. As mentioned earlier we’re going to leverage the SSRS XML connection type for our data source along with the “GetListItems” web service within SharePoint. We can outline our plan of attack as follows:

  1. Create an XML Data Source
  2. Format our webservice call in a dataset
  3. Reap the sweet rewards of our dataz!

So let’s get started!

Create an XML Data Source

  1. Open report builder
  2. Right click the “Data Sources” folder and select “Add Data Source”
  3. Enter “XMLTest” as the data source name
  4. Select “Use a connection embedded in my report”
  5. Select “XML” as the “Connection Type”
  6. For the connection string you’ll want to enter the URL to your site plus “/_vti_bin/lists.asmx”. For example, my library exists on the site, so the URL for my connection string will be You can verify the connection string by entering it into your web browser. Doing so should return a list of web services for the site as shown in the image  below. (If the particular library you’re try to pull from happens to exist on a subsite, you’ll want to include the subsite in your URL…ex:

  7. Click on the “Credentials” menu item on the left hand side of the “Data Source Properties” window
  8. Select the option “Use the current Windows user. Kerberos delegation might be required.”
  9. Click the “OK” button on the “Data Source Properties” window.

Format our webservice call in a dataset

Now that we’ve got our data source created, we’ll need to create a dataset to pull the specific data we’re after. For our scenario, we’re going to say that we only want to return items that are in the “Folder A” folder of our “FolderTest” library. To create the data set, complete the following steps:

  1. Right click on the “Dataset” folder in Report Builder and select “Add Dataset”
  2. Name the Dataset “ListItemsInFolderA”
  3. Select “Use a dataset embedded in my report”
  4. In the “Data source” dropdown, select the “XMLTest” data source we created earlier
  5. Click the “Query Designer” button
  6. In the “Command type” dropdown, select “Text”
  7. Paste the following code into the “Query Designer” window. You’ll need to change the highlighted areas of the code to contain your own SharePoint Site URL, Library Name, and Folder Name

      <Method Namespace="" Name="GetListItems">
          <Parameter Name="listName">
          <Parameter Name="queryOptions" Type="xml">
    <ElementPath IgnoreNamespaces="True">*</ElementPath>
  8. Click the red “!” to execute the query. In our example scenario we should see 2 rows of data returned. One for each item in “Folder A” of our library.


  9. Click the “OK” button on the “Query Designer” window
  10. Click the “OK” button on the “Dataset Properties” window

Now we should be able to interact with data associated with items in “Folder A”, just like we would any other dataset in reporting services.

Pulling all items for the library

Pulling data from 1 specific folder in a library is a nice trick to have up your sleeve, but another common request I see quite a bit is to pull all items from a given library, regardless of if the items are in a folder or not, we just want everything. In our example data this would be 4 total items: 2 items from Folder A, 1 item from Folder B and 1 items that’s not in either folder. To do that we’re going to use the same approach of leveraging the XML data source along with the “GetListItems” web service within SharePoint. We’ll need to change up our query a little though to look something like the following:

  <Method Namespace="" Name="GetListItems">
      <Parameter Name="listName">
      <Parameter Name="queryOptions" Type="xml">
                      <ViewAttributes Scope="Recursive" />
<ElementPath IgnoreNamespaces="True">*</ElementPath>

Note that you still have to modify the query to point it to your specific list shown in green above. The main difference between our first query and this new query that gets all the items is that we’ve replaced the section that pointed to a specific folder with the line <ViewAttributes Scope="Recursive" /> that tells the query to get all of the items in the library. As you can see below, all 4 library items are now being returned by our query.


That should do it for this post. I’ve got a few other go to tricks I seem to use over and over when reporting off of SharePoint lists that I plan on sharing in upcoming posts. In the meantime, if you’re looking to milk a little more functionality out of how you query your lists using the XML data source and the “GetListItems” web service within SharePoint, here a nice reference link:

Happy reporting!