Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Get UTC time from the Hiarchy from level hourofday

Unanswered Get UTC time from the Hiarchy from level hourofday

  • Monday, January 21, 2013 9:55 AM
     
      Has Code
    Sign In to Vote
    0

    Hi everyone

    I have the following piece of code and I am having a problem in retrieving the value or common name for the datetime rows from the ([Dim_DateTime].[Calender].[Hourof Day].&[2010]&[11]&[1]&[4]  part of the code.  This part of code "[Dim_DateTime].[Calender].[Hourof Day].[Member_Caption]" as UTC only returns the hour which in this case is 4. What I want to be able to see is a column like this 11/1/2010 4:00 Not just 4.

    Memmber_caption only returns the Houroftheday. Please help.

    Select "[Dim_DateTime].[Calender].[Hourof Day].[Member_caption]" as UTC,
			"[Measures].[Value Actual]" as ActualValue,
			CONVERT(float, "[Measures].[Value Actual Count]") as ValueActualReadings,
	        CONVERT(float, "[Measures].[Value Expected Count]") as ValueExpectedCount  from openquery
				 (A1,'Select  
						{[Measures].[Value Actual],
						[Measures].[Value Actual Count],[Measures].[Value Expected Count]} ON COLUMNS ,
					{ NonEmpty([Dim_DateTime].[Calender].[Hourof Day].&[2010]&[11]&[1]&[4]: [Dim_DateTime].[Calender].[Hourof Day].&[2011]&[12]&[1]&[5])} on rows 
					FROM [Energy Aggregator]
					where [Dim_Item].[Item Id].[Item Id].&[122559]' )

    This is how the output looks like

    utc ActualValue  ValueActualReadings  ValueExpectedCount 

    4 20 2 1

    5 16 2 1

    6 17 2 1

    I want the utc column to show the actual date in thats there in the calendar hiararchy.

    Thanks much for your help

    -Sarah

     

All Replies

  • Monday, January 21, 2013 2:54 PM
     
      Has Code
    Sign In to Vote
    0

    Hello Sarah,

       The MEMBER_CAPTION property to would return the values from the columns which has been set for the Caption property for the attribute "HourofDay". Here are some below ways to achieve this

    1. Change the caption property of the "HourorDay" to a column having the values you need to display.

    2. Create a User Defined member property for this attribute and use it in your query.

      Refer to the below blog for detailed step

    http://sornanara.blogspot.com/2012/10/ssas-how-to-create-user-defined-member.html

    3. You can create a caluclated member as below to get the value in  required format . The below sample will get the output as "1-4" as 1 is the month for the hour 4.

    WITH MEMBER MEASURES.X 
AS
[Dim_DateTime].[Calender].[Hourof Day].CURRENTMEMBER.Parent.MEMBER_CAPTION + "-" + [Dim_DateTime].[Calender].[Hourof Day].CURRENTMEMBER.MEMBER_CAPTION

    Best Regards Sorna

     
  • Wednesday, January 23, 2013 12:57 AM
     
     
    Sign In to Vote
    0

    Thanks much Sorna for your help on this.

    This is good, I think I am looking something along the same lines.Couple of questions

    1) How can I change the caption property of the HourofDay?

    2)

    So if I do something like you mentioned the calculated member. What does CURRENTMEMBER.Parent.MEMBER_CAPTION do?

    How is it returning month to me? Does it look one level above the hiararchy?

    Also I want the entire (month-day-year) as UTC, how can I go about doing that? specially if I am at a different level of Hiararchy..

    So if I am at year level, I will not have a month or day but I want it to display 1/1/2010-- 2010 being the year?

    Really appeciate your help on this

    Thanks much

    -Sarah

     
  • Thursday, January 24, 2013 6:51 PM
     
     
    Sign In to Vote
    0

    Anyone please, I need help with it.

    I have starttime = 6/10/2010 and end time 2/14/2012 coming up, I need to aggregate values from the hiararchy at appropraite levels, may be dynamic mdx or something like that. Please help..