using System;

namespace Pivot.Sample1
{
    public class Expense
    {
        public DateTime Date { set; get; }
        public string Department { set; get; }
        public decimal Expenses { set; get; }
    }
}

using System;
using System.Collections.Generic;

namespace Pivot.Sample1
{
    public class ExpenseDataSource
    {
        public static IList<Expense> ExpensesDataSource()
        {
            return new List<Expense>
            {
                new Expense { Date = new DateTime(2011,11,1), Department = "Computer", Expenses = 100 },
                new Expense { Date = new DateTime(2011,11,1), Department = "Math", Expenses = 200 },
                new Expense { Date = new DateTime(2011,11,1), Department = "Physics", Expenses = 150 },

                new Expense { Date = new DateTime(2011,10,1), Department = "Computer", Expenses = 75 },
                new Expense { Date = new DateTime(2011,10,1), Department = "Math", Expenses = 150 },
                new Expense { Date = new DateTime(2011,10,1), Department = "Physics", Expenses = 130 },

                new Expense { Date = new DateTime(2011,9,1), Department = "Computer", Expenses = 90 },
                new Expense { Date = new DateTime(2011,9,1), Department = "Math", Expenses = 95 },
                new Expense { Date = new DateTime(2011,9,1), Department = "Physics", Expenses = 100 }
            };
        }
    }
}

using System.Collections;
using System.Linq;

namespace Pivot.Sample1
{
    public class PivotTable
    {
        public static IList ExpensesCrossTab()
        {
            return ExpenseDataSource
                        .ExpensesDataSource()
                        .GroupBy(t =>
                                   new
                                   {
                                       Year = t.Date.Year,
                                       Month = t.Date.Month
                                   })
                        .Select(myGroup =>
                                   new
                                   {
                                       //Year = myGroup.Key.Year,
                                       Month = myGroup.Key.Month,
                                       ComputerDepartment = myGroup.Where(x => x.Department == "Computer").Sum(x => x.Expenses),
                                       MathDepartment = myGroup.Where(x => x.Department == "Math").Sum(x => x.Expenses),
                                       PhysicsDepartment = myGroup.Where(x => x.Department == "Physics").Sum(x => x.Expenses)
                                   })
                        .ToList();
        }
    }
}

using System;

namespace Pivot.Sample2
{
    public class StudentStat
    {
        public int Id { set; get; }
        public string Name { set; get; }
        public DateTime Date { set; get; }
        public bool IsPresent { set; get; }
    }
}

using System;
using System.Collections.Generic;

namespace Pivot.Sample2
{
    public class StudentsStatDataSource
    {
        public static IList<StudentStat> CreateMonthlyReportDataSource()
        {
            var result = new List<StudentStat>();
            var rnd = new Random();

            for (int day = 1; day < 6; day++)
            {
                for (int student = 1; student < 6; student++)
                {
                    result.Add(new StudentStat
                    {
                        Id = student,
                        Date = new DateTime(2011, 11, day),
                        IsPresent = rnd.Next(-1, 1) == 0 ? true : false,
                        Name = "student " + student
                    });
                }
            }

            return result;
        }
    }
}

using System.Collections;
using System.Linq;

namespace Pivot.Sample2
{
    public class PivotTable
    {
        public static IList StudentsStatCrossTab()
        {
            return StudentsStatDataSource
                        .CreateWeeklyReportDataSource()
                        .GroupBy(x =>
                                  new
                                  {
                                      x.Id
                                  })
                        .Select(myGroup =>
                                  new
                                  {
                                      myGroup.Key.Id,
                                      Name = myGroup.First().Name,
                                      Day1IsPresent = myGroup.Where(x => x.Date.Day == 1).First().IsPresent,
                                      Day2IsPresent = myGroup.Where(x => x.Date.Day == 2).First().IsPresent,
                                      Day3IsPresent = myGroup.Where(x => x.Date.Day == 3).First().IsPresent,
                                      Day4IsPresent = myGroup.Where(x => x.Date.Day == 4).First().IsPresent,
                                      Day5IsPresent = myGroup.Where(x => x.Date.Day == 5).First().IsPresent,
                                      PresentsCount = myGroup.Where(x => x.IsPresent).Count(),
                                      AbsentsCount = myGroup.Where(x => !x.IsPresent).Count()
                                  })
                        .ToList();
        }
    }
}

var test1 = context.Blogs
.Where(blog => String.Compare(blog.Url, "A", StringComparison.Ordinal) > 0)
.ToList();
// SELECT [blog].[BlogId], [blog].[Url]
// FROM [Blogs] AS [blog]

var test2 = context.Blogs
.Where(blog => String.Compare(blog.Url, "B") > 0)
.ToList();
// SELECT [blog].[BlogId], [blog].[Url]
// FROM [Blogs] AS [blog]
// WHERE [blog].[Url] > N'B'

var test3 = context.Blogs
.Where(blog => blog.Url.Equals("C", StringComparison.OrdinalIgnoreCase))
.ToList();
// SELECT [blog].[BlogId], [blog].[Url]
// FROM [Blogs] AS [blog]

var test3_1 = context.Blogs
.Where(blog => blog.Url.Equals("C_1"))
.ToList();
// SELECT [blog].[BlogId], [blog].[Url]
// FROM [Blogs] AS [blog]
// WHERE [blog].[Url] = N'C_1'

var test4 = context.Blogs
.Where(blog => blog.Url.StartsWith("D"))
.ToList();
// SELECT [blog].[BlogId], [blog].[Url]
// FROM [Blogs] AS [blog]
// WHERE [blog].[Url] LIKE N'D' + N'%' AND (LEFT([blog].[Url], LEN(N'D')) = N'D')

var test5 = context.Blogs
.Where(blog => EF.Functions.Like(blog.Url, "S_i%"))
.ToList();
// SELECT [blog].[BlogId], [blog].[Url]
// FROM [Blogs] AS [blog]
// WHERE [blog].[Url] LIKE N'S_i%'

var test6 = context.Blogs
.Where(blog => blog.Url.ToUpper() == "E")
.ToList();
// SELECT [blog].[BlogId], [blog].[Url]
// FROM [Blogs] AS [blog]
// WHERE UPPER([blog].[Url]) = N'E'

var test7 = context.Blogs
.Where(blog => blog.Url.ToUpperInvariant() == "F")
.ToList();
// SELECT [blog].[BlogId], [blog].[Url]
// FROM [Blogs] AS [blog]

public class OrderItem 
{ 
    public int Quantity { get; set; } 
    public decimal UnitPrice { get; set; } 
    public decimal Discount { get; set; } 
} 
public class InvoiceItemGenerator 
{ 
    private readonly OrderItem _orderItem; 
    public InvoiceItemGenerator(OrderItem orderItem) 
    { 
        _orderItem = orderItem; 
    } 
    public dynamic Generate() 
    { 
        dynamic invoiceItem = new ExpandoObject(); 
        invoiceItem.Amount = _orderItem.Quantity * _orderItem.UnitPrice - _orderItem.Discount; 
        return invoiceItem; 
    } 
}

public class OrderItem 
{ 
    public int Quantity { get; set; } 
    public decimal UnitPrice { get; set; } 
    public decimal Discount { get; set; } 
    public decimal GetFinalAmount() 
    { 
        return Quantity * UnitPrice - Discount; 
    } 
} 
public class InvoiceItemGenerator 
{ 
    private readonly OrderItem _orderItem; 
    public InvoiceItemGenerator(OrderItem orderItem) 
    { 
        _orderItem = orderItem; 
    } 
    public dynamic Generate() 
    { 
        dynamic invoiceItem = new ExpandoObject(); 
        invoiceItem.Amount = _orderItem.GetFinalAmount(); 
        return invoiceItem; 
    } 
}

جمع بندی 

Create Table TestCUME_DIST
(SalesOrderID int not null,
OrderQty smallint not null,
ProductID int not null
 );
 GO
Insert Into TestCUME_DIST
       Values (43663,1,760),(43667,3,710),(43667,1,773),
  (43667,1,775),(43667,1,778),(43669,1,747),
  (43670,1,709),(43670,2,710),(43670,2,773),(43670,1,776)

SELECT SalesOrderID, OrderQty,
       CUME_DIST() OVER(ORDER BY SalesOrderID) AS [CUME_DIST]
FROM TestCUME_DIST ORDER BY [CUME_DIST] DESC

SELECT SalesOrderID, OrderQty, ProductID,
       CUME_DIST() OVER(PARTITION BY SalesOrderID ORDER BY ProductID ) AS [CUME_DIST]
FROM TestCUME_DIST
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC, [CUME_DIST] DESC

به‌خاطر بیاورید که هر Cube در SSAS دارای یک Measure پیش فرض بود که در صورت عدم اعلام نام یک Measure در کوئری، SSAS به صورت پیش فرض مقدار این Measure را بر می‌گرداند. خوب؛ نام ستون و سطر چرا ذکر نشده است؟ به دلیل عدم اعلام صریح نام سطر و ستون در کوئری بالا، SSAS نام ستون و سطر خاصی را نمی‌تواند نمایش دهد.

با بررسی کوئری زیر به درک بیشتری از شاخص (Measure) پیش فرض Cube دست پیدا خواهید کرد.