In this article, we will show you, How to create 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.
In order to create a Bar Chart, or any Chart, or a Report 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 be opened with a starting page as shown in below screenshot.
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 as we shown below
After you select the Blank Report option, report builder will display the Blank Report. We already explained about the Designer Panes in our previous article so please refer 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 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.
This 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 3D Column Chart as shown in below screenshot.
Once you click Ok button, Three-dimensional Column chart will be displayed in the design region with dummy data as shown below.
Click on the empty space around the Column Chart will open the Chart Data window
- Values: Any Numeric (Metric) value such as sales amount, Tax, Total Sales, Customer count etc. All these values will be aggregated using an aggregate function (Sum, Count etc) because we are grouping them with the category group items.
- Category Group: Please specify the Column name on which you want to partition your Column Chart.
In this example, we will create Column chart for 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 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 be generated.
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
Thank You for Visiting Our Blog