Category Archives: SSRS

SSRS 2008 R2 LookUp and LookUpSet Functions

With the release of R2 came a couple of pretty nifty new expressions in SSRS. The LookUp function allows us to perform a look up against a separate data set in our report using a field in our source data set where there is a 1 to 1 relationship. Likewise, the LookUpSet function allows you to do a look up values in a separate data set where there is a 1 to many relationship.

The LookUp and LookUpSet functions are useful when you cannot write a query to join our two data sets together. So maybe we have a shared data source in our report that returns some data that we need to join to an embedded data set in our report. We could use the LookUp and/or LookUpSet functions to bring those two data sets together within our report.

Let’s walk through a couple examples so we can see how to use these new functions. First, lets check out the LookUp function. I’ve got a shared data set in my Report Project that returns the total Reseller sales but in my report I have an embedded data set that has all my Resellers names. I’ll use the LookUp function to bring those two data sets together.

Our expression to look up the TotalSales from our shared data set looks like this:

=LookUp(Fields!ResellerKey.Value,Fields!ResellerKey.Value,Fields!TotalSales.Value,"ResellerSales")

LookUp

Using the LookUp function, I’m able to look up the Total Sales Amount from the destination data set and bring it together with our source data set.

LookUp Report Preview

Now lets take a look at the LookUpSet function. At first glance, it appears to work exactly the same as the LookUp function.

=LookUpSet(Fields!CustomerKey.Value,Fields!CustomerKey.Value,Fields!OrderNumber.Value, "CustomerOrders")

But if we run our report with this expression, we’re going to get an error.

LookUpSet Report Preview with error

This is because the LookUpSet function brings back a collection of results. We cannot simply display the results in a textbox in our tablix. We need to concatenate the results into a string. To do this, we are going to use the Join function with our LookUpSet function.

=Join(LookUpSet(Fields!CustomerKey.Value,Fields!CustomerKey.Value,Fields!OrderNumber.Value, "CustomerOrders"),"," + vbcrlf)

The Join function takes a collection of objects and delimits them with our specified delimiter. In this case, our delimiter is a comma with the VB carriage return line feed (vbcrlf). Using the Join and LookUpSet functions, we are able to look up all the order numbers from the destination data set and bring those into the same tablix as our source data set.

LookUpSet Report Preview

Pretty cool, huh?

Use Date Picker Control with MDX Based Reports

If you’ve done a couple cube based reports, you’ve run into the issue of the dates being in string format. Because the dates are strings inside your cube, you can’t use the neat little date picker control reporting services gives you for datetime parameters. While you as a developer may understand this issue, your "tech savvy" end users may not. Fret no more. Here is a quick and easy way to use the built in date picker control for parameters in your MDX reports.

1) In the Report Data pane of BIDS, open the parameter properties for the parameter(s) you are using to specify a date or begin and end dates and change the data type to datetime. Change available values to None.

2) Go ahead and delete any hidden datasets that populate the drop down lists for your date parameters. You won’t need those with the date picker control.

3) In your data set that populates your report, open the properties, and in the parameters view, you want to use an expression on the parameter value field. So instead of the parameter value containing something like "=Parameters!DateDate.Value", you want to change it to something like:

="[Date].[Date].&[" + Format((Parameters!DateDate.Value), "MM-dd-yyyy") + "]"

Keep in mind that you’ll need to perform step 3 on any data sets that use your date parameters.

The key is to format the expression in such a way that it looks exactly like the members of your date dimension. Once you do that, click preview and you have the beautiful and end-user friendly date pickers in your cube based reports!

You can also go back to your parameters to specify default values using today and/or dateadd (ex. "=dateadd("M",-12,today)").

One of the downsides to using the date picker control with reports using your cube is a data source is that if the end user selects a date in the date picker that is not in your cube, your report is going to blow up, so just be aware of that.

If you have any questions, feel free to leave a comment!

Creating Stepped Reports with SSRS

One of the nice things about a stepped report is that the child groups are displayed beneath the parent groups but within the same column as the parent group. In the next several steps, I’m going to walk through creating a basic stepped report using the Tablix data region. I used the Matrix data region template for this example.

First I created a report and dragged in a Matrix data region into the design area. I used the Adventure Works Cube and a simple data set with the Product hierarchy, Reseller Sales Amount and Calendar Year. Here is the query I used for the main dataset in case you would like to follow along.

SELECT NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS * [Date].[Calendar].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I put Product in the row group, Calendar Year on the column group, and Reseller Sales Amount in the details.

Capture1

Next we need to create a parent group for to display our Subcategory. To create a parent group, in the Row Groups pane, right-click the Product group, select Add Group, then select Parent Group.

Capture2

Select Subcategory as the field you want to group by. Make sure to check the Add group header check box.

Capture3

After creating the new group for Subcategory, right-click the Subcategory group in the Row Groups pane, and create a new parent group for the Category field. Again, check the box next to Add group header.

At this point, you matrix should look like this.

SSRS Stepped report layout

But since we are creating a stepped style report, we want our parent groups in the same column as our detail group. So we can delete the columns that contain Category and Subcategory.

Customize your SSRS stepped report layout

Then add Category and Subcategory to the rows above Product so that your Matrix looks like what you see below. Also, don’t forget to add Reseller Sales Amount in the cells next to Category and Subcategory.

Consolidate the groups in your SSRS stepped report

Now we’re almost done. To create the stepped formatting, right-click the cell containing Subcategory and select Text Box properties. In the Alignment pane under Padding options, set the Left padding to 15pt. Then click OK.

Create dynamic padding for your SSRS stepped report

Then right-click the cell containing the Product field and click Text Box properties. This time in the Alignment pane, set the Left Padding to 30pt.

Modify the SSRS stepped report group properties

The last thing we have to do is create that nice, fancy, collapsible drill-down action. To do that, right click the Product row group in the Row Groups pane and navigate to the Visibility pane. When the report is initially run, we want the Product row group set to Hide. Check the check box next to Display can be toggled by this report item. Since we want the Product row group to be toggled by its parent group, select the Subcategory text box in the drop down list.

Modify the SSRS stepped report group properties

We also should set the visibility settings of the Subcategory row group so that it is also hid when the report is initially run. It should be toggled by the Category row group.

And after adding some nifty back ground colors to make our report a little easier to read and some bold text on the header row, you are done! We now have a simple, yet easy to read, stepped report that will allow the end user to drill down into the child group rows to view the data at a more granular level.

The final Reporting Services SSRS stepped report example

Recording Available for Reporting on a Cube with SSRS 2008 Webinar

Thanks to all of the attendees that showed up for my webinar yesterday. I had a great time speaking and I hope everyone enjoyed the webinar and maybe even learned something.

If you missed the webinar, Reporting on a Cube with SSRS 2008, have no fear! There is a recording available for you to watch. Just head over to the Pragmatic Works CMS site and create a free account. Not only will you be able to watch my webinar from yesterday, you’ll also be able to check out tons of other great webinars from some really sharp speakers. So head over to the Pragmatic Works CMS site, create a free account, and watch my webinar here.

Free Webinar Tomorrow (7/21) @ 11 am EST: Reporting on a Cube with SSRS 2008

Tomorrow (7/21) I’ll be speaking on reporting on an SSAS cube with SSRS 2008 at 11 a.m. EST. It’s a completely free event hosted by Pragmatic Works.

In this session I’ll give you an introduction to reporting on a cube with SQL Server Reporting Services (SSRS). You’ll learn about some of the advantages of using your cube as a source for your SSRS reports, how to build reports based on your cube with minimal knowledge of MDX, as well as a few tricks you can use to increase the usability of your reports.

It’s completely free to attend and you can do it during your lunch break. I’ll also be taking questions so don’t forget to get registered!

Help Me Help You With Free SSIS/SSRS Training

As you may have noticed, Pragmatic Works is once again running another promotion. This promotion is a little different though in that it’s probably the best deal we’ve ever had on our software/training bundle.

If your company purchases at least a single, lonely license to any of our incredible software, inlcluding Task Factory, BI Documenter, BI xPress, and/or DTS xChange, and you get a free seat in either our SSIS or SSRS class. The really great thing about these classes is that you can attend the class remotely via GoToMeeting. The classes are about 4 hours a day for 4 days which allows you to perform your daily duties and still attend the training class.

My personal favorite bundle is the BI xPress/SSIS Training Class bundle. If you’re interesting in learning SSIS, this would be the perfect combo to get you ramped up well past beginner level and headed towards an advanced level. Our SSIS Training Class starts out at a beginner level and progresses all the way through some advanced SSIS concepts. This plays right along with the strengths of BI xPress. Once you take our SSIS Training Class and learn to use BI xPress, you’ll be developing and implimenting SSIS packages at an incredible pace.

I’d highly suggest any of you at least consider this deal before it expires at the end of this month! Help me help you! To get more information, contact Sales@PragmaticWorks.com. You won’t regret it!