Analysis :: IIF Does Not Aggregate AND Filter In SCOPE Does Not Work
Oct 22, 2015
We have two measures
[Measures].[NSPO]
is in FACT1 Â Measure Group
[Measures].[NetSignedPremium] is in FACT2 Â Measure Group
The requirement is Measure.[NetPremium] should be the greater of
[Measures].[NSPO] OR [Measures].[NetSignedPremium].
Â
All three measures are created in the cube and this logic is to be applied in the Calculations section of the cube.
Using an IIF statement like one mentioned below does the job, but data does not aggregate correctly for the
Measure.[NetPremium]
Measure.[NetPremium] = IIF ([Measures].[NetSignedPremium] > [Measures].[NSPO], [Measures].[NetSignedPremium] , [Measures].[NSPO])
To ensure that the columns are aggregated I have to use a SCOPE statement at the leaf level
[01 UWR Reference].[UWR Reference].Members Â
Â
SCOPE(
    FILTER( NONEMPTY( [01 UWR Reference].[UWR Reference].Members  ) ,
         ( ( ([Measures].[Net Signed Premium Rcvd])
         - ( [Measures].[NSPOTest] ) )
             < 0 ) )) ;
        This =  [Measures].[Net Signed Premium Rcvd] ;      Â
ENDÂ SCOPE; Â
My problem is the FILTER within the SCOPE statement does not work. How to modify the filter in SCOPE? OR How to make IIF Aggregate correctly?
View 4 Replies
ADVERTISEMENT
Sep 22, 2015
I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named
This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.
The first factor, Â BillableLastYear is a number, aggregated (sum) from child units.
The second factor, Â BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.
How can I calculate This_Year_Billable_Objective_Count for each unit?
\ able 1
SELECT [UnitKey]
   ,[UnitID]
   ,[UnitName]
   ,[Unit2Name]
   ,[Unit3Name]
   ,[Unit4Name]
[Code] .....
View 6 Replies
View Related
Apr 22, 2015
I'm trying to sum a column in a report. in Most columns I can just wrap the row level expression with "Sum()" and it works. However, I have run into a few that give the following error. The Value expression for the text box ‘Textbox241’ specifies a scope that is not valid for a nested aggregate.  The scope must be the same name of the scope specified by the outer aggregate or the name of a group or data region that is contained in the scope specified by the outer aggregate.Here is my row level expression that works.
=Code.Divide(sum(Fields!WeeklyUnits.Value),sum(Fields!EstUnits.Value))
*
(Code.Divide(sum(Fields!EstHours.Value),sum(Fields!EstHours.Value,"Job")))
Here is my attempt to Sum the row level for the footer area.
=Sum(Code.Divide(sum(Fields!WeeklyUnits.Value),sum(Fields!EstUnits.Value))
*
(Code.Divide(sum(Fields!EstHours.Value),sum(Fields!EstHours.Value,"Job"))))
View 9 Replies
View Related
May 6, 2015
I am trying to create a column chart that calculates the percentage of computers in our IT environment that are Actively communicating to our SCCM Server.
I have two datasets:
1. Total_Count_Of_AD_PC DataSet.
2. PC_With_Active_SCCM_Clients dataset.
Basically i wan to calculate the percentage for each Region (i.e. AP for Asia Pacific, EMEA, Americas).
Below is the Total_Count_Of_AD_PC  Dataset screenshot.
Below is the PC_With_Active_SCCM_Clients dataset.
Below is the expression that i used that is causing the error.
=CountDistinct(IIf(Fields!Region.Value="AP", "PC_With_Active_SCCM_Clients"),(Fields!Name.Value, "PC_With_Active_SCCM_Clients"),Nothing)/CountDistinct(IIf(Fields!Region.Value="AP", "Total_Count_Of_AD_PC"),(Fields!name.Value,
"Total_Count_Of_AD_PC"),Nothing)
Below is the error message....
The Y expression for the chart ‘Chart2’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
View 6 Replies
View Related
May 14, 2007
Hi
I have a old report that was pointing to a different database, and i changed the connection string for that report and changed the field names (As per my new stored Procedure). In some places I keep getting this run time error, i have tried looking for all the hidden references to this field and i cannot find any parameter that the old database had. The error messages that i get are as follows:
[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_Admin_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_FA_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Hidden expression for the table €˜tblWithdrawals€™ refers to the field €˜PW_Comment€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Can Someone please help me out as to how should i get rid of these error messages, they are kinda driving me nuts.
Regards,
Karen
View 3 Replies
View Related
Nov 2, 2015
Just wanted to know is there any way to write scope statements only for a specific role in the cube.
View 3 Replies
View Related
Sep 27, 2013
I'm trying to do a currency conversion with an MDX statement in my Cube SSAS 2012.
Here is my script :
      SCOPE( LEAVES([Entity]) );
SCOPE( LEAVES([Time]) );
SCOPE( LEAVES([Currency]));
SCOPE( [Account].[Account].[Total ACCOUNT].members)
THIS = ([Measures].[Value],[Currency].[Currency].[Local])*([Measures].[Value],[Account].[Account].[Fx Rate]);
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
The problem is I want to exclude frome the scope Currency my local Currency in order to make the conversion only if a currency (€,$, £) is selected. I tried the following syntax but it always return a "MDX script is not valid" :
SCOPE( LEAVES([Currency], Except (LEAVES([Currency],[Currency].[Currency].[Local]);SCOPE( [Measures].[Value] , Except ([Currency].[Currency].members,[Currency].[Currency].[Local]);SCOPE( [Measures].[Value] , Except (LEAVES([Currency],[Currency].[Currency].[Local]);SCOPE( LEAVES([Currency] - [Currency].[Currency].[Local]);
View 5 Replies
View Related
Jul 2, 2015
IÂ am stuck in a situation where I want to use YTD for three different calendars of our company and don't want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure
If I create one YTD and try to use in context of three calendars in SCOPE statements then it does not give my right results. Following is my syntax but It does not work.
SCOPE([Billing Date].[SalesCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )Â Â Â
         = Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );
[Code] ....
However if I comment on one of SCOPE statement , other one works but both don't work simultaneously in context of different calendars.
View 2 Replies
View Related
Jul 19, 2015
In my fact table (month granularity), I've got a calculation defined:Â
Margin%=DIVIDE(SUM(Margin);SUM(SalesAmount))
How should I define a calculation based on "Margin%", so that if I browse the Pivot Table by months it would always show the year level result instead? My date dimension is also with Month granularity and it has a Year column in it.Â
View 2 Replies
View Related
Sep 8, 2015
I'm having what i think it's a strange error in CALCULATE function:
Calculation:=CALCULATE( DISTINCTCOUNT([SK_CUSTOMER]); 'fact x'[_sales net]<>0)
where _sales net is a calculated column, sum of 3 existing columns lets call columnA , columnB, columnC
This calculation doesn't work well, the results seem to be like as if _sales net was only equal to columnA.
Naturally I've already done build deploy and full process.
View 2 Replies
View Related
Aug 4, 2015
I need to conditionally include a filter in the below DAX query such a way that if an Account_ID is passed (as client side parameter) the filter should be included in the query otherwise if it is zero, the query should return results across all account. For example, if total purchase is queried for Account_ID=2 the query should be like this:
evaluate
calculatetable (
addcolumns (
summarize (
'Event'
[code]...
but if the Account_ID is passed as zero, condition 'Event'[Account_ID] = 2 should be removed.Is this possible to implement in DAX?
View 9 Replies
View Related
Sep 9, 2015
I am trying to get a list of years from a Date dimension that includes a limited number of values. Â I want the current year and the previous 5 years. Â The year number in the cube is actually a char(4) value so I will have to cast that.
Also I want to alias the field, since Im using this list to use as a parameter in an SSRS report.
The MDX I have at the moment is this:<br/>
WITH
MEMBER [SALESYEAR] AS
CInt([DimCalendar].[Year].CURRENTMEMBER)
SELECTÂ
NULL ON COLUMNS,FILTER(
[DimCalendar].[Year].MEMBERS , [SALESYEAR] > 2012) ON ROWS
FROM [MySalescube]
The above returns empty, but I also need to define an expression that is the current year - 5 years
View 4 Replies
View Related
Mar 26, 2008
Some tables i have in a report. A couple of them work, but 2 others dont. I would do it in the query, but then i wouldnt be able to group them in a list.
So my question is, how come i can filter out my BillcustomerCode, but when i want to filter out my subbrand_id (which is an int) or subbran_ Descrition(which is a varchar). how would i filter out these types in the table filter.
Also, how would i do an "Or" instead of an "and" in my filter too.
I have one table where i need to have 3 things not in the table:
Bill_Cus_code !=RNPROF
Bill_Cus_code !=RNPROC
Sub_Brand_Id ! =65 (or Sub_Brand_Discription != "Pro Club")
how do i do this in the expression. It seems to take out "RNPROF and RNPROC" but not the third one. Why is this?
View 1 Replies
View Related
Mar 29, 2007
There's a change in SP2 that I'm very happy about, but it took me by surprise since I haven't seen it documented or discussed:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/1/Default.aspx
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.
View 9 Replies
View Related
May 21, 2015
I have a parent child dimension. a time dimension i have year.
Assume,
ParentID ChildID sales Year
171 171 10 2014
171 172 200 2014
171 173 300 2014
171 172 44 2015
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.                                                                        Â
View 2 Replies
View Related
Aug 13, 2015
For example, I have a Date dimension with attributes like Current Day and Current Month. If I run the following, I get exactly what I expect: a list of the days in the current month.
select
NULL on 0,
[Case - Date - PSPT Entry].[Year - Quarter - Month].[Date] on 1
from [Customer Support]
where
[Case - Date - PSPT Entry].[Current Month].&[True]
When I run the following, I'm getting a list of the days in the current month *plus the first couple days of the next month*. with
set [Days of Interest]
as
filter([Case - Date - PSPT Entry].[Year - Quarter - Month].[Date], [Case - Date - PSPT Entry].[Current Month].&[True])
select
NULL on 0,
[Days of Interest] on 1
from [Customer Support]
View 2 Replies
View Related
Jun 10, 2013
I'm trying to create a percentile rank function based on the standard WIKI version:
I've seen Brian Knight's article here, but that only deals with percentile.
Where I'm struggling is getting the count of members in a set using a measure, in the current context on the same hierarchy, as the filter expression. I'm using the comparative set as in belonging to the same geographical location, and therefore associating by another attribute.
So, cl as below:
MEMBER [Measures].[RegionPercentileCount] AS
Count(
Filter(
NonEmpty(
descendants(Ancestor(
[Supplier].[NameMap].CurrentMember,
[Supplier].[NameMap].[Region]),
[Supplier].[NameMap].[Supplier Id]),
[Measures].[ActiveMeasure])
, [Measures].[ActiveMeasure] <
([Supplier].[NameMap].CurrentMember,
[Measures].[ActiveMeasure])))
Using the same measure and context hierarchy is always going to be equal, and therefore the count is always zero. Its almost as if I need a nested context for the FILTER which allows me to use enumerate the set on the same hierarchy whilst maintaining the external reference.Â
I'm thinking that perhaps I'm going to have to create another hierarchy and use that as the filter set and reference through StrToMember or similar.
View 3 Replies
View Related
Sep 23, 2015
I have an existing MDX query returning the correct resultset. However, I want to add a filter so that for a combination of value in Hierarchy1 and Hierarchy2, the data is filtered out.
For example I have data like
H1Â Â Â H2Â Â Amount
1Â Â Â Â Â 1Â Â Â Â Â 100
2Â Â Â Â Â 1Â Â Â Â Â 50
3Â Â Â Â Â 1Â Â Â Â 45
I am getting a value of 100+50+45=195.
I want to filter the data for the combination H1=3 and H2=1. Expected result would be 100...
H1Â Â Â H2Â Â Amount
1Â Â Â Â Â 1Â Â Â Â Â 100
2Â Â Â Â Â 1Â Â Â Â Â 50
View 2 Replies
View Related
Oct 5, 2015
I have been living in the T-SQL world for years, and am working on my first Tabular model.
I have a fact table that I would like to use for many reports. My idea is that I would use Perspectives in the tabular model to pull in the columns that I need for each purpose. In some cases, I also need to filter out some rows. Is there a way to filter perspectives? The only solution I am coming up with is to create views in SQL Server and import the fact table multiple times for each purpose. That doesn't seem very efficient.
View 2 Replies
View Related
Oct 6, 2004
I have a big table with several types of transsactions: PO (Puchase Orders, SO,( Sales orders), INV (Invoices) ....
I want to create cubes with only one type of transactions (1 cube for PO,...)
Where and how can I filter the rows I want to use in my cube ?
Thanks
View 2 Replies
View Related
May 27, 2015
My requirement is to count the customer order number for premium order type orders which has some order quantity.I am using below MDX
count
(Â
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[P]),[Measures].[Ordered, pcs]>0 ) ,
)
The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
count
(Â
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[C]),[Measures].[Shipped, pcs]>0 ) ,
EXCLUDEEMPTY)
View 3 Replies
View Related
Jul 24, 2015
SSAS 2008 R2
Is it possible to filter out a measure only at the intersection of Two dimension members? I have a date dimension, Â a Hospital dimension and a wait time measure.
For Example, is it possible to filter out Wait time for Bayside Hospital for the Month of June 2015?
I want Wait time to continue to be displayed for all other months and roll up into the totals without the filtered value.
View 4 Replies
View Related
Jun 4, 2015
I am unable to find solution for the problem while writing a Named Set in my cube.
I have a calculated measures which gives me difference in Sales in PERCENTAGE (%).
When I try to filter out those product codes which went a less than 5 %, I get no records.
I have also tried to filter direct values lets say - Products with sales > 100000 which is working fine.
Following is sample of my Named Set
FILTER([X].[Products Code].members, [Measures].[Diff in Sales]<5)
I believe as the values are in percentage, I am facing this issue.
View 21 Replies
View Related
Oct 26, 2015
I am pretty new to MDX and am having trouble getting what I need out of this MDX query. Some business rules:
Gross Amount applies to all clients, whether Type A or Type B. Â I always want to return Gross Amount.Some clients are Type A, some are Type B, some are both, and some are neither.There are Type A Net Amount and Type B Net Amount values for all clients, but I only want to display the Type A Net value if the client is a Type A client, only Type B if the client is a Type B, or both for both, and neither for neither. Â I would like to return blank/null, not $0.00, for those values that should not be displayed.
Here's the basic query. Â
SELECT { [Measures].[Gross Amount],
[Measures].[Type A Net Amount],
[Measures].[Type B Net Amount]
} ON COLUMNS,
NON EMPTY {[Dim Client].[Parent Client Code].[Parent Client Code] *
[Dim Client].[Child Client Code].[Child Client Code] *
[Dim Client].[Is Type A].CHILDREN *
[Dim Client].[Is Type B].CHILDREN
} ON ROWS
FROM ClientInfo
Here's the DESIRED output........
View 3 Replies
View Related
Sep 14, 2015
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?
View 4 Replies
View Related
Jun 3, 2015
I have SSAS cube with Fact that include values in kg (e.g. 25.3, 32.5, 18,3...).What kind of attribute or other solution should I create If I want to filter those kg's in browser with integer values e.g.:weight between 10 and 25
View 3 Replies
View Related
Nov 30, 2015
I have developed a cube in my work place for analyzing current year sales with previous year sales in Time Hierarchy (Year- Quarter- Month) using Parallel period. If we want to see data for particular Quarters i.e. Q1 and Q2 then total at the year level should also get change. Currently if we only choose 2 quarters in the filter then current year data gets change, however data using parallel period is not getting change accordingly and its shows Total of full year.
View 4 Replies
View Related
Nov 12, 2015
how can I have a default filters in my Excel PowerPivot Table, without the user need to select them in Excel creating with DAX Language.
View 8 Replies
View Related
May 14, 2015
One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.Â
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically:
1. Why it can't really filter on a measure?
2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
View 3 Replies
View Related
May 8, 2015
The user wants to be able, using excel, to apply a filter to all measures in every measure group. I though that I can create a dimension with a single level with two members, let´s say "on" and "off" and depending on the selected member and using an IIF statement decide which formula applies to the calculated measures.
I have serious doubts about the performance and for this technique because I am thinking as a .Net developer and not as a cube developer. Maybe it is better to resolve it scoping the measures but I cannot figure it out.
View 3 Replies
View Related
Sep 9, 2015
I have been following the tutorial/blog post HERE to create an annualization (or "run rate") of my Gross Amount measure.  What I want to do now is exclude any period that is not "complete". Â
For example, if today is 9/9 then Q3 is not complete - only Q1 and Q2 are complete. Â And if I'm looking at it monthly then January through August are complete, but September is not.
Cells B5 and D5 look exactly as I expect and want them to be. On row 6 below under each "Gross Annualized" value I have basically just put the formula for what it's actually doing.
What I would like to see in the blue cell is $67,211,697,268 - essentially the most recent annualization for a completed period. The annualization for Q3 is misleading because the quarter has not yet completed. Â There's $16b in Q1 and $17b in Q2, so the measly $78m in Q3 (yellow cell) is dragging the annualization down significantly. Even worse, the Gross Amount for Q4 is being treated as a $0.00, which is further dragging down the "2015 Gross Annualized" amount in blue. In a T-SQL average calculation, for example, I think the green cell would be treated as NULL rather than $0. Â That's kind of the behavior I want.
So I would like to do two things:
Create a calculation (probably just a 0 or 1 flag) that indicates whether the current period is complete or not. Â Again, using 9/9/2015 as an example, Q2 would be complete but Q3 would not be. And August would be complete, but September would not. Make the blue cell show $67,211,697,268 - an annualization based on completed quarters only.
For whatever it's worth here is the current calculation for Gross Annualized.
CREATE MEMBER CURRENTCUBE.[Measures].[Gross YTD]
AS AGGREGATE(
YTD([Pay Date].[Calendar].CurrentMember)
,[Measures].[Gross Amount]),
FORMAT_STRING = "Currency",
VISIBLE = 0;
[Code] .....
View 2 Replies
View Related
Nov 12, 2007
Good morning all,
I have searched everywhere for this and I can't find any information on it. When I use the Query Builder, in the top pane, the Filter pane, there are five columnsimension, Hierarchy, Operator, Filter Expession and Parameters.
In the dropdown for Operator, one of the choices is MDX. I cannot find any documentation on how to use this operator. I want to limit my filter to SELF_AND_AFTER, and I am hoping that this is possible using this operator. I know how to hand code it, but I have a ton of other tweaks to make to the query, so I want to use the Query Designer as much as I can.
Does anyone have any links to documentation on how to use this operator?
Thanks,
Kathryn
View 2 Replies
View Related
Mar 13, 2008
I have taken three dtsx files and re written them into one each in its own container. I use the XML Task task alot which the File connection is set by a variable and the variable value is evaluated by expression (the expression makes up the path/filename from other variable values). All the variables that make up the connection are at the container scope. The package will not run now because it is saying that the source (created by variables) for the file connection do not exist.
It seems the answer is that file connections exist at the package level therefore the variable has to be at the package level. This seems to be alot of variables i now have to move to package level to generate the XML source connection. Which in essence makes it confusing as to which variables operate in which container.
My question is can we easily move variable scope (Not ideal as we have alot of variables at package level) Or Can we do the same for connection managers as we do for variables and have them only used in a scope? (this will be ideal as some connections only need to be at a container scope)
View 1 Replies
View Related
