Need help on drilling to template 1

[tonysut]

I have a report created from ORDER table with the fields: ORDER_NUMBER, CUSTOMER_NUMBER, ORDER_DATE, etc. For example, data looks like

ORDER_NUMBER CUSTOMER_NUMBER ORDER_DATE
1 1 11/12/2003

There are two filters. One is to choose ORDER_DATE between 2 dates and the other one is to choose customer no.

One order can associate with multiple transactions such as RFQ, QUOTE, PO, INVOICE. Therefore, the TRANSACTION table will have the fields: ORDER_NUMBER, TRANS_NUMBER, TRANS_TYPE, etc. Data looks like

TRANS_NUMBER ORDER_NUMBER TRANS_TYPE
100 1 RFQ
101 1 QUOTE
102 1 PO
103 1 INVOICE

Question: If I create a template for TRANSACTION, Is it possible to create a drill map associating with ORDER_NUMBER on my report to drill to my template with filtering only by ORDER_NUMBER?

 

[entaroadun]

Yes. In your drill map, you associate the Order Number attribute on the report you're drilling from to the Transaction template. There's an "Apply User Filtering Conditions" setting; this means that the Order Number the user clicks on in the source report will be used as a filter in the Transaction report.

 

[tonysut]

Thank you entaroadun.
This is another one. Let say there is another field for Latest_Transaction in my report. Data looks like
ORDER_No CUSTOMER_No ORDER_DATE Latest_Trans
1 1 11/12/2003 INVOICE

and the dat in TRANSACTION table looks like
TRANS_No ORDER_No TRANS_TYPE TRANS_DATE
100 1 RFQ 11/02/2003
101 1 QUOTE 11/04/2003
102 1 PO 11/06/2003
103 1 INVOICE 11/10/2003
104 1 INVOICE 11/12/2003

Question: How to create a drill map for a user to right-click on the element "INVOICE" in column "Latest_Trans" of the report and drill to TRANSACTION template with filtering by ORDER_NO(1), TRANS_TYPE(INVOICE) and TRANS_DATE(11/12/2003)? The drilled template should look like

TRANS_No ORDER_No TRANS_TYPE TRANS_DATE
104 1 INVOICE 11/12/2003

Thank you.
tony

 

[entaroadun]

The same way. MSTR generates a filter for the child report not from the column you are clicking on (i.e. Last Trans), but for the values from the entire row (i.e. ID, Customer, Order Date, and Last Trans).

 

[tonysut]

In this scenario, MSTR generates the child report with filters by ORDER_No and TRANS_TYPE. ORDER_DATE is different from TRANS_DATE. ORDER_DATE is actually 11/06/2003 (PO date). I still got

TRANS_No ORDER_No TRANS_TYPE TRANS_DATE
103 1 INVOICE 11/10/2003
104 1 INVOICE 11/12/2003

Is there a way to specify in the drill map or template to create SQL statement with SELECT TOP 1 and ORDER BY TRANS_DATE?
Thanks,
tony

 

[entaroadun]

Unfortunately, no. Drilling in MSTR is pretty limited. You can only drill to a template, and that drill can't have any additional filters applied to it.

You can try playing with the Report Data options in the template. You may be able to do some Report Limit with a Max(Trans_Date) expression that might work... I doubt it though.

What interface are people using to access the reports, Web or Desktop? If Web, you can fake a drill by modifying the XSL to actually point to a different Report ID.

 

[tonysut]

Thanks entaroadun.
People view the reports via Web interface. I create the reports via Desktop interface. Is there a way to control or suppress the filter on a child report when drilling from a parent report? For example, I only need a filter for the child report from the column I am clicking in the parent report. Not any other common columns between two reports.

 

[entaroadun]

It depends on the location of the column. For example, if you have three attributes on your report:

[tt]
Month Customer Product
June Customer A Product A
June Customer A Product B
June Customer B Product A
[/tt]

If you drill by clicking on Product on the first row, then the row you select is June, Customer A, and Product A. This is carried to the drill.

If you drill by clicking on Customer on the first row, then the block of rows you select is June, Customer A. This is carried to the drill.

If you drill by clicking on Month on the first row, then the block of rows you select is June. This is carried to the drill.

Hope this helps.

 

[tonysut]

Thanks entaroadun. That helps a lot.
I wonder what logic or method MTSR implements to join different SQL tables in a report created from different fields? My clients try to create a report using attributes(or metrics) from different SQL tables but the report sometimes throws an error or has no data. I look at the SQL View and have no idea how MSTR creates that SQL statement. Any resource I can look?
Thank
Tony

 

[entaroadun]

Assuming that you have an account with tech support, you can always bounce around the Knowledge Base.

The criteria is pretty straightforward though. By default, when you drill, the filter of the report you are drilling from is carried forward. Also, a filter corresponding to the row or block of rows you are drilling from is also carried forward. These filters are combined with a big AND.

If you are drilling to something completely unrelated, you may get weird SQL, just because the report that you are drilling from has filters on unrelated attributes or has complex filters on metrics. (When it comes to drilling, all metric filters are complex.)

If you have specific examples, post them and we can explain them. I'm sure it will help everyone on the forum.

Drilling isn't as fully-featured as it needs to be, and from what I've seen, 7.5 doesn't add much to this area. If you're building portals, which seems to be where the money is, it doesn't matter. You can always customize Web or Web Universal to "drill" to a completely unrelated report. That may be why this functionality isn't getting as much attention.