here is the final result i need. it looks ulgy, but the coalesced sub-queries are all the same with different fields/predicate values.

SELECT  DISTINCT 
    [distribution_center].[name] AS [DistributionCenter], 
    [item].[name] AS [ItemDescription], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate1 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Week10], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate4 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Week09], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate7 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Week08], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate10 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Week07], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate13 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Week06], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate16 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Week05], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate19 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Week04], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate22 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Week03], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate25 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Week02], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate28 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Week01], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[store_on_hand]) AS [StoreOnHand] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate31 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [StoreOnHand], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[in_transit_on_hand]) AS [InTransitOnHand] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate34 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [InTransit], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[warehouse_on_hand]) AS [WarehouseOnHand] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate37 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [Warehouse], 
    COALESCE((SELECT TOP 1 SUM([SubMovement].[current_order]) AS [CurrentOrder] FROM [movement_data] AS [SubMovement] INNER JOIN [item] AS [SubItem] ON [SubMovement].[item_id] = [SubItem].[item_id] WHERE ( [SubMovement].[week_begin_date] = @WeekBeginDate40 AND [SubMovement].[status] = 1 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), 0) AS [CurrentOrder], 
    [item].[fine_line_id] AS [FineLineId] 
FROM    (( [distribution_center]  
    INNER JOIN [movement_data]  ON  [distribution_center].[id]=[movement_data].[distribution_center_id]) 
    INNER JOIN [item]  ON  [item].[id]=[movement_data].[item_id]) 
WHERE   ( ( [item].[fine_line_id] IN (@FineLineId43, @FineLineId44, @FineLineId45, @FineLineId46, @FineLineId47, @FineLineId48, @FineLineId49, @FineLineId50)
 AND    [movement_data].[status] = @Status51
 AND    [movement_data].[week_begin_date] BETWEEN @WeekBeginDate52 AND @WeekBeginDate53)) 
ORDER BY [distribution_center].[name] ASC,[item].[name] ASC

here is the code to produce the report

public IEnumerable<WeeklyReportDTO> FetchWeeklyReportData(DateTime date)
{
    //confirm date is satruday. this will throw an argument out of range exception if it's not saturday
    new WalmartDateManager(date);

    //multiple db calls
    this.adapter.KeepConnectionOpen = true;

    //calculate 10 weeks back from given date.
    DateTime start = date.AddDays(-63);

    //get fineline descriptions for this period
    using (EntityCollection<FineLineEntity> fineLines = new FineLineManager(this).FetchAllForDateRange(start, date))
    {
        //get FineLineIds to filter result set
        List<int> fineLineIds = new List<int>(fineLines.Count);
        foreach (FineLineEntity fineLine in fineLines)
        {
            fineLineIds.Add(fineLine.Id);
        }

        //configure fields
        IEntityFields2 fields = new ResultsetFields(17);
        fields[0] = DistributionCenterFields.Name.SetFieldAlias("DistributionCenter");
        fields[1] = ItemFields.Name.SetFieldAlias("ItemDescription");
        fields[2] = this.CreateWeeklyTotalField(start, MovementDataFields.PointOfSale, "Week10");
        fields[3] = this.CreateWeeklyTotalField(date.AddDays(-56), MovementDataFields.PointOfSale, "Week09");
        fields[4] = this.CreateWeeklyTotalField(date.AddDays(-49), MovementDataFields.PointOfSale, "Week08");
        fields[5] = this.CreateWeeklyTotalField(date.AddDays(-42), MovementDataFields.PointOfSale, "Week07");
        fields[6] = this.CreateWeeklyTotalField(date.AddDays(-35), MovementDataFields.PointOfSale, "Week06");
        fields[7] = this.CreateWeeklyTotalField(date.AddDays(-28), MovementDataFields.PointOfSale, "Week05");
        fields[8] = this.CreateWeeklyTotalField(date.AddDays(-21), MovementDataFields.PointOfSale, "Week04");
        fields[9] = this.CreateWeeklyTotalField(date.AddDays(-14), MovementDataFields.PointOfSale, "Week03");
        fields[10] = this.CreateWeeklyTotalField(date.AddDays(-7), MovementDataFields.PointOfSale, "Week02");
        fields[11] = this.CreateWeeklyTotalField(date, MovementDataFields.PointOfSale, "Week01");
        fields[12] = this.CreateWeeklyTotalField(date, MovementDataFields.StoreOnHand, "StoreOnHand");
        fields[13] = this.CreateWeeklyTotalField(date, MovementDataFields.InTransitOnHand, "InTransit");
        fields[14] = this.CreateWeeklyTotalField(date, MovementDataFields.WarehouseOnHand, "Warehouse");
        fields[15] = this.CreateWeeklyTotalField(date, MovementDataFields.CurrentOrder, "CurrentOrder");
        fields[16] = ItemFields.FineLineId;

        //configure filter
        IRelationPredicateBucket bucket = new RelationPredicateBucket();
        bucket.Relations.Add(MovementDataEntity.Relations.DistributionCenterEntityUsingDistributionCenterId);
        bucket.Relations.Add(MovementDataEntity.Relations.ItemEntityUsingItemId);
        bucket.PredicateExpression.Add(ItemFields.FineLineId == fineLineIds);
        bucket.PredicateExpression.Add(MovementDataFields.Status == true);
        bucket.PredicateExpression.Add(new FieldBetweenPredicate(MovementDataFields.WeekBeginDate, null, start, date));

        //configure sorter
        ISortExpression sorter = new SortExpression();
        sorter.Add(DistributionCenterFields.Name | SortOperator.Ascending);
        sorter.Add(ItemFields.Name | SortOperator.Ascending);

        //fetch data and return
        using (DataTable results = new DataTable())
        {
            this.adapter.FetchTypedList(fields, results, bucket, 0, sorter, false);
            this.adapter.CloseConnection();

            foreach (FineLineEntity fineLine in fineLines)
            {
                yield return new WeeklyReportDTO(fineLine.Name, fineLine.GroupByDistributionCenter, new DataView(results, string.Format("[FineLineId] = {0}", fineLine.Id), string.Empty, DataViewRowState.OriginalRows));
            }
        }
    }
}

private IEntityField2 CreateWeeklyTotalField(DateTime date, IEntityField2 field, string name)
{
    IEntityField2 pos = field.SetAggregateFunction(AggregateFunction.Sum).SetObjectAlias(movementAlias);

    IPredicateExpression filter = new PredicateExpression();
    filter.Add(MovementDataFields.WeekBeginDate.SetObjectAlias(movementAlias) == date);
    filter.Add(MovementDataFields.Status.SetObjectAlias(movementAlias) == true);
    filter.Add(MovementDataFields.DistributionCenterId.SetObjectAlias(movementAlias) == DistributionCenterFields.Id);
    filter.Add(ItemFields.Name.SetObjectAlias(itemAlias) == ItemFields.Name);

    IRelationCollection relations = new RelationCollection();
    relations.Add(new EntityRelation(ItemFields.Id.SetObjectAlias(itemAlias), MovementDataFields.ItemId.SetObjectAlias(movementAlias), RelationType.OneToMany));

    return new EntityField2(name, new DbFunctionCall("COALESCE", new object[] { new EntityField2(name, new ScalarQueryExpression(pos, filter)), 0 }));
}

my problem lies within the sub-select from clause. I can't seem to get the table alias to generate correctly. I figure the problem is the RelationCollection in the CreateWeeklyTotalField function, but I can't figure out how to fix it. here are the relational code changes and the sub query it produces. none of them seem to work:

IRelationCollection relations = new RelationCollection();
relations.Add(new EntityRelation(ItemFields.Id.SetObjectAlias(itemAlias), MovementDataFields.ItemId.SetObjectAlias(movementAlias), RelationType.OneToMany));

COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM ( [item]  INNER JOIN [movement_data]  ON  [item].[id]=[movement_data].[item_id]) WHERE ( [SubMovement].[week_begin_date] BETWEEN @WeekBeginDate1 AND @WeekBeginDate2 AND [SubMovement].[status] = @Status3 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), @LO04) AS [January]

IRelationCollection relations = new RelationCollection();
relations.Add(new EntityRelation(ItemFields.Id.SetObjectAlias(itemAlias), MovementDataFields.ItemId.SetObjectAlias(movementAlias), RelationType.OneToMany));
relations[0].SetAliases(itemAlias, movementAlias);

COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM ( [item]  INNER JOIN [movement_data]  ON  [item].[id]=[movement_data].[item_id]) WHERE ( [SubMovement].[week_begin_date] BETWEEN @WeekBeginDate1 AND @WeekBeginDate2 AND [SubMovement].[status] = @Status3 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), @LO04) AS [January]

IRelationCollection relations = new RelationCollection();
relations.Add(ItemEntity.Relations.MovementDataEntityUsingItemId);
relations[0].SetAliases(itemAlias, movementAlias);

COALESCE((SELECT TOP 1 SUM([SubMovement].[point_of_sale]) AS [PointOfSale] FROM ( [item]  INNER JOIN [movement_data]  ON  [item].[id]=[movement_data].[item_id]) WHERE ( [SubMovement].[week_begin_date] BETWEEN @WeekBeginDate1 AND @WeekBeginDate2 AND [SubMovement].[status] = @Status3 AND [SubMovement].[distribution_center_id] = [distribution_center].[id] AND [SubItem].[name] = [item].[name])), @LO04) AS [January], 

any ideas what i'm missing?

Try using this relations.Add overload:

public virtual IEntityRelation Add( 
   IEntityRelation relationToAdd,
   string aliasRelationStartEntity,
   string aliasRelationEndEntity,
   JoinHint hint)

So your code should look like

relations.Add(
     new EntityRelation(
          ItemFields.Id.SetObjectAlias(itemAlias),
          MovementDataFields.ItemId.SetObjectAlias(movementAlias), 
          RelationType.OneToMany), 
     "SubMovement", 
     "SubItem", 
     JoinHint.Inner);

that was it. thank you.