Active Report Dataset Command Type (Text or Stored Procedure)

Active Report Dataset Command Type (Text or Stored Procedure)

Posted by: dbassion on 2 December 2024, 5:00 pm EST

• 

  dbassion

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 2 December 2024, 5:00 pm EST

  I’ve looked all over the internet and the forums and can’t find any detailed documentation on when to use Text vs Stored Procedure as the Command Type in a Dataset. I’m using a SQL Stored Procedure and it works when the Command Type is Text, but not when it’s Stored Procedure. Can anyone give me a description of when to use each and possibly an example of how the Query should be formatted if the Command Type is Stored Procedure that has parameters being passed in?

• 

  katyayny.raghuvanshi

  

  • Post Options:
  • Link
    Copy

  Posted 3 December 2024, 5:17 am EST

  Hi David,

  Please refer to the attached sample where we have used a stored procedure that contains a parameter of SMALLDATETIME type. In the report, we have created a report parameter of type DateTime, and the report runs as expected by fetching the correct data from the database based on the passed report parameter. The Stored Procedure Command Type is correctly set, and the parameter from the report is mapped to the stored procedure’s parameter, ensuring the proper handling of DateTime values.

  You may also refer to the attached documentation for detailed information about using stored procedures in dataset: Stored Procedure as Dataset

  Please refer to the attached report file and .bak file for the database. If the issue persists, please share with us your SQL database (.bak) and report file so that we can test for the issue at our end as well.

  We hope this helps!

  StoredProcedureSample.zip

• 

  dbassion

  • Post Options:
  • Link
    Copy

  Posted 3 December 2024, 10:19 am EST

  Thanks for your help. When I try to open the .rdlx file in your zip file, Active Reports 17 says it’s not a valid report file?

  Also, can you describe what the benefits are of having the command type be Stored Procedure vs Text since it seems like a Stored Procedure can be used in both instances?

• 

  katyayny.raghuvanshi

  

  • Post Options:
  • Link
    Copy

  Posted 4 December 2024, 4:56 am EST

  Hi David,

  >> When I try to open the .rdlx file in your zip file, Active Reports 17 says it’s not a valid report file?]

  ActiveReports 18 onwards, the legacy RDL was removed, and RDL Multi-Section was renamed to RDLX. The RDL report type is no longer available for new reports in ActiveReports 18. RDL reports, created in the previous versions of ActiveReports, are automatically converted to the RDLX format when opened in the Designer. However, the same should get opened in v17.3 Designer as well. Where are you trying to open the report, and what exact version of ActiveReports are you using?

  >> Also, can you describe what the benefits are of having the command type be Stored Procedure vs Text since it seems like a Stored Procedure can be used in both instances?

  Yes, you can set Command Type as both StoredProcedure and Text for stored procedures. Please refer to the following:

  1. Text Command Type:

  • Set the Command Type as Text.
  • Set the Query as

    EXEC dbo.ParamTesting @DateParam

    . Here you have to write the full query command in the syntax of

    EXEC StoredProcedureName @DataSetParameterName

    .

  2. StoredProcedure Command Type:

  • Set the Command Type as StoredProcedure.
  • Simply pass the stored procedure’s name in the Query as

    dbo.ParamTesting

    . It just is a simplified way of using stored procedures.

  We hope this clears up the confusion!

  Attachment:StoredProcedureSample_Updated.zip