.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Using calculated members in rows

Posted By:      Posted Date: October 06, 2010    Points: 0   Category :Sql Server
 

Hi guys

Is it possible to use Calculated Members in rows? I'm trying to alias my column name by using members on the rows axis. Take for example the following MDX query:

 

 

SELECT {

 

ON COLUMNS

,

{

(

[Dimension].[Hierarchy].[Hierarchy].

 

ALLMEMBERS

)

}

 

DIMENSION


View Complete Post


More Related Resource Links

overriding "grand totals" for calculated members

  
hi all, is there a workaround for overriding "grand totals" for calculated members?  (I cannot use the workaround whereby I create the calculation in the DSV or view directly due to it being a pretty complex Descendant parent/child calculation).   thanks much for any thoughts, Cos

Long list of calculated members

  
·         I have a long list of calculated members. How can I make this long list user friendly like categorizing them by functionality? Can I create measure group for calculated measures?   Alex  

MDX query performance is slow for complex logic implementation in Calculated Members.

  
We have implemented a calculated members which involves multiple calculated members  called in a nested e.q. Cal1 calls Cal2 and Cal2 calls  Cal3  and Cal3 calls Cal4 .  And when browing Cal1 through Excel it takes around 30 mins for getting result from a Partition having row count around 5 million. Follwing are the information on the scenario I am having: We are having partitioning in the cube. one partition contains around 4-6 million records Processers on Query Server: 4 quad core RAM on the Query Server: 64 GB Calculated members having simple calculation logics are giving results in no time. There is a Calculated members which calls 6 intermediate calculated in nested way and with small Data Set like 5-6 k rows it is running fine.   Any help will be much appreciated.   Regards, Sandeep

Use of Calculated members from one fact into the other fact MDX

  
Hi, I have a FactBookvalues which calculates the Book value of several aircraft. this book value has a startdate going fro example from 1980 up to inlcuding 2030. With this MDX statement im able to determine what the book value is for a specific month: SELECT {[Measures].[Asset Bookvalue balance]} ON 0,   [MSN].[Asset Label].allmembers on 1 from CMSX_DWH_OLAP where {StrToMember("[Book Value Start Date].[Year - Quarter - Month].[Month].&["  + Format(now(), "yyyy") + "]&[" +  Format(now(), "MM") + "]")}   this work great in SQL server management studio, it will list all aircraft with the bookvalue for that particulair month.What i want is to get this value for that specific date and use this value as a fact for the AssetFact. how is this possible? i tried to make a calculated member but this didnt work. Im a bit stuck. I dont want to get this bookvalue from the ETL process but from the the cube itself to see how you can reuse dataelements in other areas of ssas

Please Help with Defining Calculated Measure based on Dimension Members

  

Hello,

The issue looks pretty simple yet I got stuck. I want to define the measure [NR Var] as [NR]-[F NR] for years/months/dates before 2009 and [NR]-[FI NR] for 2009 and on. I am using [Year]-[Month]-[Date] hierarchies in the cube. I have defined scope:

Scope ([Measures].[NR Var], {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2006], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2007], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2008], 2, self_before_after)});
this=[NR]-[F NR];
End Scope;

but it does not work for some reason. If I get rid of DESCENDANTS function, it works but applies the scope only to the YEAR level. Another problem with using SCOPE is that it affects [NR Var] only yet I have other calcs derivative of [NR Var] which I want the scope to affect as well.

So I ideally I would like to have something like:

 

CREATE MEMBER CURRENTCUBE.[Measures].[NR Var] as

case when {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_befor

Creation of two calculated members

  

I have set of data for which I would like to create two user defined measure members which could be used when browsing the cube or when reporting using SSRS. I'm a MDX novice and really struggling with how to go about it (coming from a T-SQL background!) so will try to explain my problem based on the Adventure Works Cube. 

The first of the two measures is to get a result back which is the most recent non-empty record of a certain type prior to the currently browsed date.

So using this data from AdventureWorks as a good example:

(Filter: <Internet Sales Order Details.Sales Order Line> = 1)

(Filter: <Sales Reason.Sales Reason> = Manufacturer, Other)

 

Calculated members in dimension are not filterable in reports

  

Hi all,

I have created a time calculation dimension (based on David ) with calculated members. When I work in Management Studio, I can select the dimension called [Date Calculations] and use it in the query. The only thing is, I don't need everytime all the available calculations, so I want to restrict them by selecting a few.

This is what I have done in the query below, but when I want to run this query I get an error:

TITLE: Microsoft SQL Server 2008 Analysis Services
------------------------------

A set has been encountered that cannot contain calculated members.

------------------------------
BUTTONS:

OK
------------------------------
Query:

 

SELECT 
 NON EMPTY { [Measures].[Stock Value], 
       [Measures].[Stockquantity], 
       [Measures].[Nr of products] } ON COLUMNS, 
 NON EMPTY { ([Date].[Year - Month - Date].[Day].ALLMEMBERS * 
        [Date Calculations].[Date Calculations].[Date Calculations].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM 
 

Miscrosoft SQL Server Analysis Service does not show conditions and calculated members

  

Hi,

I am using MS visual studio 2005 to modify a report. The reports has got some filters (conditions) and calculated fields but when I open the report, all it shows me blanks values in condition window screen (where it displays Dimension, hierarchy, Operator, Filter Expression, Parameter fields) and also it does not display any calculated fields in the calculated members widnow. 

I am using SP1. Do I need to re-install the software or am I misisng some tool to turn on.

Thanks in Advance

Naveed


Excel 2010 - Calculated Members on Dimension

  

Hi

Was wondering if there was any workaround in Excel 2010 to make these appear. My understanding is that the behaviour is unchanged from 2007.

Does anyone know of any plans to support calculated members on dimensions in Excel?

Cheers


Named Sets vs. Calculated Members and the Filter Field Conundrum (Year-to-Date measures by company c

  

I’m going to describe our SSAS solution a bit and what I have in it so far in order to ask the question regarding “year-to-date” sales. This is part example and part question, I suppose. "The Filter Field Conundrum" sounds like it should be a high-tech Encyclopedia Brown case or something. Can't seem to find a similar situation through searching, although perhaps I'm wrong and this has been done before.

We have a data warehouse that contains our transactional detail for the last nine or so years. Tables are somewhat analogous to the AdventureWorks solution, with a table for Sales, a table for Customers, Geographic information, etc. The data warehouse has a time table that contains many of the usual bits of information one would expect:

Full Date (mm/dd/yyyy)
Month Number
Month Name
Day of Month
Day of Week
Day of Week Name
Day of Year
Week of Year
and maybe a few other typical fields.

Then we also have:
Working Day (bit, with a 1 indicating that this was a scheduled working day according to our company calendar, and a 0 indicating that it was not)
Working Day of Month
Working Day of Year
(these fields indicate that a day is the nth scheduled working day, according to our company calendar, for a given month or year)

So, for example, 5/1

can set contain calculated members?

  

with
member [Material].[Material Type].[HL_LG] as ([Material].[Material Type].&[HL]+[Material].[Material Type].&[LG])
set [Material Type] as {[Material].[Material Type].&[O],
[Material].[Material Type].[HL_LG],[Material].[Material Type].&[W]}
select
[Date].[Calendar].[All]
on columns,
 non empty
 [Location].[Location].children
*
[Material Type]
*
[Material].[Material].children
*
{[Measures].[Tonnes],
[Measures].[CTDMTL],
[Measures].[Grade]
} on rows
from
 (
 SELECT ([Date].[Calendar].[Date].[2010-09-01]:[Date].[Calendar].[Date].[2010-09-30]) on columns
,[Material].[Material Type].[All].[HL_LG] on rows
 FROM [InSiteCube]
 )
where
[Location].[Location Active].&[-1]

It says A set has been encountered that cannot contain calculated members.
if i remove the 5th row from the bottom (,[Material].[Material Type].[All].[HL_LG] on rows) it works fine.

Any suggestion is appreciated. Please help.

Saikhnaa


Thanks, Saikhnaa

How to slice results of a calculated member by the members of a hierarchy?

  

Dear experts,

I am trying to define a Calculated Member such as:

WITH MEMBER [Sales].[Sales Member] AS
SUM
(
  (
    [Location].[Hierarchy].[Channel].&[Direct],
    [Date].[Date].&[Orbitrary Date]
  ),
  [Measures].[Sales]
)

The problem is that when I am trying to do something like this in MDX:

SELECT 
  [Sales].[Sales Member] ON 0,
  [Location].[Hierarchy].Members ON 1
FROM [Cubix]

  I get a result that looks like a CrossJoin() between the sum of the intersection of the measure on the given Date, and every Member of the Location Hierarchy. Feels like I am missing something very important in terms of interacting with Hierarchies via MDX. How could I go about implementing said Calculated Member? I would also love to hear a basic explanation, of the principle if that's not entirely too much to ask for.

Thank you.


How to slice results of a calculated member by the members of a hierarchy?

  

Dear experts,

I am trying to define a Calculated Member such as:

WITH MEMBER [Sales Member] AS
SUM
(
 (
  [Location].[Hierarchy].[Channel].&[Direct],
  [Date].[Date].&[Orbitrary Date]
 ),
 [Measures].[Sales]
)

The problem is that when I am trying to do something like this in MDX:

SELECT 
 [Sales].[Sales Member] ON 0,
 [Location].[Hierarchy].Members ON 1
FROM [Cubix]

 

I get a result that looks like Sales for a given Date, summed across every Location, CrossJoined() with every Member of the Location Hierarchy. Feels like I am missing something very important in terms of interacting with Hierarchies via MDX. How could I go about implementing said Calculated Member? I would also love to hear a basic explanation, of the principle if that's not entirely too much to ask for.

Thank you.

 

 

 



Cube Calculated members

  

I need to calculate daily variance on a measure between the 2 most recent dates. If I do this as a calculated member in the Cube, can I make it dynamic for dates instead of giving specific dates? 

 

Thanks,

Hitesh



Sorting calculated members in dimension

  

Hello. I've created a calculated member in dimension member.

My dimension member have three values: Item A, Item B and Item D. My calculated member is Item C (Item C = Item A + Item B). When I browsing the cube this calculated member is shown at the bottom of the list.

Is possible to specify a sort order in MDX for a calculated member? I use a SSAS 2008R2.

Thanks in advance.


Adding Dynamic Rows in ASP.NET GridView Control with TextBoxes and with Delete functionality

  
In my previous examples, I have demonstrated on how to add dynamic rows in GridView control with TextBoxes and how to save the values into the database. Now, seems that most of the developers are asking if how to add a delete functionality with it. So in this example, I'm going to show on how to delete a certain row in the dynamic GridView with TextBoxes.

jQuery: Highlight Gridview Rows with Checkbox

  
Im learning and experimenting with jQuery and ASP.NET. Below is a simple solution I created to highlight rows in a gridview by clicking on a checkbox next to the row.

Gridview setup:
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend