Design A Hybrid Report in SSRS
Hybrid reports are the reports which have two types of graphs using different axes within the same graph .
In the above report, Product cost is on the right axis while Sales Amount Percentage is shown on the left axis, so that viewers can easily compare Product Cost vs Sales Amount Percentage.
Below is the query which is required for the above graph.
WITH TotalSales ( SumTotal) AS( SELECT SUM(S.SalesAmount) FROM dbo.FactInternetSales S ) SELECT T.CalendarYear,SUM(S.TotalProductCost) TotalProductCost, 100 * SUM(S.SalesAmount)/ MAX(TotalSales.SumTotal) SalesAmountPercentage FROM dbo.FactInternetSales S INNER JOIN DimTime T ON S.OrderDateKey = T.TimeKey CROSS JOIN TotalSales GROUP BY T.CalendarYear ORDER BY T.CalendarYear
You can use the AdventureWorksDW sample database to get the above data.
To start the report , create an SSRS project and add a data source and a data set with the above query.
Next, add a chart control to the report. So your report should look as below:
Then drag the Calender Year to the X axis and TotalProductCost and SalesAmountPercentage to the graph area and your report should look as below:
Then select the SalesAmoutPecentage as shown in the above graph and change the graph type to Line by right clicking and selecting Change Chart Type.
You will see following configuration, if you look closely you will see that in the chart data and chart type has changed.
Right-click and select Series Properties and you will be taken to the following dialog:
From here you can select the Secondary option from the Axes and Chart Area.
Finally, you can change the marker to complete the process.