Olap parameterized report using a calculated member as filter

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

 

Alessandro

seems to be every other imaginable option ...  "NULL" is not accepted, [NULL] returns an error ... this should be obvious!

I've got a report that has only one parameter, it determines the department (or departments) to base the report on, the parameter is set to allow for multiple values. The report returns only on page per department, so the report could be anywhere from one to a hundred pages, one page per department. I've included a text box in the report footer that displays the report name and the parameter value, the expression looks like this: =Globals!ReportName & " For Department " & Parameters!Dept.Value(0)    I know that in this example the zero inside of the parenthesis of the parameter value is what is returning the actual value when the report is rendered, and I understand that the zero is value in the array of parameter values. What I'm looking to do is adapt this expression so that the parameter value rendered on each page corresponds with the data (departement) displayed on the specific page. So using the example of the data, if the user selected three departments in the parameter drop down, then each page that displayed the different department’s information would also display that departments name in the page footer text box. I'm thinking that each time a page is rendered that there is a group or global variable that can be called to provide this dynamically but I can't seem to find the information. Appreciate any help, Dan

Hi I've come across an example where my calculated member will yield wrong results. I've been trying in the last few days to find a solution for this, but so far no luck. I have two measures: [Measures].[Marketing Spending (Actual)]: Aggregation: Sum [Measures].[New Subscriptions (Actual)]: Aggregation: Sum Then I created following calculated member in the schema: [Measures].[CPA (Actual)]=[Measures].[Marketing Spending (Actual)]/[Measures].[New Subscriptions (Actual)] If I run a query like this I get the correct results: SELECT [Measures].[CPA (Actual)] ON 0 from [Global B2C Weekly KPI Cube] WHERE [Date.Weekly Calendar].[2010].[1]:[Date.Weekly Calendar].[2010].[32] I can even add some other dimensions and all looks fine. But, when I create a calculated Member in the MDX query with PeriodsToDate and Aggregate, the results looks like the sum of the CPAs by week. WITH MEMBER [Measures].[x] AS Aggregate(PeriodsToDate([Date.Weekly Calendar].[2010],[Date.Weekly Calendar].[2010].[32]),[Measures].[CPA (Actual)]) SELECT [Measures].[x] ON 0 from [Global B2C Weekly KPI Cube] So my take is that as PeriodsToDate gives back all the members, the calculated measure is calculated for each member and then summed up.  In this case the measures should be summed first for the time period and then the calculation should be performed. I don't want to create extra memb

WITH MEMBER [Promotion].[Campaign].[Program].[All Others] AS [Promotion].[Campaign].[Program].&[Acquisition]+[Promotion].[Campaign].[Program].&[Publications] SELECT [Measures].[Revenue] ON 0, [Promotion].[Campaign].[Program].ALLMEMBERS ON 1 FROM MyCube In the MDX above "Promotion" is the dimension.  "Campaign" is a user hierarchy.  "Program" is a level/attribute. I keep getting: Query (2, 2) The member '[Program]' was not found in the cube when the string, [Promotion].[Campaign].[Program].[All Others], was parsed. What am I doing wrong here?

I have designed a dashboard , some parametrized reports ( on date and time) in my case with a cube as datasource, added an analysis services 2005 filter, connected it to the reports. The issue concerns the default value for the filter . I f I choose [Date Local].[Calendar Hierarchy].[All] it works well. But the business requirement is to use the last day where there have been registered a measurement which in my case looks like the following mdx expression: Tail( Filter ([Date Local].[Date Key].[Date Key].Members , ( ( [Advisor Organisation].[Advisor organisation].[Contact Center].&[Denmark]&[Kundelinjen], [Measures].[Calls Offered] , [Date Local].[DK Holiday].&[False] , [Date Local].[Week Day].&[True] ) > 0 ) ), 1).Item(0).Item(0) The expression is all right as I use it in my report as an mdx filter expression. But when I use apply it I get an invalid dafault member value. So the question is : Does the filter support an mdx expression ? If yes, is there another syntax or change in order to make it work ? Thank you a lot for your answer.

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.

Hi everyone! I'm new to MDX and I can't figure out how to create a working calculated member. Here's business background: There are 2 measures in the cube - a and b. The formula for Total Revenue is a - b - $1000 ( don't ask me why =), 1000 is being subtracted from each day's Total Revenue - a ball park adjustment for something) My calculated member [Measures]. [Total Revenue] looks like this Case     When IsEmpty([Measures].[a]- [Measures].[b])     Then null     Else [Measures].[a]- [Measures].[b]-1000 Unfortunately this calculated member doesn't work correctly, it subtracts $1000 from everywhere and not from the intersection of [Dim Calendar].[HierarchyTime].[Day] and Total Revenue. How do I make it work in accordance with business logic? The objective is to adjust Total Revenue by subtracting $1000 for each day.  Thanks in advance!    

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    

Hi, I am using SSAS 2008 R2 and Excel 2010. Is there a way to override the default behavior from the cube to force an excel pivot table to perform a report action rather than a drillthrough when I double click a cell that is a calculated member? Currently, I get an error message. Truth be told, I wouldn't mind making all the double clicking in my pivot table cells pop a report for the user since the drillthrough formatting is so primitive, but I really need to know how to do it for the calculated cells. Thanks, James

Hi, I have [Sales Amount] and [Date]. How can I create a calculated member that will aggregate only the values from [Sales Amount] where month([Date]) is 'Jan' or 'Feb' and year is CurrentYear ? Then 'Mar' & 'Apr' and so on.... Thank you. D.

There are two filters added to the report filter, Date (8/31/2010) and Product (Product A), from a cube I have.  Everything works just fine.  Once the cube is refreshed each day to include the new data, for some reason, only the selection of Date, 8/31/2010, stays but the selection of Product changes to All Products.  Both dimensions are fully processed but the surrogate keys for existing records do not change.  I checked the MDX captured in Profiler and in the where clause, the date selection is passed from the pivot table but the product selection is lost and all products is passed in.  Any thoughts? TIA. 

I need a NOT LIKE filter in a dataset in ReportBuilder2.0. There is only a LIKE Operator to filter a dataset. The users who worked at the end with the ReportBuilder does not write custom code, iif or something else. Is there an easy way?

Hi there.

I have got the mdx below (titled as MDX In QUERY SsMS) that works fine in SQL Management studio.

I am battling to include it in my Analysis Services project.

Following the logic in the mdx query, I have created the 3 named set and one calculated member (You will see them below), but with no luck.

Please any guidance will do, I desperatly need to include it in my cube

MDX In QUERY SsMS

with

//Get Weeks with sales
set
[WeeksWithSales] as '
{filter(
{[Product].[Product Hierarchy].[SKU DESCR]
*[FinancialDate].[FINANCIAL WEEK].[FINANCIAL WEEK]}
,([Measures].[SLS R]) >0)}
'

//the first week
set
[FirstWeek] as 'HEAD([WeeksWithSales],1)'

set
[LastWeek] as
'
TAIL(
[FinancialDate].[FINANCIAL WEEK].[FINANCIAL WEEK],1
)'
member
[NumWeeks] as 'count([FirstWeek].item(0).item(1):[LastWeek].item(0))'

select
[NumWeeks] on 0
from
 (select [Product].[Product Hierarchy].[SKU DESCR].&[BALL BRIDGESTONE 08 E7 +]&[1041597] on 0
 from (select ([FinancialDate].[Financial Calendar].[FINANCIAL MONTH].&[FY2010_MONTH06].parent.parent) on 0
   from [TPSMerch]))

Hello I am using reort builder 1.0 . I am connecting to oracle database using a report model

I would like to get data from the tables.. the condition in pl/sql looks like this how can we convert it into .. report builder.. functions or should i create query in report model itself and use it in report builder

 

BETWEEN NEXT_DAY(sysdate-14,'SUNDAY') AND NEXT_DAY(sysdate-8,'SATURDAY')  )

 

Thanks in advance..

I am exporting a 12,000 row, 20 column report to Excel from SSRS 2008.  Once opened in Excel, everything is fine.  If I further filter the data through Excel (no external data connections) performance degrades.  If I filter down to say 20 rows, it nearly completely bogs down my entire PC.  I've emailed this spreadsheet to several others, who then experience the same issue.

If I export this same set of data from a different BI tool, such as Microstrategy, there are no performance issues what so ever.

 

Hi,

 

I have a problem with a reporting services 2005 report.

The data source is OLEDB for OLAP 9.
 

The report contains all the members of a parent child dimension. An example of the implementation is defined in the post following the forum msdn:
Http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=138549&SiteID=1.   
 

The report works fine in web. The problem is when the report is exported as Excel, the groups disappeared and the entire dimension is ragged down.

 
Normally toggled groups in reporting services are exported to Excel with the appearance '+' or '-' on the