Search code examples
reporting-servicesssrs-2008-r2

Use 2 Datasets inside a Matrix

I'm creating a report that shows how many sq ft my company worked during a time period and what's the cost per sq ft. I have these two Datasets:

ServiceProviderSqFt

  • ServiceProviderID
  • ServiceProviderName
  • Total
  • Month

CostSqFt

  • ServiceProviderID
  • ServiceProviderName
  • Cost

So the matrix I created looks like this:

ServiceProvider Expr(Months) Cost Per Sq Foot
ServiceProvider Sum(Total) missing

The word missing is where I'm having problems. I need to put over there the cost for each provider, so looks like this:

Service Provider Jan Cost Per Sq Foot
Provider 1 250 1.10

How can I achieve this?

Solution

  • If you have to use 2 separate datasets, you can use the Lookup function. There are many resources on that out there. The best option with SSRS is to combine those datasets at the database level as subqueries so that you can work with just the one dataset in the report. Hopefully this points you in the right direction.