January 28, 2009 at 11:04 pm
Hi,
I have been trying to create the Calculated Measure (Say [Measure Set 1 ] ) which contains a SET of measure (which includes previous Calculated measures too) by using MEASUREGROUPMEASURES function,
problem is when i hit the query in SSMS which is
(
Select [Measure Set 1 ] on Rows, [Products] on Columns
from [Adventure Works]
)
it give me perfect result but with no Calculated measures (but with no error).
when i try to view the same thing through Excel Pivot, it just displays the name of the measures with no values, also if i include any calculated measure in my new calculation it it gives error in the pivot.
my Scenario is:
i want to create a set of measure depending on different products e.g
measure Set 1 -- measure 1, measure 2, Calulated Measure 1, Calculated Measure 2.... etc depending on the products
measure Set 2 .......................
measure set 3........................
my calculated query goes this way
measure Set 1 ==== ([Product].[ProductName].&Product1, {Measure1,Measure2, CalculatedMeasure1, Calculated Measure 2,.....})
or
measure Set 1 ==== ([Product].[ProductName].&Product1, MEASUREGROUPMEASURES(' Measure Group Name '))
can someone help me in creating the sets of measures along with Calculated measures and using it in the Excel pivot
Regards
Mohammed
February 9, 2009 at 12:10 am
mohd.imtiaz (1/28/2009)
Select [Measure Set 1 ] on Rows, [Products] on Columnsfrom [Adventure Works]
it give me perfect result but with no Calculated measures (but with no error).
Try adding AddCalculatedMembers to your select statement
Select AddCalculatedMembers({[Measure Set 1]}) on Rows, [Products] on Columns
from [Adventure Works]
February 9, 2009 at 10:15 pm
Hi Dirk,
Thanks for your update and suggession.
I tried using the givem function to create a calculated member, but still I am getting no value for the same.
Follwing is my query which I wanted to convert to Calculated member.
SELECT
ADDCALCULATEDMEMBERS({[Measures].[VALUE]})
-
{[Measures].[H1], [Measures].[H2]}
ON COLUMNS,
[Product].[Prod_L03_Business_Unit_PNL_Items]
ON ROWS
FROM [COI Reporting]
Please help me out in writing the same inside calculated member section.
************************
Calculate member Name : [AGR - Display & Distribution]
Expression which I am writing :
(
[Product].[Prod_L03_Business_Unit_PNL_Items].&[Display]
,ADDCALCULATEDMEMBERS({[Measures].[Value]})
)
Regards
Mohammed
February 9, 2009 at 10:51 pm
Just out of interest, if you're using Excel 2007 pivot tables, have you made sure that the option 'Show calculated members from OLAP server' is checked? In case you're not using it, the OLAP PivotTable Extensions (see codeplex for download) can help here by allowing you to default this to true for all pivottables.
HTH,
Steve.
February 9, 2009 at 11:40 pm
Mohammed,
runs these queries on the Adventure Works OLAP database and notice the difference...
with member [measures].[x] as 1
select [measures].members on columns
from Finance;
with member [measures].[x] as 1
select addCalculatedMembers([measures].members) on columns
from Finance;
stevefromOZ (2/9/2009)
[...]have you made sure that the option 'Show calculated members from OLAP server' is checked?
This excel option probably adds addCalculatedMembers to the query just like the queries above.
Dirk
February 10, 2009 at 5:47 am
Yes, i knew about the option, and enabled it.
still m getting the same error.
i just want to create the set of measures (Normal as well as Calculated) and then create another Calculated member with the combination of the Dimesnions and the Set of measures.
e.g. : (Dimension1.&value , Dimesnion2.&Value, )
which is possible in SSMS but when i try to view it in Excel from pivot table, it gives error
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply