prompt info

[CoalEs]

I have one QS with Product id and Product Name in FM model. I publish QS2 with only Product Name.
How I should to set prompt info for Product Name in FM, to achieve this:

when user make filter on Product Name, sql results must be

Code Select Expand

where Product id = 2 ?
I tried to set
Display Item Reference to [Product Name],
Filter item Reference to [Product id],
Use item Reference to [Product id],

but it not works.

[MFGF]

but it not works.

What does it do? Do you get a filter based on Product Name? Do you get a prompt displaying Product ID? Do you get an error?

If I was doing this, I would leave Product ID as a query item in the query subject and set the "Is hidden" property to True.

MF.

[CoalEs]

I don't get an error, but I don't get what I want.
Ok, lets qs have two query items
Product Name and Product Id (is hidden = true)

I want to get filter based on Product Name and filter on Product Id in sql code.
How should I set Display Item Reference , Filter item Reference , Use item Reference ?

[CoalEs]

I created new FM model and import [great_outdoors_sales].BRANCH.
Then I create new QS with BRANCH_CODE (indexed field) and ADDRESS1.
In Prompt Info for ADDRESS1 I set "Filter Item Reference" = [great_outdoors_sales].BRANCH.BRANCH_CODE and I set "Use Item Reference" = [great_outdoors_sales].BRANCH.BRANCH_CODE.
I published this QS.
In Report studio I created List with ADDRESS1, then I created filter on ADDRESS1.
Sql for Query1 looks so:

Code Select Expand

SELECT DISTINCT
    "BRANCH"."ADDRESS1" AS "ADDRESS1"
FROM
    "GOSALES"."BRANCH" "BRANCH"
WHERE
    "BRANCH"."ADDRESS1" IN (
        '202-2-3 Hyakunincho' )

I want see this:

Code Select Expand

SELECT DISTINCT
    "BRANCH"."ADDRESS1" AS "ADDRESS1"
FROM
    "GOSALES"."BRANCH" "BRANCH"
WHERE
    "BRANCH"."BRANCH_CODE" IN (
        '001' )

what am I doing wrong?

[MFGF]

Can you detail the steps you are taking in the report which are resulting in this filter? How are you defining your prompt?

MF.

[CoalEs]

File -> New -> List.
Then I drop ADDRESS1 into List.
Then I click by column ADDRESS1 to select it.
Data -> Filters -> Create custom Filter.
Then I set "Specific values" for ADDRESS1. I move '202-2-3 Hyakunincho' into "Selected values" list. And click ok.

[CoalEs]

If I set filter on List Query like ADDRESS1 = ?qwe?
Then sql becomes:

SELECT DISTINCT
    "BRANCH"."ADDRESS1" AS "ADDRESS1"
FROM
    "GOSALES"."BRANCH" "BRANCH"
WHERE
    "BRANCH"."ADDRESS1" = :qwe:

[MFGF]

Hi,

Aha! The Promptinfo property in FM is specifically for automatically generated *prompts*. If you select the Address1 column and press the "Build Prompt Page" button, you should get a filter based on Branch Code, and a prompt which displays Address1 values and passes the corresponding Branch Code(s) back to the filter.

Cheers!

MF.

[CoalEs]

Oh that's it! Thanks!