Pages

Wednesday, July 17, 2013

MDX CALUCATIONS Examples---

---CALUCATIONS:

  --WITH MEMBER CALCUCATION <NAME>
    --<FORMULA>--
   
    WITH MEMBER [TEST] AS
      [MEASURES].[SALES AMOUNT],
  FORMAT_STRING ="#,##.00"

 SELECT
      {[MEASURES].[SALES AMOUNT],TEST
      } ON COLUMNS,
      NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]
  
  

 CREATE MEMBER [ADVENTURE WORKS DW2008R2].[TEST] AS
   [MEASURES].[SALES AMOUNT],
    FORMAT_STRING ="#,##.00"
   
      --OFTER CALL   
    SELECT
      {[MEASURES].[SALES AMOUNT],TEST
      } ON COLUMNS,
      NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]


 ---EXAMPLES

   WITH MEMBER [NAME] AS
   [DIM PRODUCT].[ENGLISH PRODUCT NAME].CURRENTMEMBER.NAME

   SELECT
      {[MEASURES].[SALES AMOUNT],[NAME]
      } ON COLUMNS,
      NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]



   WITH MEMBER [SALES] AS
  [MEASURES].[SALES AMOUNT],
  FORMAT_STRING="#,##.00"
  MEMBER [TOTAL SALES] AS
  SUM([DIM PRODUCT].[ENGLISH PRODUCT NAME].CURRENTMEMBER.PARENT,[MEASURES].[SALES AMOUNT]) ,
  FORMAT_STRING="#,##.00"
  MEMBER [SALES_PERSANTAGE] AS
//    ([SALES]/[TOTAL SALES])*100,
//      FORMAT_STRING="#,##.00"
 ([SALES]/[TOTAL SALES]),
 FORMAT_STRING="PERCENT"
     
     
 MEMBER [PERCENT] AS
 [MEASURES].[SALES AMOUNT]/SUM([DIM PRODUCT].[ENGLISH PRODUCT NAME].CURRENTMEMBER.PARENT,[MEASURES].[SALES AMOUNT]),
 FORMAT_STRING="PERCENT"


 MEMBER [DIF] AS
 SUM([DIM PRODUCT].[ENGLISH PRODUCT NAME].CURRENTMEMBER.PARENT,[MEASURES].[SALES AMOUNT]) -
 SUM([DIM PRODUCT].[ENGLISH PRODUCT NAME].CURRENTMEMBER.NEXTMEMBER,[MEASURES].[SALES AMOUNT])

   SELECT
      {[MEASURES].[SALES],[TOTAL SALES],[SALES_PERSANTAGE],[PERCENT],[DIF]
      } ON COLUMNS,
      --NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]) ON ROWS
ORDER(NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]),[SALES_PERSANTAGE]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]


Hope It will help u.....
Posted by at No comments:
Category:

MDX Querys Examples ...with Functions...

MDX Querys Functions:

--ASCENDANTS function
SELECT NON EMPTY([DIM CUSTOMER].[FIRST NAME].[FIRST NAME] ,
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]}) ON COLUMNS,
NON EMPTY([DIM DATE].[CALENDAR YEAR].[CALENDAR YEAR],[DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME]) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

SELECT
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]} ON COLUMNS,
NON EMPTY(ASCENDANTS([DIM DATE].[DATE].[DAY].&[FRIDAY].&[APRIL].&[2006])) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]



--RANGE FUNCTION
SELECT
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]} ON COLUMNS,
NON EMPTY([DIM DATE].[DATE].[YEAR].&[2006]:[DIM DATE].[DATE].[YEAR].&[2010]) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]


SELECT
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]} ON COLUMNS,
NON EMPTY([DIM DATE].[DATE].[DAY].&[FRIDAY].&[2006].&[APRIL].FIRSTSIBLING:[DIM DATE].[DATE].[DAY].&[FRIDAY].&[2007]. .&[AUGUST].LASTSIBLING) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

--LEAD FUNCTION(FORWAND AND BACKWARD|)

SELECT
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]} ON COLUMNS,
NON EMPTY([DIM DATE].[DATE].[DAY].&[FRIDAY].&[APRIL].&[2007].LEAD(1):[DIM DATE].[DATE].[DAY].&[FRIDAY].&[APRIL].&[2007].LEAD(3)) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

SELECT
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]} ON COLUMNS,
NON EMPTY([DIM DATE].[DATE].[DAY].&[FRIDAY].&[APRIL].&[2007].LEAD(1)) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]


SELECT
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]} ON COLUMNS,
NON EMPTY([DIM DATE].[DATE].[DAY].&[FRIDAY].&[APRIL].&[2007].NEXTMEMBER) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

SELECT
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]} ON COLUMNS,
NON EMPTY([DIM DATE].[DATE].[DAY].&[FRIDAY].&[APRIL].&[2007].PREVMEMBER) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]


---HEAD FUNCTION(FRIST 15 RECORDS )

SELECT
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]} ON COLUMNS,
HEAD([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],15) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

--TAIL FUNCTION (LAST 15 RECORDS)
SELECT
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]} ON COLUMNS,
TAIL([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],15) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]


----SORT MEASURES
---ORDER (LEVEL,MEASURE ,ASC OR DSC BY DEFULT ASC)
SELECT
[MEASURES].[SALES AMOUNT] ON COLUMNS,
ORDER([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT],ASC) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

SELECT
[MEASURES].[SALES AMOUNT] ON COLUMNS,
ORDER([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT],DESC) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

--MULTIPLE MEASURES ORDER
SELECT
{[MEASURES].[ORDER QUANTITY],[MEASURES].[SALES AMOUNT]} ON COLUMNS,
ORDER(([DIM PRODUCT].[COLOR].[COLOR],
[DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME]),[MEASURES].[SALES AMOUNT],DESC) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

SELECT
[MEASURES].[SALES AMOUNT] ON COLUMNS,
(ORDER(
[DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT],DESC),[DIM PRODUCT].[COLOR].[COLOR]) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]


--DATE ORDER (MULTIPLE MEASUES AND MULTIPLE ATTRIBUTES USING BASC,DSEC)

SELECT (ORDER([DIM DATE].[CALENDAR YEAR].[CALENDAR YEAR],[MEASURES].[SALES AMOUNT],BASC),
{[MEASURES].[TAX AMT],[MEASURES].[SALES AMOUNT]}) ON COLUMNS,
ORDER(([DIM DATE].[DATE].[ENGLISH MONTH NAME],
[DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME]),[MEASURES].[TAX AMT],BASC) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

SELECT (ORDER([DIM DATE].[CALENDAR YEAR].[CALENDAR YEAR],[MEASURES].[SALES AMOUNT],BASC),
{[MEASURES].[TAX AMT],[MEASURES].[SALES AMOUNT]}) ON COLUMNS,
ORDER([DIM DATE].[DATE].[ENGLISH MONTH NAME],[MEASURES].[TAX AMT],BASC) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

---FILLTER FUNCTION

SELECT
[MEASURES].[SALES AMOUNT] ON COLUMNS,
[DIM CUSTOMER].[FIRST NAME].[FIRST NAME] ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

   ---WHERE [DIM CUSTOMER].[FIRST NAME].&[ADAM],[DIM CUSTOMER].[FIRST NAME].&[AJAY]
  
  SELECT
[MEASURES].[SALES AMOUNT] ON COLUMNS,
[DIM CUSTOMER].[FIRST NAME].[ADAM] ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

SELECT
[MEASURES].[SALES AMOUNT] ON COLUMNS,
{[DIM CUSTOMER].[FIRST NAME].[ADAM],
 [DIM CUSTOMER].[FIRST NAME].[AJAY]} ON ROWS
FROM [ADVENTURE WORKS DW2008R2]



--WE NEED CHECK RELATIUONSHIP
   --WHERE WITH KEY MEMBER OF DIM AND VALUE MEMBER

SELECT
[MEASURES].[SALES AMOUNT] ON COLUMNS,
[DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME] ON ROWS
FROM [ADVENTURE WORKS DW2008R2]
WHERE  [DIM PRODUCT SUBCATEGORY].[HIERARCHY].[ENGLISH PRODUCT CATEGORY NAME]


SELECT
[MEASURES].[SALES AMOUNT] ON COLUMNS,
[DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME] ON ROWS
FROM [ADVENTURE WORKS DW2008R2]
WHERE {[DIM PRODUCT CATEGORY].[ENGLISH PRODUCT CATEGORY NAME].&[ACCESSORIES],
 [DIM PRODUCT CATEGORY].[ENGLISH PRODUCT CATEGORY NAME].&[BIKE]}


--MEASURES IN FILTERS
SELECT
 {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT]} ON COLUMNS,
FILTER([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[TAX AMT]>1010.92) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]

SELECT
 {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT]} ON COLUMNS,
FILTER([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]=12636.5) ON ROWS
FROM [ADVENTURE WORKS DW2008R2]


-- MEASURES WITH AND
 SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT]} ON COLUMNS,
      FILTER([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],
      [MEASURES].[SALES AMOUNT]>=12636.5 AND [MEASURES].[SALES AMOUNT]<48860 ) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

 SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT]} ON COLUMNS,
      ORDER (FILTER([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],
      [MEASURES].[SALES AMOUNT]>=12636.5 AND
      [MEASURES].[SALES AMOUNT]<48860 ),[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

 SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT]} ON COLUMNS,
      ORDER (FILTER([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],
      [MEASURES].[SALES AMOUNT]>=12636.5 AND
    [MEASURES].[SALES AMOUNT]<48860  AND
    [MEASURES].[TAX AMT]<1500),[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

    ---ATTRIBUTES FILTER(DIM)
   
  SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT]} ON COLUMNS,
     
      (FILTER(
      ([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],
      [DIM PRODUCT CATEGORY].[ENGLISH PRODUCT CATEGORY NAME].[ENGLISH PRODUCT CATEGORY NAME]),
      [DIM PRODUCT CATEGORY].[ENGLISH PRODUCT CATEGORY NAME].CURRENTMEMBER IS
      [DIM PRODUCT CATEGORY].[ENGLISH PRODUCT CATEGORY NAME].&[ACCESSORIES])
      ) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]


 --NON EMPTY,NONEMPTY NEED TO READ
  SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT],[MEASURES].[EXTENDED AMOUNT],
      [MEASURES].[ORDER QUANTITY],[MEASURES].[TOTAL PRODUCT COST],[MEASURES].[UNIT PRICE]
      } ON COLUMNS,
      NON EMPTY((
      [DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],
      [DIM PRODUCT CATEGORY].[ENGLISH PRODUCT CATEGORY NAME].[ENGLISH PRODUCT CATEGORY NAME]
      )
      ) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

 SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT],[MEASURES].[EXTENDED AMOUNT],
      [MEASURES].[ORDER QUANTITY],[MEASURES].[TOTAL PRODUCT COST],[MEASURES].[UNIT PRICE],
      [MEASURES].[UNIT PRICE DISCOUNT PCT]} ON COLUMNS,
      NONEMPTY((
      [DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],
      [DIM PRODUCT CATEGORY].[ENGLISH PRODUCT CATEGORY NAME].[ENGLISH PRODUCT CATEGORY NAME]
      ),[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

 ---TOP 10 RECORDS WITH HIGHEST SALES

 SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT],[MEASURES].[EXTENDED AMOUNT]
      } ON COLUMNS,
      HEAD(NONEMPTY([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT]),10) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

  SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT],[MEASURES].[EXTENDED AMOUNT]
      } ON COLUMNS,
      NON EMPTY(HEAD([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],10)) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]
          
           -----TOP 10 RECORDS WITH HIGHEST SALES
  SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT],[MEASURES].[EXTENDED AMOUNT]
      } ON COLUMNS,
      HEAD(ORDER([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],[MEASURES].[SALES AMOUNT],DESC),10) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

 SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT],[MEASURES].[EXTENDED AMOUNT]
      } ON COLUMNS,
      TOPCOUNT([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],10,[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

  SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT],[MEASURES].[EXTENDED AMOUNT]
      } ON COLUMNS,
      BOTTOMCOUNT([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],10,[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

  ---TOPSUM FUNCTION(TOTAL SUM OF RECORDS EQUAL TO 5000000)
  SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT],[MEASURES].[EXTENDED AMOUNT]
      } ON COLUMNS,
      TOPSUM([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],5000000,[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

 ---TOPPERCENT,BOTTOMPERCENT FUNCTION(TOTAL SUM(SALES AMOUNT) OF RECORDS TAKE 50 PERSANGE OF TOTAL SUM  RECORDS(SALES AMOUNT))

  SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT],[MEASURES].[EXTENDED AMOUNT]
      } ON COLUMNS,
      TOPPERCENT([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],50,[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

 SELECT
      {[MEASURES].[SALES AMOUNT],[MEASURES].[TAX AMT],[MEASURES].[EXTENDED AMOUNT]
      } ON COLUMNS,
      BOTTOMPERCENT([DIM PRODUCT].[ENGLISH PRODUCT NAME].[ENGLISH PRODUCT NAME],50,[MEASURES].[SALES AMOUNT]) ON ROWS
 FROM [ADVENTURE WORKS DW2008R2]

Hope it will help u.........

Posted by at No comments:
Category:

Converting a List to Datatable


The following is the method through which you can convert any list object to datatable..

public DataTable ConvertToDataTable<T>(IList<T> data)
    {
        PropertyDescriptorCollection properties =
           TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;

    }

Hope it will help u.....
Posted by at No comments:
Category:
Subscribe to: Posts (Atom)