Analysis :: Disable Drill Down On Calculate Member
Jun 3, 2015In one of the business requirement, we need to disable a particular drill down on a calculated member, Is it possible?
View 3 Replies
ADVERTISEMENT
Disable Drill Through In Model Designer
Apr 24, 2008Does anyone know how to disable the auto click through functionality in Model Designer?
Does anyone know how to prevent the "drill throught url on the cell value" to export to excel when exporting a Report Builder report with drill through enabled?
Any help on these topics would be greatly appreciated.
Analysis :: SSAS Calculated Member - MDX
Jul 1, 2015I 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
Else ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2).
I came up with
CASE WHEN [Measures].[End LIS] = 0
AND [Dim Date].[FSCL_YM].[Month Nm].currentmember.membervalue = Format(now(), "yyyy")+"]&["+Format(now(), "M")
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
But to no avail.
Analysis :: All Member Not Working In MDX Query
May 28, 2015[DIMCustomerBuyer].[CustId].[CustId] is working.
[DIMCustomerBuyer].[CustId].
All members is not working any hint why this is not working.
Analysis :: MDX - Custom SET Excluding Unknown Member
Nov 21, 2008Uknown Member is set to "Uknown" in the cube . This cannot be changed because it needs to be visible in other applications. However, in my particular MDX I want to create a custom set that excludes this member.
WITHSET setNoUnknowns as EXCLUDE( [Dim].[Hierarchy].CHILDREN, { [Dim].[Hierarchy].UNKNOWNMEMBER } )SELECT{[Measure].[MeasureName]} on COLUMNS,setNoUnknowns on ROWSFROM [Cube]
With the above MDX, I still get a return for member "Unknown". I've confirmed that there isn't an explicit member name of "Unknown".Excluding [Dim].[Hierarchy].[Unknown] does not work either.
Analysis :: Building A Calculated Member Using Except Function
Aug 23, 2015I’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]
[Code] ....
Calculated Member Analysis Services 2005
May 16, 2008
Hi,
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?)
Avg(Descendants([Manager].CurrentMember, [Manager].[Name]), [Measures].[PA Salaire]
But that doesnt work at all i get the following result: " #VALUE! " for every manager
Thx for your help
Francois
Analysis :: To Get Value Of A Member Passed As Parameter In SSAS MDX
May 27, 2015I have a requirement to show the parent value which is passed and also all the leaf member details in MDX. For example in Adventure works, if I pass Bikes as the value in MDX, the results should display Bikes along with All the child member names as a measure value in SSAS MDX. I only have one hierarchy with Product Category-> Subcategory-> Product.
I am trying to use the following MDX but I am getting all the members. I need to get only Bikes in my report.
WITH MEMBER [Prod_Passed] AS SETTOSTR(AXIS(1))
MEMBER [Prod_Child] AS DESCENDANTS ([Dim Product].[Hierarchy].CURRENTMEMBER,,LEAVES).item(0).NAME
MEMBER [Child_Value] AS ([Dim Product].[Hierarchy].CURRENTMEMBER,[Measures].[Sales Amount])
SELECT [Dim Product].[Hierarchy].[Product Category].&[1] * [Dim Product].[Product Key].[Product Key].MEMBERS ON ROWS,
{[Measures].[Sales Amount],[Prod_Passed],[Prod_Child],[Child_Value]} ON COLUMNS FROM
[AdvWorks]
Analysis :: MDX LastPeriods Not Working When Creating New Member?
Jul 20, 2015I have an issue which I cannot seem to resolve, what I need to do is to create a new member in an existing dimension which lets me filter the results of the last two weeks (this should start from last week, so ignoring the current week). I've tried a few approaches which seemed to have worked when browsing via excel, but when I tried using this in PowerView things were not right. I need a graph which shows the day on the Axis and then a single measure, with the filter being set to the last two weeks.
In management studio I got the following to work and show me the week, date and the measure:
select
(
LastPeriods(2,[Date].[Week Calendar].[Year].&[2015].&[30]&[2015].lag(1))
,[Date].[Calendar].[Date].allmembers
) on rows,
[Measures].[On Schedule %] on columns
from [Flight Movement]
where {([Flight Leg].[Direction].&[Arrival]),([Flight Leg].[Direction].&[Departure])}
This filters the results and only shows the relevant weeks and dates. When I try to put the LastPeriods logic into a new member, it returns all the dates in the dimension. This is what I am trying:
with member [Date Calculations].[Date Calculations].[Test]
AS
(LastPeriods(2,[Date].[Week Calendar].[Year].&[2015].&[30]&[2015].lag(1)))
select
(
[Date Calculations].[Date Calculations].[Test],
[Date].[Calendar].[Date].allmembers
) on rows,
[Measures].[On Schedule %] on columns
from [Flight Movement]
where {([Flight Leg].[Direction].&[Arrival]),([Flight Leg].[Direction].&[Departure])}
Analysis :: Hide Member Properties By Default?
Oct 28, 2015Is there any option in SSAS cube to hide the member properties from the user by default in the cube itself. We know we have a option in the excel pivot 'Show properties in tooltips check box where we can hide or display the member properties. But I need an option in the cube to hide the member properties by default and display in the excel depends on demand.
View 2 Replies View RelatedAnalysis :: How To Get Max Level Member Of A Dimension Without Mentioning
May 29, 2015I am Using following MDX Query to get the data for max date in the Dimension. But every time I am passing date key to dimension to get data. How to automatically get the Max available Level member for that dimension.In this case I am passing 20150429 to every line instead i need to get that automatically.
SELECT
(
{
[DimDate].[HierYQMD].[Date Key].&[20150429],
{[DimDate].[HierYQMD].[Date Key].&[20150429].PARENT.PARENT.PARENT.LAG(4):
[DimDate].[HierYQMD].[Date Key].&[20150429].PARENT.PARENT.PARENT},
[code]....
Analysis :: Security Role Default Member
May 12, 2015I am setting up some security requirements - the requirement is that team leaders get to view all teams within their division. What I would like it to do when they log onto their dashboard/ssrs report is that it defaults to the team leaders default team (they can still select from other teams if they require).If I can avoid I don't really want another parameter in the report for this.
In my cube I have an attribute under the people dimension (where teams and division also reside) called Fee_Earner_Effective_Flag and where this is set to Y this is their current team.
Under the Role properties security under the Fee_Earner_Effective_Flag attribute I have set the Default member to
[People Primary FeeEarner].[Fee_Earner_Effective_Flag].[Fee Earner Effective Flag].&[Y]
When I then logon as the user and try to access the cube I get the following
Errors in the metadata manager. The '[People Primary Feeearner].[Fee_Earner_Effective_Flag].[Y]' security default member from the '{' attribute returns a result from a different hierarchy.
I have also tried setting the default member in the cube solution and then processing but then the cube failed.
Analysis :: Role Based Drill Through Action
Jun 3, 2015I have number of users which are under 6 Groups. We have 4 drill through actions. Now I would like to restrict 2 drill through actions for only some groups.
View 2 Replies View RelatedAnalysis :: Format Numbers In Drill-through Action?
Jun 15, 2015I have table with 3 columns: record_id (int), car (varchar(50)), amount (decimal(18,4)).
I created dimension for drillthrough action. Also I set Amount's column FormatString to '##0.00' but when I use Drillthough column Amount values is shown like:
.0000
15.7900
18.4100
Is there any possibility to change that values would be shown as follow:
0,00
15,7900
18,4100
Analysis Services - Calculated Member Ignoring Decimal
Feb 1, 2006Hello,
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])
Calculated Member 3 (Booked Amount / Unit)
Measures.[Booked Amount YTD] / Measures.[Units YTD]
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?
Any help would be appreciated.
Thanks in advance.
Brian
Analysis :: Total Calculation Incorrect When Using IF In Calculated Member
Oct 27, 2015I cannot understand why Total of calculated member is displayed incorrect. How should I change calculated member for it to work correctly?
Calculated Member:
CREATE MEMBER CURRENTCUBE.[Measures].AverageScore
AS IIF([Measures].[Distance]<2001,0,[Measures].[avgscore]/[Measures].[Date Count]),
VISIBLE = 1;
It seems that Total is calculated without checking value in "AverageScore" for that month.
Analysis :: How To Change The Member Names Of Time Dimension
Oct 19, 2015<SQL Server 2008 R2>
I created a Time table using BIS. I found that the default naming of time members is too long and redundant.
For example, the wizard generated "Fiscal Calendar 2015", "Fiscal Quarter 1, 2015", etc. However, shorter expression like "FY2015", "FQ1 2015", etc would be enough for me.
Is it possible to change the default naming rule, or does SSAS works correctly if I update the Time table values using SQL?
Analysis :: Create A Calculated Member For Cumulative Sum Using Fiscal Calendar?
Jun 2, 2015I 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.
Analysis :: SSAS - Display Measure Value Based On Other Attribute Member?
Sep 2, 2015getting Correct Measure based on Member Present in Other Attribute .
I am working on SSAS 2012 and have cube build and ready ..
I have Two Measure in Cube
[MEASURES].[Actual] and [MEASURES].[Target] and I need to create One more Calculate Measure
I have dimension DimProduct
I want to Display [MEASURES].[Actual] if Color "Purple" is present for PRODUCT1 Else Display [MEASURES].[Target].
MDX to create Calculate Measure for this logic?
Analysis :: How To Get Distinct List Of Member Values For Month Numbers From Date Dimension
Sep 10, 2015Have a need to let the user select the year and month to use for filtering results from an MDX query that populates a report.I think all I should need is a list of years and a Month name / number.What would the filter look like if say I had a Date dimension that included the year number in a 4-digit and month in a 2-digit number? In TSQL it would be something like ... where Month = @month AND Year = @year...How do I get a distinct list of member values for the month numbers from the Date dimension?
View 6 Replies View RelatedAnalysis :: Using A Filter In CALCULATE
Sep 8, 2015I'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.
Analysis :: Calculate Max Value Over A Hierarchy?
Jun 19, 2015I have a fact table that captures the captures oldest date between a Task date and todays date per Person (so there is only fact per person) like this - a measure is created from the Task Days
PersonID, Dateduekey, Task Days
130 20130809 679
I have a person dimension which has a hierarchy of Department_Name->Team_Name->Person I have created some MDX that gives the MAX Task Day for the hierarchy but when you Person attribute of the hierarchy the code runs really slow.with set a as nonempty(([v Dim Fee Earners].[People Structure].[Person],[Dim Calendar].[Primary Date].[All]),[Measures].[Days KPI]) member measures.a as max(existing(a),[Measures].[Days KPI])
How can I get this running for more efficiently when [v Dim Fee Earners].[People Structure].[Person] is selected? To be fair the true measure [Days KPI] is already calculated at person level so if there is way to use the [Days KPI] when [v Dim Fee Earners].[People Structure].[Person] is selected that would be good
Calculate Average Analysis Servives
Aug 6, 2004Hi this might be pretty simple to you guys out there but i am having issues doing it.
Please help!!!!!!!!!!
Calculate Average of a measure called DIST irrespective of any dimension the page field.
Ie. to say it should calculate the average for any doension i bring on the rwo field.
Looking forward to your help.
you can mail me at hem_k01@yahoo.com
Analysis :: MDX To Calculate Maximum Gap In Sales For A Product?
Sep 30, 2015I have a fact table which lists the last 12 month historical sales of a product,country,month combination so the table structure is like:
<month, productID, countryID, (month-3 sales), (month-4 sales).........,(month-14 sales)>
I need to write an MDX query which will return a flag that indicates if a product has a gap of more than 2 consecutive months in terms of positive sales. So I calculate if there is a gap of 3 months. If yes, then return 1 else null. The calculation below is scoped at product level.
IIF(([month-3 sales]<=0 AND [month-4 sales]<=0 AND [month-5 sales]<=0)
OR ([month-4 sales]<=0 AND [month-5 sales]<=0 AND [month-6 sales]<=0)
OR ([month-5 sales]<=0 AND [month-6 sales]<=0 AND [month-7 sales]<=0)
...............
OR ([month-12 sales]<=0 AND [month-13 sales]<=0 AND [month-14 sales]<=0)),1,NULL)
Unfortunately I cannot do this calculation at relational level as a product may have a gap of more than 2 months at country level but may not have this at a higher level in geography.
optimizing this as the performance is bad and other calculated measures are dependent on this. Based on this calculation, I can write a scope which sums up all products so that I get the count of products with a gap of more than 2 months in sales.
I have also tried CASE statement and NESTED IIFs but the performance is worse than the above.
Analysis :: Tabular DAX / Calculate SUM Against Unrelated Table?
Nov 4, 2015I tried using the page below but wasn't able to quite properly adapt it to my scenario
[URL]
I have two UNRELATED (can't be related for reasons outside the scope of this, other relationships already exist) tables.
Each table does, however, contain a WorkerID:
WorkerTimesheets:
WorkerID, HoursLogged, Date
3,10,2015-05-05
3,6,2015-05-10
4,8,2015-05-01
Then the unrelated table "ConstructionSites"
SiteID, SiteManagerID, SiteOpen,SiteClosed
A5, 3,2015-01-01,BLANK
What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.
I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.
Power Pivot :: How To Calculate Discount In Analysis
Jun 16, 2015I have a problem with calculating discount in PowerPivot analysis. I have
dCalendar , dDiscount, fSalesBook .
dDiscount columns:
DiscKey, StartDate, EndDate, Product, DiscountName, DiscountPercent
fSalebook columns:
OrderDate, Product, Qty, Sales, Discount
dCalendar columns:
Date, Holiday, MonthNo, MonthName, QuarterNo, QrtName, Year.
The problem (at least I feel) is I defined some dates in the Startdate and Enddate for a particular year. But these discounts should apply for all of the years in my fSalesBook, not just for that year in the dDiscount table.
So in essence, when you calculate discount for a product in fSalesBook you should consider only the month and day of the transaction, if the sale is in between the discount dates.
I have the following relationships.
Table [Column] ------->Table [Column]
fSalesBook[OrderDate] -----> dCalendar[Date]
I am not sure how to relate the dDiscount table with the dCalendar table and not sure even there is a need to relate them to calculate Row level discount in fSalesBook. You can change the relations/model if there is need be.
PS: One more clause, Sometimes I offer discount only for a particular product in that Discount season, that's why there is a column for Product in the dDiscount table.
What should be my DAX formula to calculate Row level discount in fSalesBook?
Here is the link for the sample workbook.
[URL]
Analysis :: Calculate Percentage Difference Of Two Values From Selected Years
Aug 31, 2015Developing a measure which displays the difference of two values from the selected years.
An example : Show the difference of the sales amount from 2013 and 2015.
Since i am not really into mdx or calculated members.
Analysis :: How To Calculate Full Year Using Built In Time Intelligence
May 13, 2015I am struggling to calculate Full year in my SSAS Cube. Meaning, regardless of what fiscal year hierarchy level I am in; i need a measure aggregating from 01/01/year of current member to 12/31/year of current member.
I want to replicate it using the Year To Date below:
FY-FQ-FM is the fiscal year quarter hieararchy
I am using for built in time intelligence.
Create Member
CurrentCube.[DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date]
As "NA";
/*Year to Date*/
(
[DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date],
[Code] ....
Analysis :: Measures Showing No Rows In SSAS Browser After Cube Processed Ok And Calculate Command Is There
Jun 10, 2015I've created a cube and it processed fine. The calculate command is there. The measure that I'm attempting to run in the SSAS/Visual Studios browser is simply a count rows measure. When I drag the measure to the window, it says no rows available. If I click on the filter that allows nulls, the only change it makes is that it goes from no rows available to "NULL".
View 4 Replies View RelatedAnalysis :: Calculated Member From Aggregate And Non-aggregate Factors
Sep 22, 2015I 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] .....
A New Member Could Not Be Added To A Local Group Because The Member Has The Wrong Account Type
Mar 23, 2007Hi all,
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 tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
A New Member Could Not Be Added To A Local Group Because The Member Has The Wrong Account Type
Mar 23, 2007Hi all,
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 tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
Master Data Services :: How To Add A Reference To Another Entity Member When Creating A Member
Oct 23, 2014I need to create a member that one of its Attributes (maybe my term is wrong) is a reference to another entity member named group
the code
createRequest.Members.MemberType = MemberType.Leaf;
createRequest.Members.Members = new System.Collections.ObjectModel.Collection<Member> { };
Member aNewMember = new Member();
aNewMember.MemberId = new MemberIdentifier() { Name = uag.groupName, MemberType = MemberType.Leaf };
[code]....
"The attribute data type is not valid".Is it wrong to add the reference as attribute? How can I embed the reference in the new member?