CREATE PROCEDURE usp_GetStateProviceTotalSalesAmount

AS

BEGIN

SELECT “[Customer].[State-Province].[State-Province].[MEMBER_CAPTION]” AS [State-Provice],

CONVERT(float,”[Measures].[Internet Sales Amount]”) AS [Internet Sales Amount],

CONVERT(float,”[Measures].[Internet Gross Profit]”) AS [Internet Gross Profit]

FROM OPENQUERY([AdventureWorks], — Linked server

— Starting MDX Query

‘SELECT  NON EMPTY({

[Measures].[Internet Sales Amount],

[Measures].[Internet Gross Profit] }) ON COLUMNS,

NON EMPTY([Customer].[State-Province].[State-Province]) ON ROWS

FROM [Adventure Works]   WHERE ([Customer].[Country].&[United States],[Date].[Fiscal Year].&[2013])’

)

END