Hi All,

I have fact table with granularity up to every line item of the transaction.

CREATE TABLE [dbo].[TrxOrder] (
[OrderID] [int] NOT NULL ,
[OrderDate] [datetime] NULL ,
[ProdukId] [int] NOT NULL ,
[Amount] [smallint] NOT NULL ,
....,
....,
....)

Consequently, when I drillthrough there many rows for one Order number (as
many as the line item).
I only want to display ''DISTINCT"ly on OrderID. (One row only for each
Order)
Can I / How can I do that ?

Thank you for your help,
Krist

This is one of the problems with drillthrough. It varies from
person-to-person and application-to-application what drillthrough actually
is -- and how it should act.

Most users would want *all* of the transactions which make up the number to
be returned on a drillthrough. But as you are describing, it is prefectly
reasonable that other semantics would be more appropriate in a different
application. With drillthrough you can't really control what or in what
format is being returned. It will always be a rowset, it will always come
from the RDBMS fact table only, and it must be all of the rows. Also, if you
are writing your own client front-end, remember that what is actually
returned is a collection of rowsets; one rowset for each partition.

This is one of the reasons why I frequently recommend that folks look at
Analysis Services "Actions" rather than drillthrough.
Using a cell-level Action to invoke an external application is a much more
flexible and customizible solution, albeit requiring more "programming".
--
Dave Wickert [MS]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"tristant" <krislioe (AT) cbn (DOT) net.id> wrote