Tricky Question About Using Calculated Member In AS
Nov 18, 2004
Hi,
I am really stuck here and need some help.
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 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.
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 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.
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)
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
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.
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?)
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 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 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...
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.
A very basic MDX question. Using the following from AdventureWorks as an example:
Code Snippet WITH MEMBER [Measures].[Big Cities] AS ( [Geography].[City].&[New York]&[NY] , [Measures].[Internet Order Count] ) SELECT [Measures].[Big Cities] ON COLUMNS , [Customer].[Total Children].Children ON ROWS FROM [Adventure Works]
How would I re-write the [Measures].[Big Cities] calculated member so it included both [Geography].[City].&[New York]&[NY] and [Geography].[City].&[Los Angeles]&[CA]? Assuming there is no logical connection between these members, and that they are non-contiguous. Also, in my real example, I cannot move [Measures].[Internet Order Count] to the WHERE clause, it must remain within the calculated member.
I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.
At the moment, I'm doing this:
Code Snippet
create member [MIA DW].[DATE TIME].[Date].[Yesterday] as [DATE TIME].[Date].&[2007-01-01T00:00:00]
select [Measures].members on 0, non empty [DATE TIME].[Date].members on 1 from [MIA DW] But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?
So I guess there's two questions:
1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)
2)How come the Yesterday member is not returned by the .members function?
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
