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.



Go to Top of Page
   

- Advertisement -