Use data from 2 different Query 1

[MAJLI]

I have create a list (query 1) that contains some fields .in the same list (query1)I have added a new list(query 2)and then I have created a master detail and relationship.(date1=date2)
Query 1 contains : account number, name , amount etc(displayed for some filters )
Query 2 contains : Total amount for all account number without any filter .
I want to do a calculated field :amount (query 1)/total amount(query 2)(so the percent)
Is this possible ???
thank you

 

[johnherman]

Create a view (ask your DBA if you don't understand). The view will contain both select statements and the join. The view then becomes a virtual table in Cognos.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[timida]

I also want to know how to use data from 2 different queries
--> see thread401-1137903.
To create a view is not a feasible solution for my situation.

Is there any other way to do this?

kr, timida

 

[KingCrab]

Use a tabular set..

 

[timida]

Here is my situation:
* Query 1 contains the tank levels for a certain day.
* Query 2 contains the deliveries (to this tanks) for a certain day.
In my Report I want to show the tank levels and as detail list the deliveries (I linked the two lists via master/detail relationship). Problem is that I want to insert a calculated field in the report which references to both queries (tank level + total(deliveries)).

When I put the queries together in one SQL, there is no tank level, when there is no delivery to this tank. So I have to use 2 queries. I already tried using an outer join, but this doesn't work well either (very poor performance).

I also tried using a tabular set but that didn't work (message: the queries for the set are incompatible)... maybe I did something wrong.

Are there any other possibilities?

 

[vzak]

Cristal Reports would do that via Global Variable.

Vlad

 

[JKas]

What about a third query? Can you create one and link to both queries?

 

[timida]

It is not possible to insert 2 data items from different queries to one list. The reason is that there is only one query behind a list.
Using a third query as link is only possible when you use the 2 other queries within this query (2 levels). (I already tried this possibility, but didn't work for my situation)

In the meantime I tried again to use a tabset. I was able to make this work... however it was very complicated and I had to use a lot of workarounds and dummy-items. There must be an easier way to do this (at least I hope so).

 

[Draoued]

What if you create a UNION ??

Add dummy Total amount in the first query = Null

In the second query add all the fields from the first query eqals to NULL.

So your UNION will give you all the fields, and you can divide amount by Total Amount.

In the result query of the union , if you do a grouping on Date , it should show you the proper result.

 

[Vivarto]

I think this is reasonably straightforward to achieve (if I've understood this correctly) This is an example of a class of requirement that crops up time and again; that is, how do I show a total and the details on the same row and then perform a calculation along the row such as row amount / total amount - which is your requirement. In SQL you would code this using the OVER key word

SELECT Location, Product, amount, sum(amount) over ()
From table1

Having and aggregate function in a select list normally requires that you group the data but when using the OVER key word you specify the level of grouping to be used within the brackets, (empty brackes indicate total for all rows). If the requirement was to show the percentage product contributed to the total amount for the Location then the query would have to be modified to show this:

SELECT Location, Product, amount, sum(amount) over (partition by Location)
From table1

If there were 100 rows in the table then there would still be 100 rows in the output but the sum (amount) for each location would be placed on each of the rows.

In ReportNet

1) create a column (call it say, Total for Location) in the tabular model that has the form

Total([Amount] FOR [Location])

2) create a second column that calculates the percentage with the form:

[Amount] / [Total For Location]

Once you've got this working you can just combine the two columns in to one.

No need for views or UNIONS - I've found this functionality really useful.

Tom