** Dynamically Changing table names in report query based on prompt selection **

[Good Friend]

Hello Friends,
Need small help on the below. Please share your thoughts.

Is there a way we can dynamically change the table name in the report based on the prompt selection or any other approach to get the table name filtered.
I have created a drop-down prompt or static choice with 10 table names added inside the prompt, so based on the table selection from the prompt, the report query should dynamically change the table name and execute the report. I used a prompt macro for this like below with string data type and also tried token later. Apart from adding table_name_1 as default in macro syntax. I have also added table_name_1 as default in a static choice prompt and I think it won't help much. Also, want to add that this a custom SQL Report not based of off framework package and the underlying data source is Oracle.

#prompt('p_time_month','token', 'Default_table_name_1')#

Now when I run the report for the default table it works fine, the moment I chose "table_name_2 from static choices and run the report, it throws me an error. I kind of doesn't know how can I move forward from here. If you get a chance, can someone please provide me some insight and detailed approach on how this can be done. Thanks for your help.

[BigChris]

Can you post the error message? That might give us something to go on...

[Good Friend]

Thanks, Chris for responding. I was using the wrong use value in the prompt and that's the reason it was throwing an error before. I changed it and there is no error.
The main problem here is other than the default value no matter what I select from the static choice prompt, it's not passing into the below syntax.

My SQL statement looks something like this: SELECT * FROM TABLE A JOIN TABLE B  JOIN #prompt('p_time_month','token', 'Default_table_name_1')#

In the above statement, I need to pass the table name dynamically into the p_time_month which is the parameter I have created. since this is a custom SQL object and when I reference that SQL object to a Query and If I generate the SQL, I can only see the default name being added in the prompt macro and statement looks like below. so no matter what I select from static choice and run, it's not helping me. Is there a better way of doing this or any other approach for this like creating a variable with stored proc and using over here? Please advise and let me know if something is unclear. Thanks.

SELECT * FROM TABLE A JOIN TABLE B JOIN Default_table_name_1;

static choice prompt looks like this:

Default_table_name_1
table_name_2
table_name_3
table_name_4
table_name_5
table_name_6
table_name_7
table_name_8

[dmk.3678]

Hi Good Friend,

I have similar requirement. I have two Fact tables.

Fact_1
Fact_2

Both has same columns. Fact_1 table has data till year 2020 and Fact_2 has data starting 2021.
Now i need some help figuring out that how can i make FM model to change table name when i run the report so if i select any year = < 2020 then it pulls the data from Fact_1 and if i select year > 2020 then data are fetched from Fact_2 table.

Can it be achived from FM model it self with just one star schema or do i have to create two star schema?

Please suggest possible solution here. I would really appreciate your any suggestions.

Regards,
DK