Print Page | Close Window

Account for no records in data return...

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11487
Printed Date: 20 May 2024 at 9:18pm


Topic: Account for no records in data return...
Posted By: ekotek
Subject: Account for no records in data return...
Date 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!


Replies:
Posted By: DBlank
Date 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.

Posted By: ekotek
Date 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?

Posted By: DBlank
Date 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.

Posted By: ekotek
Date 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

Posted By: DBlank
Date Posted: 21 Oct 2010 at 9:55am

This is your sp?


Print Page | Close Window