Search
Twitter
Monday
Jun172013

Creating a Bullet Graph in Reporting Services

Overview:  This discusses creating a Bullet Graph in SQL Server Reporting Services (SSRS).  The following screen shots are from SQL Server 2012.

The Bullet Graph

Bullet graphs were developed by Stephen Few as a “no-frills” design alternative to traditional gauges which can be overly “showy,” take up a lot of space, and be difficult to interpret.  I like bullet graphs because they show a lot of information for a measurement or a KPI in a very small space.  The following example shows actual, target and thresholds in a very compact style:

     image

Let’s break it down:

  • The black bar is typically the Actual measurement.  This is considered the featured measure.
  • The tick mark is typically the Target to which we are comparing the Actual.  This is considered the comparative measure.
  • The background colors are typically thresholds that indicate if the Actual is acceptable.  The thing about the thresholds is that they give a lot of extra contextual information.  Two to five qualitative ranges is most common.
  • The scale shown below the axis defines the actual value, target value, and threshold ranges.
  • The label on the left indicates which measurement or KPI is being displayed.

     image

Bullet graphs can be horizontal or vertical.  You can include colors for the threshold backgrounds if you prefer – however, the design specification for the Bullet Graph specifies using different intensities of black.

Also, note on the 1st screen shot above that the background color for the thresholds is inverted for the Revenue and Expenses – i.e., more Revenue is better & less Expenses is better so the light background color is always better than the dark background color.  However you decide to handle it, try to be consistent from report to report.  A bullet graph requires just a bit of initial end-user training, so keeping things consistent is always a very good idea to minimize training required.

Creating a Bullet Chart in SSRS

Here’s the steps for reproducing the Revenue bullet graph:

     image

1.  Create Source Data.  This step is for us to create a bit of source data to work with in this example.  Keep in mind there’s many different ways you could structure this – what’s shown here is a relatively realistic (yet simplistic) representation of how the data might be stored in a data warehouse using a star schema model.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Dim_KPI_Measurements](
    [Measurement_id] [int] IDENTITY(1,1) NOT NULL,
    [MeasurementGroup] [varchar](30) NOT NULL,
    [MeasurementType] [varchar](30) NOT NULL,
    [MeasurementName] [varchar](30) NOT NULL,
    [MeasurementSort] [tinyint] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Fact_KPI_Measurements](
    [Measurement_id] [int] NOT NULL,
    [MeasurementAmount] [decimal](18, 2) NULL,
    [RangeFromValue] [decimal](18, 2) NULL,
    [RangeToValue] [decimal](18, 2) NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Dim_KPI_Measurements] ON

GO
INSERT [dbo].[Dim_KPI_Measurements] ([Measurement_id], [MeasurementGroup], [MeasurementType], [MeasurementName], [MeasurementSort]) VALUES (1, N'Revenue', N'Actual', N'Revenue Actual', 0)
GO
INSERT [dbo].[Dim_KPI_Measurements] ([Measurement_id], [MeasurementGroup], [MeasurementType], [MeasurementName], [MeasurementSort]) VALUES (2, N'Revenue', N'Target', N'Revenue Target', 0)
GO
INSERT [dbo].[Dim_KPI_Measurements] ([Measurement_id], [MeasurementGroup], [MeasurementType], [MeasurementName], [MeasurementSort]) VALUES (3, N'Revenue', N'Range', N'Revenue Poor Range', 1)
GO
INSERT [dbo].[Dim_KPI_Measurements] ([Measurement_id], [MeasurementGroup], [MeasurementType], [MeasurementName], [MeasurementSort]) VALUES (4, N'Revenue', N'Range', N'Revenue Middle Range', 2)
GO
INSERT [dbo].[Dim_KPI_Measurements] ([Measurement_id], [MeasurementGroup], [MeasurementType], [MeasurementName], [MeasurementSort]) VALUES (5, N'Revenue', N'Range', N'Revenue Satisfactory Range', 3)
GO
INSERT [dbo].[Dim_KPI_Measurements] ([Measurement_id], [MeasurementGroup], [MeasurementType], [MeasurementName], [MeasurementSort]) VALUES (6, N'Expense', N'Actual', N'Expense Actual', 0)
GO
INSERT [dbo].[Dim_KPI_Measurements] ([Measurement_id], [MeasurementGroup], [MeasurementType], [MeasurementName], [MeasurementSort]) VALUES (7, N'Expense', N'Target', N'Expense Target', 0)
GO
INSERT [dbo].[Dim_KPI_Measurements] ([Measurement_id], [MeasurementGroup], [MeasurementType], [MeasurementName], [MeasurementSort]) VALUES (8, N'Expense', N'Range', N'Expense Poor Range', 1)
GO
INSERT [dbo].[Dim_KPI_Measurements] ([Measurement_id], [MeasurementGroup], [MeasurementType], [MeasurementName], [MeasurementSort]) VALUES (9, N'Expense', N'Range', N'Expense Middle Range', 2)
GO
INSERT [dbo].[Dim_KPI_Measurements] ([Measurement_id], [MeasurementGroup], [MeasurementType], [MeasurementName], [MeasurementSort]) VALUES (10, N'Expense', N'Range', N'Expense Satisfactory Range', 3)
GO
SET IDENTITY_INSERT [dbo].[Dim_KPI_Measurements] OFF
GO
INSERT [dbo].[Fact_KPI_Measurements] ([Measurement_id], [MeasurementAmount], [RangeFromValue], [RangeToValue]) VALUES (1, CAST(265000.00 AS Decimal(18, 2)), NULL, NULL)
GO
INSERT [dbo].[Fact_KPI_Measurements] ([Measurement_id], [MeasurementAmount], [RangeFromValue], [RangeToValue]) VALUES (2, CAST(250000.00 AS Decimal(18, 2)), NULL, NULL)
GO
INSERT [dbo].[Fact_KPI_Measurements] ([Measurement_id], [MeasurementAmount], [RangeFromValue], [RangeToValue]) VALUES (3, NULL, CAST(0.00 AS Decimal(18, 2)), CAST(150000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Fact_KPI_Measurements] ([Measurement_id], [MeasurementAmount], [RangeFromValue], [RangeToValue]) VALUES (4, NULL, CAST(150000.01 AS Decimal(18, 2)), CAST(225000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Fact_KPI_Measurements] ([Measurement_id], [MeasurementAmount], [RangeFromValue], [RangeToValue]) VALUES (5, NULL, CAST(225000.01 AS Decimal(18, 2)), CAST(300000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Fact_KPI_Measurements] ([Measurement_id], [MeasurementAmount], [RangeFromValue], [RangeToValue]) VALUES (6, CAST(40000.00 AS Decimal(18, 2)), NULL, NULL)
GO
INSERT [dbo].[Fact_KPI_Measurements] ([Measurement_id], [MeasurementAmount], [RangeFromValue], [RangeToValue]) VALUES (7, CAST(30000.00 AS Decimal(18, 2)), NULL, NULL)
GO
INSERT [dbo].[Fact_KPI_Measurements] ([Measurement_id], [MeasurementAmount], [RangeFromValue], [RangeToValue]) VALUES (8, NULL, CAST(0.00 AS Decimal(18, 2)), CAST(35000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Fact_KPI_Measurements] ([Measurement_id], [MeasurementAmount], [RangeFromValue], [RangeToValue]) VALUES (9, NULL, CAST(35000.01 AS Decimal(18, 2)), CAST(80000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Fact_KPI_Measurements] ([Measurement_id], [MeasurementAmount], [RangeFromValue], [RangeToValue]) VALUES (10, NULL, CAST(80000.01 AS Decimal(18, 2)), CAST(150000.00 AS Decimal(18, 2)))
GO

2.  Data Source.  Add a shared source to the project which connects to the database where you created the tables in Step 1.  Also create a data source within your new report. 

3.  Dataset.  Add the following dataset to the report.  Keep in mind there’s lots of ways to do this – my approach here was to use a CTE to pivot the data from rows to columns, then aggregate it into one summarized row for the bullet graph to present.  The variable at the top is to make the query easily reusable for other KPIs.

DECLARE @MeasurementGroup varchar(30)
SET @MeasurementGroup = 'Revenue'

;WITH CTE_Details
AS
(
SELECT
   Dim.MeasurementGroup
  ,Dim.MeasurementType
  ,Dim.MeasurementName
  ,Dim.MeasurementSort
  ,Actual = CASE WHEN Dim.MeasurementType = 'Actual'
            THEN Fact.MeasurementAmount
            ELSE NULL
            END
  ,Target = CASE WHEN Dim.MeasurementType = 'Target'
            THEN Fact.MeasurementAmount
            ELSE NULL
            END
  ,Range1From = CASE WHEN Dim.MeasurementType = 'Range'
                     AND Dim.MeasurementSort = 1
            THEN Fact.RangeFromValue
            ELSE NULL
            END
  ,Range1To = CASE WHEN Dim.MeasurementType = 'Range'
                     AND Dim.MeasurementSort = 1
            THEN Fact.RangeToValue
            ELSE NULL
            END
  ,Range2From = CASE WHEN Dim.MeasurementType = 'Range'
                     AND Dim.MeasurementSort = 2
            THEN Fact.RangeFromValue
            ELSE NULL
            END
  ,Range2To = CASE WHEN Dim.MeasurementType = 'Range'
                     AND Dim.MeasurementSort = 2
            THEN Fact.RangeToValue
            ELSE NULL
            END
  ,Range3From = CASE WHEN Dim.MeasurementType = 'Range'
                     AND Dim.MeasurementSort = 3
            THEN Fact.RangeFromValue
            ELSE NULL
            END
  ,Range3To = CASE WHEN Dim.MeasurementType = 'Range'
                     AND Dim.MeasurementSort = 3
            THEN Fact.RangeToValue
            ELSE NULL
            END

FROM dbo.Fact_KPI_Measurements Fact
INNER JOIN dbo.Dim_KPI_Measurements Dim
  ON Fact.Measurement_id = Dim.Measurement_id

WHERE Dim.MeasurementGroup = @MeasurementGroup

)
SELECT
Actual = SUM(Actual)
,Target = SUM(Target)
,Range1From = SUM(Range1From)
,Range1To = SUM(Range1To)
,Range2From = SUM(Range2From)
,Range2To = SUM(Range2To)
,Range3From = SUM(Range3From)
,Range3To = SUM(Range3To)

FROM CTE_Details

4.  Gauge.  Drag a Gauge from the Toolbox to the Body of the report.  Select the Bullet Graph, which is the last item in the Linear section.

     image

The starting point within SSRS before any properties are defined includes two pointers (LinearPointer1 and 2) and three thresholds (LinearRange1, 2, and 3).  That’s good because that’s exactly the number of objects we need to create our Revenue bullet chart.

     image

For the next several steps we are going to be using various properties.  If you right-click the object and navigate the Gauge Panel menu, you will find various properties available for each Pointer, Range, and the Gauge Panel itself. 

     image

5.  Gauge Panel Properties.  From the Gauge Panel Properties, select the dataset created in step 3 to associate the bullet graph to the dataset query.

     image

6.  Actual (LinearPointer1) Properties. 

Pointer Options page:  Select the Value to be the Actual field.  It will automatically aggregate it as a sum, which is fine.

     image

Pointer Fill page:  Set the Fill Style to Solid, and the Color as Black.

     image

7.  Target (LinearPointer2) Properties. 

Pointer Options page:  Select the Value to be the Target field.

     image

Pointer Fill page:  Set the Fill Style as Solid, and the Color as Dim Gray.  The specification calls for 75% black here, and Dim Gray is close to that.

     image

8.  Poor Range (LinearRange1) Properties. 

General page:  Set the Start Range to the the “Range1From” field, and the End Range to be the “Range1To” field.

     image

Fill page:  Set the Fill Style to be Solid, and the Color to be Gray.  The specification for 3 states calls for 40% black here, so Gray is close.

     image

Note that by using fields instead of hard-coded ranges you no longer see the range while in Design mode.  You can still access its properties through.

9.  Middle Range (LinearRange2) Properties. 

General page:  Set the Start Range to the the “Range2From” field, and the End Range to be the “Range2To” field.

     image

Fill page:  Set the Fill Style to be Solid, and the Color to be Silver.  The specification for 3 states calls for 25% black here, so Silver is close.

     image

10.  Satisfactory Range (LinearRange3) Properties. 

General page:  Set the Start Range to the the “Range3From” field, and the End Range to be the “Range3To” field.

     image

Fill page:  Set the Fill Style to be Solid, and the Color to be Light Gray.  The specification for 3 states calls for 10% black here, and Light Gray is close to that.

     image

11.  Scale (LinearScale1) Properties.  Right-click the Scale and choose Scale Properties. 

General Page:  Set the Minimum to be the “Range1From” field, and the Maximum to be the “Range3To” field.

     image

Number page:  Set the Category to be Number.  Decimal places should be 0.  Use 1000 separator.  Show values in Thousands.

     image

12.  Gauge Properties.  Right-click the object and choose Gauge Properties.

Back Fill page:  Set the Fill Style to be Solid, and No Color.

     image

Frame page:  Set the Style to be None.

     image

13.  Label.  From the Toolbox, drag a Text Box onto the Body.  Call it Revenue, with a bold 10pt font.  Drag a second Text Box onto the Body.  Place it just below the Revenue one.  Call it “U.S. $ (1,000s)”, with a 9pt font.  Right-justify both text boxes.

     image

The finished SSRS gauge object should look like this:

     image

At this point you can continue tweaking the properties (there sure are a lot of them to play with!) & build out the remainder of the report or dashboard page.

Finding More Information

Stephen Few – Bullet Graph Design Specification

 

Wednesday
Jun052013

Integrating Data from Data Explorer into PowerPivot

Overview:  A few thoughts about using Data Explorer with and without the Excel Data Model (i.e., PowerPivot).

My first reaction when I heard about Data Explorer being released in preview mode as an add-in to Excel was “Why isn’t it part of PowerPivot?” Now that I’ve thought about it further, it’s occurred to me that its independence means you can use Data Explorer with or without PowerPivot.

Before we illustrate that further, let’s pull a bit of data into Data Explorer. One nice piece of functionality is that when you hover on the items in the Online Search pane on the right, the left side populates with a preview of the data immediately – this is nice to give you an idea if the data will be useful before you bother downloading it. Using the Online Search button on the Data Explorer ribbon, I’ve done a search for “Charlotte North Carolina” (just for grins) which is shown as follows:

     clip_image002

Note that with this initial preview of Data Explorer, the only website being searched is Wikipedia. Options here will be enhanced over time.  There’s numerous other ways to get external data as well (some of which are just really super cool, but that’s for another time…).

Using Data Explorer to Import Data to an Excel Table

The settings we are concerned with are in the Query Settings pane, shown as follows:

     image

The default option, as shown above, is “Load to worksheet.” This is apparent because its slider is set to On.  Also, by observing the Table Tools > Design ribbon we can see that the data downloaded went to an Excel table named Table2.

Note that this isn’t a Pivot Table – it’s an Excel Table applied to a range of cells.

Using Data Explorer to Import Data to a PowerPivot Model

Conversely, if you wish to import your data into the Excel Data Model (i.e., PowerPivot), then you want to select the “Load to data model” option on the Query Settings pane. After the data has been uploaded to the PowerPivot model, it will change your Query Settings pane as follows:

     clip_image006

If you don’t see the Query Settings Pane, just click anywhere in the table & the pane should pop up. If it doesn’t, check the Table Tools > Query ribbon & select the “Show Query Settings” button.

Tip: if you need to modify the data and/or clean it up, you might want to handle this before you load it into the data model.

Just What is the Data Model Anyway?

If you are familiar with PowerPivot, then you are already familiar with the data model.  I’d imagine the Excel team is going for ease of use & understandability with the data model terminology.

You can import data into the data model by using the PowerPivot add-in.  Alternatively, a range of data can be added to the data model.  The following screen shot shows a small range of cells I have highlighted & then selected “Format as Table” from the Home ribbon:

     image

Next you are presented with a dialog box which confirms whether or not the first row is a header:

     image

Now we have some options on the Table Tools > Design ribbon.  If you choose “Summarize with PivotTable” you can interact with this range of data within the PivotTable field list. 

     image

Creating a PivotTable from a range of data is very cool, but not the reason I’m mentioning all this…note at the bottom of the dialog box there’s an option for you to “Add this data to the Data Model.”  If you choose to add the range of data to the data model, you can then launch your PowerPivot window and see it there represented as a table.

     image

This opens up a lot of possibilities considering you can bring data from a range of cells like this, and/or you can get external data from PowerPivot, and/or you can get external data from Data Explorer –> and then choose whether or not to integrate it with PowerPivot.  That really is a lot of flexibility.

Deciding Where to Store the Data

The way I see it, you could keep the data in an Excel table if a tabular result set (i.e., the output from Data Explorer) is really all you need.  You can even use a PivotTable with that Table range of data, which is very cool.

Here’s some reasons you might want to load the data obtained via Data Explorer into the data model (i.e., PowerPivot):

  • You have an existing PowerPivot data model, or other data sources, you wish to integrate this data with (although there is Merge functionality in Data Explorer that could work too).
  • You want to utilize other PowerPivot functionality such as Key Performance Indicators or Hierarchies.
  • You want to use the map feature of Power View (requires the geographic data to have a certain data category).
  • You want to change the default aggregation type from Sum to something else like Avg or Count.
  • You are more comfortable creating DAX calculations in PowerPivot than using the Data Explorer Formula Language (called “M”).
  • You wish to compress the data significantly.
  • You have a large set of data which will perform better using the xVelocity engine.

There’s times that Excel will implicitly create a data model for you, such as:

  • If you’re importing 2 or more tables at once
  • If you’ve inserted a Power View worksheet using the Table range of cells
  • If you've inserted a Power Map (its functionality requires a data model)

The implicit creation of the data model had me a bit stumped at first, but Excel is just being helpful.  Folks without the PowerPivot add-in enabled won't even know when the implicit conversion to the data model happens.

Happy hunting for data!

Finding More Information

Office – Create a Data Model in Excel

Office – Microsoft “Data Explorer” Preview for Excel Help

 

Monday
Jun032013

Displaying a Power View Report on a SharePoint Page

Overview: How to structure the URL for displaying a Power View report on a SharePoint 2013 page, including what parameters to pass in the URL.  This entry presumes the reader is familiar with SharePoint basics, including how to create a site page.

Ways to Render Power View Reports Within SharePoint

Because of its support for previewing the report via thumbnail images, the PowerPivot Gallery is a very common way of storing & accessing Power View reports:

     image

However, perhaps you really would like to display a Power View report on a SharePoint page, possibly as part of a dashboard that combines various types of reports. 

Ways of rendering Power View reports in SharePoint 2013 include:

  • Via the PowerPivot Gallery (as shown above)
  • Via a regular document library (note: it won’t have the thumbnail previews like the PowerPivot Gallery does due to its integration with Silverlight)
  • Within a Page Viewer Web Part
  • Within a Silverlight Web Part (note: since word on the street is that Silverlight has a finite lifespan, this might not be your first choice)
  • Embedded within a PerformancePoint Dashboard (via the web page report type)
  • Within an Excel Web Access Web Part (applicable to Power View reports created with Excel 2013)

Note that even though Power View is part of the Reporting Services family, you cannot use the SQL Server Reporting Services Report Viewer web part – it can only display RDL files (generated from Reporting Services or Report Builder).  It cannot currently display RDLX files (generated from Power View for SharePoint).

The remainder if this blog entry focuses on the Page Viewer Web Part.

Steps to Use the Page Viewer Web Part

First, you’ll want to create a Site Page in SharePoint if you don’t have one already.  Once you are in Edit mode for your page, go to the Insert ribbon.  From there, choose Web Part on the ribbon.  Browse through the Media and Content category to find the Page Viewer.  Make sure you’ve clicked within the section you want the Power View report to be in, then click Add.

     image

Then you’ll want to click the “open the tool pane” hyperlink to access its properties.

     image

There are four sections of properties – only the first two sections are really important for displaying our Power View report.

Page Viewer Properties

  • Web Page radio button – Leave this selected.
  • Link – Specify the URL which will look something like this (below is more detail about constructing the URL):

    /_layouts/15/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=%2fPowerPivot%20Gallery%2fMC%20Garden%20Center%20Analysis.rdlx&ViewMode=Presentation&ReportSection=ReportSection2&Fit=True&PreviewBar=False

     image

Hint:  the only piece that is absolutely positively required is the URL as shown in the above Page Viewer section…but if you skip the rest of the settings, you’ll end up with awfully ugly output that isn’t readable at all:

     image

Since we want it to be pretty, we’ll carry on with the rest of the properties…

Appearance Properties

  • Title – Change this from Page Viewer if you intend for the title to be shown (often the report itself will have a title, so often you can omit titling the web part itself too).
  • Height – Here’s where you are going to need to experiment somewhat.  The “Adjust height to fit zone” default doesn’t really do the trick from what I have seen.
  • Width – Chances are you can leave this as-is so it adjusts to fit the zone.
  • Chrome State – You’ll want to leave this as normal to ensure the report itself displays.
  • Chrome Type – Set this based on how you want to see the page.  If you are skipping the Title (i.e., if the report itself has a title), you might set the Chrome Type to have a border only, or perhaps none. 

     image

Layout Properties

Nothing required to be changed here.

     image

Advanced Properties

Nothing required to be changed here unless you’d like.

     image

Constructing the URL for Display of the Power View Report

Now let’s take a step back and look at how the URL is constructed.  It uses the AdHocReportDesigner.aspx page, as well as several parameters, including:

  • RelativeReportURL – Points to the RDLX file
  • ViewMode – Presentation or Edit
  • ReportSection – Reference to which view will be displayed initially
  • Fit – True/False setting which allows for resizing
  • PreviewBar – True/False setting which controls if the menu across the top is shown or not

Here’s how the URL can be broken down into its components:

     image

How the Power View Report Looks In the Page Viewer Web Part

How it ends up looking in SharePoint depends a lot on the parameter values you pass in via the URL, as well as the Height property you set in the properties.  Note that the pagination controls at the bottom left & bottom right are still active which is nice.  This means that even if you set the URL to jump to view 6, for instance, the end user can still paginate through and see all the views (i.e., pages) contained within the single Power View report.

Here’s an example where the Report Section is set to launch page 2 immediately, the View Mode is set to Presentation, and the Preview Bar is set to False within the URL string:

     image

Here’s another example of the same report, except the Preview Bar is set to True within the URL string:

     image

And here’s one more example of the same report, this time with the View Mode set to Edit:

     image

Prerequisites

SharePoint needs to be on-premises (as opposed to SharePoint Online) and configured in “SharePoint View Mode” rather than “Office Web Apps Server Mode.”  I’m guessing this is a temporary limitation around OWA support.

The Power View report needs to be created in SharePoint, as opposed to within an Excel 2013 file.  This requires the Reporting Services Add-In for SharePoint.

The browser must support the <iframe> element, which basically nests browsing context separately from the entire page.  All major modern browsers support iframes.

Finding More Information

Dan English Blog – Presenting Power View Reports to Users

Dan English Blog – URL Actions within Reporting Services Power View (RTM)

Himanshu Gupta Blog – Integrate Power View with SharePoint Using Web Parts

Office - Power View in Excel  in SharePoint Server or SharePoint Online in Office 365

 

Saturday
May252013

Behavior of Power View in Excel 2013 within SharePoint 2013

Overview:  A quick post about what to expect when integrating Power View worksheets embedded in Excel 2013 into a SharePoint 2013 site.  Note that this behavior is a temporary situation…I’m guessing the SharePoint team at Microsoft is planning to getting full integration with Power View working.  Should just be a matter of time. 

Versions Applicable:  This behavior I’ve observed is applicable to Excel 2013 (i.e., the only version of Excel that supports embedding Power View worksheets) with SharePoint 2013.

First, let’s take a peek at the original Excel file.  This first screen shot is from the Excel application, before it’s been uploaded to SharePoint.  Note that there are 5 worksheets, 2 of which are Power View.

     image   

Next, let’s look at how the Excel file appears in SharePoint.  This next screen shot is from a PowerPivot Gallery in SharePoint 2013.  Note the display of an icon at the bottom right of each thumbnail preview – the tooltip for these reads “The document contains more items than can be displayed by the PowerPivot Gallery.” 

     image

Next we’ll click on the Excel file to render it interactively using Excel Services.  Note that the Power View worksheets do appear along the bottom.  Note also that the interactivity functionality is present.  That’s the good news!  (Keep reading for the bad news…)

     image

Next let’s download the workbook from SharePoint by doing a File > Save As > Download.  We are then presented with a dialog box stating “Features that we can’t show in the browser and interactive reports will be removed from the downloaded copy.  Continue with the download?”   Say Yes.

     image

     image

After selecting a location for the downloaded file, open it in Excel.  Guess what we see in the downloaded version of the file?  You got it…only 3 worksheets.  The 2 Power View worksheets were not retained because they’re not yet fully supported.

     image

The moral of the story here?  Until we have a release that fully supports Power View embedded within Excel, hang onto your original Excel file!

 

Tuesday
May212013

Resolving Error Connecting Report Builder to a PowerPivot Data Source

Recently I saw the following error while working with Report Builder in SharePoint 2013:

“An error occurred while connecting to the data source.  Only the text-based query designer will be available.  The selected data extension DAX is not installed or cannot be loaded.  Verify that the selected data extension is installed on the client for local reports and on the report server for published reports.”

     image

Huh?  I had tested the connection when I set it up, and knew it worked.  But no dice when using it for Report Builder.  Interestingly, my report executed but had empty results.

It turns out I had a connection with the wrong data source type.  Below is info re: setting up a connection in SharePoint for use with Report Builder.

Creating a Data Source for Report Builder Reports in SharePoint 2013

The first thing we need is a Report Data Source (RSDS) created so we can point to it (as a shared data source) when we create the report in Report Builder. 

The data connection can reside in a Data Connections library (if you prefer to centralize), or within the same library as the reports are stored (if you prefer to keep reports & data connections together).  For simplicity, the library shown below contains both reports and a data connection which is sales-specific.

Under Files on the ribbon, click the down arrow for New Document and choose Report Data Source.

     image

Sidenote:  If you don’t see Report Data Source under the New Document menu, you’ll need to add the content type to the library first.  That’s a two-step process within the Library Settings…first, within the Advanced Settings, set “Allow Management of Content Types” to Yes…then back under General Settings choose “Add from existing content types” and select the various options that are related to BI data connections and/or reports (depending on what you need this library).

Set the various Data Source Properties.  Make sure to use the “Microsoft SQL Server Analysis Services” data source type, as shown here:

     SNAGHTML77c93dc

In the above screen shot, note the structure of the connection string.  For a bit more information about creating connections, see my blog entry titled “Comparison of Direct URL, BISM, and RSDS Data Connections for a Power View Report.”

The problem related to my original error was that within this RSDS connection, I had a data source configured with a type of “Microsoft BI Semantic Model for Power View” which – as the name implies – will work for Power View but not for Report Builder.  Problem solved when the type was changed to SSAS.

Here’s what the Report Data Source looks like when it’s been added to the document library:

   image

Binding the RSDS Connection to a Report Builder report in SharePoint 2013

To associate the Report Data Connection it to a Report Builder report, click the ellipses next to an existing report name, then the ellipses one more time, then select Manage Data Sources.

     image

Click the link on the name of your data source.

     image

Ensure the “Shared data source” radio button is selected, and paste in the URL to the Report Data Source created previously.  Note this will have an RSDS file extension.

     image

 

Sunday
Apr282013

Tips for Getting Around Charlotte at PASS Summit

imageHeaded to Charlotte for the PASS Summit in October?  We locals are super excited to have over 4,000 pros arrive in Charlotte!  In addition to the Summit being held October 16-18, the Charlotte BI Group is hosting its 2nd annual SQL Saturday on October 19th.  Below is some information about Charlotte to get you started.

The Lay of the Land

Charlotte is considered a “hub and spoke” city, meaning the downtown area is in the middle & several major roads run outwards from the center – although it’s not actually called downtown; it’s usually referred to as Uptown, or sometimes Center City.  The convention center where the Summit will be held is in Uptown.  I’ve lived in Charlotte just over 3 years now, and I still find that Charlotte can be a bit tough to get around - it’s not a grid layout and street names change a lot (and I mean a lot!).  Here’s a high level map of the Charlotte area:

     image

Charlotte isn’t a huge metropolis, which I actually quite like.  Population of Charlotte is just over 750,000 (with just over 2.2 million if you count the entire metro area).  There’s not too many suburbs; most of the region is referred to as Charlotte.  We are known as the “Queen City” after the British Queen Charlotte Sophia.  Although Charlotte isn't immensely large, we are large enough to have an NFL team (the Carolina Panthers) and an NBA team (the Charlotte Bobcats).  And, of course, Charlotte is well known for its Nascar presence (I even admit to having been to a race...once).

Getting from the Airport to Uptown

Charlotte Douglas International Airport (CLT) is a great little airport.  Personally I’ve always gotten in and out of there very quickly. 

Driving Directions:  From the airport to the Convention Center in Uptown is about 7 miles.   

Taxi:  A taxi should be about $25 from the airport to Uptown (Center City).  Ground Transportation at the airport is just outside of Baggage Claim.

Airport Sprinter Bus:  If you are game for saving a few bucks, you could hop onto a CATS hybrid-electric Sprinter Bus and get to Uptown for $2 (this will be like a regular city bus because the light rail doesn’t run to the airport yet).  The Sprinter Bus runs every 20-30 minutes.  If your hotel is in Uptown, hopefully one of the stops along Trade Street will be fairly near your hotel.  You can transfer to the Lynx light rail at the CTC if needed.

     image

Fun Stuff To Do Near Uptown

imageThe Green.  A small park that I just adore walking through when I’m in Uptown.  It’s a couple of blocks away from the Convention Center near 1st Street between Tryon and College.  There’s different artwork, a lot of it with literary references (I look smarter in the pic just by being there don't ya think?).

NC Music Factory.  The NC Music Factory is a hip & cool place in Uptown which has bars, restaurants, comedy, and music.  It’s in Uptown just off 12th Street (hint:  take a cab over there rather than walking since it’s on the fringes of Uptown).

EpiCentre.  The EpiCentre, located in Uptown at College and Trade, has bars, restaurants, shops, a movie theater and a bowling alley.  Definitely a fun place.  This is also where you can find Whiskey River (the restaurant owned by Dale Earnhardt, Jr.)

There’s also a few trendy little neighborhoods just outside of Uptown if you have some time to explore:  Noda, Dilworth, and Plaza-Midwood.  They each have their share of eclectic dining, arts, and nightlife.

For other attractions in Charlotte, check out the Charlotte’s Got A Lot site.

Links with Helpful Information

PASS Summit 2013 – Travel and Accommodations

Convention & Visitors Bureau – Charlotte’s Got A Lot

Charlotte Center City Partners – Maps

Charlotte’s Got A Lot - Map of Center City

Charlotte Convention Center – Directions and Parking

Charlotte Douglas International Airport – Ground Transportation

Charlotte Area Transit System (CATS) – Routes & Schedules (Choose “5 – Airport” from the drop-down menu)

Lynx Charlotte - Lynx Light Rail