Use date parameter to spread cost while not limiting the data by date
<p>I have a crystal report that currently has a date parameter that is used to create a spread structure for cost and revenue. It works great for currently active initiatives, but completely excludes future jobs without cost or revenue yet associated. The report is supposed to spread over the next three fiscal years, so if an initiative begins in the next fiscal year and I am running the report in March of the current year -- I need the initiative to show in the report and the cost and revenue to show up in the 2nd fiscal year. </p>

<p>I have tried removing the date parameter so that the spread structure is still there, but then all cost and revenue that is on the books shows up. So if you run the report for the previous month it still would give you the same To Date cost and revenue as if you ran it in the current month which is a problem..</p>

<p>I have tried to only apply the month parameter to the To Date data but then the spread is still affected by that since the spread is getting the numbers from the To Date and spreading it.. </p>

<p>It seems like this must be possible, but I must not be using the right words to describe my problem..</p>

<p>Any suggestions would be appreciated. Thank you!!</p>

<p>--Thank you for your responses, I am sorry my post isn't very clear.</p>

<p>This is an abbreviate version of the crystal query</p>

<pre><code> select company, project, costEntryMonth, projectStartMonth,
 projectCompletionMonth, actualCost,  projectedCost, originalCost
 from ProjectTable join CostTable on ProjectTable.Project=CostTable.Project
 WHERE company = 100 and jobStatus=1 and costEntryMonth&lt; DateParameter 
 and projectCompletionMonth &gt;= EndOfLastFiscalYear
</code></pre>

<p>The report uses a parameter that limits the costEntryMonth so that when you run it in a previous month you get kind of a snapshot in history of where we were. 
My manager would like to maintain this functionality. The problem is that for future jobs that might not have any cost entries, they are not included in the report. It seems like I need to have a filter that brings in all active projects with or without costs yet. Then on a lower level have another filter for the costEntryMonth.. I don't really know where to include another filter layer besides the select expert. Maybe have separate groups?</p>

<p>Thank you for your help!!</p>