Active TopicsMemberlistSearchHelpRegisterLogin

Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: Account for no records in data return... Post Reply Post New Topic
Author Message
ekotek
Newbie
Newbie
Avatar

Joined: 02 Nov 2009
Location: United States
Online Status: Offline
Posts: 4
Quote ekotek Replybullet Topic: Account for no records in data return...
    Posted: 21 Oct 2010 at 7:44am
Hey all, I'm hoping someone can help me out with this -
Hopefully, this doesn't get too complicated:
I've currently got a report based on a Stored Procedure.
This SP returns a list of Projects from a tblProjects table.
Everything is great there.
However, I put in a formula field as follows:
    If DatePart("m",CurrentDate) = 10 to 12 Then
            If {tblTimeTracking_Forecast.FYear} = DatePart("yyyy", DateAdd("yyyy",1,CurrentDate)) Then
                If {tblTimeTracking_Forecast.FQuarter} = 3 Then
                    "X"
    Else If DatePart("m",CurrentDate) = 1 To 3 Then
            If {tblTimeTracking_Forecast.FYear} = DatePart("yyyy", CurrentDate) Then
                If {tblTimeTracking_Forecast.FQuarter} = 4 Then
                    "X"
    Else If DatePart("m",CurrentDate) = 4 To 6 Then
            If {tblTimeTracking_Forecast.FYear} = DatePart("yyyy", DateAdd("yyyy",1,CurrentDate)) Then
                If {tblTimeTracking_Forecast.FQuarter} = 1 Then
                    "X"
    Else If DatePart("m",CurrentDate) = 7 To 9 Then
            If {tblTimeTracking_Forecast.FYear} = DatePart("yyyy", DateAdd("yyyy",1,CurrentDate)) Then
                If {tblTimeTracking_Forecast.FQuarter} = 2 Then
                    "X"
 
This returns almost exactly what I need. However, a "boatload" of the projects disappear from the report as a result of inserting this formula field into the report.
 
I believe this is due to the fact that not all of the Projects listed in tblProjects have a record in tblTimeTracking_Forecast....is there some way of editing this formula to not remove the projects without a record in the tblTimeTracking_Forecast from the report?
 
Any help would be greatly appreciated!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Oct 2010 at 8:28am
if you were not using any field from tblTimeTracking_Forecast in this report (displayed, in a formula, in the select expert, etc.) and you did not 'enforce' the joins in the report set up once you add the formula it will neformce your joins and omit records.
You can test this removing the formula field and by just dragging any field from that table to the report canvas. If your rows start disappearing this is what is happening.
IP IP Logged
ekotek
Newbie
Newbie
Avatar

Joined: 02 Nov 2009
Location: United States
Online Status: Offline
Posts: 4
Quote ekotek Replybullet Posted: 21 Oct 2010 at 9:02am
Yeah, this is definitely what's happening...
Is there any way to not remove the Projects that don't have a record in the tblTimeTracking_Forecast table?
Maybe by setting a default value where there is no record or something?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Oct 2010 at 9:26am
are you using an outer join?
if not that is what you need to use. If youa re then what is your select statement? It is likely turning the outerjoin into an inner one.
IP IP Logged
ekotek
Newbie
Newbie
Avatar

Joined: 02 Nov 2009
Location: United States
Online Status: Offline
Posts: 4
Quote ekotek Replybullet Posted: 21 Oct 2010 at 9:44am
It actually is a left outer join...here's the statement:
 

SELECT pt.ProjectID, pt.ProjectTitle,

COUNT(CASE WHEN (FQuarter = @CurQP1 AND FYear = @CurYP1) THEN ForecastID ELSE NULL END) AS CurQuartPlus1,

COUNT(CASE WHEN (FQuarter = @CurQP2 AND FYear = @CurYP2) THEN ForecastID ELSE NULL END) AS CurQuartPlus2,

COUNT(CASE WHEN (FQuarter = @CurQP3 AND FYear = @CurYP3) THEN ForecastID ELSE NULL END) AS CurQuartPlus3,

COUNT(CASE WHEN (FQuarter = @CurQP4 AND FYear = @CurYP4) THEN ForecastID ELSE NULL END) AS CurQuartPlus4,

COUNT(CASE WHEN (FQuarter = @CurQP5 AND FYear = @CurYP5) THEN ForecastID ELSE NULL END) AS CurQuartPlus5,

COUNT(CASE WHEN (FQuarter = @CurQP6 AND FYear = @CurYP6) THEN ForecastID ELSE NULL END) AS CurQuartPlus6

FROM tblProjectTracking as pt

 
LEFT OUTER JOIN tblTimeTracking_Forecast as ttf on pt.ProjectID = ttf.ProjectID
 

WHERE (StatusID IN (1,2,4,5,6,7,10,15) OR

@IncludeCompleted = 1) AND

(ApplicationDirectorID = @EmployeeID OR

OperationsDirectorID = @EmployeeID OR

@EmployeeID = 0)

GROUP BY pt.ProjectID, pt.ProjectTitle

ORDER BY CASE WHEN @SortByID = 0 THEN ProjectTitle END,

CASE WHEN @SortByID = 1 THEN pt.ProjectID END



Edited by ekotek - 21 Oct 2010 at 9:45am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Oct 2010 at 9:55am

This is your sp?
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		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



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.