.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

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

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
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?

View Complete Post

More Related Resource Links

Crawler fails to register date properties of user profiles with the month of January, April, August


This seems to be a bug when the crawler search the user profiles in MOSS 2007.  When crawled, user profiles with a SPS-HireDate in the months of January, April, August and December will be detected, but a full-text (SQL) search returns those profiles without the HireDate field.

User profiles with HireDates in other months work correctly, returning the HireDate in the search.  And changing the month of a problematic user profile also fixes the problem.

This problem is also reflected in the fact that while we have 499 user profiles using the SPS-HireDate property,  the managed property page from the search section only has 350 items with the HireDate property.

We're running MOSS 2007 32bit with SP2 with an English language base and the Spanish language pack. I'd considered date format problems, but I can't imagine how some months would work, while others wouldn't.

Any ideas?

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

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.

Date format for Year, Year-Month, and Date (Year-Month-Day) where input is not known

I have a requirement to capture historical events in a table. Now some events will have only Year, some Year/Month, and remaining where Date is known. We should be able to store them in the column and be able to sort etc. When publishing the information, we should be able to publish as was the input, Year only, Year-Month only, or Date. I looked at the newer Date data type. I can insert a 4 digit year, but on retrieving it is YYYY-01-01. I do not know if it has input YYYY or YYYY-MM-DD and is indeed a Date or a Year. I am trying to avoid saving the format information in another column or something. For sorting, when records have same Year, I would have another column to do relative sorting... So question is - what are my best options with SQL/Entity Framework combo. And what others have done, when encountering similar - if any. Thanks in advance. --Sharad 

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

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!    

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

Retrieve month name and last 2 digit of yr from date column

Table contain one column [d_matur] [datetime] NULL 2014-06-20 00:00:00.000 2015-03-20 00:00:00.000 like this now I want to retrieve month name like June as well as last two digit of year like 14 if anyone know query how to get these from above column please let me know.Thanks

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.

Question of using CASE and IF statements for a calcuated member using MDX


I have a calculated member called 'MemberCounts = [Total # of members/#of month'] which calculate the number of members per month. For example, Total number of members in 2007 is 12,000. If user is looking at the Year level in the date hierarchy then Membercounts = 12,000/(#of months = 12) = 1000. If a quarter has been selected then MemberCounts = 12,000/(#of months = 3) = 12,000/3 = 4000 and at the month level, it will be 12,000/1 = 12,000.



WHEN [AdmitDate].[Hierarchy].currentmember.level is





      WHEN [AdmitDate].[Hierarchy].currentmember.level is







This works perfect for every year and its quarter until the current year.  

Requirement: For the current quarter (Quat-3) of Year (2010), the [Measures].[Members] field for month of Sept. is 0. (It will be populated at the end of the month).

By default, the calculated member defined by

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...

first date of previous month?

How to get the first date of previous month?
PS.Shakeer Hussain Hyderabad

Send a reminder email 1 month before renewal date




I have a custom list with information about our customers and when we need to renew there equipment. When we add entries to the list we have field called Renewal date where we select the date the equipment needs renewing.


I would like to create a workflow so I can select the entry click on workflows and set the entry to email me 1 month before the renewal is due.


I am using SBS 2008 and WSS 3.0


Thanks in advance

Query acting weird.. larger date range works in 1min 26sec and a smaller range say 1 month - 3 month


I have no clue why my query is acting weird

If i try to run it for 1/1/2010 9/30/2010 the query takes around 1min 26 sec and  return around a million rows

and If i run for 8/1/2010 to 8/31/2010 it takes forever to it...

basically i am getting data from 5 tables and putting it in a temp table and then updating that temp table 2-3 times with some information and then displaying it.

I am stumped as to why it works fast for a longer date range and runs the  snail for a small period of time..

I am in the verge of pulling my hair and going crazy..

any help will be appreciated.




check if date in last month

How can i check if the date is in last month

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

return records based on current date?


I tried finding a QA which already addressed this idea, but with no luck. Sorry if it's been addressed before.


I have a table where sales pricing is managed; it contains history of pricing where every price record has an 'effective date'. Currently when a user needs the 'active' price for a customer/item combination, the table is queried by the record that has the most recent 'EffectiveDate' before GETDATE(). Here is the def for table 'tim_PriceSheet'

ItemKey int, not null

EffectiveDate datetime, notnull

CurrID varchar(3)

ListPrice decimal (15,5)

The primary key is set on ItemKey, EffectiveDate

Sample Data:


ItemKey	EffectiveDate	    CurrID	ListPrice
45	2010-07-01 00:00:00.000	CAD	100.00000
45	2010-09-14 00:00:00.000	CAD	110.00000
45	2010-10-01 00:00:00.000	CAD	112.00000
63	2010-09-01 00:00:00.000	CAD	45.00000
63	2010-09-01 00:00:00.000	USD	45.00000
63	2010-09-18 00:00:00.000	CAD	40.00000
63	2010-10-01 00:00:00.000	USD	40.00000
63	2010-10-15 00:00:00.000	CAD	41.00000
63	2010-10-21 00:00:00.000	USD	41.00000

My challenge is this:

I want to return a record set which shows the current price for each distinct item and currency code; in the example below the result would be based on the date being September 16, 2010

Return Last 3 Months data based on the DATE


Using the getdate() function, how do I select the last WHOLE 3 months,

So for example, for todays date (29/09/2010)

I want to return all records with the date between 01/06/2010 and 31/08/2010

Is this possible?

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