Post New Web Links

How do I create a calculated member for daily total revenue?

Posted By: Posted Date: September 06, 2010 Points: 0 Category :Sql Server

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!

View Complete Post

More Related Resource Links

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.

calculated member grand total e subtotal problem

hi,

I have a cube where I added a calculated member to extract the last non null value of a measure. This is the script

Create Member CurrentCube.[Measures].[Trascinamento] as

iif(isempty([measures].[importo]),

([Data Rif].[Calendario].prevmember,[Measures].[Trascinamento]),

[measures].[importo]),

FORMAT_STRING = "Currency", VISIBLE = 1 ,

 ASSOCIATED_MEASURE_GROUP = 'PUMA';

when viewing the aggregate data in a hierarchy, calculated totals and subtotals are accurate only if all the children of a member are zero at a certain date, and if either has a value, the original total is not zero, and thus is also used in calculated member.

hierarchy | importo | trascinamento

100-----10| null | 10000
----20 | null &nb

HELP!!!!! How to create a calculated member like this??

HI ALL:

Example:

Region and Year are dimensions. Count is a measure value. I want to create a calculated member as Historical.

Region Year Month Count Historical

AMS 2010 1 4 4

AMS 2010 2 3 7 (4+3)

AMS 2010 &nb

Can I create several steps to be run on a SharePoint list as a task to run on a daily basis?

We have a manual process that we do on a SharePoint list to export data from SharePoint to a spreadsheet that can then be used by non SharePoint programs. I'd like to be able to automate this process so that it can be scheduled as a Task to run on a daily basis. Not sure if a workflow can do what I'd like it to. I have SharePoint Designer 2007 as well.

Here are the steps:

1. Load a particular View of a SharePoint List

2. From the view's Action menu, "Open with Access"

3. Save the resulting Access database as a file to a specific Folder on the computer hosting SharePoint

This would be run as a Task on the computer hosting SharePoint.

PeriodsToDate problem with calculated member

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

Cannot create user: server cannot determine if it is member of a domain

Hi, An instance of our 2008 SP1 SQL Server is running on a computer that was removed from it's domain and then re-added. Now, when I want to create a new login in Management Studio via the nodes Security/Logins, selecting "New Login", entering DOMAIN_NAME\USER_NAME as login name and then click "Search", I get an error message (in German) "Das Programm kann das erforderliche Dialogfeld nicht öffnen, da nicht ermittelt werden kann, ob Computer "COMPUTER_NAME" einer Domäne zugeordnet ist. Schließen Sie dieses Dialogfeld, und wiederholen Sie den Vorgang". Translation: "The program cannot open the required dialog, as it cannot be determined if computer "COMPUTER_NAME" is assigned to a domain. Close this dialog, and repeat the process." But closing the dialog and repeating as suggested does not help. Even restarting the SQL Server service and re-connecting in Management Studio did not help. I keep getting this message. I found, however, that just entering the domain and user name and then clicking Ok seems to add the user. Anyway, it is irritating to get this error message when clicking "Search". How can I tell the engine that the computer is really member of the domain again? Frank

How to create a calcuated member, which will always return the date one month before the currentmemb

I need to create a calculated member in the cube script, which will be subsequently used in various other script calculations. It needs to return the date, which is 1 month before the currentmember of the Date dimension. I tried something like this: CREATE MEMBER CURRENTCUBE.[Measures].[LastPD] AS PARALLELPERIOD([Date].[Calendar].[Month], 1, [Date].[Calendar].currentMember) ,VISIBLE = 1; However when I then try to check the values with this query: SELECT [Measures].[LastPD] on 0, [Date].[Calendar].[Date].members on 1 FROM [TravCSAT] ; I only get NULLs for [LastPD]. Any idea what I am doing wrong?

Trouble With Calculated Member

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?

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

MDX Date Range calculated member

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.

how to customize asp.net membership roles to create separate member area ?

I am using asp.net membership for all the users and roles, right now i have a website on which i have created 2 roles "admin" and "member"

in this website i create accounts, so all the accounts are separated/distinguished by account name

now i was to add one more role "accountholders" and i want to add this role in such a way that an account holder will be able to see his / her account's stuff ...

i as admin will be creating username and password for accountholders and i will provide them their login credentials so that they can login and see what's in their account.

My idea of creating account is that, the accounts that i have in my database will be the key, so when i will create username for particular account , in the create user wizard i should have a drop downmenu will all the accounts name. so the user that i am creating will be linked on that account only ..

how to accoplish this ? please guide me in a right direction...

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
           Surgical
           Cardiology
Hospital Name2    General Medical
           Surgical
           Cardiology
Hospital Name3    General Medical
           Surgical
           Cardiology

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:

Named Set And Calculated Member within Analysis Services Cube project

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]))

how to create class and acess member using object

hi i am a new user to C# .net can any body help me to write a new class in c# AND HOW TO ACESS methods and members using its object thnks in advance

How to create a daily attendance register.

i'm trying to design an attendance register for pupils in a class for a term (3 months). I wanted to create columns for each day throughout the 3 months in the students table of the database but i feel this is too ambigous. The register will be marked either 'presence' or 'absence' everyday by the class teacher. Going by my own idea, there will be a gridview with 3 columns (ID, Name & Current date). The current date will have checkbox to mark presence or absence.

The end result is to get the total days a pupil was present in school for the term, i think there is a better way to do this. I'll be pleased if anyone can help me out, i'm using this as my final year project.....THANKS

disable the sub/grand total column in calculated memebers

Is it possible to disable the sub/grand total column in calculated memebers programmatically. I don't want to display the sub/grand totals in pivot table? I know I can manually disable the sub/ grand totals in pivot table.

Alex,

How to force a calculated member to sum from the leave level

Hi there:

Below is a snap short of my data set and hierarchy

Brand	Product category	Product Name	Units Sale	Product With Sale


Categories:

ASP.Net	Windows Application	.NET Framework	C#	VB.Net	ADO.Net
Sql Server	SharePoint	Silverlight	Others	All