Displaying Dynamic Columns in SSRS Report

Problem: How to display selected columns dynamically in SSRS reports.

Example: A report contains more than 30 fields. Some users want to see only 5 fields, some users 10 fields, and other may want to see 20 fields.
Solution: Add a Report Parameter having the values as the name of all the fields of dataset. Now set hidden expression for each column of the tabular report.

Here is the solution with an example:

STEP1:
Create a report with required dataset. Drag and drop table control and select dataset fields.
In my example, I have following fields in the dataset: Year, Quarter, Month, Date, Product Name, Customer Name, Sales Region, Sales Country, Order Number, Sales Amount.

STEP2:
Create a dataset dsColumns using below query:
SELECT 1 ID, 'Year' AS ColumnName UNION
SELECT 2 ID, 'Quarter' AS ColumnName UNION
SELECT 3 ID, 'Month' AS ColumnName UNION
SELECT 4 ID, 'Date' AS ColumnName UNION
SELECT 5 ID, 'Product Name' AS ColumnName UNION
SELECT 6 ID, 'Customer Name' AS ColumnName UNION
SELECT 7 ID, 'Sales Region' AS ColumnName UNION
SELECT 8 ID, 'Sales Country' AS ColumnName UNION
SELECT 9 ID, 'Order Number' AS ColumnName UNION
SELECT 10 ID,'Sales Amount' AS ColumnName


STEP3:
Create a new parameter with name pDisplayFields and Promt Display Columns as shown below:

In Available Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field and label field.

In Default Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field.

STEP4:
Now you have to set the expression to display the colummns which are selected in the pDisplayColumn parameter. Right click on First Column (Year in my example) and click Column Visibility...

Write following expression in Show or hide based on an expression of Column Visibility wizard:
=IIF(InStr(JOIN(Parameters!pDisplayFields.Value,","),"Year")>0,False,True)

Now repeat this expression for all the columns by modify the expression for the respective column name accordingly.

Thats all. Now preview the report. You will see all the columns by default.

Now select required columns in Display Column parameter to modify the report layout at run time.

Exporting a report to multiple Excel tabs

Exporting a report to multiple Excel tabs

Sometimes, SQL Server Reporting Services users like to see what could be considered multiple reports in a single SSRS report. In most cases we can easily accomplish this using more than one data region in the report. If not, we can always use a subreport.
Let's say we have a report called the Top 10 Report. In it, we wish to see the top ten products sold by order quantity. We'd also like to see the top 10 resellers of our products, again by order quantity. This could be accomplished using multiple data regions or using two subreports.
We'll use two table data regions for this demonstration. This is shown in the following figure.

Previewing the report produces the following results.

This generally works well. Our users can view the single report and examine the details from its two data regions. If we choose, we can use conditional formatting techniques to dynamically show or hide each data region. But I digress; back to our example.
When our Top 10 Report is exported into Microsoft Excel format, both data regions are exported to the same worksheet as shown in the figure below.

This causes some users angst. They wish each data region to be exported to a separate worksheet or tab.
We can easily do this. Examine the properties for the first table data region. You'll notice that there is a boolean property named PageBreakAtEnd. By default, this value is set to False. Changing its value to True will cause a page break to be inserted after the table.
 
A page break at the end of a data region will cause a new tab to be created when the report is exported to Microsoft. The first data region, the Top 10 Products Sold by Order Quantity, appears on the first tab.

The Top 10 Resellers by Purchase Quantity report appears on the second tab of the Excel workbook.
 
So using this technique we can effectively combine multiple reports in the one Reporting Services report, while giving our users the ability to export them to different tabs in Microsoft Excel.

Naming Excel sheets when exporting reports

New features of SSRS 2008 R2 - Part 1 Naming Excel sheets when exporting reports

posted 19/6/2014  by Narendra Kushwaha -  Views: [50375] 

This is the first of what I hope to be a long series of posts that demonstrate some new features of SQL Server Reporting Services in the 2008 R2 upcoming release. At some point I may define and go into more detail but for now, I simply want to expose you, the reader, to some new features in the next version of SQL Server Reporting Services (SSRS).
At the company that I work for, we have created a series of reports that have been enhanced over the years based on customer feedback. These reports were built on SQL Server 2005 while it was in beta. The application is still running in SQL 2005 and it uses all of the BI componants of SQL Server -
Enough of the background....
The ProblemOne of our enhancements was taking several reports that were very similar in nature and combining them into one report. The key requirement was that these reports need to export into seperate tabs when exported into Excel. This meant that the user could run one report that contained several pages of seperate reports where as before the user would have had to run the reports seperately. We were able to tell SSRS to create a new page for each grouping of the dataset and so we combined all of the seperate reports into one dataset and seperated the data by using these groups. This worked well with one problem. When you export the data to Excel it names the sheets "Sheet 1", "Sheet 2", etc... There was no way to control this nameing without really digging under the covers of the rendering engine and creating custom code. Newsgroups were full of posts enquiring on how to name sheets in Excel with no good answers.
There was hope... SQL Server 2008 was released... but still no solution. Denied!
The Solution
Back in November I got my first view of SSRS 2008 R2 Nov CTP release during a presentation at SQL PASS. Bob Meyers and Sean Boon did a great job of showing off many new features of SSRS - many of these will hopefully be bloged in the next parts of this series soon. At the end they did a quick demo and showed that in the R2 release we will have the ability to name the sheets for exports to Excel. This 90 second demo earned the applause of the entire room.
Here is how it is done:
I have created a boring and simple report using the Adventure Works database simply for the purpose of showing this feature.
This report simply pulls Sales Person data and groups it by Territory so that each territory has it's own page.
Here is an example of the report exported to Excel before setting the sheet names.

In order to name the Sheets appropriately all you have to do is select the Group that has the page breaks from the Row Groups shown below

Then in the properties window navigate into the Groups section and find the PageName attribute.  By clicking on this attribute you can select the TerritoryName field from the dataset used to fill the report.  This can be any field or expression that you want to use, but for my example I wanted to use the name of the territory as the name of the sheets.

Here is a screen of the final result - SIMPLE!!

[SSRS] 101 SQL Server Reporting Services Interview Questions with Answers

Written by Super User. Posted in SSRS

Joomla
Joomla

Wordpress
Wordpress

In this post, I am going to walk you thru SQL Server Reporting Services Interview Questions from
· GENERAL | BASICS | Introduction
· DBA (Production Support) Prospective
· DBA + Developer Common Questions
· Developer (Report Designer) Prospective
· Solution Architect prospective
· IT Manager prospective

GENERAL Questions – SSRS Interview Questions
What is SQL Server Reporting Services?
Reporting Services helps business to Transform raw data (table data) into graphical like chart, histogram etc. to add meaning to raw data.
For example, the table below having a data but the moment that data was presented in chart, that added more business meaning to data.

SQL Server Reporting Services can query and display data from any of the multiple databases and represent in a way which is more understandable to users.
What are the tools available in market as an Alternative to SQL Server Reporting Services / (Business Intelligence tools) ?
Market is full of business intelligence tools here are few listed of them,

Actuate	Hyperion (BRIO)	SIEBEL-CRM
BusinessObjects	Oracle Express OLAP	Qlikview
Cognos	Informatica Power Analyzer	Proclarity
IntelliView	Dundas Chart for .NET	MS-Excel
SAS	MicroStrategies	Pentaho

Open Source solutions
· Jasper Reports
· JFreeReport
· BIRT (Business Intelligence Reporting Tools)
· OpenReport
· DataVision
· Pentaho
What is reporting lifecycle ?
Reporting Services has three mainly three phases
1. Development of Reports (Developer) – First of all a report needs to be design which is primarily done by report developer
2. Management of Reports (DBA) – Once the Report is being developed, DBA need to ensure
1. Security – Only authorized user should access the report
2. Execution – How the report will be executed to optimize data sources performance
3. Scheduling of reports – so that report are executed on scheduled timings
3. Report Delivery (DBA+Developer) – Once the report is being developed and executed now the report should be reached to final recipients (business users) who are going to understand / analyze report data. if any changes, we again go back to development stage.
What is Reporting Services Architecture ?
Reporting Services is a .NET framework-based platform that includes a comprehensive tools that we can use to integrate reporting solution into any centrally-managed environment. CLICK HERE(http://goo.gl/LwBRj) for more details
What can we do with SQL Server Reporting Services ?
Reporting Services can help you in building and deploying fully interactive scorecards, dashboards, and enterprise reports.
SQL Server Reporting Services Feature
· Design ad-hoc dashboards and reports quickly
· Data is interactive and available everywhere even on your IPAD / IPHONE.
· This is bundle product with SQL Server database services, so there is additional cost for this service.
· .Net integrated for rapid development for report even in few clicks by using wizards.
By using Reporting Services, we can build up a solution which is kind of Google maps, where we can list our all business locations.
Check out video post to learn, “How to Integrate Map with SQL Server Reporting Services”
What are the New Features of SQL Server 2008 R2 Reporting Services ?
Lot of new features were added in SQL Server 2008 R2, like
· New Report Types – Table, Matrix, List, Chart, Sub report
· New Tools added to report designer Toolbox
· Report Data Panel -  built in page numbers
· Report Builder 3.0
For details for new feature of SQL Server 2008 R2, please check my previous post, “SQL Server Reporting Services R2 – New Features”
What are the new features are introduced in SQL Server 2012 reporting services?
SQL Server 2012 has introduced a couple of new features like
· Power View - interactive data exploration
· SQL Server 2012 is fully integrated with SharePoint
· Introduction to Data Alerts, data alerts are a data-driven alerting solution that informs you about changes in report data that are of interest to you, and at a relevant time
· SQL Server Data tool
· new rendering extensions supports MS Office 2010
· Project Crescent is being introduced
DBA (Production Support) Prospective
What are the tasks which are mainly performed by a production DBA on a SQL Server Reporting Services ?
Operational support DBA are mainly responsible for
· Install and Configure reporting services
· Backing up Reporting Services (including databases, config files and encryption keys)
· Deploying new reports
· create and manage shared data sources
· Optimizing reports – Enable caching of reports
· Authorize users for reports
· Create and deploy security roles.
· Check Reporting Services Error Logs for errors
· Maintain response time of reporting services
Can we run Reporting Services with SQL Server Express Edition, which is a free version of SQL Server ?
Yes, SQL Server Express Edition with Advanced Services support Reporting Services. we can use SQL Server Express Edition for deploying reporting Services.
What are the limitations in Reporting Services on SQL Server Express Edition ?
Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition but following restrictions will be applicable to this edition.
· SQL Server Management Studio Express cannot be used to administer report server
· Report Models will not be available
· Other features like Caching, History and Delivery of Report is not available.
· Report Builder is not available
· Local SQL Server is a only option, which can be used as Report Data Source.
· We cannot store the report server database on a remote server, it has to be local only.
· Reports can be rendered only in Excel, PDF, Image formats only
· No SQL Server agent is available, so no scheduling is possible.
· Reporting Services will not be able to use more than 1 GB of RAM
· No Subscriptions (Standard and Data Driven) can be made
· Can not be integrated with Share Point
· Only named instances is supported
· Scale-out Report Servers will not be available
· Can not implement Role based security.
How to Install SQL Server Express with Reporting Services ?
For Step by Step instructions, Please check my previous post, “How to Install SQL Server Express with Reporting Services”
What are the parameters which should be considered for Reporting Services Disk storage requirement ?
There are various factors to be considers as a primary I would consider
1. Number of reports to be hosted server
2. Report size and frequency of reports
3. Number of snapshots to be saved
Can I configure SQL Server Reporting Services on a Windows cluster for High availability ?
Please check my previous post, “Installing & Configuring SQL Server Reporting Services on Cluster Environment for High Availability” to understand how reporting services works on cluster.
What is ReportServer and ReportServerTempDB ?
Reporting Services uses two SQL Server databases for storage by default, the databases are namedReportServer and ReportServerTempdb.
ReportServer is a main database, which store all internal configuration and report meta data where asReportServerTempdb is used to store temporary data, session information, and cached reports.
How to backup SQL Server Reporting Services ?
There are mainly three things, which should be backed up as part of reporting services backup
1. Report Server Databases, which can be backed up by SQL server backup and restore method. Check my previous Video post, “HOW to BACKUP SQL”
2. SQL Server Reporting Services Configuration, SQL Server Reporting Services Configuration is saved in config files, which can be copied as part of backup. look for other to know config files and there location.
3. Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.
What is encryption key ?
Encryption keys are used by the report server so that items such as connection strings are maintained securely. these keys are required in case you want to perform restoration of report server databases.
How to backup encryption key ?
Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.
Can we install a 32-bit version of SQL Server Reporting Services on a computer that is running a 64-bit version of Windows?
YES, we can install SQL Server 32 bit on Windows 64 bit version. SQL Server Reporting services are part of SQL Server.
Where the SQL Server Reporting Configuration is saved, is that in registry ?
NO, all SQL Server configuration is saved in configuration files (.xml files)
What are the key configuration files for SQL Server Reporting Services ?
Mostly all Configuration files located at Install Directory\Microsoft SQL Server
\<SQL Server Instance>\Reporting Services\ReportServer and  ReportManager
· RSReportServer.config stores configuration settings for feature areas of the Report Server service: ReportManager, the Report Server Web service, and background processing.
· RSSrvPolicy.config stores code access security policies for the server extensions.
· RSMgrPolicy.config stores code access security policies for Report Manager
· ReportingServicesService.exe.config stores configuration settings that specify the trace levels and logging options for the Report Server service.
Other Files
· RSReportDesigner.config contains settings for Report Designer and this file is located in the..\Program Files\Visual Studio 9.0\Common7\IDE\PrivateAssemblies
· RSPreviewPolicy.config stores server extensions used during report preview and this file is located in ..\Program Files \Microsoft SQL Server\100\Tools \ReportDesigner
What are the mostly used PERFMON Counters for monitoring SQL Server Reporting Services?
This is bit tricky and everyone has his own answer, but I personally choose the following at least
· ReportServer: Service Performance Object \ Active Connections
· ReportServer: Service Performance Object \ Report Executed per second
· ReportServer: Service Performance Object \ Total Cache Hits
· ReportServer: Service Performance Object \ Total Requests
· Web Service \ Deliveries per second
· Web Service \ Total processing failures
What all should be backed as part of reporting Services ?
1. Report server databases (ReportServer and ReporterverTempdb)
2. Encryption keys and
3. Configuration Files (xml files)
1. RSReportServer.config,
2. RSSrvPolicy.config,
3. RSMgrPolicy.config,
4. ReportingServicesService.exe.config,
5. RSReportDesigner.config and
6. RSPreviewPolicy.config
How to backup up Encryption Keys?
Use SQL Server Reporting Services Configuration tool to backup symmetric keys
Questions for Developer as well as Developers (depends on profile)
What are the tools which are being offered by Microsoft SQL Server for Developers?
· Report Designer, used to develop reports in Business Intelligence Development Studio (BIDS) and then publish them to a Report Server
· Model Designer, used create a report model that Report Builder users uses to help them build on-demand reports.
· Report Builder – used to build reports without having to understand the underlying data source structures in the database
Note – These are built in tools with Microsoft SQL Server, there is no additional cost for using these tools.
What are the tools which are being offered by Microsoft SQL Server for DBA’s?
· Report Manager – a web-based report access and management tool to browse existing reports, upload new reports, and other properties like execution properties, security, and subscriptions.
· Reporting Services Configuration –used to configure a report server like report server databases, SMTP server and services properties.
· SQL Server Management Studio (SSMS) -  an alternative to Report Manager, which is primarily being used by DBA’s for managing reports.
Note – These are built in tools with Microsoft SQL Server, there is no additional cost for using these tools.
How to deploy reports ?
Reports can be deployed by using BIDS (Business Intelligence development Studio ) or by Report Manager which is web based application. Please see previous post, “How to deploy report to SQL Server Reporting Services”
Can I deploy a report at multiple servers at one go ?
NO, native SQL Server Reporting services doesn’t supports this functionality but we can deploy reports directly to multiple servers at one go but this functionality is not built in, we need to buy some third party tools.
What is Report Builder ?
Report builder is used to create small reports and it a define interface. You can’t change the report interface in report builder it pre designed. You can just drag columns in the report.
Report builder creates reports on database objects available with report model project. Please check my previous Video post, ”How to Install Report Builder 3.0”
What is report rendering ?
Exporting a report data with design o different type of file types is knows as Report rending. SQL Server Reporting Services supports multiple rendering extensions like Word, Excel, CSV, PDF, HTML etc.
Can I disable | restrict SQL Server Reporting Services Export Formats (Rendering Formats) ?
YES, we can restrict Reporting services export formats.
SQL Server Reporting Services (SSRS) supports export to different formats like XML, CSV, HTML, PDF, etc., when you view the report.  This export option is available at report viewer Export drop-down list.
We can control this via report services server configuration file which is available at “C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\Report Server\ rsreportserver.config“.
For More details, please check my previous post “How to Disable/restrict SSRS Export format ?”
How to migrate SQL Server 2008 Reporting Services to another computer?
If you are trying to migrate only SQL Server reporting services to new server, then we have an option to install reporting services on new server and while configuring report server, we can point report server services to old report database database.
Or incase, you are trying to migrate report server databases and reporting service together, the install reporting services on new instance and restore configuration files  and restore report server databases and reconfigure the report server services.
How to check how frequent report is being called ?
Check Report history either from SSMS or Report manager. In report manager, go to report and select history tab. check here for more details
What is Report Caching ?
Report caching is a performance enhancing technique in Reporting Services that saves copies of reports for faster viewing. Cached instances have an expiration time set to force a refresh of the data set used
for the report.
In case you have filters in your report, when filters will be applied in Cached Report instance ?
Filters are applied when a report is rendered, Filters will not create a new cached instance on the Report Server.
What is report snapshot ?
Snapshot means a instance of a report for future reference, that means a copy of report (data is freezed) will be saved on a report server for future reference.
What is Data Driven Subscription ?
Subscriptions are used to deliver rendered reports to business users at specified schedule. For example, a sales report should go to sales manager daily at 7:00 AM for last day report. This is kind of pushing reports on a schedule. By creating subscription, we can send reports to users in e-mail or Deliver to a file share
or can put report in Microsoft Office SharePoint server
A data-driven subscription can deliver a report in many rendered formats to many destinations. For example, USER1 wants report in XML format and USER 2 wants in a pdf format and user 3 wants in word but that too on a shared drive.
To meet these objective we can use Data-driven subscriptions as data driven  subscription require a database table to store all these report values so that these values can be used during processing.
How to upgrade report from SQL Server 2005 to SQL Server 2008?
SQL Server Setup is used to upgrade SQL Server Reporting Services. Run the SQL Server 2008 setup on existing SQL Server 2005 and opt for upgrade.
Developer (Report Designer) Prospective
Any Idea, how to to build / design Report using Visual Studio?
Building Reports using Visual Studio is quite easy, Please check my previous Video POST, “How to create a report using Visual Studio 2008?” which describes
1. how to build a report using Visual Studio 2008
2. deploy report to SQL Server Reporting Services
3. How to create an shared data sources
4. How to design a report
5. How to add interactive sorting Change
6. Run the report in SSRS
What is Parameterized Reports in SSRS ?
You often need more advanced reporting such as drop down lists and the ability to use parameters when working in Sql Server Reporting Services. Please check VIDEO Tutorial, “How to make Parameterized Reports in SSRS”  which describes,
· How to use variables that have not been declared in SSRS vs TSQL
· How to create report with auto-Parameterization
· How to View Report Parameters
· How to create a drop-down list
What are different types of reports are available ?
There is as such not any type of of reports, but we can categories them as
Tabular Report – where we represent data in rows and columns format. (Now this is non as Tablix which is mixture of table and a matrix)
CHART REPORTS – where we represent the data in graphical format mainly in charts?
further more these reports can fetch data either from relational database (TSQL REPORTS category) or from CUBES (MDX Reports)
What sort of query we can write in SSRS, is that just SQL / TSQL or is there anything else too?
· MDX – for OLAP / CUBE data sources
· DMX – for data mining
Can you edit a report that an end user created by using Report Builder in BIDS?
Yes, if an end user created a report by using Report Builder in BIDS and saved the report definition file, you can open the file in BIDS and edit it.
What is Sub report ?
SUB report is very helpful when the detail of a report’s data region is so complex that a separate report is a better option then using other tools.
Can sub report data source be different from that of the parent report ?
YES, sub report data source can be different from that of the parent report.
What are the various ways to deploy reports ?
Reports can be deployed to report server by using any of the following method
· BIDS
· Computer Management console
· .nET START command
· SSMS
· Report Manager
but normally BIDS  and Report managers are mostly used.
Can we use Analysis Services Database as a data source with Model Designer ?
NO, Model Designer doesn’t access data from Analysis Services cubes.
What is report subscription?
Subscriptions are standing requests to deliver report data to requested recipients. Once the report is being subscribed and subscriber will get updates from report server on scheduled interval.
What is the RDL file?
RDL stands for Report Definition Language, when we design a report using any tool like BIDS and when we save the report, it’s saved as .rdl file, RDL file is an kind of xml code, which stores the design of the report.
This RDL file is being used  for deploying report to report server.
Can you use a stored procedure to provide data to an SSRS report?
Yes, you can use a stored procedure. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.
How to deploy Reports on SQL Server Reporting Services ?
Please check my previous VIDEO post to learn, Step by Step Learning, How to deploy report to SQL Server Reporting Services 2008
How can I build my first report which is ready to deploy in SQL Server Reporting Services ?
Please check my previous blog post, Introduction to SQL Server Reporting Services, for a brief video tutorial for SQL Server Report designer.
Solution Architect prospective
IT Manager prospective
Can we run Reporting Services with SQL Server Express Edition, which is a free version of SQL Server ?
Yes, SQL Server 2005 Express Edition with Advanced Services support Reporting Services. we can use SQL Server Express Edition for deploying reporting Services. For more details to know SQL Server Express Limitations, please check my previous post, “What are the limitations in Reporting Services on SQL Server Express Edition”
Is there any free tools to build reports ?
YES, there are log of open source code solution are available, which can used to design reports, here are few listed.
· Jasper Reports
· JFreeReport
· BIRT (Business Intelligence Reporting Tools)
· OpenReport
· DataVision
· Pentaho
What are the product alternatives to SQL Server Reporting Services ?
Market of full of tools and here are few of them

Actuate	Hyperion (BRIO)	SIEBEL-CRM
BusinessObjects	Oracle Express OLAP	Qlikview
Cognos	Informatica Power Analyzer	Proclarity
IntelliView	Dundas Chart for .NET	MS-Excel
SAS	MicroStrategies	Pentaho

How many instances of Reporting Services can be managed by a single DBA ?
This is pretty tricky questions, and there is not any single formula to decide this. This all depends on what sort of monitoring tools you have and what sort of automation you have achieved.
But as per my understanding and calculations a single 3-7 year experience DBA without any third party tool can easily deploy 25-35 report in a day, which is one time activity for most of the organizations and DBA can easily monitor / provide production support to 25-40 instances of SQL Server, which included daily
· Monitor Event Viewers
· Check SQL Server Errors
· Check Reporting Services Logs
· Monitor Perfmon counters
· Backup all databases
· check schedule sql jobs etc.
but I believe, without any tool (just using manual / Script) we can assume 1 DBA for every 25 servers is ideal number to focus more on preventive actions rather then corrective actions.
Is my data is safe, What all we are backing up for SQL Server Reporting Services ?
There are mainly three things, which should be backed up as part of reporting services backup
1. Report Server Databases, which can be backed up by SQL server backup and restore method. Check my previous Video post, “HOW to BACKUP SQL”
2. SQL Server Reporting Services Configuration, SQL Server Reporting Services Configuration is saved in config files, which can be copied as part of backup. look for other to know config files and there location.
3. Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.
What is the licensing cost for SQL Server Report Server?
SQL Server Reporting Services is bundled with SQL Server. So in case you have already purchased SQL Server Standard edition for your business then there is no additional cost for using this SQL Server Reporting Services.
Do I need to buy separate licenses for users who view reports ?
NO, there is no separate licenses for report delivery or viewing report on demand, i’’s all bundelled in SQL Server with once license cost.
Is there any free edition where I can use SQL Server Reporting Services ?
Yes, SQL Server 2005 Express Edition with Advanced Services support Reporting Services but there are several limitation, check my post,”What are the limitations in Reporting Services on SQL Server Express Edition“ for these limitations.

Source : http://www.sqlserver-training.com

Repeat Header / Keep Header Visible in Tables in RS 2008

Repeat Header / Keep Header Visible in Tables in SSRS 2008

 You selected "Repeat header rows on each page" or "Keep header rows visible while scrolling" in the tablix properties dialog, but it doesn't seem to work as expected?  You might want to try the following four steps if you are using a "table"-style layout:

1. in the grouping pane, click on the small triangle and select "Advanced Mode" to show static members:
   
    
2. verify that you have the properties grid turned on (in BI Development Studio use F4, in Report Builder go to the "View" tab and select "Properties")
    
3. select the corresponding (static) item in the row group hierarchy
    
4. in the properties grid:
   - set KeepWithGroup to After
   - set RepeatOnNewPage to true for repeating headers
   - set FixedData to true for keeping headers visible

Please read on if you are interested in more details and ever wondered about the meaning of double-dashed lines on the design surface.  In short, double-dashed lines show the row group, column group, corner, and tablix body areas of a data region.  In the "matrix"-style example shown below, the yellow area represents the corner of the tablix, the light blue areas are row group headers, dark blue areas denote column group headers.  The settings under "Row Headers" and "Column Headers" in the tablix properties dialog only apply to the row and column group areas (i.e. the blue areas on the left / above the double dashed lines).

If you have a "table"-style layout however, then the row/column group areas are often empty as you are using "headerless" table-style groupings.  In that case, you have to set the properties as explained above to make entire static members (rows / columns) repeat / visible.