Linking Subreports using Command

[former_member779033]

Problem:

I have created a report using the command function. I am trying to insert a subreport and link it to the billno field in the main report (?Pm-Command.billno). The code below is used in the command funtion of the main report and pulls all data within a given date range as well as any records related to the particular invoice(billno).

select b1.*
from billed b1
  inner join (
    Select billno, min(datebilled) as FirstDate
    from billed
    group by billno) dts
  on b1.billno = dts.billno
    and dts.FirstDate between {?BillDate} and {?enddate}

When inserting the subreport it is linked to command.billno with subreport parameter fields set to ?Pm-Command.billno and linked to the field tickc.billno

The subreport is not currently populating correctly it appears to be trippling some records and omitting others.

The subreport I would like to add does not use the command function when selecting records from the database.

Any help would be appreciated. Also please keep in mind if crystal reports was a person id still be in diapers.

[former_member779033]

Any other suggestions?

Your assistance is much appreciated.

[DellSC]

The data that appears in the subreport is only related to the main report by the field you're linking on - it does not have access to any of the other data in the main report. So, whatever data issues you're seeing in the subreport are related to the data that's in the subreport.

First, I would try going to the Report menu in the subreport and turning on "Select Distinct Records" to see if that resolve the issue with duplicate records.

If that doesn't solve the issue and for the data that's not appearing, I would look at how you have the data set up in the subreport. In particular, go to Database >> Show SQL Query in the subreport, copy that SQL and then run it in whatever tool you use outside of Crystal to query your database - something like Toad, SSMS, PL/SQL, etc - for individual billno values where you're seeing an issue.

-Dell

[former_member779033]

the following is what I get when "Show SQL Query" on the sub report. The subreports populated correctly prior to changing the main report to use the command funtion.