First SSRS Report

  • 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.
  • New SSRS Project

  • 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.
  • Shared Data Source

  • 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
  • Report Wizard

  • 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
  • Report Layout
  • 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
  • New SSRS Report

  • 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…
  • Report Parameter
  • Note that your Country parameter is in the list of Parameters:
  • In the Properties section made sure you check multi-value check box
  • Report Parameter Detail
  • 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.