Re: Using SetToStr problem

#1

I have figured a few more things out but I'm still fundamentaly stuck
on this problem.

I am now able to pull all of the dates of the transactions for a given
entity for a given quarter using the following:

WITH

MEMBER [Measures].[IRRResultDates] AS '
CustomXIRR(
SetToStr(
NonEmptyCrossJoin(Descendants([PeriodDownToDay].[All
PeriodDownToDay].[1997].[Quarter 1], Day), {[Entity].CurrentMember})
)
)'

SELECT {[Measures].[IRRResult]} ON COLUMNS,
{[Entity].[All Entity].[i].Children} on ROWS
FROM
[ContribDistrib]

This sends a string containing all of the relevant PeriodDownToDay
members for each entity to my external function. Something like:
{([PeriodDownToDay].[All PeriodDownToDay].[1997].[Quarter
1].[1/1997].[1/27/1997], [Entity].[All Entity].[i].[1 - 123 Anywhere
Lane (I)]), next entity, etc...}"

I could then parse out all of the dates.

I would ideally like to use the SetToArray function to only send the
dates instead of the date members but I'm not sure out to get the
transaction date measure into an array to pass. I guess if I could
get the date measure then the acutal transaction amount measure would
be the same.

Can anyone help?

Thanks,

Greg

gregcarder (AT) hotmail (DOT) com (Greg) wrote in message news:<d61bada1.0307300953.6bde788 (AT) posting (DOT) google.com>...

Quote:

Hi all,

I'm sure I'm missing something simple. My problem is described
below...

I have 2 dimensions in my cube.

Time dimension with hierarchy of year -> qtr -> day.
Entity dimension that contains real estate properties.

The fact table contains the days that transactions occurred for each
property and the amount of each transaction.

I need to perform an XIRR calculation and I'm trying to pass an array
of the transactions that occurred during a given quarter for a given
entity to an external function that ends up calling the XIRR function
from the Excel addin toolkit.

I need to get an array containing the dates of any transactions that
happened and an array containing the values for each of the
transactions.

For the example below I'm just trying to get the array of dates.

My MDX is below:

---------------
WITH

MEMBER [Measures].[IRRResult] AS '
CustomXIRR(SetToStr({([Entity].CurrentMember,
[PeriodDownToDay].CurrentMember, [Measures].[Tw Date])}))'

SELECT
NON EMPTY {[Measures].[IRRResult] } ON COLUMNS,
NON EMPTY {[Entity].[All Entity].[i].Children} on rows
FROM
[ContribDistrib]
WHERE ([PeriodDownToDay].[All PeriodDownToDay].[1997].[Quarter 1])
---------------

When I debug into my external function in .NET I see something like
the following:

{([Entity].[All Entity].[i].[1 - 123 Anywhere Lane(I)],
[PeriodDownToDay].[All PeriodDownToDay].[1997].[Quarter 1],
[Measures].[Tw Date])}

What I wanted was an actual set of the days from the fact table for
the current entity/quarter.

Any hints on what I should try instead?

Thanks,

Greg

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Using SetToStr problem

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: Using SetToStr problem - 08-04-2003 , 12:59 PM