Use 2 data sets in one chart in SSRS

Question

Is it possible to have 2 data sets and display the data for them in one chart on an SSRS report or will I need to combine the data sets?

I have number of calls answered in one dataset and number of calls missed in another and want to show them both in a graph. The data is held in different areas which is why I didn't create it in one data set to start with.

Raffinose

You'll need to combine the datasets using a common axis like time. – Duchamp

Thanks, yes both my x and y axis do measure the same so I will look at combining the dataset to create one query – Raffinose

@JaazCole - nope, no need to combine the datasets (though it would certainly make OP's life a lot easier). See my answer below. – Smashup

Answer 1

This may not work for all types of charts, and does require your datasets to be constructed with common axis values:

• Select the chart so that the "chart data" panel appears.

• Click the green "+" above the "Values" pane. You'll see a list of fields in the dataset bound to the charts data region. Rather than choosing any of those, choose "Expression" on the very bottom.

• Add a value from your other dataset - note that it will probably need to be wrapped in an aggregate function, like SUM or FIRST. For example:

  =sum(Fields!YourField.Value, "2ndDatasetName")

• All datasets will need to have common axis values, otherwise you're in for a bad time. If you need to split them up, you can have TWO sets of axis values for each orientation (vertical, horizontal); to change which axis position is used, bring up the "Series Properties", choose the 2nd tab on the left ("Axes and Chart Area"), and choose the Primary or Secondary axis accordingly.

Smashup

Thanks, I have tried this, haven't got it working yet, but it has let me access the other data sets so im hoping a bit more playing it may work. – Raffinose

@Raffinose Did you ever get this to work? I'm trying something similar, but with a pie chart, and it is not working correctly. I have a question open about it here #36895320 What I'm seeing now though is that my second data set only has one row, but it shows up the same number of times as my first data set. – Intellection

Zack no I don't think I did get this one working think I combined the datasets in the end, although the answer I ticked did let me access the other dataset but cant remember what the next error I got was that stopped me from getting it up and running so worth testing and see what happens for you. – Raffinose

Answer 2

You can use Lookup function to join two data-sets on common field (i.e. DateTime) and then drive part of the chart from first data-set and part from the other. It's like INNER JOIN in T-SQL. I tested it in my project and it works.

Source: http://www.techbrothersit.com/2016/01/how-to-display-data-on-single-tablix.html

Schizomycete