Search
Twitter

Entries in Reporting Services (39)

Friday
Aug162013

Things to Consider When Planning a BI System / Report Conversion Effort

Overview:  It’s common for companies to consider moving from another BI platform to Microsoft BI in order to reduce licensing costs, to take additional advantage of familiar tools, or to expand the body of workers capable of developing and supporting the Microsoft BI system.  Following are some considerations when embarking on such a project.

Fact-Finding about Existing Reports

One of the first things you may wish to tackle is creating an inventory of reports to be converted.  Good thinking.  You may want to do this in parallel with some of the other items listed in the other sections.

  • Consolidation opportunities (for example, could 8 reports be consolidated to 1 with use of appropriate parameter selections?)
  • Level of complexity for each report (so that perhaps you can try to start with easier reports before tackling more difficult ones)
  • Priority level of each report (start with: high value, less effort)
  • Category of each report (such as by subject area, or by data source)
  • Data source(s) required for each report (and will all data sources exist going forward?)
  • Interactivity requirements for each report (such as hyperlinks, drill-down, drill-through)
  • Decision of which reporting tool is most suitable for each report (SSRS and Excel and Power View and PerformancePoint all have their particular strengths)
  • Any cosmetic changes, improvements, or fixes permitted during conversion (if any changes are permitted during conversion that is)
  • Export requirements (this may drive decision on which reporting tool in the Microsoft BI toolset is most appropriate)
  • Subscription and alerting requirements (this may drive decision on which reporting tool in the Microsoft BI toolset is most appropriate)

Planning - BI Change Management

This section includes some of the more broad items to consider which can affect the scope and timing of the project.

  • Identify business rules from the previous toolset's metadata layer (here we are looking for logic embedded in another reporting tool’s metadata layer that needs to be replicated in ETL or elsewhere in the BI system – reproducing a Business Object Universe or a Cognos Framework Manager Model can be done with the BI Semantic Model, but it could take you by surprise and expand the scope & the schedule)
  • Identify data movement and ETL processes used by the previous toolset (here we are looking for any data sources that will no longer exist, or need to be replicated in the new system)
  • Understand security constraints for report access and data access (obviously very important – how much work this is depends on the particulars of your environment and the role the old BI system may have played with regard to security)
  • Develop process for testing & validating converted reports (is a comparison of new results vs. old results acceptable for testing, and does the testing require sign-off from a functional user?)
  • Determine if an automated conversion tool will be used, or if all reports will be redesigned from scratch (this is a big decision – no tool can convert every single report 100% perfectly, but it might save some time especially for basic reports)
  • Identify needs and requirements for governance, auditing and change management (such as approvals required and version history to retain, among many other things)

Planning - BI Training and Documentation

You can’t spend too much time on training and documentation.  Trust me on this.

  • Determine how report developers will get trained on new set of tools (perhaps a combination of classroom training, on-the-job training, and assistance from a consulting firm that specializes in Microsoft BI)
  • Determine the extent of user training that will be optimal
  • Finalize naming conventions & reporting standards (to facilitate a consistent user experience & more efficient development experience)
  • Finalize documentation requirements (this may or may not change with the introduction of a new system)

Planning - BI Environment

These items are focused on the technical components of the BI infrastructure.

  • Planning for the new BI portal (this is the delivery piece – I can’t overstate how important it is to plan this out well)
    • Structure of the overall site (often this is a SharePoint BI Center which includes the enterprise site and team/departmental sites)
    • Structure of report libraries (often by subject, by user base, and/or by report type)
    • Structure for supporting objects (such as data sources, starter reports)
    • Structure for training and documentation materials (such as quick start guides, FAQs, how-to videos)
    • Need for custom metadata columns (such as purpose, description, report owner, who to contact for help)
  • Plan for the backend BI environment (this is the ETL and database piece – critical to ensure performance is optimal)
    • Server architecture (including whether you wish to build it yourself, take advantage of a reference architecture, or if an appliance is most suitable for the workload)
    • Software editions & versions (drives which capabilities are available)
    • On-premise vs. cloud vs. hybrid solution
  • Security for the BI environment, including access to ad-hoc reporting tools and data source connections (the structure of the new BI portal will often be very inter-mingled with security requirements)
  • Template(s) for standardization of report sets (to speed developer productivity and ensure a consistent user experience)
  • Preparation for ongoing monitoring of query loads from new system and resources required to continue to monitor
  • Plan for how Self-Service BI will complement the Corporate BI system, if applicable

ROI and Cost/Benefit of New BI System

This type of analysis can be difficult.  The organizations that do formal ROI and payback calculations already have formulas to do this, so I’ll just list a few things to consider with a BI system.

  • Costs:  Include things like hardware, software licensing, cloud services, consulting services, IT support time, developer time, administrator time, time invested in training, ongoing software maintenance costs, etc
  • Benefits: Include things like cost savings, improved productivity, increase in sales, increase in customer retention, new capabilities, increase in agility, support for strategic goals, faster access to information, broader access to information, etc

Strategies for User Adoption of New BI System

Here you want to consider things that will encourage adoption of the new system.  If a user has a poor initial experience with a system it can take a long time to win them over again.

  • Communication plan (such as timing of rollout, what is expected during testing cycles, schedule of reports to be converted, what users should do during the interim)
  • Support plan (first level support, second level, and if “power users” will get involved with support at all)
  • Training plan (if applicable – at a minimum, some helpful documentation is usually a great idea)

Hope this gives you some things to think about.

 

Thursday
Jul252013

Using an SSRS URL Which Does Not Display Breadcrumbs

Overview: Quick tip about how to structure the URL if you wish to render an SSRS report without any breadcrumbs being displayed at the top left of the browser window.  Applicable to Native Mode Reporting Services configuration.

Why We Want a URL Without Breadcrumbs

I have a client who has a Reporting Services implementation.  Since it’s native mode, the Report Manager SSRS application is where reports are stored and rendered. 

Normally breadcrumbs are really helpful for users to navigate various pages and find their way back from where they came.  However, in this situation the requirement is for users to access reports solely from a menu-driven report portal.  Each menu choice in the report portal contains a URL which runs the report.  This means that, although Report Manager is responsible for rendering each report from the custom menu, users should not navigate or browse around Report Manager itself.

The Report Manager URL Shows Breadcrumbs

When you execute a report via Report Manager, the URL you see in the browser will be structured like this:

http://sqlreport/Reports/Pages/Report.aspx?ItemPath=%2fBI+Reports%2fMarketing%2fDealerDashboard_Internal  

(The actual URL path will vary based on your environment.)

When this “standard” SSRS URL from Report Manager is used, see how the top left of the browser window has breadcrumbs?  Usually that’s great, but recall that we have a requirement to make sure that the user does not browse around Report Manager but, rather, access reports solely from the menu links made available to them.  So, this won’t work for our purposes.

     image

The Report Server URL Omits Breadcrumbs

Using alternative syntax which utilizes the Report Server Web Service rather than the Report Manager application, the breadcrumbs at the top left are suppressed. Mission accomplished!

http://sqlreport/ReportServer/Pages/ReportViewer.aspx?%2fBI+Reports%2fMarketing%2fDealerDashboard_Internal&rs:Command=Render

     image

Depending on your configuration, if you have a named instance or not, and if you changed the default setting for the virtual directory, the above paths could be different. 

Finding More Information

MSDN – Configure Report Server URLs

 

Saturday
Jul062013

Report Builder … the Red-Headed Stepchild of Self-Service BI Tools?

A conversation on Twitter earlier this week has inspired me to pose this question…Is Report Builder the “Red-Headed Stepchild” when it comes to the Microsoft Self-Service BI toolset?  Report Builder doesn’t get much attention these days, for various reasons we’ll discuss below.

(Note:  The perspective of this blog entry is Self-Service BI so Report Builder is the reference – however, everything stated is applicable to full-fledged Reporting Services as well.)

Is Report Builder Deprecated?

Nope!  Report Models, however, are deprecated.  As of SQL Server 2012, you can no longer create or update Report Models (SMDL files).  You can continue to use Report Models currently, but it is recommended to move away from them as time allows.  Report Builder itself is at version 3.0 and is a mature product.

Self-Service BI Tools

The primary set of Microsoft Self-Service BI tools includes Excel (+ add-ins for data modeling such as PowerPivot), Power View, and Report Builder.  Some people actually wouldn’t include Report Builder in this list, but I do believe it has a valid place as one of the Self-Service BI tools (albeit, a smaller audience & used for specific purposes).

Drawbacks of Report Builder

  • Learning curve / ease of use for report designer.  There are a significant number of properties and options.  This offers significant control over the output – the cost for this significant control is ease of use because all the options in Report Builder can be a bit overwhelming for the casual business user.  However, it’s not overly difficult to use for technically adept users who enjoy working with reporting tools and data.
  • Limited interactivity.  While there are some interactive features (such as drill-down, drill-through, sortable columns), each has to be explicitly defined by the report designer.  Report Builder isn’t dynamically interactive like Power View or even Excel – rather, Report Builder is far more suitable for fully formatted reporting needs.
  • Longer to develop.  There’s some things that can be done with Power View or Excel that are inherently more work to do in Report Builder.  The first example that comes to mind is hierarchies – with Power View or Excel, you drop a hierarchy onto the row & the navigation up/down works; with Report Builder you’d have to set up what is shown vs. hidden and the toggle properties.  This is not overly difficult to set up, but could be frustrating for someone just getting started with Report Builder.
  • GUI support is limited.  There is a drag & drop graphical interface for SQL Server and Analysis Services data sources (plus a couple of others).  This limited support leaves the report designer writing query syntax sometimes – which is obviously not the most user-friendly for non-technical folks.  Currently the nicest way for users to work with Report Builder is using a BISM data source (i.e., the data is stored in Analysis Services or PowerPivot).  SQL Server (relational) can be ok for users to work with if the data sources are made to be understandable & easy to work with (ex: with reporting views or stored procedures) – this takes some effort from the IT Dept. or BI Center to make sure it’s made suitable for self-service.

Positives of Report Builder

  • Native connectivity to many data sources, including BISM.  The Microsoft BI framework is very different from other BI tools (such as Cognos or Business Objects) which require a metadata layer – i.e., a report model between the data source & the reporting tool.  Microsoft permits tremendous flexibility here – in fact, you can natively send queries from Report Builder to a variety of databases including non-Microsoft.  Power View is very limited in terms of accepted data sources, and Excel can be somewhat limited (unless you bring the data into an intermediary PowerPivot model first – PowerPivot offers great flexibility in this regard, but do realize you are storing the data redundantly). 
  • Significant formatting control.  If you need a pixel-perfect highly formatted report, Report Builder is the tool for you.  Ironically, this is the inverse of the “learning curve / ease of use” drawback listed above.  With Excel you can exercise a lot of control over the look & feel of your report (except Pivot Tables – you have to use formulas if you need to "break” out of the Pivot Table).  Power View has some text size control and some color schemes to choose from, but overall offers minimal user control over formatting (after all, it’s a data discovery tool meeting an entirely different need – and it is purposely trying to remain simple).
  • Consistent RDL file format.  If a business user starts a report in Report Builder and needs some help with it, one of the IT or BI folks can open the report using their tool of choice (BIDS or SSDT in Visual Studio), make some updates, and send it back to the user with the file format intact.  The consistent format is also helpful if a report is being promoted from the Self-Service environment to the Corporate BI environment.
  • Reusable elements.  To facilitate Self-Service BI using Report Builder, things such as shared data sources (reusable data connections) are obvious but there are others as well.  Shared datasets (reusable queries) can be really helpful to handle common things like Dates and Geography.  Report parts (reusable charts, graphs, tables) can be helpful to display commonly used elements.  Images can also be stored centrally for reuse.  Setting up reusable elements does take some effort from the IT Dept. or BI Center though, but can improve the Self-Service user’s experience tremendously.
  • Parameterization.  Reports with a number of parameters (within reason of course) can sometimes be thought of as “guided ad-hoc analysis” because one report can yield many different combinations of results depending on parameter values.  Report Builder handles parameterization well.
  • Subscriptions and alerts.  If you wish to have reports delivered to you at a predefined frequency or based upon a condition, then Reporting Services is the tool to make that happen.
  • Export and RSS capabilities.  Report Builder can export to many different file formats.  It can also publish an RSS feed – this can be very useful for a business user to consume existing aggregated/calculated data that has been rendered by Report Builder without recalculating or reinventing anything.  Utilizing published report data via RSS also helps with the elusive “one version of the truth” that’s a constant challenge.
  • Integration with SharePoint.  With a Report Builder report, you can view or edit the report directly from the SharePoint document library (with appropriate permissions of course).  Alternatively, a Report Manager portal can be used (although it would be used in isolation from other BI tools and reports).

So, even though sometimes Report Builder seems to be the “Red-Headed Stepchild” I very much appreciate having the tool in our toolbox.  I hope it’s alive and well for a long time. 

Got any thoughts on this subject?  Leave a comment … I’d love to hear your thoughts.

 

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

 

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

 

Saturday
Apr202013

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>'

    image

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

    image

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.

    image