How to use data items from different crosstabs

[21lakegirl]

I have a report with four crosstabs, each using a different query because they are using different packages.  I need to create a total value  from the four crosstabs at the end.  It looks like I should be joining the queries, but I get lots of errors when I try to run it. Any help with this, or should I use a different approach?  thanks

[MFGF]

Hi,

What sorts of errors are you getting? Can you give examples?

If I had to guess, I'd wager that the columns you are trying to join are different data types?

Alternatively, perhaps one or more of the sources is not relational? You cannot join queries from packages based on an OLAP source.

Can you give is more info?

Best regards,

MF.

[21lakegirl]

It is OLAP.  So, even though Report Studio has these nice union and join objects, I can't use them?  Is there any other way to use data items from different queries within report studio?

[21lakegirl]

I should have mentioned the problem when I use the union object.  I only get the first query in the union.  So, if I union A and B, only A data items show up.  Seems to me that the dimensions are the same, and I just include one measure from each query.  I'm grabbing COGS from A and Revenue from B.  I want to add them in the resulting query.  Seems simple enough.

[Lynn]

I have a report with four crosstabs, each using a different query because they are using different packages.  I need to create a total value  from the four crosstabs at the end.  It looks like I should be joining the queries, but I get lots of errors when I try to run it. Any help with this, or should I use a different approach?  thanks

When you create a report you need to select one package, so I'm not sure how you create a report with multiple queries based on different packages. What am I missing?

I should have mentioned the problem when I use the union object.  I only get the first query in the union.  So, if I union A and B, only A data items show up.  Seems to me that the dimensions are the same, and I just include one measure from each query.  I'm grabbing COGS from A and Revenue from B.  I want to add them in the resulting query.  Seems simple enough.

Creating a query that returns two metrics for the same dimensions should be something you can do easily in a single query. Sounds like there is something funky in the way your data is modeled perhaps?

[21lakegirl]

The union is on two dimensions, versions and time.  The data types are the same.  I should have explained the I can create multiple queries in a report, using different packages, because the different packages were put together in Framework.  So I have a big package made up of multiple packages available to me in Report Studio.  However, I don't seem to be able to use data items across these packages.  I can only create separate  crosstabs from each.  I thought creating a query with a union would be the answer.  But when I do the union, only the first package I select shows up in the resulting query.

[MFGF]

Hi,

Lynn is right - you have one package. Your package contains references to multiple cubes, but it's still one package. :)

With OLAP sources, you cannot join queries. You can UNION them and you can link them via master/detail relationships, though.

You do need to be aware that OLAP sources bring in members, identified by MUNs - even though by default you see the captions of the members displayed when your report runs. Why not take things right back to basics:

Create a new crosstab report based on your multi-cube package
Add two extra queries in the query explorer window
Drop a UNION onto Query1 and use Query2 and Query3 to feed into this UNION
Go into Query2 and drag the level of a hierarchy and a measure from one cube into the query
Go into Query3 and drag the level of a hierarchy and a measure from a different cube into the query
Go into Query1 and drag the two items from the two items from the Union into the query
Go to your page and drag the two items from Query1 into your crosstab

Do you see the results you expect? 

[21lakegirl]

That's what I've been trying to do.  I can't how I could mess that up, but I always only get the metric from the first cube in the union instead of seeing both measures.

[MFGF]

When you UNION two queries like this, the name of the resultant column for the metric (for all the columns, in fact) is based on the column name(s) from the first query.

Lets say your two queries contain the following data:

Query 1:
PRODUCT      QUANTITY
A      20
B      30
C      10


Query 2:
Prod      Qty
D      15
E      25
F      10

Your resultant query after these are UNIONED together will have names based on the first query's names, so the results will be:

PRODUCT      QUANTITY
A      20
B      30
C      10
D      15
E      25
F      10

Hope this helps!

MF.

[21lakegirl]

Thanks!  That works.  I didn't realize that each metric wouldn't have it's own name.  If I would have just printed it out, I'd have seen the name there.