Sunday, February 9, 2014

Getting Excel Pivot Data into Microsoft Project as a Tree-Based Task Hierarchy

Background

Ever have denormalized data in Excel that you've made into a pivot so you can see what the data looks as a hierarchy?  Ever want to put that data into Microsoft Project so you can manage that structure as tasks?  I did today and figured out how today it.  What follows are the steps.

The original, denormalized data used as the data source of the Pivot Table.

Steps

In order to get your denormalized data as a hierarchy into Microsoft Project, follow these steps:

1. Create a pivot table


The standard pivot table in Excel.

2. Put the pivot table in "outline" mode


Displaying the pivot table in Outline format.

3. Paste the dimensional hierarchy data into MS Word


Selecting and copying only the dimensional (grouping/category data) for pasting into Word.


4. Paste the data from Word into MS Project


The dimensional data pasted into Word.

The dimensional data pasted from Word into MS Project.


Milestone: You have your hierarchy but still need the data


5. Paste the measure (numerical) data from Excel into MS Project


Selecting and copying only the numerical totals (measures) data for pasting into MS Project.

Pasting the measures data into MS Project.

You're done!

Enjoy!


Sunday, June 24, 2012

Including a Web API in knockout (going from hard coded to dynamic) (Part 2)

Continued from this post.

Now, to have my JavaScript talk to this URL and get some data that it can write to the screen.
I decided to watch more of the video.
Some Razor code that uses Upshot in the View. More on UpshotContext helper method:
@(Html.UpshotContext().DataSource<DataServiceController>(

Some fancy JavaScript also in the View that creates the ViewModel from Upshot for consumption by Knockout:
$(function() {
    function DeliveriesViewModel() {
        //Private
        var self = this;
        var dataSource = upshot.dataSources.DeliveriesForToday.sdkfl

        //public
        self.deliveries = dataSource.getEntities();
    }
});

The HTML template with Knockout attributes in it to manage the binding.
<ol data-bind="foreach:deliviries")>
    <li>
        <strong data-bind="text: Description" />
    </li>
</ol>

Steve explains that Upshot is the Model. The View is HTML (Razor in our case), and Knockout is the ViewModel, which contains application logic. Knockout uses "Observables", which are an interesting concept.

All good, but all I’m trying to do is take the code I have and get data from the server. So it has to be in JavaScript and it doesn’t have to be any fancier than that!

I knew that I wanted to have my API controller spitting out the same JSON that was presently hard-coded in the JavaScript, so I went and tweaked my C#. I was concerned that I was going to be spitting out XML, so I did more reading on Stack Overflow on how to initialize and spit out JSON. I was still stumped and ran into an good, recent Hanselman piece. I also ran into an article on how to have it spit out JSON by default but didn’t pursue it at the time as it wasn’t the most pressing. I decided to just let it fly and see what it would look like:
public class SportThing
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// GET api/values
public IEnumerable<SportThing> Get()
{
    return new List<SportThing> {
        new SportThing { Id = 1, Name = "Ball Handling" },
        new SportThing { Id = 2, Name = "Passing" },
        new SportThing { Id = 3, Name = "Shooting" },
        new SportThing { Id = 4, Name = "Rebounding" },
        new SportThing { Id = 5, Name = "Transition" },
        new SportThing { Id = 6, Name = "Defense" },
        new SportThing { Id = 7, Name = "Team Offense" },
        new SportThing { Id = 8, Name = "Team Defense" }
    };
}

So I was back to the JavaScript trying to figure out how to call the api and have it return all this. I needed to learn how to work with JQuery to request the data and ran into an article on that. You can convert XML to JSON with AJAX on the client. Steven Walther, who lives locally here in Seattle, has an article with the answer on how to do this. I started thinking that I needed to use the Knockout mapping stuff so it would bind to the observable array. An article on that helped me out.

This looks like a promising guide on Stack Overflow. I was trying to figure out how to get the JSON response into the local variable that gets into the KS observable and found more info here.

So my JavaScript code was modified to this:
$(function () {

    var data = null;

    $.ajax({
        url: "api/values",
        contentType: "json",
        success: function (foo) {
            data = foo;
        }
    });


    var viewModel = {

        tags: ko.observableArray(data),
        tagToAdd: ko.observable(""),

        //behaviors
        addTag: function () {
            this.tags.push({ Name: this.tagToAdd() });
            this.tagToAdd("");
        }

    };

    ko.applyBindings(viewModel);

});

And I decided to run the app. At first I thought it worked entirely but then I thought maybe not. At a minimum, it was calling the Web API and returning the JSON data, so that was exciting:

vrj4czrs_thumb3

It wasn’t working right. I wanted to simplify the way I was getting and setting the JSON into the KS observable. I learned of the getJSON method, which looked promising on a page from Knockout itself.

This resulted in my JavaScript being very simplified and more readable:
$(function () {

    $.getJSON("/api/values", function (resp) {
        var data = resp;
    })

    var viewModel = {

        tags: ko.observableArray(data),
        tagToAdd: ko.observable(""),

        //behaviors
        addTag: function () {
            this.tags.push({ Name: this.tagToAdd() });
            this.tagToAdd("");
        }

    };

    ko.applyBindings(viewModel);

});

But would it work? No. I looked at the getJSON call some more and found Stack Overflow article on this issue. But the guy was using the Mapping KS stuff and I didn’t want to go there to have that dependency right now. But I did learn, I think, that you need to take the response.Data property. Okay. But no. Still didn’t work with this:
var data = null;
    $.getJSON("/api/values", function (resp) {
        data = resp.Data;
    })

    var viewModel = {

        tags: ko.observableArray(data),

So I was pissed and called Garth who’s helping me with this and we finally got it running with the following syntax:
$(function () {
 
    var viewModel = {
        tags: ko.observableArray([]),
        tagToAdd: ko.observable(""),

        //behaviors
        addTag: function () {
            this.tags.push({ Name: this.tagToAdd() });
            this.tagToAdd("");
        }
    };

    $.getJSON("/api/values", function (resp) {
        viewModel.tags(resp);
        ko.applyBindings(viewModel);
    })

});

Wrapping up


So I’m happy that I made it this far. I have a Web API. I have Knockout working in MVC 4.0 Release Candidate. So I’m stoked. It took hours but I hope you can learn from my post.

Next steps:


Now that I have the architecture there, I need to do some real scenarios and see how all of this works!

Other stuff I learned while writing this post



  • I want to get me some Visual Studio 2012 and Windows 8 (and a faster computer!)
  • PhoneGap can package SPAs and publish them to Apple, Google, and MSFT stores!?!!!!!!
  • Local Storage API in HTML5
  • History JS for back-forward navigation on the client.
  • Upshot.js as data access layer. Uses datasource object
  • dbDataController is a specialization of the controller class and points to an EF data store and manages change sets.

    • Returning iQueryables allows upshot to query things on the client.
  • EF code first is still in play.
  • The need to use something like OAuth to make the API secure. Here’s a stack overflow article on it.
  • Use the Include method in EF to nest data in the controller response.
  • Can use OData queries against the controller’s URL like ?$filter=. But JavaScript can do this for us with Upshot.
  • Use Chrome's "Network" tab to see the call to the API and its response.
  • Rely on friends who know when you’re stuck (Garth is the man)
  • Use [] to initialize arrays in knockout
  • Garth recommended that I bind in the view model for the page that I'm on. Don’t know what this means. : )
  • Use the visible command in knockout to only paint markup when there’s data in the model.
  • In the HTML within the view, you can change data-bind to data-bindFOO or OFF or whatever to debug Knockout.