MicroStrategy Subqueries

[Sridharan]

Hi,

I've gotta peculiar problem that I want to do it in MicroStrategy7.0.

Sample data

----------------------------------------
Name Date Type
----------------------------------------
abc 20.01.2001 Cold
def 20.01.2001 Warm
abc 21.01.2001 Hot
xyz 21.01.2001 Converted
def 22.01.2001 Hot
abc 24.01.2001 Converted
-----------------------------------------

This is the hierarchy of type

Cold -> Warm -> Hot -> Converted.

Now i want to display all the Hot or Warm or Cold or Converted cases as on given date.

I'am able to do Converted but the rest i don't know how to do in MicroStrategy. The thing is if a name is Converted then it should not appear in Hot or Warm or Cold cases report even though that name might have got that type during the convertion process coz ultimately it is
Converted.


Going by the above example if i say all Hot cases then i should get only

----------------------------------------
Name Date Type
----------------------------------------
def 22.01.2001 Hot

and i shouldn't get this
----------------------------------------
Name Date Type
----------------------------------------
def 22.01.2001 Hot
abc 21.01.2001 Hot ***

since abc it is ultimately Converted.

Similarly if say Warm cases it shouldn't display anything coz the only Warm case ultimately became Hot on 22.01.2001

Also I would like to know if it is possible to do sub-queries in MicroStrategy as one do in Business Objects..

Any help will be greatly appreciated

Regards

Sridharan

 

[nlim]

Sridharan, try this:

create 3 attributes called "account", "date" and "status", and a fact called "dummy" defined as "name"
since you really want to know the accounts that are hot within a speicific period,

then create a filter with the following definition:

status = "hot"
AND
date between startdate and enddate
AND NOT
set of accounts (where status = converted and date > startdate) relate at the dummy level.

this should get you the subquery you need. Note the subindent is important as you can create indented filters in MicroStrategy. You are essentially finding the "hot" accounts, and subtracting from it the accounts which were converted after the startdate.

 

[Sridharan]

Thanks nlim ! I'll try that.

Regards

Sridharan

 

[Sridharan]

nlim,

i tried that but didn't quite work for me, then i removed the date part and made the query as on current date it worked perfectly... when i try to bring date into the filter and indent it(don't know its use) it indents both date as well as the third condition.... like this

status = "hot"
AND
date between startdate and enddate
AND NOT
set of accounts (where status = converted and date > startdate) relate at the dummy level.

not the way u mentioned... i checked up the sql generated and i find it unnecessarily creates an another subquery for date....

cud u plz explain about the indent part! its use... when to use that?.... and also u've asked me to relate at dummy level... why's that.

thanks very much for your valuable time!

regards

sridharan

 

[nlim]

the relate at dummy was to get the SQL to generate the subquery at the fact table level. You can get the subquery to generate at different levels.

if you can get the query to work without subindent, then that's great news! the subindent is useful for this kinds of queries

(status=hot AND date =xyz)

OR

(status =cold AND date = abc)

hope this helps. MSTR is having a user conference in Jan, are you going? They might have some technical sessions which will help you out.