prompt for grouped records

[gurralakrishna]

Set    Part number
A   X01
   Y01
   Z01
   P02
   Q03
B   X02
   Y02
   Z02
   P01
   Q01
C   X01
   Y02
   Z01
   P01
   Q02
D   X01
   P01
   Z01
E   X01
   Z01

F       X01
   Y01
   Z02
   Q01

Above is my report. I have prompt on Part number. Prompt having multi select. If I select X01 & Z01 Part numbers in prompt  the report should be display like below

Set  Part number
A   X01
   Y01
   Z01
   P02
   Q03

C   X01
   Y02
   Z01
   P01
   Q02

D   X01
   P01
   Z01

E   X01
   Z01
Could you please help me how  to create prompt on Part number  to achieve above requirement.

many Thanks in advance
Regards,
Krishna.

[MFGF]

Hi.

You need two queries in your report - joined together. You will define your prompt against one query containing set and part number, then join this to your list query based only on set. This you will end up with the sets (and all their part numbers) that contain the part numbers you selected in the prompt.

Regards,

MF.

[gurralakrishna]

Thank you very much.

Still i am facing one problem. If i select two part numbers  in prompt i am getting all components of each set even one selected part number is there. but the report should not display any of the part number of each set is missing in the selected part numbers prompt.

Please help me.

many thanks in advance..

Regards,
krishna

[MFGF]

Hi,

Sorry, I can't follow what you mean. The technique I described allows you to select part numbers in your prompt, then show those sets (with all part numbers for those sets) which contain both part numbers.

If it's not working, can you provide details of your two queries, the prompts/filters you are using, and how the queries are joined?

MF.

[Bark]

I don't understand the issue. You should have two queries: 1 for the prompt and 1 for the table you posted.

PromptQuery: 1 DataItem --> [Part Number]
Report Query: 2 DataItem --> [Part Number], [Set]
                       1 Detail filter --> [Part Number] in ?pPartNumber?

It should display only those rows that contain the selected part numbers, have you got something different in your report?

Regards,

Bark

[gurralakrishna]

My report designed as follows.

1. Created Query1 and applied the prompt filters to Query1
2. Copy the Query1 and removed the prompt filters from copy of Query1
3. Join two Queries based on set and apply link Query1(1:1) :: Copy of Query1(0:n)
4. Result query joined to the report.
5. created one dataitem1 for count of part numbers in Query1
6. created one dataitem2 for count of parameter of part numbers in Result Query
7. created dataitem3 in result query 
    case   
       when dataitem1= dataitem2 then part numbers
       else ('null')
    End
added dataitem3 to the report

In my case I have seleted two part numbers in the prompt,my report showing all part numbers of each set even one selected part number in the part numers.But I want to display if the selected two part numbers in the set of part numbers,if any of the selected part numbers is not in set of part numbers no need to display in the report.

Many thanks in advance...

Regards,
Krishna.

[Bark]

I don't see why you need to do it that complicated, why the join??

In any case, I think the mistake in your definition is the "0:n" in the join as it is forcing it to be an outer and, therefore, it will never filter.

Anyway, could also you try the definition I gave you and see if solves the problem? At first sight, it looks much simpler and if it gives the expected results, job done :)

Regards,

Bark

[gurralakrishna]

Thanks Bark.

In my first post I defined how to display the report output.According to your suggestion the report should display only selected part numbers.But my report display all part numbers even unselected part numbers from the group of part numbers along with set.If any of the selected  part number missed from group of part numbers no need to display in the report out put.

Regards,
Krishna.