#prompt statement error "missing expression"

[MPritchard55]

Hello.   :D
I'm creating a report in Report Studio using an sql block.  I have a required prompt for academic period that I would love to make a drop down selection prompt with all available academic periods by referencing the data item at the model/database level within my prompt call.  I've reviewed the documentation and previous posts but can't see why I am receiving an error for "missing expression".  Can anyone see something I'm overlooking in my code?   (This is just a portion of my code.  I know I could use a filter and prompt page to create a drop down selection prompt but this is actually part of a subquery in my sql block so that's why I'm using the #prompt.)

select person_uid, academic_period, du_id, name_lfmi
from du_ods.du_student_calc_ptft
where admissions_decision in ('EW','IE')
and enrollment_status = 'EL'
and academic_period=#prompt('Academic Period', 'string', ' ', ' ', '[Student].[Key and Frequency Identifiers].[Academic Period]')#

Thanks for any insight.  M

[bdbits]

The prompt macro will substitute everything between the # symbols (including the symbols) with whatever the prompt macro resolves to. So if it is a string, you probably need to add single quotes around the resulting function. Happily the sq() macro function does just that. So maybe something like this...

and academic_period=#sq(prompt('Academic Period', 'string', ' ', ' ', '[Student].[Key and Frequency Identifiers].[Academic Period]'))#

[MPritchard55]

 ;D ;D ;D ;D  You are a genius!  That did it!  Many thanks!

[MPritchard55]

hmmm....the prompt created the dropdown but the query is now not returning any results.  (I know it should)  I feel like this has something to do with a data item type mismatch.  The academic_period data item is defined as a varchar2 in the database.  Any ideas?

[bdbits]

That would be difficult to answer without access to both your data source and the report xml.

The root question here is why you have to do SQL in your report. Generally, with a proper model, you wouldn't be messing around with trying to get this to work. It is much easier when you can use Cognos functionality to do the lifting for you.