RE: Calculated Member question

I thought I had it! After some internal data auditing it looks like the
claim count is way under-reporting. Could you take a look at my posted MDX
and let me know if you see any glaring issues? Thanks

Chad

The full query is as follows:

WITH MEMBER [Measures].[Group] AS
'Mid(MemberToStr([PhaseTask].CurrentMember),30,1)' MEMBER
[Measures].[LawFirmDesc] AS 'MemberToStr([Lawfirm].CurrentMember)' SET
[ClaimCloseDate_Set] AS '{[ClaimCloseDate].[All ClaimCloseDate].[2004] }'
MEMBER [ClaimCloseDate].[All ClaimCloseDate].[SelectedClaimCloseDates] AS
'Aggregate([ClaimCloseDate_Set])' SET [Jurisdiction_Set] AS
'{[Jurisdiction].[All Jurisdiction].[State]}' MEMBER [Jurisdiction].[All
Jurisdiction].[SelectedJurisdiction] AS 'Aggregate([Jurisdiction_Set])'MEMBER
[Measures].[ClaimCount] AS
'Count(NonEmptyCrossJoin(Descendants([Case].CurrentMember,,LEAVES),[ClaimCloseDate_Set],
[Jurisdiction_Set],{[PhaseTask].[All PhaseTask]}, {[Lawfirm].CurrentMember},
1))' SELECT
{[Measures].[LawFirmDesc],[Measures].[Group],[Measures].[ClaimCount],[Measures].[Legal
Fees Approved], [Measures].[Expenses Approved], [Measures].[Hours Approved]}
ON COLUMNS, CrossJoin ({[PhaseTask].[All PhaseTask].[L100
Assessment].Children, [PhaseTask].[All PhaseTask].[L200
Pre-Trial].Children,[PhaseTask].[All PhaseTask].[L300
Discovery].Children,[PhaseTask].[All PhaseTask].[L400
Trial].Children,[PhaseTask].[All PhaseTask].[L500
Appeal].Children,[PhaseTask].[All PhaseTask].[E100
Expenses].Children,[PhaseTask].[All PhaseTask].[Indemnity],[PhaseTask].[All
PhaseTask].[Prior Legal Fees]}, {[Lawfirm].[All Lawfirm]}) ON ROWS FROM
Datamart_Vis_Ins165_Claim WHERE ([Case].[All Case].[C], [FeeAgreement].[All
FeeAgreement].[Hourly + Expenses], [ClaimCloseDate].[All
ClaimCloseDate].[SelectedClaimCloseDates], [Jurisdiction].[All
Jurisdiction].[SelectedJurisdiction])



"Deepak Puri" wrote:

> This MSDN Paper covers Distinct Count Analysis:
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k
> /html/distinct2.asp
> >>
> Analysis Services: DISTINCT COUNT, Basket Analysis, and Solving the
> Multiple Selection of Members Problem
>
> Amir Netz
> Microsoft Corporation
>
> Updated May 18, 2004
>
> Applies to:
> Microsoft SQL Server 2000
> Microsoft SQL Server 2000 Analysis Services
>
> Summary: This article discusses ways to meet information demands and
> take full advantage of Analysis Services 2000 by illustrating the use of
> calculated members and multidimensional expressions (MDX). (10 printed
> pages)
> ...
> >>
>
>
> One approach would be to use NonEmptyCrossJoin():
>
> >>
> MEMBER [Measures].[ClaimCount] AS
> 'Count(NonEmptyCrossJoin(Descendants(
> [Case].CurrentMember,,LEAVES),
> [ClaimCreationDate_Set],
> [ClaimCloseDate_Set],
> [ClaimAssignmentDate_Set],
> {[Lawfirm].CurrentMember},
> {[Jurisdiction].CurrentMember}, 1))'
> >>
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
.

Relevant Pages

  • Re: Distinct Count
    ... In Yukon, however, ... Analysis Services: DISTINCT COUNT, Basket Analysis, and Solving the ... Microsoft SQL Server 2000 Analysis Services ...
    (microsoft.public.sqlserver.olap)
  • RE: Storage Design - some questions. (Deepak , r u there?)
    ... Microsoft SQL Server 2000 Analysis Services Performance Guide ... This paper describes techniques you can use to optimize query ...
    (microsoft.public.sqlserver.olap)
  • RE: Calculated Member question
    ... "Deepak Puri" wrote: ... > Analysis Services: DISTINCT COUNT, Basket Analysis, and Solving the ... > Microsoft SQL Server 2000 Analysis Services ...
    (microsoft.public.sqlserver.olap)
  • Re: Cell Calculation Performance
    ... the Microsoft Visual Basic® for Applications Expression Services ... This library is included with Analysis Services and ... Deepak Puri ... Microsoft MVP - SQL Server ...
    (microsoft.public.sqlserver.olap)
  • RE: CreatePropertySet in ASY!?
    ... Migrating from Microsoft SQL Server 2000 Analysis Services to SQL Server ... Deepak Puri ...
    (microsoft.public.sqlserver.olap)
Loading