Sunday, 29 July 2012

Page Break at Nth row in SSRS Report Step by Step

We can design a SSRS report with a constraint of only Nth rows per page.

I am showing you that how to display only Nth rows in the report in this article.

Here is an example from  AdventureWorks Database.

SQL Scripts.

SELECT TOP 1000 [StateProvinceID]
  FROM [AdventureWorks].[Person].[StateProvince]

Select the Header Row of the table in the report.
Go to Properties Window.
 Under the BackgroundColor property, Select color from drop down.

Now create a group.

After creating the group go to the group properties and click on fx button.

And set the expression for group properties.

Expression :
=Ceiling((RowNumber(Nothing)) / 15)

( Note : You can put Nth number in place of 15. For example if you want to your page break affect after each 25th row than you have to put 25 in place of 15 )  

 Now, report will show 15 rows in a group. Next step is to define page break for the group. Go to page breaks tab and select page break option.

And now run the report. Below the preview of report.

Tuesday, 24 July 2012

Create SSRS report using Excel Data Source Step by Step

By creating a report using Excel as a Data Source we will follow the following steps ...

Right click on Shared Data Source folder and choose Add New Data Source. A new popup screen will open. Give the Data Source Name and Choose ODBC from the dropdown box.

Then Click on Edit button. A new screen will open. From the new screen i.e. Connection Properties go to the Use user or system data source name and from the drop down choose Excel Files .

Then Choose the Use Connection String and click on Build button. A new pop up screen will open . Click on New button.

Then click on Next button

Choose the Microsoft Excel Driver and click on Next button and then click on Finish button.

Then click on Select Workbook button and choose the file and click on OK button.

Click on OK.

Test the connection and press OK.

Click on OK.
Click on Reports folder and choose add new items and choose Report. Map your Data Source and click OK.

Right click on Dataset Folder and Write the query for the table. (I have used a very simple query .For ex.)

This is Your Report from Excel data source.