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
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
Then select the SalesAmoutPecentage as shown in the above
graph and change the graph type to Line by right clicking and selecting Change
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.