Developing Dashboard in SSRS from multiple Stars

Developing  dashboards and reports across multiple process   can be tricky in some cases,in this post i will develop a dashboard in SSRS which uses the data from multiple stars to show the sales performance against the goals.

The  key to the solution is the concept of dimensional conformance  which allows for drilling across different processes  .In order to drill across the basic requirements are the structure and the content  of the dimensions should be same .

For this demo i'm using Adventureworksdw2012  database and the dimensions i will be using are DimDate,DimSalesTerritory and a fact FactResellersales. I have created  another star for Sales Goals to show the actual sales against goals.This star contains the same dimensions  but the granularity of the salesgoals facts for Date Dimension is year and for Sales Territory is Country which doesn't affect the dimensional conformance as i will be aggregating the both stars at Year and Country level .Also for fact Reseller sales when you aggregate the dataset at the country and Year some of the data for sales will be missing as there are no sales in that particular regions for that period so i have densified the data so that the sales value will be 0 for  countries, which haven't got sales for that period.

The  data set  for Actual Sales  aggregated at the country and Year looks like below

The Data  for the SalesGoal looks Like below

Now I have all the datasets for my demo,In the next few steps i will be showing how to implement this solution in SSRS.

The Key to the solution in SSRS is using Lookup to join  on multiple fields across two different datasets  in the reporting region.As we know lookup only takes two fields for joining ,you need to add some more logic  to join on multiple columns .There is a great article of Devin Knight   on his Blog for the same concept .


I have created to shared DataSources and  Datasets for the two Stars  as shown below

Than  i have used those data sets for the Report  ,in those individual data sets i have created a calculated field which the look  to join two different datasets .

The first step is to create a calculated field,for that right click the dataset , go to the fields section and add calculated field  as shown below

In the Calculated field i have created an expression which allows for multi join on year and country fields as shown below

I have created a similar calculated  expression on the other dataset as shown below

Now i have the pre requirements ready ,In the next steps i have  added a new report and tablix  on to the data region and  added   fields for  Country, Actual Sales  from dataset1  for ADW as explained previously, and added  an expression for Sales Goal for which i have given the description how to build it  in the next section  and  also  added an  indicator  for SalesTrend as shown below

In the Sales Goal field , i have used an expression to lookup the sales goal  which is on the second dataset2 based on the calculated  fields, which  i have created earlier .

The final report  looks like below