.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

SSAS Calculated Member not working as expected in a SSRS report

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

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



View Complete Post

More Related Resource Links

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

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    

Conditional formatting not working as expected in SSRS report


I am trying to display conditional fill on a report in SSRS on SQL Server 2008. Looking at Fields!MS_Communicator.Value, desired fill to be SeaGreen if version build is >= 83, Tomato if < or [Not Installed]. Query provides either [Not Installed] or the version number (i.e. 3.5.6907.83).

=(IIF((Split(Fields!MS_Communicator.Value, ".")).GetValue(3) >= 83, "SeaGreen", "Tomato")) works as expected, leaving cells with [Not Installed] transparent.

=IIF(Fields!MS_Communicator.Value = "[Not Installed]", "Tomato", "Transparent") works as expected, leaving cells with version transparent.

My problem:

=IIF(Fields!MS_Communicator.Value = "[Not Installed]", "Tomato", (IIF((Split(Fields!MS_Communicator.Value, ".")).GetValue(3) >= 83, "SeaGreen", "Tomato"))) works if there is a version number, but leaves cells with [Not Installed] transparent.



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 ?  

create a calculated member in a cube in SSAS

Hello   I’m trying to create a calculated member in a cube in SSAS and was hoping someone could help.   What I’m looking to do is count the number of occurrences of a value in a field.  I have Status field that contains New, Renewals and Enquiries and another field that contains a count, the problem I have and the reason for this query is the count is set to 0 for Enquiries.   The tSQL equivalent would be something like...   Select Date, Status , count(Status) From aTable Where Status = ‘ENQ’   And to summarise, what I’m looking to do would look something like the below   Date Status Count Commission Apr-10 NEW 1 100 May-10 NEW 1 100 May-10 REN 1 50 Apr-10 ENQ 0 0 May-10 ENQ 0 0 Jun-10 REN 1 50 May-10 NEW 1 100 Just looking at the ENQ by month. Date Count of ENQ     Apr-10 1     May-10 1     Jun-10 0       Any help gratefully received.

Olap parameterized report using a calculated member as filter

I'm in the following situation : olap parameterized report - using an Analysis Services data provider  - with 4 filters and parameters.   One of the filters - Time dimension and one of its hierarchies - includes 2 calculated members called:  Primary and Secondary, which basically describe a time interval of a day based on some business rules. An Excel pivot tabel retrieves data without any problems. But a SSRS 2008 matrix report fails when picking up one of the calculated members , as it does not support it, just like olap browseren in AS 2008 does not support filtering by any calculated member. So it's difficult to find a good explanations to the users ... I'm not quite sure that using the ole db provider for analysis services in stead of analysis services will solve my problem. That's why following question : Are you absolutely sure that using the ole db provider for analysis services will solve my issue ? If not, is it any other work around ? If yes, are there any tips and tricks of using / typing within the ole db provider for analysis services editor when using parameters. ( I tried it once without any succes and found it quite complicated when it comes to handling expressions with parameters). Thank a lot for your answer. Best regards, Mihai   

property setting in report server (2008) config file is not working as expected.



I have set the <WorkingSetMinimum> property to 2.4GB in my reportserver 2008 config file.

By defintion, <WorkingSetMinimum> setting means,

  1. Lower limit of service memory consumption; memory will not be released if the consumption is below this amount, though the service will not reserve the designated amount of memory when it starts.
  2. If service uses more than the designated amout of memory, it will release back the memory only till this declared value and not beyond that, which means it should keep 2.4GB as designated.

See this link for definition: http://books.google.com/books?id=mzY_Xo_9J-gC&pg=PA589&lpg=PA589&dq=What+is+%3Cworkingsetminimum%3E+in+report+server+configuration+file&source=bl&ots=r_xhRp8erj&sig=E_o0-Dh2SXOgMNrW3sptt5oZrYI&hl=en&ei=f5mjTNeNM5O8ccmErJMI&sa=X&oi=book_result&ct=result&resnum=4&ved=0CCAQ6AEwAw#

SSRS 2000 report not able to show any data in ie6 and ie7 although found working in ie8 and firefox


we have a SSRS report which was working till few weeks backs on all the ie and started to show Diagnose the Internet connection error on ie6 and permission denied on ie7. although the report is running fine on ie8 and firefox with same security settings. There has been no recent changes done to the report. tried it through fiddler but gives a 200 status for http. IS also connecting to URL and is making connect to the DB. HAve tried to check the script and activex settings also and found nothing absurd which can stop the functionality.I have tried reducing the security to low also but with success. Can someone please help.

SSRS working fine in Report builder and manager but not in Report viewer


HI I have a report that worked fine until we upgraded the SQL server from 2008 to 2008 R2 and the report viewer to latest version as well.
This report has a pie display, NOW instead of showing one pie, it is showing 20 pies!

I am trrying to learn how to resolve and what is causing this. 

Order by not working in SSRS report


Hi All,

I have created a report that sharing  two datasets  for displaying the data. This datasets are using Stored procedure for execution of the report.

One of the stored procedure using Order by clause and returning the data.


But on running the report , the report viewer displays the unsorted data of the filed. But if we run the stored procedure directly from the sql server , it will return the sorted data.


Could you please help me to resolve the issue?


Thanks in advance




SSRS report show all member


I have a problem in displaying the all the member in the ssrs report. So what i am trying to do is i want to see the products sold city wise. But there might be cities where no products are sold. Below is the query that is used.

SELECT ([Measures].[value]) ON COLUMNS , 
NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[Dim Location].[city].[All].children})}),

 I dont see cities where no products were sold in the report. Please advise. 

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. 

SSRS 2008 R2 and sharepoint 2007 report builder problems


I upgraded our enterprise sql server 2008 to sql server 2008 r2 last weekend.  Along with this i installed the report builder 3.0 on the servers as well and the updated ssrs add in for sharepoint.  The upgrade went fine, no errors that i could find.  The problem is now when i go to a library that has the report builder content type enabled and click report builder report under new it does not load report builder 3.0.

I updated the custom report builder url in central administration from /_vti_bin/ReportBuilder/ReportBuilder_2_0_0_0.application to /_vti_bin/ReportBuilder/ReportBuilder_3_0_0_0.application

however when i do click the new report builder report it kicks me over to http://company.com/ReportServer_MossEnt  which itemizes out each individual sharepoint site in the farm.

Is the integration of ssrs 2008 r2, sharepoint 2007 and report builder 3.0 work?  did i install the wrong version of the report builder add in?  The above is the only problem i am having now.

all servers are server 2008, 64 bit and moss 2007 enterprise with sp2 & april 2010 cu

SSRS Report viewer + Remove


I am running the report from SSRS report server in the view i am finding that the window title is Report Viewer - Windows Internet Explorer.

How to remove this name and get the name of the report over there.

I am using java at the back end to call the repot through url.

Please help me .

Thanks ,


Report Builder in sharepoint is not working.



We are using below Topology in production
Topology is 3 servers:
Sever1: Sharepoint Form (wss3.0 no MOSS)
Sever2: Sql server 2008
Server3: Reporting Services 2008
Operating system on 3 servers: Windows server 2008.
Authentication mode: Form based authentication.

 SSRS reports are working success fully in SharePoint using above topology, The issue is through SharePoint library when i am trying to access report builder reports the client is installing, after enter the credentials it is not connecting to server and am getting the following error "Unable to connecting server at 'http://servername/Reports/Forms/AllItems.aspx'  please use different url

The above issue on production only.

But in local development box there is no problem (in my development box SharePoint t, database (sql) and reporting service sits on my box only)

Thanks for your he

SSRS - Where are the Sharepoint Report Usage Reports provided by Microsoft



Microsoft provided 3 example reports for Sharepoint report usage. Does anyone know where they are located? I used them about 2 years ago.




Calculated Columns in a Form Library receive error: Value does not fall within the expected range.


I have a forms library that posts the date the form was created. We would like to create a calculated column that allows us to look at these forms based on the month and year they were created. I had created a calculated column that extracts the month and replaces the number with a text string by using an if statement; "IF(MONTH([Proposal Date])=1,"January","") It goes on to evaluate each month and replace the number with the text string. This formula worked for quite some time, though now when I try to add any calculated columns or edit any existing calculated columns on this form library I receive the following error: Value does not fall within the expected range. Now my existing calculated columns (like the one above) do not work and I can not create new calcualted columns. Any insight would be awesome as this has been plaguing me for a couple of months now.

Textwrapping with buttons not working as expected

Guys and Gals, Got a bit of a problem with my application. I am trying to get the text and buttons in a groupbox to wrap when the page size changes (i.e. gets too small to fit it all on the page.) I have inserted the TextWrapping="Wrap" into the TextBlock line but it refuses to wrap. I have tried everything I can think of for this. Am I doing something wrong or is it impossible to do? If someone could point me in the correct direction it would be greatly appreciated. I don't want to use a scroll viewer either. <Window x:Class="Test.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="MainWindow" Width="Auto"> <StackPanel> <TextBlock >Load Data Mappings From File</TextBlock> <TextBlock TextWrapping="Wrap"> This task will truncate all existing data Maps and re-import from an excel spread sheet.<LineBreak/> </TextBlock> <GroupBox Header="File to Load" Name="groupBox1" Padding="10" Margin="10"> <StackPanel> <StackPanel Orientation="Horizontal"> <TextBlock DockPanel.Dock="Left" VerticalAlignment="Center" Width=&qu
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