Jessica M. Moss

Hello, SQL Server 2016 Yesterday, Microsoft announced the release of SQL Server 2016 on June 1st of this year: https://blogs.technet.microsoft.com/dataplatforminsider/2016/05/02/get-ready-sql-server-2016-coming-on-june-1st/ .  Along with performance benchmarks and a description of the new functionality, came the announcement of editions and features for the next release. Good-bye, Business Intelligence Edition The biggest surprise to me was the removal of the Business Intelligence edition that was initially introduced in SQL Server 2012.  Truthfully, it never seemed to fit in the environments where I worked, so I guess it makes sense.  Hopefully, fewer licensing options will make it easier for people to understand their licensing and pick the edition that works best for them. Feature Comparison Overall, the business intelligence services features included with each edition for SQL Server 2016 are fairly similar to SQL Server 2014.  Nothing has been "...

I am excited to be presenting a pre-conference session at the Atlanta SQLSaturday on April 13, 2012.  The session is entitled Learn Reporting Services in a Day! and is a full day introduction to SSRS 2008 R2.  It costs $99.00 through March 15, and $109.00 after that.  There are only 16 seats left, so don’t hesitate to sign up here: http://ssrsinaday.eventbrite.com/ In addition, there is a full slate of sessions on April 14, 2012, including my session: Report Parts: Increasing Productivity Since 2008R2. Here is the information about the pre-conference session: SQL Server MVP, Jessica M. Moss, presents an exciting, introductory, full day training session on SQL Server Reporting Services 2008 R2. In the three-part class, Jessica will teach you how to build reports from the ground up. In Part 1, learn the basics of report development, including picking a report development tool and creating your first report. Part 2 delves into visualizations, groupings, and dri...

One of the things we love in business intelligence is pretty pictures. We want to be able to show graphs and KPIs that really highlight information and direct people to an accurate conclusion. We also want to be able to show images that help tell a story, such as a picture of a certain location or the logo for a store. Analysis Services provides the functionality to show images, but it’s a little buried. Let’s walk through the steps of making it easier for you to access! Our final product will look like this: Setting up Analysis Services to show the image Let’s work with the AdventureWorks 2008 R2 sample database and Analysis Services cube. In the product dimension, there is already an attribute in the database and Analysis Services cube called LargePhoto with a data type of varbinary(max) . This contains an image that is associated with each product in the dimension. In fact, in the Analysis Services dimension, we can see that it has been set up so that the ValueCol...

You may not be aware of this great side of SQL Server: free Reporting Services. When I say free, I don’t even mean “comes with the SQL Server that you already paid for, so is sort of free”, I mean “any random person off the street can use it for free, as in beer” I know, the first time I heard it, I was a little skeptical too. Fortunately, it is absolutely true! SQL Server Express Edition with Advanced Services comes with the ability to create your own reports. There are some limitations of this version; for a full list see this site . A few of the larger limitations are listed here: You can only display data from the SQL Server Express databases on the same server The Reporting Services metadata will be stored in the SQL Server Express database on the same server You can only run on-demand reports, which means no subscriptions This version is a great way to try out the capabilities of Reporting Services and learn how to develop reports. And on top of that, eve...

I had an interesting reporting scenario posed to me that I thought I would share!  (The actual business information has been changed to protect the innocent.)  In this scenario, we own multiple stores that sell a dozen or so products.  We would like a report that shows the type of products that each store has sold on a particular day.  Keep in mind that we could start stocking new products at any time, and we would still like our report to work. Here is our existing data for the database: 3/4/2010 Sports-R-Us Virginia Softball Glove 12/15/2009 Sports-R-Us Virginia Tennis Racquet 12/15/2009 Sports-R-Us Virginia Dartboard 6/6/2009 Sports-R-Us Virginia Fishing Pole 6/6/2009 Games And More Virginia Canoe 2/21/2010 Games And More Mary...

To put it bluntly, configuring SQL Server Reporting Services 2005 or 2008 to run in SharePoint integrated mode with Microsoft Office SharePoint Server 2007 is a pain in the behind.  The setup of the numerous moving pieces seems to get confused between two sets of configurations, two sets of security, and two sets of databases. (I would get confused with all those pieces too! ;) )  Let's take a look at some highlights of the installation steps and a few warnings that may help you in your environment. Components It makes sense that you would need Reporting Services (2005 with SP2 or 2008) and SharePoint (WSS 3.0 or MOSS 2007) in some fashion on your servers.  The secret ingredient to tie everything together is actually an extra add-in that must be installed on your SharePoint server.  This is a free download that can be found here (2005) or here (2008) . Security Once you have installed all components, you must complete a series of screens in the SharePo...

Using the default IIS 7.0 installation on Windows Server 2008 does not include all of the role services necessary to install SQL Server Reporting Services 2005. I found this command in an online forum when looking for a list of all the needed role services. (By the way, I apologize, but I can no longer find the post to link back to. If it was you who wrote it, please post a comment with a link back to that post!) Here is the command. I hope it helps you as much as it helped me! ServerManagerCmd.exe -i Web-Server Web-Asp-Net Web-Http-Redirect Web-Windows-Auth Web-Metabase Web-WMI Version: SQL Server 2005

In a previous post , I showed how to dynamically limit the number of rows shown in a table or matrix. This methodology used a filter on the table to only show the desired number of rows. The disadvantage with this method is that all rows are returned, even if only a small subset of rows is actually desired. To alleviate this problem, you can use a query parameter to restrict the number of rows returned at the query level. This does introduce a different problem. Every time the report is run with a different Top N parameter, the query will be rerun to bring back the correct number of rows. You will need to determine which method is optimal for your situation. Here are the steps to create a sample report to show this new method. 1. Add a report parameter named “NumberPpl” of data type integer. 2. Create a DataSource connecting to the AdventureWorksDW database and a DataSet with the following query: SELECT TOP(cast(@TopN as int)) DimEmployee.FirstName, DimEmployee.LastName, SUM(FactResell...

Extensibility in SQL Server Integration Services and SQL Server Reporting Services is achieved by writing custom code inside your package or report. SSIS provides a great interface by using Visual Studio for Applications (2005) or Visual Studio Tools for Applications (2008), lightweight versions of the Visual Studio IDE. When you create a script task or component, you can write code using intellisense and error squiggles. SSRS, on the other hand, does not provide any IDE for writing custom code. If you want to create VB functions, you have a notepad-like window without colors, intellisense, or any error handling. In comes SnippetCompiler, an application that allows you to write and compile snippets of code. You can download the executable here: http://www.sliver.com/dotnet/SnippetCompiler/ . The current version allows code in both VB.NET 2.0 and C# 2.0. A .NET 3.5 version is in Alpha release and can be downloaded from the same location. Keep in mind that if you minimize the app...

In SQL Server Reporting Services, you can dynamically limit the number of rows that are displayed in a table or a matrix. Here are the steps to create a sample report showing this. 1. Create a DataSource connecting to the AdventureWorksDW database and a DataSet with the following query: SELECT DimEmployee.FirstName, DimEmployee.LastName, SUM(FactResellerSales.SalesAmount) AS SalesAmount FROM DimEmployee INNER JOIN FactResellerSales ON DimEmployee.EmployeeKey = FactResellerSales.EmployeeKey WHERE (FactResellerSales.OrderDateKey = 20011001) GROUP BY DimEmployee.FirstName, DimEmployee.LastName 2. Add a report parameter named “NumberPpl” of data type integer. 3. Add a table to the body of the report with the data fields of firstName, lastName, and salesAmount. Sort by the column salesAmount from ‘Z to A’. 4. Set the Filters option of the table as shown below. (This is where the real work is happening!) 5. Preview the report, modifying the value for the parameter NumberPpl to see the number...

After converting your SQL Server Reporting Services reports from 2005 to 2008, you may notice a difference in the way the report looks. One possible reason for this difference is the change in the way overlapping report items are rendered. In both SSRS versions, the message in the Output window is similar to: "[rsOverlappingReportItems] The text box ‘Textbox2’ and the text box ‘Textbox1’ overlap. Overlapping report items are not supported in all renderers." In Reporting Services 2005, these textboxes would display in an overlapped fashion, often causing the text within both textboxes to be unreadable. In Reporting Services 2008, however, the second textbox shifts, so that it appears the textboxes are vertically next to each other. This allows you to always read the text in both textboxes. The new overlap rendering functionality is included in the "Soft Page-Break Renderers", including the Report Preview window, HTML, MHTML, Word, and Excel. Taking a look at the...

I’ve recently had the pleasure of diving into Reporting Services 2008 RC0, and I’m amazed at the difference in the design environment from SSRS 2000/2005! To give a comparison of the designer, I took a look at the BIDS Reporting Services project. Overall, I think this version is more slick and more user-friendly (once I find everything again :) ) than its predecessor. To begin, there is no longer a Data tab in the main frame of the design environment. You can find your DataSets and Fields on the “Report Data” window on the left side of your designer. If you lose your Report Data window, click the View menu > Report Data, or just select Ctrl-Alt-D. Also joining the new Report Data family are Report Parameters, originally found on the Report menu, and Fields, originally on its own window. A look at the new RS would be remiss without mentioning the new Tablix control. From the toolbox, you still select a Table, Matrix, or List control, but these are templates for the Tablix contr...