DrillThrough to Details.... Programming

Hello,

In the article on microsoft : http://msdn2.microsoft.com/en-us/library/aa175980(SQL.80).aspx

It explains how to use ADOMD and ADODB to retreive detailed information for the cell returned from the analysis service. For example, detailed information (such as student ID, First name, last name.., etc) about the student that's in location Cincinnati.

However, this example is quite old and doesn't apply to the new ADOMD.net (Microsoft.AnalysisServices.ADOMDClient). I tried to find any example on the web, but without success.

Is there a way to retrieve underlying detailed information?

Any help is greatly appreciated. Thank you very much,

Annie

Finally, I found some resources online and wrote the following code. It worked for me.

Dim AS_Conn As New ADOMDConnection(ConfigurationManager.AppSettings("OLAPConnection2005"))

AS_Conn.Open()

Dim AS_Comm As New AdomdCommand

Dim dt As new DataTable

Dim dr As DataRow

Dim dc As DataColumn

AS_Comm = AS_Conn.CreateCommand()

AS_Comm.CommandText = strMDX

Using rsDetails As AdomdDataReader = AS_Comm.ExecuteReader()

For intFieldCount As Integer = 0 To rsDetails.FieldCount - 1

dc = New DataColumn(rsDetails.GetName(intFieldCount))

dt.Columns.Add(dc)

Next

While rsDetails.Read

dr = dt.NewRow

For intFieldCount As Integer = 0 To rsDetails.FieldCount - 1

If NOT rsDetails.Item(intFieldCount) Is Nothing AndAlso TRIM(rsDetails.Item(intFieldCount)).Length > 0 Then

dr(intFieldCount) = rsDetails.Item(intFieldCount).ToString()

Else

dr(intFieldCount) = ""

End If

Next

dt.Rows.Add(dr)

End While

End Using

AS_Conn = nothing

drillthrough in pdf

Hi
MSDN say "Drillthrough links are not recommended for PDF output"
(http://msdn2.microsoft.com/en-us/library/ms159713.aspx), but it's possible?
How?Hi,
you can set hyperlinks to another reports, yes this is possible.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Thanks
I should transform <Drillthrough> to <Hyperlink> ?
from:
<Action><Drillthrough><ReportName>/Folder/SubReport</ReportName></Drillthrough></Action>
to:
<Action><Hyperlink>http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fFolder%2fSubReport&rs:Command=Render</Hyperlink></Action>
This one way?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168462105.283086.268290@.k58g2000hse.googlegroups.com...
> Hi,
> you can set hyperlinks to another reports, yes this is possible.
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Drill to detail in ProClarity

Hi,

This is from a previous post I had put,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=973310&SiteID=1

ProClarity gives a "Drill-To-Detail" by default. When you click on that it shows the key columns for the fact tables. It uses Drillthrough for this.

I tried defining drillthrough actions in ssas 2005, but they come up seperately as actions in proclarity. The "Drill-To-Detail" is still displayed on right-click at proclarity.

The actions are working in ssas 2005, but according to the definition, if a drillthrough is executed, if no return columns are specified, then it picks the return columns from the action that resembles similar query target. This does not seem to work for me!

if nothing can I at least remove the "Drill-To-detail" from ProClarity? please help.

Regards

Hi Vijay,

Could you explain this in more detail, because it doesn't sound identical to the Drillthrough whitepaper verbiage:

"then it picks the return columns from the action that resembles similar query target."

http://msdn2.microsoft.com/en-us/library/ms345125.aspx

>>

...

When a client sends a DRILLTHROUGH statement that does not contain the RETURN clause, the server looks for a default drillthrough action whose target subspace contains the cell coordinate for which drillthrough is being executed.

>>

In what way does it not work for you - what results do you get? Also, have you tried accessing the "xxxDetails" Drillthrough actions in the Adventure Works cube from Proclarity - do they work as expected?

|||

Hi Deepak,

Thanks for the reply.

Apologies, this is what I meant by my statement :

"When a client sends a DRILLTHROUGH statement that does not contain the RETURN clause, the server looks for a default drillthrough action whose target subspace contains the cell coordinate for which drillthrough is being executed."

Here is the query that ProClarity sends out when you right-click on any cell and click on the "DrillThrough"

DRILLTHROUGH MAXROWS 1000 SELECT { ( [Measures].[ABC] ) } ON COLUMNS ,

{ ( [DATE].[NAME].&[1.001734E6] ) } ON ROWS

FROM [ZZZCUBE]

WHERE ( [DATE].[DAY OF MONTH].[All], [DATE].[DIM MONTH NAME].[All], [DATE].[DIM YEAR NAME].[All], [LL_USER_TYPE].[DIM_LL_USER_TYPE_KEY].[All], [LL_USER_TYPE].[DIM_LL_USER_TYPE_NAME].[All], [LL_USER_TYPE].[DIM_LL_USER_TYPE_SHORT_NAME].[All], [SLICE_LOCATION].[KEY].[All], [SLICE_LOCATION].[NAME].[All], [SLICE_LOCATION].[SHORT NAME].[All], [LOCATION].[KEY].[All], [LOCATION].[NAME].[All], [LOCATION].[SHORT NAME].[All], [APPLICATION].[DIM_APPLICATION_KEY].[All], [APPLICATION].[DIM_APPLICATION_NAME].[All], [APPLICATION].[DIM_APPLICATION_SHORT_NAME].[All], [DATACENTER].[DIM_DATACENTER_KEY].[All], [DATACENTER].[DIM_DATACENTER_NAME].[All], [DATACENTER].[DIM_DATACENTER_SHORT_NAME].[All], [MONTH].[DIM_MONTH_NAME].[All], [MONTH].[DIM_MONTH_SHORT_NAME].[All], [MONTH].[KEY].[All], [ALL_COUNTRIES].[KEY].[All], [ALL_COUNTRIES].[NAME].[All], [ALL_COUNTRIES].[SHORT NAME].[All] )

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>CON4QA</Catalog><Timeout>0</Timeout><Format>Native</Format><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><Dialect>MDX</Dialect><MdxMissingMemberMode>Error</MdxMissingMemberMode><LocaleIdentifier>1033</LocaleIdentifier><DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility></PropertyList>

Notice, it contains no return columns. but it contains a <PropertyList>.

This cube's measureGroups contains 2 measures, out of which one is selected for the graph. The cube's measureGroup uses 3 dimensions, two are used in the graph. that would be, let us say, Measure M1 (background) , Dim Date (rows) , Dim Type(columns).

So I created a drill through action for the same MeasureGroup. Then in return columns, I selected only one measure (the one used in graph- M1). Then I selected the dimensions which I have used in the graphs (Dim Date.Name, Dim Type.Name).

After saving everything, I browse the same Proclarity graph , right click on a cell and click on Drill-To-Detail. I get the following columns in the drillthrough window that gets opened.

Date.Name (because Date dimension has Name as its key attribute)
Type.Key
location.key (not in graph)

M1 (measure)
M2 (measure) (not in graph)

My problem is , why are dimensions and measures that are not used in the graph coming? (although they are used by the measuregroup as displayed in the dimension usage tab of BI studio)

In any case, I do not want the "Key" attribute of dimension to be displayed in the drill-to-detail. If I get the "Name" attribute instead of the key attribute, then it would be okay with me. that is what I am trying to do.

note. In the BI studio browser, we do not have a default drillthrough in the UI. If we define a drillthrough action on the measureGroup, then it gets rendered in the BI studio browser. Also this action is visible in Proclarity, under 'actions'. but the drill-to-detail still exsits. The actions that I have created are working fine by themselves.

Please tell me if you need any other information?

How can I solve this,

Regards

|||

Hi Deepak,

I forgot to ask this,

what does "target subspace contains the cell coordinate......" mean?

How can I create a default drillthrough, which will be used whenever the user does a drillthrough on any of the cells of the a particular cube/measuregroup/dimension combination?

regardig the 'xxxDetails' drillthrough in Adventure Works, I will check it.

Thanks!

Regards

|||

The TargetType for Drillthrough Actions is "Cells" - for example, the definition of the "Reseller Details" action in Adventure Works includes:

<TargetType>Cells</TargetType>
<Target>MeasureGroupMeasures("Reseller Sales")</Target>

So it applies only to cells in the "Reseller Sales" Measure Group - but you can select <All> as the Target for a Drillthrough which applies across all Measure Groups. You should test your Drillthrough Action directly, using an MDX query in Management Studio, to ensure that it is returning the correct columns. For example:

>>

Drillthrough

select {[Measures].[Reseller Order Quantity]} on 0,

[Date].[Calendar].[Month].&[2001]&[7] on 1

from [Adventure Works]

where [Product].[Product Categories].[Product Name].&[300]

-

[Reseller Sales].[Reseller Sales Amount] [Reseller Sales].[Reseller Order Quantity] [Reseller Sales].[Reseller Extended Amount] [Reseller Sales].[Reseller Tax Amount] [Reseller Sales].[Reseller Freight Cost] [Reseller Sales].[Discount Amount] [Reseller Sales].[Reseller Unit Price] [Reseller Sales].[Unit Price Discount Percent] [Reseller Sales].[Reseller Total Product Cost] [Reseller Sales].[Reseller Standard Product Cost] [$Reseller].[Reseller] [$Promotion].[Promotion] [$Employee].[Employee] [$Delivery Date].[Date] [$Sales Territory].[Sales Territory Region] [$Reseller Sales Order Details].[Carrier Tracking Number] [$Reseller Sales Order Details].[Customer PO Number] [$Reseller Sales Order Details].[Sales Order Number] [$Reseller Sales Order Details].[Sales Order Line] [$Product].[Product] [$Ship Date].[Date] [$Source Currency].[Source Currency Code] [$Date].[Date] [$Geography].[City] [$Geography].[State-Province] [$Geography].[Country] [$Destination Currency].[Destination Currency Code]
809.76 1 809.76 64.7808 20.244 0 809.76 0 699.0928 699.0928 Original Bicycle Supply Company No Discount Jos Edvaldo. Saraiva July 13, 2001 Canada 4E0A-4F89-AE PO18473189620 SO43661 1 HL Mountain Frame - Black, 48 July 8, 2001 CAD July 1, 2001 Toronto Ontario Canada USD

>>

The list of columns corresponds to those specified in the definition of the "Reseller Details" action.

drill down spid for high CPU

http://www.support.microsoft.com/?id=117559
I have read something equivalent to the article above but for SQL 2K that
drills down to a particular spid while troubleshooting for high CPU usage.
I cant seem to recollect where I read it. Does anybody know ?
ThanksCheck out DBCC SQLPERF (THREADS)
Bart Simpson
On Tue, 29 Jul 2003 13:04:22 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>http://www.support.microsoft.com/?id=117559
>I have read something equivalent to the article above but for SQL 2K that
>drills down to a particular spid while troubleshooting for high CPU usage.
>I cant seem to recollect where I read it. Does anybody know ?
>Thanks
>|||Well not too sure if that was it.. but more of an article in the same line
as the link I sent
"Bart Simpson" <bartsimpson000@.yahoo.com> wrote in message
news:oipdivs7otcna04mc5ct9lifk8d8huaris@.4ax.com...
> Check out DBCC SQLPERF (THREADS)
> Bart Simpson
> On Tue, 29 Jul 2003 13:04:22 -0700, "Hassan" <fatima_ja@.hotmail.com>
> wrote:
> >http://www.support.microsoft.com/?id=117559
> >
> >I have read something equivalent to the article above but for SQL 2K that
> >drills down to a particular spid while troubleshooting for high CPU
usage.
> >
> >I cant seem to recollect where I read it. Does anybody know ?
> >
> >Thanks
> >
>