Analysis :: Create A Calculated Set From Union Of Values In Two Sets?

Oct 26, 2015

I have the following MDX Query:

Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
[Cost Centres].[Cost Centre].[All].Children
[Locations].[Locations].[All].Children
on 1
From MyCube

which produced the following table:

Division
 Cost Centre
 Location
 Dollars
AA
1
X
$30.00

[code]....

What I am hoping to do is create a set out of the Union of specific values in the [Cost Centres].[Cost Centre] and [Locations].[Locations] hierarchies into a single set and use that new set in my MDX query across the columns.

Using the table and query from above, I have the following conditions that would determine the value in the set (lets call the new set 'NewSet')

When Cost Centre = 1 and Location = X Then "CustomType1"
When Cost Centre = 1 and Location = Y Then "CustomType2"
When Cost Centre = 1 and Location = Z Then "CustomType3"
When Cost Centre = 2 and Location = Y Then "CustomType4"
When Cost Centre = 2 and Location = Z Then "CustomType5"Else "Default"

Then, if I was to execute the new query:

with

set NewSet as "Some Unknown Magic Here"

Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
{NewSet}
on 1
From MyCube

I would end up with 

Division
 NewSet
Dollars
AA
CustomType1  
$166.64
AA
CustomType3 
$64.24
BB

[code]....

View 2 Replies


ADVERTISEMENT

Analysis :: Two Sets Specified In UNION Function Have Different Dimensional

May 14, 2015

I am using following Query to get data from Cube for some dates and Quarters at single time, but I am getting an error "Two sets Specified in the UNION function have different dimensional".

WITH 
MEMBER [Measures].[Data Value1] 
AS [Measures].[Average Value]
SELECT 
{UNION(
([DimPeriodType].[Period Type Key].&[1],[DimDate].[Date Key].&[20150428],[DimEnergyCalculated Ratio].[Calc Ratio Key].&[1],[Measures].[Data

[code]....

View 3 Replies View Related

Analysis :: Create A Calculated Member For Cumulative Sum Using Fiscal Calendar?

Jun 2, 2015

I 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.

View 2 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

Calculated Field Using Two Data Sets

May 12, 2008

Hi,

I'm trying to created a report.
Final report looks like this.







Total Loans/Lines (#)
13,283



Total Commitments ($ MM)
$1,703



Total Outstandings ($ MM)
$1,175







A
B
C
D
F

Bankruptcy
0
$0
$0
0.00%
0.00%

Charge Off
0
$0
$0
0.00%
0.00%


Source table looks like this;







Bankruptcy



0

Charge Off



0

CLTV
131

DSR
102

Exc Total
265

FICO
7

Foreclosure/Repossession

Grand Total
13283

Loan Amount
32


Column D = A Bankrupcy(0) / Total Loans/Lines #(13283)

But it does not let me to use report expression as its not in the same scope.

Can anyone tell me how to do this calculation ?I was trying to use a report expression but it seems like not working.

Thanks

View 15 Replies View Related

Adding A Calculated Field By Using Two Fields From Different Data Sets

Oct 1, 2007



Can I make a calculated field by using two fields from different data sets?(I'm talking about SSRS data sets)

I tried to do that. But I got a error message.




"Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."


Please can some one help me out?

View 5 Replies View Related

Union Two Sets (consolidating Results)

Jul 8, 2015

Code:
SELECT DISTINCT LEFT([REPORTING_MONTH], 4)+'-'+SUBSTRING([REPORTING_MONTH],5,6) as REPORTING_MONTH, t.EMPLOYEE,
'' as COUNT_FTP,
CASE WHEN [MEDIUM_RCVD] = 'EMAIL' THEN COUNT(MEDIUM_RCVD) ELSE '' END AS COUNT_EMAIL
FROM [GPO].[dbo].[DW_SUBMISSION] as s
JOIN #TEMPActivity as t

[Code] ....

I'm trying to get the set to come out all on one line. REPORTING_MONTH, EMPLOYEE, COUNT_FTP, COUNT_EMAIL

But when I try null or '' it creates a second record and doesn't merge the two results.

View 3 Replies View Related

How To Rank An Union Of Two Result Sets?

Mar 1, 2007

I am new to SQL Server Integration Services. I need to help here.

If there is only one result set, I could get rank info by putting DENSE_RANK expression inside of my select. My case is that I have two data source. After union of them, I need rank the result set and save result set plus rank info into the destination. I tried to use Derived Column component by using expression DENSE_RANK() over ( order by columnName ). But it does not like it.

Does anyone know how to achieve this?

Thanks!

View 3 Replies View Related

Analysis :: Translate Named Sets

May 19, 2011

I have some Named Sets that I want to translate. I have done exactly the same way as with the Calculated Members but the Sets don´t use the translations? Any ide why?

View 2 Replies View Related

Analysis :: Partitioning Sets Of Data Dynamically

Jul 17, 2015

I have a situation where i have a transactional fact table which consists of date, row type, order number and value.  Simple example below

Date, RowType, OrderNo, Value
01-May, New, A1, 100
01-Jun, Change, A1, -10
01-Jul, Invoiced, A1, -90

What I need to be able to do is somehow select based on a day, the total value of open orders.  I have tried to do this in the database but it becomes fixed and quite cumbersome (this is a simplified example in reality i have line information and line component information).I am not hugely skilled with MDX and SSAS but know there are some semi-additive functions i want somebody to be able to pick a day and have the total value of only open orders.

View 2 Replies View Related

Analysis :: Adding / Removing / Modifying Named Sets

May 25, 2015

Is it possible to add, remove or modify a named set in an already deployed cube? I don't want to modify anything except the sets. If it is possible at all, are there any example of doing it?Second is technical. I have redefined set "myRowsSet" to "myRowsSet2". What I did is I removed the calculated members and placed their code directly inside the set. Why isn't it working?

with
-- road bikes in france
member [Product].[Product Line].myMemberRoad as aggregate(
existing(
intersect(
[Product].[Product Line].[Product Line].members
,[Product].[Product Line].&[R] -- road

[code]...

View 2 Replies View Related

Analysis :: SSAS Calculated Member - MDX

Jul 1, 2015

I 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. 

View 4 Replies View Related

Analysis :: Dynamic Calculated Measure In MDX

May 8, 2015

 I have a dimension called as DIM1 which has list of all measures and has an attribute called as ATTRMDX Formula. The formula will be like

([DIM1].[ATTR Measure Code].&[M1],[Measures].[ATTR MEASURE VALUE])+([DIM1].[ATTR Measure Code].&[M2],[Measures].[ATTR MEASURE VALUE]).

I want to pass this formula to a calculated measure as given below -

MEMBER [Measures].[FormMeasure] as ([Measure].[ATTRMDX Formula].currentmember.MEMBERVALUE)

but I get the string value itself as output, but when I put the formula as a string in the calculated measure I obtain the value.

View 2 Replies View Related

Analysis :: MDX - Count A Set As Calculated Measure

Sep 9, 2015

I am trying to count a set as a calculated measure, when this set is called directly in the row , it returns fast, but when i try to count the set as calculated measure(so i can slice with another dimension) the query keeps running forever.

The queries are below

select
{} on 0,
nonempty
(
{([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])}
,
{([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])}
)  on 1

[Code] .....

View 5 Replies View Related

Analysis :: Calculated Attribute In SSAS?

Sep 17, 2015

I've been working with SSAS for a good few years now but I keep bumping into this problem - my users are trying to build a measure that is based on a calculated attribute and finding it difficult to work out how to write the MDX to do so. Intuitively, they thought a Calculated Member would work, but I don't think a Calculated Member is quite the same thing from my understanding.

So, here's the scenario.

We have a Product Dimension. We have a Measure that is the Number of days the Product took to make, e.g. 5 days. We also have a Product Count measure that counts the number of Products.

The user would like to write a calculated measure that works out the number of products that took <5 days, 5-10 days, 10-15 days etc.It would be easy to write a set of calculated measures for each of these bandings, but the user wants effectively a single dynamic attribute to use in the calculation in order to automatically distribute these values across the columns in their pivot table.

Is this even possible? I was thinking I could build an attribute on the Product Dimension in the ETL to do this quite easily, but the user wants to be able to change the bandings on the fly by changing the MDX for the attribute, rather than go back to the developer every time.

View 4 Replies View Related

Analysis :: Building A Calculated Member Using Except Function

Aug 23, 2015

I’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] ....

View 6 Replies View Related

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

View 5 Replies View Related

Analysis :: Calculated Measure Under Folder In SSAS

Sep 2, 2015

How to put these calculated measures under the folder "sales line". i want to see it under the drop down...

View 5 Replies View Related

Analysis Services - Calculated Member Ignoring Decimal

Feb 1, 2006

Hello,

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

View 1 Replies View Related

Analysis :: SSAS Calculated Members For Each Week Of Month

Jul 6, 2015

I have a date dimension with structure/hierarchy (year - month - wkofmonth - day) & measure count

This works fine when I insert wkofmonth in columns & count in values but when I add another measure it duplicates per week

Product CountWk1(jan) CountWk2(jan) CountWk3(jan) CountWk4(jan) SalesVolume
1            1                        2                  0                         0               1000

Is there something I can do on the structure & do 4 calculated members pending how many weeks in a month, is there a simpler way where the next measure does not calculate by columns field.

If calculated member for each week, how do I do this, any example code?

View 2 Replies View Related

Analysis :: Increase Size Of Calculated Column In Cube

Aug 21, 2015

Where we have increase the size of the calculated column in cube..whether in attribute  or some where else...

View 4 Replies View Related

Analysis :: Statistical Function Results In Calculated Members

Jul 8, 2015

I'm working with the statistical functions Stdev and Median with calculated members.  The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself).  Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :

stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value])  returns answers in the thousands when it should be more like 2.5

When used with a query, there would only be a single date member and a specific parameter member.  The total number of fact records is between 200 and 500 with values that range between 0 and 150.  This is the version that gives me answers that resemble the total sum of the [Measures].[Value].

If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....

stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members, [FACTTable].[FACTTable].[KeyField].members ), [Measures].[Value])

View 4 Replies View Related

Analysis :: Total Calculation Incorrect When Using IF In Calculated Member

Oct 27, 2015

I 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.

View 3 Replies View Related

Analysis :: Calculated Translated Measures - Underlying Data

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

Analysis :: PeriodsToDate Function Return Nothing In Calculated Measures

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

Caching (storing) Calculated Members In Analysis Services 2005

Mar 2, 2006

Do anybody know if Analysis Services 2005 allows cashing (storing) calculated members ?

There is a cube in Analysis Services 2000 with a calculated member based on some running sums. The performance of MDXs is too slow because running summs are being recalculated for each member of time dimension on each call.

I think that caching calculated members might help me and hope that this feature exists in Analysis Services 2005 .

Thanks!

View 1 Replies View Related

Analysis :: How To Apply A Filter To Decide Which Formula Is Used In A Calculated Measure

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

Analysis :: Create Analysis Services Project In Visual Studio 2012 Data Tools?

Feb 18, 2013

It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?

View 5 Replies View Related

Analysis :: How To Make Calculated Measure Behave Based On Dropped Dimension

Jun 25, 2015

i want to create a new measure that will behave based on the dimension dropped,ex. if i added the employee dimension only it will aggregate data from the #Calls Count but if i added the product dimension it should display # Product Calls at the product level and #Calls Count at the employee level as shown in the screen shot.

View 7 Replies View Related

Analysis :: How To Union 2 MDX Query Results From 2 Cubes

Nov 12, 2015

How can I UNION two MDX query results which are deriving from 2 cubes?MDX queries will return same ROW information and only Measure names will be different.

View 8 Replies View Related

Reuse Calculated Values

Nov 14, 2006

What is the best code pratice to use do the following code,

SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
sum(In1+In2)/10 as inDec,
from fo group by fo.no,fo.name order by fo.name

instead of

SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
((sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END))+sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END)))/10 as inDec,
from fo group by fo.no,fo.name order by fo.name

I cant use functions and procedures. Is there any better and cleaner way to code this, reusing the calculated values?

View 1 Replies View Related

How-to Return Calculated Values From A Called SP

Dec 11, 2001

Can someone please reply w/ example syntax on how to receive calculated variables from an invoked SP. DESCRIPTION:

sp_caller invokes sp_calc_values (passing @var1, @var2) via:
exec sp_calc_values @var1, @var2

sp_calc_values receives @var1, @var2 then calculates @var3, @var4

HOW does sp_caller receive the calculated values @var3, @var4 ???

thx in advance

View 2 Replies View Related

Calculated Values And Database Design.

Jul 20, 2005

Hi everybody,I need some help on calculated values in my database design.I'm creating an accounting / business management application with theusual modules like A/R, A/P, Inventory, etc.I don't know how to handle calculated values in fields like "CustomerBalance", "Inventory Item Qty on Hand", "Inventory Item Qty Last cost"and other similar.I don't want to create fields in the database to store these valuesbut I want to create them on the fly instead. Another thing I preferto do is use code in my application (vb.net) not T-SQL to do this(stored procedure or views), because this way I'm moving businesslogic into SQL; so far all the logic is located in a Business RulesDLL.My question is how can I do that and at the same time achieve "easy"joins.For example:Every time I retrieve some customer's info I need his balance. Let'ssay if my query is:SELECT CustomerID, CustomerName, Address from CustomersWhere CustomerID = xxxxxx'How can I retrieve the balance, when is calculated from other fieldsin other tables, such as Invoices, Credit Memos and Cash Receipts?I thought to use views and add a join to the above query. Is that goodor bad, performance wise?What other options I have?Looking forward for your answer.Thanks in advanced.Stefanos

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved