Joined: 15 Nov 2011
Online Status: Offline
Posts: 29
Topic: Missing journal line Posted: 16 Aug 2012 at 2:55am
Hi there
I have a report that pulls out line totals for each journal (debit -credit as LineTotal) but when a transaction has two lines going to the same account for the same amount only one line is picked up.
I think it excludes possible duplications but it is not in this case, any idea how it can be fixed?
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 16 Aug 2012 at 4:02am
Go to the Database menu and make sure that "Select Distinct Records" is turned off. Also, if your data is displayed in a group header section instead of a details section, that can cause duplicates to not be displayed.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 16 Aug 2012 at 4:05am
what is your source and how is it 'written'?
Under File>Report Options> do you have select distinct records as True?
If so drag the unique field from the transaction table (primary key) onto the report canvas to see if that 'fixes' it.
Are you Are you sure it is excluded from your data set or just not being displayed in the report. It could be suppressed or if you are you grouping and placing records on headers or footers
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 16 Aug 2012 at 7:17am
In Crystal, go to the Database menu and select "Show SQL Query". Copy the query and run it against your database in a tool like Toad or SQL Server Management Studio, etc. (whatever is appropriate for your database.) Notice whether there is a "distinct" or "group by" in the query - these will eliminate duplicates.
Also, is your report based on tables, a command, or a universe query?
Joined: 15 Nov 2011
Online Status: Offline
Posts: 29
Posted: 16 Aug 2012 at 9:44am
Hi again
Unfortunately don’t have access to Toad or SQL Server Query tool
This is the command used:
Declare @yr int, @pr int SET @yr = {?yr1} SET @pr = {?pr1}
SELECT *, (SELECT e1.T_RefDate FROM OFPR e1 INNER JOIN OACP f1 on f1.PeriodCat=e1.Category WHERE f1.Year = @yr AND e1.SubNum = @pr) MonthEnd FROM (SELECT 'JE ' + RTRIM(c.TransId) AS DocNum, c.RefDate, c.Account, c.Debit-c.Credit AS LineTotal, c.SYSDeb-c.SYSCred AS SysTotal, c.TransId, c.FinncPriod, f.Year, e.SubNum, e.F_RefDate, e.T_RefDate, f.F_RefDate as F_YDate, f.T_RefDate as T_YDate, g.AcctName, g.ActType,g.FatherNum, g.Levels,g.GrpLine, g.GroupMask, h.AcctName AS Father, OADM.CompnyName, OADM.MainCurncy, OADM.SysCurrncy From JDT1 c INNER JOIN OFPR e on e.AbsEntry=c.FinncPriod INNER JOIN OACP f on f.PeriodCat=e.Category INNER JOIN OACT g on g.AcctCode=c.Account INNER JOIN OACT h on h.AcctCode=g.FatherNum, OADM WHERE f.Year = @yr AND e.SubNum <= @pr UNION SELECT 'JE 0',NULL,g.AcctCode,0,0,0,0, f.Year, e.SubNum, e.F_RefDate, e.T_RefDate, f.F_RefDate as F_YDate, f.T_RefDate as T_YDate, g.AcctName, g.ActType,g.FatherNum, g.Levels,g.GrpLine, g.GroupMask, h.AcctName AS Father, OADM.CompnyName, OADM.MainCurncy, OADM.SysCurrncy FROM OACT g INNER JOIN OACT h on h.AcctCode=g.FatherNum Cross join OFPR e INNER JOIN OACP f on f.PeriodCat=e.Category, OADM WHERE f.Year = @yr AND e.SubNum <= @pr) Q1 CROSS JOIN (SELECT * FROM (Select Category, SubNum, SUBSTRING(DATENAME(mm,T_RefDate),1,3) + ' ' + RTRIM(DATEPART(YEAR,T_RefDate)) AS Yr FROM OFPR) Q PIVOT (Max(Yr) FOR SubNum IN ([1], [2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])) AS pvt WHERE pvt.Category=@yr ) Q2
The details section is suppresed but not conditionally.
So, when a journal is as follow:
line1: account1 40cr
line2: account2 20 dr
line 3: account2 20dr
only line 2 amount is showing.
Any other ideas what might not be right in my report
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
