October 13, 2008 at 8:03 am
Hi All,
I am working on Reporting Services(Analysis services) in Sql server 2005 , i am facing problem to implement bussiness in Calculated member field where i have one measure data as Revenue and one dimension as sector division like energy, services, utility.
i want to implement logic to find %revenue for all the sector seperatly by using function. i am using this query but return me NULL value :
(IIF([Dimension Sector].[Dimension Sector] <> 0,
[Measures].[Total Revenue]/[Dimension Sector].[Dimension Sector],0),
"##.00;(##.00);0.00;#.00")
since dimension sector having no data may be that's why i am getting NULL . Can any guide me to Implement this.
Ragards,
Alok
October 13, 2008 at 8:53 am
You might use isnull(value, 0). It will put 0 if value is null. You can put 1 or anything so that it will not be null any time. obviously u should have zero in denominator. Your final (##) is confusing. Can you clarify?
October 13, 2008 at 9:10 am
Hi,
Thanks to reply , actually i am using this function :
(IIF([Dimension Sector].[Dimension Sector] <> 0,
[Measures].[Total Revenue]/[Dimension Sector].[Dimension Sector],0),
"##.00;(##.00);0.00;#.00")
in IIF function if [Dimension Sector].[Dimension Sector] in equal to 0 then it should return 0 otherwise calculated value of [Measures].[Total Revenue]/[Dimension Sector].[Dimension Sector] this expression but problem here is that [Measures].[Total Revenue] is having numeric value and [Dimension Sector].[Dimension Sector] is like different sector as energy , utilities , services So i want to calculate seperate revenue for these sectors. These are "##.00;(##.00);0.00;#.00" only format to show output in % form .
here i am getting [Measures].[Total Revenue] value from measure and [Dimension Sector].[Dimension Sector] from dimension table in analysis services.
regards,
Alok
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply