Tabe change in From clause

[Former Member]

Hi all,

I want to implement dynamic table functionality.

User should prompt for the table. If he choose EMP table data in the reports should display from the EMP. if he choose DEPT data should display from the DEPT table.

I mean Fromn clause of my select statment should change as per the value selected in prompt.

[Former Member · Accepted solution]

hi,

you can try the below implementation.

Try to create 2 queries (one from EMP, other from DEPT)

Try to create two tables in the report from these 2 queries.

Based on the user input of table name, create a variable "Var1" using 'UserResponse' function

logic should be if UserResponse = 'EMP' then 1 else 0.

Apply a report level filter on Table1 (EMP) : Var1 equal to 1

Apply a report level filter on Table2 (DEPT): Var1 equal to 0.

So, based on userinput corresponding table is shown.

Regards,

Vamsee

[Former Member · Accepted solution]

Hi Vamsee,

Thanks for your responce.

But here if I am choosing Dept my another table structur in report is still getting displayed.

I did try the same functionality by putting @ Prmpmt in the Report SQL. but getting error message.

Do you any idea on this.

[Former Member · Accepted solution]

hi Rachna,

1.Uncheck the property Display -> 'Show When Empty' for both tables.

So when condition is satisfied, only one table is shown and other table is completely hidden.

2. Also, Please don't create a filter in Universe for Prompt.

Please create an object and definition should be as follows. you can associate any table in Tables button for this object.

@Prompt('Select:','A',{'EMP','DEPT'},mono,constrained)

Use this object in both queries by dragging into 'Result Objects' pane instead of 'Query Filters' pane.

You will not get any error when you use this object for both queries.

Please let me know if problem still exists.

Regards,

Vamsee