Newbie!! Report Help

[rajn]

Hello Everyone!

I'm new to MicroStrategy. I got a evaluation cd and i was just testing it.
I'm trying to create a report that i couldn't somehow figure how to do it in
MicroStrategy.

I've gotta table where i gather the amount paid by each branch on a given date.
The amount gets decreasing or increasing depending on invoices or receipts.

structure looks like this.

City Date Amount

Bangalore 11th Nov 2001 40,870
Cochin 10th Oct 2001 27,500
Cochin 10th Nov 2001 35,000
Delhi 09th Nov 2001 67,000
Delhi 20th Nov 2001 44,000
Delhi 27th Nov 2001 34,750
Bangalore 01st Dec 2001 23,550
Pune 15th Dec 2001 23,900


Now I want to display for each city the outstanding receivable on
the maximum date for that city. Like this


City Date Amount

Bangalore 01st Dec 2001 23,550
Cochin 10th Nov 2001 35,000
Delhi 27th Nov 2001 34,750
Pune 15th Dec 2001 23,900

Is it possible to print just the City and Amount without date?

Thanks


Raj

 

[nlim]

create a metric defined as MAX(date). Then add the city and this metric to the report template. it should work.

 

[rajn]

nlim

i did that and i'm able to get the maximum date for a particular city but not
the amount for that date i.e. when i bring that amount part its not working and
its giving all the details based on unique combination of city and amount.

if the data looks like this

Bangalore 11th Nov 2001 40,000
Bangalore 01st Dec 2001 20,000
Delhi 09th Nov 2001 60,000
Delhi 20th Nov 2001 40,000

the above report again gives that based on unique combination of city and date
so i get two Bangalore and Delhi whereas i should get only one each.

getting my problem?

rajn

 

[nlim]

sorry for delay, just got back. try this instead:

in your amount metric, under dimensionality, add date attribute to the dimensionality, and for grouping select "ending fact" option. then run the report with city, and metrics of date and amount.

Essentially this causes the amount metric to always be selected at the largest date value, ie. latest.