Creating a Bullet Graph in Reporting Services

This is Part 1 in a series on Creating Interesting Visualizations with Microsoft BI Tools.  This part covers 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:

    

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.

    

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:

    

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.

    

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.

    

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. 

    

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.

    

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.

    

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

    

7.  Target (LinearPointer2) Properties. 

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

    

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.

    

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.

    

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.

    

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.

    

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.

    

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.

    

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.

    

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.

    

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

    

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

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

    

Frame page:  Set the Style to be None.

    

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.

    

The finished SSRS gauge object should look like this:

    

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

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

    

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.

    

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:

    

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:

  

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.

    

Click the link on the name of your data source.

    

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.

    

Refreshing an SSRS Snapshot Using T-SQL

Overview: Quick tip about using T-SQL in an Agent Job to refresh a SQL Server Reporting Services snapshot report, rather than a schedule.

SSRS has the capability to schedule the refresh of report snapshots via a report-specific schedule or a shared schedule.  However, what if you don’t have a specific time you want the refresh to run?  Alternatively, what if you want the snapshot to be refreshed after an event occurs, such as ETL completion?

The first step is finding the ReportID (aka ItemID) assigned to the report by ReportServer.  The following query will return several pieces of information, including ReportID (aka ItemID):

SELECT
  NameOfReport = Cat.Name
  ,Cat.Path
  ,ReportID = Cat.ItemID
  ,NameOfAgentJob = Sched.ScheduleID
  ,LastExecutionTime = Cat.ExecutionTime

FROM ReportServer.dbo.Catalog Cat WITH(NOLOCK)

LEFT JOIN ReportServer.dbo.ReportSchedule Sched WITH(NOLOCK)
  ON Cat.ItemID = Sched.ReportID

WHERE Cat.Name = '<InsertReportNameHere>'

   

Now that you have the ID for the specific snapshot report you need to get refreshed, insert that ID for the EventData parameter in the following T-SQL statement:

exec [ReportServer].dbo.AddEvent @EventType='ReportExecutionUpdateSchedule', @EventData='<InsertReportIDHere>'

Using this technique, you could add one or more T-SQL step(s) to kick off the refresh of subscriptions after ETL completes (or whatever other event you wish to trigger the refresh). 

   

If you are controlling refreshes via an Agent job step, you probably also want to make sure the report-specific schedule is set to “Once” or turn it off completely.  If you leave the schedule active (such as the “Once” option shown below), you will still have an Agent Job present that is associated to this schedule (same as how subscriptions are handled).  The name of this Agent job can be found by referring to the Sched.ScheduleID field in the query above.

   

SSRS Report Execution Snapshot vs. a Report History Snapshot

Overview:  An introduction to using snapshots in SQL Server Reporting Services, including an explanation of the differences between a “Report Execution Snapshot” and a “Report History Snapshot.”

Intro to the Ways Users Can Run Reports in Reporting Services

1.  On Demand.  Most reports typically are executed on demand – i.e., the query in the dataset runs and the report is displayed at the time the report is requested by the user.  This is the default in “Processing Options” within Report Manager (accessed via the “Manage” menu option).

     

2.  Cached Reports.  If you have a long-running query that cannot be tuned further, one option you might look into is caching.  With this method, the first user that executes the report will wait for the report to render.  Subsequent users, until the cache expires in minutes or on a schedule, will view the cached report.  Getting the benefit of caching depends on the subsequent users having the same security and same parameter choices that the first user had.

     

3.  Snapshots.  If you have a long-running query, or perhaps a dataset you don’t want users accessing at any & all times of the day, then a snapshot might be a great option.  A snapshot can be thought of as a pre-executed report, usually run on a schedule.  The rest of this blog entry focuses on using snapshots.

     

Prerequisites for Using a Snapshot in Reporting Services

There’s a couple of requirements in order to use a snapshot.  If you are familiar with subscriptions in SSRS, these will sound familiar.

a.  Stored credentials for the data source.  Windows authentication is not acceptable when a report is scheduled.  If you aren’t using a data source with the ID and Password stored (so that data level security is a non-issue when it runs), you’ll get the message “Credentials used to run this report are not stored.” 

     

Tip:  Create a separate shared data source that has a name such as “MCGardenCenterDW_StoredCreds” so you know at a glance it’s using a specific ID and Password as opposed to Windows Authentication (assuming Windows auth is your default).  You may even put these any data sources with stored credentials into their own folder with limited permissions.

b.  Defaults for all Parameters.  Another prerequisite for using snapshots is that all parameters need a default value defined.  This makes sense as there’s no user interaction when a snapshot is being populated at, for instance, 4am.

     

Tip:  Sometimes you might have to be a bit clever in order to ensure all parameters have a default.  For instance, if the default is always “current fiscal period” that continually changes, you can make this happen with an intermediary hidden parameter – it might just take a bit of extra time to think up a good solution.

Scheduling a Snapshot

As you are perusing the options when you “Manage” a report, you will notice there’s two different schedules associated to snapshots.  First, there’s the scheduling options on the “Processing Options” page:

     

Secondly, there’s scheduling options on the “Snapshot Options” page:

     

Initially you might say “huh?” or wonder which should be set.  The key here is that there’s really two kinds of snapshots…

Two Different Types of Snapshots in Reporting Services

On the Processing Options page, you would schedule a “Report Execution” snapshot.  The primary purpose for a Report Execution snapshot is usually to improve performance by reducing report rendering time, or to ensure queries are passed to the source database at very specific times.

Conversely, on the Snapshot Options page, you are setting up a “Report History” snapshot.  The primary purpose for a Report History snapshot is to keep a copy of the report at a specific point in time.

So, basically you want to make sure you set the schedule associated to the purpose you’re trying to accomplish.  Usually it’s just one or the other, but it could be both depending on the requirements.  Microsoft explains it like this:

“Snapshots that are generated as a result of report execution settings have the same characteristics as report history snapshots. The difference is that there is only one report execution snapshot and potentially many report history snapshots. Report history snapshots are accessed from the History page of the report, which stores many instances of a report as it existed at different points in time. In contrast, users access report execution snapshots from folders the same way that they access live reports. In the case of report execution snapshots, no visual cue exists to indicate to users that the report is a snapshot.”

Avoiding the “Selected Report is Not Ready for Viewing” Error

Let’s say on the Processing Options page you checked the radio button to “render this report from a report snapshot.”  Then you scheduled it on the Snapshot Options page.  Sounds reasonable, right?  You can even view the report within the Report History just fine.  However, you click on the report name (i.e., the normal way to run a report) you get a message:  The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available.”

     

Why the message?  And where’s the report?  What happened is with the setup just described, a Report History snapshot now exists but no Report Execution snapshots exists.  So, SSRS has no report to render given this circumstance.  To resolve, it does depend on what you are trying to accomplish, but you probably want to schedule it on the Processing Options page instead.  (Alternatively, if the data rarely changes you could control when it’s created by using the Apply button option.  The Apply button is also useful for generating the first snapshot for testing.)

Viewing Report Execution Snapshots within Report History

There’s one more really important thing to be aware of.  Let’s say you schedule your Report Execution snapshot within the Processing Options.  The report runs fine, but you don’t see it within Report History. 

By default the option to “Store all report snapshots in history” is not checked.  If you want to see a Report Execution snapshot within history, you’ll want to check the box to do so within Snapshot Options.  If you’re only using Report Execution snapshots for this report, you’ll probably want to store them in the history.  However, if you happen to be using both Report Execution and Report History snapshots for the same report, you might want to leave it unchecked so they don’t get mixed up – i.e., if you are doing both, then the schedules will differ for a reason.

     

Letting the User Know When the Data was Refreshed

Since a snapshot will render data at a particular point in time, it’s very kind to the end users of the report to display the “data as of” data in the report header (or footer, however you have it standardized).  To ensure it displays when the snapshot was created, you’ll want to use the ExecutionTime global field (as opposed to Now() or Today() type of functions).  For example:

      =FormatDateTime(Globals!ExecutionTime, DateFormat.ShortDate)

Finding More Information

MSDN – Set Report Processing Properties

MSDN – Processing Options Properties Page (Report Manager)

Cancelling a Long Running SSAS Query

Overview:  Quick tip for cancelling a long-running SQL Server Analysis Services query.  Screen shots & statements are from SQL Server 2008 R2.

We’ve all been there…waiting in BIDS for a report to render.  You begin to think uh-oh.  After a bit longer you know it’s uh-oh. 

  

There’s multiple ways to accomplish cancelling the session; here’s one way to cancel a long-running SSAS query.  One caveat: this technique does require elevated privileges.

Step 1:  Use a DMV to find the SPID

First, you need to find the SPID associated to your long-running report query.  We’ll use a Dynamic Management View (DMV) for that.  First let’s launch an MDX window in SQL Server Management Studio:

  

Obviously you can modify the select statement here to whatever columns you wish to see. 

Query:

SELECT

session_spid

, session_user_name

, session_last_command

, session_current_database

, session_cpu_time_ms

, session_elapsed_time_ms

, session_start_time

, session_last_command_start_time

, session_last_command_end_time

FROM $system.discover_sessions

WHERE session_status = 1

AND session_user_name = 'DomainName\MCoates'

ORDER BY session_start_time desc

Results:

  

In the above query, I restricted the results to just my user name.  Using the results, find the query associated to your long-running report.  Note the Session_Spid (i.e., the Server Process ID).

Step 2:  Use XMLA Command to Cancel the SPID

Now that we have the SPID, we need to pop over to an XMLA window (rather than the MDX window we were in for Step 1 above).

  

Command:

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<SPID>185325</SPID>

</Cancel>

After executing the Cancel Command, you should immediately see the message in BIDS that the operation has been cancelled.  Mission accomplished.

  

A Final Word on Permissions

I’m lucky enough in the current environment to be able to do this on my own.

DMV Select Permissions:  Requires VIEW SERVER STATE or VIEW DATABASE STATE permissions.

XMLA Cancel Command Permissions:  Requires administrative permissions.

Finding More Information

MSDN – Use Dynamic Management Views (DMVs) to Monitor Analysis Services

MSDN – Discover Sessions

MSDN – Cancel Element (XMLA)

Reverse Engineering a SharePoint Integrated Mode SSRS Report for Use in BIDS

Overview:  Discussion of how to alter the RDL code of a Reporting Services (SSRS) report stored in SharePoint (Integrated Mode), so the report can be executed within BIDS.

A Little Background

Recently I was tasked with updating an SSRS report.  So I did a Get Latest in our Team Foundation Server environment & proceeded to open up the SSRS project in BIDS.  I open the report & run it before making any changes.  Huh.  Wait.  Something looks different.  So, I launch my browser & go run the live report, which is stored in SharePoint 2010 running in SSRS Integrated Mode.  Something IS different between the reports!  How can this possibly be?!?

Well, I will not name any names to protect the innocent, but one of the developers had been in a rush to get the report updated so he used Report Builder to change the live report directly in SharePoint.  I proceeded to do much “tsk, tsk, tsking” and looking over my glasses at the poor fella.  So, at this point, my first step is to update BIDS with the latest report code.  We have our BIDS environment integrated with TFS for source control.

Data Sources in BIDS versus SharePoint Integrated Mode

Before we try to sync up the RDL files, let’s do a quick refresher of how data source file extensions are just a bit different when we’re dealing with SharePoint integrated mode:

• BIDS or Report Manager (Native Mode):  RDS file format
• SharePoint Integrated Mode:  RSDS file format

BIDS takes care of this conversion for us when we publish from BIDS to SharePoint.  However, the difference in file formats makes it so we cannot just download the RDL and use it in BIDS – i.e., going backwards requires us to make one modification to the RDL code before the report will execute properly in BIDS.

Steps to Reverse Engineer the Report For Use in BIDS

Step 1.  Download the file.

• Within SharePoint, click the drop-down arrow > Send To > Download a Copy.  Save the RDL file wherever you like.

Step 2.  Copy contents of the file.

• Open the RDL file you just downloaded in your favorite text editor, such as Notepad.  Select all text and copy it onto your clipboard.

Step 3.  (Optional)  Reproduce the error.

Just for grins, let’s try to run the RDL in BIDS, without modifying it, so we can see the error that BIDS returns in this situation.

• Find the outdated version of the report within your BIDS project.  (Note: in my situation, the original RDL did already exist in TFS; it just wasn’t current.  If you need to create a new report, that's fine; the rest of the steps are still the same.)
• Within BIDS, right-click your report in Solution Explorer & choose View Code.  Select all of the XML code & delete it.  Replace it with the RDL code from your clipboard (i.e., the version from SharePoint).
• Now, try to preview the report in BIDS.  You should see an error to the effect of:

      An error occurred during local report processing. Could not find a part of the path … Path\DataSourceName.rsds.rds.

Hmmm, based on the error message we can see that SSRS is looking for the rsds file.  Well, that makes sense since we downloaded it from SharePoint.  The good news is we can fix that very easily!

Step 4.  Update the RDL file.

• Within BIDS, right-click your report in Solution Explorer & choose View Code. 
• Do a Ctrl-F to find the <DataSources> XML tag.  Find <DataSourceReference> & change the “http://url.rsds” to be the name of your data source as you have it set up in BIDS.  Here’s an example:

Before (SharePoint Integrated Mode):

<DataSources>

     <DataSource Name="MelissaDW">

          <DataSourceReference>http://reports.sqlchick.com/Reports/MelissaDW.rsds</DataSourceReference>

          <rd:SecurityType>None</rd:SecurityType>

          <rd:DataSourceID>a1eb2cb6-a837-475c-a924-ff4b4a195c04</rd:DataSourceID>

     </DataSource>

</DataSources>

After (BIDS & Native Mode):

<DataSources>

     <DataSource Name="MelissaDW">

          <DataSourceReference>MelissaDW</DataSourceReference>

          <rd:SecurityType>None</rd:SecurityType>

          <rd:DataSourceID>967ece07-fbff-4580-9968-97ebdd74b6bf</rd:DataSourceID>

     </DataSource>

</DataSources>

• When you preview the report in BIDS now, it should execute properly.  If it doesn’t, then find another existing report in BIDS that uses the same data source.  Copy & paste the whole contents from <DataSource> to </DataSource> and you should be good to go!
• Check-in to TFS, or whatever source control system you are using.

Lastly, it goes without saying, but any time you are modifying the RDL code directly – be super careful!