Analysis :: Conditional Sum Based On Latest Survey Answer
Jul 24, 2015
Basically our cube contains two different Facts:
- Sales Volume: Information about sold articles to a customer incl. Selling date
- Survey: irregular answered survey questions about customers incl. date of answer
and three Dimensions:
- Customer
- Date
- Survey Answer: Information about possible Answer values (e.g. Yes / No)
Relations:
Fact: Sales Volume ------> Dim: Customer <------ Fact: Survey ------> Dim: Survey Answer
'--------------> Dim: Date <----------------'
We would like to be able to determine the aggregated sales volume (sum) of a customer for a specific period depending on the latest survey answer within this period.
For example:
Selected Time period: Jan - Jul 2015
Sales Volume Customer X - Jan - Jul 2015: 1000 Litres
Sales Volume Customer Y - Jan - Jul 2015: 500 Litres
Surveys answered:
15th Jan 15: Customer X, Survey Question A: Yes
2nd Mar 15: Customer X, Survey Question A: No
20th Apr 15: Customer X, Survey Question A: Yes
10th Feb 15: Customer Y, Survey Question A: Yes
20th Jul 15: Customer Y, Survey Question A: No
Latest survey answer (Jan-Jul) Customer X, Question A: Yes
Latest survey answer (Jan-Jul) Customer Y, Question A: No
Excel Pivot should show something like this:
Question | Latest Answer: Yes | No |
------------------------------------------------------------------------------------
A | 1000 Litres | 500 Litres |
How this is possible and has to be implemented?
View 5 Replies
ADVERTISEMENT
Nov 27, 2007
SQL Server Integration Services team would like to hear from you. Our goal is to understand your current Data Extract, Transform & Load (ETL) practices and prioritize features for future releases.
You will have a chance to win one of five 80GB Zunes. To win a Zune, you must fill out the survey by Dec. 20, 2007.
To participate in the survey, please click here: https://mscuillume.smdisp.net/Collector/Survey.ashx?Name=SqlETLSurvey2
Thank you,
SSIS Team
View 2 Replies
View Related
Jun 30, 2015
I have a cube with a fact table and 3 dimensions. One of the dimensions is a type 2 and surrogate key is stored in fact table. If i query the database, the dimension attributes display correctly, however the cube is always displaying the latest dimension attribute and not preserving the history.
The measures are correct for the time period displayed, but the dimension attributes always show the latest values.
View 3 Replies
View Related
Dec 3, 2012
I want to select the latest single value based on ProductID. The value should be the last value inserted from the Date column.
I want to use this as part of a query to delete this value when a button is slected in ASP.Net C#.
Here is my code which does not work
select basketID, SessionID, UserID, MAX(Date), productID from ShoppingBasket
where userID = '1'
group by productID, basketID, SessionID, UserID
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
Jun 4, 2008
Good Afternoon,
I am new to MSSQL and am trying to write a complicated SQL statement that I'm having trouble with. Any help that anyone can offer is much appreciated!
Here is the problem I am tackling:
I have a list of about 5,000 members of our organization stored in the MemberList MSSQL table. I have a separate MSSQL table (CityList) that has approximately 500,000 resident of a city.
I am trying to find matches between MemberList and CityList for the purposes of figuring out which of our members are registered voters.
The tricky part of this problem, is that there is no unique ID (such as a social security number) that is present in each list. Accordingly, I have decided to created several types of matches:
1. NameDOBMatch: Where the FirstName, LastName & DOB fields in MemberList table match the same fields in CityList table.
2. NameAddressMatch: Where the LastName, FirstName & Address fields in the MemberList table match the same fields in the CityList table.
3. DoubleMatch: A combination of the first two matches (i.e. where the LastName, FirstName, DOB, & Address fields in the MemberList table match the same fields in the CityList table).
My goal is to "loop" through the MemberList and CityList tables and to add a new row to a third MSSQL table (MemberMatch) each time one of the aforementioned matches is found. The MemberMatch table has the following fields:
1. MatchID (key)
2. MemberID (Unique ID of member from MemberList table)
3. ResidentID (Unqiue ID of matching member from CityList table).
4. MatchType (value of NameDOBMatch, NameAddressMatch or DoubleMatch, depending on match type).
If anyone could help me create an SQL statement that would accomplish that, I would very much appreciate it!
Thanks,
Bryan
View 9 Replies
View Related
Oct 15, 2013
In the database, there is Date, Store#, Item#, and %Total Sales. In some cases, the same item# for the same date may be given more than one value for '% of Total Sales'. (For some reason this is a valid business scenario that happens rarely, but it happens.)
In that situation only, the requirement is to sum the two values together into one line. So if Item# 123 has a line with a value of .05%, and another line with a value of .08%, I need to sum those two values into one line for Item #123 that has a %Total of .13%. ONLY when an item has more than one percentage assigned, those percentages should be summed. Otherwise, if an item# has only one percentage value assigned, we just want to see that value.
Basically, I would like to implement logic that would work like this:
SELECT Date, Store#, Item#,
CASE WHEN Count(%Total Sales) >1 THEN Sum(%Total Sales)
ELSE %Total Sales
END
FROM (some tables and joins)
GROUP BY Date, Store#, Item#
However, I'm not sure how to craft it so that I don't get a syntax error (this query produces errors).
View 5 Replies
View Related
Mar 12, 2007
Hi,
I have a report that is conditionally showing a textbox based on the previous entry that is working correctly.
My issue is that the non visible entries are still being added to my Sum statement at the end of the report.
I need a way to exclude an entry based on its visibility.
Any help would be greatly appreciated.
View 9 Replies
View Related
Mar 5, 2014
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
Sales Table:
BillMasterId BillDate CustomerId NetAmount CreditAmount
26 03/03/2014 101 1000 1000
My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
My Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0
Exact Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0
View 7 Replies
View Related
Sep 23, 2007
Hi,
I have the following table in MsAccess
EmployeesA
empId integer,
empName varchar(60),
empAge integer,
empStatus char(1) - can be N,D or S - New, Deleted or Shifted
and the following in Sql2005
EmployeesB
Id smallint,
Name varchar(60),
Age int,
Status char(1) - Bydefault 'N'
I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.
If empStatus = N in EmployeesA, then insert a new record in EmployeesB
If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'
If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.
How do I do it for each table each row in EmployeesA using a foreach file loop?
Thanks,
lolsron
View 6 Replies
View Related
Jun 15, 2007
I am trying to create a report which has conditional formatting.
The primary dataset is a view of objects with several values
eg
object1,0,4,0,1
object2,0,3,1,1
The secondary dataset is the comparison table and just contains the values
eg
0,3,1,1
I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset
eg
=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")
i get
Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?
Ideas gratefully received !
View 7 Replies
View Related
Mar 15, 2007
I have two databases located on seperate machines, using different platforms and credentials.
I have setup dataset1 source to the first database wich provides me the following colums
Incident_id, description, date, status.
And dataset2 datasource provides
incident_id, is_billable
My report is a tabular report showing the four fields from dataset1.
I am trying to apply some conditional formating wich will link the two datasets on the incident_id field.
something along the line of if the dataset1.incident_id is in dataset2.incident_id then turn it red.
I tried using the below expression on the color property but the IN part is not allowed.
=iif(Fields!INCIDENT_ID.Value,"Dataset1" in Fields!INCIDENT_ID.Value,"Dataset2","Black","Red")
View 2 Replies
View Related
Dec 15, 2005
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
View 6 Replies
View Related
Dec 15, 2005
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
$3.99/yr .COM!
http://www.greatdomains4less.com
View 3 Replies
View Related
Aug 27, 2007
I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example).
I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.
Any help would be appreciated.
Jerry
View 2 Replies
View Related
Jul 14, 2015
My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum PolicyStartdate
123G 01/01/2012
456D 02/16/2012
789A 01/21/2012
163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
View 12 Replies
View Related
Sep 5, 2007
This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need.
Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing.
Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command.
How can I do this?
Thank you!
Nolan
View 1 Replies
View Related
Sep 4, 2015
We are building a dataload application where parameters are store in a table. And there are multiple packages for each load.There is a column IsChecked column if it is 1 then only the child package should execute.Created a master package. In which i have taken execute SQL task in that storing a results in variable and based on the result the child package should execute. But In executesql task i selected result set as full result set. I am getting the below error.
[Execute SQL Task] Error: Executing the query "SELECT isnull(ID ,0) AS ID FROM DataLoadParameter..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::LoadValue" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 3 Replies
View Related
Jul 15, 2014
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
View 6 Replies
View Related
Aug 2, 2007
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
thanks
View 1 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 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
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
Jun 6, 2007
Hello,
I was trying to do cascading parameters based off my cube and I wasn't able to do this. Is it possible?
For example, I have a dimension that has Products so I first select the parameter for product type (Dairy, Frozen, Candy) and then I have another dropdown listbox that has the name of each product (Milk, Ice Cream, Lemon Drops). The second dropdown listbox should only contain the products that match what parameter was selected in the first dropdown.
When I couldn't get that to work, I went to the source system containing the Dimension tables and just did nice and easy SQL statements from there. It worked but I, for some reason that I can't explain, think this is not the proper way to do it.
Also, is there a way to have a default on the second parameter based on the first parameter selected? I would assume that default would be [All].
Thank you.
-Gumbatman
View 4 Replies
View Related
Jun 3, 2015
I 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 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
Jul 29, 2015
I am new to SSAS. I have requirement to build a cube based on SQL Stored procedure. This Stored Procedure contains lot of temp tables, which are aggregated as measure columns.
Initially I have done creating views on each temp table, finally I created a view which calls like 15 views. when I try to execute the view, it is taking long time to execute the view.
I tried building cube on this view, when I try to deploy, even it is taking long time to deply..I have waited for 2 hours, still the deployement process going..
What I wonder is, is there any other way I can build cube based on SQL stored Procedure.
View 2 Replies
View Related
Sep 28, 2015
I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:
- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.
Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department.
It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.
View 3 Replies
View Related
Aug 26, 2015
I would like to add a calculation to provide an annualized Gross Amount value. Most solutions I have seen are for annualizing down to the day. For instance, if today is the 123rd day of the year, the calculation would be Amount / 123 * 365. What I want to do is annualize the amount based only on completed periods. Today is 8/26.
The last completed quarter ended on 6/30. Therefore I want to basically take the sum of Q1 and Q2, divide by two, and multiply by four to get an annualized amount based on completed quarters. Eventually I want to do something similar with months.
So here's what I have so far. In my DSV my DimDate is actually based on a view. The view has a Date column with the actual date, all of the normal fields you would expect in a DimDate table, and two columns that look like this:
CASE
WHEN [Date] < DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0)
THEN 'Y'
ELSE 'N'
END AS
CompletedQuarter,
[Code] ....
Note that for dates between 1/1 and 3/31 the CompletedQuarterCount will be 0. I want any annualized amount to be 0 in that case because I only want to use completed quarters in my calculation.
I added both to my Pay Date dimension (which uses vwDimDate from the DSV) in the cube. I have tried the calculation below and in my Excel pivot table I'm getting blanks for the calculated field.
CREATE MEMBER CURRENTCUBE.[Measures].[Gross Amount Annualized by Pay Date Quarter]
AS SUM(
IIF([Pay Date].[Pay Date Completed Quarter Count] > 0,
[Gross Amount] / [Pay Date].[Pay Date Completed Quarter Count] * 4,
0)
),
FORMAT_STRING = "Currency",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Claim' ;
So, is my calculation correct?
View 2 Replies
View Related
Nov 29, 2007
I've got a RS2000 report from Analysis services 2000. This report is working fine but after the upgrade to SQL2K5, i'm having issues with changes to parameters.
Basically i'm passing DateQuarter, CustomersLevel as parameters to the following mdx script. I've tried to change mdx script in RS2005 but no luck yet. I don't much about the parameter level changes in sql2k5. So can anyone suggest what is wrong in the script.
In sql2000 the script looks like this...
="with " &
"member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' " &
"Member [Measures].[CustomerUniqueName] as '[Customers By Class].currentmember.UniqueName' " &
"Member [Measures].[CustomerDisplayName] as '[Customers By Class].Currentmember.Name' " &
"SELECT " &
"{[Measures].[CustomerUniqueName],[Measures].[CustomerDisplayName]} on Columns, " &
"filter({descendants(" & Parameters!CustomerClass.value & ", [Customers by Class].[" & Parameters!CustomersLevel.value & "], SELF)}, " &
" [Measures].[Ex Price] > 0) on rows " &
"from Sales "
i've changed the script in sql2005 but not working yet..
with
member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)'
Member [Measures].[CustomerUniqueName] as '[Customers By Class].currentmember.UniqueName'
Member [Measures].[CustomerDisplayName] as '[Customers By Class].Currentmember.Name'
SELECT
{[Measures].[CustomerUniqueName],[Measures].[CustomerDisplayName]} on Columns,
filter({descendants( STRTOMEMBER(@CustomerClass), "[Customer Class]." + STRTOMEMBER(@CustomersLevel), SELF)},
[Measures].[Ex Price] > 0) on rows
from Sales
Thanks in advance..
Vivek
View 4 Replies
View Related
Feb 7, 2008
Mr. Pearson,
I am currently reading your article titled, "MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist". This article is directly applicable to a problem we are currently trying to solve regarding ragged hierarchies as input parameters.
I have not read the entire article through but am in the process of doing so. Also, I will be trying to implement your solution.
I have one question, will SSRS support multi selection when using a hierarchical picklist? Thanks in advance for any assistance.
View 1 Replies
View Related
Sep 2, 2015
getting 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?
View 4 Replies
View Related
Oct 2, 2015
I have a business requirement to build a tabular data model, where I need to mask information of other Agents from a given Agent but I still need to show the overall sales of the given product.
For eg: IF an Agent is in APAC region he should see APAC region sales and also should be able see the sales of the same product in other region without knowing region specific break down.
For Agent "Tom" in APAC region, the numbers will look like this
APAC_Sales = 100,000
Other_Sales = 500,000
And if "John" is in NA region, then the number will look like this for him
NA_Sales = 200,000
Other_Sales = 400,000
I wanted to create "Roles" based on the Region, so all the agents belong to "APAC" region will have same view as Tom and "NA" region agents will have John's view.
View 2 Replies
View Related
