Is there a macro to devise a prompt to choose from the suffix of a table?

[pkrishnaq]

Hi,
I am in a bit of a predicament.

I am trying to get a macro to design a prompt to pick a specific table based on the its suffix .

For e.g
if there is a table name : TableA_201304
I want the prompt to request the user to select the yyyymm..so if they enter 201304
the columns from the above table would be display

[pkrishnaq]

Update: I could get to do this in Framework manager using
Select

   *
From
   TableA_#prompt('yyyymm','token')#

How can I do this in report studio? It returns with an error if i try the same thing.Thanks in advance

[Rahul Ganguli]

Hi Kratos,

This should work equally well in Report Studio.
I believeyouare using SQL component in Report studio to acieve this.

Can you plese share the error you are getting.

Regards,
Rahul

[pkrishnaq]

Hi Rahul,
I am using the SQL component of Report studio and get the following error
"The server returned an unrecognizable query framework response"

[Rahul Ganguli]

Hi Kratos,

Are you able to run any SQL (i.e. without macros)?
It seems to be an access issue to me. If you are not able to run any SQL, please contact your administrator for access permissions.
If the simple SQL works, the we haev to check the SQL with macro and how is it different than FM. (Hope this will not be the case)

Regards,
Rahul

[pkrishnaq]

Hi Rahul,
I am the system administrator and do have SQL access.

Unfortunately ,it seems to be a SQL issue.

I have not changed the SQL that worked good in FM.

Thanks for checking..

[Lynn]

Take a look at the data source property associated with your SQL object in the report. You need to specify which Cognos Data source your SQL applies to.

[pkrishnaq]

Lynn,
I have specified the correct data source.