Microstrategy - Ratio from Summary Report fields

Question

In a query I have a case statement that gives either of two values for Attend Interview - this is worked out from another field that gives one value for 3 different scenarios that actually mean attend = Y, one value covers not attend = N. So for example I have something like this in my output:

Unique_ID   New/Existing                 Attend_Interview         
=========   ========================     ================    
12554445    E                            Y
65766879    N                            N
53375654    N                            Y
44323224    E                            N
93656786    E                            Y

What I then do is put this into a summary grid in Microstrategy (MS) to work out the new/existing applications further broken down by Y/N for attend.

But I also want to find out the ratio of New Application Attend to New Application Not Attend, however can't do that in MS since I am using a crosstab summary report and therefore need to try and calculate in SQL if possible.

My summary looks something like this at the moment:

                  N                       E
       ======================  =========================
        **Y**           N         **Y**           N
      =========    ==========   =========     =========
         570           140         89            56  

And I would like to work out the ratio of 570/89 but as I said I think I need to do something in SQL so that I can put this in to my summary report as these are not physical columns I can divide.

Many thanks in advance for any suggestions on how to create this ratio field or suggestions for rework what I am doing.

Using : SQL Server 2008 R2 Express and web version of MS

Answer 1

You can create two conditional metrics for Attend_Interview = Y and Attend_Interview = N cases, then you can calculate the ratio between these two metrics.

If you want to use these metrics in you in your crosstab report you should replace the Attend_Interview attribute with the metrics.