Filtering/Parameterizing one dataset based of another (cascading queries-ish)

Filtering/Parameterizing one dataset based of another (cascading queries-ish)

Posted by: mike.sarko on 26 June 2024, 9:31 am EST

• 

  mike.sarko

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 26 June 2024, 9:31 am EST

  Hello,
  
  Problem:
  
  New to ActiveReports (using 17 Designer). Wanted to know if one dataset (DS-A) can be filtered or parameterized based on another dataset (DS-B). And can’t (yet) use sub-reports as our hosted application provider doesn’t support them (yet, they are looking into it).

  Scenario: Previous user of Crystal Reports had created a receipt report for building permits. It had a small sub-report for the payer information, a small sub report for the owner information and a detail section.

  As noted above, sub-reports are (currently) an issue.

  I was thinking I could use a a value returned in DS-A (receipt_id) and pass it to DS-B as a parameter into the actual SQL statement.
  
  I also thought about filtering the same way (filtering DS-B based on the ca_receipt_id from DS-A).

  The basic receipt query is based on a unique permit id and the payment date, those two things typically result in receipt id. However, multiple receipt ids are possible and will be handled via grouping.

  Any thoughts? I’m guessing there is no way to control which query runs first…I want to avoid one giant query to rule them all.

• 

  anand.sinha

  

  • Post Options:
  • Link
    Copy

  Posted 27 June 2024, 2:53 am EST

  Hi Mike,

  You can take a look at Cascading Parameters that are created just for this purpose i.e. when the second parameter value depends on the value of the first parameter.

  Please find attached a sample implementing the same for your reference.

  Regards,
  
  Anand

  CascadingParameters.zip

• 

  mike.sarko

  • Post Options:
  • Link
    Copy

  Posted 27 June 2024, 11:08 am EST

  Anand,

  Thank you for the example. Almost what I’m looking for, I don’t want users to be prompted for anything other than the a start date and end date (the application id is passed in by the web-app).

  The workflow I have in mind is something like this:
  
  Report is selected to be ran
  
  Current date is the default date, but users should have the option to override that date and pick another.
  
  The report is ran.
  
  On the back end, the datasets would be something like:

  DS-RECEIPT-ID: Get the unique receipt_id(s) for the given date and application_id (application id is passed in by the software)
  
  DS-PAYOR-DETAILS: Get the payor details based on the receipt ID(s) above
  
  DS-APP-DETAILS: Get the applicant details based on application_Id above
  
  DS-RECEIPT-DEAILS: Gets the receipt details based on the RECEIPT_ID from DS-RECEIPT-ID.

  Think sub-reports passing parameters, without the sub-reports? Does this make sense?

  -Mike

• 

  anand.sinha

  

  • Post Options:
  • Link
    Copy

  Posted 1 July 2024, 12:42 am EST

  Hi Mike,

  For your use case, we’ll suggest assigning a run-time datasource to your report, i.e. when the report is run you can handle the report.Document’s ‘LocateDataSource’ event to get the inputted DataParameter based on which you can filter and populate a DataTable and assign this DataTable as the datasource to your report.

  You can learn more about binding dataset at runtime here: Dataset Provider.

  Please find attached a sample implementing the same.

  Regards,
  
  Anand

  FilterDatasourceByDate.zip