Design A Hybrid Report in SSRS

Hybrid reports are the reports which have two types of graphs using different axes within the same graph .

For example.

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.

]]>