mdx question

Hi,
I was wondering if anyone could help me with a bit of mdx.
We want to display something like this:
Year
+ Month
Company
Num of Cases
Num of Cases that haven't been paid
We want to show the number of cases for a company that were created in
certain year/month time slices but we also want to show the number of cases
that haven't been paid.
We find if the the case has not been paid or not from child table so we have
following situation:
Fact Table: Case - Measure is id (Count)
Dimension Tables: Case Company - Level is name
Case - level is created date
Case further details - paid date
We have tried many possibilities but can't seem to get the number of cases
for when the paid date is empty. I suppose our best try is:
Count(
Filter(
CrossJoin([Measures].members, [PaidDate].Members),
not IsEmpty([PaidDate].CurrentMember )
)
)
but this gives the wrong value. All the other attempts give us errors
(either syntactical or runtime).If I understand your message.
You can create a different cube called Cases that have this set of
dimension: Periods.Year.Month and Company and for measures that have value
of paid or code that show if it have been paid or not.
On principal cube you will use LookUpCube("Cases","(code)") where (code) is
equal to :
Count(<set>,measures.value of Cases cube) this <set> will be composed from
dimension that you have in Cases.
Remeber that dimensions in Cases cube must be exists in principal cube and
must be equal for name and level etc..
I hope that I help you.
Bye.
"FraserS" <FraserS@.discussions.microsoft.com> ha scritto nel messaggio
news:7899446A-6E43-4975-B1BD-16872EB93F93@.microsoft.com...
> Hi,
> I was wondering if anyone could help me with a bit of mdx.
> We want to display something like this:
> Year
> + Month
> Company
> Num of Cases
> Num of Cases that haven't been paid
>
> We want to show the number of cases for a company that were created in
> certain year/month time slices but we also want to show the number of
> cases
> that haven't been paid.
>
> We find if the the case has not been paid or not from child table so we
> have
> following situation:
> Fact Table: Case - Measure is id (Count)
> Dimension Tables: Case Company - Level is name
> Case - level is created date
> Case further details - paid date
>
> We have tried many possibilities but can't seem to get the number of cases
> for when the paid date is empty. I suppose our best try is:
>
> Count(
> Filter(
> CrossJoin([Measures].members, [PaidDate].Members),
> not IsEmpty([PaidDate].CurrentMember )
> )
> )
>
> but this gives the wrong value. All the other attempts give us errors
> (either syntactical or runtime).
>|||With OLAP date dimensions, you would typically set an arbitrary large value
(say, 12/31/9999) to indicate that an event (in this case, payment) had not
yet occurred. Note that IsEmpty([PaidDate].CurrentMember) does not test for
an empty date. So, the Num of Cases that haven't paid, for a selected
[CreatedDate], would then be:
([Measures].[CaseCount], [Paid Date].[12/31/9999])
"FraserS" wrote:
> Hi,
> I was wondering if anyone could help me with a bit of mdx.
> We want to display something like this:
> Year
> + Month
> Company
> Num of Cases
> Num of Cases that haven't been paid
>
> We want to show the number of cases for a company that were created in
> certain year/month time slices but we also want to show the number of cases
> that haven't been paid.
>
> We find if the the case has not been paid or not from child table so we have
> following situation:
> Fact Table: Case - Measure is id (Count)
> Dimension Tables: Case Company - Level is name
> Case - level is created date
> Case further details - paid date
>
> We have tried many possibilities but can't seem to get the number of cases
> for when the paid date is empty. I suppose our best try is:
>
> Count(
> Filter(
> CrossJoin([Measures].members, [PaidDate].Members),
> not IsEmpty([PaidDate].CurrentMember )
> )
> )
>
> but this gives the wrong value. All the other attempts give us errors
> (either syntactical or runtime).
>|||Hi, thanks for your replies.
In order to achieve this, we tried to set the default member to an arbitrary
large value like you said. This didn't work. How should we go about doing
this?
"Deepak" wrote:
> With OLAP date dimensions, you would typically set an arbitrary large value
> (say, 12/31/9999) to indicate that an event (in this case, payment) had not
> yet occurred. Note that IsEmpty([PaidDate].CurrentMember) does not test for
> an empty date. So, the Num of Cases that haven't paid, for a selected
> [CreatedDate], would then be:
> ([Measures].[CaseCount], [Paid Date].[12/31/9999])
>
> "FraserS" wrote:
> > Hi,
> >
> > I was wondering if anyone could help me with a bit of mdx.
> >
> > We want to display something like this:
> >
> > Year
> >
> > + Month
> >
> > Company
> >
> > Num of Cases
> >
> > Num of Cases that haven't been paid
> >
> >
> >
> > We want to show the number of cases for a company that were created in
> > certain year/month time slices but we also want to show the number of cases
> > that haven't been paid.
> >
> >
> >
> > We find if the the case has not been paid or not from child table so we have
> > following situation:
> >
> > Fact Table: Case - Measure is id (Count)
> >
> > Dimension Tables: Case Company - Level is name
> >
> > Case - level is created date
> >
> > Case further details - paid date
> >
> >
> >
> > We have tried many possibilities but can't seem to get the number of cases
> > for when the paid date is empty. I suppose our best try is:
> >
> >
> >
> > Count(
> >
> > Filter(
> >
> > CrossJoin([Measures].members, [PaidDate].Members),
> >
> > not IsEmpty([PaidDate].CurrentMember )
> >
> > )
> >
> > )
> >
> >
> >
> > but this gives the wrong value. All the other attempts give us errors
> > (either syntactical or runtime).
> >