Intro to SQL Server Reporting Services 2008 : Report Builder 2.0

This shipped after SQL Server Reporting Services 2008 and is available as a separate download. I have always run a search to download the latest installation file.

Report Builder 2.0 is a too design to allow the easy and quick development of Reporting Services Reports and was created with the non-developer in mind. This is reflected by the fact that it looks like MS Office.

It can be started by selecting Start > All Programs > SQL Server 2008 Report Builder > Report Builder 2.0.

Once you have opened Report Builder 2.0 you will notice there are 4 tabs Run,Home, Insert and View.

The Home tab contains Clipboard, Font Paragraph, Border, Number and Layout.

1

Insert tab has Data regions, Report Items, Sub reports, Header & Footer.

2

View tab has Show and Hide.

3

The Run tab has Views, Zooms, Navigation, Print, Export, Options and Find.

4

 

SQL Server Reporting Services 2008 (SSRS) Databases- ReportServer & ReportServerTempDB.

When reporting service is set up there are two default databases created which are

1. ReportServer

2. ReportServerTempDB.

1

Reportserver database contains a wide range of information which is briefly listed below.

1. Subscriptions

2. Schedules

3. Data sources

4. Folder

5. Keys

6. History

7. Notifications and much much more.

I would suggest looking at the tables which are contained in the ReportServer database to get a good understanding about all the data which is stored and within and I have listed below.

ActiveSubscriptions

Batch

CachePolicy

Catalog

ChunkData

ChunkSegmentMapping

ConfigurationInfo

DataSets

DataSource

DBUpgradeHistory

Event

ExecutionLogStorage

History

Keys

ModelDrill

ModelItemPolicy

ModelPerspective

Notifications

Policies

PolicyUserRole

ReportSchedule

Roles

RunningJobs

Schedule

SecData

Segment

SegmentedChunk

ServerParametersInstance

ServerUpgradeHistory

SnapshotData

Subscriptions

SubscriptionsBeingDeleted

UpgradeInfo

Users

I would always recommend setting a maintenance plan to regularly backup this database due to all the important data that is held within. Do not attempt to try and modify the table structure within these tables as you will likely cause an error.

ReportServerTempDB stores temporary data from the ReportServer DB which includes cached reports, session information….. This data is still important and it should still be backed up to aid speedy recovery.

Adding SQL Server Reporting Services 2008

Adding SQL Server Reporting Services 2008

Before installing SQL Server Reporting Services 2008 it is important that you check whether your operating system is supported ( Windows Server 2003,2008,2012, some editions of XP, Vista, 7 and 8), you have sufficient RAM ( 512mb free), your CPU is powerful enough ( 1.0GHZ ) and if you have sufficient hard disk ( about 2gb). The installation checks will check all of this but a bit of prior research could prevent any time being wasted.

Always refer to Book Online to check the required specifications as these are the most up to date available on the internet.

1. Goto Start Menu > All Programs > Microsoft SQL Server 2008 R2, SQL Server Installation Centre

1

2. Click on New Installation or add new features to an existing installation

2

3. The SQL server setup wizard will then check your environment to see whether it meets the requirement to install reporting services. As long as there are no failures click OK.

3

4. As long as there are no problems click Install on the next screen.

4

5. The setup rules will check the system against another set of rules and will highlight any problems.

5

6. Select Full Text Search Reporting Services , Business Intelligence Development Studio, Client Tools Connectivity, Management Tools – both Complete and Basic.

6

7. Another check will be done against the features selected. Press next if there are not any problems.

7

8. Disk space of your setup will be checked.

8

9. Please use whatever accounts you have assigned for the account. I will be using the local service account as I haven’t properly setup my VM.

9

10. Press next

10

11. Press next. Generally we don’t send back the rror reports.

11

12. The final checks will be done and press next.

12

13. There will be a comprehensive list of the options chosen. Please review this list and then if your happy press install.

13

14. When the installation is completed successfully then you will get the final screen.

14

What is Business Intelligence ( BI )? Business Intelligence Development Studio ( BIDS )? SQL Server Reporting Services ( SSRS )? SQL Server Integration Services ( SSIS )? SQL Server Analysis Services ( SSAS )?

What is Business Intelligence ( BI )? Business Intelligence Development Studio ( BIDS )? SQL Server Reporting Services ( SSRS )? SQL Server Integration Services ( SSIS )? SQL Server Analysis Services ( SSAS )?

What is Business Intelligence ( BI )?

Business intelligence is simply using software to manage large amounts of data to get meaningful and actionable results. It is also an growing field within IT where people are choosing to specialise in.

Software associated with BI includes SQL Server Reporting Services, SQL Server Analysis Services, Biztalk, Business Objects, Cognos…..

Business intelligence is used to make smarter marketing, identify new markets, efficiency and cost savings.

What is Business Intelligence Development Studio ( BIDS )?

Business Intelligence Development Studio ( BIDS ) is a custom version of Visual Studio which is supplied with SQL Server where the development of Analysis Services databases and mining models, Integration Services packages and Reporting Services reports is completed.

If BIDS is installed on a system that already has a version Visual Studio installed then these additional project types are added to the existing installed Visual Studio.

What is SQL Server Reporting Services ( SSRS )?

SQL Server Reporting Services is a comprehensive reporting feature of SQL Server where reports can be created, deployed and managed across an Enterprise scale environment.

SSRS is used by many firms to replace Crystal Reports, Excel and Access as it has a far greater range of abilities and features as well as far greater performance.

What is SQL Server Integration Services ( SSIS )?

SQL Server Integration Services is often described as an ETL tool as its function is to Extract, Transform and Load data (ETL). It is often used to perform repetitive tasks as each process can be saved as a SSIS package and can be scheduled to run automatically.

SSIS packages can be made simply via the GUI, BIDS ( drag and drop ) or enhanced using T-SQL.

What is SQL Server Analysis Services ( SSAS )?

SQL Server Analysis Services is made up of two distinct part which an Online Analytical Processing (OLAP) and Data mining.

OLAP allows the building of multidimensional structures which are known as cubes. Using these structures businesses can then analyse the data to solve problems. Cubes are queried using Multidimensional Expressions (MDX) rather than T-SQL.

Data mining allows the analysis of huge amounts of data and is used to find patterns and insight into information which is not as seemingly obvious as that found in Cubes.