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 is incomplete

I have a strange problem in my cube when drilling down to details.

If I run the following query and leave out the DRILLTHROUGH, I get only one number as result, 7.

The Measure Counter is defined as "COUNT on lines" of the source table. So I expect that the number 7 results from 7 lines in the source table - And when I run an SQL query translated to use the same filter options as WHERE part, I do indeed get 7 lines matching the date and other settings I asked for. Everything ok so far.

When I execute the MDX query with DRILLTHROUGH I get only 3 lines as result - I remove the DRILLTHROUGH again and get 7 as result again... what can be the reason for this?

DRILLTHROUGH SELECT {[Measures].[Counter]} ON 0 FROM [Cube] WHERE ([Final Level].[Level Hierarchy].&[-6], [Action].[Action].&[4], [Submitted Date].[Submitted Date].[Submitted Year].&[2007], [LMU-SL].[LMU-SL Hierarchy].&[3])

Perhaps there are duplicate records at the measure group granularity, therefore in the cube you ended up with only 3 distinct records, but the total value is still correct - 7.|||

Mosha Pasumansky wrote:

Perhaps there are duplicate records at the measure group granularity, therefore in the cube you ended up with only 3 distinct records, but the total value is still correct - 7.

Mosha, thanks for answering.

I understand what you mean, but I can confirm that this is not the reason / solution.

I did confirm that in fact the 7 rows are distinct for the granularity of the measure group. They each have their own primary key in the database, and the measure group is based on this table, on this pk.

I browse the cube in Excel, and there are more combinations like this one where a number of lines is counted in cube ("11" in this other case), 11 distinct lines can be confirmed using sql, but no line at all is returned for drill. If I create my MDX without drillthrough, I get "11" as value for the measure. If I put "drillthrough" before it - I get no results at all.

As always, it is all far more complicated, as you guessed... My original MDX showed 4 dimensions sliced. 3 of them are really dimension on the measure group, these dimensions are joined in as regular to the measure group. The 4th one is a dimension on a measure group with a finer grain, a deeper level of detail as I try to imagine it. This 4th dimension is joined to the finer grain as a regular dimension, and to the measure group queried using m-n joining "back up from finer detail level".

For information, I can display a row count on the detail measure group as well, and these numbers in the cube are also perfectly in line with tests done using sql. But drills to the detail group give no results at all (even if the cube showed some 10 (distinct) detail lines for this dimension, verified using sql.

I suspect the problem is around that m-n from a finer level, since drilling into both measure groups without using this dimensions mix from different grains works perfectly fine. I am quite sure my dimension - measure setup is correct, since all counts in the cube come out the same value as manual sql test comparisons.

I am really a bit worried here with the reliability of SSAS 2005.

I am now cleaning out the db to only host the 7 lines in question as all data available, opposed to 2.5 mill on the queried level and 10 mill lines in the finer grain section to see if that makes any change in the result, and reload the whole SSAS db with this greatly simplified amount of data. I might also use CTP of SP2 to see what happens.

|||

Ralf_from_Europe wrote:

I am now cleaning out the db to only host the 7 lines in question as all data available, opposed to 2.5 mill on the queried level and 10 mill lines in the finer grain section to see if that makes any change in the result, and reload the whole SSAS db with this greatly simplified amount of data.

I think I found out the worst I could imagine: With less data loaded, or better: only data that falls within the area I slice for, I get ALL my 7 lines as result in drill and as cube measure. I did not change any settings in the project or made any changes to the query. I only threw out around 12 mill lines of data from the source db... I really doubt the precision, or maybe better the reliability of SSAS now.

In case anyone wants to know I noticed the difference between the lines given back and not given back: I suspect the problem I have is somewhere along the aggregations build for the cube. Why? The lines that work are in one category of a dimension (a pretty simple one with the order state, 8 items, like in "ordered", "mailed", "received" or "returned" and so). Anyway, the lines that work are in one state, the ones that do not are in another state.

This dimension is NOT used in this example, and also not part of my MDX query, neither with or without the drillthrough part. I suspect there is no aggregation on this dimension since the design wizard did not call an 8 entry dimension worth it. Then again, I did not change the aggregation setup, I only cleaned the source db of other data...

I am SERIOUSLY in doubt now! Trying SP2 CTP now...

Drilldown reports

I want to show a list of accounts and when a user clicks on one show
account's details.
What is a best way of approaching that?
ThanksIf you generate a dataset with all the accounts' details you could then use a
table control with grouping by account. You would hide the detail line by
default but allow this to be toggled from a textbox on the group
header/footer.
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Mark Goldin" wrote:
> I want to show a list of accounts and when a user clicks on one show
> account's details.
> What is a best way of approaching that?
> Thanks
>
>|||But since I have a detailed report already how can I create report with
links to the detailed report?
"magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
news:FEE2EC61-3596-4C90-92FE-C1A1EDF5DF6E@.microsoft.com...
> If you generate a dataset with all the accounts' details you could then
> use a
> table control with grouping by account. You would hide the detail line by
> default but allow this to be toggled from a textbox on the group
> header/footer.
> HTH
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland
>
> "Mark Goldin" wrote:
>> I want to show a list of accounts and when a user clicks on one show
>> account's details.
>> What is a best way of approaching that?
>> Thanks
>>|||Most of your detailed report is not visible because the detail line is
hidden. You will only see a list of account numbers. If you click the +
toggle, which you would probably have beside the account number, you would
then see all the detail for that account.
Your other option is a separate drilldown report. To do this you would need
to define an "action" linked to the account number textbox which opened
another report. One of the parameters you would pass to the drilldown report
is the account number.
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Mark Goldin" wrote:
> But since I have a detailed report already how can I create report with
> links to the detailed report?
> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
> news:FEE2EC61-3596-4C90-92FE-C1A1EDF5DF6E@.microsoft.com...
> > If you generate a dataset with all the accounts' details you could then
> > use a
> > table control with grouping by account. You would hide the detail line by
> > default but allow this to be toggled from a textbox on the group
> > header/footer.
> >
> > HTH
> >
> > --
> > Magendo_man
> >
> > Freelance SQL Reporting Services developer
> > Stirling, Scotland
> >
> >
> > "Mark Goldin" wrote:
> >
> >> I want to show a list of accounts and when a user clicks on one show
> >> account's details.
> >> What is a best way of approaching that?
> >>
> >> Thanks
> >>
> >>
> >>
>
>

Drill Through From Drill Down

I have a report grouped that has a drill down, from the drill down
records, I can drill through to another report that has the details of
the records recorded on the drill through.
Example:
Location (Drill Down)
+York
+Cardiff
+London
Displays:
-York
Team1
Team2
Team3
-Cardiff
Team1
Team2
Team3
-London
Team1
Team2
Team3
Each of the Teams below the locations have a drill-through connection
to another report. My issue is that the Location parameter is only
being passed to one of the Locations on the Drill Through. The other
locations are asking for a location parameter.I found this issue. I had been 'caseing' my initial display to format
the display, and this then passed through a wrong parameter value to
the drill through report.