There are alot of ways to inject SQL into your MicroStrategy reports. I've used fairly complex methods like using dynamic SQL to inject my custom code, but let's review the most straightforward way to inject SQL into your MicroStrategy report.
First, lets understand what a MicroStrategy SQL inject is. It's a method of adding in your own custom SQL into an existing MicroStrategy report. This is different than a FreeformSQL report. With a FreeformSQL report, you write all of the SQL yourself. The SQL inject allows you to use the standard MicroStrategy Schema objects but add in custom SQL somewhere along the way.
Simple Method Example
The most simple method to inject SQL is to do it at the end of of a select statement. So let's work through a simple example.
Below I have a report with a category attribute. You can see the 4 categories displayed in the report.
Now let's look at the SQL. Notice there are two columns - One for the ID and another for the Description. This is important to note.
In this example, I want to inject my name into this list of attribute elements. To do this, I'm going to open the report VLDB settings, go to the Select/Inserts, and select the "Select post string" option. Here I can add custom code to the end of the report SQL. I know there are two columns for the ID and Description, so I'll add the following code
Select -999, 'Omar Vidaure'
Now when I run the report I see my name in the list of attribute elements.
Let's take a look at the SQL which now has my custom code
Viola! Custom SQL Injected into your MicroStrategy report. It's important to understand what we're doing in the MicroStrategy engine. We're saying that after each select statement, add this custom SQL. This wont work in all cases - think multi-pass reports. In a later post, I'll share how to inject SQL into a multi-pass report within specific passes.
Thanks for checking out this blog post. If you found this post helpful please consider donating. Any contribution is appreciated! Just click the PayPal icon at the bottom of this page.