Freeform SQL (FFSQL) - Tip - How to create TEMP(Temporary) tables

I have been working on bunch of FFSQL development and realized that there isn't quick and easy way to learn about how can one make use of TEMP tables while working on FFSQL development in MicroStrategy. Thought this post might be useful tip for someone looking to perform similar tasks.

TEMP tables are very essential mostly while working on complex SQL development though there are numerous resources available online as well as database(Oracle, SQL Server , TeraData etc.) manuals about how to make use of TEMP tables including correct set of syntax and semantics. However when it comes to using this feature in MicroStrategy its not directly feasible by simply copying the SQL to FFSQL editor. Here are quick steps about creating and using TEMP table using MicroStrategy Developer/Desktop.

1. Access the MicroStrategy developer to create new report > Choose the Freeform Sources > select the appropriate database instance > Click OK > This will open the FFSQL report editor. 
2. Type query for test say "select current_date-1" and map it to date attribute in lower portion of FFSQL Editor.
3. Now Click OK this will take you to report design view
4. Access the Data menu > VLDB Properties option > This will open dialog window like one below 
5. Now copy the temp creation SQL in pre-sql statement 5
6. You choose to create temp table and use the output for reporting in which case once you are done pasting your SQL in VDBL properties  Save and Close to come back to report design view
7. From Data menu > Choose Freeform Report Definition > This will open FFSQL editor for you to update the previously written query with query using temp table just create. temp table will be create in account used in MicroStrategy data instance selected while creating report so you should avoid using any schema reference for the table.

Hope this post help you with any FFSQL development. I could not add more details with screen shots however if you have any question you may comment and I will revert on that as soon as I could. 

Additionally in cases you might want to cache this report output so every time someone runs the report it should not create temp table on the fly. specially if temp table;e creation query run for long time. In this case you should use schedule based cache invalidation feature so you can schedule to run this report and cache it before next schedule run another schedule should invalidate the report cache.