DMR Reporting

[cfsindorf]

Can you do a join in report studio against 2 cubed based queries to create a third query?  All I have been able to find says no I just want to make sure.

The next question is, in a cross tab report I have measures as columns and dimensions from a cube as its source.  I need to create a new measure that ONLY uses data from a certain year then takes that number and adds it to data from 2 other measures for current period.  Any ideas how to do this??

Tks

Craig

[cognostechie]

DMR or Cube ?

Yes, you can. I created a report like that some time back. Don't remember the exact steps but remember inserting a Crosstab inside another Crosstab as a column. Create the 1st crosstab from 1st cube , 2nd crosstab from 2nd cube then create another Crosstab which will be the join of 1st two. To achieve this, first you have to publish a package that contains both the cubes.

For your 2nd question, create 3 columns for those 3 measures using a condition like:

If [Date].[Year] = [Selected Year] then [Mesaure] else 0

Use the same technique for other 2 columns then create a 3rd column to add all 3.

[cfsindorf]

It is a cube.  I need to have a query based on stuff from one year joined to another based on another joined on state.  Every time I tried it crapped out on me.

[cfsindorf]

This is what I get in my join of 2 report studio reports using a cube as a source:

QE-DEF-0360 The query contains a reference to at least one object '[2010].[Plant]' that does not exist. Possible cause is the use of 2-part reference to a model query item, which is a deprecated feature

query 1

plant
cases
filter year 2010

query 2
plant
gallons
no filter

[cfsindorf]

Never mind cube to cub join it is not permitted in 8.4.  Just found it in print.

So now I am back to creating a calculation as a column in a crosstab that takes the data from a prior year and uses that value to add to a value of current year

Errrrrr

[cognostechie]

That's NOT true. I found the report I had created from 2 cubes and I just ran it in 8.4 and it works. See the attachment. As you can see, the version is 8.4 and the data in the 1st column matches with the 3rd column to prove the join.

Anyway, it's up to you to give up or pursue. I will not post anything more on this.

[MFGF]

Just to clarify, are you saying you added a query join and it worked, or did you use a master/detail relationship?  The latter should work, but not the former (unless I'm losing my barbles big-time!) :)

MF.

[cognostechie]

I think it might be me losing it !!

You are correct, I posted the answer out of my vague rememberance and ran the report but did not open it to check it. It is using a Master Detail relationship.

..but what the hell ! It's a Friday so it's allowed  ;)

However, it's still a solution, right? Depending on how the data is structured, a Master Detail can still work as a join/union on 1:1 basis too !

[cfsindorf]

SO how would you do a master detail for a calc?

[cognostechie]

Yeah, I see your point. The Detail query is filtered by the Master so it won't allow creating a calculation involving both of them.

If you need to create a calculation that involves measures from the same cube, you can still do it.