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

Top 5 Contributors of the Month
Sandeep Singh
Melody Anderson
Eminent IT
Post New Web Links

SSAS - calculation of ssrs report should be calculated before calculation in MDX script

Posted By:      Posted Date: September 13, 2010    Points: 0   Category :Sql Server
Hi All I have: - A calculated member defined in an mdx query of an SSRS report (using the aggregate function). - A calculated member defined in the MDX Script of the cube The calculated member of the cube calculates a ratio. What we should achieve is the ratio of the aggregated members (defined in the report) and not the aggregation of the ratios. I thought that it would be automatically enforced by: http://msdn.microsoft.com/en-us/library/ms145539.aspx :"The exception to this precedence is the Aggregate function. Calculated members with the Aggregate function have a lower solve order than any intersecting calculated measure." I tried to play with the solve_order of the two calculated members (defining explicitly a lower solve_order for the aggregation defined in the report), but the calculation of the cubes is always executed first => I end up with the aggregation of the ratios. Are calculations defined in the MDX script always executed first? This doesn't seem logical to me.. Thanks a lot for your help. Regards Stefan    

View Complete Post

More Related Resource Links

SSAS Calculated Member not working as expected in a SSRS report


Dear forum guys, I have a simple cube to analyze my customer's sales. I have a Time Dimension, a Product Dimension (with a few hierarchys) and a couple measures (sales and purchase amounts).


I also have defined a few Calculated Members, in particular the average sales and purchase amount over the last 12 months from the selected month.


I need to show the results in a SSRS Report: the user must select a month and the report needs to show the sales and purchase amount of the selected month and the average of the previous 12 months (both for sales and purchases).


Everything works perfectly if I test the cube using the Cube Browser or Excel 2007 as a client.


When I define the SSRS Report I cannot get to work with the calculated members (the average): I get strange numbers which are much higher than the real numbers.


I've tried both Matrix and Table reports.


Can anyone suggest me what I'm doing wrong: I think this should be a very common mistake.


Best regards



Yield to Maturity Calculation in SSRS Report



We are migrating a report from Crytal to SSRS 2008. It has a Yield function(Calculates Yield to maturity). Is there any equivalent in SSRS 2008. or alternatively is there any way to calculate in SSRS. Even Microsoft Excel has this Yield Function.

Any help is greatly appreciated.

ssrs 2008 R2 - report parameter with no default set throws java script error

We have recently upgraded from SQL Server 2008 to SQL Server 2008 R2....I am experiencing a problem with my report parameters on my SSRS reports. It seems that my report parameters that I do not have a specified 'default' value throws a java script error when I try and run/render the report under R2.  As soon as I put a 'default' value on the report param, everything is fine.  However, that is not the behavior we need for our reports...the user needs to pick their selection. Can anyone shed some light on this, it seems to be an AJAX problem...I need to be able to have report parameters with a list of values, but no 'default' value on the parameter.

Enhance SSAS so that a calculation returns multiple cells at once (Excel fill not a single cell but

I wonder if somebody can help me with the following question. I would like to create an enhancement to SSAS in a way so that one can pass in multiple values, run a calculation and in return retrieves not one but multiple cell results at the same time e.g. retSet = functionABC(inSET) where inSet is somthing like 5 4 3 6 7 3 4 4 AND retSet is 4 6 7 8 9 3 4 4 All my calculated members in MDX return a single value BUT I need to have a calculation that if called returns multiple results (a set) at once and in a way so that Excel will properly pick up on it and consequently fills multiple cells in the Pivot table. Is that possible at all and if so how? I would not mind to write a little CLR assembly to enhance the SSAS but don't know how to pass the results back. Any ideas are highly appreciated. Dirk

SSRS 2008 Sum Calculation in text box

Hi,  Any one pls tell me how to calculate sum of text box values in SSRS 2008. (sum(Fields!Total_Days.Value, "DS_Monthday") - sum(Fields!Weekends.Value,  "DS_Weekends")) - (datediff("d",DATEADD("d",-(DAY(Today())-1),Today()),Today()))   * (Count(Fields!ClientID.Value, "DS_AVGRate")/Count(Fields!DimGPClientID.Value)) +  count(Fields!ClientID.Value, "DS_AVGRate"),Count(Fields!DimGPClientID.Value))   i want to calculate sum of the above expression in text box. I have tried it is showingerror  Aggregate function will not support in text box Pls help me to show the whole sum of above expression in text box only. Thanks in advance. Regards, Abdul2010

SSRS Report based on SSAS Cubes doesn't show NULL values correctly

I have a SSRS report which is based on an SSAS Cube. In the Cube the formating of numeric fields that NULL values are properly shown as NULL values. In the Cube Browser and Excel this is shown correctly. However when I design the report already the query against SSAS shows these fields as "0" instead of NULL. Is there a way to fix this or is this a bug ?  

Some functions are missing in SSAS calculation tool for my sql 2008 R2 installation (DAX functions?)

In relation to this post: http://connect.microsoft.com/SQLServer/feedback/details/569767/some-functions-e-g-timedate-are-blank-under-the-calculation-toolbar-others-are-ok  I understand that the AS functions are loaded from a XML schema, could a quick solution from Microsoft be to send an updated xml file? When I expand the "All" group under Functions the first 120 MDX (or DAX) functions are blank. I understand that this only a SQL Server 2008 R2 problem and that it's perhaps the DAX functions that shows up blank. // Bjorn

SSRS Chart calculation displaying future values


I'm new to SSRS and especially to the chart features.  I currently have a line graph chart where the data points are based on a % calculation for each month of a year.  The prior years are all displaying correctly. 

The current year however shows the correct values to the present date, but rather than stopping it continues the line to the end of the chart showing the most recent value.

What can I change so that it will stop the line at the most recent data point?

The calcuation being used is "=1-(Fields!ID2009_Backordered_Lines.Value)/Sum(Fields!ID2009_Fillable_Lines.Value)"

Any insight would be appreciated!







Call Statement in SSAS calculation


Hi Experts,

 Any one pls tel me how to use the following statement in SSAS calculations

call SQLQuery.ExecuteSql(
    "Provider=SQLNCLI10.1;Data Source=TMDEVDWHSRV01;Integrated Security=SSPI;Initial Catalog=BankingDWHStg"
    ,"Exec DecryptPANKey 1")


While i use the above statement in SSAS calculated member it is showing error as

"MdxScript(Dax Data Mart) (8, 5) Parser: The syntax for 'call' is incorrect."

Or tell me how to use the above statement in SSAS Calculations or in Named query

Pls help me to solve the issue.




SSRS calculation problems


Hello Experts,
I have a SSRS reports that displays weekly sales and Store inventory levels. In this report, I have used a calculated member named "Branch Stock Value". And its expression is:

Fields!Store_Stock_Level.Value *(Fields!PS.Value - ((Fields!PS.Value * Fields!VAT_Rate.Value)/100))

Now, I would like to use another calculated member that uses this BranchStockValue member. My desires calculated member expression is:

BranchStockValue / SalesValue

I get divide by zero or other null errors. But the final purposed result is really very easy.

For example for the first line Branch Week Cover value should be 12544.36 / 1656.39 =  7.57


Please Help

SUM(Root()) in SSAS calculation



Am trying to add a calculation member inside the Cube (BIDS) but am not getting the results I need...
I need to show the percentage of one measure depending on the dimension. So, the total is 100% and then inside each row has to show less than 100%, split it by dimension attributes.

What I have right now is:
[Measures].[Call Volume] / Sum(Root(Measures), [Measures].[Call Volume] )

But is not showing anything....

I will appreciate all the information you can give me...




SSAS 2008 - Occupancy Calculation Cube?


Hi there,

Previously I posted my probelm into T-SQL forum section. Here is link http://social.msdn.microsoft.com/Forums/en/transactsql/thread/b02d37b4-d36e-42cc-b1f4-3c00abadfdd4

I have SQL 2005 table like

Room      AdmissionDate    DischargeDate
R1             2010-01-25           2010-01-28
R1             2010-01-28           2010-01-30
R1             2010-02-15           2010-02-17
R1             2001-02-20           NULL         
R2             2010-01-31           2010-01-31
R2             2010-02-10 

range inclusive ssrs report builder using SSAS as source unsolved



I am trying to use MDX query designer in SSRS report builder. I choose a date dimension and i need to include a range for it . So i selected range inclusive as the filter.

When i do that and give default dates it works fine. When i select parameters it and hit ok. it immediately throws a error.

"Query (1, 216) The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated."

So i went ahead changed it to scripting mode and removed the "constrainted" flag and ran it . but then a different error comes up


then i get this error.

"Query (1, 216) The STRTOMEMBER function expects a member expression for the 1 argument. A tuple set expression was used."

what do i need to do to make this compile correctly. Please suggest. I am working on this for 2 weeks. 

Rollup calculated measure at aggregate measure and only apply calculation at leaf



I have the following calculated measure: MeasureX = MeasureA * MeasureB.


I only want the calculation to apply at the leaf level, then the answer should rollup automatically to higher levels.


So instead of Sum(MeasureA) + sum(MeasureB). I need sum(MeasureX)


The solutions I've found for this issue, refer to the relational engine to solve the problem: etl/dsv/calc column.

But I need it to be solved in the cube.




MCITP BI Developer 2008 - MCTS SQL Server 2005


How to calculate childs age,but when i enter childs age as '03/27/2007' it returns 3 years where as actual age is 2 yr 11 months

calculation, field and map traverse adjustment, and coordinate transformation

Free Pocket PC land surveying software -- COGO calculation, field and map traverse adjustment, and coordinate transformation -- for students and professionals.

Excel Services: Develop A Calculation Engine For Your Apps


The Excel Services architecture lets users design their own algorithms and share workbooks on a server.

Vishwas Lele and Pyush Kumar

MSDN Magazine August 2007

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