Cost Tracker in SSRS 08 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cost Tracker in SSRS 08

Author

Topic

IMZSHAH
Starting Member

12 Posts

Posted - 2015-06-22 : 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,
Case Segvalue1
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'
from Gljrndtl
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.

IMZSHAH
Starting Member

12 Posts

Posted - 2015-06-23 : 06:43:48

Maybe this will help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources