Search code examples
crystal-reports

Modifying existing Crystal Reports to include additional filters

I have an existing report that I didn't make. It is pretty complicated. It does not use the command function, so I cannot directly change the filters in the SQL code. I have reviewed several links that basically say that there is no way around this and that I will need to create a new command and redo the report. I would like to find a solution that doesn't involve me redoing the report.

My logic that I need is to only select subcontracts that only have one instance (regardless if approved or not), if they have two instances associated only choose the one that has been approved

This is the current backend Crystal SQL

   SELECT columns 
    from several tables joins etc 
  WHERE  subcontract = '12345'--parameter entry
   AND company=1 --parameter entry

I would like to add this logic which works in a normal sql statement but since I can't modify Crystal's SQL I am stuck...

   AND ((subcontract in (select subcontract from table
    group by subcontract having count(*) = 1))
  OR (subcontract in (select subcontract from table group by subcontract  having count(*) > 1)  
 AND "approved = 'Y'))

I have looked at you the Select and Group Expert but I am just not getting there. Has anyone found some simple work-arounds? Thanks!!!

edit: I already have a select expert that involves subcontract but I don't know how to add what I want to it since it is validation based and I am wanting to add a filter component to it..

(
if (IsNull({?BeginSub}) or Trim({?BeginSub})="") then
    true
else
    {subcontract} >= Trim({?BeginSub})
 ) and
 (
 if (IsNull({?BeginSub}) or Left(Trim({?BeginSub}),3) in ["","zzz"]) then
    true
else
    {subcontract} <= Trim({?EndSub})
 )

Edit Update: I have found a way to get what I want in crystal reports but it throws an error when I run the report from my application. I create a command with the conditions that I want and then link to the other tables where command.subcontract=othertables.subcontract.

Solution

  • I found what I needed.. I added the following sql expression field,

          (Select count(subcontracts) from subcontract table)

    then I added the following to the select expert 

            if(({%SubContractCount} <>2) or ({%SubContractCount}>1 AND  
       approved = Y)) then true else false

    Thank you!!