Conditional Page break in SSRS

Conditional page breaks in SSRS are always something developer needs to work on in order to get it done. This is especially true when you need to render the same report in PDF.

Recently I need to implement the same and did that with a quick trick. Here is the sample SELECT I am using to demonstrate the same.

select 'Record 1' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 2' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 3' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 4' as record, 1 as PageBreak, 2 as grp
UNION ALL
select 'Record 5' as record, 0 as PageBreak, 3 as grp
UNION ALL
select 'Record 6' as record, 0 as PageBreak, 3 as grp
UNION ALL
select 'Record 7' as record, 1 as PageBreak, 4 as grp
UNION ALL
select 'Record 8' as record, 0 as PageBreak, 5 as grp

"Record" column is just to show unique records. The most important column here is "PageBreak", which is setting the conditional page break with "1". That means whenever there is "1" in "pagebreak" column you need a page break otherwise keep on displaying the records on same page.

The other very important column is "grp". Well, you really don't need this column and still will be able to implement conditional page break functionality. But then SSRS will club all the records which requires page break into 1 grouping set, probably in the end with this sample data. So, in order to maintain the sequence of records we need to add a grouping column which will create a new group whenever it finds a different value in "pagebreak" column.

Now, lets move on to SSRS report. These steps will work in SSRS 2008. I am not adding standard steps of creating a data source and data set. I know you are all smart guys...

Simple enough, please follow these steps.

1. Add a table with 2 columns, record and pagebreak.
2. Now run the report and you will see all the records are coming in same page.
3. Next, add a parent group to this row, parent to "Details" group in "Row Groups". Please check "Image 1".
4. In the group expression select the "grp" column and the sorting, select "grp" in Sort by. Please check "Image 2".
5. MOST Important, select "Between each instance of a group" of the newly create group under "Page Break" tab. Please check "Image 3".

Yep, that's it, run the report now. And you will see 3 records on page 1, 1 record on page 2, 2 records on page 3 and 1 record on page 4 and page 5.

Image 1

Image 2

Image 3

There are also other ways of doing the same, but I find this one very easy to implement.

Don't forget to hide new group by just minimizing the width and you are good to go. Try to render the same in PDF, and it works like a charm....

- Mohit Nayyar

BI and Reporting enhancements in Microsoft SQL Server 2008 "Katmai"

Change Data Capture
Change Data Capture (CDC) is a really interesting feature in Katmai using which we can focus on specific business requirement to capture changed data. That means this component captures all changes asynchronously and then exposes the changes through T-SQL based on specific requirements.

The provision to capture schema-changes is also very useful in this component.



Merge SQL Statement
Truly fantastic and really useful........

So how many times you have used "IF EXISTS" to make sure that you insert new row otherwise update the existing row.....yes I am talking about UPSERT...

This MERGE statement takes care of this scenario and check for existing rows to make updates and inserts new rows in the table.

MERGE INTO FactTable F
USING TransactionTable T ON T.OrderID = F.OrderID
WHEN MATCHED THEN UPDATE
SET F.Quantity = T.Quantity
WHEN NOT MATCHED THEN INSERT (OrderID, Quantity)
VALUES (T.OrderID, T.Quantity)



C# support for SSIS scripting components



Doesn't require IIS for SSRS
Yeah, thats the good part, we don't need IIS anymore to load SSRS reports with SQL Server 2008. This is the best for a developer like me.......



Grouping Sets
So how many times you have asked by business users to give specific grouping totals, people in sales only ask for product level totals, management looks for employee-product totals for bonus, finance needs total on 10 different things........

Well now with this new feature we can define multiple grouping option within the same query....that means instead of writing multiple group by queries and then combining the result with UNION ALL, we can have just one query with multiple grouping sets



Tablix – This release of SQL Server introduces a new report component called tablix, technically it’s a combination of existing table and matrix in SSRS. We just need to change few basic properties and this will do the job, so no more patchwork required to achieve stacked columns, pivot and more than 1 subtotals.



RDL Object Model – Good news for developers who wants to write/extend reports programmatically.

Dundas RS controls – As we know that Microsoft recently acquired Dundas chart, so this release of Reporting Services will contain the full power of dundas, an absolutely must have in reporting chart world.



Soft Artisans Office Writer technology – Again, This acquisition will allow users to embed their reports in Microsoft Word and Excel documents. Talk to you manager, he will love this.



Export to Microsoft Word




SSIS Lookup "TxLookup" in SQL Server 2008
Microsoft is really working on this component, and now we can lookup into any data source, including other SSIS packages, great news. Along with this TxLookup also works on "pre-charge" query, currently we have cache-miss queries in SQL Server 2005 Integration Services.



SharePoint user interface to manage SSRS reports



Office based report authoring
Now we can used report authoring tool, which allow us to author our reports using excel or word based tools. That means we can put tables, formulas, and also apply formatting too.




FYI - Microsoft entry into MDM world
Microsoft have also purchased Stratature recently, which provides Master Data Management solutions. MDM solution is really useful and must have for organizations having multiple businesses. Read more on MDM at MSDN The What, Why, and How of Master Data Management