Posted - 06/22/2015 : 07:45:09
| Hi Guys,
I need some help and you guys have been so helpful in the past. I’m a novice but I have created an IT cost tracker within SSRS 2008. This IT tracker has a start and end date parameter, it also has a parameter relating to IT costs e.g.
Select gljrndtl.segvalue1 as Account,
When '310100' Then 'Software license'
When '310200' Then 'Software Maintenance'
When '310300' Then 'Hardware Maintenance'
When '315100' Then 'IT Consumables Hardware'
When '315200' Then 'IT Services'
When '318100' Then 'Computer Equipment Rental'
When '330100' Then 'Telephone'
When '330300' Then 'Mobile'
When '330500' Then 'Internet'
END as 'IT Cost Type'
Where Gljrndtl.segvalue1 like '31%' or Gljrndtl.segvalue1 like '33%'
Group by gljrndtl.segvalue1
This parameter limits the data specific to IT related costs which is correct but I want to see IT costs that appear on an invoice which may have costs other than IT associated with it e.g. I have an invoice, journal line 1 = IT costs, journal line 2 =travel costs, I want to see the net value. Can someone give me pointers on how I can achieve this?
This is the main query
SELECT GLJrnDtl.Company, Vendor.Name, Vendor.VendorID, APInvHed.InvoiceDate, APInvHed.DueDate, DATEDIFF(dd, APInvHed.DueDate, GETDATE()) AS [Days Over], GLJrnDtl.APInvoiceNum, GLJrnDtl.GLAccount, GLJrnDtl.JournalLine,
GLAcctDisp.AccountDesc, APInvHed.Description, GLJrnDtl.VendorNum, GLJrnDtl.CurrencyCode,
GLJrnDtl.BookCreditAmount * - 1 + GLJrnDtl.BookDebitAmount AS [GL Invoice Amount], GLJrnDtl.JEDate, APInvHed.ApplyDate, gljrndtl.segvalue1, gljrndtl.segvalue2, gljrndtl.segvalue3
FROM GLJrnDtl LEFT OUTER JOIN
GLAcctDisp ON GLJrnDtl.GLAccount = GLAcctDisp.GLAccount AND GLJrnDtl.Company = GLAcctDisp.Company RIGHT OUTER JOIN
Vendor ON Vendor.VendorNum = GLJrnDtl.VendorNum AND GLJrnDtl.Company = Vendor.Company RIGHT OUTER JOIN
APInvHed ON GLJrnDtl.VendorNum = APInvHed.VendorNum AND GLJrnDtl.APInvoiceNum = APInvHed.InvoiceNum AND APInvHed.Company = GLJrnDtl.Company AND
APInvHed.ApplyDate = GLJrnDtl.JEDate AND APInvHed.ApplyDate BETWEEN @startdate AND @enddate
WHERE (GLJrnDtl.JEDate BETWEEN @startdate AND @enddate) AND (APInvHed.ApplyDate = GLJrnDtl.JEDate) AND (GLJrnDtl.SourceModule = 'AP') AND
(GLJrnDtl.JournalCode = 'pj') AND (Vendor.Name NOT LIKE 'statpro%')
and (Gljrndtl.segvalue1 in (@Account))
and (Gljrndtl.company in (@Company))
GROUP BY GLJrnDtl.Company, Vendor.Name, Vendor.VendorID, GLJrnDtl.APInvoiceNum, GLJrnDtl.GLAccount, GLJrnDtl.JournalLine, GLAcctDisp.AccountDesc,
APInvHed.Description, GLJrnDtl.VendorNum, GLJrnDtl.BookCreditAmount * - 1 + GLJrnDtl.BookDebitAmount, GLJrnDtl.JEDate, APInvHed.ApplyDate,
APInvHed.DueDate, APInvHed.InvoiceDate, GLJrnDtl.CurrencyCode,gljrndtl.segvalue1, gljrndtl.segvalue2, gljrndtl.segvalue3
ORDER BY GLJrnDtl.Company, Vendor.Name, GLJrnDtl.APInvoiceNum, GLJrnDtl.JournalLine, Vendor.VendorID, GLJrnDtl.JEDate
Excuse me if I haven't listed the question appropriately, give me pointers and I'll fix up.