Greetings,

We have cube with a Dimension (three levels) :

Level 1 - Branch

level 2 - Products

Level 3 - Accounts

All the Accounts have Member Properties associated with them such as
"Account Opening Date".
The Cube has measures such as Outstanding Principal Amount / Outstanding
Interest Amount / Loan Disbursed Amount.

We use the Member properties (Account Opening Date) to Filter the Account
based on the User Inputs and we display the data grouped on Product ( the
Data is grouped at SRS 2k)

We also need to display the Count of Total Customers & New Customers added
within the Date Range (which is user specified) product wise.

Here is the Query we have written to get the Total Customer Count Till Date :-

WITH


MEMBER
[MEASURES].[CalcCount] AS
'COUNT(CROSSJOIN({[MEASURES].[RelativeCalcCount]},
DESCENDANTS([Customers].CURRENTMEMBER,
[Customers].[Customer ID])), EXCLUDEEMPTY)'

MEMBER
[MEASURES].[RelativeCalcCount] as ' CoalesceEmpty(
([MEASURES].[Customers Count], [Transaction Dates].CurrentMember),
([MEASURES].[RelativeCalcCount], [Transaction
Dates].currentmember.prevmember))'


SELECT
{ [MEASURES].[CalcCount]} ON 0,
-- [Customers].CHILDREN ON 1,
[ProductsAccounts].[Product IDBR].Members on 1

FROM LoansAndSavings
WHere
([Transaction Dates].[All Transaction Dates].[2004].[Quarter 4].[October].[1])

Now our problem is getting the Count of New Customers , how do we introduce
the From Date to get the New Customers.

Thanks,
Ravi Jitani

Hi Ravi,

You may consider adding another date dimension, to faciliate user
selection of a From Date. In AS 2005, a date dimension can be added to a
cube in multiple roles. But if you're using AS 2000, this past post
describes an approach to selecting time ranges:

http://groups.google.com/group/micro...olap/msg/27564
ddc49de36a1

From: Chris Webb [MS]
Date: Thurs, Feb 6 2003 9:13 am
Groups: microsoft.public.sqlserver.olap

Rather than let the user enter a date manually, you might want to do the
following instead: create two Time dimensions in your cube, one where
the
user can select the start of the time range and one where they select
the
end of the time range. You can then use calculated measures to return
the
aggregated values. Here's how you do this:

In your existing cube, call your Time dimension something like 'Start
Date'.
Then copy this dimension, and paste it into the same database; you will
get
a dialog asking you for a new name for this dimension, and you should
call
it something like 'End Date'. Next, create a virtual cube containing all
the
dimensions and measures from your regular cube. Since you can't add your
'End Date' dimension to this virtual cube in Analysis Manager (it isn't
connected with any cube), you need to use some DSO code to add it -
something like the following bit of VBScript is all you need:

Dim myserver
Dim db
Dim vcube
Dim fromdim
Set myserver=createobject("dso.server")
myserver.connect("MyServer")
Set db = myserver.mdstores("MyDatabase")
Set vcube = db.mdstores("MyVirtualCube")
Set fromdim = vcube.dimensions.addnew("End Date")
Vcube.update

You then have a virtual cube with two Time dimensions your users can use
to
set the start and end dates in their Time range. Finally you need to
create
some calculated measures that do the summing - the MDX would be
*something*
like:

SUM({
OPENINGPERIOD([START DATE].[LOWEST LEVEL], [START DATE].CURRENTMEMBER)
:
CLOSINGPERIOD([START DATE].[LOWEST LEVEL], LINKMEMBER([END
DATE].CURRENTMEMBER, [START DATE]))

}, VALIDMEASURE(MEASURES.[MY MEASURE]))
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***