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)

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
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
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.


No comments yet... Be the first to leave a reply!