Steps to Create a SSRS Project with Components in BIDS :A project is the container for all objects - for example, reports or SSIS packages - in BIDS. Each project is of a certain type; you can create a SSIS or other project,Here we want a SSRS project.
- From the Start menu, select All Programs > Microsoft SQL Server 2008 (or 2005, as appropriate) > SQL Server Business Intelligence Development Studio.
- Select File > New > Project.
- Select "Report Server Project" from the list of Business Intelligence Projects, enter a project name, and click OK.
You now have an empty SSRS project open in BIDS. If the Solution Explorer is not visible, click on the Solution Explorer tab on the right, or select View > Solution Explorer. This window displays the project name, and all the objects associated with the current project.
Create a Shared Data Source:SSRS data sources can be either embedded in reports, or shared among reports within a project. A shared data source is a separate object from your reports. If or when your data source changes - for example, if the source database moves to a new server - you must only change one shared data source, instead of dozens (or hundreds) of individual embedded report data sources.
Steps To create a shared data source:
- In the Solution Explorer, right-click the Shared Data Sources folder and select "Add New Data Source".
- Give the data source a meaningful name (e.g., ServerName_Database). Note that the data source name cannot contain spaces.
- Select the data source type from the drop-down list. For this example, choose SQL Server.
- Click Edit to enter the connection details.
- In the Connection Properties window, enter the server name, logon credentials, and database. Click Test Connection to check the connection, and click OK on all windows.
Create a Report:You can create your report in SSRS either with the report wizard, or from scratch. To create a report with the wizard:
- In the Solution Explorer, right-click the Reports folder and select "Add New Report".
- On the Select the Data Source screen, select the shared data source you created.
- The Design the Query screen allows you to type or paste your query; or you can click "Query Builder" to build your query in the graphical design. Paste the following query in the Design the Query window:
- On the Select the Report Type screen, select Tabular. Note that the Tabular format is like a simple spreadsheet (or database table), with columns across the top, and the number of rows depending on the rows returned by the dataset. (The Matrix format is like a pivot table.) Click Next.
- On the Design the Table screen, add Color and Category to the Group section; add the remaining fields to the Details section. Click Next.
- Keep the "Stepped" selection on the Choose the Table Layout screen, and click Next.
- Select a style for your table, and click Next.
- Give your report a meaningful name (e.g., Products By Category) and click Finish.
SELECT P.Name , ProductNumber , Color , ListPrice , SC.Name [Category] FROM Production.Product P LEFT OUTER JOIN Production.ProductSubCategory SC ON P.ProductSubCategoryID = SC.ProductSubCategoryID WHERE P.ProductSubCategoryID IS NOT NULL AND P.Color IS NOT NULL ORDER BY Category, ListPrice ASC
- Change the size of the report and report elements with drag and drop.
- Add report elements - such as images - from the Toolbox.
- Format character and number displays (right click and select Text Box Properties).
- Add, move, change, or delete data elements.