3.1 Make a few testtables and inserts some records (just "teststuff" to build reports on)

create table CUSTOMERS
(
cust_id int primary key,
cust_name varchar(32),
cust_country varchar(32)
)

create table PRODUCTS
(
product_id int primary key,
product_name varchar(32)
)

create table SALES
(
product_id int foreign key references products(product_id),
cust_id int foreign key references customers(cust_id),
amount_sold int
)

insert into CUSTOMERS values (1, 'KLM','Netherlands')
insert into CUSTOMERS values (2, 'Shell','England')
insert into CUSTOMERS values (3, 'Kaputt AG','Germany')
insert into CUSTOMERS values (4, 'ABCcorp','USA')
insert into CUSTOMERS values (5, 'XYZcorp','USA')
insert into CUSTOMERS values (6, 'DEFcorp','USA')

insert into Products values (1, 'streetbike')
insert into Products values (2, 'offroadbike')
insert into Products values (3, 'handy crate of TNT per 20 kg')
insert into Products values (4, '5GW Powerplant')
insert into Products values (5, 'rollerskates')

insert into SALES values (1,1,50)
insert into SALES values (5,2,10)
insert into SALES values (3,3,25)
insert into SALES values (3,3,15)
insert into SALES values (1,4,15)
insert into SALES values (1,4,15)
insert into SALES values (1,4,15)
insert into SALES values (1,4,25)
insert into SALES values (4,3,7)
insert into SALES values (2,4,66)
insert into SALES values (1,1,50)
insert into SALES values (1,1,50)
insert into SALES values (1,1,100)
insert into SALES values (1,1,50)
insert into SALES values (5,1,17)
insert into SALES values (3,4,35)
insert into SALES values (3,2,25)

3.2 Create and Deploy a Report with BIDS

3.2.1 Starting a Solution or Project:

Before we make reports, I created a folder "C:\testreports" to hold all my new sources. Maybe you should do the same.
Now that all preparations are done, let's start creating a Report.

In your Windows SQL Server program group, locate the "SQL Server Business Intelligence Development Studio", and start it up.
Since we are going to create a new report, click on "File", click "New", and choose "Project"

Fig 6. Choosing for "Business Intelligence Projects"


Now watch the above dialogbox closely. Here, in the leftpane, we can choose for "Business Intelligence Projects" and "Other Projects Types"
Let's first try "Business Intelligence Projects". In the rightpane we can choose for "Report Server Project Wizard"
or "Report Server Project".
Both will lead to screens in order to create a Report, but the Wizard helps tremendously if you are new.
Now you could fill in "name" (TheFirstReport) and "location" (c:\testreports), and we could start.
But I want you to start a bit differently.

Instead, in the leftpane, choose "Other Project Types" and then choose "Visual Studio Solution", and choose "Blank Solution".

Fig 7. Choosing for "Solution"



Since the terms "solution" and "project" are names for "containers" which can hold items (such as a "report"),
there meaning in BIDS are very close (or almost identical). However, generally, a "solution" is a bit "wider" concept.
Anyway, for our goal to create reports we can choose the way like shown in fig 6, or like in fig. 7.

I like to start as shown in figure 7, so please choose that way too, and fill in the "Name" and "Location" exactly as shown:

Name: TheFirstSolution
Location: c:\testreports

After you have typed in the above, confirm your choice and click "OK".
At the right side of BITS, you will see the "Solution Explorer". Here it will show "Solution 'TheFirstSolution' (0 projects)"
Were gonna add a Project to our solution, so rightclick your solution and choose "Add" and choose "New Project".

Fig 8. Adding a Project to our "Solution"


The following will appear:

Fig 9. Adding a Project to our "Solution"



Fill in the Projectname as shown above (TheFirstReport) and click OK.
The solution explorer has changed to:

Fig 10. Solution Explorer showing "SharedDataSources" and "Reports". I already rightclicked on DataSources.



3.2.2 Add a Shared Data Source:

Since a Report is build on "data", we must create a "DataSource" first. A DataSource could be an Oracle database,
or TerraData, XML etc.., and ofcourse a SQL Server database as well.
So, rightclick the "Shared Data Sources" container, and choose "Add New Data Source".


Fig 11. Shared Data Source Properties.



Give the Data Source an appropriate name. I named it "SQLServerTESTDB".
In order to create a "connection string" (on how the report will access the database), click "Edit".

Fig 12.



Select the desired SQL Server Instance name, and after that, select the desired Database name.
In my case, the instance is called "WSWIN7", and the database containing the sample data is "TEST".
Click OK to return to the former Dialogbox. Take notice of the "connection string". Apparently, a Database is called a "Catalog".
Confirm this Dialogbox too, and in the Solution Explorer you will see your Data Source listed as "SQLServerTESTDB.rds".


3.2.3 Add a Report:

Finally, we are going to create a Report. Rightclick the Reports container. Note that we have two relevant choices here:

- Add New Report (this will start a Wizard)
- Add -> New Item (create the Report in "do it yourself" mode: no Wizard)

Here, we take the Wizard, so choose "Add New Report". The "Welcome To The Report Wizard" screen pops up. Click Next.

In the Dialogbox that follows (not shown here), you can either select an existing DataSource, or create a new one.
Since we already have the correct DataSource, we only need to to click "Next".

Note: indeed, a "solution" or "project" might have multiple "DataSources", so this is why you were able
to select an existing DataSource, or create a new one.

Next follows the "Query Builder" screen. Here you have some choices. You might enter a "stored procedure" that
retrieves the Report data, or you may enter a Query (what I did), or you can graphically build a Query by
clicking on "Query Builder".
I simply added a Query as shown in the figure below.

By the way, here is the query in text:
select CUSTOMERS.cust_name, Products.product_name, SUM(SALES.amount_sold) Total_Amount
from CUSTOMERS inner join SALES on CUSTOMERS.cust_id=SALES.cust_id
inner join PRODUCTS on Products.product_id=SALES.product_id
group by CUSTOMERS.cust_name, Products.product_name


Fig 13. Add a Query



After that, a few dialogboxes will follow with respect to the "formatting" of the Report.
I think that this is not so very important for this note. However, one of those screens is shown below.
In this particular screen you decide on how you "group" the records. Here is where a general knowledge
of SQL queries might help (like how to group data).


Fig 15. Design the Report



Now some other dialogboxes will follow with respect to formatting. Choose what you like, or just
take the defaults. However, there is one screen where you can select
"Enable Drill Down" and "Include Subtotals". I would like you to enable those.

After a while, you are finished, and BIDS will look like this:

Fig 16. BIDS:



Take a good look at that screen. On the right is the Solution Explorer, showing the objects that you have created sofar.
In the middle we have your Report in two possible modes (note the tabs): Design and Preview.
If you switch to "Preview" mode, the report will be build and you will see what your endusers will see as well.
In "Design" mode, you can still alter details of your report (like for example widening a column).


3.2.4 What files do we have up to now?:

Since I stored everything in C:\testreports, let's see what we have in there.
You might find several directories and files, among which there should be:

-There is a .sln file, which describes versions of the "solution"
-We have a .rdl file, which describes the "report" in XML
-And we have .rds file which describes the DataSource

In BIDS, in the Solution Explorer, you can rightclick Report1, and choose "View Code" which shows you the XML.


3.2.5 Deploy the Report using BIDS:

Now we need to deploy the report on the Reporting Services, so that endusers may access it using a browser.
Don't forget: BIDS is a client tool, so maybe you have developed the report on your Workstation.
When we deploy, the ReportServer database receives the neccessary information about the DataSource, Report etc..
so that this information is centrally accessible.

If you take a look at the figure below, then you see how you can deploy your solution.
But hold on: BIDS first need some info from you like the Server URL.

Fig 17. To Deploy a Solution/Report using BIDS:



So before you take action like is suggested in figure 17, rightclick your solution and choose "Properties".
In figure 18 you see an example of those properties and values.
Take a good look at those properties. The server URL, in my case "http://wswin7/reportserver" is essential.


Fig 18. Viewing properties before deploying:



When all properties are set, perform the action as is suggested in figure 17.

Note: when you performed all of the above on a Vista/Win7 workstation with SQL Server and SSRS installed,
then you might receive an error on "insufficient permissions", even if you were logged on as an Administrative user.
In this case, startup BIDS again, but this time using the "run as Administrator" (rightclick the icon).

To view your report, use: http://YourReportServer/reports

In my case, I would see this:

Fig 19. Accessing the Report using a browser:



As shown above, I can click the "TheFirstReport", and from there I am able to really active the report with data.

Fig 20. Viewing the Report from client browser:

4. Some other ways to Create and Deploy a Report.

In deploying Reports accross Development, Test, Acceptance and Production systems, it is absolutely essential
that you keep (for example) DataSource names consistent accross all environments.
Generally it is not difficult to deploy reports. The problem often is, to have a good consistent strategy
for deploying reports from Test to Acceptance to Production.

Never underestimate this: it requires carefull planning and probably quite some research.

Here are just a few "hints" on how you can deploy reports:

1. Using BIDS:

This is what we already have seen in the above section.


2. Using "rs.exe" command prompt utility:

You can deploy reports using the "rs.exe" command utility.
Here is an example:

C:\SSRSscrips> rs –i Script.rss -s http://servername/reportserver

You can easily check the internet for the full syntax of "rs" and examples of deployment.


3. Using the Report Manager:

You can access the Report manager for managing and viewing reports. Use the following URL:

http://YourReportServer/reportserver

Here you will find controls to upload a file. To upload a report, browse to a report definition (.rdl) file.
See also section 3.2.4.

Fig 21. Upload a report .rdl file:

5. Security involved with Reports and SSRS.

Here are a few words on managing security around published Reports.

SSRS 2008 does not use Internet Information Server (IIS) to "host" the folders as "virtual directories".
SSRS sort of hosts them itself using http.sys and database information (as we shall see later).
This somewhat complicates "good" management of virtual directories.

As we know we can connect to the folders and modify the permissions using the Report manager. What type of "permissions"
you can "grant" to Domain groups (or users), are determined by the implicit "roles" within the Report Services itself.
This will be explained now.

1. Roles in Report Services:

Just as with the SQL Server engine, where predefined database roles exists (like db_datareader etc..), the same paradigm
is in place with the Report Service engine.

From SQLServer Management Studio (SSMS), you not only can connect to SQL Server instances, but to "Analysis Services" and
and "Report Services" as well.
In the figure below, I connected to my Report Server (using SSMS) and viewed which roles exists.
As you can see, various roles exists, like "Browser" and "Content Manager" etc.., each with a predefined list
of "permitted tasks" aka permissions.
I doubleclicked the Content Manager role, which role may perform just about any task as you can see from figure 22.
By the way, it's possible to select/deselect a certain task (permission) in order to "lower" or "increase"
the role's power

Fig 22. Roles in Report Server:




Note that it's easy to create a custom role. Just rightclick the Roles container and choose "Add Role"
As you can see in the figure above, I created the role "ThisRoleWasCreatedByAlbert" which may only view reports.


2. Managing Folders and Reports in Report Manager:

You can start the Report Manager (which is a webinterface) using the URL http://YourReportServer/reports

Once you see the list of folders, you can manage the permissions around them. In the figure below, I indented
to "edit" the security of the "TheLastReport" folder. So, select that folder and click edit.

Fig 23. Granting permissions in Report Manager (webinterface):



So, I pursued my action, and tried to alter the "permissions" with respect to "TheLastReport".
As you can see, I can grant the group "Antapex\nerds" the role "ThisRoleWasCreatedByAlbert" to view reports.

Fig 24. Granting permissions in Report Manager:





3. Registrations in the ReportServer database:

In the ReportServer database, a number of tables exists which form "the registry" around SSRS.
As you can see in the figure below, the folders or virtual directories (like TheLastReport), are simply records
in the Catalog table.

Fig 25. Metadata in the ReportServer database:



So, multiple SSRS implementations in native mode, will never form an Enterprise System.
For Enterprise implementations, Sharepoint is a much better starting point, where SSRS is just simply a component
among many other components.

6. A note on SSRS and High Availability.

Reporting Services itself, is not cluster-aware. But you can let the underlying Report database(s) to be part of a HA solution,
like Database Mirroring or Failover Clustering.
But the SSRS instance itself, is not cluster aware and cannot failover, even if you install SSRS on two Failover clusternodes.

However, you might consider a SSRS scale-out deployment to run in a "Network Load Balancing cluster" (NLB).
This means that a "Load Balancer" "connects" to two (or more) SSRS instances, which uses a shared Reporting Database.
This Reporting database then, can just be part of a SQL Server Failover cluster.
In this case, the balancing also has an implicit HA functionality for the SSRS instances, while the database(s)
just ar part of standard Failover clustering.
It is very important to understand that the "Balancer" is just a "Virtual IP" in DNS, while the NLB cluster software is installed
and configured on the Servers where the SSRS instances are going to be installed.

So, the NLB cluster is accessible through a virtual server name that "maps" to the virtual server IP address (in DNS).
A virtual server name is needed so that you can configure a single point of entry to the NLB cluster.
The NLB cluster then will adress the balancing between the "real SSRS nodes".
When configuring a URL for each report server instance, you will specify the virtual server name as the host.





So..., this note..., was it not "Super Simple" ? Still, I hope it was usefull.....