Prompts in multi-Report query

[Infosauger]

Hello,

i want to create a roport following style (sql)

select a,b,f(c)
from x
where filters
and a in (select a
from x
where filters
and b = y
group by a,b
having f(c) = z
group by a,b;

so for all a having in b value z in f(c) display all b with f(c). I have to use recordset for the sub-select and in both reports there are the same filters. In the filters are (catalog-)prompts.
The problem is, that Impromptu asks for the prompts twice (for each report). Does anyone know a solution for this problem?

 

[griffindm]

InfoSauger,

Your example is a little cryptic. Taken literally, why not forgo the subselect and just do:

select a,b,f(c)
from x
where filters
and b = y
group by a,b
having f(c) = z;

as both selects pull from the same table and have the same filter. Perhaps if you give real-world information in your example it will become clearer.

If you have a prompt filter in your final report, and a filter in your dataset report, Impromptu will ask for both prompts. It has no way of knowing you're asking for the same information twice.

Give a little more detail on the report and someone can probably assist.

HTH,

Dave Griffin ;-)

 

[Infosauger]

Here is an real world example:
(sorry for any language mistakes)

for each customer who has bought more than 10 parts of product 1display all products with sum.

select customernumber, partnumber, sum(quantity)
from customer c, transaction t
where c.customernumber = t.customernumber
and c.customernumber in (select customernumber
from customer c2, transaction t2
where c2.customernumber = t2.customernumber
and t2.partnumber = 1
group by 1,2
having sum(quantity) > 10)
group by 1,2;

In your solution i would get just the customernumber and partnumber where sum(quatity) is more then 10.
e.g.:
customernumber partnumber sum(quantity)
1 1 11
1 2 9
2 1 9
2 2 11

should deliver:
1 1 11
1 2 9

not (your solution):
1 1 11
2 2 11

 

[griffindm]

Are there dataset prompts for both the partnumber and the quantity? The problem is definitely clearer now. Language is fine.

It looks like the final report should have no prompts at all, only a filter of customernumber IN (Dataset) ..., unless there are other prompt items in your generic 'filters' statement in your first post.

You could also do this with a cascading report picklist prompt. Rather than a Dataset, use a prompt within the IN clause. Make it a report picklist prompt and use the Dataset report, selecting the customernumber as the comparison column. I apologize if this is what you are already doing, but it is not completely clear.

Expand a little on the duplicate prompt problem.

HTH,

Dave Griffin