Search results matching tag 'SSRS'

SSIS Reporting Pack – a performance tip

jamiet — Mon, 05 Nov 2012 22:36:41 GMT

SSIS Reporting Pack is a suite of open source SQL Server Reporting Services (SSRS) reports that provide additional insight into the SQL Server Integration Services (SSIS) 2012 Catalog. You can read more about SSIS Reporting Pack here on my blog or had over to the home page for the project at http://ssisreportingpack.codeplex.com/.

After having used SSRS Reporting Pack on a real project for a few months now I have come to realise that if you have any sizeable data volumes in [SSISDB] then the reports in SSIS Reporting Pack will suffer from chronic performance problems – I have seen the “execution” report take upwards of 30minutes to return data. To combat this I highly recommend that you create an index on the [SSISDB].[internal].[event_messages].[operation_id] & [SSISDB].[internal].[operation_messages].[operation_id] fields. Phil Brammer has experienced similar problems himself and has since made it easy for the rest of us by preparing some scripts to create the indexes that he recommends and he has shared those scripts via his blog at http://www.ssistalk.com/SSIS_2012_Missing_Indexes.zip. If you are using SSIS Reporting Pack, or even if you are simply querying [SSISDB], I highly recommend that you download Phil’s scripts and test them out on your own SSIS Catalog(s).

Those indexes will not solve all problems but they will make some of your reports run quicker. I am working on some further enhancements that should further improve the performance of the reports. Watch this space.

@Jamiet

Multi-State Maps in Reporting Services

smisner — Thu, 24 Mar 2011 13:33:17 GMT

In SQL Server 2008 R2 Reporting Services, you can now create maps for reports using the built-in map gallery of the United States or individual states, ESRI shapefiles, or a spatial query. In a previous post, I explained how to obtain an ESRI shapefile for another country and convert it into spatial data so that you can have other maps available in the map gallery. In this post, I'll show you how to create one map from multiple ESRI shapefiles. Whether you add it to the map gallery or not is up to you!

Obtaining State Shapefiles

To start, the process is similar to the one I described in my previous post on working with spatial data. This time my goal is to create a map of two states - Nevada and California. The best place to download free shapefiles for US states is the U.S. Census Bureau where I used the link to download by state. I found the Nevada directory and then used the topmost directory 32 where there many files to download. I chose tl_2010_32_state00.zip. Similarly in the California directory, I used the topmost directory to find and download tl_2010_06_state00.zip. Then I extracted each zip file to its own directory on my computer. They each contain a variety of files, including SHP and DBF files that I could use for a map using the ESRI shapefile data source option if I want to create a separate map for each state. But I want to combine these maps, so my mission is not yet complete.

Converting Shapefiles to SQL Spatial Data

My next step is to use a tool called Shape2SQL to extract the spatial data from the shapefile into a SQL Server table. Pinal Dave (blog|twitter) has a tutorial explaining how to do that. I had the same experience that I described in my last post - I had to clear the Create Spatial Index checkbox to get the table to load properly for the first state. When I loaded the second state, I had to clear the Replace Existing Table checkbox. Now I have both states in a table called State, as shown below, with a geom column having the SQL Geometry data type.

Creating the Multi-State Map

Now that I have my spatial data in a table, I'm ready to create the map. In Business Intelligence Development Studio, I added the map to my report which launched the Map Wizard and then I used the following steps:

On the Choose a source of spatial data page of the wizard, select SQL Server spatial query, and click Next.
On the Choose a dataset with SQL Server spatial data page, select Add a new dataset with SQL Server spatial data.
On the Choose a connection to a SQL Server spatial data source page, select New.
In the Data Source Properties dialog box, on the General page, add a connecton string like this (changing your server name if necessary):
Data Source=(local);Initial Catalog=SpatialData
Click OK and then click Next.
On the Design a query page, add a query for the country shape, like this:
SELECT NAME00, geom FROM State
Click Next. The map wizard reads the spatial data and renders it for you on the Choose spatial data and map view options page, as shown below.

Of course, you can use these technique with any shapefiles that you can locate. It's not limited to the United States. For example, if you want to create a map of multiple European countries or a combination of states in India, you could use a similar process by downloading shapefiles from either the Global Administrative Areas spatial database or the Natural Earth database.

The Case of the Extra Page: Rendering Reporting Services as PDF

smisner — Fri, 18 Mar 2011 18:49:47 GMT

I had to troubleshoot a problem with a mysterious extra page appearing in a PDF this week. My first thought was that it was likely to caused by one of the most common problems that people encounter when developing reports that eventually get rendered as PDF is getting blank pages inserted into the PDF document. The cause of the blank pages is usually related to sizing. You can learn more at Understanding Pagination in Reporting Services in Books Online.

When designing a report, you have to be really careful with the layout of items in the body. As you move items around, the body will expand to accommodate the space you're using and you might eventually tighten everything back up again, but the body doesn't automatically collapse. One of my favorite things to do in Reporting Services 2005 - which I dubbed the "vacu-pack" method - was to just erase the size property of the Body and let it auto-calculate the new size, squeezing out all the extra space. Alas, that method no longer works beginning with Reporting Services 2008. Even when you make sure the body size is as small as possible (with no unnecessary extra space along the top, bottom, left, or right side of the body), it's important to calculate the body size plus header plus footer plus the margins and ensure that the calculated height and width do not exceed the report's height and width (shown as the page in the illustration above). This won't matter if users always render reports online, but they'll get extra pages in a PDF document if the report's height and width are smaller than the calculate space.

Beginning the Investigation

In the situation that I was troubleshooting, I checked the properties:

Item	Property	Value
Body	Height	6.25in
	Width	10.5in
Page Header	Height	1in
Page Footer	Height	0.25in
Report	Left Margin	0.1in
	Right Margin	0.1in
	Top Margin	0.05in
	Bottom Margin	0.05in
	Page Size - Height	8.5in
	Page Size - Width	11in

So I calculated the total width using Body Width + Left Margin + Right Margin and came up with a value of 10.7 inches. And then I calculated the total height using Body Height + Page Header Height + Page Footer Height + Top Margin + Bottom Margin and got 7.6 inches. Well, page sizing couldn't be the reason for the extra page in my report because 10.7 inches is smaller than the report's width of 11 inches and 7.6 inches is smaller than the report's height of 8.5 inches. I had to look elsewhere to find the culprit.

Conducting the Third Degree

My next thought was to focus on the rendering size of the items in the report. I've adapted my problem to use the Adventure Works database. At the top of the report are two charts, and then below each chart is a rectangle that contains a table.

In the real-life scenario, there were some graphics present as a background for the tables which fit within the rectangles that were about 3 inches high so the visual space of the rectangles matched the visual space of the charts - also about 3 inches high. But there was also a huge amount of white space at the bottom of the page, and as I mentioned at the beginning of this post, a second page which was blank except for the footer that appeared at the bottom. Placing a textbox beneath the rectangles to see if they would appear on the first page resulted the textbox's appearance on the second page. For some reason, the rectangles wanted a buffer zone beneath them. What's going on?

Taking the Suspect into Custody

My next step was to see what was really going on with the rectangle. The graphic appeared to be correctly sized, but the behavior in the report indicated the rectangle was growing. So I added a border to the rectangle to see what it was doing.

When I added borders, I could see that the size of each rectangle was growing to accommodate the table it contains. The rectangle on the right is slightly larger than the one on the left because the table on the right contains an extra row. The rectangle is trying to preserve the whitespace that appears in the layout, as shown below.

Closing the Case

Now that I knew what the problem was, what could I do about it? Because of the graphic in the rectangle (not shown), I couldn't eliminate the use of the rectangles and just show the tables. But fortunately, there is a report property that comes to the rescue: ConsumeContainerWhitespace (accessible only in the Properties window). I set the value of this property to True. Problem solved. Now the rectangles remain fixed at the configured size and don't grow vertically to preserve the whitespace. Case closed.

Applying Interactive Sorting to Multiple Columns in Reporting Services

smisner — Wed, 16 Mar 2011 18:06:00 GMT

A nice feature that appeared first in SQL Server 2005 (not SQL Server 2008 - I mis-remembered!) is the ability to allow the user to click a column header to sort that column. It defaults to an ascending sort first, but you can click the column again to switch to a descending sort. You can learn more about interactive sorts in general at the Adding Interactive Sort to a Data Region in Books Online. Not mentioned in the article is how to apply interactive sorting to multiple columns, hence the reason for this post!

Let’s say that I have a simple table like this:

To enable interactive sorting, I open the Text Box properties for each of the column headers – the ones in the top row. Here’s an example of how I set up basic interactive sorting:

Now when I preview the report, I see icons appear in each text box on the header row to indicate that interactive sorting is enabled. The initial sort order that displays when you preview the report depends on how you design the report. In this case, the report sorts by Sales Territory Group first, and then by Calendar Year.

Interactive sorting overrides the report design. So let’s say that I want to sort first by Calendar Year, and then by Sales Territory Group. To do this, I click the arrow to the right of Calendar Year, and then, while pressing the Shift key, I click the arrow to the right of Sales Territory Group twice (once for ascending order and then a second time for descending order). Now my report looks like this:

This technique only seems to work when you have a minimum of three columns configured with interactive sorting. If I remove the property from one of the columns in the above example, and try to use the interactive sorting on the remaining two columns, I can sort only the first column. The sort on the second column gets ignored. I don’t know if that’s by design or a bug, but I do know that’s what I’m experiencing when I try it out!

Working with Reporting Services Filters–Part 5: OR Logic

smisner — Fri, 04 Feb 2011 17:26:43 GMT

When you combine multiple filters, Reporting Services uses AND logic. Once upon a time, there was actually a drop-down list for selecting AND or OR between filters which was very confusing to people because often it was grayed out. Now that selection is gone, but no matter. It wouldn’t help us solve the problem that I want to describe today.

As with many problems, Reporting Services gives us more than one way to apply OR logic in a filter. If I want a filter to include this value OR that value for the same field, one approach is to set up the filter is to use the IN operator as I explained in Part 1 of this series. But what if I want to base the filter on two different fields? I need a different solution.

Using the AdventureWorksDW2008R2 database, I have a report that lists product sales:

Let’s say that I want to filter this report to show only products that are Bikes (a category) OR products for which sales were greater than $1,000 in a year.

If I set up the filter like this:

Expression	Data Type	Operator	Value
[Category]	Text	=	Bikes
[SalesAmount]		>	1000

Then AND logic is used which means that both conditions must be true. That’s not the result I want.

Instead, I need to set up the filter like this:

Expression	Data Type	Operator	Value
=Fields!EnglishProductCategoryName.Value = "Bikes" OR Fields!SalesAmount.Value > 1000	Boolean	=	=True

The OR logic needs to be part of the expression so that it can return a Boolean value that we test against the Value. Notice that I have used =True rather than True for the value. The filtered report appears below. Any non-bike product appears only if the total sales exceed $1,000, whereas Bikes appear regardless of sales. (You can’t see it in this screenshot, but Mountain-400-W Silver, 38 has sales of $923 in 2007 but gets included because it is in the Bikes category.)

The Case of the Missing Date/Time Stamp: Reporting Services 2008 R2 Snapshots

smisner — Tue, 25 Jan 2011 23:14:46 GMT

This week I stumbled upon an undocumented “feature” in SQL Server 2008 R2 Reporting Services as I was preparing a demonstration on how to set up and use report snapshots. If you’re familiar with the main changes in this latest release of Reporting Services, you probably already know that Report Manager got a facelift this time around. Although this facelift was generally a good thing, one of the casualties – in my opinion – is the loss of the snapshot label that served two purposes… First, it flagged the report as a snapshot. Second, it let you know when that snapshot was created.

As part of my standard operating procedure when demonstrating report snapshots, I point out this label, so I was rather taken aback when I didn’t see it in the demonstration I was preparing. It sort of upset my routine, and I’m rather partial to my routines. I thought perhaps I wasn’t looking in the right place and changed Report Manager from Tile View to Detail View, but no – that label was still missing. In the grand scheme of life, it’s not an earth-shattering change, but you’ll have to look at the Modified Date in Details View to know when the snapshot was run. Or hope that the report developer included a textbox to show the execution time in the report. (Hint: this is a good time to add this to your list of report development best practices, whether a report gets set up as a report snapshot or not!)

A snapshot from the past

In case you don’t remember how a snapshot appeared in Report Manager back in the old days (of SQL Server 2008 and earlier), here’s an image I snagged from my Reporting Services 2008 Step by Step manuscript:

A snapshot in the present

A report server running in SharePoint integrated mode had no such label. There you had to rely on the Report Modified date-time stamp to know the snapshot execution time. So I guess all platforms are now consistent.

Here’s a screenshot of Report Manager in the 2008 R2 version. One of these is a snapshot and the rest execute on demand. Can you tell which is the snapshot?

Consider descriptions as an alternative

So my report snapshot demonstration has one less step, and I’ll need to edit the Denali version of the Step by Step book. Things are simpler this way, but I sure wish we had an easier way to identify the execution methods of the reports. Consider using the description field to alert users that the report is a snapshot. It might save you a few questions about why the data isn’t up-to-date if the users know that something changed in the source of the report. Notice that the full description doesn’t display in Tile View, so keep it short and sweet or instruct users to open Details View to see the entire description.

Working with Reporting Services Filters - Part 4: Creating a NOT IN Filter

smisner — Thu, 20 Jan 2011 18:38:59 GMT

A question came in from a reader asking about how to create a NOT IN filter. You can see in the first of this series of blog posts about Reporting Services filters, NOT IN is not available in our list of operators for filters. However, it's still possible to produce a filter that excludes items in the list. The technique is not particularly intuitive, which is why I've added this filter type to my series on filters.

Preparing the Report

Let's start with a simple scenario using the AdventureWorksDW2008R2 database. I set up a query that returns reseller sales by category. In my report, I have two tables: one for Bikes and Components, and the other for everything else. I want to create a filter for the first table using the IN operator, but I'll have to create an expression for the second table to achieve the NOT IN effect.

To hold the list of values for my IN filter, I create a hidden report parameter with multi-values, without available values, and with a list of default values that includes Bikes and Components, like this:

Of course, I don't need to manually populate the default values. I could use a query instead, but I think you get the idea.

Using the IN Operator

This step is not really required to set up the NOT IN filter, but I have included it as another example of working with the IN operator to supplement my first post in this series. In my report, I want the first table to include the values that are defined in the parameter, so I add a filter to the tablix like this:

Expression	Data Type	Operator	Value
[Category]	Text	In	=Parameters!FilterList.Value

You can also use [@FilterList] as the value if you want to type in a simple expression into the Value field. If you use the Expression Editor to set up the Value expression, and double-click on FilterList in the Parameters category, be sure to remove the (0) from the end of the expression so that the IN operator is comparing Category to all values in the parameter array, not just the first value.

Creating a NOT IN Filter

For the second table, I also add a filter, but I set it up differently. There is no such thing as a NOT IN operator, so I need to get creative. I need to come up with an expression that evaluates as True or False, and then set that up as my Value in the filter definition. Then I’ll set the Expression of the filter to True and use an = operator. The filter will keep rows where Value is True and exclude rows which cause Value to be False.

Expression	Data Type	Operator	Value
=True	Boolean	=	=Iif(InStr(Join(Parameters!FilterList.Value,","), Fields!Category.Value)=0,True,False)

For Expression, note that I have =True and not just True. This is important because True by itself will be interpreted as a string instead of a Boolean data type.

I use the InStr function in the value to compare the current row’s Category to the FilterList which I convert from an array to a comma-delimited string by using the Join function. The InStr function returns 0 if the category is not found, which is the equivalent of NOT IN. Thus, I have the expression return a True – which keeps the row in the second table.

Checking the Results

Here’s the final report:

If you’d like to have a look at how I set this up, you can download the RDL.

Have you found another way to solve NOT IN? Let me know!

Working with Reporting Services Filters – Part 3: The TOP and BOTTOM Operators

smisner — Fri, 07 Jan 2011 23:07:00 GMT

Thus far in this series, I have described using the IN operator and the LIKE operator. Today, I’ll continue the series by reviewing the TOP and BOTTOM operators.

Today, I happened to be working on an example of using the TOP N operator and was not successful on my first try because the behavior is just a bit different than we find when using an “equals” comparison as I described in my first post in this series. In my example, I wanted to display a list of the top 5 resellers in the United States for AdventureWorks, but I wanted it based on a filter. I started with a hard-coded filter like this:

Expression	Data Type	Operator	Value
[ResellerSalesAmount]	Float	Top N	5

And received the following error:

A filter value in the filter for tablix 'Tablix1' specifies a data type that is not supported by the 'TopN' operator. Verify that the data type for each filter value is Integer.

Well, that puzzled me. Did I really have to convert ResellerSalesAmount to an integer to use the Top N operator?

Just for kicks, I switched to the Top % operator like this:

Expression	Data Type	Operator	Value
[ResellerSalesAmount]	Float	Top %	50

This time, I got exactly the results I expected – I had a total of 10 records in my dataset results, so 50% of that should yield 5 rows in my tablix.

So thinking about the problem with Top N some more, I switched the Value to an expression, like this:

Expression	Data Type	Operator	Value
[ResellerSalesAmount]	Float	Top N	=5

And it worked!

So the value for Top N or Top % must reflect a number to plug into the calculation, such as Top 5 or Top 50%, and the expression is the basis for determining what’s in that group. In other words, Reporting Services will sort the rows by the expression – ResellerSalesAmount in this case – in descending order, and then filter out everything except the topmost rows based on the operator you specify.

The curious thing is that, if you’re going to hard-code the value, you must enter the value for Top N with an equal sign in front of the integer, but you can omit the equal sign when entering a hard-coded value for Top %. This experience is why working with Reporting Services filters is not always intuitive!

When you use a report parameter to set the value, you won’t have this problem. Just be sure that the data type of the report parameter is set to Integer. Jessica Moss has an example of using a Top N filter in a tablix which you can view here.

Working with Bottom N and Bottom % works similarly. You just provide a number for N or for the percentage and Reporting Services works from the bottom up to determine which rows are kept and which are excluded.

Working with Reporting Services Filters – Part 2: The LIKE Operator

smisner — Thu, 30 Dec 2010 18:06:00 GMT

In the first post of this series, I introduced the use of filters within the report rather than in the query. I included a list of filter operators, and then focused on the use of the IN operator. As I mentioned in the previous post, the use of some of these operators is not obvious, so I'm going to spend some time explaining them as well as describing ways that you can use report filters in Reporting Services in this series of blog posts.

Now let's look at the LIKE operator. If you write T-SQL queries, you've undoubtedly used the LIKE operator to produce a query using the % symbol as a wildcard for multiple characters like this:

select * from DimProduct where EnglishProductName like '%Silver%'

And you know that you can use the _ symbol as a wildcard for a single character like this:

select * from DimProduct
where EnglishProductName like '_L Mountain Frame - Black, 4_'

So when you encounter the LIKE operator in a Reporting Services filter, you probably expect it to work the same way. But it doesn't. You use the * symbol as a wildcard for multiple characters as shown here:

Expression	Data Type	Operator	Value
[EnglishProductName]	Text	Like	Silver

Note that you don’t have to include quotes around the string that you use for comparison.

Books Online has an example of using the % symbol as a wildcard for a single character, but I have not been able to successfully use this wildcard. If anyone has a working example, I’d love to see it!

UPDATE: Thanks to loyal reader Pravin, I can confirm that the ? character is the wildcard for the single character.

Uninstalling Reporting Server 2008 on Windows Server 2008

rodak.p@gmail.com — Thu, 23 Dec 2010 00:03:18 GMT

Ha. I had quite disputable pleasure of installing and reinstalling and reinstalling and reinstalling – I think about 5 times before it worked – Reporting Server 2008 on Windows Server with the same year number in name.

During my struggle I came across an error which seems to be not quite unfamiliar to some more unfortunate developers and admins who happen to uninstall SSRS 2008 from the server. I had the SSRS 2008 installed as named instance, SQL2008. I wanted to uninstall the server and install it to default instance. And this is when it bit me – not the first time and not the last that day . The setup complained that it couldn’t access a DLL:

Error message:

TITLE: Microsoft SQL Server 2008 Setup

------------------------------

The following error has occurred:

Access to the path 'C:\Windows\SysWOW64\perf-ReportServer$SQL2008-rsctr.dll' is denied.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.0.1600.22&EvtType=0x60797DC7%25400x84E8D3C0

------------------------------

BUTTONS:

OK

This is a screenshot that shows the above error:

This issue seems to have a bit of literature dedicated to it and even seemingly a KB article http://support.microsoft.com/kb/956173 and a similar Connect item: http://connect.microsoft.com/SQLServer/feedback/details/363653/error-messages-when-upgrading-from-sql-2008-rc0-to-rtm

The article describes issue as following:

When you try to uninstall Microsoft SQL Server 2008 Reporting Services from the server, you may receive the following error message:

An error has occurred:
Access to the path 'Drive_Letter:\WINDOWS\system32\perf-ReportServer-rsctr.dll' is denied.

Note Drive_Letter refers to the disc drive into which the SQL Server installation media is inserted.

In my case, the Note was not true; the error pointed to a dll that was located in Windows folder on C:\, not where the installation media were.

Despite this difference I tried to identify any processes that might be keeping lock on the dll. I downloaded Sysinternals process explorer and ran it to find any processes I could stop. Unfortunately, there was no such process.

I tried to rerun the installation, but it failed at the same step.

Eventually I decided to remove the dll before the setup was executed. I changed name of the dll to be able to restore it in case of some issues. Interestingly, Windows let me do it, which means that indeed, it was not locked by any process.

I ran the setup and this time it uninstalled the instance without any problems:

To summarize my experience I should say – be very careful, don’t leave any leftovers after uninstallation – remove/rename any folders that are left after setup has finished. For some reason, setup doesn’t remove folders and certain files. Installation on Windows Server 2008 requires more attention than on Windows 2003 because of the changed security model, some actions can be executed only by administrator in elevated execution mode. In general, you have to get used to UAC and a bit different experience than with Windows Server 2003.

Technorati Tags: SQL Server 2008,Windows Server 2008,SRS,Reporting Services