Tutorial

You are currently browsing articles tagged Tutorial.

You all know that report parameters can be populated through a query, right?  And multi-value parameters get this magical (Select All) item as first one in the dropdown list.  I used the word magical in the previous sentence because this (Select All) item is not a real one.  It’s a bit like a fata morgana: you can click it but it doesn’t really get selected.  It only appears that way in the dropdown.  But you can’t write an expression that tests if the (Select All) item is selected because it doesn’t really exist in the array.  The only thing it does is it selects all items when it gets clicked.

Little side note: as usual there’s a workaround to check if all items are selected.  I’ll mention the details on that later.

Sometimes people want to avoid using this functionality and implement their own "All" item.  Even more so in the case of a single-value parameter which doesn’t get an auto-generated (Select All) item.  And that’s what this article is all about!

Setting The Scene

Once again I’ll use the AdventureWorksDW2012 free sample database and write some product-related queries.

The Parameter Query

Let’s use the following query in a dataset called ProductCategory to populate a multi-value parameter called ProductCategory, type integer:

select ProductCategoryKey ID
    , EnglishProductCategoryName ProductCategory
from dbo.DimProductCategory

ID is used as Value field while ProductCategory serves fine as Label.  To have all values selected by default we can use that same dataset:

Selecting all parameter values by default

When rendering the report we now indeed see a dropdown list with a (Select All) item:

image

To come back to the workaround mentioned in the intro: you can compare the count of the selected items array with the count of the whole dataset.  When equal then all items are selected.  Here’s what that looks like in an expression:

=IIF(Parameters!ProductCategory.Count = COUNT(1, "ProductCategory")
    , "all selected", "not all selected")

Fetching Some Data

Like in any regular report we’d like to display some data.  Let’s keep it simple and show a list of product subcategories based on the selected categories:

select cat.EnglishProductCategoryName ProductCategory
    , sub.EnglishProductSubcategoryName ProductSubcategory
from dbo.DimProductSubcategory sub
inner join dbo.DimProductCategory cat
    on cat.ProductCategoryKey = sub.ProductCategoryKey
where cat.ProductCategoryKey in (@ProductCategory)

Adding an "<All>" item to the parameter

The first step in setting up a custom "<All>" item is adding a simple UNION ALL to the query that fetches the parameter list.  That goes like this:

select ProductCategoryKey ID
    , EnglishProductCategoryName ProductCategory
from dbo.DimProductCategory
UNION ALL
select -1, '<All>'
order by ProductCategory asc

I’m using -1 as ID for the "All" record because I know -1 will never exist in the database.  And I’ve included < > characters in the "All" label for two reasons: to nicely distinguish it from the other items as being a bit special and it ends up on top of the list when sorted alphabetically!  Neat huh?!

This also changes how you set the default parameter selection to "All": just set it to –1:

Setting the parameter default selection to the custom "All" item

Taking the "<All>" selection into account

Alright, the next step is taking the "All" parameter item into account in the dataset that retrieves the subcategories.  Here’s what the WHERE clause will need to get changed to:

where cat.ProductCategoryKey in (@ProductCategory)
    or -1 in (@ProductCategory)

In case of a single-value parameter there’s no need to use the IN operator.  For those situations the WHERE clause can be simplified to this:

where cat.ProductCategoryKey = @ProductCategory
    or @ProductCategory = -1

Detecting if "All" is selected in an expression

The expression to check if "All" is selected also changes, we no longer need to use the COUNT function.  Instead, we can use the Array.IndexOf VB function:

=IIF(Array.IndexOf(Parameters!ProductCategory.Value, -1) > -1
    , "all selected", "not all selected")

Checking if the "All" item is selected in a multi-valued parameter

How does this work?  Because ProductCategory is a multi-value parameter, its .Value property returns an array of selected items.  The second parameter of IndexOf is the value we’d like to search for.  As our report parameter type is integer, this needs to be an integer as well.

When the item is not found in the array IndexOf returns -1, otherwise it returns the zero-based index position of the item.

Of course, in case of a single-value parameter this check can be simplified to the following:

=IIF(Parameters!ProductCategory.Value = -1
    , "all selected", "not all selected")

Conclusion

This article demonstrates how to implement a custom "All" item for both single and multi-valued report parameters meant for filtering data.  It also shows how these values can be used in report expressions.

Have fun!

V.

Share

Tags: , , ,

About half a year ago I wrote an article in which I explained how you can get newsletter-style reports implemented which work in every renderer (except just the image ones), I called that article Creating Multiple-Column Reports.  And last week that article received an interesting comment, a comment which requires a bit more than just a couple of lines in reply.  So I decided to write an article instead!

I’ll be using the same technique as in my previous article but the output (and part of the technique) will be different.  So I will not go into all detail on the parts that overlap.  If something in this article isn’t clear, please have a read through my previous article first.

The resulting RDL can be downloaded from my Skydrive.

The Scenario

Just like in my previous article the data should get rendered over multiple columns, in the example I’ll implement two columns but the technique can be used easily for more columns as well.  However, the difference is this: instead of spreading the data horizontally, from left to right, it should spread vertically, from top to bottom!

The Query

Let’s start with a really simple query:

select DP.ProductKey, DP.EnglishProductName
    , COUNT(*) over () RecordCount
from dbo.DimProduct DP

When executed against the AdventureWorksDW2012 database it gives something like this:

Output of my simple query

What you see is a list of products.  The last column, RecordCount, has been added by using the OVER clause and represents, guess what…, a record count.  It will become clear later on why this is needed.  In case you’re not familiar with the trick with the OVER clause, have a look at this article: Aggregating Data With The OVER Clause.

The Tablix

Start by adding an empty Table to the report and remove one of the columns.  Also remove the header line and the default (Details) Row Group:

Empty table without row group

Connect the table to the dataset by setting the DataSetName property.

Nest another Table inside the left cell of the main table.  This is explained in detail in my other article, if needed.

Nesting a tablix inside another tablix

Open up the properties of the nested tablix and switch to the Filters page.

Filtering the tablix to show only the first 50% of records

The expression in the Value box is this:

=Fields!RecordCount.Value / 2

As you can see, we’re using the count of records to decide which records should be shown.  As we want half of them, we’re dividing by two.  This also assumes that the dataset contains a field with an increasing number.  Out of convenience, I used the ProductKey from the DimProduct table.

That’s the left column completed.  Now select the nested tablix and copy/paste it into the remaining empty cell on the right of the main tablix.  Open up its properties and switch to the Filters page.

Filtering the remaining 50%

Change the Operator as shown in the screenshot, > instead of <=.

Let’s render that report!

Data rendered in two columns, top down!

Looks good doesn’t it?  We’ve got 606 records and the first item in the second column is 304!

Conclusion

In this spin-off on my previous article on rendering data over multiple columns I have demonstrated a method which can be used to render the data top-down and spread over two columns.

Have fun!

Valentino.

Share

Tags: , , ,

Unfiltered Coffee - http://www.flickr.com/photos/derektor/92480692/In this article I’ll be describing a method which you can use to filter data in your Reporting Services reports without making any changes to the dataset query.

To get started, I’ll first imagine I’m being interviewed.  (Yeah, I like that.)

Q: You mentioned “without making any changes to the dataset query”.  So you’ll be filtering on the SSRS side then?

A: (Darn, I think she’s onto me.)  Well, yes.

Q: Would you recommend this method?

A: (Yep, she is.) Well, erm, no.  For performance reasons it’s much better to filter on the database server side.  You don’t waste any network bandwidth and database servers are specialized in filtering data.

Q: Then why are you writing an article about filtering on the report side?

A: Well, because sometimes you don’t have another option.  Not all data sources are as flexible as SQL Server, and SSRS supports many different providers.  In some occasions, the DBA that supports exotic data source X gives you a command, similar to a stored procedure call, that returns the data for your report.  And you don’t get any say on how that gets implemented, you’re just told to use that command.  Unfortunately that command returns items A-Z while the report has an item filter.  In those scenarios, it’s interesting if you’d be able to filter on the report side.

Q: Let’s say I would like to filter on the database server side, how would I do that?

A: Just use the WHERE clause in your query.

Alright, enough intro, time to start the article!

To build the report I’ll be using SQL Server 2012, more precisely:

Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

I’ve made the RDL available for download from my SkyDrive.

The Scenario

You’ve been asked to develop a report that shows a list of products, grouped by category.  The report needs to take a filter on product category into account.  The values on which you need to filter are stored in a configuration table.  You’ve been given a query which for some mystical reason can’t be modified.

Weird enough?  Okay, let’s get that implemented then!

The Report

Let’s first set up a simple report with a dataset and a table.  The table groups the products on category:

A Grouped Table

In Preview it would look like this:

List of products grouped on category

The dataset used in the report above is called ProductList and uses the following query:

select DPC.EnglishProductCategoryName ProductCategoryName
    , DPS.EnglishProductSubcategoryName ProductSubcategoryName
    , DP.EnglishProductName ProductName
from dbo.DimProduct DP
inner join dbo.DimProductSubcategory DPS
    on DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey
inner join dbo.DimProductCategory DPC
    on DPC.ProductCategoryKey = DPS.ProductCategoryKey
order by DPC.EnglishProductCategoryName, DPS.EnglishProductSubcategoryName
    , DP.EnglishProductName;

Retrieving The Filter Configuration

So now we need to filter this report based on configuration stored in a table.  That means we’ll need an additional dataset, let’s use the following query and call it CategoryFilter:

select 'Category' FilterName, 'Components' FilterValue
union all select 'Category', 'Clothing'

(Sure, I’ve hardcoded the configuration data but for scenario’s sake, imagine it’s coming from a table.)

Here’s what the result looks like:

The Category Filter

The dataset contains two fields: the name of the filter and a value on which to filter.  Each value on which we need to filter has its own record.  Having this FilterName field in the table means that it can be re-used for several different filters, if needed.  Your dataset would then use a WHERE FilterName = ‘YourFilter’ clause.

Now, one crucial question remains: how can we use this dataset to filter the data from the other dataset?

Depending on your requirements, there are several possibilities. If you’re sure none of your data regions require the data that will get filtered out, you can use the Filter options on the Dataset.  Alternatively you can use the Filter options on your data region.  In any case, those filters all use the same interface, so implementation-wise it’s all the same.

I’ll implement the filter on the tablix, this gives me the opportunity to show how to get to the properties (dataset is easier).

Okay, so how do we implement a filter on a tablix?

First select the Tablix and right-click the grey square in the top left corner:

Opening the Tablix Properties

Select Tablix Properties and switch to the Filters page.  Click the Add button to add a filter.  As you can see, filters support expressions.  That’s a good thing!

In the Expression combobox, select the field on which you want to filter the data.

The Operator that fits with our requirements should be one that supports multiple values.  In case you’re wondering why, take a good look at our CategoryFilter dataset mentioned earlier.  We want to filter on both Components and Clothing.  So the default equals sign is not really what we’re after, the IN operator will serve much better.

The only remaining empty textbox is Value.  How can we get the values from our filter dataset into this textbox?  In any case, we’ll need to use an expression so click the little fx button next to the Value textbox.

Adding a Filter to the Tablix

As you’re probably aware, a data region can only be linked to one dataset.  Which means we can’t say:

=Fields!FilterValue.Value

because FilterValue doesn’t exist in the dataset that’s linked to the tablix.

So how can we implement a filter based on data from another dataset?  Once again we’ve got several options.  Let’s look into them!

Option One: Report Parameter

If we’d set up a Report Parameter, we could reference that, right?  Let’s close the properties popup for now (click OK) and create a new report parameter:

Adding a report parameter

Our parameter is called CategoryFilter, its type is Text and it should allow multiple values.  We’ll also set visibility to Internal because it doesn’t need to get exposed in any way.

The parameter’s default values are the values retrieved by the CategoryFilter dataset, so switch to the Default Values page and specify the dataset as shown in the screenshot:

Retrieving the default values from a query

With the parameter created, switch back to the Filters page on the Tablix Properties and enter the following expression:

=Parameters!CategoryFilter.Value

If you’ve created the expression by double-clicking the parameter in the Values box, don’t forget to remove the (0) or your data will get filtered on the first item only.

Click OK and render the report:

Report Preview now shows Clothing as first category

It’s working!  Accessories is no longer the first category that gets shown because it’s been filtered out.  The data is now filtered on the configuration as specified through the second dataset.

But… do we really need to set up a report parameter for this?  Well, no!  Let’s investigate option number two.

Option Two: The LookupSet Function

A couple of years ago I wrote an article to explain how you can retrieve data from another dataset through the new lookup functions.  Well, “new” at the time meant SQL Server 2008 R2.  We’re now on 2012 so the functions still exist!

The function that can help us out today is LookupSet.  Open up the Filter properties once again and replace the Value expression with the following:

=LookupSet("Category", Fields!FilterName.Value, Fields!FilterValue.Value, "CategoryFilter")

Close the popup window and preview the report.  If it runs fine, delete the report parameter and run the preview again.

Filtering the data using the LookupSet function

Yes, it still works, nice!  So we don’t need to use a report parameter to filter our data, we can use a fairly simple expression using the LookupSet function!

Option Three: The Split Function

To finalize let’s investigate a variation on the requirement.  In this variation, the filter configuration doesn’t need to come from a table.  It can just be hardcoded in the report.  Can we do that, without using an additional dataset?  Yes we can!

In the Filter properties, change the Value expression to:

="Components,Clothing"

Now render the report:

Oh no, the data is gone!

Hmm, that didn’t work well.  Ow right, the IN operator expects an array of string values and not a comma-separated value string.  Let’s see, how can we get an array out of a character-separated value string?  Actually, I’ve covered this in one of my #tsql2sday posts.  We can use the Split function:

=Split("Components,Clothing", ",")

With that expression specified, let’s render the report a last time:

Filtering the tablix using the Split function

Woohoo, it works once again!

Conclusion

In this article I’ve demonstrated three different possibilities to implement filtering on multiple values on the report side.  It is not a recommended method but in some cases it can be very useful, especially when you don’t have another choice!

Have fun!

Valentino.

Share

Tags: , , , ,

I’ve been using the SQL Server Management Studio (aka SSMS) since it was first released with SQL Server 2005.  And yet, it hasn’t stopped to surprise me.  Earlier this week I’ve discovered a feature which I will use frequently as of now!

While doing my day-time job at the customer, I have a habit of connecting to several servers each time when I open SSMS.  I always connect to them in the same order so that I can quickly locate them in the Object Explorer.

The servers I want open all the time are DEV DB server, DEV SSIS server, UAT DB server and UAT SSIS server.  The SSIS servers are needed to get quick access to the Job Agent, while the DB servers are what I use all the time to actually do my job.  To avoid confusion: on those SSIS servers, I’m connecting to the Database Engine, not the SSIS service.

So, earlier this week I was trying to find a method to easily connect to these servers with as few clicks as possible.  And guess what: I found one! (Well, otherwise I wouldn’t be writing this of course.)

In the following paragraphs I’ll describe a method which you can use to connect to several servers at once, with just two clicks!  I can tell you, it sure beats the Connect To Server popup window!

Screenshots taken using SQL Server 2012, but this should work as of 2005.

First we’ll create a group of all the servers that we’d like to connect to with just two clicks.

Open the Management Studio and switch to the Registered Servers view.  If you can’t find it, use the menu to select View > Registered Servers or hit CTRL+ALT+G on your keyboard.

Open the Registered Servers view

Right-click the Local Server Groups node located under Database Engine and select New Server Group… to create a new group.

Creating a new registered servers group

Give it a good name and click OK.

New Server Group Properties

Right-click the new group and select New Server Registration….

Enter the server credentials as appropriate.

New Server Registration

What’s important here, besides the obvious such as entering correct credentials, is the Registered server name.  The servers will be ordered alphabetically using that name, and that’s also the order in which they’ll be opened!  So if you’re like me and you want your servers to be opened in a specific order, you’ll need some naming creativity or use numbers.  In my case I’m glad that DEV orders alphabetically before UAT, which is what I want. Smile

Ow, in case you’re wondering about that server name shown in the screenshot above, the dot refers to localhost and sql2012 is the instance name.

To demonstrate that I’m not joking, I’ll now register a second server and name it “Another server”.  After clicking the Save button, here’s what the Registered Servers window displays:

"Another server" shown above "My DB Server"

As I told you, “Another server” is shown above “My DB Server” even though it was created later.

Now, what you’ve all been waiting for, how do I tell SSMS to connect to these servers?

Click number one is a right-click on the group name, My Favorite Servers in this case:

Use Object Explorer on the group name to open all servers in the group!

And click number two is the one on Object Explorer!

SSMS will now switch to the Object Explorer window and connect to the servers, in alphabetical order!

Object Explorer is connected to multiple=

How’s that for a time-saver huh?  I hope you’ll enjoy this as much as I will!

As a little extra, let’s quickly discuss one more feature of the Registered Servers window.  When you right-click a group, you also get a New Query menu item.  Clicking that will open a query window that’s connected to all the servers in the group.  You’ll be able to tell because the database dropdown in the menu reads <multiple>:

Query window connected to multiple=

And the status bar mentions the name of your server group and also <multiple>:

Query window connected to multiple=

Having a query window connected to several servers at once let’s you do interesting things, such as quickly checking what version and edition you’re running on all those servers:

Running the SELECT @@VERSION command on more than one server at once

Of course, I’m sure I don’t need to remind you but I will anyway, this means it’s actually a dangerous window as well.  In the database dropdown, you’ll get a list of all databases that are found on each server.  There’s no advanced logic used in the process of building that list: if the database name is found on all servers in the group then the name of the DB is added to the list.

You can select one of those databases and execute queries against them, just like this:

Running a query against more than one database over several servers

The status bar will now mention the actual database it’s connected to, which is actually its regular behavior.  And the messages pane will mention how many servers you’ve run the statement against.

In the case above, I created a table in two tempdbs on two servers.  Imagine that I ran a DROP TABLE instead, and I forgot that one of the servers in the group was the production server on which I didn’t want to drop that table.  So, be careful with those windows.  To avoid errors, close them as soon as you’ve finished the job for which you needed them open!

Have fun!

Valentino.

Share

Tags: , , ,

Now and then I get inspired to write about a certain topic as a result of what I encounter on the forums.  Here’s one of those moments.

The examples in this article are created using SQL Server 2012 RTM – 11.0.2100.60 (X64).  I’m also using the free AdventureWorksDW2012 sample database available at CodePlex.  But that doesn’t imply that you actually need 2012 to get this to work.  The principle explained in this article should also work on SQL Server 2008 and even 2005.

You can download the resulting RDL from my SkyDrive.

Introduction

Have you ever needed to create a report that shows a list of something spread over multiple columns?  Then you may have come across a feature called newsletter-style reports.  Excited to have found out about that feature, you started creating your report.  But alas, upon rendering the preview you discovered that it didn’t work.  Further investigation made you conclude that the feature is only supported in two specific renderers (PDF and Image).  Ouch!

In this article I’ll be showing you a method to create multiple-column reports that you can actually use with all renderers!

Setting The Scene

We’ve been asked to create a report that shows a list of product codes with each category displayed on a separate page.  The codes should be displayed in four columns.

Implementing The Report

To get these requirements implemented, we’ll be using some T-SQL skills in combination with nested tables.  So, first step is the dataset query.

The Dataset

The query retrieves only the fields needed in the report plus one additional, calculated field called DisplayColumn.  This additional field indicates the column in which the code should get displayed.  Here’s what the results look like:

List of product codes with column number by category

As you can see, the column numbering starts at 1, goes up to 4 and then starts again at 1.  It also starts at 1 again at the start of a new category.  This is needed because we’ll be grouping the data on category in the report.

Now, let me show you that query and explain a bit how the DisplayColumn is getting calculated:

declare @numberOfColumns int = 4;

select dpc.EnglishProductCategoryName, dp.ProductAlternateKey
    , (ROW_NUMBER() OVER (
        PARTITION BY dpc.EnglishProductCategoryName
        ORDER BY dp.ProductAlternateKey) + @numberOfColumns - 1) % @numberOfColumns + 1
    as DisplayColumn
from dbo.DimProduct dp
inner join dbo.DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
inner join dbo.DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey;

The main ingredient here is the ROW_NUMBER function.  According to its definition, it “returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition”.

That brings us to the OVER clause, and its PARTITION BY statement.  In our example we partition by category because we need the numbering to start over for each category.

Next up is the ORDER BY part.  We order by ProductAlternateKey, which is the product code that our report should display.  We want the codes to be ordered alphabetically so the column numbering should take that order into account.

As you can tell, I’m using some numeric trickery on the result of the ROW_NUMBER function.  The reason for that is because I’d like my numbers to start at 1 and end at 4.  If we would just do “row_number % 4” (% is the modulo operator btw), then our last column would be zero instead of 4.

Okay, that’s part one of the method explained.  Over to the visualization part.

Setting The Tables

We’re going to create a table that shows the product category in a header with the product codes spread over four columns below the header.

So, first drag a table into the design canvas and give it an additional column.

To quickly link the table with the dataset, drag the product code field into the detail cell of the first column.

Now we’re going to set up the grouping.  We’ll also be making use of nested tables and because tables cannot be nested on the detail level of a tablix, we need to set up grouping even on that detail level.  So first, open up the Group Properties of the Details level by right-clicking it in the Row Groups pane:

Opening up the Detail-level Group Properties

Click the Add button to add a group and group on the product category:

Group the Detail level on product category

We want to display a header on the category level, so we’ll need to add another group on top of this one.  Right-click the Details row group once more and add a Parent Group:

Adding a Parent Group to the Details level

Group by product category once more and activate the Add group header checkbox:

Adding parent group on product category

Here’s what our table now looks like:

Some cleaning-up to be done

We don’t need that first column and we don’t need that top row, so delete both.  Now select the four textboxes in the upper row, right-click and select Merge Cells:

Merging the header cells to prevent text getting cut off or wrapped

This way the header line gets to use all available space.  Now put the product category field into the remaining merged cell and render the report:

Grouping set up as expected

If all went well you should be seeing the four categories.  That means the grouping part is implemented.

Of course, the detail level is not yet as needed because only the first code of each category is being shown and the three other columns are still empty.

So let’s address that now!

Nesting The Tables

Currently our four detail-level cells are just plain textboxes.  But textboxes don’t allow filtering and we need filtering because the first column should show only the product codes with DisplayColumn equal to 1, second column 2, and so on.

So what we’re going to do is nest another tablix inside each of those four cells.  This nested tablix should be one-celled, so just one column and one detail-level row.  That can be done through several ways.  I’ll show you two.

To set up the first detail-level cell, drag another Table inside that first textbox (the one that currently contains the product code).  That gives us this:

Nesting a table in a table

Now select the nested table by clicking one of its cells:

Select the nested table

It may be a bit tricky so if needed, temporarily increase the height of the detail-level row to enlarge the nested table.

Now remove two columns and remove the header row of the nested table.  Then right-click the grey square in the upper-left corner and open up the Tablix Properties:

image

These are the properties of the nested table.

Switch to the Filters page, click the Add button and set the filter to DisplayColumn = 1.

Filter on DisplayColumn

Click OK to close the properties.

Now put the product code field again in that first detail-level cell and render the report:

One column done, three remaining!

One column done, three remaining!

Let’s set up the second column.  This time, drag a List into the second detail-level cell:

image

A List is in fact a one-celled tablix with a Rectangle inside.  But we don’t need that rectangle here, we need a Textbox.  So select the rectangle by clicking once inside the cell.  You can tell that the rectangle is selected by looking at the Properties window, as shown above.

Now hit the Delete button on the keyboard.  In the Properties window, you should see Rectangle changing into Text Box:

Removing the Rectangle from the List leaves us with a Text Box

Once more we’ve got a one-celled tablix.  Set up filtering on DisplayColumn = 2, using the method as explained when setting up the first column (hint: upper-left grey square).

Put the product code field inside the second detail-level cell and render the report:

Second column set up

If all went well, your second column should now display some codes as well.

Two more to go!

Setting up the remaining two columns is actually really easy.  Select the nested tablix inside the second cell by clicking the small grey square in the upper-left corner.  Then hit CTRL+C, select the third detail-level cell and hit CTRL+V.  Also paste into the fourth detail-level cell.

Now change the filtering to 3 and 4 for column three and four and render the report:

All four columns display product codes

All four columns display product codes!

What now remains is some visual cleanup and adding page breaks on the category group.  I’m only going into details on the page break.

In the Row Groups of the main table, right-click the upper group and select Group Properties.  Then switch to the Page Breaks page and check the Between each instance of a group checkbox:

Adding a page break on the category group

Render the report to see the final result:

Report implemented: product codes in four columns grouped per category!

The first page displays the product codes of the first category, spread over four columns!  The other pages contain the other categories.

Requirements implemented!

Conclusion

Even though it takes a bit of work and some tricks, it is possible to create multi-column (aka newsletter-style) reports using basic SSRS components whilst still supporting all renderers.

I hope you enjoyed this article, have fun!

Valentino.

References

ROW_NUMBER() function

Share

Tags: , , , ,

« Older entries

© 2008-2017 BI: Beer Intelligence? All Rights Reserved