Value Prompt and Substring

[joecog]

Hi,

I have 2 value prompt, one is Region and Customer
Eg:

In the Value Prompt for Region will have a list as below :

Region
1
2
3
4

When user select Region as 1, Customer value prompt will only show the list of customer that starts with 1

I had set Region with Auto Submit as Yes and Customer using Cascade Source "Region parameter" but report still show full list of customer no matter which region that the user select.

Any idea to solve this?


Joecog

[PRIT AMRIT]

I believe there is no grouping between Region & Customer. When u write the SQL in DB does it show like below?

Region      Customer
1               1-A
                 1-B
                 1-C
2               2-A
                 2-B
                 2-C
3               3-A
                 3-B
                 3-C

Thanks

[Gopinath]

In your Customer prompt query include a filter like
[REGION_DATA_ITEM] = ?Region?

[joecog]

Hi Gopinath,


I had include the detail filter in the Customer Query Prompt, when I run the report, system prompt to select Region but the Customer prompt still show a full list.

Any idea why there is such problem?


Joecog

[PRIT AMRIT]

Did u check the grouping as I explained in my last post?

[joecog]

Hi Prit,

Yes, I had checked the grouping.
If I write :

region = substring(customer,1,2) then will show all the customer under this region.

regards,
Joe

[PRIT AMRIT]

u mean you tried with the below SQL and getting the desired output in database?

Select a.region, a.Customer
from a
group by a.region, a.Customer

I am not talking about Filter in your query, i think the below what are you saying is a Filter in your report query?

region = substring(customer,1,2)

[joecog]

Hi Prit,

Yes, if is in database, both region and customer is under same table.

[joecog]

Hi,

Anyone know how to solve this ?

Is there any other way beside using substring?

Joe

[167505]

this is strange ..

Are you sure that the customer and region dataitem comes from the same table or if it is different does it has joins between them..

as others suggested go to the cusomer prompt query and apply the region filter and run the tabular data for that customer query alone. it will ask you to select the region value..select region 1 and check whether it fetches only the customers for region 1.

Regards,
Nag

[joecog]

Hi Nag,

Sorry for giving wrong information.
I think the region and customer is not under same table.
When I select region 1, it will not reflect to only customer under region 1.

So, how do I link both of them?


Joe