locked
Using a parameter field in add command in a subreport

    Question

  • Hello all,

    I am working on a Crystal Report with a few nested sub reports.  I am experienced using Crystal and I am experienced in writing SQL, but I have very little experience utilizing SQL with Crystal.  Any help would be appreciated.

    One of my subreports creates a header for the main report.  While testing, I realized that I can sometimes return more than one value within the header subreport.  I actually only want the maxmimum value in the header, so I wrote the following SQL and inserted it in the add command table:

    Select Max(PeopleRSN)
      from folderpeople fpe
         , folderprocess fp
         , folder f
     where f.folderrsn = fp.folderrsn
       and fpe.folderrsn = f.folderrsn
       and fpe.peoplecode = 2
       and fp.processrsn = {?ProcessRSN}

    {?ProcessRSN} is a parameter that is required in all of my subreports and also the main report.  I would like for the user to only be prompted one time when they run the main report.  On my other subreport, I removed the parameter from the subreport and linked the parameter from the main subreport by saying fp.processrsn = {?Pm-ProcessRSN}.  However if I make that change to the add command above, I receive the error:

    "Failed to retrieve data from the database.  Details: 42000:[Microsoft][ODBC driver for Oracle]Syntax error or access violation"

    Is it possible to pass in a parameter from a main report into a subreport's "add command" SQL?

    As I typed this, I thought of a second solution.  I've never used SQL Expression Fields before, but if I can use SQL similar to what I pasted above to retrive the Max(PeopleRSN), I can then utilize that field in the SQL Expert and pass in the parameter from the main report as I noted above ( {?Pm-ProcessRSN}.

    I think solution #2 is probably a better answer, but I'm also curious if solution #1 is possible.  Any help would be greatly appreciated.

    thanks,

    Noel

    Thursday, July 23, 2009 6:45 PM