CODEWITHSUNDEEP
ssrs-2008mdx
user842033
user842033

Reputation: 1

Joing several MDX query results in a single report

I use MS SQL Server 2008 R2.

I've got the problem, please, excuse the long explanation.

We've got the SSAS cube. It is under development at this time, but it is partially working and can be accessed through excel.

There are projects: hierarchycal parent-child dimension There are resources assigned to the project (e.g. man-hours, building materials, technic): dimension with resource types, fact M2M table ProjectId-ResourceId-UnitsCount-Cost There are milestones for the projects: dimension with milestone types (few are defined), M2M fact table: ProjectId-MilestoneId-...milestone dates: planned/actual start/finish

This is a simplified schema.

I need to create a MS Reporting Services report with the following columns:

  1. Project Hierarchy
  2. several columnts with the pre-defined and "hardcoded" resource type amount. e.g the business wants to see the columnt with man-hours spent, and concrete consumption in cub-meters. Thse two clauses can be hardcoded in the query.
  3. several columns with the pre-defined and "hardcoded" milestone type dates

this is a simplified schema too, more columns with other dimension slices are needed...

The problem is that i cannot find an elegant way to create this report.

in my current version, i have to create 2 datasets and query the resouce and milestone data in separate mdx queries. then i need to use RS-lookup function to join the data in report outcome.

Please acvise:

  1. is there a possibility to query this data in an single mdx query. when i try something like this: union({{[Dim Resource].[Measure].[man-hour]} + {[Dim Resource].[Measure].[cub-meter]}}, {[Dim Milestone].[Milestone Type].[ProjectStart]}) i've got "different dimensionality" error. Any workarounds?
  2. if i need to output a formatted value like: "X 'man-hour' / Y 'cub-meter'", i have to use lookup func to get both parts of the formula - any better way?
  3. can i query this data any other way?

Please, indicate the direction of googling

or... should i just query the data from the source tables (this is allowed by security restrictions) with SQL

thank you in advance

Upvotes: 0

Views: 300

Answers (1)

Magnus Smith
Magnus Smith

Reputation: 5963

Perhaps create a new 'virtual cube' to contain data from both of your existing cubes, then query that one.

Upvotes: 0

Related Questions