Category Archives: SSRS

SQLRally Voting Opens to the Community

PASS SQLRally 2012 voting is now open! There’s tons of great sessions out there and many of them have been submitted by some of my good friends here at Pragmatic Works. While voting look for great sessions presented by Pragmatic Works experts like

  • Adam Jorgensen
  • Chris Albrektson
  • Gareth Swanepol
  • Dan Clark
  • Brad Schacht
  • and me, too!

Make sure you head over to SQLRally.com and cast your vote! You can vote for as many sessions as you want but you can only vote once, so make your selections wisely! Yours truly has also submitted a few sessions that are currently up for vote! Here are my sessions up for vote and their descriptions.

Zero to Cube – Fast Track to Analytics
This session has been updated for SQL Server 2012. It is consistently a top session from previous events like SQLRally and PASS Summit. Come interact with your speaker and audience like you never have before. This session is 100% demo, packed full of fun and will get you up and running on Analysis Services 2008R2 and 2012 right away!

SSIS For Your Data Warehouse
The ability of SQL Server Integration Services to easily bring together multiple data sources makes it the perfect ETL tool for loading your data warehouse. Loading your data warehouse quickly and efficiently, however, is not always easy. In this session, we’ll explore some advanced techniques for loading your data warehouse and help you overcome some of the learning curve associated with SSIS. The topics discussed will include handling late arriving facts, loading slowly changing dimensions efficiently, and more.

SSIS Unleashed: Expressions and Variables Edition
One of the most powerful aspects of SSIS is the ability to create dynamic and powerful packages utilizing package variables and the SSIS expression language. Unfortunately, the SSIS expression language is also one of the more difficult features to learn regarding SSIS. In this session we will walk through the syntax of the SSIS expression language, common uses for SSIS expressions, as well as some more advanced ways to fully realize the power of SSIS.

Tackling the SSRS Expression Language
The SSRS expression language is one of the most powerful pieces when it comes to creating dynamic reports. Unfortunately, for reporting developers the SSRS expression language is also one of the more difficult features to use regarding SSRS. During this session we will walk through the syntax of the SSRS expression language, how and where to use the expressions, as well as some advanced ways to truly reveal the true power of your reports.

Now that you’re informed, go cast your vote!

BI Documenter 4.2 Released

I’ve been meaning to blog this for a while but since I’ve been on the road for the past month I’ve been pretty busy. This past January, BI Documenter version 4.2 was released by Pragmatic Works. With it comes some pretty cool upgrades and some new functionality.

One of the biggest improvements was the upgrade to the SQL Server Instance Snapshot functionality. It is now possible to include in your documentation Backup Devices, SQL Agent Jobs, and Server Configurations. Personally, I’ve been waiting for the ability to document SQL Agent Jobs so I’m very excited about this release. There’s also many other improvements to the SQL Server Instance Snapshot functionality, so head over to PragmaticWorks.com to check out the other improvements.

Theres also a couple new enhancements that are really nice. Now you have the option to output your documentation in MS Help 2 format. Also, BI Documenter can now document Analysis Services Mining Structures.

With the ability to document SQL Servers, SSIS packages, SSRS reports, and SSAS cubes, its certainly the most encompassing SQL documentation tool out there. If you’re searching for a one stop documentation tool for your SQL Server environment, definitely check out BI Documenter.

Documenting Your SSRS Reports and Data Sources

If the organizations you’ve worked with are anything like the ones I’ve had the pleasure of working with, then they probably had or have thousands of SSRS reports spread out all over the place. And back around the time a majority of companies were gearing up to migrate to SQL 2008 from 2005, getting a grasp on the number and complexity of these reports was quite a challenge.

This is just the scenario where BI Documenter really shines. Besides being able to document your SQL databases, SSIS packages, and SSAS cube, BI Documenter can also document your SSRS reports and shared data sources, all the way down to the XML behind the scenes in case your reports are lost.

Let’s walk through creating the documentation we need to understand how many and how complex the reports are that are being considered for migration. For the sake of this example, just pretend my SSRS 2008 reports are actually 2005 ;).

Open BI Documenter, click Add a New Solution. Give the solution a name and description and then click Create Solution.

1

After clicking Next, click Add Reporting Services Server on the next screen.

2

The reports you wish to document can exist either on the SSRS server or on a SharePoint Server. In my case, my reports are on my local SSRS instance so I’ll select Native for my Server Mode. Key in the server and any credentials. Click the hyperlink at the bottom of the screen to verify BI Documenter can reach the SSRS server. Click Next.

3

Give a name and description of the Report Server and click Finish.

4

Now that we’ve add the Report Server to our documentation solution, we need to create a snapshot. On the Filter tab, check the boxes next to Reports and RS Data Sources.

5

Click Next a couple times. All we need to do now is finalize the documentation. You can output the documentation in HTML format or in a .CHM file. Select the output destination.

7

On the last tab, Reporting Objects, make sure the box is checked to document the Report Definition. This is a great tool if you reports are lost. With a little work, the documentation of the Report Definition can be used to recreate the reports. Click Next. Once the process to create the documentation is finished, click Finish.

8

Now we have a precise and detailed document outlining all the reports living on our SSRS server. I can view the .rdl’s for my reports, the parameters, data sources, and data sets. I can even view the data set queries.

910

The great thing about this feature of BI Documenter is that I can easily document all my SSRS reports as long as I know the Report Server. Head here to download the free trial.

SQL Saturday #86 Aftermath

Thanks to everyone who attended my session “Reporting on a Cube with SSRS 2008.” I had a great time at the event and I hope those who attended enjoyed the great sessions and maybe even learned a couple things.

If you’re interested, here is the link to download my slide deck and examples I used during the session. In my example, I used the Adventure Works 2008 R2 cube, so head over to Codeplex to pick up the materials.

Also, here is the link to Mosha Pasumansky’s MDX Studio Online tool that will format your MDX for you. It’s a very cool tool and very helpful. Thanks to Pam Shaw for providing this link!

SQL Saturday #86: BI Edition in Tampa, FL – Reporting on a Cube w/ SSRS 2008

Next month on November 5th is SQL Saturday #86 in Tampa, Florida. But this is no ordinary SQL Saturday. This is SQL Saturday: BI Edition! Which means this SQL Saturday is going to be even more amazing, hard core, and in your face than usual! If you’ve never been to a SQL Saturday event, let me just say that you are missing out. And if you have been to one before, then you know exactly how awesome these events are. Whether you show up for the incredible FREE training from qualified industry pro’s, the networking with fellow SQL Server fiends, or the great lunch and snacks, you’re sure to have a good time.

So before you waste anymore time, head on over to their site and get registered. Check out the extensive list of sessions and speakers and plan your day.

Yours truly will be giving a session covering Reporting on a Cube w/ SSRS 2008 at 10 a.m.  I’ll be covering some of the advantages the cube presents as a data source and how you can leverage the power of the cube with SQL Server Reporting Services 2008. So head on over to SQLSaturday.com and get registered before it’s completely full! We’re going to have a blast!

Showing an SSAS Hierarchy as Delimited String

I recently encountered a requirement for a client to display a list of all the individual stores of a ragged hierarchy (the leaf members) on an SSRS report with a bunch of different measures. But they also wanted to be able to quickly see the the ancestor members of each level above each store quickly by using a tool tip to display the different ancestors.

Turns out there are a couple nifty, little MDX functions that can allow us to do this very easily. The Ancestors and Generate functions make this possible. Here an example I put together with the Adventure Works 2008 R2 cube so you can play along.

WITH MEMBER [Measures].[Management Structure] AS
            GENERATE(
            EXCEPT(({Ancestors([Employee].[Employees].CURRENTMEMBER,6),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,5),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,4),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,3),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,2),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,1)}),[Employee].[Employees].[All]),
            [Employee].[Employees].CURRENTMEMBER.Name, "  >  ")

Select {[Measures].[Management Structure],
    [Measures].[Reseller Sales Amount],
    [Measures].[Reseller Order Quantity]} on 0,

NON EMPTY(DESCENDANTS([Employee].[Employees],10,LEAVES)) ON 1

From [Adventure Works]

Where [Employee].[Title].&[Sales Representative]

Pay attention to the calculate member [Measures].[Management Structure]. The way the Generate function works is that it takes one set and applies that set to each Member in another set. In this case, our first set is the ancestor from each level in our hierarchy of the current member, excluding the All member.

So this our first set:

EXCEPT(({Ancestors([Employee].[Employees].CURRENTMEMBER,6),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,5),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,4),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,3),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,2),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,1)}),[Employee].[Employees].[All])

Our second set is the Name property of the CurrentMember of the Employee hierarchy being displayed:
 
[Employee].[Employees].CURRENTMEMBER.Name
 
The Generate function actually also excepts a third argument: A delimiter. We can specify a delimiter. In my example, I use “  >  “ to make the string a bit easier to read. If we execute the query we can see the results of our calculated member.

10-10-2011 9-47-39 PM

Pretty cool, huh? So now that we have each employees management hierarchy in sweet, little string, we can easily display this in a report as a tool tip.

I added the tool tip to the cell in my report’s tablix that contains the employee name. Here’s the expression I used:

=REPLACE(Fields!Management_Structure.Value,">",VBCRLF)

This expression replaces the “>” with a carriage return, line feed. Here’s what our tool tip looks like:

10-10-2011 10-02-24 PM

Using this method, we can easily display the managers for each employee in a tooltip without having to waste any real estate with additional columns.

I hope somebody finds this useful. I’m out!

SSRS Expression Iif Statement Divide by Zero Error

If you’ve ever tried to use an IIF statement expression to fix an error received by dividing by zero, you probably still received the divide by zero error message. Very frustrating.

An expression like this returns an error when Sum(Fields!Beta.value) = 0:

=sum(Fields!Alpha.Value)/sum(Fields!beta.Value)

So you, being the critical thinker that you are, try the following:

=iif(sum(Fields!Beta.Value)=0,0,sum(Fields!Alpha.Value)/sum(Fields!Beta.Value))

Alas, this will not work. Even though SSRS may evaluate your expression as true, it still will attempt to resolve the false part of your expression, which gives you the divide by zero error.

To get around this infuriating issue Microsoft should have dealt with in the first place, try this:

=iif(sum(Fields!Beta.Value)=0,0,sum(Fields!Alpha.Value)/iif(sum(Fields!Beta.Value)=0,1,sum(Fields!Beta.Value))

This solution should fix any issues you have dividing by zero.