SSRS 2008 - R2 Lookup - How to use multiple datasets in a tablix

1.     Background

The purpose of this article is to provide a way of using multiple datasets in a tablix within SQL Server Reporting services 2008 R2. In all the previous versions of SQL Servers (SQL Server 2005/2008), we can use only single dataset for a table/matrix/chart.

2.     How we can use multiple datasets in a tablix?         

SQL Server Reporting Services 2008 R2 has introduced a new function called “LOOKUP”. LOOKUP function is used to retrieve the value from multiple datasets based on 1 to 1 mapping. For example if we have two datasets and both the datasets have EmpID so based on the EmpID mapping, we can retrieve the data from both the datasets.

Syntax: LOOKUP (Key value from already mapped dataset, Key value from new dataset, new value from new dataset)

3.     Create data source and datasets for the report

                    I.            Create a reporting solution in SQL Server Business Intelligence Development Studio.

                  II.            DataSource Creation:

·         For creating a data source for Report, go to Shared data sourceèright clickè Add New data source

·         Click on Editè Provide the server name and database and click ok.

               

·         Now we can see the connection string for our source server. Click ok.

                         

               III.            Dataset Creation:

·         For creating a dataset, go to Shared datasetsèAdd new dataset

·         Choose the DataSource for the dataset and provide the query:

                       

·         Click ok. Below we can see MyDataset1 has been created:

Follow the same steps for creating MyDataset2.

Query for MyDataset1:

SELECT ProgrammerName,SALARY FROM dbo.Programmer

It returns the programmer name and their salary

Query for MyDataset2:

SELECT ProgrammerName, Skill FROM dbo.Programmer

It returns the programmer name and their skills.

Here we can see the two datasets in the solution explorer:

4.     Create a tablix report with two datasets

                    I.            Create a tablix report : For creating a tablix report, Right click on Reports folderèAddèNew ItemsèReportèGive the name of the report e.g. ProgrammerDetailReport.

                  II.            Insert a tablix in the report: Right click on design surfaceèInsertèTable

 

                III.            Set the dataset for the tablix:

Let’s select MyDataset1 and click ok.

                IV.            Now we can map the columns from MyDataset1 to the tablix:

                  V.            Below is the report after formatting:

                VI.            Now if you see the property of the report for dataset. It will show two options either MyDataset1 or none. Even there is no option for writing the expression. Moreover, in all the previous version of the SQL Server Reporting services, there is no option for mapping multiple datasets to a tablix.

5.     Step by step procedure to implement multiple datasets in a single tablix

                    I.            Add one more column to the report by right click on the last columnèInsert columnèRight

                  II.            Now we can see another column in the report:

                III.            Now right click on the text box of new columnèchoose Expression:

                IV.            Use the LOOKUP function in the expression editor to map both the datasets and click Ok.

Expression:=Lookup(Fields!ProgrammerName.Value,Fields!ProgrammerName.Value,Fields!Skill.Value,"MyDataSet2")

             

                  V.            We can give the column header text as Skill. Now report will looks like below one:

6.     Conclusion

By using the LOOKUP function introduced in SSRS 2008 R2, we can map multiple datasets to a tablix.

SSRS Report - Implementing Page Break while exporting bulk data to Excel 2003

1.     Background

The purpose of this article is how to implement Page Break and grouping while exporting bulk data to excel. It provides a solution to resolve the issue with excel 2003 limitations of 65,000 records. SQL Server Reporting services by default export the report into excel 2003 format (.xls). Excel 2003 has worksheet of size 65,536 rows by 256 columns. When we export a report which has more than 65,536 records into excel. It gives the following error:

To resolve this problem, here I am going to share some useful information and my experience  regarding how to resolve the export issue with  excel ‘s 65,536  records limitation while working in SSRS. 

2.     What is Excel 2003’s 65,536 records limitation?    

                Microsoft Excel 2003 has a limitation of 65,536 records in a sheet. As we know that SQL Server Reporting Service is one of the most popular tools for creating the reports. Many      times, a developer has to create reports for bulk data. In this case, when we go to      export the report, it gives error due to limitations of excel. Microsoft Excel is the      most   flexible format to see the report. It supports dynamic reports which have drilldown, drill             through and interactive sort. We can see the report more easily in            excel as compare to other formats. So for enterprise data level, it is necessary to resolve       the issue to get advantages of Microsoft excel with SQL Server Reporting Services.

3.     Step by Step procedure to create a report

                Here I am going to create a simple tabular report with a table that has more than 65,536                records.

a.      While creating dataset for report. Check the count of the table for which we are going to create report

The table has 121710 records.

b.     Dataset for report

                     Select query that retrieve all the records from the table:

c.      Layout design

                I have created a simple report that has three columns – CalendarYear, SalesRegion and                 SalesAmount:

               

d.     Preview the Report.

                Here I run the report for all the records available in “GlobalSales_Fact” table: You can   see the report has total 2767 pages. It has 1, 21, 710 records because we are extracting        all the records from GlobalSales_Fact table.

               

 

e.      Export the Report to Excel (Default format is xls)

               

               

               

f.       While exporting, Reporting Service gives the error:

           

Excel Rendering Extension: Number of rows exceeds the maximum possible rows     per  sheet in this format: Row Requested: 121716, Max Rows: 65536

4.     How to resolve this excel records limitation issue by using Grouping and Page Break in SSRS?

               

v  Create a Row Group: Go to Row Group pane, Right click on the Details èAdd Group è Parent Group.

v  Tablix Group window comes. Here we have to put GroupBy Expression for this new Group.

                       

v  Click on the expression button and put the expression to makes group of each 65,536 record sets.

Expression:  =CInt(Ceiling(RowNumber(Nothing)/65000))

                               

                                Click ok.

v  Row Group has been created. We can see the Group1 in  the Row Group Pane:

v  In the report Layout, you can see Group1 is shown in the Report Layout:

                               

v  Right Click on the Group1 column èDelete Columns

                               

v  Delete Column window comes, Choose Delete column only and click on:

v  Now you can see the Group1 column is deleted but Group1 is still available in the report that groups every 65,500 records:

                               

v  Go to Group1 property by right click on Group1 in Row grouping pane:

v  In the Group property, go to Page Beakè Page Break Optionè Check the box for “Between each instance of a group” and “at the end of group”.

                               

v  Right click on the Tablixè go to Tablix property. Tablix property windows comes: Check “Add Page break after” and in column header, check “Repeat header columns on each page.”

v  After implementing Page Break and Grouping, run the report and export it to excel. Now Report is exported successfully. You can see a Save window for saving the excel report:

v  Save and Open the excel.

v  In the Excel sheet, Data exported into sheet1 and sheet2. Sheet1 has 65,500 records as per the grouping we implemented and sheet2 has 56710 records.

Total records in the table = Total Records in sheet1 + Total records in sheet2

                121710                     = 65,500 + 56,710

v  Output of Sheet1:

v  Output of Sheet2:

               

5.     Conclusion

By using Page Break & appropriate grouping, we can export the bulk data report from SSRS to excel 2003. This is very useful in reporting where we have to deal with bulk data e.g data warehouse.