Field value not showing in Prompt drop down

[nbailey]

I am having problem with an existing report that was working fine before.  this report has a prompt on a STATE field in the query subject.  When the report was run the prompt drop down would provide a selection of all states available for easy selection,  but now the prompt is blank and provides no state choices ?

I have recently updated  the existing package to add a couple of new fields to the same Query subject... this may not have anything to do with the problem but worth mensioning...

If I create a new report and create the same prompt on STATE the same problem is happening ... so I don't think the problem is in the report ... but in Cognos itself ...

Does anyone have any idea why my field would no be providing values to the prompt ???

see enclosure for details.

[Lynn]

Have you looked at the generated SQL? Has anything in your database changed?

[nbailey]

The only change to the database was that I added 4 new fields to the bottom of the same table that the date is on.  Generated sql looks fine and if I hard code the State in the filter it works fine... it is just getting the prompt to contain the state list ...

[Lynn]

Did you check the prompt properties in your Framework Model? Maybe it is looking somewhere it shouldn't for use and display information to generate the prompt list.

[nbailey]

Not sure where to look in framework manager for that ... enclosed is the framework manager properties for the State Field.   

One thing I did notice was that this field has a Usage value of Unknown... I changed that to Attribute ... but made no difference. 

[Lynn]

The prompt info properties are all the defaults so I think Cognos would just get a distinct list of that field to fill the generated prompt control. I don't see a reason why it won't. Did you look at the "Show Generated SQL/MDX" from the tools menu? Maybe that will show the query it is trying to use to fill the prompt and you'll spot a clue in there.

You could create your own prompt query and add a prompt control to the report, but that won't solve whatever problem is going on in your model.