Posted By:Posted Date:October 13, 2010Points:0Category :Sql Server

I'm using a Cube to display the % of Total Head Count measure fo [Measure].[Head_Count] with help of cubegrid. I show the record level by level each level form a MDX query and send to the ssas.

I'm using the calculated measure with axis(0,0) formula, when click the second level i get the wrong data. what is the problem ??

I created a calculated measure called ‘Selected Hierarchy’ as follow which returns either ‘Admit’, ‘Received’ or ‘Discharge’ as the hierarchy is selected.

Hi
I've come across an example where my calculated member will yield wrong results. I've been trying in the last few days to find a solution for this, but so far no luck.
I have two measures:
[Measures].[Marketing Spending (Actual)]: Aggregation: Sum
[Measures].[New Subscriptions (Actual)]: Aggregation: Sum
Then I created following calculated member in the schema:
[Measures].[CPA (Actual)]=[Measures].[Marketing Spending (Actual)]/[Measures].[New Subscriptions (Actual)]
If I run a query like this I get the correct results:
SELECT
[Measures].[CPA (Actual)] ON 0
from [Global B2C Weekly KPI Cube]
WHERE
[Date.Weekly Calendar].[2010].[1]:[Date.Weekly Calendar].[2010].[32]
I can even add some other dimensions and all looks fine.
But, when I create a calculated Member in the MDX query with PeriodsToDate and Aggregate, the results looks like the sum of the CPAs by week.
WITH MEMBER [Measures].[x] AS
Aggregate(PeriodsToDate([Date.Weekly Calendar].[2010],[Date.Weekly Calendar].[2010].[32]),[Measures].[CPA (Actual)])
SELECT
[Measures].[x] ON 0
from [Global B2C Weekly KPI Cube]
So my take is that as PeriodsToDate gives back all the members, the calculated measure is calculated for each member and then summed up.
In this case the measures should be summed first for the time period and then the calculation should be performed. I don't want to create extra memb

Hi
Was looking to create a calculated Measure Which needs to be dependent on another column of the FACT table.
I need to have the Average of the particular Measure based on the fact that this particular 'set' has a common Related dimension's
The example would give a better picture.
I have a set of dimensions like
STUDENT, TEACHER, COURSE etc as my dimensions and the
grades, class standing based on marks for the subject, percentage of marks scored, Percentile, Marks Scored,StudentID and the related PK's etc are the measures on my fact table
Here I want the Avg of the marks scored by the Students based on the grade say is A or B or C
I was looking to create a calculated measure by using MDX
which was a follows
(NOT THE CORRECT SYNTAX JUST THE IDEA)
SUM (MARKS SCORED)/COUNT(DISTINCT StudentID)
WHERE GRADE = A
As this would be measured against a particular subject or a teacher or a Course.
I was hoping if I could get help in putting this in a proper SYNATAX
Thanks in advance.

Hi,
I have to dimensions: [Currency] with members CAD and USD and [Convet to] with members None, CAD, USD. The measures involved in calculation are [Amount Billed] and [Amount Received]
The problem calculation is defined pretty simple:
MEMBER CURRENTCUBE.[Measures].[AR] AS (abs([Measures].[Amount Billed] - [Measures].[Amount Received])>0.1,[Measures].[Amount Billed] - [Measures].[Amount Received] ,null);
and worked perfect without currency conversion .
I've read some info about currency conversion and designed this calculation to convert:
scope (leaves([Time 2]));
scope([Convert To].[Convert To].&[USD],[Currency].[Currency].&[CAD]);
[Measures].[Amount Received] = ([Measures].[Amount Received],[Convert To].[Convert To].&[None])/validmeasure([Measures].[Cdrate]);
[Measures].[Amount Billed] = ([Measures].[Amount Billed],[Convert To].[Convert To].&[None])/validmeasure([Measures].[Cdrate]);
End Scope;
scope([Convert To].[Convert To].&[CAD],[Currency].[Currency].&[USD]);
[Measures].[Amount Received] = ([Measures].[Amount Received],[Convert To].[Convert To].&[None])*validmeasure([Measures].[Cdrate]);
[Measures].[Amount Billed] = ([Measures].[Amount Billed],[Convert To].[Convert To].&[None])*validmeasure([Measures].[Cdrate]);
End Scope;
End Scope;
That calculation provides correct results for both [Amount Billed] and [Amount

Hi,
My goal is to change the text color of all cells that contain aggregated values.
Currently I achieve it like this:
I COUNT the members of all attributes of all dimensions.
To be multi-select-safe I am using the EXISTING keyword.
If the members count of at least one dimension is not 1 than the background color of the cell is changed.
CREATE
MEMBER
CURRENTCUBE .[Measures].[SingleCellSelected]
AS iif ((COUNT (Existing ([Dim1].[Attr1].[ Attr1].MEMBERS ))=1)
AND (COUNT (Existing ([Dim1].[Attr2].[Attr2].MEMBERS ))=1)
AND (COUNT (Existing ([Dim2].[Attr3].[Attr3].MEMBERS ))=1)
AND (COUNT (Existing ([Dim2].[Attr4].[Attr4].MEMBERS ))=1)
AND (COUNT (Existing ([Dim3].[Attr5].[Attr5].MEMBERS ))=1),1,0),
VISIBLE
= 0
;
SCOPE
([Measures].AllMembers );
FORE_COLOR
(this ) =
iif ([Measures].[SingleCellSelected]=1,0,16744448);
END
SCOPE ;
This approach works but it performs badly with attributes with many members.
Do you have any idea how to optimize this?
Thank you!

Hi,
ÃÂ
I have a list withÃÂ a calculated column named 'FullName', the formula is: "FirstNameÃÂ & " "ÃÂ &ÃÂ ÃÂ LastName". I created a view group by 'FullName' column. The follow error appeared on the column:
ÃÂ
<!-- #RENDER FAILED -->
ÃÂ
Please help

Following fact table
- Amount
- StartDate
- EndDate
- SomeOtherDate
If the user selects a date in the time dimension I want to build the sum of Amount where the selected date is between StartDate and EndDate.
Like this
SELECT SUM(Amount) WHERE <date> between StartDate and EndDate
Is it possible to create a calculated measure like this.
Thanks.

I have a 64bit server running SSAS 2010 and I keep getting an ExcelMDX error when processing my cube. We do use an Excel function called FINV() in a calculated measure. I installed Excel 2007 on the SSAS 2010 server but this did not help
(I also tried Excel 2010). I can start Excel on the server so I know it is installed. Does it matter whether I install Excel 32bit or 64bit on the server? How do I verify that Excel is installed correctly for SSAS 2010?
Errors and Warnings from Response
MdxScript(Sales and Returns) (94, 8) The '[Excel].[FINV]' function does not exist.Kerry

Hello:
I have a Measure Group that has two Date dimensions, a Reporting Date and a Fee Paid Date. The Reporting Date and the Fee date is based on the same Dimension, which has a standard YQMD hierarchy. It's very typical.
The calculation is called "Fees Paid In Same Period", and it would display the Fees paid if the Reporting Month equals the Fees Paid Month.
How do i do this?
Thanks,

I have a cube with a Measure (A) and a dimension (C). I've created a Calculated Measure (B) whose tuple is Measure A with the all member of Dimension C. The Calculated Measure looks like this:
([Measures].[A], [C].[ALL])
Scenario 1
When I place the dimension on a pivot table, the Calculated Measure B reflects the correct value, Measure A by all members of dimension C.
Scenario 2
When I place the dimension in the filter area and filter by one or more members, the Calculated Measure B reflects the correct value, Measure A by all members of dimension C. The same answer as Scenario 1.
Scenario 3
When I place the dimension in the pivot table AND filter by one or more members, I am getting a different answer than I want. It is filtering first by the members selected and then reflecting the Calculated Measure as the All value of ONLY the selected
filter members. I want the calculated Measure to reflect Measure A by all members of dimension C. (The same number as in the first two scenarios)
Is there any way to create the Calculated Measure differently so that all three scenarios give me the same answer?

The issue looks pretty simple yet I got stuck. I want to define the measure [NR Var] as [NR]-[F NR] for years/months/dates before 2009 and [NR]-[FI NR] for 2009 and on. I am using [Year]-[Month]-[Date] hierarchies in the cube. I have defined scope:

but it does not work for some reason. If I get rid of DESCENDANTS function, it works but applies the scope only to the YEAR level. Another problem with using SCOPE is that it affects [NR Var] only yet I have other calcs derivative of [NR Var] which I want
the scope to affect as well.

So I ideally I would like to have something like:

CREATE MEMBER CURRENTCUBE.[Measures].[NR Var] as

case when {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_befor

Can I create a calculated member that will filter out based on a certain dimension? eg, I have a customer dimension and a sales cube. The sales cube has the sales count measure. Can I have a calculated measure that will give me the sales count for all customers that are flagged as 'New'?ÃÂ That way, I have a total count and a New customer count.

For col4 i need to calculate the difference b/w (the value in current row in col3 i.e. 250) - (the value in the previous row in col 3 i.e.100). i need to do that for all rows of col4. I need help with the expression to do this in SSRS.

I have designed a Report with a Graph of the Type Simple Scatter. I have set
the Average value for x and the y axis Coordinate is duration in integer. For the y-Axis it
works, but the Average value on the x axis is ignored. I want to consider the value max 500
for the x-axis’s, but it could not display the x-axis value when I put the series value as Events

And If I am considering the Category field as Year, Quarter, Month or week but it not considered.

I confused about this problem. Please tell me This problem is occur from my side or from SSRS.

I have a cube with measure groep sickness, and dimensions date and employee.

In the sickness fact table i have employeeID, sick ID, sick start date, sick end date. The employeeID is linked to employee dimension, and both the sick start date and end date are linked to the date dimension.

Now i want to create a calculated measure, which calculates the sum of the duration of all sick cases that ended in period t.

So that I later can create a report with on one axis the period (months) and on the other axes the sum of the duration of all sick cases ended in that month.

I already have a measure which counts sick duration. But how can I add the condition that it only takes the sick cases ended in a period?

## Problem with a calculated measure that works across different time hierarchies.

I was inspired to create a calculated measure which works with all available time hierarchies after reading this original post.

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/69708c41-08e6-4085-ba41-8b5297d20c57

I created a calculated measure called ‘Selected Hierarchy’ as follow which returns either ‘Admit’, ‘Received’ or ‘Discharge’ as the hierarchy is selected.

CASE

when NOT IsError(Extract ( Axis (0), [Admit Date].[Date Hierarchy]).Count ) then 'Admit'

## PeriodsToDate problem with calculated member

## Measure Dependent Calculated Measure

## Please help with converting calculated measure.

## Optimize Calculated Measure containing COUNT EXISTING

## Problem when using calculated column as column group in a view

## Calculated measure between two dates

## Which version of Excel to install for a calculated measure?

## Calculated Measure Help - Comparing Months from two Date Dimensions

## Calculated Measure scenario with a filtered dimensional attribute

## cummulative calculated measure

HI Guys

i have time Dimemsion and hiriarchy as Year-month-week-day

i have a account Dimension

Actual as measure

i want to create a Calculated measure for Balance Account which is an member in account dimension

i want to create a calculated Cumulative measure for balance sheet account like the below

which should give me sum of cumulative of actual amounts for Required period

Time FY2010-Period1 FY2010-Period2 FY2010-Period3

Balance Sheet $2000 2100 4100

the above will calculate the Balance of previous month + current month so on

i hope i am making sense

Kind Regards

bhas

## Please Help with Defining Calculated Measure based on Dimension Members

Hello,

The issue looks pretty simple yet I got stuck. I want to define the measure [NR Var] as [NR]-[F NR] for years/months/dates before 2009 and [NR]-[FI NR] for 2009 and on. I am using [Year]-[Month]-[Date] hierarchies in the cube. I have defined scope:

Scope ([Measures].[NR Var], {

descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),

descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_before_after),

descendants([Date].[Year - Month - Date].[Year].&[2006], 2, self_before_after),

descendants([Date].[Year - Month - Date].[Year].&[2007], 2, self_before_after),

descendants([Date].[Year - Month - Date].[Year].&[2008], 2, self_before_after)});

this=[NR]-[F NR];

End Scope;

but it does not work for some reason. If I get rid of DESCENDANTS function, it works but applies the scope only to the YEAR level. Another problem with using SCOPE is that it affects [NR Var] only yet I have other calcs derivative of [NR Var] which I want the scope to affect as well.

So I ideally I would like to have something like:

CREATE MEMBER CURRENTCUBE.[Measures].[NR Var] as

case when {

descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),

descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_befor

## Calculated measure that works across multiple hierarchies

I need to create a calculated measure for YTD that dynamically calculates based on the date hierarchy selected by the user (SSRS & Excel).

On the date dimension, I have 2 hierarchies "Year-Month-Day" and "Year-Week-Day", that both share the year and day attributes.

What's the best way to go about this?

Many thanks,

Matt

## Creating a calculated measure that filters

Hi,

Can I create a calculated member that will filter out based on a certain dimension? eg, I have a customer dimension and a sales cube. The sales cube has the sales count measure. Can I have a calculated measure that will give me the sales count for all customers that are flagged as 'New'?ÃÂ That way, I have a total count and a New customer count.

Thanks,

Brian

## Problem with calculated field in SSRS

For col4 i need to calculate the difference b/w (the value in current row in col3 i.e. 250) - (the value in the previous row in col 3 i.e.100). i need to do that for all rows of col4. I need help with the expression to do this in SSRS.

## Scatter Chart Problem With X-Axis

Hello,

I have designed a Report with a Graph of the Type Simple Scatter. I have set

the Average value for x and the y axis Coordinate is duration in integer. For the y-Axis it

works, but the Average value on the x axis is ignored. I want to consider the value max 500

for the x-axis’s, but it could not display the x-axis value when I put the series value as Events

And If I am considering the Category field as Year, Quarter, Month or week but it not considered.

I confused about this problem. Please tell me This problem is occur from my side or from SSRS.

I have find some forums as :

## Scatter-Chart Bug ?!?!

## Scatter Chart Issue

http://www.thedailyreviewer.com/dbsoftware/view/scatter-chart---point-color-based-on-data-value-107504920 <

## mdx calculated measure sum of sick duration for all sick cases ended in period T

Hello,

I'm using SQL server 2008 standard edition.

I have a cube with measure groep sickness, and dimensions date and employee.

In the sickness fact table i have employeeID, sick ID, sick start date, sick end date. The employeeID is linked to employee dimension, and both the sick start date and end date are linked to the date dimension.

Now i want to create a calculated measure, which calculates the sum of the duration of all sick cases that ended in period t.

So that I later can create a report with on one axis the period (months) and on the other axes the sum of the duration of all sick cases ended in that month.

I already have a measure which counts sick duration. But how can I add the condition that it only takes the sick cases ended in a period?

Regards, Hennie