“They retain the courage to play, the courage to try and win.”

Useful Links

Add Custom ASPX Pages or ASP .Net Pages in SharePoint

http://sharenotes.wordpress.com/2008/02/21/add-custom-aspx-pages-or-asp-net-pages-in-sharepoint/

Pakistan’s Motorway, World’s First Motorway

If you can’t explain it simply , you don’t understand it well enough. (Albert Einstein)

Pakistan’s Motorway, World’s First Motorway where Pakistan Army can land and takeoff their FIGHTER JETS easily, the whole motorway was constructed on Runway Rules.

Col said

Each and every tenure of passing time is problem solving.
( Life is meant to be problem solving and you must nourish yourself and your siblings to do problem solving.)

Instead of using ATM each and every time. Try to withdraw the budgeted amount and stick to it for the whole month.

Teri Azmaton se hoon be-khabr
Ye meri nazar ka qasoor hai
Teri rah-guzer pe qadam qadam
kahin arsh hai kahin toor hai
Ye baja k malik-e-do jahan
meri bandagi ka qasoor hai
Ye khata hai meri khata mager
Tera naam bhi tu ghafoor hai
Ye bata k tuj se milon kahan?
Muje Tuj se milna zaroor hai
Kahin dil ki shart na dalna
Abi dil gunahon se mera choor hai
Tu bakhsh de merey sab gunah
Rehman hai tu ghafoor hai

“The sixth sick Sikh’s sixth sheep is sick”
The most difficult sentence to speak according to Guinness Book Of world Record (Try it repeatedly.)

Wise words

Ability can never b hidden
No injury is deeper than insult
The birth of tension is the death of talent
No sound is louder than silence

4 great Ways of living
First, Look Back and Thank ALLAH
2nd, Look Forward and Trust ALLAH
3rd, Look Around and Believe ALLAH
4th, Look With in U and Find ALLAH

Going Mouseless – Implementing Keyboard Shortcuts in ASP.NET 2.0 Using Javascript

Going Mouseless – Implementing Keyboard Shortcuts in ASP.NET 2.0 Using Javascript

Keyboard shortcuts improve productivity by accomplishing tasks more quickly and without much effort. In applications, where the user has to select from a variety of actions to perform, keyboard shortcuts can save on time and effort. If you have used the new YahooMail or Gmail, you will be quiet familiar with these shortcuts. In this article, we will explore how to implement shortcuts and use it in our ASP.NET applications. Please note that this solution is Internet Explorer(IE) specific.

I was recently implementing a solution where users needed the ability to use shortcuts in ASP.NET applications, similar to what we see in Gmail and Yahoo mail. The requirement was as follows:

Problem Scenario

The user visits a screen which contains a series of links (linkbuttons). The user is then required to click on one of the links to navigate to a different page to perform some action (For eg: filling forms). After performing the action, the user returns back to the screen. The process is repeated by the user, quiet a number of times. Now if you visualize, the user has to use the mouse to click one of the links and then shift back to the keyboard to enter data. Then use the mouse again to go back to the main screen. What a pain, especially if someone is involved in data entry operations!!

Solution

The solution was simple. The solution was to introduce Keyboard shortcuts into the application. The user could use a shortcut instead of the mouse, to click the linkbutton and perform an action. So let us say for example, when the user clicks ‘H’, the click event of the Home button gets fired and the user navigates to the Home page.

We will see how to implement this feature in this article. It can be done easily with just a few lines of code. Follow these steps:

1. Create an ASP.NET website. Add a master page to the application. Also create 4 pages that will be using this master page: Default.aspx, Page1.aspx, Page2.aspx and Page3.aspx. If you are unfamiliar with MasterPages, I recommend you to read my article : http://www.dotnetcurry.com/ShowArticle.aspx?ID=80

2. In the default.aspx, drag and drop 3 link buttons to the form. Rename these linkbuttons as Page 1, Page 2 and Page 3 respectively.

The page would look like this:

<asp:Content ID=”Content1″ ContentPlaceHolderID=”ContentPlaceHolder1″ Runat=”Server”>

<div style=”width: 100%; height: 154px;”>

<asp:LinkButton ID=”lbPage1″ runat=”server” PostBackUrl=”Page1.aspx”>Page 1</asp:LinkButton><br />

<br />

<asp:LinkButton ID=”lbPage2″ runat=”server” PostBackUrl=”Page2.aspx”>Page 2</asp:LinkButton><br />

<br />

<asp:LinkButton ID=”lbPage3″ runat=”server” PostBackUrl=”Page3.aspx”>Page 3</asp:LinkButton><br />

<br />

</div>

</asp:Content>

3. In the code behind of default.aspx, add the following code to the Page_Load event

C#

protected void Page_Load(object sender, EventArgs e)

{

ClientScript.RegisterClientScriptBlock(this.GetType(), “Shortcut”, “document.attachEvent (‘onkeyup’,ShortcutKeys);”, true);

}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

ClientScript.RegisterClientScriptBlock(Me.GetType(), “Shortcut”, “document.attachEvent (‘onkeyup’,ShortcutKeys);”, True)

End Sub

The ClientScript.RegisterClientScriptBlock registers the client script block to the top of the rendered page using a type, key, and script literal. A client script is uniquely identified by its key and its type.

4. Add a javascript file to the project called shortcut.js. Add the following code to the javascript file.

function ShortcutKeys()

{

//alert(event.keyCode);

// 1 Pressed For Page1

if (event.keyCode == 49)

{            document.getElementById(‘ctl00_ContentPlaceHolder1_lbPage1′).click();

}

// 2 Pressed For Page2

if (event.keyCode == 50)

{

document.getElementById(‘ctl00_ContentPlaceHolder1_lbPage2′).click();

}

// 3 Pressed For Page3

if (event.keyCode == 51)

{                      document.getElementById(‘ctl00_ContentPlaceHolder1_lbPage3′).click();        }

}

The javascript, depending on the keycode, invokes the click event of the element, in our case the link button. The element is obtained by using document.getElementById(‘ ‘<%=Control.ClientID%>’‘).

Note: While using Master Pages, you need to refer to the control using the control’s ClientID. I have directly used the ID generated.

5. Now refer to the javascript in the MasterPage.

<head runat=”server”>

<title>Untitled Page</title>

<script type=”text/javascript” language=”javascript” src=”shortcut.js”> </script>

</head>

The keyboard shortcut functionality has been added to the Default.aspx. Before we go ahead and test it, let us make the other pages too ‘mouseless’.

6. Add the keyboard functionality in each of the pages (Page1, 2 and 3). To do so, add a button called btnHome to each of the pages. Set the PostBackUrl property of btnHome to ‘Default.aspx’.

7. In the shortcut.js, add another method to handle the event for the Home button using the keyboard shortcut “H”

function HomeKey()

{

if(event.keyCode == 72)

{        document.getElementById(‘ctl00_ContentPlaceHolder1_btnHome’).click();

}

}

8. Now register the newly added Client script in each of your pages as shown below :

C#

protected void Page_Load(object sender, EventArgs e)

{

ClientScript.RegisterClientScriptBlock(this.GetType(), “Home”, “document.attachEvent(‘onkeyup’,HomeKey);”, true);

}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

ClientScript.RegisterClientScriptBlock(Me.GetType(), “Home“, “document.attachEvent (‘onkeyup’, HomeKey);”, True)

End Sub

9. There you are!!. Now all the pages in your application contain keyboard shortcuts.

Run Default.aspx. Test the pages by pressing 1, 2 or 3 to navigate to Page1 , Page2 or Page 3 respectively. Similarly press “H” on each of these pages to go back to the Default.aspx page.

The usage of keyboard shortcut, in this article has been kept as simple as possible. However once you understand how to use this effectively, the possibilities are endless. Gmail and Yahoomail are two good examples where keyboard shortcuts are being used to improve user interactiveness with the application.

You can download the source code of this article over here.

I hope this article was useful and I thank you for viewing it.

muslim_calendar_1429

Asp.NET user Control tips

<br /> Shahed Khan (MVP)<br />

<!–
//

<!–
//

Shahed Khan (MVP) blog posts – 213, comments – 140, trackbacks – 68
My Links Home Contact Login <!– var browName = navigator.appName; var SiteID = 1; var ZoneID = 1; var browDateTime = (new Date()).getTime(); if (browName==’Netscape’) { document.write(‘‘); document.write(”); } if (browName!=’Netscape’) { document.write(‘‘); document.write(”); } // –> &lt;br /&gt; &lt;a href=”http://ads.geekswithblogs.net/a.aspx?ZoneID=1&amp;Task=Click&amp;Mode=HTML&amp;SiteID=1&amp;PageID=51102″ mce_href=”http://ads.geekswithblogs.net/a.aspx?ZoneID=1&amp;amp;Task=Click&amp;amp;Mode=HTML&amp;amp;SiteID=1&amp;amp;PageID=51102″ target=”_blank”&gt;&lt;br /&gt; &lt;img src=”http://ads.geekswithblogs.net/a.aspx?ZoneID=1&amp;Task=Get&amp;Mode=HTML&amp;SiteID=1&amp;PageID=51102″ mce_src=”http://ads.geekswithblogs.net/a.aspx?ZoneID=1&amp;amp;Task=Get&amp;amp;Mode=HTML&amp;amp;SiteID=1&amp;amp;PageID=51102″ width=”160″ height=”600″ border=”0″ alt=”"&gt;&lt;/a&gt;&lt;/p&gt; &lt;p&gt; &lt;br /&gt; &lt;a href=”http://www.quantcast.com/p-03-ZsKAm7O3lI” mce_href=”http://www.quantcast.com/p-03-ZsKAm7O3lI” target=”_blank”&gt;&lt;br /&gt; &lt;img src=”http://pixel.quantserve.com/pixel/p-03-ZsKAm7O3lI.gif” mce_src=”http://pixel.quantserve.com/pixel/p-03-ZsKAm7O3lI.gif” style=”display: none” mce_style=”display: none” border=”0″ height=”1″ width=”1″ alt=”Quantcast”/&gt;&lt;/a&gt;&lt;br /&gt; News I am a Microsoft Certified Application Developer MCAD Chartered Member (C# .Net) and born in Bangladesh. I work for Ocean Informatics Pty Ltd as a Senior Developer – Analyst. I am also co-founder and core developer of Pageflakes www.pageflakes.com and most recently created SmartCodeGenerator My Articles Flexible and Plugin based .Net Application.. Mass Emailing Functionality with C#, .NET 2.0, and Microsoft® SQL Server 2005 Service Broker’ Write your own Code Generator or Template Engine in .NET Smart Code Generator .NET: Usage Overview Smart Code Generator .NET: Architectural Overview Smart Code Generator .NET: using with NAnt and Cassini Archives June, 2008 (4) May, 2008 (2) April, 2008 (2) March, 2008 (2) February, 2008 (6) January, 2008 (4) December, 2007 (1) November, 2007 (2) October, 2007 (5) September, 2007 (5) July, 2007 (3) June, 2007 (1) May, 2007 (4) April, 2007 (6) March, 2007 (4) February, 2007 (8) January, 2007 (18) December, 2006 (13) November, 2006 (23) October, 2006 (12) September, 2006 (11) August, 2006 (13) July, 2006 (5) June, 2006 (7) May, 2006 (5) April, 2006 (2) March, 2006 (2) February, 2006 (2) January, 2006 (6) December, 2005 (8) November, 2005 (6) October, 2005 (21) Free Programming Language Training Free Programming Language Training	Thursday, June 26, 2008 ASP.NET tips: Golden rules for Dynamic Controls. 1. Make sure your dynamic controls are Loaded on every postback. Lets play with a very simple example, ASPX <%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %> <body> <form id=”form1″ runat=”server”> <div> <asp:PlaceHolder ID=”PlaceHolder1″ runat=”server”></asp:PlaceHolder> <asp:Button ID=”Button1″ runat=”server” Text=”Button” /> </div> </form> </body> </html> C# Code Behind public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { TextBox t = new TextBox(); t.ID = “textBox”; this.PlaceHolder1.Controls.Add(t); } } The above code works fine, but a common mistake is to try to conditionally load dynamic controls, if we tweak the code a little bit you will notice we loose our TextBox after any postback. The following code will not load the TextBox after our first postback. public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { TextBox t = new TextBox(); t.ID = “textBox”; this.PlaceHolder1.Controls.Add(t); } } } Its recommended to load the dynamic controls during the Page_Init instead, because we may want to hook up our events with proper handler at an early stage. public partial class _Default : System.Web.UI.Page { protected void Page_Init(object sender, EventArgs e) { TextBox t = new TextBox(); t.ID = “textBox”; t.TextChanged+=new EventHandler(t_TextChanged); this.PlaceHolder1.Controls.Add(t); } } 2. Do not assigning properties of a dynamic control (viewstate enabled), during Page_Init, it will not be reflected. Here is scenario of another common mistake, “123″ assigned to the Text property during Page_Init, public partial class _Default : System.Web.UI.Page { protected void Page_Init(object sender, EventArgs e) { TextBox t = new TextBox(); t.ID = “textBox”; t.Text = “123″; this.PlaceHolder1.Controls.Add(t); } } the above code will not work because, Initialization happens before LoadViewState during the control lifecycle. The value assigned to the properties during Initialization will simply get overwritten by the ViewState values. 3. If you are expecting your ViewState to retain after the postback, always assign same ID to the dynamic control The following piece of code will not work, as I am assigning a new ID to the dynamic control after each postback. The LoadViewState retrieves previously saved viewstate data using the control ID, as the control ID has changed, it doesn’t know anymore what to load, as a result it cannot load previously saved viewstate data any more. public partial class _Default : System.Web.UI.Page { protected void Page_Init(object sender, EventArgs e) { TextBox t = new TextBox(); t.ID = Guid.NewGuid().ToString(); this.form1.Controls.Add(t); } } Thank you for being with me so far. posted @ Thursday, June 26, 2008 2:36 AM | Feedback (0) | Tuesday, June 17, 2008 ASP.NET tips, Making Custom Validators work in Partial Rendering mode. Introduction There are many situations where we need to identify if partial rendering is supported in a page, especially when a control uses javascript, to get the control work in partial rendering mode, the script needs to be registered using a ScriptManager Type instead. A classic example will be Validators. The ASP.NET Page class exposes the Validators property, which is a list of all the IValidator types on the page. A page keeps track of its validators, and registers a javascript array of validators automatically to the page. Example, When we add 3 RequiredFieldValidator in a page the following javascript Array will be automatically generated and added in our page automatically during the page load. Page_Validators = new Array(document.getElementById(“RequiredFieldValidator1″), document.getElementById(“RequiredFieldValidator2″), document.getElementById(“RequiredFieldValidator3″)); The ASP.NET Page also registers couple of other script which eventually hooks up different events ( onclick, onkeypress, onchange, onblur ) to the the target control (ControlToValidate), to some predefined javascript functions that resides in WebUIValidation.js file. So when we add a validator in our Page we also notice the following script is automatically added. [WebUIValidation.js ships with ASP.NET and resides in the following folder "/aspnet_client/system_web/<version>/WebUIValidation.js".] <script type=”text/javascript”> <!– var Page_ValidationActive = false; if (typeof(ValidatorOnLoad) == “function”) { ValidatorOnLoad(); } function ValidatorOnSubmit() { if (Page_ValidationActive) { return ValidatorCommonOnSubmit(); } else { return true; } } // –> </script> ValidatorOnLoad plays the big role of hooking up the the events mentioned above, and here is a code snippet from this function, for (i = 0; i < Page_Validators.length; i++) { val = Page_Validators[i]; if (typeof(val.evaluationfunction) == “string”) { eval(“val.evaluationfunction = ” + val.evaluationfunction + “;”); } … if (typeof(val.controltovalidate) == “string”) { ValidatorHookupControlID(val.controltovalidate, val); } … } keen eyes may have already noticed the val.evaluationfunction property, yes every validators needs to have this property for it to work properly under the ASP.NET validation framework. Custom validators takes advantage of this property to point to custom js functions. Custom validator developers normally use RegisterExpandoAttribute method to register this attribute. protected override void AddAttributesToRender(System.Web.UI.HtmlTextWriter writer) { base.AddAttributesToRender(writer); if (this.RenderUplevel) { string clientID = this.ClientID; Page.ClientScript.RegisterExpandoAttribute(clientID, “evaluationfunction”, “EntryValidatorEvaluateIsValid”); } } Problem When I used Update Panel with partial rendering enabled the Page.ClientScript.RegisterExpandoAttribute did not work for me. My validators always stopped working after the first postback, which was performed via partial rendering and triggering. I found the “evaluationfunction” in the javascript to be undefined. Solution I started looking under the hood, and soon discovered, that the ASP.NET Validators that ships out of the box, ( eg. RangeValidator, RequiredFieldValidator ) uses a different internal method “AddExpandoAttribute” to register the property. Here is a code snippet from the RangeValidator. protected override void AddAttributesToRender(HtmlTextWriter writer) { base.AddAttributesToRender(writer); if (base.RenderUplevel) { string clientID = this.ClientID; HtmlTextWriter writer2 = base.EnableLegacyRendering ? writer : null; base.AddExpandoAttribute(writer2, clientID, “evaluationfunction”, “RangeValidatorEvaluateIsValid”, false); … } } and code snippet from BaseValidator, the internal method AddExpandoAttribute. internal void AddExpandoAttribute(HtmlTextWriter writer, string controlId, string attributeName, string attributeValue, bool encode) { AddExpandoAttribute(this, writer, controlId, attributeName, attributeValue, encode); } After digging further I realized, AddExpandoAttribute checks the ASP.Page whether partial rendering is supported, then it registers the attribute using ScriptManager instead. I did the same with my validation control and it works for me. Here is the piece of code that solved my problem. protected override void AddAttributesToRender(System.Web.UI.HtmlTextWriter writer) { base.AddAttributesToRender(writer); if (this.RenderUplevel) { string clientID = this.ClientID; if (!this.IsPartialRenderingSupported) { Page.ClientScript.RegisterExpandoAttribute(clientID, “evaluationfunction”, “EntryValidatorEvaluateIsValid”); } else { Type scriptManagerType = BuildManager.GetType(“System.Web.UI.ScriptManager”, false); scriptManagerType.InvokeMember(“RegisterExpandoAttribute”, BindingFlags.InvokeMethod | BindingFlags.Public | BindingFlags.Static, null, null, new object[] { this, clientID, “evaluationfunction”, “QuantityEntryValidatorEvaluateIsValid”, false }); } } } Note, the I am first checking whether Partial Rendering is Supported and using the ScriptManager Type to register the property instead. The following piece of code uses Reflection to figure out whether partial rendering is supported. internal bool IsPartialRenderingSupported { get { if (!this.PartialRenderingChecked) { Type scriptManagerType = BuildManager.GetType(“System.Web.UI.ScriptManager”, false); if (scriptManagerType != null) { object obj2 = this.Page.Items[scriptManagerType]; if (obj2 != null) { PropertyInfo property = scriptManagerType.GetProperty(“SupportsPartialRendering”); if (property != null) { object obj3 = property.GetValue(obj2, null); this.IsPartialRenderingEnabled = (bool)obj3; } } } this.PartialRenderingChecked = true; } return this.IsPartialRenderingEnabled; } } private bool PartialRenderingChecked { get { object val = ViewState["PartialRenderingChecked"]; if (val != null) return (bool)val; return false; } set { ViewState["PartialRenderingChecked"] = value; } } private bool IsPartialRenderingEnabled { get { object val = ViewState["IsPartialRenderingEnabled"]; if (val != null) return (bool)val; return false; } set { ViewState["IsPartialRenderingEnabled"] = value; } } Conclusion The Page.ClientScript.RegisterExpandoAttribute may not work in Partial Rendiring mode, when a postback is performed via triggering, to get this work we need to determine whether partial rendering is supported and use the ScriptManager Type instead like described above. Hope this helps, and saves some of your time, Thank you for being with me so far. posted @ Tuesday, June 17, 2008 5:43 AM | Feedback (0) | Wednesday, June 11, 2008 C# 3.0 tips, Automatic Property Declaring a property in C# 3.0 is super easy and super short. public class Student { public string Name { get; set; } } yes that’s it, the framework will take care of the rest, the private variables will be automatically created and the getter and setter will be automatically implemented. Here is how we can assign value to an automatic property via the constructor public class Student { public string Name { get; set; } public Student (string name) { this.Name = name; } } And finally, here is how we can declare a Readonly property public class Student { public string Name { get; private set; } public Student (string name) { this.Name = name; } } Hope this helps, Enjoy coding. posted @ Wednesday, June 11, 2008 12:21 AM | Feedback (0) | Monday, June 09, 2008 System.Net.WebClient().DownloadString(url) for Web Scrapeing WebRequest is the abstract base class for the .NET Framework’s request/response model for accessing data from the Internet. To get content of a website, in .NET 1.0. we used to use WebRequest, which is good and also works asynchronously. public static string GetContent(string url) { System.Net.WebRequest request = System.Net.WebRequest.Create(url); using (System.Net.WebResponse response = request.GetResponse()) { using (System.IO.StreamReader reader =new System.IO.StreamReader(response.GetResponseStream())) { return reader.ReadToEnd(); } } } But in .NET 2.0, we can also use the WebClient class. It can also work asynchronous and works the same as the other one. public static string GetContent(string url) { using (System.Net.WebClient client =new System.Net.WebClient()) { return client.DownloadString(url); } } We can use any of the above method for web scrapeing in .NET. But the second approach is probably more cleaner. posted @ Monday, June 09, 2008 3:28 PM | Feedback (0) | Friday, May 23, 2008 Project SOAK winner of 2008 Imagine Cup Australia. The theme of this year’s Imagine Cup is “Imagine a world where technology enables a sustainable environment.” It had been a great honorary for me to take part as one the Judges of the 2008 Imagine Cup Australia. All the teams worked real hard and came up with breathtaking solutions. The interesting part was solutions were built on top of cutting edge technologies technologies ie. Silverlight, Virtual Earth, LINQ, ASP.NET 3.5, WCF, .NET 3.5 and SQL2005. The Project SOAK is announced the winner of Australian Imagine Cup 08. SOAK which stands for “Smart Operational Agriculture toolKit” is an integrated hardware and softwares platform that aims to help farmers make the most of the water (and other) resources on their land. It does this through an integration of a wide range of sensors which gathers data about the environment in real time, provide rich visual information to end-user about the status of the farm, and electronically controls various systems such as sprinklers. The team members of SOAK blogged more about the project, can be found here http://davidburela.wordpress.com/2008/05/22/2008-imagine-cup-australian-winner-project-soak/ http://www.istartedsomething.com/20080523/imagine-cup-australian-winner-project-soak/ Australian PC Magazine has just published published a very insightful story on this project and the competition I found SOAK to be a brilliant piece of work, where the team combined latest technologies together and came up with a environment friendly cost effective solution. The winning team will now represent Australia at the World Imagine Cup finals in Paris in July. Imagine Cup 2008 – Australia – Judging Panel The Imagine Cup’s judging panel consisted of Roger Lawrence, Microsoft Australia’s Manager Developer Evangelism; Nigel Watson, Microsoft Australia Architect Evangelist; Shekhar Kalra, computer science lecturer at RMIT University; Shahed Khan, Senior Software Engineer at Ocean Informatics MVP C#.NET, and APC, represented by its editor, Tony Sarno. posted @ Friday, May 23, 2008 11:41 PM | Feedback (0) | Saturday, May 03, 2008 ASP.NET Bug, Multi View control do not save ViewState, of dynamically added controls Couple of days back me and my colleague, we discovered an issue with the ASP.NET Multi View Control. We were surprised to see that it do not add ViewState, of the dynamically added controls, of the Inactive Tabs. ASP.NET 2.0 <asp:MultiView ID=”MultiView1″ runat=”server” EnableViewState=”true”> </asp:MultiView> C# Code Behind if (!IsPostBack) { int index = 1; foreach (View v in MultiView1.Views) { TextBox t = new TextBox(); t.ID = index.ToString(); t.Text = “This text will not be assigned, to any Inactive Tabs, unless you put a breakpoint on this line and watch the value of this line explicitly“; v.Controls.Add(t); index = index + 1; } } Surprisingly, you will notice only the Textbox.Text of the Active Tab will have value, however if you go to any other Tab of the MultiViewControl, you will notice that the TextBoxes are empty. After investigating further we realized that the Viewstates of the dynamically added controls are not saved (for any of the inactive tabs). It became more interesting, when we started to debug, by putting a breakpoint to watch TextBox.Text, surprisingly every TextBox gets populated with desired Text ( for all tabs including the inactive tab, only when you explicitly watch ). Also it saves all ViewState correctly. Not sure whether its a bug, the ASP.NET team may have wanted this behavior to enhance performance of the Multi View control, but if that is the case, why does it populate the TextBox.Text and also saves into Viewstate, when we try to debug !!! Do not believe me? Try it by yourself !! posted @ Saturday, May 03, 2008 3:31 AM | Feedback (1) | Monday, April 28, 2008 ASP.NET in VISTA ( IIS7 ) with VS2005 or VS2008 The following 2 links may help. VS 2005: http://learn.iis.net/page.aspx/431/using-visual-studio-2005-with-iis-70/ VS 2008: http://learn.iis.net/page.aspx/387/using-visual-studio-2008-with-iis-70/ posted @ Monday, April 28, 2008 4:13 PM | Feedback (0) | Saturday, April 12, 2008 ASP.NET Tips: Using Image as Embedded Resource for ASP.NET CustomControl Problem I started writing an ASP.NET Custom Server Control, where I wanted an Image to be Embedded Resource of the Assembly itself, so that, I do not need to ship the images separately, but surprisingly it did not work straightway for me. The following line was not working for me: writer.AddAttribute(HtmlTextWriterAttribute.Src, Page.ClientScript.GetWebResourceUrl(typeof(MyControls.MyImageControl), “ferrari.jpg”)); Ok, lets elaborate what I did and what I missed, Step 1, I created my ClassLibrary project, added an Image, added a Custom Control class. Step 2, Made the image an embedded resource of the Assembly. Step3, Written my very simple Custom Control, where I assigned the image “src” to the WebResource URL Step 4, Then I wanted to tryout this CustomControl in my Test Website Step 5, But I got the following result. Solution After investigating a bit, I realized I missed some critical bits. 1. I did not put the correct Resource URL. I discovered this by opening up the assembly via Reflector, I found that the resource URL is different than what I have put in my code. I corrected the resource URL in my code, (but still it did not work). writer.AddAttribute(HtmlTextWriterAttribute.Src, Page.ClientScript.GetWebResourceUrl(typeof(MyControls.MyImageControl), “MyControls.images.ferrari.jpg”)); 2. I investigated further and found that I did not explicitly declare the image as WebResource in my assembly info . To get the embedded resource bit working, the following line is very important, and this solved my problem. [assembly: System.Web.UI.WebResource("MyControls.images.ferrari.jpg", "image/jpg")] Note: We can also put this directly in the class file itself. After the fix I got the following result as I have desired. Summary I have discussed here, how to embed image in an Assembly and how to use it as WebResource. Two points to note here, which are 1. After embedding a resource it is very important to explicitly declare itself as WebResource in the assembly, 2. We need correct resourceURL to access resouces from the assembly. Note: its case-sensitive as well. I hope this discussion will save you some time. Thank you for being with me so far. posted @ Saturday, April 12, 2008 8:45 PM | Feedback (0) | Saturday, March 22, 2008 DataTable to JSON and ToJSON() Extension Very recently I wrote an application where I had to deal with DataSet from a Web Service. Please note, I have no control on the Web Service and I ended up writing a small function which converts DataTable to JSON. I understand I haven’t gain anything on the web traffic, but it surely simplified my JavaScript programming. Let me go through what I did Step 1. Extract the XML Schema. DataTable has two handy methods to extract Xml and Xml Schema. I extracted the Xml Schema to be able to generate a C# class using the xsd.exe. string path = “Your File Path”; myDataTable.WriteXml(path); myDataTable.WriteXmlSchema(path); Step 2. Generate C# Class using Xsd.exe that ships with the .NET Framework. C:\temp>xsd mydatatable.xsd /l:cs /c Microsoft (R) Xml Schemas/DataTypes support utility [Microsoft (R) .NET Framework, Version 2.0.50727.42] Copyright (C) Microsoft Corporation. All rights reserved. Writing file ‘C:\temp\mydatatableclass.cs’. Step 3. DataTable to Object conversion The Web Service returns DataSet/ DataTable, and I want to transform all data that I I receive in the DataTable, to an instance of the class that I just generated in the above step. Something like this: private T DataTableToT<T>(DataTable dataTable, T obj) { using (MemoryStream ms = new MemoryStream()) { dataTable.WriteXml(ms); Type thetype = obj.GetType(); XmlSerializer x = new XmlSerializer(thetype); ms.Position = 0; return (T)x.Deserialize(ms); } } The above method uses the WriteXml() to write the data of DataTable in to a MemoryStream, then using the XmlSerializer I deserialize the xml to a .NET object. Here is how we may use the this method: DataSet ds = WebService.GetDataSet(); DataTable myDataTable = ds.Tables[0]; MyDataTableClass obj = DataTableToT(myDataTable, new MyDataTableClass()); Step 4. Serialize .NET object to JSON We have done the hard part above, now we have .NET object so we have all the flexibility as you can imagine. I found that there is a handful amount of libraries which can serialize .Net Objects to JSON string ie. JavaScriptSerializer, DataContractJsonSerializer, JSON.NET etc. JavaScriptSerializer ships with System.Web.Extensions.dll and you can locate it under Namespace: System.Web.Script.Serialization. The following method returns JSON from a .NET object using JavaScriptSerializer. private string GetJSONUsingJavaScriptSerializer<T>(T obj) { JavaScriptSerializer serializer = new JavaScriptSerializer(); string json = serializer.Serialize(obj); return json; } DataContractJsonSerializer also does pretty much the same as above, it ships with .NET Framework 3.5 : System.ServiceModel.Web.dll, and you can locate this under Namespace: System.Runtime.Serialization.Json, But we need to decorate the class with DataContract and DataMember attributes. Example [DataContract] class Order { [DataMember] public int OrderID { get; set; } [DataMember] public DateTime OrderDate { get; set; } } and the following method can return a JSON string. private string GetJSONUsingDataContractJsonSerializer<T>(T obj) { using (MemoryStream ms = new MemoryStream()) { DataContractJsonSerializer serializer = new DataContractJsonSerializer(obj.GetType()); serializer.WriteObject(ms, obj); return Encoding.UTF8.GetString(ms.ToArray()); } } Conclusion Here we have discussed how we can easily transform a DataTable to JSON. Sometimes we do not have enough control over the Web Service, or we may need to invoke a legacy Web Service that returns DataSet/ DataTable. In those scenarios sometimes converting DataTable to JSON comes very handy in AJAX programming. In the above example I have shown plain vanilla .NET methods, but we can even take it further and implement Extention methods to return JSON string. Scott has shown in his blog how to produce JSON using JavascriptSerializer. Here I show how we can do the same using DataContractJsonSerializer. Example: public static string ToJSON<T>(this T obj) { using (MemoryStream ms = new MemoryStream()) { DataContractJsonSerializer serializer = new DataContractJsonSerializer(obj.GetType()); serializer.WriteObject(ms, obj); return Encoding.UTF8.GetString(ms.ToArray()); } } and then we will be able to use it like this on a order collection, string json = orders.ToJSON(); Hope this helps. posted @ Saturday, March 22, 2008 5:31 PM | Feedback (1) | Tuesday, March 18, 2008 Visual Studio 2005 Debugging in Vista Problem: I was trying to debug in Visual Studio 2005 in my new machine which came with Vista Home Premium. Surprisingly I found that none of the break points are touched and I cannot debug. I tried giving all types of permission to the folder but no luck. Solution: After googling a bit, I found that I needed to run VisualStudio2005 as an Administrator, and everything started working as I expected. So all I had to do is : Right-click the Visual Studio icon and select the “Run as administrator” option from the context menu. Hope this saves some of your time. posted @ Tuesday, March 18, 2008 2:39 PM | Feedback (1) | Thursday, February 21, 2008 Javascript Tips: Carefully use “this” when writing classes, else you may cause memory leak. Lets say we want to declare a class in Javascript, which is equivalent to the following C# class. public class Student { public string FirstName = ""; public string LastName = ""; public Student( string firstname, string lastname) { this.FirstName = firstname; this.LastName = lastname; } public string GetFullName() { return FirstName + LastName; } } <!– To write a similar class in JavaScript we can do something like the following [ but this will create memory leak, I am explaining that in a moment ] function Student ( firstname, lastname) { this.FirstName = firstname; this.LastName = lastname; this.GetFullName = function() { return this.FirstName + this.LastName; } } now in C# if we want to instantiate an object of Student and want to call the GetFullName() method, we do the following. Student student = new Student("Shahed", "Khan"); string fullname = student.GetFullName(); <!– and we can create as many objects as we want and call its methods, each of the object will maintain its own state, and all objects will use the same copy of the GetFullName() method. But Javascript has different behaviour when we do the following on the above Javascript class. var student = new Student("Shahed","Khan"); car fullname = student.GetFullName(); <!– In Javascript, functions are treated as variables as a result when we create a new object of Student it creates new sets of firstname, lastname and also a new copy of GetFullname method, as a result we are creating memoryleak. Do not worry too much, there is a workaround for this, lets redefine the class in a different way. function Student ( firstname, lastname) { this.FirstName = firstname; this.LastName = lastname; this.GetFullName = GetFullName; } function GetFullName() { return this.FirstName + this.LastName; } <!– Notice I have moved the GetFullName function out of the class, and for this tweaking all new objects of the Student class will share the same instance of of GetFullName method and avoid memory leak. Thank you for being with me so far. Updated 24th Feb =============== Laurent from Galasoft gave some good feedback, JavaScript object oriented should be done by modifying the prototype property of the object, and never by storing methods using the “this” keyword. The workaround provided above is not good practice, as it forces the use of a global function. We should always declare methods in JavaScript object like this: function Student(firstName, lastName) { this.firstName = firstName; this.lastName = lastName; } Student.prototype = { getFullName : function() { return this.firstName + ” ” + this.lastName; } } also note correct naming convension, ( Javascript follows Java notation not C#). For JavaScript best practices please refer to the work of Microsoft Silverlight team. posted @ Thursday, February 21, 2008 5:38 PM | Feedback (1) | Tuesday, February 19, 2008 Solving DNN deployment issues, Redirecting to localhost and Running DNN in a different port I was trying to host a small DNN application in one of our Server and I was facing couple of issues. Problem 1: The first problem I faced is it was always redirecting to localhost, whenever I tried http://domain.com/dnn it was redirecting to http://localhost/dnn as a result the site was un-accessible from outside. Solution This was easy to solve. 1. I needed to log in as host account. 2. Then I needed to go to the Admin > Site Settings page 3. And finally In the Portal Alias section I added a new Http Alias “domain.com/dnn” This solved my problem when I hosted the site in port 80. Problem 2: Now I tried to host the application in a different port 8080. I.e. http://domain.com:8080/dnn. and somehow when I clicking to redirect to any other page the port started to disappear. The http://domain.com:8080/ automatically turned to http://domain.com/ . Solution After googling and looking at the web.config carefully I found, its clearly documented in web.config that <!– set UsePortNumber to true to preserve the port number if you’re using a port number other than 80 (the standard) <add key=”UsePortNumber” value=”true” /> –> I tweaked my appsettings section and added the magic key <add key=”UsePortNumber” value=”true” /> Also I had to add a new Http Alias “domain.com:8080″ This solved my problem and started retaining the port for my http://localhost:8080 but not http://domain.com:8080. The http://domain.com:8080 was still turning to http://domain.com Note: I later discovered this was not a problem of DNN and the issue happened because of the setup of our router settings and port forwarding, which I’ll discuss next. Problem 3: Even after adding the “UsePortNumber” key it did not solve my problem Solution Our Router was Port Forwarding all traffic of 8080 to the port 80 of the machine where DNN app is hosted. I.e. 8080 –> 80. As a result even from a browser I as typing http://domain.com:8080 , the DNN Request object was getting http://domain.com and when DNN handlers and url rewriters spitting the reformatted url it was spitting http://domain.com. This was a big problem for me, initially I thought I would write a HttpHandler for 404 page not found, but soon realized it will never hit the server with the spitted Url so that didn’t work. Then I thought I would tweak the DNN handlers to handle this scenario, but later tweaked the IIS and Router to handle this. 1. In IIS I added, support for 8080 to my Default Website. 2. In Router instead of forwarding to port 80 I started forwarding 8080 to 8080. 3. Made sure that in my DNN Site Settings, I have added Http Alias “domain.com:8080/dnn”. Waaa la, This solved my issue. Hope this helps and Thank you for being with me so far. posted @ Tuesday, February 19, 2008 5:14 PM | Feedback (2) | Thursday, February 14, 2008 Dotnet Nuke Tips: Two common error while writing the SqlDataProvider Two common errors done while writing the SqlDataProvider SQL for Dotnet Nuke Modules are 1. Not saving the file that contains SqlDataProvider SQL codes in the correct format. A quick trick is to open the files in NotePad and save them as “Unicode”. 2. Not putting atleast 2 line breaks after each GO statement in the SQLDataProvider SQL code. Hope this helps. posted @ Thursday, February 14, 2008 1:00 PM | Feedback (0) | Wednesday, February 06, 2008 LINQ Tips: Implementing IQueryable Provider Check out the following from Matt Warrens blog posts, if you are interested on how to implement IQueryable Provider. source: http://blogs.msdn.com/mattwar/archive/2007/07/30/linq-building-an-iqueryable-provider-part-i.aspx Part I – Reusable IQueryable base classes Part II – Where and reusable Expression tree visitor Part II – Local variable references Part IV – Select Part V – Improved Column binding Part VI – Nested queries Part VII – Join and SelectMany posted @ Wednesday, February 06, 2008 5:50 PM | Feedback (0) | Tuesday, February 05, 2008 LINQ Tips: Querying ArrayList via LINQ Problem If you try to query an ArrayList via LINQ you might be surprised to see that its not supported and throwing an exception. In other words the following query will not work at all. ArrayList students = GetStudents(); var query = from student in students where student.Score > 80 select new { student.ID, student.Name }; Cause The problem comes from the fact that LINQ to Objects has been designed to query generic collections that implement the System.Collections.Generic.IEnumerable<T> interface. Where an ArrayList is a nongeneric collection that contains a list of untyped objects and also does not implement IEnumerable<T>. Solution Cast operator comes into rescue. Here is the signature of the Cast operator: public static IEnumerable<T> Cast<T>(this IEnumerable source) Cast can take a nongeneric IEnumerable and returns a generic IEnumerable<T>. A modification of the above query like this will solve the problem and you will be able to query ArrayList via LINQ. ArrayList students = GetStudents(); var query = from student in students.Cast<Student>() where student.Score > 80 select new { student.ID, student.Name }; posted @ Tuesday, February 05, 2008 4:30 PM | Feedback (1) |
	Powered by: Copyright © Shahed Khan

<!–
//

Oracle Techniques by Sameer Wadhwa ( Analytic Functions)

borrowed from : http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Ranked_func_bk

xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:w=”urn:schemas-microsoft-com:office:word”
xmlns=”http://www.w3.org/TR/REC-html40″>

 

id=”_x0000_t136″ coordsize=”21600,21600″ o:spt=”136″ adj=”10800″ path=”m@7,0l@8,0m@5,21600l@6,21600e”>

o:connectangles=”270,180,90,0″/>

strokeweight=”1.5pt”>

fitpath=”t” string=”Analytical functions”/>
     

style=’mso-tab-count:13′>                                                                                                                                                     

 

                                                                                    style=’color:maroon’>SAMEER WADHWA

                                                                        style=”mso-spacerun: yes”>       Wadhwa_S@Hotmail.com

                                                           

In this article I have 
tried to aware you about some of the analytic functions provided by oracle
8i.These funtions are very powerful and ease to use.

 

style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:Symbol;
color:blue;mso-bidi-font-weight:bold’>·        
href=”http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Rollup_and_cube_bk”>ROLLUP
AND CUBE AGGREGATE FUNCTIONS

style=’mso-bidi-font-weight:normal’>·        
href=”http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Ranked_func_bk”>RANKED
FUNCTION

style=’mso-bidi-font-weight:normal’>·        
href=”http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Case_bk”>CASE

style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:Symbol;
color:blue;mso-bidi-font-weight:bold’>·        
href=”http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Lag_and_lead_bk”>LAG
AND LEAD FUNCTION

style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:Symbol;
color:blue;mso-bidi-font-weight:bold’>·        
href=”http://www.samoratech.com/topicofinterest/swAnalyticalFuntions.htm#Ratio_to_report_bk”>RATIO_TO_REPORT

 

 

 

                                    name=”Rollup_and_cube_bk”>ROLLUP AND CUBE
AGGREGATE FUNCTIONS

 

To understand the power of ROLLUP and CUBE functions ,consider the
following SQL statement :-

 

ora816 SamSQL :> compute sum of totsal on deptno

ora816 SamSQL :> break on deptno

ora816 SamSQL :> select deptno,job,sum(sal) totsal from emp group
by deptno,job;

 

style=”mso-spacerun: yes”>    DEPTNO JOB           TOTSAL

———-
——— ———-

style=”mso-spacerun: yes”>        10 CLERK           1300

style=”mso-spacerun: yes”>           MANAGER         2450

style=”mso-spacerun: yes”>           PRESIDENT       5000

********** style=”mso-spacerun: yes”>           ———-

sum style=”mso-spacerun: yes”>                        8750

style=”mso-spacerun: yes”>        20 ANALYST         6000

style=”mso-spacerun: yes”>           CLERK           1900

style=”mso-spacerun: yes”>           MANAGER         2975

********** style=”mso-spacerun: yes”>           ———-

sum style=”mso-spacerun: yes”>                       10875

style=”mso-spacerun: yes”>        30 CLERK            950

style=”mso-spacerun: yes”>           MANAGER         2850

style=”mso-spacerun: yes”>           SALESMAN        5600

********** style=”mso-spacerun: yes”>           ———-

sum style=”mso-spacerun: yes”>                        9400

 

Now see the use of ROLLUP Function

 

ora816 SamSQL :> select deptno,job,sum(sal) totsal from emp
group by ROLLUP(deptno,job);

 

style=”mso-spacerun: yes”>    DEPTNO JOB           TOTSAL

———-
——— ———-

style=”mso-spacerun: yes”>        10 CLERK           1300

style=”mso-spacerun: yes”>        10 MANAGER         2450

id=”_x0000_t88″ coordsize=”21600,21600″ o:spt=”88″ adj=”1800,10800″ path=”m0,0qx10800@0l10800@2qy21600@11,10800@3l10800@1qy0,21600e”
filled=”f”>

textboxrect=”0,@4,7637,@5″/>

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>        10 PRESIDENT style=”mso-spacerun: yes”>       5000

style=”mso-spacerun: yes”>        10                 8750   Total of Deptno 10

style=”mso-spacerun: yes”>        20 ANALYST         6000

style=”mso-spacerun: yes”>        20 CLERK           1900

style=”mso-spacerun: yes”>        20 MANAGER         2975

style=”mso-spacerun: yes”>        20                 style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:red’>10875

style=”mso-spacerun: yes”>        30 CLERK            950

style=”mso-spacerun: yes”>        30 MANAGER         2850

style=”mso-spacerun: yes”>        30 SALESMAN        5600

type=”#_x0000_t88″ style=’position:absolute;left:0;text-align:left;
margin-left:297pt;margin-top:5.55pt;width:9pt;height:18pt;z-index:6′>

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>        30 style=”mso-spacerun: yes”>                 9400

style=”mso-spacerun: yes”>                          style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:red’>29025   Grand Total

 

 

So if you compare the two output you will notice that you are getting
the same output.  By using rollup
you can avoid compute and break clausesfrom SQL. style=”mso-spacerun: yes”>  This will mostly helpful in style=”mso-spacerun: yes”>  PL/SQL 
.  We do not have to put logic
for computing values on break of groups.

 

 

Now see the use of
CUBE  Function

 

ora816
SamSQL :> select deptno,job,sum(sal) totsal from emp group by
CUBE(deptno,job);

 

Fri Mar 23

style=”mso-spacerun:
yes”>                                                          NuGenesis
Report

 

style=”mso-spacerun: yes”>    DEPTNO JOB           TOTSAL

———-
——— ———-

style=”mso-spacerun: yes”>        10 CLERK           1300

style=”mso-spacerun: yes”>        10 MANAGER        
2450

type=”#_x0000_t88″ style=’position:absolute;left:0;text-align:left;
margin-left:4in;margin-top:8.25pt;width:9pt;height:18pt;z-index:4′>

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>        10 PRESIDENT style=”mso-spacerun: yes”>       5000

style=”mso-spacerun: yes”>        10                 8750   Total of Deptno 10

style=”mso-spacerun: yes”>        20 ANALYST         6000

style=”mso-spacerun: yes”>        20 CLERK           1900

style=”mso-spacerun: yes”>        20 MANAGER         2975

style=”mso-spacerun: yes”>        20                10875

style=”mso-spacerun: yes”>        30 CLERK            950

style=”mso-spacerun: yes”>        30 MANAGER         2850

style=”mso-spacerun: yes”>        30 SALESMAN        5600

style=”mso-spacerun: yes”>        30                 9400

type=”#_x0000_t88″ style=’position:absolute;left:0;text-align:left;
margin-left:4in;margin-top:2.95pt;width:18pt;height:54pt;z-index:2′>

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>           ANALYST style=”mso-spacerun: yes”>         6000 style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>

style=”mso-spacerun: yes”>           CLERK           4150 style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>

style=”mso-spacerun: yes”>           MANAGER         8275    Total w.r.t JOB

style=”mso-spacerun: yes”>           PRESIDENT        style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:maroon’>5000

style=”mso-spacerun: yes”>           SALESMAN        5600 style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>

id=”_x0000_t87″ coordsize=”21600,21600″ o:spt=”87″ adj=”1800,10800″ path=”m21600,0qx10800@0l10800@2qy0@11,10800@3l10800@1qy21600,21600e”
filled=”f”>

textboxrect=”13963,@4,21600,@5″/>

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:#3366FF’>            style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”;
color:red’>Grand Total   29025

 

Cube also do a total with respect to second group
for example JOB in our case . Also at end you will see the grand total

 

 

Conclusion : Rollup and
Cube are the aggregate function which allows developers and dbas to avoid
compute and break clauses and simplify logic of programming

 

name=”Ranked_func_bk”>Ranked Function in 8i (816) style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;font-family:”Times New Roman”;
mso-fareast-font-family:”MS Mincho”;color:red’>

 

Suppose you have a data in table which you want to rank in a specified
order for example you have a table test and you want to rank a value of repcol.

 

                                               style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>Ora816 SamSQL> select * from test;

 

REPCOL         
VALUE

———-   
———-

A                
100

A            style=”mso-spacerun: yes”>     200

A                
300

B               
1000

B                
900

B                
800

A                
500

B                
400

B                
500

 

Ora816
SamSQL> select repcol,value, style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>rank() over ( partition by repcol

style=”mso-spacerun: yes”>                2 style=”mso-spacerun: yes”>  order
by value desc ) ranked_value

style=”mso-spacerun: yes”>           style=”mso-spacerun: yes”>     3 
from test;

 

REPCOL         
VALUE RANKED_VALUE

———- ———- ————

A                
500            1

A                
300            2

A                
200            3

A                
100            4

B  style=”mso-spacerun: yes”>              1000            1

B                
900            2

B                
800            3

B                
500            4

B                
400            5 style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>

 

The
above value is ranked by the rank function provided by 8.1.6 style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>

 

 

style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;font-family:”Times New Roman”;
mso-fareast-font-family:”MS Mincho”;color:red’>Use of Case in SELECT style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;font-family:”Times New Roman”;
mso-fareast-font-family:”MS Mincho”;color:red’>

 

Case Statement are similar to decode , it is more
flexible and gives better performace

 

Ora816
SamSQL>  ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select sum(case style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> repcol=’A’ then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) sum_of_A,

style=”mso-spacerun: yes”>           style=”mso-spacerun: yes”> 2        
sum(Case style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> repcol=’B’ then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Sum_of_B,

style=”mso-spacerun: yes”>  3        
sum(case style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>value = 500 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 1 else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Value_Eq_500,

style=”mso-spacerun: yes”>  4        
sum(case style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value > 100 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>1 else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Value_Gre_100

style=”mso-spacerun: yes”>  5* 
from test

Ora816
SamSQL>  /

 

  SUM_OF_A style=”mso-spacerun: yes”>   SUM_OF_B VALUE_EQ_500 VALUE_GRE_100

———- ———- ———— ————-

      1100 style=”mso-spacerun: yes”>       3600            2             8

 

Use of Group by in CASE

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select repcol,sum(case when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> repcol=’A’ then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) sum_of_A,

style=”mso-spacerun: yes”>  2        
sum(Case when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> repcol=’B’ then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Sum_of_B,

style=”mso-spacerun: yes”>  3        
sum(case when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value = 500 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 1 else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Value_Eq_500,

style=”mso-spacerun: yes”>  4        
sum(case when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value > 100 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>1 else style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> 0 end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) Value_Gre_100

style=”mso-spacerun: yes”>  5  
from test

style=”mso-spacerun: yes”>  6* group
by repcol

Ora816 SamSQL> /

 

REPCOL      
SUM_OF_A   SUM_OF_B VALUE_EQ_500
VALUE_GRE_100

———- ———- ———- ————
————-

A               
1100          0 style=”mso-spacerun: yes”>            1             3

B                  
0       3600 style=”mso-spacerun: yes”>            1             5

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select (case style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value between 100 and 300 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> ’100-300′

style=”mso-spacerun: yes”>  2      
when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value between 400 and 700 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> ’400-700′

style=”mso-spacerun: yes”>  3      
when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value between 800 and 900 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> ’800-900′

style=”mso-spacerun: yes”>  4      
when style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> value > 900 then style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> ‘>900′ end style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’>) VALUE_RANGE,

style=”mso-spacerun: yes”>  5       
count(*) as VALUE_COUNT

style=”mso-spacerun: yes”>  6      
from test

style=”mso-spacerun: yes”>  7 
group by

style=”mso-spacerun: yes”>  8 
(case when value between 100 and 300 then ’100-300′

style=”mso-spacerun: yes”>  9       
when value between 400 and 700 then ’400-700′

style=”mso-spacerun: yes”> 10       
when value between 800 and 900 then ’800-900′

style=”mso-spacerun: yes”> 11*      
when value > 900 then ‘>900′ end )

Ora816
SamSQL> /

 

VALUE_R VALUE_COUNT

——- ———–

100-300          
3

400-700          
3

800-900          
2

>900             
1

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1  select
(case when value between 100 and 300 then ’100-300′

style=”mso-spacerun: yes”>  2      
when value between 400 and 700 then ’400-700′

style=”mso-spacerun: yes”>  3      
when value between 800 and 900 then ’800-900′

style=”mso-spacerun: yes”>  4      
when value > 900 then ‘>900′ end) VALUE_RANGE,value

style=”mso-spacerun: yes”>  5*     
from test

Ora816
SamSQL> /

 

VALUE_R     
VALUE

——- ———-

100-300       
100

100-300       
200

100-300       
300

>900         
1000

800-900       
900

800-900       
800

400-700       
500

400-700       
400

400-700       
500

 

9 rows selected.

 

style=”mso-spacerun: yes”> Lag and Lead
Functions

 

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select repcol,

style=”mso-spacerun: yes”>  2 
value,

style=”mso-spacerun: yes”>  3  style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>lag(value, style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>1) style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>over ( style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>order by repcol) lag_value ,

style=”mso-spacerun: yes”>  4  style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>lead(value, style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>1) style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>over ( style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>order by repcol) lead_value

style=”mso-spacerun: yes”>  5* from test

Ora816
SamSQL> /

 

REPCOL         
VALUE  LAG_VALUE LEAD_VALUE

———- ———- ———- ———-

A                
100                   200

A                
200        100 style=”mso-spacerun: yes”>        500

A                
500        200 style=”mso-spacerun: yes”>        300

A                
300        500 style=”mso-spacerun: yes”>       1000

B              style=”mso-spacerun: yes”>  1000       
300        500

B                
500       1000 style=”mso-spacerun: yes”>        900

B                
900        500 style=”mso-spacerun: yes”>        400

B                
400        900 style=”mso-spacerun: yes”>        800

B                
800        400

 

9 rows selected.

 

The LAG function provides access
to a row at a given offset prior to the position and the LEAD function provides
access to a row at a given offset after the current position.

The functions have the
following syntax:

{LAG | LEAD}   (, [ [, ]]) OVER       ([PARTITION BY [,...]]      ORDER BY  [collate clause>]      [ASC | DESC] [NULLS FIRST | NULLS LAST] [,...]) 

style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>is
an optional parameter and defaults to 1. is an optional parameter and
is the value returned if the falls outside the bounds of the table or
partition.

 

style=’font-size:12.0pt;mso-bidi-font-size:14.0pt;color:red’>Ratio_to_report style=’font-size:12.0pt;mso-bidi-font-size:14.0pt;color:red’>

The RATIO_TO_REPORT
function computes the ratio of a value to the sum of a set of values. If the
expression value expression style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>
evaluates to NULL, RATIO_TO_REPORT also evaluates to NULL, but it is treated as
zero for computing the sum of values for the denominator. Its syntax is:

RATIO_TO_REPORT() OVER        ([PARTITION BY [,...]]) 

style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>where

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Symbol;
mso-bidi-font-weight:bold;mso-bidi-font-style:italic’>· style=’font-size:7.0pt;mso-bidi-font-weight:bold;mso-bidi-font-style:italic’>        
and can be any valid
expression involving column references or aggregates.

style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Symbol;
mso-bidi-font-style:italic’>·         style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>The
PARTITION BY clause defines the groups on which the RATIO_TO_REPORT function is
to be computed. If the PARTITION BY clause is absent, then the function is
computed over the whole query result set style=’font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>. style=’font-size:11.0pt;mso-bidi-font-size:12.0pt;font-family:”Courier New”‘>

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select repcol,

style=”mso-spacerun: yes”>  2 
value,

style=”mso-spacerun: yes”>  3 
sum(value) over() style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:blue’> sumofvalue,

style=”mso-spacerun: yes”>  4  style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>ratio_to_report (sum(value)) style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>over() RATIO

style=”mso-spacerun: yes”>  5 
from test

style=”mso-spacerun: yes”>  6* group by repcol ,value

Ora816
SamSQL> /

 

REPCOL         
VALUE SUMOFVALUE      RATIO

———- ———- ———- ———-

A                
100       4700 .021276596

A                
200       4700 .042553191

A                
300       4700 .063829787

A                
500       4700 .106382979

B                
400       4700 .085106383

B                
500       4700 .106382979

B                
800       4700 .170212766

B            style=”mso-spacerun: yes”>     900      
4700 .191489362

B               
1000       4700 .212765957

 

9 rows selected.

 

Ora816
SamSQL> ed

Wrote
file afiedt.buf

 

style=”mso-spacerun: yes”>  1 
select repcol,

style=”mso-spacerun: yes”>  2 
value,

style=”mso-spacerun: yes”>  3  style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>sum(value) style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>over(partition by repcol) sumofvalue,

style=”mso-spacerun: yes”>  4  style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>ratio_to_report (sum(value)) style=’font-size:11.0pt;mso-bidi-font-size:10.0pt;mso-fareast-font-family:”MS Mincho”;
color:red’>over(partition by repcol) RATIO

style=”mso-spacerun: yes”>  5 
from test

style=”mso-spacerun: yes”>  6* group by repcol ,value

Ora816
SamSQL> /

 

REPCOL         
VALUE SUMOFVALUE      RATIO

———- ———- ———- ———-

A                
100       1100 .090909091

A                
200       1100 .181818182

A                
300       1100 .272727273

A                
500       1100 .454545455

B                
400       3600 .111111111

B                
500       3600 .138888889

B                
800       3600 .222222222

B                
900       3600        .25

B               
1000       3600 .277777778

 

9 rows selected.

 

Conclusion : These analytic functions are very powerful and very
useful.  Thanks to Oracle to provides us
such a great tool.

 

 

References:

 

Oracle Server concept manual

Oracle Datawarehouse manual.

 

Please feel free to send  comments or  feedback at style=’mso-fareast-font-family:”MS Mincho”;color:red’> href=”mailto:wadhwa_s@hotmail.com”>wadhwa_s@hotmail.com style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;font-family:”Times New Roman”;
mso-fareast-font-family:”MS Mincho”;color:red’>.

Thanks for reading this article style=’font-size:12.0pt;mso-bidi-font-size:10.0pt;font-family:”Times New Roman”;
mso-fareast-font-family:”MS Mincho”;color:blue’>.

SAMEER WADHWA

 

Copyright  2001 style=”mso-spacerun: yes”>  Sameer Wadhwa (All right reserved)

 

 

style=”mso-spacerun: yes”> 

height=1 id=”_x0000_i1030″ src=”swAnalyticalFuntions_files\serv.gif” alt=1>

 

 

Enhanced Aggregation, Cube, Grouping and Rollup | Oracle FAQ

borrowed from the site: http://www.orafaq.com/node/56

 

 

User login Login: * Password: * Create new account Request new password Search [ Advanced ] [ Help ] Site navigation About Articles / Blogs News Applications Developer Suite Enterprise Manager Java Operating Systems RDBMS Server SQL & PL/SQL Warehousing Blog Aggregator Books Directories Events FAQ’s Forums Mailing Lists Papers Scripts Tools Wiki XML Feeds Featured book Database Systems Using Oracle (2nd Edition) Similar entries How does one code a matrix/crosstab/pivot report in SQL? Drilling from OLAP to Relational using Discoverer 10.1.2 How does one SELECT, INSERT, UPDATE and DELETE data from PHP? How does one implement AutoNumber and Identity columns in Oracle? Oracle 10g New Features: DML Enhancements

Home » Articles / Blogs » Shouvik Basu’s blog Enhanced Aggregation, Cube, Grouping and Rollup Submitted by Shouvik Basu on Tue, 2005-03-01 00:00. SQL & PL/SQL   (OLAP reporting embedded in SQL) Much of the OLAP reporting feature embedded in Oracle SQL is ignored. People turn to expensive OLAP reporting tools in the market – even for simple reporting needs. This article outlines some of the common OLAP reporting needs and shows how to meet them by using the enhanced aggregation features of Oracle SQL. The article is divided in two sections. The first introduces the GROUP BY extensions of SQL, and the second uses them to generate some typical reports. A section at the end introduces the common OLAP terminologies. The enhanced SQL aggregation features are available across all flavors of Oracle including Oracle Standard Edition One. It might be worth mentioning here, that Oracle OLAP, the special OLAP package of Oracle, is not available with Oracle Standard Edition and Standard Edition One. Enhanced aggregation features discussed here have been tested on Oracle 9i and Oracle 10g. Advanced Aggregation Extensions of GROUP BY GROUPING SETS clause, GROUPING function and GROUPING_ID function The fundamental concept of enhanced aggregation features of Oracle is that of GROUPING SETS. All other aggregation features can be expressed in terms of it. With GROUPING SETS clause comes the functions GROUPING, GROUPING_ID and GROUP_ID. The GROUPING SETS clause in GROUP BY allows us to specify more than one GROUP BY options in the same record set. All GROUPING clause query can be logically expressed in terms of several GROUP BY queries connected by UNION. Table-1 shows several such equivalent statements. This is helpful in forming the idea of the GROUPING SETS clause. A blank set ( ) in the GROUPING SETS clause calculates the overall aggregate. Table 1 – GROUPING SET queries and the equivalent GROUP BY queries Set A – Aggregate Query with GROUPING SETS Set B – Equivalent Aggregate Query with GROUP BY A1. SELECT a, b, SUM(c) FROM tab1 GROUP BY GROUPING SETS ( (a,b) ) B1. SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b A2. SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a,b), a) B2. SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a A3. SELECT a,b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS (a,b) B3. SELECT a, null, SUM( c ) FROM tab1 GROUP BY a UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY b A4. SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a, b), a, b, ( ) ) B4. SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b UNION SELECT null, null, SUM( c ) FROM tab1 Example (Table-1 Set 4) is like a superset of all the above cases and also includes an overall aggregate by the use of ( ). We will see latter that this result is similar to that of CUBE (a, b). The first 3 columns of Table-2 show the result of a query of this type. GROUPING clause uses a single scan to compute all the required aggregates. So the performance is better than its logical equivalent of several GROUP BY and UNION. The general syntax of a SQL with GROUPING SETS is - SELECT <grouping_columns>, <aggregate_functions> FROM <table_list> WHERE <where_condition> GROUP BY GROUPING SETS (<column_set_1>, … , <column_set_N> The “column sets” can have none, one or more “grouping column” from SELECT. However, all columns from the select should be present in at least one of the column sets. In mathematical terms - UNION UNION should be equal to So the following two queries below will return error - (1) SELECT a, b, c, SUM(d ) FROM tab1 GROUP BY GROUPING SETS ( (a,b), b) — Reason (a,b) U ( b ) is not equal to (a,b,c) (2) SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS (a, ( ) ) — Reason (a) U ( ) is not equal to ( a, b ) Table 2 – A GROUPING SET query with GROUPING and GROUPING_ID Function on EMP SELECT deptno, job, SUM(sal), GROUPING(deptno) GDNO, GROUPING (job) GJNO, GROUPING_ID(deptno, job) GID_DJ, GROUPING_ID(job, deptno) GID_JD FROM EMP GROUP BY GROUPING SETS ( (deptno, job), deptno, job, ( )) DEPTNO JOB SUM(SAL) GDNO GJNO GID_DJ GID_JD ———- ——— ———- ———- ———- ———- ———- 10 CLERK 1300 0 0 0 0 10 MANAGER 2450 0 0 0 0 10 PRESIDENT 5000 0 0 0 0 20 CLERK 1900 0 0 0 0 20 ANALYST 6000 0 0 0 0 20 MANAGER 2975 0 0 0 0 30 CLERK 950 0 0 0 0 30 MANAGER 2850 0 0 0 0 30 SALESMAN 5600 0 0 0 0 10 8750 0 1 1 2 20 10875 0 1 1 2 30 9400 0 1 1 2 ANALYST 6000 1 0 2 1 CLERK 4150 1 0 2 1 MANAGER 8275 1 0 2 1 PRESIDENT 5000 1 0 2 1 SALESMAN 5600 1 0 2 1 29025 1 1 3 3 18 rows selected. GROUPING Function and GROUPING_ID Function From Table-2 we see that when aggregates are displayed for a column its value is null. This may conflict in case the column itself has some null values. There needs to be some way to identify NULL in column, which means aggregate and NULL in column, which means value. GROUPING function is the solution to that. This function returns a flag “1″ for a row in the result set if that column has been aggregated in that row. Otherwise the value is “0″. There can be only one column expression as the argument of the GROUPING function and that column should also be in the SELECT. GROUPING function can be used to substitute the NULL value, which usually appears in columns at the aggregation level by something meaningful like Total. GROUPING function has the general syntax of GROUPING ( ). It is used only in SELECT clause. It takes only a single column expression as argument. GROUPING_ID takes a set of columns. It applies the GROUPING function on each column in its argument and composes a bit vector with the “0″ and “1″ values. It returns the decimal equivalent of the bit vector. The columns GID_DJ and GID_JD show the use of GROUPING_ID function and also show how interchanging the order of the columns inside the GROUPING_ID function might impact the result. CUBE This is the most generalized aggregation clause. The general syntax is CUBE ( ). It is used with the GROUP BY only. CUBE creates a subtotal of all possible combinations of the set of column in its argument. Once we compute a CUBE on a set of dimension, we can get answer to all possible aggregation questions on those dimensions. Table-3 shows a cube building. It might be also worth mentioning here that GROUP BY CUBE( a, b, c) is equivalent to GROUP BY GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )). ROLLUP ROLLUP clause is used with GROUP BY to compute the aggregate at the hierarchy levels of a dimension. ROLLUP(a, b, c) assumes that the hierarchy is “a” drilling down to “b” drilling down to “c”. ROLLUP (a, b, c) is equivalent to GROUPING SETS ( (a, b, c), (a, b), (a), ( )). The general syntax of ROLLUP is ROLLUP( ) Composite Columns A composite column is a collection of columns that can be used in CUBE or ROLLUP. They are treated as unit before computing the aggregate.Composite columns usage in CUBE and ROLLUP and the equivalent GROUPING SETS - . CUBE( (a, b), c) is equivalent to GROUPING SETS ( (a, b, c), (a, b) , c, ( )) . ROLLUP ( a, (b, c) ) is equivalent to GROUPING SETS ( (a, b, c), ( a ), ( ) ) Partial GROUPING SETS, CUBE or ROLLUP If any column appears in GROUP BY but outside the aggregation clauses discussed above. It can be thought of as being first column of the resulting GROUPING SET equivalent. The following examples make this clear. GROUP BY a, CUBE( b, c) is equivalent to GROUP BY GROUPING SETS ( (a, b, c), (a, b), (a, c), (a) ) GROUP BY a, ROLLUP( b, c) is equivalent to GROUP BY GROUPING SETS ( (a, b, c), (a, b), (a) ) OLAP Reporting using enhanced aggregation features While the queries on the EMP table are used to illustrate the GROUPING SETS they will be poor examples for discussing the next sections. The reason is that the tables are not in a Star-Schema format. Please run the script (Script A) to get a simple Star Schema. The tables of the Sample Schema are Product(prdid, prd_name, prd_family) TimeByDay(datekey, td_month, td_quarter, td_year) Location( Loc_id, City, State, Country) Customer(cust_id, cust_name, cust_type); Sales(sales_id, cust_id, loc_id, prdid, sales_date, amount); The schema is about a fictitious Art Trader that supplies remakes of statues of famous historical figures (like ALEXANDER, BUDDHA, etc) or landscape paintings of places (like SIKKIM, etc). They sell to museums, resellers or individuals. The dimensions are Product, TimeByDay, Location and Customer. The fact is Sales. The hierarchies are - (1) Product_Name (prd_name) -> Product Family (prd_family) (2) Date (datekey) -> Month (td_month) -> Quarter (td_quarter) -> Year (td_year) (3) City -> State -> Country (4) Customer_Name (cust_name) -> Customer_Type (cust_type) The two approaches used for generating OLAP reports are as follows - (1) Get the most generalized possible CUBE built with the dimensions, or (2) Use on the fly aggregation queries to get the real-time report. Using generalized pre-built CUBE for CUBE, ROLLUP, Drill Down and Slicing Queries. This approach consists of building a table or a materialized view with the CUBE of the dimensions. Table-3 shows the SQL to build such a cube. The generalized CUBE keeps all possible meaningful aggregation pre-computed. We need to query some of the rows of the CUBE to get the desired values. Since the CUBE stores all possible permutations of the dimensions there is a chance that the number or records in the cube itself might be large. Intelligent use of composite columns might help a great deal here. Note the use of composite columns (City, State) in the CUBE. This is because each state has got only one city with the office of our demo organization. The GROUPING_ID function helps to achieve the ROLLUP. For example, take the combination (cust_name, cust_type). It is meaningless to make the cube perform aggregations for customer types across customer names. So we include only the bit vectors (1,1), (1, 0) and (0,0) that is GROUPING_ID of 3, 2 and 0 on the customer dimension. Table 3 – Building a cube CREATE TABLE sales_cube AS SELECT prd_name, prd_family, datekey, td_month, td_quarter, td_year, cust_name, cust_type, city, state, country, GROUPING_ID (prd_name, prd_family) GID_product, GROUPING_ID (datekey, td_month, td_quarter, td_year) GID_DATE, GROUPING_ID (cust_name, cust_type) GID_CUST, GROUPING_ID (city, state, country) GID_LOC, sum(amount) amount FROM sales, product, timebyday, location, customer WHERE sales.cust_id = customer.cust_id and sales.loc_id = location.loc_id and sales.sales_date = timebyday.datekey and sales.prdid = product.prdid GROUP BY CUBE ( prd_name, prd_family, datekey, td_month, td_quarter, td_year, cust_name, cust_type, (city, state), country ) HAVING ( GROUPING_ID (prd_name, prd_family) = 0 or GROUPING_ID (prd_name, prd_family) = 2 or GROUPING_ID (prd_name, prd_family) = 3) and ( GROUPING_ID (datekey, td_month, td_quarter, td_year) = 0 or GROUPING_ID (datekey, td_month, td_quarter, td_year) = 8 or GROUPING_ID (datekey, td_month, td_quarter, td_year) = 12 or GROUPING_ID (datekey, td_month, td_quarter, td_year) = 14 or GROUPING_ID (datekey, td_month, td_quarter, td_year) = 15) and ( GROUPING_ID (cust_name, cust_type) = 0 or GROUPING_ID (cust_name, cust_type) = 2 or GROUPING_ID (cust_name, cust_type) = 3) and ( GROUPING_ID (city, state, country) = 0 or GROUPING_ID (city, state, country) = 6 or GROUPING_ID (city, state, country) = 7 );The next table (Table-4) shows a typical crosstab query of sales for Product and Location. It shows the query and also how to generate a crosstab report out of it by using the function CROSSTAB (Script-B). The next examples show the query and cross-tab report and skips the PLSQL portion. The WHERE condition is determined by the bit vectors. We need - Details of product and details of customer – Both Product and Customer dimensions are all details. So GID_Product = bit vector (0,0) = 0. Same for GID_Cust. Summary of product and details of customer – Product is summarized fully, so GID_Product = bit vector (1,1) = 3. Details of product and summary of customer – Customer is summarized fully, GID_Customer = bit vector (1,1) = 3. Summary of product and summary of customer – Both Customer and products are summarized. Along with any of the above 4 conditions we need full summary or the rest of the dimensions. So GID_date = bit vector (1,1, 1,1) = 15 and GID_Loc = bit vector (1,1,1) = 7. Table-4 Crosstab Query on Product and Location (Query, Generation Routine and Result) /*********** The Query ***********/ SELECT prd_name, cust_name, amount FROM sales_cube WHERE ((GID_Product = 0 and GID_Cust = 0) or (GID_Product = 0 and GID_Cust = 3) or (GID_Product = 3 and GID_Cust = 0) or (GID_Product = 3 and GID_Cust = 3)) and GID_date = 15 and GID_LOC = 7; /*********** Generating the crosstab report ********/ set serveroutput on set lines 120 var tempstr varchar2(500) exec :tempstr := ”||- ‘SELECT cust_name, prd_name, amount’||chr(10)||- ‘FROM sales_cube’||chr(10)||- ‘WHERE ((GID_Product = 0 and GID_Cust = 0) or’||chr(10)||- ‘ (GID_Product = 0 and GID_Cust = 3) or’||chr(10)||- ‘ (GID_Product = 3 and GID_Cust = 0) or’||chr(10)||- ‘ (GID_Product = 3 and GID_Cust = 3)) and’||chr(10)||- ‘ GID_date = 15 and’||chr(10)||- ‘ GID_LOC = 7′; exec crosstab(:tempstr); *Customers * *———————- Products —————————–* ************ ALEXANDER BUDDHA CHANDRAGUPTA PURI BEACH SIKKIM –Total– ART HOUSE 0 0 0 500 750 1250 BARKER 5100 0 0 0 0 5100 JONES 0 0 0 2050 3500 5550 MAHAJATI 0 0 0 1000 0 1000 RATAN 0 5000 0 0 4000 9000 SMITH 9500 9000 900 0 0 19400 STONEWORK 850 800 6000 0 0 7650 –Total– 15450 14800 6900 3550 8250 48950Tables Table-5, Table-6 and Table-7 show TimeSales report and drill-down to the quarters of year 2003. Slicing is achieved by including WHERE condition in the query with desired values of the dimensions. Drill down is achieved by selection of proper value of GID_ type columns and by deciding the proper GROUPING value of all the dimensions at the particular level of drill down. Dicing is achieved by merely interchanging the first two columns of SELECT. Table-5 Year-Product Sales Report: Main (Query and Result) SELECT prd_name, td_year, amount FROM Sales_cube WHERE ((GID_Product = 0 and GID_date = 14) or (GID_Product = 0 and GID_date = 15) or (GID_Product = 3 and GID_date = 14) or (GID_Product = 3 and GID_date = 15)) and GID_Cust = 3 and GID_Loc = 7; ************ 2002 2003 –Total– ALEXANDER 5100 10350 15450 BUDDHA 6800 8000 14800 CHANDRAGUPTA 0 6900 6900 PURI BEACH 3550 0 3550 SIKKIM 0 8250 8250 –Total– 15450 33500 48950Table-6 Year-Product Sales Report:Drill Down to Quarters and Dicing Product and Time Dimensions (Query and Result) SELECT td_year||td_quarter, prd_name, amount FROM Sales_cube WHERE ((GID_Product = 0 and GID_date = 12) or (GID_Product = 0 and GID_date = 15) or (GID_Product = 3 and GID_date = 12) or (GID_Product = 3 and GID_date = 15)) and GID_Cust = 3 and GID_Loc = 7; ************ ALEXANDER BUDDHA CHANDRAGUPTA PURI BEACH SIKKIM –Total– 2002Q1 0 1000 0 2050 0 3050 2002Q2 5100 0 0 500 0 5600 2002Q4 0 5800 0 1000 0 6800 2003Q1 10350 0 6000 0 0 16350 2003Q2 0 0 0 0 750 750 2003Q3 0 8000 0 0 3500 11500 2003Q4 0 0 900 0 4000 4900 –Total– 15450 14800 6900 3550 8250 48950Table-6 Year-Product Sales Report:Slice of year 2003, Quarter level drill down (Query and Result) SELECT td_year||td_quarter, prd_name, amount FROM Sales_cube WHERE ((GID_Product = 0 and GID_date = 12) or (GID_Product = 0 and GID_date = 14) or (GID_Product = 3 and GID_date = 12) or (GID_Product = 3 and GID_date = 14)) and GID_Cust = 3 and GID_Loc = 7 and td_year = 2003; ************ ALEXANDER BUDDHA CHANDRAGUPTA SIKKIM –Total– 2003 10350 8000 6900 8250 33500 2003Q1 10350 0 6000 0 16350 2003Q2 0 0 0 750 750 2003Q3 0 8000 0 3500 11500 2003Q4 0 0 900 4000 4900Using on-the-fly aggregation queries for CUBE, ROLLUP, Drill Down and Slicing While using on-the-fly aggregation queries the cube is not pre-computed and we get the real time summary. However the performance is slower than querying pre-computed cubes. Several features (CUBE, ROLLUP, Composite Columns) discussed here can be used to generate the required aggregation levels. An important thing to ensure during executing on-the-fly queries is that, the query should not perform any useless aggregation. Proper use of the GROUPING functions is important. Conclusion Most of the OLAP tools will provide several additional features other than just reporting. There are user-friendly drag and drop interfaces which make drill-down, rollup, slicing, dicing happen on a mouse-click. Report generation and formatting is easier for someone who is not familiar with SQL. There are security features that restrict specific users from drilling down specific sections of the cube or viewing some specific cubes. When the requirements are just few canned OLAP reports or when simple custom GUI can be made to mask the SQLs, use of the enhanced aggregation features can be really effective. A large portion of the requirements do fall in the second category. Glossary and Scripts Script A Script to create Sample Tables for Reporting examples drop table product; drop table timebyday; drop table location; drop table customer; drop table sales; create table product (prdid number(2), prd_name varchar2(12), prd_family varchar2(10)); create table timebyday (datekey date, td_month char(3), td_quarter char(2), td_year char(4)); create table location (loc_id number(2), city varchar2(10), state varchar2(10), country varchar2(10)); create table customer (cust_id number(2), cust_name varchar2(10), cust_type varchar2(4)); create table sales (sales_id number(6), cust_id number(2), loc_id number(2), prdid number(2), sales_date date, amount number); insert into product values (1,’BUDDHA’,'STATUE’); insert into product values (2,’ALEXANDER’,'STATUE’); insert into product values (3,’CHANDRAGUPTA’,'STATUE’); insert into product values (4,’SIKKIM’,'PAINTING’); insert into product values (5,’PURI BEACH’,'PAINTING’); insert into location values (1, ‘KOLKATA’, ‘WB’,'INDIA’); insert into location values (2, ‘MUMBAI’,'MH’,'INDIA’); insert into location values (3, ‘SYDNEY’,'SA’,'AUSTRALIA’); insert into location values (4, ‘CHICAGO’,'IL’,'USA’); insert into customer values (1, ‘SMITH’, ‘INDV’); insert into customer values (2, ‘JONES’, ‘INDV’); insert into customer values (3, ‘BARKER’,'INDV’); insert into customer values (4, ‘ART HOUSE’,'ORGN’); insert into customer values (5, ‘STONEWORK’,'ORGN’); insert into customer values (6, ‘MAHAJATI’,'ORGN’); insert into customer values (7, ‘RATAN’,'INDV’); insert into sales values (1, 1, 4, 1, ’05-JAN-02′, 1000); insert into sales values (2, 2, 3, 5, ’1-MAR-02′, 2050); insert into sales values (3, 3, 4, 2, ’14-MAY-02′, 5100); insert into sales values (4, 4, 3, 5, ’22-JUN-02′, 500); insert into sales values (5, 5, 3, 1, ’15-OCT-02′, 800); insert into sales values (6, 6, 1, 5, ’10-NOV-02′, 1000); insert into sales values (7, 7, 1, 1, ’25-DEC-02′, 5000); insert into sales values (8, 1, 4, 2, ’18-FEB-03′, 9500); insert into sales values (9, 5, 3, 2, ’28-FEB-03′, 850); insert into sales values (10, 5, 4, 3, ’15-MAR-03′,6000); insert into sales values (11, 4, 4, 4, ’12-JUN-03′, 750); insert into sales values (12, 1, 4, 1, ’18-JUL-03′,8000); insert into sales values (13, 2, 3, 4, ’5-AUG-03′,3500); insert into sales values (14, 1, 4, 3, ’8-NOV-03′, 900); insert into sales values (15, 7, 2, 4, ’23-NOV-03′,4000); insert into timebyday select distinct sales_date, to_char(sales_date,’MON’), ‘Q’||to_char(sales_date,’Q'), to_char(sales_date,’YYYY’) from sales; commit;Script B Script to create procedure CROSSTAB drop table temp_tab1; create table temp_tab1 (col1 varchar2(12), col2 varchar2(12), col3 number); create or replace procedure crosstab(ip_qstr varchar2) is type c1_rec is record (col1 varchar2(14), col2 varchar2(14), col3 number); type c1_tab is table of c1_rec; c1_tab1 c1_tab; varstr1 varchar2(200) := ”; ho_value number; begin dbms_output.enable(1000000); execute immediate(‘truncate table temp_tab1′); execute immediate ip_qstr bulk collect into c1_tab1; for ii in 1..c1_tab1.LAST loop insert into temp_tab1 values (nvl(c1_tab1(ii).col1,’–Total–’),nvl(c1_tab1(ii).col2,’–Total–’),c1_tab1(ii).col3); end loop; varstr1 := ‘************ ‘; for jj in (select distinct nullif(col2,’–Total–’) col2 from temp_tab1 order by 1 nulls last) loop varstr1 := varstr1||lpad(nvl(jj.col2,’–Total–’), 12)||’ ‘; end loop; dbms_output.put_line(varstr1); for ii in (select distinct nullif(col1,’–Total–’) col1 from temp_tab1 order by 1 nulls last) loop varstr1 := rpad(nvl(ii.col1,’–Total–’),12); for jj in (select distinct nullif(col2,’–Total–’) col2 from temp_tab1 order by 1 nulls last) loop begin select col3 into ho_value from temp_tab1 where col1 = nvl(ii.col1,’–Total–’) and col2 = nvl(jj.col2,’–Total–’); exception when no_data_found then ho_value := 0; end; varstr1 := varstr1||’ ‘||lpad(ho_value,12); end loop; dbms_output.put_line(varstr1); end loop; return; end crosstab; /OLAP Terminologies I am not considering in this article familiarity with OLAP terms as a prerequisite. Readers are advised to skip this section in case they are familiar with OLAP terminologies. Data Warehouse Huge Store of historical transaction data from various sources. Gradually keeps on growing as more and more data is loaded into it (history increases). Logical Table structure of a data warehouse is designed keeping in mind the parameters we would like to analyze them with. As such, the logical design has special forms like Star Schema or Snowflake Schema. The physical design is done kept in mind the ease of query. As such warehouse table structures are de-normalized and heavily indexed. ETL Stands Extract, Transform and Load. This is the technique to load the huge store of point (1) from the various sources. OLAP Engine OLAP stands for Online Analytical Processing, the technique of reporting from the data warehouse. Dimension and Measures Take the function F (a, b, c) = (x, y, z) where a, b, c, x, y, and z are several attributes. Function F can be thought of as a business behavior where (x, y, z) are measures of that behavior. The business needs to know how the measure of behavior F, i.e. x, y, z, varies with categories (a, b, c). Probably, with that knowledge the business will try to manipulate (a,b,c) to get the desired (x, y, z), or it might take several other decisions not expressible in such simple terms. (a, b, c) are relatively static well defined categories known as Dimensions. The measures (x, y, z) are also known as Facts. Dimension Table and Hierarchies Each dimension has a fixed (or slowly changing) domain of values, which is stored in the dimension table. Each dimension can have aggregate attributes defined at several levels known as hierarchy. Hierarchies of a given dimension are also stored in dimension tables. Let “a” be a dimension, let “a1″ be a collection of “a”, let “a2″ be a collection of “a1″. The dimension table can be (a, a1, a2). Fact Table The table where dimensions are linked to the measures is known as Fact table. In our example we can take the fact table as (a, b, c, x, y, z). The dimension attributes mentioned in the fact tables are the ones with lowest granularity. So the fact table, in our example, will not have a1 or a2, which are aggregates. Cube Cube stores pre-computed aggregations, such as sums and counts, across all dimensions. It is useful in answering multidimensional questions by avoiding frequent summary from the flood of detailed data. Drill Down Frequently the business user will start with the highest aggregation of the hierarchy and ask for details on one or some selected dimensions. This is known as drill down. Rollup The requirement might also be to start from the lower granularity of a particular dimension and then move to the higher aggregates. This activity is known as rollup. If the requirement is to view the aggregation across all the level of hierarchies of any given dimension, then such a report is also known as rollup report. Slice Selecting some values of a dimension based on a filtering condition and then applying aggregation is known as slicing. Dice Changing the outlook of the report table, i.e., interchanging the row labels and column labels is known as dicing. It might be mentioned here that dicing does not affect the values of the aggregate columns. » Shouvik Basu’s blog Login to post comments Submitted by Arnab (not verified) on Thu, 2005-04-28 13:05. This is an extremely good & useful article. However I have a few points to make. I have personally worked in a few OLAP tools like Cognos. In Cognos the Transformer is used for creating a multidimensional cube. The rolled up data is same as that using Oracle Provided CUBE function. However the difference is that it uses multipass queries instead of a single query. There are several complex business rules which are either impossible or really difficult to implement in a single query. Also OLAP tools have lots of other reporting features including dashboard, graphs, drill through etc. » Login to post comments Submitted by ila (not verified) on Thu, 2005-06-02 19:19. This is a very good artilcle. Any one looking for OLAP should try to use the oracle provided features before they look for other expensive OLAP softwares in the market. » Login to post comments Submitted by shouvik (not verified) on Sat, 2005-04-30 23:21. Indeed Arnab, I do not disagree with you. You can refer the last line of my article. But the tradeoff here is the cost of Cognos vs. the cost of native SQL PLSQL crosstab and graph in XL. » Login to post comments “Taken the bull by the Submitted by Ashwani Sehgal (not verified) on Fri, 2005-10-14 11:27. “Taken the bull by the horn” – Indeed » Login to post comments Explanation on extension to sql for aggregate Submitted by angelo cutolo (not verified) on Tue, 2006-09-26 09:10. EXCELLENT! You should do the same work with other obscure topics! Grazie. a.c. » Login to post comments

.:: Site Home :: Wiki Home :: Forum Home :: Privacy :: Contact ::.

 

Start of SSAS

 

Being an implementer of custom data warehouse and query engine ; i just required an erge to learn and develop the SSAS . In post a head i will suppose to post some basic concpets regrading SSAS.

Any help will be appreciable