Creating a Reporting Services Subscription

Posted on October 13, 2014 by devinknight

Subscriptions are a great feature in Reporting Services that will run a report unattended and deliver it to users either by email or Windows File Share. Subscriptions can also be scheduled so if your end users need a report monthly you can automate the delivery process. The steps given in this article will show you how to configure subscriptions using the Windows File Share delivery method.

Before you start to configure your first subscription make sure to have the following taken care of first:

Start the SQL Server Agent

When you installed Reporting Services it also created the ReportServer database. This database stores all subscription related data among other things. Ensure the SQL Server Agent is running on the database engine that has the ReportServer database.

To start the SQL Server Agent you can either login to the database engine in SQL Server Management Studio or open the SQL Server Configuration Manager then right-click and select start on the SQL Server Agent.

SQL Server Configuration Manager SQL Server Management Studio
1 2

Enabling sharing on folder delivering to

If you have decided to deliver reports via Windows File Share then you must configure the folder that will accept the reports for sharing. Navigate to the folder that you want to be used with the Subscription and right-click on it then select Properties. Go to the Sharing property tab to enable sharing.

3

Use SQL Server Authentication on data sources used in report

To enable Subscriptions on a report you must have a SQL Server Authenticated account used to access the data sources. In SQL Server Management Studio, create a new SQL login that has db_datareader access to the databases used in the report data sources.

4

After you have created this account change the report data sources to make sure it is being used in the Report Manager.

5

Now that these prerequisites have been taken care you can actually create a new Subscription. Click on the report that you want to add a Subscription to and click New Subscription in the report toolbar. If you do not see the New Subscription option you may need to have your privileges elevated.

6

There are several options you have when creating a Subscription:

Delivered by – Can either be Windows File Share or Email. If you have not setup your SMTP server to send email then your only option is Window File Share. The SMTP configuration is setup in the Reporting Services Configuration Manager.

File Name – What you want the file that is created to be called. Also, there is a checkbox for “Add a file extension when the file is created” which is checked by default. There may be some special circumstances where you do not want the file extension, like your work email does not accept attachments with an .xml extension.

Path – The file path where the file should be sent to. It must be written as the full UNC path (\\ServerName\FolderName). For example, my path would be \\Devin-PC\Reports.

Render Format – How you want the format to be rendered. Your options are XML file with report data, CSV (comma delimited), Acrobat (PDF) file, HTML 4.0, MHTML (web archive), Excel, RPL renderer, TIFF file, or Word.

Credentials used to access the file share – This must be a login that you setup to have access the file share in the eariler prerequisites.

Overwrite options – Overwrite an existing file with a newer version is set by default and will always replace a file if they are named the same. Do not overwrite the file if a previous version exists will keep the original file and cause the subscription to fail. Increment file names as newer versions are added will continue to add a new file if one already exists with the same name but will increment it (ReportName_1.pdf, ReportName_2.pdf, ReportName_3.pdf).

7

You can also click Select Schedule to design a set time for the Subsciption to run. You can also configure when you want the schedule to start and how long it should continue. Once you have set the schedule you want click OK.

8

This will return you to the previous screen where can lastly decide to pass in different values to the report parameters before you finalize the Subsciption by clicking OK.

On the Subscription tab of the report you should see listed all the Subscriptions that have been created for this report.

9

When the Subscription runs you should see the file now in your shared folder. If you open Management Studio and take a look at the SQL Agent jobs you will see there is a new one for the Subscription you created. Here you can run the Subscription manually so you do not have to wait for the scheduled time.

10

WARNING: Do not change any settings of this job. Even just changing the name could cause the Subscription to fail.

Filed under: SSRS | Tagged: , | 1 Comment »

Writing Parametrized MDX for Reporting Services

Posted on August 4, 2014 by devinknight

Writing MDX inside of Reporting Services has been the nemesis for many report writers for far too long.  If all you need is the Query Designer to create a drag and drop datasets then it’s a piece of cake, but have you ever wondered what’s going on with all the MDX that the Query Designer creates.  Or how about how do the parameters work that it creates inside MDX.  I’ve heard many report writers that use Analysis Services as a data source (including myself) say it is too difficult to create parameters and that’s why they use the Query Designer.  In reality, I think what the real problem is probably that the MDX results that the Query Designer provides make it look a lot more difficult than it really is.  If you know some rudimentary MDX you can probably be writing your datasets yourself instead of using the Query Designer, which there’s no problem with sticking with if it fits your needs.  The goal of this article is to guide you through writing a basic MDX statement that can be used for a report query and more importantly show you how to parameterize it. 

The two MDX functions that we are going to use for this are:

StrToSet

Returns the set specified in the string expression (MSDN definition).  Essentially, Converts the text you write into an MDX set.

StrToMember

Returns the member specified in the string expression (MSDN definition).  Converts the text you write into an MDX member.

 First we will start with an example that uses StrToSet and then add to it using StrtoMember.  To follow along with this example you’ll need the Adventure Works cube that can be found at www.codeplex.com.  I’ll assume you know some basics of Reporting Services and not define each component of the tool that is not new for this example. 

Create a new Report Server Project and Report that uses the Adventure Works cube as a Data Source.  Next, create a Dataset and for this example you can call it CategorySales (this Dataset should use the Adventure Works cube data source).   Select Query Designer and click the Design Mode button 1a  to begin writing your query.  Use the following query to return back results without a parameter:

1

Select
[Measures].[Internet Sales Amount] on Columns
From [Adventure Works]

All this query did was returned back the total sales, but this is useless for a report by itself.  Let’s say we want to add the product category that the sales belong to.  If we were to do this normally in MDX it would look something like this:

Select
[Measures].[Internet Sales Amount] on Columns,
[Product].[Category].Children on Rows
From [Adventure Works]

That’s not good enough for our pretend end users though.  They not only want to see the list of product category sales, but they also want it made into a parameter so they can select from a dropdown box the category or categories they wish to view.  To do this, first create the parameter by selecting the Query Parameters button ParameterButton.

  • Assign the parameter a name. Ours  will be called ProductCategory
  • Identify the Dimension and Hierarchy it associates with. Ours will be from the Product dimension and the Category attribute hierarchy
  • Multiple values identifies if it should accept multiple values.
  • Last give the parameter a default value. This can set to Bikes for our example

2

After you hit OK to confirm the Query Parameter screen you will need to modify your MDX to take advantage of the parameter you have created.

Select
[Measures].[Internet Sales Amount] on Columns,
StrToSet(@ProductCategory, Constrained) on Rows
From [Adventure Works]

Here we use StrToSet to convert whatever is brought in from the parameter values selected by the end users to something MDX can understand.  The Constrained flag here just ensures that parameter provides a member name in the set.

Hit OK twice to confirm the query so far and make a simple tabular report to view your results so far.  You will notice that a dropdown box parameter was automatically created for the ProductCategory parameter we defined. You may already be familiar with this if you have ever used the drag and drop interface for making parameters. Reporting Services automatically creates a Dataset for this parameter dropdown box, which you can view if you right-click on the datasets folder and select Show Hidden Datasets.

3

Now let’s take this example one step further.  We will add two more parameters to our query to create a date range.  Hit the Query Parameters button again and create the parameters StartDate and EndDate that reference the Date Dimension and the Date attribute.  These can be defaulted to any date for now. 

4

Change the MDX to now add a where clause that restricts dates this time using StrToMember .

Select
[Measures].[Internet Sales Amount] on Columns,
StrToSet(@ProductCategory, Constrained) on Rows
From [Adventure Works]
Where StrToMember(@StartDate, Constrained) :StrToMember(@EndDate, Constrained)

Hit OK twice to confirm the changes to the Dataset.  Remember that these parameters will automatically create new Datasets and parameters so you should just need to preview the report to see the changes.

5

I hope this makes writing your own MDX with parameters inside Reporting Services a little easier. 

Filed under: SSRS | Tagged: , | Leave a comment »

New Book: Building Performance Dashboards and Balanced Scorecards with SQL Server Reporting Services

Posted on November 14, 2013 by devinknight

I’m very excited to announce that you can now purchase my latest book: Building Performance Dashboards and Balanced Scorecards with SQL Server Reporting Services . This booked, offered by Wiley, takes you through designing effective balanced scorecards using Reporting Services. You can get your copy of my new book here!

Filed under: SSRS | Tagged: , , | Leave a comment »

SSRS – Using Lookup Function for Multiple Column Join

Posted on July 27, 2012 by devinknight

When SQL Server 2008 R2 was released several new Reporting Services expressions were made available that would help join multiple datasets together.  Those functions were the Lookup Function, LookupSet Function and the MultiLookup Function.  My general recommendation for these function is not to use them when you have two dataset from the same data source.  They’re really intended for connecting datasets together that are not directly related because otherwise I could just handle it in my source query.

My experience with them had been fairly academic until recently when I had a real scenario where the Lookup Function would be beneficial.  I was working with a customer that had a report requirement that needed data from DB2 and Oracle in a single tabular report.  What made this circumstance more difficult was that the join between the DB2 dataset and the Oracle dataset was not a single column but two.

Seems basic enough right?  If we were writing T-SQL we would simply do a join on multiple columns with an AND between them.  Because these are two different data sources in Reporting Services I had to get a little more creative.  If you’ve used the Lookup function in SSRS (template below) expressions then you know it can only accept one field from the source and one from the destination.  So what if you have two columns to join on?

Lookup(source_expression, destination_expression, result_expression, dataset)

The approach I took was to concatenate the two join columns together and then place the concatenated value in the Lookup function.  I also placed a literal string between the two columns to protect myself from coincidental matches.  To make the solution a little cleaner I created a calculated field on each dataset that handled the expression.  So both datasets had a calculated field I called JoinColumn and was defined similar to this:

=Fields!JoinColumn1.Value + ”||” + Fields!JoinColumn2.Value

Notice the double pipes that are used here to protect from coincidental matches.  With these calculated field created on each dataset now my Lookup function would be fairly standard.  Here’s my end result:

Lookup(Fields!JoinColumn.Value, Fields!JoinColumn.Value, Fields!ReturnColumn.Value, “DataSet2”)

I hope this helps as you find practical uses for the Lookup Function in Reporting Services.

Filed under: SSRS | Tagged: , , , | 15 Comments »

Choosing the Right Microsoft Reporting Technology Part 6: Making the Decision

Posted on May 2, 2012 by devinknight

We’ve completed reviewing the major Microsoft tools and now you have to make “The Decision”.  Which tool(s) is the best fit for the reporting needs?  Remember just picking one tool and using it for all reports may not be the best decision because you are missing out on a lot of the benefits of other tools. 

One tool I like to use that helps me make a decision like this is a decision matrix.  With a decision matrix you provide the possible options, which for us has been the following tools:

You also provide the factors that are important to you in making a decision.  You can see in the screenshot below I’m considering tools based on:

  • Ad Hoc Reporting
  • Content Sharing
  • User Flexibility
  • Developer Flexibility
  • Time to Develop

image

Within the matrix you rate each of the tools (0-100) in their capabilities of the factors you’re considering.  This rating should only be done once. 

Some factors to making a reporting decision may be more important than others depending on the need.  For example, for one report Ad Hoc Reporting may be the most important thing while another report Content Sharing may be the most important thing.  That’s why you sign a relative weight (0-10) to each decision you need to make.  That way if Ad Hoc Reporting is most important than rate it higher than the other factors.  Again, this weighting system would be changed for each report you need to make a decision on choosing a tool.

Download a sample Decision Matrix here:

https://devinknightsql.files.wordpress.com/2012/04/decision-matrix.xlsx

Using a tool like a Decision Matrix can help you make decisions quickly and with more confidence.  I hope this series has been helpful.  If you need to catch up on previous posts follow the links below.

Filed under: Excel, PerformancePoint, Power View, PowerPivot, SSRS | Tagged: | 10 Comments »

Choosing the Right Microsoft Reporting Technology Part 1: Report Services

Posted on March 1, 2012 by devinknight

As Business Intelligence has evolved over the years the number of tools we have to choose from for presenting data has advanced drastically.  With so many tools to choose from it can be rather confusing (especially when your company is just getting their feet wet in BI) to determine which tool is the right one for an organizations reporting needs.  Just within the Microsoft suite of tools (not including third-party tools) you have Reporting Services, Excel, PowerPivot, PerformancePoint and Power View.

Many companies try to marry themselves to one or two reporting tools and fit their needs into the restrictions of the tool(s) they have chosen.  The truth is not a single one of these tools can solve all reporting needs.  While each one of these tools by themselves may be able to present your data, you will find that using a combination approach will conclude in a much more well rounded and impressive reporting solution.

That’s why in this blog series I hope to demystify the decision process and educate you a little bit on each of the tools so that you can easily determine which tool would be best in different scenarios.  Like I mentioned earlier the Microsoft reporting tools we will cover will be:

  • Reporting Services
  • Excel (From the perspective of building PivotTables without PowerPivot)
  • PowerPivot
  • PerformancePoint
  • Power View (Yes Power View is supposed to be considered part of Reporting Services in SQL Server 2012, but it really is an entirely different tool than SSRS is traditionally thought of as)

After discussing each of the tools each individually I’ve bring it all together by showing you how to use a decision matrix to determine which tool makes sense for your work examples.

Reporting Services

What it is

Reporting Services is traditionally used for developing static reports.  The word static here is referring to the report layout rather than the report data (obviously the data will change).  For example, I want my sales team to be able to review a line item detail of each of their sales from the prior day.  With Reporting Services I could have the sales team either log onto a web front end to view a live version of the report or I could have the report emailed to them daily.

image

The reports developed with the tool are highly customizable.  In fact just about any object of the report can be manually changed or made dynamic with the expression language it uses.

If you’re looking for a tool for creating dashboards then Reporting Services also has the capability of filling that need.  With charts, gauges, indicators, sparklines and maps you have many of the necessary tools for creating an impressive dashboard.  There are other tools that like PerformancePoint that can create more impressive interactive dashboards but Reporting Services can get the job done.

Reporting Services is also highly scalable.  Without much effort the workload for report processing can be shared across multiple instances of reporting services.  After installing multiple instances the native wizard guides you through creating a farm of report servers.

Another thing worth noting because it will not be true of all the other tools I discuss in this series is that it can connect to virtually any data source you have.

What it isn’t

While there are some parts of Reporting Services that can provide ad hoc reporting capabilities the core of the tool is for static reporting.  If you really have a need for ad hoc reporting and could care less about report layout than you would likely pick another tool like Excel.

Who Uses it

While Reporting Services does have functionality for a power user to create their own reports it is typically thought of as a developers tool.  So report developers would use the tool Business Intelligence Development Studio (BIDS), which is part of the SQL Server install) to create incredibly detailed reports with almost endless flexibility when it comes the visualizations.

The tool that power users can use for creating reports is called Report Builder.  It has almost all the same functionality as BIDS but has been simplified a little for end users with more wizards and easier ways to bring in datasets.  Because it has a lot of the same functionality of the developers too you will find that this tool is for highly technical end user.

How is it consumed

Like I mentioned earlier Reporting Services reports can be consumed through a web front end of the native Report Manager, SharePoint, or even embedded in a custom application you’ve written with the Report Viewer control.

In addition to viewing the reports from a web browser they can also be delivered directly to the users via email or in a shared folder that they can access.  This is done using an incredibly useful part of the tool call Subscriptions.

Limitations

Again, Reporting Services is a static reporting tool therefore if your users find the need to change report layout frequently than that may eliminate this tool as an option for that particular report.

Another limitation you may run into is some of the functionality with Subscriptions, the report delivery tool mentioned earlier, are enterprise only features.  For example, if you wanted to make the reports delivered dynamic based off a list of emails you have in the database that must be done using Data Driven Subscriptions, which are Enterprise only.

Summary

As we go through this series remember these high level characteristics about Reporting Services:

  • For static report development
  • Extremely customizable
  • Dashboard capable
  • Highly scalable
  • Traditionally thought of as a developers tool
  • Has a native web front end but can also view reports through SharePoint and direct delivery with subscriptions

I hope you’ve found this helpful and stay tuned for the Part 2 in this series on Excel.  To read any of the other parts to this series follow the links below.

Filed under: SSRS | Tagged: , | 9 Comments »

SSRS – Creating a Reporting Services Web Farm

Posted on February 20, 2012 by devinknight

Reporting Services performance problems can often be narrowed down to report design or resource constraints and sometimes a combination of the two.  In this post I’ll review the process of scaling-out Reporting Services so server resource constraints can be shared across multiple servers instead of relying on a single server for all report processing.

If you are new to understanding Reporting Services architecture it is important to understand the general steps that are occurring behind the scenes with Reporting Services. From the beginning of a user request the steps that happen are:

  • Client makes a request to the report server
  • Report server then makes a request to the report catalog
  • Report catalog sends back to report server report definition and tells the report server which data sources to hit.
  • Report server request data from source database
  • Source database sends data and the report server renders report metadata and data for the client

Understanding Scaling-Out Architecture

If you have ever installed installed SQL Server and the BI stack locally on your machine for testing purposes than you’ve likely done a single server install.  This means you have SQL Server, Reporting Services, Analysis Services and Integration Services engines all running on the same machine. 

image

While this might be fine running in a local testing environment on your machine this obviously doesn’t scale well on a server that requires thousands of users to connect.  In an environment that requires this many users a scaled-out deployment is ideal and can support a heavier workload. 

In a scaled-out deployment multiple Report Server instances are installed that all use the same repository or ReportServer database.  So when a thousand client machines make a request to the ReportServer the report rendering can be split the requests across multiple servers to do the work.  They still must use the same repository database because they still want to view the same report metadata but the report processing should be helped.  The screenshot below depicts a scaled-out deployment with three instances of Reporting Services installed and a single SQL Server database repository to return back report metadata.  This is an example of a common setup but not the only way to do this of course.

image

With this approach we could have our west coast customers using a URL that points to Server A, mid west customers using a URL that points to Server B, and all east coast customer use Server C. 

Another approach to this is to use a Network Load Balancer (NLB).  The idea behind a Network Load Balancer is that you provide a single virtual URL to the report server and the NLB sends the user to the Report Server that is least utilized.  So if you have two servers and Server A is only 20 percent utilized and Server B is 80 percent utilized then the NLB would choose Server A for the next incoming user instead of you manually providing a URL for each user.  The Network Load Balancer can either be a physical device on your network for making these decisions or you can enable the feature in Windows Server.

Setup and Configuration

The setup and configuration for scaling Reporting Services into a Web Farm is actually fairly easy.  In fact to oversimplify it here is an overview of the steps required:

  • Install SQL Server (Database Engine) on one server.
  • Install Reporting Services on two servers without configuring either.
  • Pick one of the Report Server installations and configure it using the Reporting Services Configuration Manager.  Go through each page of the configuration manager except the Execution Account and E-mail Setting, which are optional setting depending on you required setup.
  • Configure the second Report Server but use same report catalog database as the first and set URLs.  So on the database page ensure it points to the same database that was setup during the setup of the first Report Server

image

  • Go back to first Report Server you configured and click Add Server under the Scale-out Deployment page to join the instances as shown below.

image

Without overcomplicating this that is really all it takes.  This would be done for as many Report Servers as you would like to add to the farm.  Hope this helps.

Filed under: SSRS | Tagged: , | 3 Comments »

Next Page »