Microstrategy Blog

This is an old trick concerning sorting in reports and templates.
Let’s say your report uses an object prompt containing a number of attributes and you want the results to be ordered by those attributes. If you simply go to the ‘Advanced Sorting’ menu you will see that the attributes are not there, obviously because they are not part of the report itself.

Here comes the trick. Just add the attributes to the report, define the sorting and then remove them. Go again into the ‘Advanced Sorting’ menu and you will see that they are still there. Neat!

So now, when you execute the report and select your attributes from the object prompt in any combination, the Analytical Engine will know how to deal with them. Even if you play with the position of the attributes (rows, columns or page-by) the sorting options will be maintained.

A while ago I had the opportunity to prepare a short training on the FreeForm SQL reports for some of my colleagues. I thought I’d better share this with you as well, so… here is a not-at-all-exhaustive list of things worth knowing about this type of report.

What is a FF SQL?

FreeForm SQL (aka FreeStyle SQL) is a type of report which allows the designer to define its own SQL code. Notice this is a rather different approach than what we are normally used to in MicroStrategy, where we would usually let the SQL Engine do the job.

When should it be used?

1. Whenever you tried everything (VLDB properties, Metric Levels, Attribute Relationships, Table Logical Size, Data Model changes…) and the SQL Engine still doesn’t perform that join the way you want it to.

2. Whenever you want to use a data source which does not exist in the Warehouse Catalog. That’s right, with FF SQL you can use almost any data source, including those tables you see in the left hand side of the Catalog.

How do I recognize it?

You don’t, at least not from the outside. You can either edit a report and see if you can spot the unnoticeable “Freeform SQL Definition” button, or you can right click on a report and make a component search. If it has no components, you found it.

So, unless you set up some naming convention for FF SQL reports you will very soon lose track of them.

How is it created?

Click on “New -> Report” and select “Freeform SQL” from the list (you need a special role to see this option).

Type your code in the upper right hand side frame.

Now you have to map the items from the “Select” section of your script to virtual Attributes and Metrics. To do this, right click on the lower frame and select either “Add new Attribute form” or “Add new Metric”.

For a script like “SELECT country_id, country_name, SUM(sales)…” the mapping should be defined as:

These are virtual objects. You may actually have a “Country” Attribute in your project, but please don’t make the mistake of thinking these two are the same thing.

Click OK and you are back on familiar grounds.

Prompts in FF SQL

You can either create or insert them into a WHERE clause, by using the two dedicated buttons. The Prompts will show in bold pink.

Now, here is the interesting part. Let’s say you want to make some changes to the script, and you copy it into an external editor. You will notice that the prompts were converted into object IDs. Now, paste the text back into the FF SQL editor. They are bold pink again. Too bad the report won’t work anymore…

The prompts will have to be inserted again, the “button” way, if you want the report to run properly.

Words of caution.

Being what I call a “manually defined object”, the FF SQL is not subjected to automatic Schema updates. That is to say, you won’t be warned if you delete a table which is being used by such a report. So please bear in mind that maintenance for FF SQL can be harsh.

Did you know you can actually create on the fly reports in MicroStrategy Office? Of course you did…I mean, even I saw the video lauding how it can be done.

I just never took the time to figure out how. On the video it say’s “here’s the wonderfulness of creating reports”, however, it never says the settings that need to be changed. When I click on “File”, all I see is the image below. There’s now “New”.

So, there’s a couple of key settings you have to change. In your MicroStrategy Office application, click on “Tools -> Options“, and then click on the first option under General, which is Server. If you did an upgrade like we did, you’ll see that your Web URL is blank. Here’s where you enter your actual MicroStrategy web URL, usually: http://localhost/microstrategy/asp/main.aspx as seen below.

Hit save, and voila:

Now you get the pleasure of this when you create a new Blank Report:

Web is now integrated with Office, making you Excel snobs who refuse to Migrate to a strictly web environment content…at least for the time being.

Okay, so this is an old one, but I still find it useful.

If you can’t run a report because of a spool space error, and you can’t convince the DBA to give you a couple of gigabytes more, then you might want to go to the Report Editor, Data ->VLDB Properties -> Tables -> Intermediate Table Type and select True temporary table.

What this does is modifying the SQL so that instead of having a huge chunk of code (that would use up a lot of memory) it breaks the code into CREATE – INSERT bits that are far easier to handle. It also makes the SQL more readable by humans (this includes programmers).

By the way, this setting also comes to rescue when you get the “Database is terrified by the script and it can’t even begin to analyze it” error. I don’t have the specific error message at hand, but I know for sure that Teradata does this when the SQL code is all in one pass and is so large that it can be seen from the Moon.

It’s the little things in life that make me smile. A brand new video game…a good sci-fi book…cheese…and now, notes.

I’ve been playing around with my 9.0 Development installation, and I really like what see. Integrated Narrowcast functions, a smoother ride in the “new” environment, and gee golly, “Notes”. One of the reasons that we rely so heavily on MicroStrategy Office in our organization, is because the 8.X version of MicroStrategy does not give users the ability (short of SDK or creative RSS) to communicate with each other. So we pull our reports out of MicroStrategy, into PowerPoint, where notes are added, and then printed into a PDF where they’re distributed. It’s a complicated process that usually involved as much time being spent formatting the visuals as is spent actually understanding the data. With 9.0, that can come to an end. Now, Administrator and report users can communicate back and forth…complete with time stamp (editable of course), without the complexities and time drain of exporting.

While not exactly as “stylized” as some might like (I prefer my style in the report itself, not the explanation), this “Notes” feature can be seen in the Object Manager, in a collapsible menu underneath your Dataset Objects. “Notes” can be added to Reports, Dashboards and Documents, adding the benefit of not only dynamic formatted data, but the explanations necessary to give the report viewer the context in which those numbers matter. Once the explanation is added…MicroStrategy 9.0 then allows you to send that report to whomever you please, whenever you please.