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

    clip_image001

  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”

    clip_image003

  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]”

    clip_image005

  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.

clip_image006

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!

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 http://msftdbprodsamples.codeplex.com/releases/view/55330. 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

    clip_image001

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.

clip_image002

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”

    clip_image004
  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".

    clip_image006
  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.

    clip_image008
  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"

    clip_image010
  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”

      clip_image012
  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.

clip_image013

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”

    clip_image015
  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)

    clip_image016
  6. Click “OK”

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

clip_image017

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!

Using SSRS with SharePoint Library Folders

Overview

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.

clip_image002

clip_image004

clip_image006

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 http://portal.contoso.com, so the URL for my connection string will be http://portal.contoso.com/_vti_bin/lists.asmx. 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: http://portal.contoso.com/subsite/_vti_bin/lists.asmx)

    clip_image008
  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

    <Query>
    <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
      <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
        <Parameters>
          <Parameter Name="listName">
            <DefaultValue>FolderTest</DefaultValue>
          </Parameter>
          <Parameter Name="queryOptions" Type="xml">
            <DefaultValue>
              <QueryOptions>
                <Folder>http://portal.contoso.com/FolderTest/FolderA</Folder>
              </QueryOptions>
            </DefaultValue>
          </Parameter>
        </Parameters>
      </Method>
    <ElementPath IgnoreNamespaces="True">*</ElementPath>
    </Query>
  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.

    clip_image010

  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:

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
  <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
    <Parameters>
      <Parameter Name="listName">
        <DefaultValue>FolderTest</DefaultValue>
      </Parameter>
      <Parameter Name="queryOptions" Type="xml">
        <DefaultValue>
          <QueryOptions>
                      <ViewAttributes Scope="Recursive" />
         </QueryOptions>
        </DefaultValue>
      </Parameter>
    </Parameters>
  </Method>
<ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>

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.

clip_image012

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: https://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx

Happy reporting!

Reporting Services (SSRS) Tutorial Video….AND ONLINE CLASS!

If you’re looking to ramp up on Reporting Services, I recently made a tutorial video that you might find interesting.  While the video Focuses on building reports with SQL Server Data Tools (Visual Studio), I’m also teaching a monthly 4 hour online “Reporting Services 101” class! The class focuses more on Report Builder and SharePoint:  In that class we take it step by step….starting at step one to give you a good foundation, and by the end we’re cranking out some pretty robust reports.  

You can check out the details for the class here: http://sharepoint.rackspace.com/ssrs-training

You can check out the short tutorial video of building a report using SQL Server Data Tools (Visual Studio) below. 

Reporting Services using SSDT



How to make SSRS reports in a PerformancePoint dashboard “pop out” or open in a new window.

I’ve had this request from multiple clients.  The scenario is usually that they have a PerformancePoint dashboard that contains a mix of PerformancePoint Analytic Chart reports and Reporting Services reports.  The Reporting Services reports are usually just gauges or some kind of chart, but they want the ability to click on them and to show the underlying detail data.  The problem is usually that the detail data they want to see is far larger than the space allotted in the dashboard for the original SSRS gauge or chart…….so we need the detail data to open in a new window.

Unfortunately there isn’t really an option for “Open report in new window” in the settings (but that sure would be nice if anyone from Microsoft ever reads this).  So to make this happen, we’re going to need to write a little Javascript.

Assumptions for this walkthrough:

·         SSRS is in SharePoint integrated mode (although you can do this in Native mode as well)

·         SSRS is either 2008 or above.

The short version of this post is that you basically need to add the javascript shown below to the “Action” property of whatever it is your user is going to click on in the report. 

="javascript:void(window.open('http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl', '', 'width=1000, height=800, top=0, left=0, resizable=yes'))"

Where:

Yellow = The URL to your report.

Green = The dimensions of the new window.

So the first thing we need to know is the URL to the report that we want to come up in our new window.  To get the URL we’re just going to browse to and open the report in SharePoint, and then copy it from the browser address bar….however you’ve probably got some stuff at the end that you don’t need.  For instance my report is called BlogExampleReport and the URL is:

http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl&Source=http%3A%2F%2Fmastvmbase%2Fsites%2FTavisBIDemo%2FSSRS%2FForms%2Fcurrent%2Easpx&DefaultItemOpen=1

We want to remove everything after reportname.rdl so it looks like this:

http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl

Now that we have our report URL we just add it to the Javascript as shown highlighted in yellow above.  Now we need to add our javascript to our report that the user will click on in the dashboard.  In my case, this report is a gauge.  So we’ll need to

1.      Right click on the Gauge in design mode, and select “Properties”

2.      Select “Action” from the Properties window.

3.      Select “Go to URL” as the action.

4.      Click the “Fx” button next to the “Select URL” field, and copy in our Javascript.

Click on the picture below to see each of the open screens.

clip_image002[4]

That should do it.  Just click OK on all the open windows and then deploy the report to test it.  NOTE: The report must be deployed in order for the Javascript to work.  It will not work in the preview window of your report designer. 

Now let’s say we want to pass a parameter from our original report to our report that opens in the new window.  We can also do this through the URL by adjusting our Javascript like so:

="javascript:void(window.open('http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl&rp:p_ReportParameter1=ParameterValue', '', 'width=1000, height=800, top=0, left=0, resizable=yes'))"

Where:

Yellow = The URL to your report.

Purple = The parameter and value you’re passing in.

Green = The dimensions of the new window.

In the above example we’re passing a value of “ParameterValue” into our parameter named “p_ReportParameter1”.

It’s worth noting that you can also control several aspects of your report viewer toolbar by passing parameters in via the url.  There is an excellent post about doing so here:

http://blogs.msdn.com/b/prash/archive/2009/01/21/passing-url-report-parameters-to-reports-in-sharepoint-document-library-in-ssrs-2008.aspx

So I think that about wraps it up.  While it’s a little messy, and kind of a pain to manage through environments….the functionality is possible.  If you have several reports that need to be moved between dev/uat/prod, I highly suggest leveraging shared data sets to store your base report URL ei: “http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/” .  Doing so will allow you to change it in one place, instead of having to do so in each individual report when you deploy to a new environment.  Until next time, hope this helps.

How do I add pictures to a Power View report using Power Pivot as a data source?

One of the great new features of SQL Server 2012 is Power View.  In this walkthrough I’ll be creating a very simple Power View report using Power Pivot as the datasource.  The goal of this report will be to demonstrate how you can pull in images into your report to give the user a nice visual…so let’s get started.

1.       Get your pictures ready:  I simply took some quick pictures of items laying around on my desk, then I uploaded them to a SharePoint library.  These pictures are a little less than visually stunning, but you get the idea.  Just make sure to remember the URL, as we’ll be pulling our pictures in from this library.

clip_image002

2.      Now we need to build ourselves a Power Pivot workbook.  I’m going to keep this real simple with only two tables, and we’re really only going to talk about one of them….the “Items” table shown below.  There are a few things to note about the table below.

o   Each entry in the Item column is named after one of our pictures…that makes it easy to construct what the URL to our picture is

o   The “ImageURL” column is a calculated column which is simply the URL to the document library where my pictures are located. (you can see the formula in the fx bar in the image) .  The key point here, is that you need to have the full URL to your picture somewhere in your Power Pivot book.

clip_image004

 

3.      Now that we’ve got some data with URLs to some images, we need to tell Power Pivot that our “ImageURL” columns is actually an image.  To do this we’ll want to click on our main menu in the Power Pivot window and select “Switch to Advanced Mode” as shown in the picture below.

clip_image005

This should make a new “Advanced” tab available.  From here we want to highlight our “ImageURL” column, and then check the “ImageURL” checkbox on the advanced tab as shown below highlighted in yellow.

clip_image006

4.      As a finishing touch let’s let Power Pivot know that our image column is actually a picture of the entry in our “Item” Column.  This will allow us to show both the item text and the picture in the same tile when we go to make our report in Power View.  To do this we need to click on “Table Behavior” under the “Advanced” tab (shown in the image above highlighted in yellow).  Doing so will open the “Table Behavior” window shown below.

clip_image008 

On this screen we need want to do the following:

·         For “Row identifier” select the column we want to work with, in our case this is the “Item” column.

·         For “Keep Unique Rows” select columns you don’t want to aggregate on (you don’t need to do this for our example, but it’s a good practice to get into.)  In our case, the “Item” columns is unique in our data.

·         For “Default Label” select what we want the label to be when we create our tiles.  In our case this is the actual text in the “item” column.

·         For “Default Image” select the column that the URL to our picture resides in.  This will be our  “ImageURL “ column.

Once that’s finish click OK, and the save and close the workbook. 

5.      Now that we’ve created our Power Pivot data set we just need to upload it to our Power Pivot Gallery in SharePoint, and then create a new Power View report by clicking on the Power View Link as shown highlighted in yellow below.

 

clip_image010

 

 

If I select “Item”, “description”, and “Sales Total” (sales total was the other table we didn’t talk about, but it was just a bunch of numbers I made up and related to our first table) and then click the “Tiles” button from within Power View it’ll look something like this.

clip_image011

So there we have it, a very quick/simple report example using images in Power View.  In addition to showing images in our tiles, we can also show our images in tables or on cards.  I think that about wraps this walkthrough up, hope you found it helpful.

 

 

How to fix Reporting Services broken images when hosting reports in a PerformancePoint dashboard.

I’ve been looking for the solution to this problem on and off for months, and finally stumbled upon the answer.

The scenario was this:

I placed an SSRS report into a PPS dashboard. The SSRS report has nothing but a gauge, and a table with some related data. For some reason the gauge image only shows up when I have the “show toolbar” option checked, otherwise the gauge shows up as a broken image or red x. In either case, the data for the table always shows up. If I navigate to the report server directly and view the report I have no problems, it only seems to happen from within PPS (both in designer and once deployed).

Apparently when Reporting services is configured to authenticate via “Trusted Account” mode, you need to specify the URL of the Report Server proxy rather than just the URL to the Report Server.

So for example, when configuring the “Report Server URL” in your Performance Point SSRS report you would typically enter it as

http://server/reportserver

however if you’re experiencing the problem described above, try referencing it including _vti_bin in the URL like so

http://server/_vti_bin/reportserver

That seemed to do the trick for me. Hope this saves someone some frustration.

 

How do I get ReportItems referenced in my Report header to show on all pages?

In Reporting Services (SSRS) 2005 there are a few tricks to being able to display data items other than report parameters in the header of your report. For some reason if we return values in a dataset and want to use them in the header of the report, the values we want to use in that dataset must appear somewhere in the body of the report. Well, that’s easy enough. We can simply

  • Put a textbox in the body of the report.
  • Set its visibility to hidden
  • Drop in our dataset result
  • Reference the textbox in the header of our report like so: “=ReportItems!TextboxName.Value”.

This works great if your report is < 1 page. However if your report becomes more than 1 page, someone will eventually notice that your report header only displays those values on the first page of the report. If you need those values to appear on all pages of your report you’ll need to make a few additional tweeks to get there….namely:

  1. Add a function to our report to keep track of what our value is.
  2. Add that function call to the header of our report.

**UPDATE: The solution provided in this example only works while viewing reports online. I later noticed that while printing, or in “Print Preview” mode, items in my header were once again appearing as blank. Ultimately to work around this problem, I converted any item I needed to reference in my report header into a parameter, and set it to “internal” or “Hidden”.

To add code to our report, we’ll need to navigate to the report properties by clicking on “ReportàReport Properties…” (Remember the menu options in the report designer are context sensitive, so make sure you’re on the “Layout” tab of your report). This should open a new window, you’ll want to click on the “Code” tab and add the following function:

Dim lastDivision As String = Nothing

Function GetDivision(ByVal currentDivision As String) As String
If Not currentDivision = Nothing Then
lastDivision = currentDivision
End If
Return lastDivision
End Function

clip_image001[4]

If all has gone well, you should be looking at something similar to the image above. Click “OK” back to the designer.

Next we need to call our new function in our header to get the results. To do so all we need to type is “=Code.FunctionName(ReportItems!YourTextbox.Value)”. So in my example I’m getting a Division name that I’m looking up based on one of the report parameters, thus I named the function in this example “GetDivision”. The textbox that’s storing the value in the body of my report is named “DivisionName”……put that all together and my call in the header looks like this:

=Code.GetDivision(ReportItems!DivisionName.Value)

Now my division name will show up on all headers of all pages of my report….schweet.

You’ll probably want to change the VB we entered in the code window to more accurately reflect whatever value you’re retrieving (or more accurately, keeping track of across pages if you examine the code). Also note that if you want to do this for multiple values, you’ll need to do a bit more customization to the code, and add new variables.

Hope this helps.

 

I’ll be speaking at "SharePoint Cincy – 2011"

I'll be speaking at the "SharePoint Cincy - 2011" conference on March 18th. If you're looking to dive into the world of SharePoint Dashboarding but aren't sure where to start, here's your chance!

The event website is here: http://www.sharepointcincy.com

Even though they failed to post my bio under the speakers page (....sigh) you'll still find me mentioned on the Sessions page under the BI track here: http://www.sharepointcincy.com/session-information/13

Or you can just read my session abstract below.

Subject: Implementing Dashboard Solutions Using PerformancePoint & SQL Reporting Services in SharePoint 2010
Presenter: Tavis Lovell – Ascendum, Senior Consultant

Learn how to build Reports, KPIs, Scorecards and Dashboards for deployment to SharePoint 2010 using PerformancePoint Services (PPS) and SQL Server Reporting Services (SSRS). Attendees will gain familiarity with PerformancePoint Dashboard Designer and SQL Servers Business Intelligence Development Studio (BIDS) and how they can be leveraged within an organization to meet strategic, tactical, and operational goals.

Hope to see you there.