Where by measure

Feb 10, 2014 at 12:14 PM
Edited Feb 10, 2014 at 12:35 PM
Hi, how can I generate a query like this with LINQ?

SELECT
{([Date].[Fiscal Year].[Fiscal Year])} ON COLUMNS,
{( [Customer].[Customer].[Customer])} ON ROWS
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount]

Thanks a lot :)
Coordinator
Feb 18, 2015 at 1:29 PM
Edited Feb 18, 2015 at 1:43 PM
Your MDX query example does pivoting.
LINQ syntax does not support pivot / unpivot, so SDX and SSAS Entity Framework provider cannot do it. (None of LINQ or Entity Framework providers does it).

This problem looks more like a presentation concern and rarely has to be solved programmatically. Pivot Table and other similar presentation tools are the most frequent solution.
When you do need to solve pivoting problem programmatically, usually it is being done by moving Year into ON ROWS axis and then pivoting the query result in memory using C# or any other .NET language.
SDX LINQ query part would look very simple:

from customer in dbContext.Customers
select new { customer.Year, customer.InternetSalesAmount }

Where by measure would work this way (note that both Year and InternetSalesAmount should be in select clause):

from customer in dbContext.Customers
where customer.InternetSalesAmount > 100
select new { customer.Year, customer.InternetSalesAmount }