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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

MDX to calculate average based on the diemension of Analysis

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server


I have a scenario where the count of dimension values need to be used in the calculation.

For Example:

Lets have 2 dimensions

Dim_SalesGroup and Dim_Product. And, I have one measure as Count of Items Sold as MEAS_CNT.

Now, I need to create a calculted member which should satisfies the follwing calculation

MEAS_CNT / (Count of Dimension members in Axis)

If we slice with Dim_SalesGroup then it should be like

MEAS_CNT / (Count of Dimension members in Dim_SalesGroup)

If we slice with Dim_Product then it should be like

MEAS_CNT / (Count of Dimension members in Dim_Product).


Please suggest me some calculation.


Thanks in advance.



View Complete Post

More Related Resource Links

SQL Analysis Service based Status Indicator

Can you help me on this error while i am trying to create sql analysis service based status indicator.. 1- I created a .odc file connection string from MS Excel 2010. 2- After that I uploaded that file to my sharepoint site data connection list. 3-  After that I Selected SQL Analysis Server Status Indicator & uploded a .odc file from data connection list. After uploading .odc file it shows an Error : Error :- "This data connection does not use encryption for the communication with the SQL Server Analysis Services server. It is recommended to enable encryption for the SQL Server Analysis Services server to help prevent unauthorized users from viewing this information." Sharepoint version :- 2010 Thanks and Regards, Faheem Ahamed

Calculate variance based on property of account

Hi all, I'm having a problem implementing a requirement in SSAS. The case: There is a parent-child dimension Account which contains different accounts on which figures can be booked. This dimension has a unary operator. In this example I have three accounts: "Net interest income" and "Global operation allocations". These two accounts have the same parent "Net result". Besides the account dimension there is a scenario dimension which contains three scenarios: Actual, Budget and Variance. On every account can be indicated whether it is a "Expense Reporting" account. If this is the case the Variance is calculated by "Budget - Actual", otherwise this is calculated by "Actual - Budget". I implemented this by the following calculation script in the cube: -- Calculate Variance scenario SCOPE ([Scenario].[Scenario].[Variance]); SCOPE ([Account].[Expense Reporting].&[True]); THIS = [Scenario].[Scenario].[Budget] - [Scenario].[Scenario].[Actual]; END SCOPE; SCOPE ([Account].[Expense Reporting].&[False]); THIS = [Scenario].[Scenario].[Actual] - [Scenario].[Scenario].[Budget]; END SCOPE; END SCOPE; So far, so good: the variance is calculated correctly for the accounts on which the data is loaded: "Net interest income" and "Global operation allocations". However, on the account

How to calculate a SQL Server performance of a query based upon table schema, table size, and availa

Hi What is the best way to calculate (without actual access to a SQL Server) the processing speed of a query (self-inner-join) based upon table schema, table size, and hardware (CPU, RAM, Drives)? ThanksThanks Jeff in Seattle

Report Model based on Analysis Services Cube doesn't work


Hi all,

I am working in BIDS and would like to create a Report Model based on a cube.

Creating a Data Source based on SSAS goes well, but when I want to make a Data Source View, the earlier created Data Source doesn't show up, which is necessary for the final resulting Report Model.

Could somebody help me out in solving this issue? (And also tell me why this is issue is rising?)


SQL Server 2008 & BIDS 2008 (Version 9.0.3x)

Query Help - calculate weighted average cost from PO Details


I need to calculate a weighted average cost from Purchase Order History.  this is what I want

For each part number and PO, mulliply the qty by the cost to get a total.

Then sum all purchases together, and divide by the total purchased qty.  Pretty straight forward in theory... so if i have this data:


123    10    $1

123    20   $.5

123    15    $ 1.5

My weighted average Calculation would look like this:  W-AVERAGE = ( (10 * 1) + (20 * .5) + (15 * 1.5) ) / (10 + 20 + 15) = .9444

I want the query to return just PARTNUM and W-AVERAGE.  No details.

So, how can I do this with SQL?  I have done simple grouping with Sums and Averages, but as you can see I need a weighted calculation based on purchased quantity.  The straight average ignoring qty is $1, but when you "weight" it, it is $.9444.

Any tips or ideas would be great!


Thanks, Keith

Calculate a Field Based on Approval Status


I have a list that coordinates purchasing approval and tracks our budget.  Someone submited a new item to the list with Estimated Cost, Vendor, Description, etc.  When a new item is submitted, it triggers a workflow that sends it to me as the purchasing approver.  I approve the task and it noties everyone and puts the Approval colum as Approved.  This is all out of the box functioning.  I don't have designer on our network yet (working on it).

What I want to do now is create a column that checks the approval status and if it's approved puts the estimated cost in the column, if it's not approved, it puts 0.  This way I can sum that column to track the expenditures of only approved purchases and ignore the ones I disapproved.  This will allow me to have an accurate picture of how much money we have without having to totally delete a purchase request out of the list.

I've tried using the calulcated field for a new column but it doesn't like having to look at approval status.  I've tried it as both text and integer.  IE "=IF([Approval Status]='Approved',[Estimated Cost],0)" as well as "=IF([Approval Status]=16,[Estimated Cost],0)" as 16 is the number in datasheet view for approval.  Neither of them works as it doesn't like the approval status column use in general.

5 GridView Themes Based on Google's Personalized Homepage (igoogle)

One of the silly features I have found that I like about igoogle is how the pages color palette changes depending upon the time of day. So as the day progresses from morning to afternoon to evening the colors on the page change as well. Like I said, I know this is a silly feature, but I like it. And I have asked around a little bit and seems like other people like it to. To get a better idea of how the colors change, here are the background images from a few of the HTML elements.

Calculate distance, bearing and more between Latitude/Longitude points

This page presents a variety of calculations for latitude/longitude points, with the formulæ and code fragments for implementing them.

All these formulæ are for calculations on the basis of a spherical earth (ignoring ellipsoidal effects) - which is accurate enough* for most purposes. [In fact, the earth is very slightly ellipsoidal; using a spherical model gives errors typically up to 0.3% - see notes for further details].

How to calculate the distance between two points on the Earth

We offer many Global Database Products that you can use with the below formulas to calculate distances and many other uses. Not sure how to use distance calculations or global databases within your company? See our Global Database Examples for more information on how to use our data within your industry. Be sure to Download a Free Sample of one of our many Global Database Products.

dynamically adding sql server columns based on number of files in fileuploader


I want to create a new table to sql database at runtime.  The column names would be "Name", "Date", "Event".  I would also like to have one more column name "Image" but the problem is that since the amount of images will vary, I want the application to count the amount of files in the Multiple fileuploader and then add a image column to the table for the respected amount of files. 

For example if i am uploading 3 files in the multiple fileuploader, the names should be "Name", "Date", "Event", "Image1", "image2", Image3"

I also would like that table name to be name dynamically from the textbox Name when the user enters the information.


I have the following code to make a new table, but I dont know how to name the fields at runtime like previously asked please help!!!


Dim objConn As New SqlConnection("Server=<servername>;uid=<userid>;pwd=<password>;database=master")


Unique content within master page based on conditions met


 Hi all-

I would like to present users with unique content based on certain conditions being met as they land on the home page (default.aspx).  The condition logic will be in the VB code behind Page Load event. The content will go in a content placeholder as specificed from the master page. 

However, the content change wouldn't be something small (ie making a panel or label visible or not) but rather it will be different HTML,  Divs and databound gridview content.  The HTML content will be stored in the database.

Can someone point me in the right direction for best practices on how to accomplish this?


Problem making Role based Menu in MVC application



 I want to make Rolebase menu in MVC such that if user doesnot have permission for some action then that Action name shouldnot be shown in the Menu.

 I have used the code in the url(http://forums.asp.net/t/1566328.aspx) in my MVC application.My application is a Discussion Forum(in MVC) same functionality as in this forum forums.asp.net

 I have used Controllers for post, thread etc.In each controller there are some actions that are using [Authorize(Roles)] attribute

 but this coding does not count  those Actions in Controllers having Authorize attribute according to the url http://forums.asp.net/t/1566328.aspx 

 In the code, Authorize attribute is applied to Controller class, but my requirement is of Applying Authorize attribute to some actions in controller so that some are available for all users and  some links are available rolewise.Now what is the solution for that?



How to get data from table based on four table



I need to know how to show top 30 records from four table

with fastest speed.. in ms sql server 2005..

hope You do the needfull


how to bind Dropdownlist based on textbox values



I have one drodownlist which show data based om textbox values...

Let us consider i have 

Select Name from table1 where Type IN(@typ)

here @typ is textbox values

here for example textbox will have some thing like this L1,L2,L3..

How to filter Name based and shown in drodownlist based on this L1,L2,L3(on textbox)..

Help me in this

services based architecture


was giving a though on using ajax enabled wcf services as part of architecture

generally wht im using

is UI-> BL-> data access


is it suggestable to use wcf services layer instead

of BL

nd hv UI-> services-> data access

related to asp.net


would like to no pro's and con's of doing this thing.

is it suggestable to do it. 

Logic to calculate business hours


Hi All,

I have tried searching all over the web for this logic. Got many but half of them did not match what i was looking for and half were malfunctioning.

I want to calculate business working hours between 2 datetime, where in I should be able to set the working hours as well as weekends and holidays should not be calculated.


Please help me guys... It will be a great help... 

creating a code based on the evaluation


I would like to create a simple program, like a game, but so far without any graphical interface. Only a code, supported with some numbers, so that the user will know whats going on in the background.

I was thinking of creating a racing game (like formula 1 or sometihng similar), with available data (attributes) like:

Driver: age, agility, concentration, experiance, stamina,...

Car: engine, chassis, tyres

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