Report Insight 1

[Sridharan]

hi,

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
only...

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

1450 Clutch Cable 23rd May 2002 Del 320



million thanks for your replies.

regards

sridharan

 

[JRO061601]

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.

 

[Sridharan]

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,


sridharan

 

[JRO061601]

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.

 

[Sridharan]

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,

Sridharan

 

[JRO061601]

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.

 

[Sridharan]

JRO061601,

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....

TIA

Best Regards,

Sridharan

 

[chael]

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(&quot;CASE WHEN #0 <= 20 THEN 0
WHEN #0 BETWEEN 21 AND 50 THEN 1
ELSE 2
END&quot;, [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.