×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

MicroStrategy Subqueries

MicroStrategy Subqueries

MicroStrategy Subqueries

(OP)
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

RE: MicroStrategy Subqueries

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.





    

RE: MicroStrategy Subqueries

(OP)
Thanks nlim ! I'll try that.

Regards

Sridharan

RE: MicroStrategy Subqueries

(OP)
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

RE: MicroStrategy Subqueries

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close