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

Report Insight

Report Insight

Report Insight


any inputz on how to accomplish this report in microstrategy....
suppose i have data like this

ProdID    Prod Name    Date        State    Qty
------  ---------       ----            -----   ---

1200    Brake Shoe    20th May 2002    Hyd     150
1200    Brake Shoe    22nd May 2002    Blr      75
1450    Clutch Cable    10th May 2002    Chn     450
1450    Clutch Cable    23rd May 2002    Del     320

how do i display details of the product for the maximum date(qty as on last date)...
wat i want to display is

ProdID    Prod Name    Date        State    Qty
------  ---------       ----            -----   ---

1200    Brake Shoe    22nd May 2002    Blr      75
1450    Clutch Cable    23rd May 2002    Del     320

i.e. for each product it shud take the maximum date of that product and display the details for that date only... so there will be one row for each product in my report...

also how do display another report which will display
the last available quantity only if the given specified state
value say Blr or Del... if the user says Del it shud display

ProdID    Prod Name    Date        State    Qty
------  ---------       ----            -----   ---

1450    Clutch Cable    23rd May 2002    Del     320

million thanks for your replies.



RE: Report Insight

Your first problem should be resolved with a dimensional metric.  If you create a metric for Quantity, and add Dimensionality for a Time attribute (which Time attribute depends on how information is written to your database), you can then specify the grouping to be 'Ending (Fact)'.  What you describe is generally known as 'Beginning On Hand' (BOH) or 'Ending On Hand' (EOH) metrics, and the tool is set-up to handle them.  You may find more information if you search those keywords in their knowledge base.  And there is an example in the Vmall project.  Search for the metric 'Dollar EOH'.

Unless I'm missing something, your second problem sounds like all you need is a report level filter that prompts on State.

RE: Report Insight


thanks for ur reply...

but how do i say take the last date quantity for each product... one more thing is if
i create metric as u said i gotta use some functions like sum or count etc... but i don't
want to sum or count quantity instead jus show watever value is present for that date.....

the way i do in SQL is create the temporary spool table and use that table to
join with the main table and display the results... but now i want to implement that report using MS7...

best regards,


RE: Report Insight

If you set up the metric correctly, it should take the max date of the time attribute you specify.  And if product is on the template, it will do so for each row/product.  In addition, if this is set up correctly, you can SUM your metric since you should be aggregating only one row of data anyway.

I would create a couple reports in Vmall using the metric 'Dollar EOH' and the attributes 'Quarter' and 'Item'.  Although the project uses partioned tables for this query (one per quarter), you should be able to see in the SQL how the engine handles this kind of report.

RE: Report Insight

Hi JRO061601,

Thanks for ur kwik reply…..

Well I’ve got that ending part value to display properly... itz displaying the last date quantity for each product correctly but the next report that I’m looking for is not coming properly…. That is if say the last date for each product should be Delhi then it displays all the products where there was a transaction for Delhi even though Delhi is not the last place to have got that product….

ProdID    Prod Name    Date        State    Qty
------        ---------          ----            -----   ---

1200    Brake Shoe    20th May 2002    Del     150
1200    Brake Shoe    22nd May 2002    Blr      75
1450    Clutch Cable  10th May 2002    Chn     450
1450    Clutch Cable  23rd May 2002    Del     320

For a data like the given above if user says display only for Delhi it should display only one row…. But instead it displays two rows….

ProdID    Prod Name    Date                State    Qty
------        ---------          ----                  -----      ---

1200    Brake Shoe    20th May 2002    Del      150
1450    Clutch Cable  23rd May 2002    Del      320

I’ve looked at the SQL and found that itz taking the filter Del during the first stage itself that is when finding the Ending Fact…. But I feel it should take the filter only during the final phase…… how do I get that working….

I tried embedding the filter in the metric and also make it a report level filter still no results….

This one is really driving me crazy….

Best Regards,


RE: Report Insight

if i understand your requirement correctly, the following modified data would return 2 rows?

ProdID   Prod Name     Date           State    Qty
------  ---------      ----------     -----    ---

1200    Brake Shoe    24th May 2002    Del     150
1200    Brake Shoe    22nd May 2002    Blr      75
1450    Clutch Cable  10th May 2002    Chn     450
1450    Clutch Cable  23rd May 2002    Del     320

such that you want the row(s) where a given product was last in Del.  but if a product was last in another state, you don't want to see it.

the additional filter for this would not be at the metric level (i think).  you somehow have to construct the logic such that a subquery qualifies on max(date) at the product level, for a given time interval, and only takes the rows where state is Del.  a level filter may do the trick.  if not, you may want to experiment with writing a custom expression and using the applysimple function in order to bypass the SQL parser.

RE: Report Insight


xactly thatz wat i want.... yes i agree the filter shud not be at the metric level....

wat do u mean by level filter.... can u elaborate more on that.... also 'bout custom expression and how to use ApplySimple so that one can bypass SQL parser.....

thatz how i did it BusinessObjects... write my own SQL.... is that possible in MicroStrategy....


Best Regards,


RE: Report Insight

In MicroStrategy you cannot directly write SQL to the database but there are several options available to you.

The first option in massaging the SQL is to use the VLDB (Very Large DataBase) properties.  This lets you construct whether you use subqueries or temp tables, for example...or whether you want to force a full outer join versus a left outer join.

The ApplySimple function allows you to insert database specific functions into facts, prompts, and attributes.

From Tech note TN5200-7X0-0131:

ApplySimple("datepart(year, dateadd(month, -1, getdate())) * 100 + datepart(month, dateadd(month, -1, getdate()))",0)

For this particular problem, as JRO061601 mentioned, you could toy with using the ApplySimple logic to do some sort of CASE statement...

Form TN TN5200-071-0120:
ApplySimple("CASE WHEN #0 <= 20 THEN 0
END", [Number of Items])

But you could also create a metric that use the CASE function...

CaseV(1, ([Number of Items] <= 20), 0, ([Number of Items] <= 50), 1, 2)

...if you don't have access to the MicroStrategy knowledge base you should.  Contact your company's sales rep or SI on how to get access to the KBase.

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