Analysis :: Swap Measures According To Dimension Selection
Oct 9, 2015
I've 2 measures that I would like to swap according to which member in a dimension are selected by the user:Ex.
[Measure].[A]
[Measure].[B]
Dimension called TEMP with 2 members (HOT and COLD).So if the user choose HOT it should show [Measure].[A] and if the user choose COLD it should show [Measure].[B]If the dimension is not included in the analysis by the user or if no dimension attribute are choosed then it should default to [Measure].[A].How to do that as Calculated Script in the SSAS cube ?
View 3 Replies
ADVERTISEMENT
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
Oct 26, 2015
When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.
View 4 Replies
View Related
Jan 23, 2007
hello at every body.. well i have a problem. i want to create a report which will present two measures vued with the same dimsneion but with different values.for example i want to present sales bu country and i want to present at the report the sales for the US and the sales for UK at the same time
indicator1=([Measures].[sales],[country].[UK]),
indicator2=([Measures].[sales],[country].[US]).
Further more i want to create two parameters which take the same values in order to change the values of indicators..
i have creted the datasets for the parameters and the parameters but i don't know how to code the main data set in order to change the values of indicator 1 with the values of parameter 1 and the values of indicator 2 with the values of parameter 2
thank u for your responce
View 1 Replies
View Related
Feb 20, 2008
Folks,
I have an issue in producing output for the following query in MDX Query builder avaialable in BIDS. the error message I get is:
The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)
Code Snippet
WITH MEMBER [Measures].[Annualized Vs Target] AS
'[Measures].[Revenue]-[Measures].[Target Revenue]'
SELECT
NON EMPTY {
([Measures].[Balance],[Year].[Year - Year].&[2006]) ,
([Measures].[Balance], [Year].[Year - Year].&[2007]),
([Measures].[Revenue], [Year].[Year - Year].&[2006]),
([Measures].[Target Revenue], [Year].[Year - Year].&[2007]),
([Measures].[Revenue], [Year].[Year - Year].&[2007]),
([Measures].[Annualized Vs Target], [Year].[Year - Year].&[2007])
}
ON COLUMNS ,
NON EMPTY { ([Client - Coverage Region].[Region Name].[Region Name].ALLMEMBERS *
[Client].[Client Name].[Client Name].ALLMEMBERS)} ON ROWS
FROM [MIS POC FINAL]
Do Anybody has a solution for this? I require the output to be in the following format :
Region_Name(RegDim) Client_Name(ClientDim) Balance_2006 Balance_2007 Revenue_2007 TargetRevenue_2007
USA A 20000 30000000 40000000 5000000000
B 60000 70000000 80000000 9000000000
Note: We can execute the above query in SSMS-MDX Query window without any issues.
Many Thanks in Advance for any assistance.
Subhash Subramanyam, Suraj Magdum
View 6 Replies
View Related
May 19, 2008
Hi!
Need some help building a query that does the following :
I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)
In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]
Both Time Dimensions are Year -> Month -> Day and have the same Members.
I have every option available, using calculated Members and/or Measures to do this.
The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.
Appreciate all help!
Regards,
Stian Bakke
View 3 Replies
View Related
Aug 13, 2004
I have a problem where I have 3 three measures in a virtual cube:
"Actual", "Budget" and "Full Year Budget".
The dimensions I have are:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates
- Budget Name
Both "Actual" & "Budget" measures need to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates (exclusive to "Actual", "Budget")
Thus have put these in one cube
AND "Full Year Budget" needs to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Budget Name (exclusive to "Full Year Budget")
THUS have put this as one cube
I then created a virtual cube, with the 2 cubes thinking that the dimensions I created in the original cubes would only filter the measures of the original cube measures in the virtual cube. ...BUT all dimension filters in the virtual cube filter all measures in the virtual cube, irrespective of which dimensions were created with the original cubes.
please help!
View 1 Replies
View Related
Oct 1, 2015
I have a cumulative measure in Tabular, and when I filter by a date in SQL it brings back all rows up to that date. However, when I drill down on any of the numbers, it only brings back rows related to the filtered date, and not the cumulative rows. Is there any way round this at all? Major obstacle for me at the moment...
View 5 Replies
View Related
May 4, 2015
Actually I want to do distinct sum on a measure group, please find the below table as sample
XL Measure group
LK OK Amount
1 10 100
1 11 100
3 30 250
3 31 250
3 32 250
For the above measure group two dimensions have relationships, One is L dimension which is having relationship with XL on LK and One is O dimension which is having relationship with XL on OK. If I drag L dimension attributes it should show results as below
LK LName Amount
1 A 100
3 C 250
But above results are coming as below
LK LName Amount
1 A 200
3 C 750
If I drag O dimension attributes along with L dimension, it should show results as below.
LK LName OK OKName Amount
1 A 10 XYZ 100
1 A 11 UVW 100
3 C 30 PQR 250
3 C 31 KLM 250
3 C 32 TUV 250
I used formula Measures.Amount/Measures.Count, this formula is not showing correct results when I don't drag any dimensions, it is showing results for All member as 425, but it should show as 350.
So I made a same change ([L].[LK].Currentmember, Measures.Amount)/([L].[LK].Currentmember,Measures.Count), this worked fine but performance is very low and so stopped working on this.
Atlast I did the measure group like this
LK OK LAmount OAmount
1 10 100 100
1 11 0 100
3 30 300 300
3 31 0 300
I want to show Measures.LAmount when only L dimension is querying and want to show OAmount when both L dimension and O dimension are querying. Is this possible ?
View 3 Replies
View Related
Nov 4, 2014
I'm trying to show measures from 2 measures groups in a drillthrough. Obviously, it's not possible with a standard drillthrough action, but I still hope that I can somehow achieve this with the ASSP GetCustomDrillthroughMDX function.
Speaking in AdventureWorks2008R2 terms: Imagine I have a pivot table with Product Categories in filter (say, filtered on Gloves) and "Internet Sales Amount" as measure. From context menu in Excel I can call the drillthrough action which shows me the individual sales records. I would like to show in drillthrough additionally "End of Day Rate" measure from "Exchange Rates" measure group.
One option would be to join FactInternetSales with FactCurrencyRate and make EndOfDayRate a physical measure in the "Internet Sales" measure group. This is a pretty huge overhead for my scenario and I'd like to avoid this.
Another one would be to call something completely external for a drillthrough (for example, a SRRS Report).
View 3 Replies
View Related
Feb 22, 2006
Hi,
I am having the requirement where I have to use string as measures. Is this possible in Analysis services 2K.
Any help on this will be greatly appreciated
View 23 Replies
View Related
Oct 7, 2015
I am trying to develop a cube with translated calculated measures in it. I have also translated the all underlying measures/dimensions being used in action for a header.
I can see my translated measures and dimension in excel but when I go to Underlying data, I see default member's names not the translated one. Please note that I have translated all underlying members.
View 4 Replies
View Related
Aug 28, 2015
I am new one in MDX. Our PeriodsToDate function does not return any value. We have set type property of our Date Dimension as time.Actually CURRENT MEMBER does not return a valid value. So our PeriodsToDate function fail.
With MEMBER [Measures].[YTD Actual]
AS
Aggregate
(
PeriodsToDate
(
[DimDate].[CalendarHierarchyDateLevel].[Calendar Year]
,[DimDate].[CalendarHierarchyDateLevel].CURRENTMEMBER
)
[code]....
View 2 Replies
View Related
Apr 24, 2015
large tabular cubes (SSAS 2012 SP2), with lots of calculations, the keyboard seems to be stuck, one cannot type or make any modifications to any measures, even after closing a project.
The only workaround is to completely restart the machine. Using Visual Studio 2010 Shell.
Are there any possibly corruptions or calc limitations that one should be aware of?
Notable that this never happens with any small cubes, but just with the largest cube that we have, that has probably tens of calcs.
View 6 Replies
View Related
Mar 27, 2012
I am trying to create a calculated member for parallel period function using ssas 2012. I have 10 measures for which i need to create parallelperiod.
I can successfully create for 1 measure but when i add multiple values to it it breaks. Below is the sample i tried for multiple measures:
sum(ParallelPeriod([Date].[Calendar].[year],1,[Date].[Calendar].currentmember) ,
([Measures].[Revenue],[Measures].[Expenses]))
View 10 Replies
View Related
Jun 10, 2015
I'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 Related
Jul 5, 2015
I have an old model that unfortunately had to be re-establish.
In order to save time, I thought that I can export all my measures and paste it as measures in my new model.
I used the following technique to export the measures from the old file: [URL] ....
How to use the output and create the identical measures in my new model, without the need to manually write each one of them?
View 7 Replies
View Related
Jun 10, 2015
I am unable to see my DSV when I try to edit my dimension. I have added some new fields in my dimension table and now want to use them in my cube dimension.
View 5 Replies
View Related
Apr 30, 2015
Many dimensions don't have unique members. Instead, the dimension source data has duplicates at the leaf level: it's left up to SSAS to aggregate up to the actual leaf level used in hierarchies.
Every cube I've worked on in the past, a dimension is clearly defined in the source data, with uniqueness already present there: we don't make a dimension out of duplicated, sort of facty data. This kind of design seems as weird to me as an unnormalised SQL database.
Here's an example to illustrate what I mean; I'll use that Adventureworks database.
We have a Geography dimension with a Geography hierarchy. Levels go like this from top to bottom:
Country
State-Province
City
Postcode
The Geography dimension has a key attribute called Geography Key. It's there in the cube design as a dimension attribute, but it's not in any of the hierarchies, so I can't query it in MDX. But that's fine: it has the same cardinality as the lowest level (Postal Code), because the dimension has some kind of normal design.
In the cube I'm dealing with, it's all messed up. Using the AdventureWorks example above as a parallel, someone made a Geography dimension with source data keyed on [PostalCode, ExactAddress], but only wanted the dimension granularity to be PostalCode.
This makes it very hard to debug why the data in this dimension is incorrect. I can't match up the dimension members in the cube to the source data, because the dimension doesn't actually go down to the real leaf level!
So I have a dimension attribute called ExactAddressKey, but I can't query on it in MDX, because it's not part of any dimension hierarchy. Unfortunately changing any part of this cube design is not possible, so I can't even experiment with settings and see what happens.
How I could get to the leaf level of the data imported? Something like
Geography.Geography.TheInvisibleLeafLevel.Members.Properties('Key')
Or does this kind of dimension design result in SSAS discarding all the data that's more granular than the most granular attribute defined in any hierarchy - so that the data actually isn't there to be queried?
View 2 Replies
View Related
Dec 24, 2002
Trying to create a time dimension off of a large table. When I go with the default "start year at Jan. 1", everything goes fine. When I switch that to July 1, however, (drat those fiscal years, anyway), the construction of the dimension times out. Any suggestions?
View 5 Replies
View Related
Apr 16, 2004
I have a database with millions of users with email addresses. I want to create an email domain dimension that groups domains into all the big email domains (hotmail, aol, yahoo) and an "other" consisting of all the other domains. I can create a table with entries for the big domains, and get the grouping working but anything that is not a big domain will get thrown out rather than put into an "other" category.
Any idea how to get such an "other" category?
View 1 Replies
View Related
Aug 19, 2015
I am trying to define the security based on Current Member.
Can i put following statement in the DimensionData - Allowed Members Set
EXISTS([Dim M].[Emp No].MEMBERS, [Security EMP].[Role].CurrentMember, "Uvw Security Emp ")
So that the list of EMP Nos that are visible changes when a different Role is selected (Filtered).
If not then is there any other way of achieving the same? What I want is that the list of EMP No that are visible should change when the user filters it using "Role" attribute in the "Uvw Security Emp" dimension.
If I add the Role attribute in the Dim M Dimension I think it will unnecessarily calculate aggregations, etc.
View 2 Replies
View Related
Nov 18, 2015
I have a measure group product price, the measures are
1. Product Id
2. Price
3. Date
I have mapped the product id and date with product and date dimensions.I need to get the latest price of the product.For example:
Prod1, 100, 1/1/2015
Prod1, 300, 2/1/2015
Prod1, 250, 3/1/2015
Prod1, 150, 10/1/2015
So, i need the latest price. The latest price is 150.I new to MDX query. MDX query to achieve this?
View 3 Replies
View Related
Jun 4, 2015
I am in a situation to slice a fact in two date dimension .. my <g class="gr_ gr_73 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="73" id="73">sql</g> code is like this
select count(1) from fact_table where end_date >=(selecteddate on filter) and startdate <=end_date .
The common date dimension is Start date. and the filter will be only on <g class="gr_ gr_168 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="168" id="168">end</g> date.
Below is the code so far
<g class="gr_ gr_228 gr-alert gr_tiny gr_spell ContextualSpelling multiReplace" data-gr-id="228" id="228">i</g> did, but <g class="gr_ gr_229 gr-alert gr_tiny gr_spell ContextualSpelling
multiReplace" data-gr-id="229" id="229">i</g> am not finding how to filter the start date <=end_date
with member [Measures].[X] as
Sum(
{[EndDate].[YQMWD].currentmember:NULL},
([Measures].[Count]))
select [Measures].[X] on 0 ,
[EndDate].[YQMWD].year.members on 1
from Cube
What is the trick I can put so that I can filter those data where start date <=selected end date ....
View 3 Replies
View Related
Jun 15, 2015
If I have a dimension with 3 records like this:
BrokerDimId BrokerNm
1 Broker1
2 Broker2
3 Broker3
And I have a fact table that has 2 rows that join back to the first 2 dimension rows like this:
BrokerDimId Amt
1 $100
2 $200
I want to create the mdx so that I get all of the dimension rows, even if there is no record in the fact table for it and just default the amount to 0, like this
BrokerDimId Amt
1 $100
2 $200
3 $0
Is this possible using MDX?
View 2 Replies
View Related
Sep 1, 2015
I am getting the dropdown of the dimension folder twice, as i took printshot from client tool. In SSMS/bids also its showing this error. How to resolve it.
View 9 Replies
View Related
May 4, 2015
I'm building a cube for sales team , to test out I'm trying to process just one dimension called DimCalandar , when I try to process this dimension I get the following error ,
'Either the user abc/def, does not have access to database, or the database does not exist' ...
View 2 Replies
View Related
Sep 2, 2015
I AM GETTING THIS ERRORS, and even when i drag and drop calculated measures, it gives error as" query error" with RED mark..
View 4 Replies
View Related
Sep 8, 2015
I have 2 dimensions that pull their Facility Name from the same Location Dimension. The business users want to change Facility Name in the Material Facilities dimension to “Material Facility Name”, but keep Facilities dimension attribute the same. What is a good way to go about completing this task.
View 2 Replies
View Related
Sep 17, 2015
The Member_Key we can get by the method suggested in the post, but when we get the Member_Key, Id as Measures/members then performance is very slow.
Is there any way i can get the Member_key in a set in rows section.
Like
With SET [UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME
Select Measures.Value on Columns,
UniqueName on Rows
From Cube
I want the value as just like
Value
Employees, UniqueName
View 2 Replies
View Related
Mar 14, 2013
I'm getting this error during processing one dimension.OLE DB error: OLE DB or ODBC error: SQL Server blocked access to STATEMENT 'OpenRowset/ OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.; 42000.my dimension contains member from two datasource table.
and in another dimension i get error;Errors in the high-level relational engine. The 'dbo_vicidial_Users' table that is required for a join cannot be reached based on the relationships in the data source view.Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Staging User', Name of 'DimUsers' was being processed.
View 4 Replies
View Related
Oct 8, 2015
Need to resolve this calculation, which I would believe is something very common on SSAS environments.
Like many companies, my company has different ways of calculating Sales and the two I want to focus are Sales Gross and Sales Net.
At a high level, we calculate Sales Gross as Sales with returns, and Sales Net as Sales without returns.
We have an attribute called Order Type that has various types of orders a user can execute with my company. One of them is Returns. If you return something back to us, we record that as a return line on the sales table. With that, we can calculate that return, breaking data down by Order Type, such as:
Order Type Line Total
Mail Orders $ 776,655.44
Internet Orders $ 2,211,334.00
Call Center Orders $ 11,223,344.00
Credit Orders $ (55,666.00)
Today, to calculate Sales Gross and Sales Net, we are creating two dimensions: DimSalesGross and DimSalesNet.
To calculate Sales Gross, we leave the data at the natural state, not making any changes to mappings.
To calculate Sales Net, we map Credit Orders to Call Center Orders at the ETL level, getting a Net value for sales (Orders - Returns), however, I doubt this is the correct way of doing.
I would like to have a Line Total Net / Line Total Gross calculation, which would be based on the Order Type value.
Perhaps using a CASE statement in MDX? Is the above possible?
View 4 Replies
View Related
Aug 3, 2015
I have a calculated measure. On a particular dimension I want to hide the grand total. for remaining dimension grad totals to be visible. Like the below screen shot ....
View 4 Replies
View Related