.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

Calc'd Member Specifically for JAN, FEB etc of Chosen Tran Date

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

View Complete Post

More Related Resource Links

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?

MDX: Percent Average of Average (Calc Member)

Not sure this makes sense. What I have is an average payment amount which will be sliced by payer. I want to see what is payer1's percent average of the overall average from all payers. I have a calculated member for average payment amount already: CREATE MEMBER CURRENTCUBE.[Measures].[Average Payment Amount] AS CASE WHEN IsEmpty([Measures].[Count]) OR [Measures].[Count] = 0 THEN NULL ELSE ([Measures].[Payment Amount] / [Measures].[Count]) END, FORMAT_STRING = "currency", NON_EMPTY_BEHAVIOR = {[Payment Amount]}, ASSOCIATED_MEASURE_GROUP = 'Payment', VISIBLE = 1   Any help is appreciated. I was thinking I need to do something with currentmember, but then I couldn't think of how I would have the denominator be all payers.  I dunno, hopefully it's easier than I'm making it.

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.

MDX - Find last leaf member of Date dimension under selected member of Date dimension, independently


Hi all

I need to reproduce the behaviour of the averageofchildren function with the exception that the function should always build the average of all members (even when the value of a member equals null). As you know average of children function always build the average at the lowest level of the time dimension.

My date dimension looks like the following:

[Date].[Fiscal] user defined hierarchy:




if context of [Date].[Fiscal].CurrentMember corresponds to a Week, I can retrieve all weeks before the current week using

In Period: MTD([Date].[Fiscal].CurrentMember)  -- PeriodsToDate also works fine

In Year: YTD([Date].[Fiscal].CurrentMember)

if context of [Date].[Fiscal].CurrentMember corresponds to a Period

How can I get all weeks (NOT Periods!) contained in all Periods before and within current period since start of year?

What would be a generic expression that retrieves all weeks before and in independently of level of selected Date dimension member




SSAS 2008 - Date Member Property gets converted from NULL to (Blank)


Hello everyone,

I have this cube with a dimension of investment deals.  The "Deal" member has a attribute named "Latest Acquisition Date" where this date can be null for some deals.  Once the cube is processed, the null dates always get converted to "(blank)" either in BIDS cube browser or in Excel.  This happens no matter what I do to the NullProcessing property of this date attribute.

Is there any way to have SSAS display [NULL] (as in nothing at all) instead of "(blank)" ?

Creating a default member on date hierarchy



I am trying to set a default member on a date dimension.

I have an attribute form the database of IsToday which is a boolean with the value of today eg 23/11/2010 showing as true.

When i go into the Date dimension and try to select [Date].[Is Today].&[True] in Choose a member to be the default I get the error below

DefaultMember(Date,Is Today) (1, 1) The level '[Date]' object was not found in the cube when the string, [Date].[Is Today].&[True], was parsed.

Does anyone have any ideas why this is?


Default date dimension member


Hi All,

Is there a way to set a default member in the date dimension so that it will be automatically observed by Excel as a filter?

I have a stock cube which shows stock levels by day. At the moment, users have to change the day filter manually in the Pivot. I'd like it so that it refreshes automatically based on a cube setting so that it is centralised.

It appears I can set a default member for the date attribute, but without selecting date as a specific filter it shows the last non empty value (as defined by the cube structure). I need it to show the stock as per the specific date.

I've accomplished this before using vba in the Excel file, but wondered if there was a better method. Unfortunately named sets don't work in Excel as filters and I don't want to see the date on the rows/columns.

Many thanks,


Using a CompareValidator to check input is a valid date

The CompareValidator can do more than just compare two controls. You can also compare it against several of the main .net data types such as Date, Integer, Double and Currency.

To do this you would set Operator="DataTypeCheck" and instead of setting the ControlToCompare or ValueToCompare attributes as you normally would you use the Type="Date" (or any of the data types I have listed above).

How To Set a Date Format In GridView Using ASP.NET 2.0

A very common desire is to set a column of a gridview to display just the month, day and year of a DateTime type. The problem is the by default, the HtmlEncode property of the boundfield attribute (
The problem is that if this field is enabled, you can not pass format information to the boundfield control. That is, if you try the following code, you will not get the desired result.

jQuery Date picker Implementation in ASP.NET

I've posted a wrapper ASP.NET around the jQuery.UI Datepickercontrol. This small client side calendar control is compact, looks nice and is very easy to use and I've added it some time back to my control library.

This is primarily an update for the jQuery.ui version, and so I spend a few hours or so cleaning it up which wasn't as easy as it could have been since the API has changed quite drastically from Marc's original implementation. The biggest changes have to do with the theming integration and the resulting explosion of related resources.

If you want to use this component you can check it out a sample and the code here:

How to format datetime & date with century?

Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available and the application of date / datetime functions.


Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

Built-in Functions - Date and Time Functions

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

Essential SQL Server Date, Time and DateTime Functions

The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

Date/Time Conversions Using SQL Server

There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Date and Time Data Types and Functions

The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions
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