.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Unable to get Lastperiods 4 periods in time hierarchy at different levels.

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
Hi, I am unable to get last 4 periods when the weeks are falling at two different months. Example: The calendarsales contains the following hierachy : Year, HalfYear, Quarter, Month, Week August month contains the following weeks:1033,1034,1035 July month contains the follwing weeks: 1032,1031,1030. So i am selecting the week 1035 and i need last 4 weeks till 1032. But the below qry is returning last 3 weeks 1035,1034,1033. It is unable to retrieve 1032 as it is coming under previous month of July.  Below is the mdx query i am using. **************************************** WITH     SET ORDEREDCAT AS   Order ( [Dim_Product].[ProductDefaultHierarchy].[Category]. MEMBERS ,[Measures].[Sales Units] , BDESC )     MEMBER [Measures].[SalesUnits_L_4WKBP] AS   Sum ( ( {   LastPeriods (4 ,[Dim_RetailerSalesCalendar].[CalendarSales]. CurrentMember ) } ,[Measures].[Sales Units] ) )     SELECT     NON EMPTY {[OrderedCat]} ON 1 ,{ [Measures].[SalesUnits_L_4WKBP]     } ON 0 FROM   [Aroyale] WHERE   {[Dim_Retailer].[Retailer].&[5]}* {[Dim_RetailerSalesCalendar].[CalendarSales].[week].&[1035]} ***************************** Please can any one help me on this. Regards, Srini.

View Complete Post

More Related Resource Links

MDX to retrieve data from Time hierarchy

Hi there, I have a time hierarchy Year->Quarter->Month->Day. I need to get all the level data by one MDX.  Desired result set should look like Year    Quarter    Month    Day  MeasureValue 2010    Q1           Jan       01     1000 2010    Q1           Feb       28     500 Thanks, Palash

T-SQL query, average of daily time periods over a date range



I'm building a report in Crystal Reports using a SQL command against a T-SQL 2005 telephony database.

I need to be able to run the query across a given datetime range, 6 months for example and bring back a 5 day display (Mon-Fri) with a group for every 15 minute interval in each day.

The group figures need to contain an average of the amount of calls presented for each 15 minute interval on any day across the whole datetime range, so for example the Monday 10:00 - 10:15 figure would be an average of all calls presented in every 10:00-10:15 range on each of the Mondays that fall within the datetime range.

I've got a query built now that gives total presented figures grouped by these intervals across one week but I can't figure out how to do this average function across a range.

Does anyone have any idea how I'd go about accomplishing this? I'm pretty new to SQL but keen to learn so any pointers on functions to research etc would be very much appreciated.

Thanks alot in advance, Andy.

Query for detail view across one week included below for table/fields etc...



count(DISTINCT Calls.SessionID) as Presented, min(Calls.startDateTime) as DateTime

INNER JOIN QueueDetail
ON Calls.sessionID =  Queues.sessionID
AND Calls.sessionSeqNum =  QueueDet

Range dimension - possible with Relative Time Periods?

Hi All,

I have a Date_Range dimension with members 7 days, 30 days and 90 days only.
Although all reporting needs could be well address using existing date dimension and
MDX filter or range operator I need a physical Date_Range dimension.

It seems possible to address my requirement (implementing Date_Range dimension) by Handling Relative Time Periods .
The blog post (by Chris) is rather old and I did not find updates on it or recent similar articles.

Please share your thoughts on how best to address this requirement.


Prakash Gautam

Calendar View: Unable filter Recurring Event by Start/End Time and Group by Recurring Event View

Hi All,
I have just found several issues with Calendar View from WSS 3.0:

Unable to filter by [Start Time] and [End Time]
I am not sure why these 2 columns doesn't appear in the Filtering column in the View Settings. The workaround found in the internet is to create calculated column for Start Time and and End Time. However, it doesn't work for Recurring Event, the calculated column will show only first recurring event Start Time.

Unable to use "Group By"
When I create new view in Calendar using format: "Standard View, with Expanding Recurring Events", there is no option to specify "Group By". Anyone knows how to show all recurring event in List view and grouped by Start Time (or any other column).

Thank you and apprecate for any idea.

suppressing two highest levels of employee hierarchy in excel


Hi.  My recollection is that in mgt studio's cube browser I can drag the highest level executives off an employee hierarchy pivot by dragging level numbers.  After all, we know who the ceo is and the ceo's direct report as well.

I dont see this capability in excel 2007.  When I unclick these top level execs in the filter already being used to hide ex employees, excel still shows the two highest level execs.  Is there an elegant way to suppress them in excel?  I dont want to remove them from the cube, just to suppress them in one particular excel pivot.

howto: display only hierarchy-members (levels) which are selected in the parameter (SSAS and SSRS)


Hi all,

I'm a SSRS/MDX beginner, so this might be a basic question. Sorry for that.

I have a hierarchy, which I use as a parameter. The Hierarchy has 3 levels (level1, level2, level3).

Sample Hierarchy

Now I want to achieve the following:

  • If a user selects CH1 --> the report should show only the CH1 (which is the sum of CH11 + CH12 + CH13)
  • If a user selects CH11 and CH12 and CH13 --> the report should show only these leafs (no aggregations)
  • If a user selects CH1 and CH11 and CH12 and CH13 --> the report should show the leafs and CH1 (seperate)

I guess, this is not that hard, but somehow I don't get it.

Here my sample mdx-query (the hierarchy I'm writing about is "Lieferantenstruktur" and the parameter "@Lieferant" (BOLD)):
(right now, only the leaf-level [Lieferant] is returend by the query)

SELECT NON EMPTY { [Measures].[Einheiten] } 

, NON EMPTY { ([Lieferantenstruktur].[Lieferantenstruktur].[Lieferant].ALLMEMBERS 
* [Artikel].[Artikel Kategorie].[Artikel Kategorie].ALLMEMBERS 
* [Artikel].[Artikel Gruppe Code Name].[Artikel Gruppe Code Name].ALLMEMBERS 
* {[Kundengruppen].[Kundengruppen].[Verkaufskanal].ALLMEMBERS 

Sorting according to Hierarchy levels



I have a Region Hierarchy which has Continent, Country level in it.



<Dimension type="StandardDimension" highCardinality="false" name="Location">
    <Hierarchy hasAll="true" primaryKey="region_id">
      <Table name="dim_region">
      <Level name="Continent" column="continent_map" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
        <Property name="Continent_Name" column="continent">
      <Level name="Country" column="country_code_iso2" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
        <Property name="Country_Name" column="country_name">

Move up a time hierarchy from current date to return a set of quarters


I have this calculated set to return the previous 3 months.  How do I rewrite to get the last 3 quarters? 



(3,strtomember("[Date].[Month].&[" + Format(Now(),"yyyy-MM") + "-01T00:00:00]").prevmember)


Unable to convert MySQL date/time value to System.DateTime



I get the following error when i call my method from the class any ideas?

Unable to convert MySQL date/time value to System.DateTime

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time value to System.DateTime

Source Error:

Line 324:        string strqry = "Select * FROM tblclients";
Line 325:
Line 326: DataTable returnDatatbl = conClass.queryDB(strqry);
Line 327:
Line 328:

Unable to see Asp.net tab in IIS at the time of installation



I am trying to install an asp.net application in IIS but am unable to see the asp.net tab! to set the enviorment.

I uninstalled and reinstalled IIS and followed the following steps


1. Unregister all the versions of ASP.NET with command "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis –ua".


2. Then registere ASP.NET 2.0 with IIS using "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis –i".

3. Give permissions to the ASPNET account using "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis –ga machinename\ASPNET".

4. Reset the IIS and that resolved the issue for ASP.NET 2.0.

4. Register ASP.NET 1.1 with IIS as well using command "C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\aspnet_regiis –i".

7. Reset the IIS.


But it does not work..Kindly suggest it is urgent...

Many Thanks.

Round off time to the nearest minute

How would you round this up to the nearest minute? There isn't a built in function to do this so you have to use a little bit of maths to get there. There are 60 seconds in a minute. We already have 38 seconds on the clock. So we need to add on 60 - 38 = 22 more seconds.

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.

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.

How to programmatically add controls to Windows forms at run time by using Visual C#

Create a Windows Forms Application
Start Visual Studio .NET or Visual Studio 2005 or a later version, and create a new Visual C# Windows Application project named WinControls. Form1 is added to the project by default.
Double-click Form1 to create and view the Form1_Load event procedure.
Add private instance variables to the Form1 class to work with common Windows controls. The Form1 class starts as follows:

.NET 4 Web Application Startup Time

I was chatting with Jonathan Hawkins and some of the folks on the ASP.NET team about performance and Jonathan mentioned the startup time for large ASP.NET applications is improved on .NET 4. There are some improvements in the CLR and in ASP.NET itself that helped. If you have a giant app, you should do some tests.

Built-in Functions - Date and Time Functions

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.
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