.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: Percent Average of Average (Calc Member)

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
Not sure this makes sense. What I have is an average payment amount which will be sliced by payer. I want to see what is payer1's percent average of the overall average from all payers. I have a calculated member for average payment amount already: CREATE MEMBER CURRENTCUBE.[Measures].[Average Payment Amount] AS CASE WHEN IsEmpty([Measures].[Count]) OR [Measures].[Count] = 0 THEN NULL ELSE ([Measures].[Payment Amount] / [Measures].[Count]) END, FORMAT_STRING = "currency", NON_EMPTY_BEHAVIOR = {[Payment Amount]}, ASSOCIATED_MEASURE_GROUP = 'Payment', VISIBLE = 1   Any help is appreciated. I was thinking I need to do something with currentmember, but then I couldn't think of how I would have the denominator be all payers.  I dunno, hopefully it's easier than I'm making it.

View Complete Post

More Related Resource Links

For loop to find sum and average

I am trying to write a program that will use the for loop to find the sum, and average of 10 numbers a user enters. I was able to do something like this with out a for loop but I want something a bit more efficient . double num1; double num2; double num3; double num4; double num5; double num6; double num7; double num8; double num9; double num10; Console.Write("Please enter a number: "); num1 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num2 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num3 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num4 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num5 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num6 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num7 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num8 = Convert.ToDouble(Console.ReadLine()); Console.Write("Please enter a number: "); num9 = Convert.ToDouble(Console.ReadLine()); Conso

average of difference between rows

Hello, I need to calculate the difference between two rows and than find the average of those values. For example I have: date Value Value I need (difference between rows) 22.03.2010 50 30 24.01.2010 20 10 10.09.2009 10 NULL ----- 20 (which is the average of this column) Any suggestions? Thanx in advance :)

SSRS 2008 Matrix - Cannot get average formula or growth formula to work

I have a report in Excel that I'm trying to replicate in SSRS 2008.  The report looks like this:   Priority 08/21/10 08/14/10 08/07/10 07/31/10 07/24/10 07/17/10 07/10/10 TOTAL Average Customer Name Code # of Lds # of Lds # of Lds # of Lds # of Lds # of Lds # of Lds  # of Lds   Check  #N/A            4            4            4            8            4            9           -            33          5       83%  A2             1          -            -            -            -            -           

Calc'd Member Specifically for JAN, FEB etc of Chosen Tran Date

Hi.  I'd like to display same year calc'd measures [January Completions], [February Completions] ...[December Completions] next to the [Actual Completions] measure for transaction year x, transaction month y. If y is January then [January Completions] thru [December Completions] need to be overridden with zeros.  If y is February, Feb thru Dec need to be overridden with zeros and so on. I started experimenting with the query you see below before transferring the logic to my cube.  I didnt even get to the if/then logic before I saw that April and beyond of each year's [January Completions] was null.  But the correct/same Jan amount showed next to the 1st 3 [Actual Completions] for 2009 and 2010 which are the only years I've loaded at this point. [Actual Completions] itself is a calc'd measure from two different measure groups that share most dims including transaction date.  I dragged the hierarchical month name to the "on rows" clause.  I had no luck mixing hierarchical names with the currentmember function in the "with" clause so I dragged the non hierarchical tran date names there.  What am I doing wrong? with member [Overall Jan Completions This Year] as ([Measures].[Actual Completions], [Date Transaction].[Year].   Currentmember,   select {([Measures].[Actual Com

Average number of products by customer in segment

Hi again, I have performance issue with one query, and hope that you will help me as always. I am getting exact result, but with poor performance (more than 20 minutes) with member   [Measures].[SumProductsByCustomer] as Avg ( existing {[Customer].[Customer_ID]. Children},     Filter ( ( {[Product_Arrangement_BasedOnProductId].[Product]. Children}     --, ), [Measures].[AvgArrangementCount]>0 ) )   Select   NON EMPTY [Measures].[SumProductsByCustomer]   on 0, [MarketSegment_Customer_MarketSegmentId].[MarketSegmentName]   on 1 from   [CPM] What I want is to calculate average number of different products by customers and than show avg number of different products by customer in every segment.   Thank you very much for any idea.    

T-SQL query, average of daily time periods over a date range



I'm building a report in Crystal Reports using a SQL command against a T-SQL 2005 telephony database.

I need to be able to run the query across a given datetime range, 6 months for example and bring back a 5 day display (Mon-Fri) with a group for every 15 minute interval in each day.

The group figures need to contain an average of the amount of calls presented for each 15 minute interval on any day across the whole datetime range, so for example the Monday 10:00 - 10:15 figure would be an average of all calls presented in every 10:00-10:15 range on each of the Mondays that fall within the datetime range.

I've got a query built now that gives total presented figures grouped by these intervals across one week but I can't figure out how to do this average function across a range.

Does anyone have any idea how I'd go about accomplishing this? I'm pretty new to SQL but keen to learn so any pointers on functions to research etc would be very much appreciated.

Thanks alot in advance, Andy.

Query for detail view across one week included below for table/fields etc...



count(DISTINCT Calls.SessionID) as Presented, min(Calls.startDateTime) as DateTime

INNER JOIN QueueDetail
ON Calls.sessionID =  Queues.sessionID
AND Calls.sessionSeqNum =  QueueDet

Percent of Total for Each Member in a Dimension Across Another Dimension


I've got a particularly nasty problem that I've been pounding my head against that I hope someone has encountered and can help me with.  I have a hospital cube that has a Hospital dimension and a Service Line Dimension (among others).  Service Lines are such things like General Medical, Surgical, Cardiology (there are 16 total).  Each member hospital in the Hospital dimension will have Discharges associated with each Service Line.  I'm trying to construct a percent of total calculation for each Hospital and Service Line, so I can look at the percent of total of each Service Line across Hospitals.  I'm trying to create a report in SSRS based off this cube.  Here's what the dimensions look like:

Hospital Name1    General Medical
Hospital Name2    General Medical
Hospital Name3    General Medical

What I want to do is calculate the percent of total of each hospital's Service Lines, using the total for each Service Line across hospitals.  This will show the contribution of a Service Line at a specific Hospital to the overall total for that Service Line for all Hospitals.  Here's the MDX I've been working with that does not work the way I want:

Get Average from interval(Time) by hour


You Got to love management,

I've been averaging for the last 12 records with this code:

SELECT AVG(LMP) AS Average FROM (SELECT TOP (12) LMP FROM dbo.LMP_5Min order by id desc)

I need to get the average for just the current hours data that comes in every 5 minutes.  So there will be 12 records by the end of the hour.  However I need to average each increment as it comes in.  So after 1, 2, 3...... increments come in each would have to be averaged.

My tables goes as follows:


ID                  int, not null

Day         &n

Average delay between 2 sales (AdventureWorks)



In the Adventurework FactInterSales table, there's an "orderDateKey" key. 

Imagine that I want to add a new calculated measure, in order to be able to get the average delay (in days) between sales, in general or by customer...

How would you do that ? A new calculated member ? a script command ? Or do I have to do this with SSIS when importing the data ? I hope no...

I've been searching on the net, the DATEDIFF function seams to be the solution, but I couldn't make it work successfully

Thanks a lot for your help


Cumulative Average in SSRS sub-total



There is a requirement to calculate the cumulative average on the sub-total in an SSRS report which sources data from the cube.

The report, which is a matrix, has Product Group and Brand Family on the X-axis, Days of week on the Y-axis and Volume in he data section.

Data in the report is displayed as below:

When viewed on Monday the output is as below-


Product Group Brand Family Mon Tue Wed Thu Fri
PG1 BF1 10,960      

Get Average by hour but but not by top of hour


I got this code with the help of K H Tan:

declare @now datetime

select @now = dateadd(hh,0,getdate())

; with cte as

 (select LMP,

  tm = convert(datetime, left(interval, 19))

 from LMP_5Min


select avg(LMP)

from cte

where tm >= dateadd(hour, datediff(Hour, 0, @now), 0)

and tm < dateadd(hour, datediff(hour, 0, @now) + 1, 0)

My only problem is that it grabs the data at the top of the hour if it is 8:00 P.M. it starts at 8:00 and goes to the last 5 minute increment at 8:55. 

How to add another column to sum/average/count/or any possible calculations to those pivoted columns


Hi All,

I am currently using this Dynamic Pivot Table Queries from this link:


I manage to get the result i required but i need to add further column to it. I have a table such as this.

Name  |   Item   |   Points

Eddy       A             3

Eddy       B             4

John       A             2


I could get the result such as this from the above link after pivot.

Name  |  A   |    B

Eddy      3         4

John       2         null

How do i make it to become like this? Adding a column average so the final result will look like this.

Name | Average |  A  |  B

Eddy     3.5           3      4

John        1          

Group by department with a select top 1 and average rating!?




I'm trying to create a report that can use the same data set to group by a particular field - e.g. Department or Job title, and then output by each the most commonly occurring piece of data for the selected group.  Plus I want an average as well.

So for example I have:

Job Title, Department, Chosen Factor, Rating

Programmer, IT, 'factor x', 7

Programmer, IT ' factor x', 8

Sales Manager, Sale, factor z, 9


From the one dataset I want to create two tablixs one that reports:

Job Title, Most common selected factor, avg rating

Programmer, 'factor x', 7

Sales Manager, 'factor z, 8


And then another tablix that shows

Department, Most common selected factor, avg rating

IT, 'factor x', 7

Sale, 'factor z, 8


Now depending on whether I'm grouping on Department or Job title the number of any particluar factor will change, as will the average rating.


Can I do this?  Or do I need a seperate dataset for each table?


Thanks in advance

How to FORMAT a AVERAGE number


How can i write a similar query like this in TSQL?


this was written in SSRS

Average Column in fact table - how to aggregate



I have a column in my fact table called Average Handling Time, here I keep an average value - this is at a leaf level.

What should I set the aggregate to in my cube. For example, when I aggregate this to team level I don't want the sum but the average.

I did try the AverageOfChildren but didn't get the right value - see below



User Average  Handling Time
a 400
b 1011
c 602
d 298
e 393

Calculation of average sales


Hi everybody,


I am currently developing reports with Report Builder 2.0 and got stuck with the following:


In order to calculate an average sales amount, I did the following:


Dataset 1:


sql average function giving zero output



WHAT sould i edit imn my QUERY to get below required output..I TRIED SO MANY OPTION BUT IT DIDNT WORK..PLEASE HELP

i have table name "report" which has 4 column 3 are time T1,T2,T3 AND FOURTH IS MONTH

T1  T2  T3  MONTH

2   2    1         1

3   2    2         1

2   1   6          2

3    4   6         2

1    2   1         2



.31               1

.68               2

if i use query select sum(t1+t2+t3)as sum,month(reportdate) from report group by month(reportdate)

i get the output

sum month

12      1

26      2

but if i use query"select  avg(t1+t2+t3) as average ,month(reportdate) as month from r

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