how do you write multiple sql queries in powerpivot

[jersey]

Hi,
Is it possible to paste 2 queries in powerpivot?
The first query creates a temp and the 2nd query bring in some additional columns.

Thanks,
Cindy

 

[scottsen]

You could always just put that in a SPROC/VIEW in sql, That is probably the "more correct" thing to do. That said, I suspect it would work just fine -- power pivot does let you write queries when importing data. Did you try it?

 

[DWig]

I don't know about additional columns, but I know that T-SQL allows unions. (I think that's what PowerPivot runs on, in any case I mean the SQL that's used in PP)

For example, this code would get you a sum of all activity for 2011 and older, followed by the detail for 2012 and newer:

SELECT  GLSummary.Company_Key
  ,GLSummary.GLAccount_Key
  ,SUM(GLSummary.Debit) AS Debit
  ,GLSummary.Project_Key
  ,SUM(GLSummary.Credit) AS Credit
,Max(FinancialPeriod.FinancialPeriod) AS FinancialPeriod
 ,Max(FinancialPeriod.FinancialPeriod_Key) AS FinancialPeriod_Key
FROM
  FinancialPeriod
  INNER JOIN GLSummary
    ON FinancialPeriod.FinancialPeriod_Key = GLSummary.FinancialPeriod_Key
WHERE
(FinancialPeriod.FinancialPeriod)<='12/31/2011'
GROUP BY
  GLSummary.Company_Key
  ,GLSummary.GLAccount_Key
  ,GLSummary.Project_Key


UNION


SELECT
  GLSummary.Company_Key
  ,GLSummary.GLAccount_Key
  ,SUM(GLSummary.Debit) AS Debit
  ,GLSummary.Project_Key
  ,SUM(GLSummary.Credit) AS Credit
,(FinancialPeriod.FinancialPeriod) AS FinancialPeriod
 ,(FinancialPeriod.FinancialPeriod_Key) AS FinancialPeriod_Key
FROM
  FinancialPeriod
  INNER JOIN GLSummary
    ON FinancialPeriod.FinancialPeriod_Key = GLSummary.FinancialPeriod_Key
WHERE
(FinancialPeriod.FinancialPeriod)>='1/1/2012'
GROUP BY
  GLSummary.Company_Key
  ,GLSummary.GLAccount_Key
  ,GLSummary.Project_Key
,FinancialPeriod.FinancialPeriod
,FinancialPeriod.FinancialPeriod_Key