Using result of one query as value in calculation on another query.

[hem852]

Hi,

Forgive me if this is a really stupid question! My report studio skills are poor at best.

I have a report which has two queries.

Query A simply returns a single numeric value telling me the number of weeks to date that have passed in the current company calendar year.

Query B is used to produce a crosstab report which shows a names of 'sites' down the left and 'year to date' along the top.

What I want to be able to do is add a column to the right of the crosstab which takes the value in the 'year to date' column and divides it by the value returned by Query A (to give me a weekly average). Any ideas?

There is no way to join Query A and Query B but then they don't need joining?

It seems like such a simple thing to ask (take that number and divide it by the result of that query) but I am rapidly finding out Report Studio doesn't like doing 'Simple'.

Any help would be greatly appreciated.

Regards
John  ???

[vij]

Hi  hem852 ,
you can put "Union" in between these two queries .

regards,
vij

:)

[angela]

Here's how I'd do it if it's relational data - not sure about if it's a cube.

- Add another data item to Query A called JoinItem and put a 1 inside it.
- Add another data item to Query B called JoinItem and put a 1 inside it.
- Create a Query 3 using a Join and join A to B on JoinItem.
- In Query C bring in all your data items from Query B and the single value you need from Query A, do your calc in Query C then include it in your Crosstab.

NOTE: you then need to change your Crosstab to use Query C.  Also, if you were doing any Calculated items in Query B they'll get messed up; all calcs need to be done at the highest query level for consistent results.