Freeform SQL Reports

Freeform SQL Reports can be a handy tool in your MicroStrategy bag of tricks.  We’re all tempted to turn to them from time to time, but when is the right time and what kinds of tricks can we manage by leveraging them?  As always, the answer to the first question is going to be a personal preference and depend greatly on your project scenarios.  Today I’ll offer my opinions, as well as highlight some of the tricks you can do with Freeform SQL Reports.

What are Freeform SQL Reports?
MicroStrategy’s core purpose is a SQL engine.  You define the schema and it will dynamically write the SQL by reacting to the objects you drag on and off of the report.  It’s incredibly powerful and flexible and empower users to do their own data mining and analysis.  A Freeform SQL report skips all of that.

Basically, you bypass the MicroStrategy SQL Engine and instead provide the SQL directly for the report.  You still have to use MicroStrategy objects and map it to the report so that it has something to anchor on to, but you have a little more flexibility in doing this.  You don’t get advantages like true drilling (you can still use links) but it can really help out in a pinch by letting you provide some complex queries directly instead of breaking your back trying to manipulate the schema to handle it.

When to use it
Personally, I love using Freeform SQL Reports for Exception Reporting.  Specifically, when I’m going to be using tables that would otherwise violate my normal data model.  For example, if I’m trying to expose an exception table that contains attributes and facts my normal reporting using, exposing it to the schema could create join paths and aggregate table options I wouldn’t otherwise want a normal report to access.  While there are plenty of ways to avoid those tables, it’s not necessarily a complication I want to deal with.  Freeform SQL Reports are best used for 1-off reports that either access tables not otherwise used in normal operations.

When not to use it
Since you’re basically skipping the core function of MicroStrategy, they really should be used as a last resort.  MicroStrategy is a tool, and sometimes it can’t do everything you want immediately (specifically, things like theta joins and difficult relationships) but these should usually be solved with more thought out ETL and Data Model design than taking the FFSQL shortcut.  If you lean too heavily on these reports, you can really put your project at a disadvantage down the road in terms of flexibility (drilling), portability (changing database platforms) and scalability (introducing aggregate tables).

Mapping Objects
When you paste your SQL into the FFSQL Editor, you must map it to objects.  You can either create new objects specifically for Freeform use (referred to as Managed Objects) or map to existing objects.  It’s important to note that mapping to existing objects will require the mapping of the ID form, and then optionally any other forms you want to use in your query (most commonly the DESC form).  The advantage of linking to existing objects is that you have a slightly cleaner environment and better support for the links feature, but the disadvantage is it gets a little more complicated to unlink from the Managed objects which can cause some migration issues down the line.  Personally, I only link to the normal schema objects if I intend for there to be some interaction between normal reports and FFSQL reports, and usually stick to Managed Objects for 1-off reports.

Tricks & Tips

  • One of the best uses for a Freeform SQL Report is to point it to a Stored Procedure or use MultiPass SQL.  There are VLDB settings to ignore passes with no data results, but for some reason you can only accept data from the 1st or 2nd pass.  Anything after that gets ignored for results, but still gets executed.  
    • Another thing I like to use FFSQL reports for is to place some custom processing SQL that a user needs to run.  This is a pretty rare task for a Data Warehouse, but sometimes there’s a case where a user needs to kick off a process.  This is an easy way to give a user access to database Processes.
  • You can use normal Element Prompts in your Freeform SQL Reports so the user won’t notice anything is different.  You can also use the User Prompt to pass the User Login.  The editor also comes with the ability to mark section of SQL optional so that if the user doesn’t answer the prompt, incorrect SQL isn’t generated.
  • You can point a Freeform SQL Report to any DSN, not just the primary.  Without a Multisource license, this is an easy way to expose some quick bits of data from another system (like a Transactional system) into your project without additional costs or complicating modeling.
  • If you closed out the option to create a Freeform SQL report long ago, you can get it back by going to My Preferences in Desktop and under the Object Templates section, checking the box for Report.   Now when you click New Report, you’ll have the option to create it from a DSN (Freeform).

You may also like...

32 Responses

  1. Hi Bryan,
    It would be very helpful if you write a post on ‘Transaction Services’ reports and your experinces with this.

  2. Ida says:

    Hi Bryan,

    Getting a Too few parameters. Expected 1 error, however my query has no parameters. Have you ever run into this?

  3. This comment has been removed by the author.

    • Hi Bryan,
      I have been trying to edit an attribute form created on a transaction report FFSQL definition. It does not let me change the column alias. All the options are greyed out. Is this a known issue or am i missing something?

    • Actually it does not give an option to create a new column alias. We have changed the type for the form from number to big decimal. But unless the column alias can be changed the transaction does not work as expected.

  4. Hi Bryan,

    I want to create a script or a report which test Microstrategy Connections to database hourly.This report should email to MSTR admin if any connection fails.

    Please help me how to proceed with this.

    • Bryan says:

      I’ve setup something like this before. What I did was create a FFSQL report that just inserted a row with a timestamp into a table on the database. A Database job then monitored that table and if it went more than X minutes without an insert, then the Database would send an email alert. This would verify that MicroStrategy was running and the database was reachable.

      If your concern is more about the database itself and not MicroStrategy, then you could setup a report that returns no data (with a filter like Region=’not a region’). Subscribe to it and in Project Preferences, you can define reports with no data aren’t sent so you won’t receive an email. Then in that same Preference section, put your email address to be alerted if the email fails. In the event the database can’t be reached, you’ll receive a failure of the subscription which will be your alert that the database was unreachable.

  5. Thirumalai says:

    Hi Bryan,

    I have a requirement where I have 4 types of Actual metrics and the corresponding 4 types of Target Metrics and the report should be displayed as follows :

    MN Actual Target
    Metric1 v1 v2
    Metric2 v3 v4
    Metric3 v5 v6
    Metric4 v7 v8

    Note : Where the words MN (Metric Name), Metric1,Metric2,Metric3,Metric4 , Actual and Target are String Constants and v1 to v8 are values for these metrics .

    So in plain sql this can be written as :

    Select MN,Actual,Target from dual
    union all
    Select Metric1,avg() as v1,avg() as v2 from t1 group by <>
    union all
    Select Metric2,avg() as v3,avg() as v4 from t2 group by <>
    union all
    Select Metric1,avg() as v5,avg() as v6 from t3 group by <>
    union all
    Select Metric1,avg() as v7,avg() as v8 from t4 group by <>

    Can we use freeform for this scenario, The metrics are already available in MSTR its just the placement of the metrics in the report that matters. Can I use freeform or any other better option is appreciated.

  6. Hi Brayan,

    I am trying to create a FFSQL report with Postgres as database.
    I want to create a “Top 10 + others” report, so I am using a union operator.
    The from clause of both the select statements has a sub query . I want to optimize the SQL by creating a temp table of the subquery using “With” keyword, so that the calculations are not repeated twice.

    But I get a error : Internal Error– no cursor for fetch.
    The query that I write with “With” keyword works fine if I test it outside directly on the postgres db.

    Also, I am able to use “With” keyword on a SQL server database.

    I cannot use the pre statement VLDB property beacuse I want to use prompts in the temp table I create, nor do I want to go with Custom Group for performance reasons.

    Kindly help with the error or suggest me some alternative


    • Bryan says:

      If you go into the VLDB Properties, there’s a Freeform SQL section which has some options on handling multipass SQL. There’s 3 settings, try one of the latter two and see if that fixes it.

  7. Thanks for response Bryan, but no help with the VLDB settings. Still I get the same error

  8. Alish Manvar says:

    Dear Bryan,

    Can i use VALUE Prompt on Free Form SQL Report.


  9. Hi Bryan,

    I have a simple free form SQL report, with 4 attributes mapped to it. When this reports is migrated to some other server, sometimes it happens that the first attribute Mapping is removed, in the editor pane a blank attribute sign appears.
    (In the mapping pane, for the first column of SQL, it shows a attribute sign with no name,no type,no form. Also when I check the expected attribute in attribute editor, there also this FFSQL report is not mapped)

    What could be the cause of the issue? Schema is updated after migration. Also the issue is inconsistent.
    Server is 9.2.1 and the mapping is to a existing attribute (it is not a managed object)

  10. Unknown says:

    Hi Bryan,

    I am working on a report that uses compound metrics where the underlying metrics are count metrics, I need values for all rows, even if the count is 0.

    I am thinking of creating a table or view where I have:

    The problem is that I will lose the ability to filter by date.

    I was thinking of filling out this table on a pre-sql statement, however, I have not found any way to pass prompt answers as parameters in pre-sql statements.

    I was thinking of running a stored procedure on a FFSQL report, where I can pass the prompt values easily.

    Do you think I could use the FFSQL report as a filter on the original report? Or can you think of a different alternative?

    • Unknown says:

      Hmm… It works if I run the FFSQL report and then run the original report. If I use the FFSQL report as a filter, I answer the prompts and the report runs, but the FFSQL report’s SQL is is not executed.

    • Unknown says:

      Sorry to ‘spam’, but I got it to work and in case anyone is interested this is what I changed:

      Added the lowest level attribute of the report I wanted to use the FFSQL report as a filter as the only column in the FFSQL report. With this, the FFSQL report is actually run when used as a filter.

      This is an empty result set for the original report, so I created an OR filter condition where the attribute id is not null OR the ffsql result.

      This will tricks microstrategy into running the report 😉

  11. Govindarajan says:

    Need help to work Stored Procedures on Transaction Services.
    My scenario is i need to run the stored procedure and the SP’s input one come from user input and another have to come from the grid report.

    Please help me to Work

    Govindarajan. S

    • Bryan says:

      The first bullet under “Tips & Tricks” in the above article says how to support a Stored Procedure in a FFSQL report. I’ve never used Transaction Services, so I don’t know if those have different limitations. You can use a prompt in the FFSQL to prompt the user for input to the SP, but you can’t read data off of another grid report.

  12. Vini says:

    Hi Bryan,

    I have a FFSQL report which needs some attributes not to be displayed on the grid, but I want them as I need to show it as the report header in a document where I need to add this report.
    What is happening here is, if i remove the atttribute from the grid, the metric values disappear. They are added back on as I add the attributes back as well. Is this an expected behavior? Or is there a workaround this? Any help appreciated.


    • Bryan says:

      It’s because the metrics would have to aggregate to a higher level if you remove an attribute, and MicroStrategy doesn’t have enough context in a FFSQL report. If your metrics are counts, average or some other non-additive aggregation, then it’s impossible for MicroStrategy to do the math, but if they’re all Sum, then I would expect it to still work. Either way, the only way to do it is to create two different FFSQL Reports or create a basic schema so you can let the SQL Engine do it’s thing.

  13. I have two tables in in two different instances (1 is pointing to a DB2 and other 1 is in another database)
    How the sql will look like in FF SQL editor ?

  14. mohamed yasar says:

    Hi Bryan,
    i am creating Free Form SQL Report. while creating the report i written my query in the query panel in object browser wheni choose free form objects, its running for long time without showing any results. Need help. But i am getting the output in Plsql developer when i run the same query. the database is oracle. Need help

  15. 123ugf says:

    Hi Bryan,
    We want to know if thers is a workaround to use object prompts in freeform sql reports?

  16. viswakanth says:

    in our project we have a requirement where in, the oracle code is linking two database instances(ex.LIve and Advance databases).

    We have to convert that report into freeform sql.

    When we run a report with the tables only from one instance(eaither Live or Adnanve).. it is running.

    But when we include the tables from both the environments it is throwing an error.

    Could you please suggest me on how to handle this situation?

  17. Naveena says:

    Hi Bryan, Can I use Query Builder report with Multi Source? I have need to use that in our project but iam getting error. Thanks.

  18. Calvin says:

    Hi Byran,

    Does Connection Mapping work with FreeForm SQL Report?


Leave a Reply

Your email address will not be published. Required fields are marked *