Results 1 to 3 of 3

Thread: Calculated Member Count problem

  1. #1
    Join Date
    Jul 2005
    Location
    Chicago, IL
    Posts
    4

    Question Calculated Member Count problem

    I've been having difficulties with the following mdx query:

    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].[2004],
    [Jurisdiction].[All Jurisdiction].[State]
    )



    The problem is [Claim Count]. I want the claim count on each row to be the highest claim count from selected rows and placed on each row. The count is way under-reported. However, If I use a simple count statement or distinct count then only the last 2 rows have the count value I am looking for. It doens't seem to be affected by the where clause or the aggregated sets so it is over-reporting on the claim count. Anyone have any thoughts on how to solve this problem?

    Basically, I'm cross joining law firms with tasks that show expenses and fees submitted by law firms for billing. I need to calulate averages by dividing the dollar amounts by a claim count. The twist is that the claim count I am looking for only occurs on the indemnity and prior legal fees rows because I know that every claim/case has an indemnity and prior-legal fee transaction in the fact table. The others types of transactions don't. So I have to assume the claim count is max claim count calculated on one of the rows and that number needs to be applied to the rest. Also, the count has to be adjusted by the slicers in the where clause (when I used a simple count statement it wasn't adjusting as I changed the values in the where clause) The slicers in the where clause change the date, or the jurisdiction as well as other dimensions. Hope this information has helped. Any suggestions would be greatly appreciated.

  2. #2
    Join Date
    Jul 2005
    Location
    Chicago, IL
    Posts
    4

    One other thing

    One other thing. It seems to me, the problem might be related to the case status in the where clause. When I remove [Case].[All Case].[C], the numbers get a lot closer to accurate. Most of the cases in the cube are closed,
    probly 90 percent, and when I remove case status out of the where clause the numbers seem pretty accurate (although it is including open and incomplete cases as well). When I put the filter on closed cases back, I get a claim count that is probably 10% of the correct result on claim count. I'm
    starting to think that maybe the problem isn't MDX related and maybe it's a problem with the cube/dimension structure.

  3. #3
    Join Date
    Jul 2005
    Location
    Chicago, IL
    Posts
    4

    Problem Solved

    Problem solved. The MDX is fine. There was a flaw in the aliased date dimensions. Had to fix the cube design and the claim count problem went away.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •