Panorama Business Intelligence

I knew for a long time that measures can have text and not only numbers, but last week I had a scenario where it was the perfect solution for my problem.

Let’s say that I work in number of jobs. In every job I got some tasks that I need to do. The fact table looks like this:

Summarizing the “Tasks To Do” column will get the total tasks that I need to do. Summarizing the “Tasks Completed” column will get the tasks that I did. Note that in the Therapist job I didn’t get any tasks.
In the OLAP cube, I created a calculated measure which is the percent of the completed tasks (for every job or for any other dimension). The problem is: What is the answer in case that there are no any tasks to do? Some will say 0% and some will say 100%. That’s why a text measure such as “There are no tasks” can be a perfect solution for this kind of problem. This is the calculation of the calculated measure:

Create Member CurrentCube.[Measures].[Completed Percentage] as
IIF(IsEmpty([Measures].[Tasks To Do]),
null,
IIF([Measures].[Tasks To Do] = 0),
-1,
[Measures].[Completed Tasks] / [Measures].[Tasks To Do]
)
)
,format_string = “#,##0%;\T\h\e\r\e\ \a\r\e\ \n\o\ \t\a\s\k\s;0%”;

In the Panorama’s view, the format of the measure must be “General”. Otherwise, the view won’t show the format string with the text