I need help figuring out what setting I need to tweak to get the correct calculations for the default aggregate attributes for the related entities of the one I am drilling into. Right now it is calculating the total across all for every row and not slicing by sub-customer.
Example:
I have a customer with a one-to-many relationship to incidents. Both have a count aggregate that is part of the default aggregates for the entity. There are 58 rows in my table. If I run a report with CustomerName and #Incidents, I correctly get different sub totals for each customer, totalling to 58 for the grand total. However, if I run a summary report on customers and drill into the customers using click through, the #Incidents is displayed but it is 58 for all customers - every row.
If I go into the defaultDetailAttributes of the Customer and add the #Incidents to it and run the previous test, then the correct number of incidents are shown for the customer, then the incorrect number of incidents follow (from getting the aggregates from the children).
The query generated is huge and I am sure it has something to do with my OptionalMany relationships between the tables, but I can't understand why...
I have a Calculated Member in SSAS that I need to adjust based what the current member is.
The code is below
CASE WHEN [Measures].[End LIS] = 0 AND "HELP" THEN CASE WHEN [Measures].[Beginning LIS] = 0 OR [Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS] = 0 THEN NULL ELSE ROUND([Measures].[Disconnects]/(([Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS])/2) * 100 ,2) END ELSE ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2) END
In English - i need this to translate to - of End LIS is 0 "AND the current member is the current month and current year" THEN carry on
I’m trying to build a calculated member (see below script) using “Except” function but I get an error result:
#Error The function expects a string or numeric expression for the argument. A tuple set expression was used.
My idea is to take a measure and exclude 2 members from the dimension.
I tried using “Aggregate” but got the error:
#Error Query (3, 1) Aggregate functions cannot be used on calculated members in the Measures dimension.
Please note that my measure is ACD that is already calculated average in olap and I can’t use AVG function instead of Aggregate. What can I do?
With
member [Measures].[AppOrig All Roaming] as ( Except( [Source IP Location].[Country Name].[All].children,{ [Source IP Location].[Country Name].&[Colombia]
I need to calculate the average of an existing measure of my cube. The measure is PA_Salaire from a column of my fact table. I would like to calculate an average i could use with all the dimensions of my cube but i think i can only create one linked to a single dimension. Is that true ?
I dont really understand the way i should write my request. Here is what i did: (Manager is one of my dimensions, it has a parent child relation... is that a problem? does it change the way i should do?)
I have a calculated member that composed of dividing two other calculated members. The calculation is incorrect and is ignoring the decimal places in both of the source measures of the equation.
Example:
Calculated Member 1 = 300.01 Calculated Member 2 = 10.25 Calculated Member 3 = (CM1 / CM2 ) = 30 INCORRECT!!! Answer should be = 29.269
The MDX that I am using is as follows:
Calculated Member 1 (Booked Amount YTD): Sum(PeriodsToDate([Fiscal].[Date].[Fiscal Year]),[OH Booked Amount])
Calculated Member 2 (Units YTD) Sum(PeriodsToDate([Fiscal].[Date].[Fiscal Year]),[OH Units])
This seems like a fairly common and straight forward type of calculation, taking the YTD amount / YTD units to come up with YTD amount per unit. When I create a calculated member with the hardcoded values of 30.01 / 10.25 I get the correct answer of 29.269. Am I missing something with formatting? Is this a bug in Analysis Services?
I am trying to create a calculated member for Cumulative sum using Fiscal Calendar using the below code:
WITH MEMBER [Measures].[No of Accounts Cumulative] AS 'Sum(PeriodsToDate([DimFinancialDate].[FiscalMonth].[(all)],[DimFinancialDate].[FiscalMonth].CURRENTMEMBER), [Measures].[No of Accounts])' SELECT {[Measures].[No of Accounts],[Measures].[No of Accounts Cumulative]} ON COLUMNS, [DimFinancialDate].[FiscalMonth].Members ON ROWS FROM [Acquisition] where [DimFinancialDate].[Year - Quarter - Month - Date].[FiscalYear].&[2014-04-01T00:00:00];
Getting below OUTPUT:
No of Accounts No of Accounts Cumulative All 1763 1763 Apr 14 116 353 May 14 30 383 Jun 14 284 667 Jul 14 112 779 Aug 14 38 817
[code]....
Basically, It is taking all the members of the Fiscal date dimension to compute the output, But what i am expecting is to compute only for Fiscal calendar that is from April 2014 - March 2015.
Can somebody on the SSRS product team double check me that I've written that up right? That seems like a pretty important change and I can't see it mentioned in BOL anywhere.
if I pass 2014 and 2015 in sub select 171 data is not coming in result. i i pass only 2014 in sub select i get value of only 2014. if I pass 2015 in sub select i didn't get any value.
At the 2nd level, the calculated measure will only be correct if it is Averaged. And at the 1st level, the calculated measure will only be correct to take these Average value from 2nd level and Sum then up together.
Level 1 Level 2 A X1 X2 ================= (Avg of X1 + X2) B Y1 Y2 Y3 ================= (Avg of Y1 + Y2 + Y3) =================================================== (Sum of (Avg of X1 + X2) + (Avg of Y1 + Y2 + Y3) )
Currently, Instead of summing all the 3 averaged value, it is averaging against all the items like,
(Avg of X1 + X2 + Y1 + Y2 + Y3)
My MDX currently looks something like that
[Measure].[Value] / [Dimension].[Attribute.count] -> so i can get the avarage at the 2nd level but it doesn't require on the 1st level but retaining this value.
How can i do an average on the leaf level and using these average values to sum at parent level?
At the following MDX code , I want to get the aggregate of measure only for members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.
WITH MEMBER A AS (b)+(C) MEMBER [Measures].[Aggregate] AS Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00], ([Measures].[D])
I have a column that has an expression with a runningvalue in it, a "Carrying Cost" for each month. I need to create another column that aggregates the monthly Cost. I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.
A measure in a cube need to be divided by another measure as follows:
MEASURE1MEASURE2
Measure 1 (SUM of Money spent by each person) Measure 2 (Amount of Money available for each country per person). Example: USA: 155 Germany:134 France:143)
Measure1 is a SUM and works fine, but Measure2 should only be a distinct value for each country. So if person comes from Germany, then the SUM of Spent Money should be divided by 134.
I'm trying to calculate the prior year sales count in a calculated member with the following expression. The cube processes without errors but, the result in PriorYearCount column when i browse the cube is null value for all rows.
-- Prior Yr Sales Count Calculated member
(
[Measures].[W SALES F Count],
ParallelPeriod([Date].[Fiscal Date Hierarchy].[Year]
, 1
, [Date].[Fiscal Date Hierarchy].CurrentMember)
)'
But the same expression works fine when I used it to write query on the cube to get previous year sales count. Following is the query for that. Coundnt understand why the same WITH MEMBER expression used in Calculated member is not working.
With Member [Prior Yr Sales count] As
'(
[Measures].[W SALES F Count]
, ParallelPeriod([Date].[Fiscal Date Hierarchy].[Year]
, 1
, [Date].[Fiscal Date Hierarchy].CurrentMember)
)'
Select
{[Date].[Fiscal Date Hierarchy].[Year].[2004]
, [Date].[Fiscal Date Hierarchy].[Year].[2004].Children} on columns
, {[Measures].[W SALES F Count], [Measures].[Prior Yr Sales count]} on rows
I have a calculated member that calculatest the prior year sales count and the expression looks like this:
--[Prior Yr Sales Count] ([Measures].[W SALES F Count], ParallelPeriod([Date].[Fiscal Date Hierarchy].[Year] , 1 , [Date].[Fiscal Date Hierarchy].CurrentMember))
I can see the values for [Prior Yr Sales Count] in the cube if i filter the cube on any year from Date hierarchy.
But, when i gave
[Measures].[Prior Yr Obl Count]
as KPI Value expression, I dont see any value for the KPI even after filtering on Date hierarchy. The calculated members should act just as any other Measures, so why am I not able to see the prior year sales count for the KPI value? Correct me if I'm not interpreting the usage of calculated members in KPI value expressions.
I need to calculate an average over a dimesion other than time and "average of children" won't do that for me. I'm aware I can create a calculated member where I divide a sum by a count but the problem I have is I need use this calculation in a performance point scorecard and I want to drill down on the measure and you can't do this on calculated members.
Is there any other way of obtaining an this type of average?
The scenario is that I have a dimension called Product... each product is given points and this is defined in the underlying product table.
The table on which my fact is based has a field called QtySold (which is the qty sold for the product). WhatI want is to somehow use Calculated Member (if that is the right solution) so that for any selected product, the user get's to see a calculated field called, say' Points earned, which will be QtySold multiplied by the points for that product.
Hoping that my question is clear, can someone please help me find a solution.
The problem is [Claim Count]. I want the claim count on each row to be the highest claim count from selected rows and placed on each row. The count is way under-reported. However, If I use a simple count statement or distinct count then only the last 2 rows have the count value I am looking for. It doens't seem to be affected by the where clause or the aggregated sets so it is over-reporting on the claim count. Anyone have any thoughts on how to solve this problem?
Basically, I'm cross joining law firms with tasks that show expenses and fees submitted by law firms for billing. I need to calulate averages by dividing the dollar amounts by a claim count. The twist is that the claim count I am looking for only occurs on the indemnity and prior legal fees rows because I know that every claim/case has an indemnity and prior-legal fee transaction in the fact table. The others types of transactions don't. So I have to assume the claim count is max claim count calculated on one of the rows and that number needs to be applied to the rest. Also, the count has to be adjusted by the slicers in the where clause (when I used a simple count statement it wasn't adjusting as I changed the values in the where clause) The slicers in the where clause change the date, or the jurisdiction as well as other dimensions. Hope this information has helped. Any suggestions would be greatly appreciated.
The scenario is that I have a dimension called Product... each product is given points and this is defined in the underlying product table.
The table on which my fact is based has a field called QtySold (which is the qty sold for the product). WhatI want is to somehow use Calculated Member (if that is the right solution) so that for any selected product, the user get's to see a calculated field called, say' Points earned, which will be QtySold multiplied by the points for that product.
Hoping that my question is clear, can someone please help me find a solution.
I have created a virtual cube in SQL2000. Then, I have generated aCalculated Member. When I did rollup into a higher level, SQL2000 didre-calculate the member. However, I want to summerize the Calculatedmember. The Solve Order could not be worked in this situation since ameasure, which is used to generate the Calculate Member, will beavailable only in the lowerest level of the dimension. Please help me!
I think my problem is that the dimension "Manager.Name" has a parent child relation. And the result i have is strange. I have the Pa_Salaire for the employee when its the last child but the total of the PA_Salaire of a manager (not one of the last child) doesnt give the average of his child but the average of all the employee of the society... Im not sure it's clear so Here is an example:
AVG_PA_Salaire Mr David (the boss) Mr David 10 000 Mr Smith (a manager) Mr Smith 5000 Ms Kalvin 2000 Mr Ollbek 3000 Total 5571.4 instead of 3333.33 Ms Richo (an other manager) Ms Richo 8000 Mr theck 5000 Ms Irita 6000 Total 5571.4 instead of 6333.33 Total 5571.4 the good total of all employee
Can anyone help me plz, i really need to do that...
I defined several calculated measures in my cube. In Report Designer, i want to filter data depending a calculated measure.
Some caluclated measures are missing properties like value or ismissing. Anybody a hint, why these measures dont provide the properties, as for that filtering or hiding is not possible.
In AS every property is the sam for all calcs and the leaking calcs dont have difficult expressions (IIF(measure<0,measure/measure,0.0)
The following query gives me counts by Year, Quarter and Month
SELECT NON EMPTY { [Measures].[MS093 A KEY Distinct Count] } ON COLUMNS, NON EMPTY { ([A MILESTONES KEY - MS093 A 1].[Year Name].[Year Name].ALLMEMBERS * [A MILESTONES KEY - MS093 A 1].[Quarter Name].[Quarter Name].ALLMEMBERS * [A MILESTONES KEY - MS093 A 1].[Month Name].[Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [DW Lite De- Normal]
The result looks like this
I would like to have the cumulative count say [MS093 A KEY Cumulative] as a separate calculated member / measure. I tried various MDX exps but none seem to work. Can someone please help me out here.
I have added a calculated member in my Analysis Service Cube, but this member does not show up in Report Builder. It is in my cube. I have tried to rebuild the model and to generate a new model. I can create a report from Visual Studio 2005 with the calculated member. Does not Report Builder support calculated members?
I woudl like to know how to improve performances of query. I have a cube, quite small with 8 dimensions and 2 measures. I also have 3 calculated members because i need avg aggregates on my measures. So 2 of my calculated measures are the simple measures but with avg aggregate. the third measure is the increase in percent between the 2 other measures.
I do use and browse my cube in Excel 2007. And the execution time of query is too long. I used the partition to design aggregates and optimize performances but it's still very low.
I saw that "scope" could be more efficient than calculated members but i dont know how it works. Could someone tell me that?
As the users drill down one of the dimensions, I want to show the breakup of each sub member in terms of %age e.g.
Suppose total Sales are 300 and breakup is as follows: Category A = $ 120 Category B = $ 65 Category C = $ 115
I want that when the user drills down to the 2nd level in the Products dimension (let's say this Level is called Category) then I want the %age of Sales for each category - all adding up to 100
So this is what I should get (for the desired % breakup)
Category A = 40 Category B = 22 Category C = 38
I'll appreciate your help in making such a Calculated field.
hi all, I was wondering if it is possible in SSAS 2005 that a calculated member is based off of an (integer) dimension attribute and another (integer) measure (let's say a multiplication operation) ?
If there a trick on doing so? other than stuffing the (integer) dimension attribute back in the fact table, as an measure?
I want an MDX calculated dimension member, (with no measure specified in expression, so that any related measure can be used in a query/browse), and that accomplishes either one of the following:
Ideal: To calculate Median values on every level of a user hierarchy
If "Ideal" is not possible, then "Acceptable" is: To calculates Median values on one or more levels OTHER than the highest level.
I have a table that contains a column for a calcuated member (x) of type decimal number. When I tried to display the total of this calculated member in the table footer (=sum(x)), I am getting "#Error" instead of the sum of all displayed calculated values.
Column X
--------------
0
0.67
0.10
0.23
#Error (footer cell, expression -> =Sum(x))
=First(x), =Last(x) and =Max(x) worked fine, not sure why Sum failed. Please help...
