Display the unfounded parameter values in a list

[afzaki7]

Hi all
my question seem to be simple but i can't get what i want
first i have prompt page has text box prompt that allow multiple value so the user can insert a lot of values in it
second i have the page has a list that displayed data filtered by the inserted value in the text box prompt
[ID] in (?textboxParam?)
what i want is to display the not founded values in another list
ex :
user insert  1,2,3,4 in the text box prompt
the list displayed   ID
                             1
                             2
now i want to display the 3 and 4 in another list called [not found ID's]
                                                                                              3
                                                                                              4

any ideas

[BigChris]

I can't think of a way to do that straight out of the box. You could perhaps have the data in a spreadsheet rather than your text box prompt, then create a new package to include your spreadsheet and put an outer join in. That way you should be able to get the full list of your values and from that you should be able to identify the ones that don't match.

[afzaki7]

thanks for your reply BigChris
but unfortunately i can't make any change in the report
i am not the report maker and the customer need is like what i explain before

thank you again
and let's hope someone has some way to do that 

[Lynn]

You need to somehow generate a query that has all the values the user enters in order to compare it to the results. Are the values being entered something that exist in a dimension table? Can you somehow fabricate a list of all the possible values a user can enter?

Is it really integers like 1,2,3 etc. as your example implies? Is there a fixed number of responses a user can provide? If you share some explanation of what the domain of possible values can be then someone might be able to help you use your date dimension or something else to "fabricate" this list of values that may or may not exist in your facts.

[afzaki7]

Thanks for your reply Lynn

actually the user want to insert the values by his hands in the text box prompt
also the values not integers i just wanted to make the question simple.
the values is codes like CSCZ17 

i may ask another question may help me ...
can i put the inserted value in the text box prompt in a query ?
i mean the parameter values can i use them in a query Dataitem ?

or what you can suggest for me to reach what i want ?
thanks in advance

[Lynn]

Thanks for your reply Lynn

actually the user want to insert the values by his hands in the text box prompt
also the values not integers i just wanted to make the question simple.
the values is codes like CSCZ17 

i may ask another question may help me ...
can i put the inserted value in the text box prompt in a query ?
i mean the parameter values can i use them in a query Dataitem ?

or what you can suggest for me to reach what i want ?
thanks in advance

Where do those codes exist in your data source? Are they only present when connected to a fact of some sort and therefore don't exist anywhere else but the mind of the user when there are not facts for them? Or is there a dimension table somewhere that lists the complete domain of possible codes?

[BigChris]

Thanks for the explanation - it all helps to get you the right answer.

Let's assume that the code you're talking about is a customer code and you're looking for sales quantities. If the user enters a valid customer code, in this case CSCZ17, and there have been no sales, you should still be able to list that customer. You'd just need an outer join between the customer and the sales tables.

The problem comes when the user enters an incorrect customer code, say CSCX17. If that customer doesn't exist in your database there's no way for the report to bring the data back (someone please correct me if I'm wrong).

Personally, I'd make the customer prompt a value prompt so that the user can ONLY select valid customer codes.

[afzaki7]

Thanks BigChris

but actually i have more than million record in the database  (for the code).
so i can't display all those values in a value prompt it doesn't make a scene

so that the user want to insert what he want in the text box prompt

and yes the user may insert values of codes not exist in the database :(

[BigChris]

Hi - I agree that you can't display all million+ records, but you could make it a search and select prompt based on those codes. The user could key in the start of a code then select the values that they want. That would stop them from entering invalid codes

[afzaki7]

Thanks BigChris

I really appreciate your respond . But in fact what the user do is copying the codes and paste it in the text box prompt
he may copy 300 codes or more at once and then insert them to the prompt

he can't do that in the select & search prompt because it allow only one by one search

[Lynn]

Thanks BigChris

I really appreciate your respond . But in fact what the user do is copying the codes and paste it in the text box prompt
he may copy 300 codes or more at once and then insert them to the prompt

he can't do that in the select & search prompt because it allow only one by one search

If you use the external data feature they could load a spreadsheet containing all the values so you can have them as separate entries in a query subject rather than as a concatenated list of parameters.

Then you can outer join the parameter query with the data query to determine what is or isn't found. This would be done as a report author and not include a framework manager change to the package.

[afzaki7]

Hello guys here what i did

i created a Native SQL query with this script

with test as (select #csv(array(promptmany('CodeParam','Token')))#col from dual)
select regexp_substr(col, '[^,]+', 1, level) result from test
connect by level <= length(regexp_replace(col, '[^,]+')) + 1

1-it will give me a dataitem called result

2-in the same data item write  :   replace(replace([SQL1].[RESULT], ''''  ),' ') 

3- add filter to the query like that   :  [RESULT] not in ([Main Query].

Code Select Expand

)

thanks


[the6campbells]

Think of it like this, you need to create a set (of rows) or predicates which you can then use to use to drive an operation (ie. intersect, = ANY (same as exists) etc) to determine which values do not exist etc. Look at 4.5.2 in https://www.redbooks.ibm.com/redbooks/SG248121/wwhelp/wwhimpl/js/html/wwhelp.htm re how to transpose prompt values into a set.