In this article, we will show you how to create a Bar Chart in SSRS Report Builder without using Wizard. Or say steps involved in creating Bar Chart, or any Chart using SSRS Report Builder.
For this, we are going to use the Shared Data Source that we created in our Early reports and an Embedded Dataset.
To create a Bar Chart or any Chart, we have to open the report builder. To do this, Please open your report manager and click on the Report Builder button as shown in below screenshot
Create Bar Chart in SSRS Report Builder
Once you click on the Report Builder button, SQL Server Report Builder will open with a starting page.
In this example, we want to create Bar Chart in SSRS without using Report Builder Wizard. So, Please select the New Report tab, and then select the Blank Report option
After you select the Blank Report option, the report builder will display the Blank Report. We already explained the Designer Panes in our previous article. So please refer to Create a New Report article to understand the same.
To create a data source, Right-click on the Data Sources folder present in the Report Data Pane. Next, select the Add Data Source option from the context menu
By clicking on that option, it will open the Data Source Properties window to configure the Data Source.
In this example, we are selecting the Shared data Source that we created earlier. Please refer to Create Shared Data Source in Report Manager to understand the creation of Shared Data Source.
To create a dataset for this report, Right-click on the Datasets folder present in the Report Data. Then select the Add Dataset option from the context menu.
It will open a New window called Dataset properties. Here select the first option to select the Shared Dataset that we created earlier. Or by selecting the second option, we can create a new dataset also.
For now, we are selecting the second option. I suggest you refer the Create a New dataset in Report Builder article to understand the steps involved in creating a shared dataset.
SQL Query that we used in our Dataset is:
-- Create Bar Chart in SSRS Report Builder SELECT Cat.[EnglishProductCategoryName] AS ProductCategory, SubCat.[EnglishProductSubcategoryName] AS ProductSubCategory, Prod.EnglishProductName AS ProductName, Prod.Color, Fact.OrderQuantity, Fact.TotalProductCost, Fact.SalesAmount, Fact.TaxAmt, Fact.[Freight] FROM FactInternetSales AS Fact INNER JOIN DimProduct AS Prod ON Fact.ProductKey = Prod.ProductKey INNER JOIN DimProductSubcategory AS SubCat ON Prod.ProductSubcategoryKey = SubCat.ProductSubcategoryKey INNER JOIN DimProductCategory AS Cat ON SubCat.ProductCategoryKey = Cat.ProductCategoryKey
Let me check the fields that the Dataset is returning.
Within the Report Builder, please select the Insert Tab to insert the Chart in to report designer.
Please click on the down arrow below the Charts button. Next, select the Insert Chart option from the Menu to add the table to the report region.
Drag and drop a Chart to the Data region. As you can see from the below screenshot, by default, it is displaying the Bar Chart
When you drag and Drop the Chart from Insert Tab to Design space, a new window called Select Chart Type will be opened to select the required chart from the available once. In this example, we are selecting a 3D Column Chart.
Once you click the Ok button, a Three-dimensional Column chart will display in the design region with dummy data.
Click on the empty space around the SSRS Column Chart will open the Chart Data window
In this example, we will create a Column chart for the Sales Amount and the Total Product Cost of Products divided by Product Color. So, Drag and drop the Sales Amount and Total Product Cost column from dataset to chart data values and Color in the category group.
Let me change the report name to Bar Chart using Report Builder, and click the Run button
Once you click the Run button, following the Bar Chart will generate.
Let me change the Chart to regular Column Chart, and Format the Chart for a better look. I suggest you to refer Create a Column Chart, and Formatting Column Chart to understand the steps involved in changing the Chart types, and adding Data labels to charts.
Next, Please click on the Save button to save the report in the report server.
After you click on the Save option, a new window called Save As Report will be opened. Here you have an option to select the local file system or the Report Server. Let me select the Report Server
Let me open my Report Server to show the newly created Bar Chart Report. Here, If you want to Run the report, or to see the report preview, then you don’t have to access the Report Builder or BIDS. Just click on the Bar Chart will run the report. Let me select the report that we saved previously and click on it
Now you can see the Bar Chart inside the Report Server