Iterative Calculations Analysis Server 2000

Hi all

I'm looking for some help on an iterative cell calculation. I read somewhere that this can be done using a combination of the calculation pass value and calculation pass depth. I'm still having trouble though.

The easiest way of explaining this is to use an example. Sales reps get incentives based on profit, however incentives affect expenses which in turn affects profits. Using a starting income statement with no incentive about I need to go through about 20 iterations to come an answer.

So basically we have the following lines - (Abridged version )

Income - 10000

Other Expenses - 5000

Incentives = ( Profit * 0.025 )

Profit = ( Income - Other Expenses - Incentives )

Over 20 passes we should have the following

Income - 10000

Other Expenses - 5000

Incentives = 121.95

Profit = 4878.05

Any help would be appreciated.

Thanks

Garron

Hi Garron,

Since your sample only involves iteration, without using data from any specific OLAP cube, the MDX below uses the Foodmart Sales cube only as a place-holder. The CALCULATION_PASS_DEPTH=20 specifies the number of iterations, which should culminate at CALCULATION_PASS_NUMBER = 21. In the Sample MDX App, the Ivalues returned are: Incentives = 121.951, Profit = 4878.049

>>

With Member [Measures].[Income] as '10000'
Member [Measures].[OtherExpenses] as '5000'
Member [Measures].[Incentives] as '0'
Member [Measures].[Profit] as
'[Measures].[Income] - [OtherExpenses]
- [Measures].[Incentives]'

Cell Calculation [IterativeIncentives]
for '({ [Measures].[Incentives] })'
as 'CalculationPassValue([Measures].[Profit],
-1, RELATIVE) * 0.025',
CALCULATION_PASS_NUMBER = 21,
CALCULATION_PASS_DEPTH = 20

select {[Measures].[Income],
[Measures].[OtherExpenses],
[Measures].[Incentives],
[Measures].[Profit] } on columns
from Sales

>>

Here's a link to the AS 2000 BOL section which explains Pass Order:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxadvanced_6jn7.asp

>>

Understanding Pass Order and Solve Order

Two of the most powerful and, correspondingly, most difficult concepts in Microsoft? SQL Server? 2000 Analysis Services, solve order and pass order together determine the manner in which a cube is resolved when queries are processed. This topic assumes that you have a basic understanding of cubes, custom members, calculated members, and custom rollups.

>>

|||

Thanks for the help.

Is it possible to do this as a calculated cell within a cube?

Thanks

Garron Mosley

|||

Sure, you could create the same calculated members and cells in a cube, I just used query-scoped calculations as an illustration...

sql

Italic font, text aligned right

Hello
A textbox within a table cell is displayed strangely. The font style is
italics and the text is aligned to the right. Now the text is cut at its
right side by some pixels. So only 2/3 of the last letter is displayed.
Does someone have a solution for this problem?
Ciao,
JensI found out that it works with arial font, but it doesn't work with
Tahoma, though.
Ciao,
Jens
Jens Winter wrote:
> Hello
> A textbox within a table cell is displayed strangely. The font style is
> italics and the text is aligned to the right. Now the text is cut at its
> right side by some pixels. So only 2/3 of the last letter is displayed.
> Does someone have a solution for this problem?
> Ciao,
> Jens|||Try checking the padding parameters. This might cause the text to disappear,
if the text is exactly as long as the text box permits.
Kaisa M. Lindahl Lervik
"Jens Winter" <jwinter@.icubic-consulting.de> wrote in message
news:uUJi8g3YGHA.3516@.TK2MSFTNGP03.phx.gbl...
> Hello
> A textbox within a table cell is displayed strangely. The font style is
> italics and the text is aligned to the right. Now the text is cut at its
> right side by some pixels. So only 2/3 of the last letter is displayed.
> Does someone have a solution for this problem?
> Ciao,
> Jens|||Hello Kaisa,
I tried this too, but it didn't work.
I also tried to append some whitespaces at the end of the text. But they
get trimmed. This is another weirdness.
Anyhow, I assume it is a font issue. We will switch to Arial for the moment.
Ciao,
Jens
Kaisa M. Lindahl Lervik wrote:
> Try checking the padding parameters. This might cause the text to disappear,
> if the text is exactly as long as the text box permits.
> Kaisa M. Lindahl Lervik
> "Jens Winter" <jwinter@.icubic-consulting.de> wrote in message
> news:uUJi8g3YGHA.3516@.TK2MSFTNGP03.phx.gbl...
>> Hello
>> A textbox within a table cell is displayed strangely. The font style is
>> italics and the text is aligned to the right. Now the text is cut at its
>> right side by some pixels. So only 2/3 of the last letter is displayed.
>> Does someone have a solution for this problem?
>> Ciao,
>> Jens
>|||Sounds like maybe the size of your text box needs to be bigger to fit your
text.
"Jens Winter" wrote:
> Hello Kaisa,
> I tried this too, but it didn't work.
> I also tried to append some whitespaces at the end of the text. But they
> get trimmed. This is another weirdness.
> Anyhow, I assume it is a font issue. We will switch to Arial for the moment.
> Ciao,
> Jens
> Kaisa M. Lindahl Lervik wrote:
> > Try checking the padding parameters. This might cause the text to disappear,
> > if the text is exactly as long as the text box permits.
> >
> > Kaisa M. Lindahl Lervik
> > "Jens Winter" <jwinter@.icubic-consulting.de> wrote in message
> > news:uUJi8g3YGHA.3516@.TK2MSFTNGP03.phx.gbl...
> >> Hello
> >>
> >> A textbox within a table cell is displayed strangely. The font style is
> >> italics and the text is aligned to the right. Now the text is cut at its
> >> right side by some pixels. So only 2/3 of the last letter is displayed.
> >>
> >> Does someone have a solution for this problem?
> >>
> >> Ciao,
> >> Jens
> >
> >
>

Issue with inserting chart into table's cell

Hello, look at this one

I've formed table with grouping

+ Group 1 | Sum(field1) | Chart
Elem1 | field1 | "Cannot place a chart at this location in the table"

Why I cannot insert chart at this place?

Thanks

It sounds like you are trying to place the chart into the table detail area. I.e. for every detail data row, you would one chart instance - so every chart instance has just one data point.

Instead, put the chart into the group header or group footer.

-- Robert

|||I get a similar error when trying to paste a table in a detail cell of another table. "Cannot place a Table at this location in the Table" Yet I can insert a new table into that table cell from the toolbox. The table I want to paste is based on a single data point from the parent table. I can recreate it, but it would be a lot easier to paste it from another report.