UnboxedSolutions

MDX: Top N Amounts and Combine the Rest

• Created: Thursday, July 12, 2012
• Sean

Let's say you want to see the top 5 measure values for a dimension and then combine the values of the rest:

with set [TopSelection] as
      topcount([Dim Customer].[Contact Type Key].[Contact Type Key], 5, [Measures].[Company Invoice Amount])
member [Dim Customer].[Contact Type Key].[Total] as
      sum([TopSelection])
member [Dim Customer].[Contact Type Key].[Everything] as
      sum([Dim Customer].[Contact Type Key].[Contact Type Key])
member [Dim Customer].[Contact Type Key].[All Others] as
      [Dim Customer].[Everything] - [Dim Customer].[Contact Type Key].[Total]
select {
              [TopSelection],
              [Dim Customer].[Contact Type Key].[All Others]
        } on 0,
        [Measures].[Company Invoice Amount] on 1
from [Business DW]
where (
    [Dim Business Type].[Business Type Key].&[1],
    [Dim Company].[Parent Company Key].&[1],
    [Dim Date].[Date].&[2012-07-12T00:00:00]
)

• Read more

SSAS: AverageOfChildren vs. Min/Max

• Created: Thursday, July 12, 2012
• Sean

Hit a bit of a snag creating an Avg Score measure in my cube in SSAS. I used the AverageOfChildren aggregation behavior and apparently this ignores the Date dimension because it is semiadditive. To create an [Avg Anything] measure, create a Sum measure and a Count measure, then create a calculated member as Sum / Count....or define the calculated member in your MDX statement every time.

Anyway, hope this helps someone else who runs into the same issue.

• Read more

My First MVC3 Site: www.ratoutyourfriends.com

• Created: Monday, February 13, 2012
• Sean

The very first project I did using MVC 3 went live and we made the news! :)

 

• Read more

MVC 3 - Search Form & Result Paging

• Created: Wednesday, March 30, 2011
• Sean

Developing a search form should be one of the simplest Web development tasks in the world. I can do it in a few minutes with ASP.NET WebForms, but for some reason with MVC it kicked my ass! Then I remembered back to the good old 90s when contract rates were high, the economy was great, and I was 20lbs lighter.

...oh and classic ASP forced you to create forms where you could either do a POST or a GET. Remember GET? Yeah it's beeen a while if you've been an ASP.NET developer. But welcome back! That is...if you are using MVC.

It is actually pretty simple. If, unlike me, you by chance bump into the blog post instead of trying to find the (IMO) really BAD answers on stackoverflow.com (at least to-date) and various corners of the Interwebz googling things with Bing or binging things with Google....but I digress.

Here's the answer:

1. Make your form do an HTTP GET.
2. Use Nuget to download PagedList
3. Use Model Binding in your controller action method.
4. Use RouteValueDictionary in the PagedList Pager HTML helper.
5. ???
6. Profit!

Here's the code for the ViewModel class, controller, and View (in that order):

 

using System.Linq;

 

The magic is that MVC coerces the querystring back into the model and vice versa.

Hope this helps anyone trying to figure this out.

• Read more

MVC = Lots More jQuery

• Created: Sunday, February 13, 2011
• Sean

Once you get past all of the cool MVC simple-mode demos which always have things like typing dates into textboxes, no Ajax, etc...you start to dig into what I personally find to be the most work with MVC: enriching the UI. WebForms can kind of spoil us with things like update panels, Ajax control toolkit, or 3rd party solutions such as Telerik Ajax Controls. I've burned many hours with UI enhancements using jQuery. Yes, this is a confession not a complaint. For example, autocomplete is a fairly simple concept; however, this also involves changing up style sheets, or adding additional behavior to examples you might find on Google such as what I've illustrated below: filtering the autocomplete list by employee title.

 I'm used to cranking out WebForms apps very quickly, whereas MVC has been a bit more tedious. So I thought I'd post some things I worked on yesterday that will hopefully save other people some time. jQuery Autocomplete and Dropdownlist replacements are very nice plugins. Here's some code that leverages both of them.

The View

@model AdventureWorks.Common.SearchEmployeeViewModel
<h2>Search Employees</h2>
<link href="@Url.Content("~/Content/jquery.autocomplete.css")" rel="stylesheet" type="text/css" />
<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>
<link href="@Url.Content("~/Content/jquery.dropdownReplacement-0.5.css")" rel="stylesheet" type="text/css" />
<script src="@Url.Content("~/Scripts/jquery.scrollTo-1.4.2.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.bgiframe.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.dropdownReplacement-0.5.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.autocomplete.min.js")" type="text/javascript"></script>

<!-- this should go in its own js file... -->
<script type="text/javascript">
    $(document).ready(function () {
        $("#Title").dropdownReplacement({ selectCssWidth: 600 });
        $("input#LastName").autocomplete('@Url.Action("Find")', { cacheLength: 0, delay: 500, extraParams: { title: function () { return $("#Title").val(); } } });
    });
</script>

@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)
    <fieldset>
        <legend>SearchEmployeeViewModel</legend>
            <div class="editor-label">@Html.LabelFor(model => model.LastName)</div>
            <div class="editor-field">
                @Html.TextBoxFor(model => model.LastName, htmlAttributes: new { style = "width: 300px " })
                @Html.ValidationMessageFor(model => model.LastName)
            </div>
            <div class="editor-label">@Html.LabelFor(model => model.Title)</div>
            <div class="editor-field">
                @Html.DropDownListFor(model => model.Title, new SelectList(Model.TitleList))
                @Html.ValidationMessageFor(model => model.Title)
            </div>
            <p><input type="submit" value="Search" /></p>
    </fieldset>
}

The Controller

using System.Web.Mvc;
using AdventureWorks.BusinessInterface;
using AdventureWorks.Common;

namespace MvcDemo.Controllers {
    public class EmployeeController : Controller {
        EmployeeBusinessInterface _employeeBusinessInterface = new EmployeeBusinessInterface();

        public ActionResult Search() {
            var model = new SearchEmployeeViewModel();
            model.TitleList = _employeeBusinessInterface.GetTitleList();
            return View(model);
        }

        public ActionResult Find(string q, int limit, string title) {
            string[] lastNames = _employeeBusinessInterface.FindLastNames(q, title, limit);
            return Content(string.Join("\n", lastNames));
        }
    }
}

The Model

using System.Collections.Generic;
using System.Linq;

namespace AdventureWorks.BusinessInterface {
    public class EmployeeBusinessInterface {
        DataAccess.AdventureWorksEntities _adventureWorksEntities = new DataAccess.AdventureWorksEntities();
        public string[] FindLastNames(string q, string title, int limit) {
            return _adventureWorksEntities.Employees.Where(e => e.Title == title && e.Contact.LastName.StartsWith(q))
                                                    .Select(e => e.Contact.LastName)
                                                    .Distinct()
                                                    .Take(limit)
                                                    .ToArray();
        }

        public List<string> GetTitleList() {
            return _adventureWorksEntities.Employees.Select(e => e.Title).Distinct().OrderBy(name => name).ToList();
        }
    }
}

The ViewModel

using System.Collections.Generic;

namespace AdventureWorks.Common {
    public class SearchEmployeeViewModel {
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string Title { get; set; }
        public List<string> TitleList { get; set; }
    }
}

So now we have a really nice dropdown where you can set the height and scroll unlike a regular HTML Select control.

And our Autocomplete assists the user with the search.

As an aside, I'm starting to change my mind about needing a Common, BI, DAL, but I definitely think the ViewModel and MVVM pattern is the way to go using only strongly-typed Views. I'm also playing around with putting some of the current logic for Common, BI into the ViewModel class with the goal of keeping the Controller code as clean as possible. This means the ViewModel class will be encapsulating the logic necessary to call into the Entity Framework (in this case) classes to fill itself with data, handle validation with annotations, etc eliminating the need to have a bunch of library projects. In this example the Controller calls to the BI to fill the ViewModel class with data, the jQuery Autocomplete Controller method basically does the same thing to get a list of names. Seems to work pretty well so far.

• Read more