The following is an example of the first SQL Server Reporting Services (SSRS) report for Visual Basic 2005. The report is called Order by Country. It will display the Order detail by Country. Frist let start Visual Bacis 2005 and select a new project. The following screen will appear. Give it a name and note the defualt location when the project is save.
Adding a data source to your report
Right click on the Data Sources folder and select to add a new data source
Give the data source a name like Northwind (screen below)
Click Edit and find the Northwind database
Use Window Authentication this is for internal use (only your domain users)
SQL Server Authentication - You will need to add a SQL Server user for this.
Adding a new SSRS Report
Now that you have a data source you can right click the Reports folder and choose Add then New Item (report)
Choose Report Wizard, Enter a name for the report and click Add
Follow the Wizard - it will guild you thought the whole process
You can use most of the default setting when you get to Data Source make sure you select the Shared Data source (NorthWind) you just created
When you get to Query enter the following SQL
select t1.CustomerID, t2.OrderID, t2.UnitPrice, t2.Quantity, t3.City, t3.Country, t3.* from
dbo.[Orders] t1
inner join dbo.[Order Details] t2 on t1.OrderID=t2.OrderID
left outer join dbo.Customers t3 on t1.CustomerID=t3.CustomerID
When you get to the following screen make the following selection. You want to Group by Country and add the other fields under the Details section
Run your run and you should see Order information by Country. If not then Your Order Details table do not have data in it. Then run the following to add record into this table.
insert into dbo.[Order Details] (OrderID, ProductID, UnitPrice, Quantity)
Values (10257,10,31.00,1)
Now let add a new column to the report
Let say you want to include a column with Extended Prices (Price*Qty)
Select one of the column (The Quantity Column)
Right Click and select Copy
Right Click again and select Paste
Now click the new column and select Expression
Enter =sum(Fields!Quantity.Value*Fields!UnitPrice.Value)
Now if you run the report, the colomn will have Qty*Price
Now let add a grand total at the bottom report
Select the entire detail row (screen below)
Right click and select Show Footer
In the Footer section under Extended Price add the following expression
=sum(Fields!Quantity.Value*Fields!UnitPrice.Value)
Run the report and see that it has a Total of the Extended Price at the bottom
Add Parameter(s) to report
Let said your users want to select the country(s) they want
Add a new data source called Country with the following query
select distinct Country from dbo.Customers
Update you report query to include an @ parameter like @Country
You what multiple country so your parameter need to be in (@Country)
So go the Data Tab click the . . . next to Northwind (data set)
You will see the query for the report, add where t3.Country in ( @Country) to the end
Save and go back to Layout Tab, Right click the area outside your report section (to the left of the report title) and select Report Parameters…
Note that your Country parameter is in the list of Parameters:
In the Properties section made sure you check multi-value check box
In the Available values Section select from query select the data set Country
Save and run your report and see that you have to select Country(s) before running it
Below are my Sponsor/Advertisements ads. Please do not click if you do not want to find out more about their services or products.
