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
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.
Quote from: CoalEs on 05 Jul 2013 05:50:17 AM
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.
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 ?
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:
SELECT DISTINCT
"BRANCH"."ADDRESS1" AS "ADDRESS1"
FROM
"GOSALES"."BRANCH" "BRANCH"
WHERE
"BRANCH"."ADDRESS1" IN (
'202-2-3 Hyakunincho' )
I want see this:
SELECT DISTINCT
"BRANCH"."ADDRESS1" AS "ADDRESS1"
FROM
"GOSALES"."BRANCH" "BRANCH"
WHERE
"BRANCH"."BRANCH_CODE" IN (
'001' )
what am I doing wrong?
Can you detail the steps you are taking in the report which are resulting in this filter? How are you defining your prompt?
MF.
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.
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:
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.
Oh that's it! Thanks!