Creating a Value Prompt on specific values

[bud659]

Hi everyone,

I am having a tough time trying to create a report for specific customers.   I have 500+ customers and I want the user to select from a List Box on only 3 of the customers.   But,  each customer has a ID and the report needs to be filtered by Customer ID,   however display the 3 Customer names in the List box.  Is that possible?

Example of Data:

Customer ID          Customer Name
1                          Customer A
2                          Customer B
...500                   Customer ZZZ


The List Box should display only 3 specific customer names but filter on their ID.   

• Customer  F
• Customer  G
• Customer  H

[MFGF]

Hi everyone,

I am having a tough time trying to create a report for specific customers.   I have 500+ customers and I want the user to select from a List Box on only 3 of the customers.   But,  each customer has a ID and the report needs to be filtered by Customer ID,   however display the 3 Customer names in the List box.  Is that possible?

Example of Data:

Customer ID          Customer Name
1                          Customer A
2                          Customer B
...500                   Customer ZZZ


The List Box should display only 3 specific customer names but filter on their ID.   

• Customer  F
• Customer  G
• Customer  H

Are you using a relational or dimensional model? I'm assuming relational?

Your report object (list/crosstab/chart etc) should have a detail filter with the expression

[Customer ID] in (?CustomerParameter?)

On your prompt page your prompt will have a Use Value of Customer ID and a Display Value of Customer Name

Cheers!

MF.


Sent from my iPad using Tapatalk HD

[bud659]

Thanks MF for the reply.  How do I update the syntax to only include 3 Customer ID's (out of 1000) to be filtered? 

[Customer ID] in (?CustomerParameter?)

Its a list report with Relational Data.  thanks!

[Lynn]

Thanks MF for the reply.  How do I update the syntax to only include 3 Customer ID's (out of 1000) to be filtered? 

[Customer ID] in (?CustomerParameter?)

You don't need to change the filter in the query that produces your report. You just need to add a filter to the query that feeds your prompt control. This will limit the choices your users can see and select from.

Presumably you have a prompt page with a prompt control on it? Or a prompt control directly on the report page? Either way, you should create a query that just includes the customer id and the customer name. These are the use and display values for your prompt control. Add a filter in that query (not the report query) to filter the result set for just your desired customers.

[bud659]

I have a separate Prompt Page with the Value Prompt Properties set Use Value is Customer ID and Display Value is Customer Name.   I have a Query called SelectCustomer where the Data Items are Customer ID and Customer Name.  In the left corner under Data Item  the Expression is the Customer ID = 1  (just to test with 1 Customer Name).   

I either get all the Customers  or  'No Data'  when I use the Expression.  If I remove the expression,  I get all the Data but it lists every customer.   There is data associated with Customer1.   Sorry for the newbie questions

[Lynn]

Instead of changing the customer ID query item expression, try putting a filter on your SelectCustomer query like [Customer ID] in ( 1, 2, 3 )

Then run the tabular data for that query to see if you are getting just those customers returned. You should also see only those customers in the value prompt.

Next you need to make sure your report query is using the customer parameter. There should be a filter in your report query similar to what the muppet told you to do. Make sure the parameter name matches whatever the parameter name is for your prompt control.

I'm not sure why you would either get all the customers or 'No Data' from what you were attempting. You'd need to explain further about the scenarios that led to one outcome or the other. Changing the customer number expression to a hard coded value would bring up all the customers in the prompt control but every one of them would have the same use value.

A good debugging technique is to bring in a layout expression onto your report page and then use the ParamDisplayValue function and another one for the ParamValue function. This will allow you to see the use and display values of whatever you select to ensure it is as you expect.

Good luck!

[bud659]

ahh thanks for the that.  its working now.   :)