Skip to content

Pivot Data in SQL and Display in a Flex Advanced Data Grid Part Two

So in part one of my Pivot adventure I have managed to get the data into the format i need to drive my application.

But I need to manipulate the way that the Advanced Data Grid works to handle the dynamic elements (i.e. the Dates) of my data set.

The end product looks like this. There are three separate grids in this image a Forecast Grid and a Confirmed Grid and finally a Usage Grid each has its own data set but those data sets have been gathered using similar methods to those explained in part one of this post.

However they all have the same base class with a slight variation on the static element which is the first column in each.

In all my Flex applications I use the Cairngorm framework, it took some getting used to but now I don’t know what I would do without it. It’s certainly worth learning.

So I have created a base class that extends the Advanced Data Grid and within the class I have created the following Column Groups.

public var begining:AdvancedDataGridColumnGroup;
public var dynamic:AdvancedDataGridColumnGroup;
 

Of these beginning will be my static element and dynamic is obviously the dynamic. From there we need to override the set dataProvider function.

Within this function we need to get hold of the First Object in our data set.

var firstObject:Object = ( value as ArrayCollection ).getItemAt(0);
 

and then create a new array collection to store our dynamic dates in.

var dates:ArrayCollection = new ArrayCollection();

Then we need to get hold of the properties for the firstObject.

var properties:Array = ObjectUtil.getClassInfo(firstObject).properties as Array;

For every element in properties I am going to make sure that there are no –‘s used in the dates from SQL and if there are replace them with a /. To do this I’m going to use a Regular Expression and a replace function on each element in the properties array and then take this date that has had the Regular Expression applied to it and insert it into the dates array. To do this I used the following for loop.

for each( var property:Object in properties )
{
                var myPattern:RegExp = /-/gi;
                var tidyDate:String = (property.localName as String).replace(myPattern, “/”);
                var num:Number = Date.parse( tidyDate );
                if( !isNaN(num) )
                {
                                var date:Date = new Date( num );
                                dates.addItem( date );
                }
}
 

So we have now applied our Regular Expression to the dynamic dates provided to us via SQL and populated the dates Array with those “tidyDates”

I then created two date formatters one for the Header Text and One for the Data Field like so.

var dateFormatter:DateFormatter = new DateFormatter();
dateFormatter.formatString = “YYYY/MM/DD”;
 
var headerDateFormatter:DateFormatter = new DateFormatter();
headerDateFormatter.formatString = “MMM-YY”;

and an array to store our dynamic columns.

var dynamicColumns:Array = new Array();

I then need to take the dates and put them into the dynamic Column Group I created earlier.Whilst populating this Column Group I will apply the two Date formatters and also a simple decimal place function.The decimal place function looks like this.

private function decimalPlaceFunction(item:Object, column:AdvancedDataGridColumn):String
                        {
                                    var numberFormatter:NumberFormatter = new NumberFormatter();
                                    numberFormatter.precision = 2;
                                    if(( item[column.dataField]) != null)
                                    {
                                                return numberFormatter.format( item[column.dataField] );
                                    }
                                    else
                                    {
                                                return numberFormatter.format( 0 );
                                    }
                                   
                        }
 

Back in the override dataProvider function I will use a for loop to populate the dynamic Column Group like so.

for each( var dateForColumn:Date in dates )
                                                {                                                         
var column:AdvancedDataGridColumn = new AdvancedDataGridColumn();
column.headerText = headerDateFormatter.format(dateForColumn);
column.dataField = dateFormatter.format(dateForColumn);
column.labelFunction = this.decimalPlaceFunction;
                                                            column.editable = false;
                                                            column.width = 145;
                                                            column.minWidth = 145;
                                                            dynamicColumns.push( column );
                                                }
                                                this.dynamic.children = dynamicColumns;
 

I then created an Array for storing the Group Columns created earlier and populated it with those groups.

 
var groups:Array = new Array();
                        groups.push( this.begining );
                        groups.push( this.dynamic );

And finally making these columns part of the Data Grid.

this.groupedColumns = groups;

To then populate the static element in the first column of our grid I used the following code.

protected function categoryLabelFunction( item:Object, column:AdvancedDataGridColumn):String
            {
                  trace(ObjectUtil.getClassInfo(this).name);
                  switch( ObjectUtil.getClassInfo(this).name )
                  {
                        case “Location in App of Forecast Grid”:
                              return “Forecast”;
                              break;
                        case “Location in App of Confirmed Grid”:
                              return “Confirmed”;
                              break;
                        case “Location in App of Usage Grid”:
                              return “Usage”;
                              break;
                        default:
                              return “”;
                  }
            }
 

So that’s how I managed to get the Advanced Data Grid to accept dynamic data to appear as columns. I am sure there are more elegant and less code heavy ways of doing this but I had to learn how to do this on the fly and It seems to work for me. I’m sure that some of the far more experienced developers could pick the bones out of this and create a much simpler solution. But for a 1st try I’m very happy with it.

The full code can be seen below.

    public class DynamicDatesBase extends AdvancedDataGrid
    {        [Bindable]
        protected var _modelLocator:ModelLocator = ModelLocator.getInstance();

        public var begining:AdvancedDataGridColumnGroup;
        public var dynamic:AdvancedDataGridColumnGroup;
        /*public var ending:AdvancedDataGridColumnGroup;*/

        public function DynamicDatesBase()
        {
            super();
            this.resizableColumns = false;
            this.draggableColumns = false;
        }

        private function decimalPlaceFunction(item:Object, column:AdvancedDataGridColumn):String
        {
            var numberFormatter:NumberFormatter = new NumberFormatter();
            numberFormatter.precision = 2;
            if(( item[column.dataField]) != null)
            {
                return numberFormatter.format( item[column.dataField] );
            }
            else
            {
                return numberFormatter.format( 0 );
            }

        }

        override public function set dataProvider(value:Object):void
        {
            super.dataProvider = value;

            if( value is ArrayCollection )
            {
                var firstObject:Object = ( value as ArrayCollection ).getItemAt(0);
                var dates:ArrayCollection = new ArrayCollection();

                var properties:Array = ObjectUtil.getClassInfo(firstObject).properties as Array;

                for each( var property:Object in properties )
                {
                    var myPattern:RegExp = /-/gi;
                    var tidyDate:String = (property.localName as String).replace(myPattern, “/”);
                    var num:Number = Date.parse( tidyDate );
                    if( !isNaN(num) )
                    {
                        var date:Date = new Date( num );
                        dates.addItem( date );
                    }
                }

                var dateFormatter:DateFormatter = new DateFormatter();
                dateFormatter.formatString = “YYYY/MM/DD”;

                var headerDateFormatter:DateFormatter = new DateFormatter();
                headerDateFormatter.formatString = “MMM-YY”;

                var dynamicColumns:Array = new Array();    

                for each( var dateForColumn:Date in dates )
                {                    
                    var column:AdvancedDataGridColumn = new AdvancedDataGridColumn();
                    column.headerText = headerDateFormatter.format(dateForColumn);
                    column.dataField = dateFormatter.format(dateForColumn);
                    column.labelFunction = this.decimalPlaceFunction;
                    column.editable = false;
                    column.width = 145;
                    column.minWidth = 145;

                    dynamicColumns.push( column );
                }

                this.dynamic.children = dynamicColumns;

                var groups:Array = new Array();

                groups.push( this.begining );
                groups.push( this.dynamic );
                /*groups.push( this.ending );*/

                this.groupedColumns = groups;

            }

        }

        protected function categoryLabelFunction( item:Object, column:AdvancedDataGridColumn):String
        {
            trace(ObjectUtil.getClassInfo(this).name);
            switch( ObjectUtil.getClassInfo(this).name )
            {
                case “Location in App of Forecast Grid“:

                    return “Forecast”;

                    break;
                case “Location in App of Confirmed Grid“:

                    return “Confirmed”;

                    break;
                case “Location in App of Usage Grid“:

                    return “Usage”;

                    break;
                default:

                    return “”;

            }
        }
    }
}

Pivot Data in SQL and Display in a Flex Advanced Data Grid Part One

My latest app called for six months worth of data to be displayed in a table with the dates appearing as column headings rather than as data in columns. The data itself is stored on a SQL 2008 server as flat data in a Data Warehouse. So some sort of PIVOT will be required. The date heading must be dynamic and change as each month passes with the oldest date dropping out and the other dates shuffling to the left to accommodate the newest column / date. The data grid must also contain certain data that is not affected by date. So static data if you will.

So I have two problems to deal with.

1. Pivot the date’s column into a Header row.

2. Create a data grid that can handle this dynamic section and then combine it with static (non date related data).

In this post I will deal with Getting the data ready for Flex and ColdFusion in Part Two I will deal with Handling the data in the Flex app itself.

I decided to start with a Stored Procedure to pivot the data ready for consumption in Flex and ColdFusion.

So first of all I need to convert the Date information from a datetime to a VARCHAR and apply some formatting to the Date to make it easier to read as a Column Heading. I then need to do this for every instance of data that appears in the set. I did this by doing the following.

I first of all declared a column variable.

declare @columns VARCHAR(8000)

and declare my date range which want to be 6 months worth of data. So that is 5 months of data previous to the current one.

declare @startDate varchar(50)

declare @endDate varchar(50)

set @startDate = dateadd(month,datediff(month,0,getdate())-5,0)

set @endDate = dateadd(month,datediff(month,0,getdate()),0)

then Convert and Coalesce the data from the table it is contained in where my date range applies and group them by the Date.

select @columns = COALESCE( @columns + ‘,[' + CONVERT( VARCHAR, MonthDate, 111 ) + ']‘, ‘[' + CONVERT( VARCHAR, MonthDate, 111) + ']‘ )

from dbo.Table_Name

where

MonthDate between @startDate and @endDate

group by MonthDate

Where the 111 formatting = yyyy/mm/dd

So with that bit of the stored procedure done my data that were in columns will now be headers when I pivot the data. So how did I do that.

I did it by creating a query string and executing that once the Convert and Coalesce and been completed.

declare @query VARCHAR(8000)

and setting the string to do the following

set @query = ‘select * from dbo.Table_Name PIVOT ( SUM(Tonnes) FOR [MonthDate] in (‘ + @columns + ‘)) as p WHERE Var1 LIKE ‘ + ”” + @InputVar1 + ”” + ‘ AND Var2 LIKE ‘ + ”” + @ InputVar2 + ”” + ‘ AND Var3 LIKE ‘ + ”” + @ InputVar3 + ”” + ”

Then executing it thus.

execute( @query )

Turning this flat data below

Into

So doing this has converted the MonthDate column into Column headers and summed up all the Values of the Tonnes Column for a specific Customer and Item within a six month window.

I can now get this out of SQL using a straight forward CF Function and present it to Flex.

The function is simple enough.

<cffunction access=”remote” name=”getForecastData” returntype=”query” >

<cfargument name=”Arg1″ type=”string” required=”yes” />

<cfargument name=”Arg2″ type=”string” required=”yes” />

<cfargument name=”Arg3″ type=”string” required=”yes” />

<cfstoredproc procedure=”sp_FLEX_myProcedure” datasource=”#dataSource#” >

<cfprocparam variable=”@InputVar1″ type=”in” cfsqltype=”cf_sql_varchar” value=”#arguments. Arg1 #” />

<cfprocparam variable=”@InputVar2″ type=”in” cfsqltype=”cf_sql_varchar” value=”#arguments. Arg2 #” />

<cfprocparam variable=”@InputVar3″ type=”in” cfsqltype=”cf_sql_varchar” value=”#arguments.Arg3 #” />

<cfprocresult name=”result” />

</cfstoredproc>

<cfreturn result>

</cffunction>

So that’s the data ready for Flex it took me a lot of trial and error to get to this point and it may not be the most elegant way of preparing the data for what I need it to do but it certainly works for me. In part Two I will deal with extending an Advanced Data Grid to handle the pivoted data section and a Static Data section too.

Coldfusion and Flex display 0E-20 Instead of a Zero

An application I have been developing over the last few months had recently gone into Test within the business and an odd glitch in the Data was brought to my attention.

A little background first, the Application has been developed for a company who wants a Web based tool for their external sales force to enable them to see Sales and Complaint data relevant to their Accounts prior to a site visit. The company has an extremely well developed almost bespoke ERP system in the shape of Microsoft Dynamics NAV but they do not want to give their Sales Reps access to this. Ours is not to reason why!

So with the data living in SQL and them already having a ColdFusion instance the way forward was clear.

So with that brief I chugged along and after a few revisions went back to them with an Application I felt was what they had requested. They agreed and it was passed to a few members of staff to check off the data and give it the Thumbs up for a go live.

After a week of testing reports were being sent to me of data that should have been a Zero being displayed as -20.

Odd!

I went to the SQL to check my views and Stored Procedures and sure enough the data did indeed display as a Zero. My SQL being rusty I was convinced the issue would have been there. But alas no!

See the code snippet for part of my SQL sp’s

So after that I looked at the ColdFusion functions I had created to extract the data from SQL and after invoking them and dumping the Data to screen. What was this?? Not a -20 but a 0E-20 ooo a NaN interesting!! But I’m handling NULL’s within the Stored Procedure.

Google save Me!!

After a mammoth internet trawling session and Stack Overflow being bombarded with CF \ SQL questions I was still stuck without an answer. Bugger!!

So back to my app I went and some major Data tests within CF.

It eventually became apparent the only time this would happen is if SQL had had to insert a Zero over a NULL ColdFusion would have a fit and kick out a NaN.

OK!

So in my simple brain if it’s a NULL it’s a Zero.

And Flex label functions to the rescue. It would be by no means a fix to my little issue but it was a sneaky work around. My favourite kind!!! Just ask anyone I work with. Mwuhahahaha.

So with LabelFunction to the rescue what was I going to do? Mmm.

Well lets double check that the data hitting Flex is a NaN and If it is lets return a Zero. If not show me the money baby.

So with the Code below applied to the Data Grid’s I can go back to sleeping and drinking Coffee my two Favourite pastimes on work time ha-ha.

Thanks for reading my rambles and I hope it may get you out of a similar bind.

Peace Out!!

New Year New Me

In June 2010 after several years of YoYo Dieting I had reached 25 and a half Stone. I realised it was time to do something about it, seeing that when I moved to the North East of England to go to University in 1995, I was 13 Stone! My self esteem was at an all time low and no doubt my health was suffering too.

I looked at lots of different diet plans and eventually decided to go with LighterLife. I spent a lot of time on the road with work and I needed something that was easy and convenient. It also had a period of ‘abstinence’ which seemed like a good way of wiping the slate clean and starting again. 

After a few weeks of getting my head in the right place and gearing myself up to do it I went along to a local LighterLife group in Stockton and signed up. I had to have a blood pressure test and general medical, it was a good job too I found out I had borderline high blood pressure and, according to the Practice Nurse ( I had to go to the doctors for the medical ) I was on my way to diabetes and heart disease. Blimey!!!!

So I started in earnest, the abstinence began with all my food replaced by just 4 Milkshakes a day. The first three days were bloody awful, all I could think about was food and eating. After that initial period it got easier, but I told my girlfriend “if I’ve only lost a couple of pounds Im packing it in” ( dedication eh lol!! ). I went to my 1st weigh in and nervously got on the scales 14lbs lost “Bloody Hell!!!” “A stone!”

Over the next 10 weeks the diet got easier and easier with the weight just falling off. Infact it became quite addictive trying to beat the previous weeks loss. But after the 10 weeks I had had enough and I needed food!

So instead of leaving me high and dry, LighterLife have a 12 week programme called ‘Route to Management’ where, over the course, real food, healthy food, food glorious food is reintroduced into your diet and a Milkshake is removed.

So Day One what would I be getting?!?!? Steak and Chips? Pie and Mash oooo I couldn’t wait. So what was it? 3 Milkshakes and a Tin of Tuna for tea. What!! Is that it??

Yup one tin of Tuna. But I have to say after 10 weeks of taking only Banana, Strawberry and Vanilla Milkshakes it turned out to be the tastiest tin of fish I have ever eaten. It was like a party on my tounge! Whoop.

Over the twelve weeks more and more food was introduced into my diet and at about 10 weeks in I had pretty much all food open to me.

So here I am at the end of the diet 7 and a half Stone later and feeling absolutely fantastic. as well as the weight I’ve lost 4 Inches off my neck, 12 Inches off my Waist and 10 Inches off my chest.

I would recommed it to anyone. I would say though make sure you are ready to do it. The first few weeks are tough but worth it Sooooo worth it.

 

Move Cancelled Arrghhhh

Well after announcing I was moving eHome to some space offered to me by my friends at http://www.regvardyband.co.uk I have had to cancel the move.

Due to circumstances beyond my control the move was just not a viable option. I want to thank the guys and gals there for the offer and the chance to give it a stab but it wasnt to be. So I have come back to what i know.

So stick with me as I have loads to whitter on about. Speak soon folks.

Im Moving

Well folks I thanks to my friends at the Reg Vardy Band I have moved to a selfhosted site. The webmaster at http://www.regvardyband.co.uk has given me some of thier webspace to use for which I am very grateful.

 

You can now follow me at http://www.regvardyband.co.uk/boozydoo

 

I hope to see you there.

A bad Buritto ruined my FLEX APPetite!

ColdFusion 8 Icon

Image via Wikipedia

Having recently watched some of the Adobe TV videos from MAX2010 my appetite for all things mobile was whetted.

So I made a decision to give the FLEX SDK ‘Hero’ release a look and I thought why not go the whole hog and get the pre release version of Buritto ( seeing as I’m still using Flex Builder 3 )

One of the features that stood out to me in the videos was the Data\Service Wizard. Granted I am a coder but this looked quite cool. A wizard that would help in connecting to my CFC’s, introspect them and help me configure the return types!! BOOM!! I’m there for some of that action.

So I down loaded the builder with the SDK. My grubby mits looking forward to getting coding.

But before I get into what happened after the install of Buritto I should at least give you an idea of the Dev environment I get to play with.

So all the data is stored in a Datawarehouse that’s sitting in SQL 2005 on a Windows 2008 Box. A VM I might add.

The Coldfusion instance is version 9 Enterprise Edition sitting on a 64 bit installation of windows 2008. This is also using IIS again this is a sweet VM.

So not too shabby a set up and its been running several corporate applications currently undergoing user testing written in FLEX 3.2 for a while now month probably.

All was rosy in the garden with my Guinea Pigs happily testing away.

So away I went with Buritto using the CFC’s I already had from a previous App I fired up the Data\Service Wizard and pointed it to my Coldfusion box and there they were my CFC’s ripe for the picking. So I clicked on one!

Error this CFC is invalid. Mmm odd I thought it definitely works because one of my apps uses it. But I shall try another one. Same error! Mmm this is odd.

So I went away and consulted Dave Arnold who came in and helped to set up our dev environment. He spotted that the wizard was trying at access the CFIDE section of the coldfusion instance. In the set up we had placed this in a https website to lock out remote admin of CF.

So after some jigery pokey and a reconfig of the http instance we managed to get the wizard to work. Fabaroony!!!!

So away I went and created a few demo apps and followed a few tutorials on Adobe TV. Great I though this is all really useful. However dark coulds were on the horizon. I started to get calls from my testers saying the apps in Test had started falling over.

I put my support hat on and dived into debug mode. After an initial dig around it appeared that Coldfusion was able to apply the Value Objects I had created but Flex was failing to do so! Mmm odd. All the code was fine and hadnt changed in weeks so what had happened? Well I was using Buritto but how could a client side tool affect that? Surely not.

Well I did some digging and discovered that the flex app was set to use variable and datafields in Camel Case but in the Variable Tab of the debugger they were all lower case. I checked Coldfusion and found that the case before it left for flex was correct. So something was stripping the upper case leffter from the variables.

After more digging and a lot of swearing I found that in the my-cfamf channel the case sesitivity settings had been overwritten. To ignore the case and knock it all down to lowercase. Very frustrating!! I changed this back and KAPOW!!! all the original apps started woring again but the apps created in Buritto stopped.

So i created a new channel for Buritto and set its case sensitivity settings back to what it required and gues what both group of apps now work Woo Hoo!!!

So after all that rambling be careful folks when installing Buritto it could affect your my-cfamf channel and if you use camel case in you variables and data sets you could get the problem I had.

So be safe and remember stay classy San Diego!

Follow

Get every new post delivered to your Inbox.