Two Measures With The Same Dimension But With Differents Values
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
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
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 ?
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
I made a number of dimension and calculated member mods and notice that all now no base or calculated measures display values, they just show blanks in the browser and query window. To try to isolate, I've (1) removed every dimension and calculated member (2) verified there are no defaultmembers for dimensions (3) verified there are no dimension attributes set to IsAggregatable = False
(4) verified there are no deploy or script errors (5) fully processed every dm and fact (6) verified there are dim and claim records loaded with values in all appropriate columns
When I process the cube, it shows that rows are read, etc... All that looks normal. Just when I browse or query, all measures display as blanks or nulls.
What could cause this behavior? Any ideas on how to futher isolate? I can go to a backup but very concerned that this can happen with no error messages or warnings, so I want to find the cause.
Bitmask fields! I am capturing row changes manually via a high frequency ETL task. Â It works effectively however i am capturing the movement of multiple fields. Â A simple example, for Order lines, i have a price, a discount and a date. Â I am capturing a 001, 010, 100 respectively for each change. Â
I would like my users to be able to select from a dimension which has the 3 members in it and they can select one, multiples, or all values (i.e. only want to see rows that have had the date and price changed).Â
Obviously if i only had 3 columns i would use bit's and be done with it, i have many different values (currently around 24 and growing).
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.
I have a fact table with a simple integer lookup key into a basic dimension table. However, some of the fact lookup key fields are NULL. I would like the Analaysis Services reports to show this NULL category. Instead, Analysis Services discards any NULL entries and the records are completely absent from the reports. What is the best way to achieve this?
I've a table which contains a record for each day an employee is employed:In the cube I've create a measure which is a sum of the field "Employed" which provides me the number of employees that are employed for a given date (lowest level of my time dimensions which are YEAR-MONTH-DATE)The problem is that the number aggregating on YEAR and MONTH which provides me with wrong figures at these levels - So how can create a calculated measure (maybe with Scope) that only show the Ultimo Numbers at the lowest level when I am browsing on the Month or Year level. For example if I am on the Year level I only want it to sum on the date 31-12-xxxx and if I'm at the month level(for example July 2012) it should show me the sum of 31-07-2012 which are the last level for the given month.
This provides a good example for my situation. In this example, you will see a Movie dimension with four attributes; Genre, Language, Movie, and Theme. I have a similar setup except mine is Top Level Hierarchy>Categories>Values which are all under the one hierarchy.
My Question: I have the dimension setup as a multi-value parameter in one of my reports. When I filter on a value in Genre and in Language, it provides all values from that genre and all values from that language. I really only want the values that include both.
If I filter on Western and English, I get Movie1-5 when all I really want is Movie2 only. Is there any way to have this do an Intersect within the same dimension or do I have to build each one out into its own dimension?
Have 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?
what security measures should I take to restrict access on sql server 2005 from LAN and WAN. I am using local Ip address to connect Sql server. Security measure should be so strong that unauthorized user can not access my database.
Hi, I would like to create a report on a cube with Report Server (Query Builder) with one dimension in columns and the "measures" dimension members in rows:
Jan Feb Mar ... Measure1 10 12 14 Measure2 20 22 24 ..
How to create with either Tabular or Matrix report type?
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.
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...
I am trying to calculate the median value using one of the measures and a dimension value.
Time is a measure in my cube and OpId is one of the dimensions.The result is as follows:
opid time median
1 55
2 23
3 23
Total 23
The Time here for Op Id 1 is the aggregation for all the rows whose OpId is 1.I want the median of the values whose OpId is 1 which is not showing at the moment.
What I am getting here is the median for all of the OpId but what I really want is the median for each of the individual Opid's as well.
I am using a calculated field Median with the following expression.
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.
My report, which has a matrix and a chart work fine. Then...
I click on the Data tab, drag a measure into the results grid. At this point I haven't added the new measure to the matrix or anything in Layout.
I click the Preview tab and get the following message: "The definition of the report XXX is invalid. More than one data set, data region, or grouing in the report has the name 'YYY'. Data set, data region and grouping names must be unique within a report.
Ok, I get the point. But why and how would adding a measure give me this issue? Where can I even find where this is being duplicated?
The message refers to a name that is a parameter, and I don't see anything in there that might cause this.
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 ?
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).
We have a cube that has financial data in it. There are report actions on cells. I want them to be able to drill through the the supporting data stored in the fact table and all the measures I am showing are calculated measures.
Users connect to this cube from Excel 2007. If they use a "Filter" in Excel and do not select "All" or "One" of the members of the filter hierarchy, then the action dissapears. Microsoft explained this is because that cell then has a calculated cordinate and they can't determine the action to perform.
Has anyone experienced a need to do this? Can you provide me alternatives?
Thanks
[Edit]
The functionality I am looking for is not available in the BIDS either (Well it is to a point--if you select the first 2, 3, 4...members in the filter, the action will still appear. It only reads the first one however. If you select the members starting with any but the first, the action disappears).
I have a fact table with one column storing string values. I need to use this column as a measure, but as different measure. Here's a snapshot of the fact ( with 4 columns)
#1 #2 #3 #4(value) 1 1 2007-12-20 Male 2 1 2007-12-20 French 1 2 2007-12-20 Female 2 2 2007-12-20 English
I have a dimension which is like id name 1 Gender 2 Language
Is there a way to get measures from column 4 in fact table - so that I can give reports like how gender graph during a time period or language usage during a time period. The design will have more than 2 dimension members, so fact column can hold more data like location, etc...
Can you please suggest a way to handle such kind of fact table?
When I try to create a measure based on that column (and 'no aggregation') - I get error like - "Error 1 Errors in the metadata manager. The data type of the 'value' measure is not valid because the data type of the measure is a string type."
is there a way to create parameterized FYTD and LFYTD measures and avoid hardcoding fiscal year end date in the DATESYTD function ?
I have tried using both VALUES and LOOKUPVALUE to retrieve fiscal year end date inside DATESYTD, but it apparantly only accepts a literal.I have created a Calendar table with e.g. FiscalYearIndex which is 0 for current fiscal year dates and was thinking somthing with CALCULATE and a filtered Calendar table based on FiscalYearIndex = 0.
The Semi-Additive Measures feature of MS SQL Server 2005 is not supported in its Standard Edition SKU. How do solve this issue? What editions support the feature?
How do I stop the PowerPivot Grid from re-ordering the measures?
This is a very significant issue from my perspective since currently everytime I open the PowerPivot model the ordering of the display of the design of the measures is changed every time the PowerPivot model grid is opened.
This is so trivial but I cannot figure out where the calculated fields/measures area is in the PP window. Is there something to toggle it on and off? I used PP in Excel 2013 before and the area I'm talking about was at the bottom of the PP window. Now I'm with a new company who has Excel 2010 so I downloaded the PP add-in for Excel 2010. It is version 10.50.4000.0. This add-in does not have a calculated fields/measures are at the bottom of the PP window. Is it just a matter of 2010 vs. 2013 or is there a way to turn the frame area I'm talking about on?
I have a complex model with lots of tables and measures. It's got to the point where pasting measures in the measure grid is getting very slow with a few seconds flashing "finishing". The calculation mode is set to manual and I'm not switching between the workbook and the PP window. How to speed this up?
