Calculations on members and aggregation

I'm designing a Profit And Loss report dimension that has the following attributes: Report Line, Cost Center and Account. Attribute relationships are defined between the attributes so that Report Line->Cost Center->Account.

A Report line is either A) A combination ofcost centers and accounts like:

Report Line A

- Cost Center 1

--Account 3000

--Account 3001

-Cost Center B

.. And so on

Or B) A calculation

I'm trying to handle calculations through mdx scripts by overwriting the value for those report lines that are calculations like:
scope (Report Line B);

this = Report Line A - Report Line C;

end scope

The problem is that the calculation report lines mess up the aggregation of the cube. I guess what I am really asking is wether its possible to turn off the members that are calculations in the aggregation of the cube. I realize i could use calculated measures for this but this doesnt fit my need for two reasons: Ease of use of the dimension and inability to drill down in reporting services on drillable members when calculated members are included.

Anyone have any ideas on this?

You can use the Freeze MDX statement to prevent changes to "Report Line B" from effecting the totals. To do this add simply apply Freeze to the all member of the hierarchy that contains Report Line B prior to updating Report Line B.

|||

Thanks!

Works like a charm, and as a bonus it helped me understand freeze

Calculation Error

I am designing a report and am getting an error when I run this calculation
in the row. Here is my expression;
"=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
Fields!SUM.Value/Fields!Hours.Value,0)"
Basically, I am doing a divide calculation of two columns, but sometimes
there is a zero in the columns which causes an error. In my expression
above, I am saying if column A and column B are >0, then divide, if not, then
0.
Any ideas on what I am doing wrong? I have about 100 reports that I am
cranking out, so learning a lot.
Thanks in advance,
RyanOn Oct 19, 10:11 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I am designing a report and am getting an error when I run this calculation
> in the row. Here is my expression;
> "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> Fields!SUM.Value/Fields!Hours.Value,0)"
> Basically, I am doing a divide calculation of two columns, but sometimes
> there is a zero in the columns which causes an error. In my expression
> above, I am saying if column A and column B are >0, then divide, if not, then
> 0.
> Any ideas on what I am doing wrong? I have about 100 reports that I am
> cranking out, so learning a lot.
> Thanks in advance,
> Ryan
If I remember correctly, > 0 does not exclude null; so, you might want
to change your expression to something like:
=iif(Fields!Hours.Value > 0 and Fields!Hours.Value <> Nothing and
Fields!SUM.Value > 0 and Fields!SUM.Value <> Nothing , Fields!
SUM.Value/Fields!Hours.Value, 0)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Check the article here for expression examples using the IIF statement:
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
All of the examples I've seen have involved using one expression in each
IIF so if you are doing Fields!Hours.Value > 0 and Fields!SUM.Value > 0
then you would need two nested IIFs. Also, Enrique is correct. You should
check for NULL before doing any comparisions to make sure you're getting
valid data.
Here is an example expression you can try:
=iif(Fields!Hours.Value > 0, IIF(Fields!SUM.Value > 0,
Fields!SUM.Value/Fields!Hours.Value,0),0)
If you do think you'll have NULLs in your data use:
=iif(IsNothing(Fields!Hours.Value), 0,
iif(IsNothing(Fields!SUM.Value),0,iif(Fields!Hours.Value > 0,
IIF(Fields!SUM.Value > 0, Fields!SUM.Value/Fields!Hours.Value,0),0))
Didn't test the above so don't hammer me for any mistakes I may have put in
there :)
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Calculation Error
> thread-index: AcgSYkRUoT7qoNO7S9WCFbXNnevQBA==> X-WBNR-Posting-Host: 207.46.19.168
> From: =?Utf-8?B?UnlhbiBNY2JlZQ==?= <RyanMcbee@.discussions.microsoft.com>
> Subject: Calculation Error
> Date: Fri, 19 Oct 2007 08:11:04 -0700
> Lines: 15
> Message-ID: <562C6023-A07E-4FA8-83B4-2B486EDDE038@.microsoft.com>
> MIME-Version: 1.0
> I am designing a report and am getting an error when I run this
calculation
> in the row. Here is my expression;
> "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> Fields!SUM.Value/Fields!Hours.Value,0)"
> Basically, I am doing a divide calculation of two columns, but sometimes
> there is a zero in the columns which causes an error. In my expression
> above, I am saying if column A and column B are >0, then divide, if not,
then
> 0.
> Any ideas on what I am doing wrong? I have about 100 reports that I am
> cranking out, so learning a lot.
> Thanks in advance,
> Ryan
>|||On Oct 22, 2:08 pm, cal...@.online.microsoft.com (Chris Alton [MSFT])
wrote:
> Check the article here for expression examples using the IIF statement:http://msdn2.microsoft.com/en-us/library/ms157328.aspx
> All of the examples I've seen have involved using one expression in each
> IIF so if you are doing Fields!Hours.Value > 0 and Fields!SUM.Value > 0
> then you would need two nested IIFs. Also, Enrique is correct. You should
> check for NULL before doing any comparisions to make sure you're getting
> valid data.
> Here is an example expression you can try:
> =iif(Fields!Hours.Value > 0, IIF(Fields!SUM.Value > 0,
> Fields!SUM.Value/Fields!Hours.Value,0),0)
> If you do think you'll have NULLs in your data use:
> =iif(IsNothing(Fields!Hours.Value), 0,
> iif(IsNothing(Fields!SUM.Value),0,iif(Fields!Hours.Value > 0,
> IIF(Fields!SUM.Value > 0, Fields!SUM.Value/Fields!Hours.Value,0),0))
> Didn't test the above so don't hammer me for any mistakes I may have put in
> there :)
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
>
> > Thread-Topic: Calculation Error
> > thread-index: AcgSYkRUoT7qoNO7S9WCFbXNnevQBA==> > X-WBNR-Posting-Host: 207.46.19.168
> > From: =?Utf-8?B?UnlhbiBNY2JlZQ==?= <RyanMc...@.discussions.microsoft.com>
> > Subject: Calculation Error
> > Date: Fri, 19 Oct 2007 08:11:04 -0700
> > Lines: 15
> > Message-ID: <562C6023-A07E-4FA8-83B4-2B486EDDE...@.microsoft.com>
> > MIME-Version: 1.0
> > I am designing a report and am getting an error when I run this
> calculation
> > in the row. Here is my expression;
> > "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> > Fields!SUM.Value/Fields!Hours.Value,0)"
> > Basically, I am doing a divide calculation of two columns, but sometimes
> > there is a zero in the columns which causes an error. In my expression
> > above, I am saying if column A and column B are >0, then divide, if not,
> then
> > 0.
> > Any ideas on what I am doing wrong? I have about 100 reports that I am
> > cranking out, so learning a lot.
> > Thanks in advance,
> > Ryan- Hide quoted text -
> - Show quoted text -
Another way to work around this is with a custom code function.
In Layout mode, click Report > Report Properties > Code to open the
Custom Code editor.
In the editor, type:
Public Function DivideBy(exp1, exp2)
If exp2 = 0 Then
DivideBy = Nothing
Else DivideBy = exp1/exp2
End If
End Function
Now whereever you need to divide (with the potential for divide by
zero errors) insert this expression:
=code.divideby(exp1,exp2)
For you it would be:
"=code.divideby(Fields!SUM.Value,Fields!Hours.Value)"
If your shop has SQL 2005 enterprise edition, you can create a custom
assembly out of the function.|||On Oct 23, 11:55 am, toolman <t...@.infocision.com> wrote:
> On Oct 22, 2:08 pm, cal...@.online.microsoft.com (Chris Alton [MSFT])
> wrote:
>
>
> > Check the article here for expression examples using the IIF statement:http://msdn2.microsoft.com/en-us/library/ms157328.aspx
> > All of the examples I've seen have involved using one expression in each
> > IIF so if you are doing Fields!Hours.Value > 0 and Fields!SUM.Value > 0
> > then you would need two nested IIFs. Also, Enrique is correct. You should
> > check for NULL before doing any comparisions to make sure you're getting
> > valid data.
> > Here is an example expression you can try:
> > =iif(Fields!Hours.Value > 0, IIF(Fields!SUM.Value > 0,
> > Fields!SUM.Value/Fields!Hours.Value,0),0)
> > If you do think you'll have NULLs in your data use:
> > =iif(IsNothing(Fields!Hours.Value), 0,
> > iif(IsNothing(Fields!SUM.Value),0,iif(Fields!Hours.Value > 0,
> > IIF(Fields!SUM.Value > 0, Fields!SUM.Value/Fields!Hours.Value,0),0))
> > Didn't test the above so don't hammer me for any mistakes I may have put in
> > there :)
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > --
> > > Thread-Topic: Calculation Error
> > > thread-index: AcgSYkRUoT7qoNO7S9WCFbXNnevQBA==> > > X-WBNR-Posting-Host: 207.46.19.168
> > > From: =?Utf-8?B?UnlhbiBNY2JlZQ==?= <RyanMc...@.discussions.microsoft.com>
> > > Subject: Calculation Error
> > > Date: Fri, 19 Oct 2007 08:11:04 -0700
> > > Lines: 15
> > > Message-ID: <562C6023-A07E-4FA8-83B4-2B486EDDE...@.microsoft.com>
> > > MIME-Version: 1.0
> > > I am designing a report and am getting an error when I run this
> > calculation
> > > in the row. Here is my expression;
> > > "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> > > Fields!SUM.Value/Fields!Hours.Value,0)"
> > > Basically, I am doing a divide calculation of two columns, but sometimes
> > > there is a zero in the columns which causes an error. In my expression
> > > above, I am saying if column A and column B are >0, then divide, if not,
> > then
> > > 0.
> > > Any ideas on what I am doing wrong? I have about 100 reports that I am
> > > cranking out, so learning a lot.
> > > Thanks in advance,
> > > Ryan- Hide quoted text -
> > - Show quoted text -
> Another way to work around this is with a custom code function.
> In Layout mode, click Report > Report Properties > Code to open the
> Custom Code editor.
> In the editor, type:
> Public Function DivideBy(exp1, exp2)
> If exp2 = 0 Then
> DivideBy = Nothing
> Else DivideBy = exp1/exp2
> End If
> End Function
> Now whereever you need to divide (with the potential for divide by
> zero errors) insert this expression:
> =code.divideby(exp1,exp2)
> For you it would be:
> "=code.divideby(Fields!SUM.Value,Fields!Hours.Value)"
> If your shop has SQL 2005 enterprise edition, you can create a custom
> assembly out of the function.- Hide quoted text -
> - Show quoted text -
Actually,
Public Function DivideBy(exp1, exp2)
If exp2 = 0 Then
DivideBy = 0
Else DivideBy = exp1/exp2
End If
End Function
is better so you get the zero as output.