Select Report or Export (Example)

This simple example extends the Report Parameters example and allows the user to also choose to either Export to Excel or run a Quick Report..

The procedure checks its inputs, and if anything is missing, it exits and asks dbFront to prompt the user for the missing information and desired report format. dbFront prompts the user and passes their response back to the procedure. The procedure can optionally trigger an additional prompt or run to completion..

NOTE: This example depends on functionality found in dbFront 1.4.1.

The Procedure

Below is the top of the procedure, but with the code sections delimited and explained.

1. Parameters

The procedure is created with one special parameter @dbfFormData that allows dbFront to pass the FormData from the previous request. For more details see: API Fields

The first section within the procedure does a quick check to ensure that the procedure is given JSON data as expected.

CREATE PROCEDURE [dbo].[spReportingExample]

		    @dbfFormData nvarchar(MAX) = null

		AS

		BEGIN

		    -- SET NOCOUNT ON added to prevent extra result sets from

		    -- interfering with SELECT statements.

		    SET NOCOUNT ON;

		    -- Ensure that the parameters are valid JSON

		    IF (ISJSON(@dbfFormData,object) = 0) BEGIN

		        ;THROW 51000, 'The FormData JSON is invalid', 1;

		        return;

		    END;

2. Field and Button Constants

Using specific field and button constants may seem more verbose, but it enables field names and button prompts to be safely changed without breaking the procedure.

DECLARE @BUTTON_CANCEL as varchar(32) = 'Cancel';

		DECLARE @BUTTON_QUICK as varchar(32) = 'Quick Report';

		DECLARE @BUTTON_EXCEL as varchar(32) = 'Excel Export';

		DECLARE @FIELD_ReportYear as varchar(32) = 'Report Year';

3. Button Click - Cancel

If the user clicks 'Cancel', then simply exit.

DECLARE @buttonClicked as varchar(32) = JSON_VALUE(@dbfFormData,'$.ButtonClicked');

		IF @buttonClicked = @BUTTON_CANCEL BEGIN

		    select 'close' as action;

		    return; -- Cancelled Request.

		END;

4. Validate Inputs and Prompt if Missing or Invalid

The procedure then parses the @dbfFormData looking for the required parameters. If anything is missing, it tells dbFront to display a dialogue to prompt for the missing data. See: OpenDialog

If all of the inputs are valid, then the procedure continues to the end.

DECLARE @YEAR as int = try_cast(JSON_VALUE(@dbfFormData,concat('$.FormData."', @FIELD_ReportYear, '"')) as int);
DECLARE @minYear as int = 2020; --(select min(r.Year) + 1 from Referrals r);
DECLARE @maxYear as int = year(getdate());
IF @buttonClicked is null or @YEAR is null OR not @YEAR between @minYear and @maxYear BEGIN
   SELECT
       'OpenDialog' as Action
       , concat(
           '<h1>Report</h1>',
           '<hr>',
           '<h2>Please enter a report year (', @minYear, ' to ', @maxYear , ')</h2>') as Message
       , concat('{"', @FIELD_ReportYear, '":"int" }') as Fields
       , concat('["', @BUTTON_CANCEL, '","', @BUTTON_QUICK,'","', @BUTTON_EXCEL,'"]') as Buttons;

return; -- Early exit to draw the form.

5. Select the Report/Export Format

If the user chose Excel, then set up the OpenReport action, including formatting the report name. Otherwise, select the HtmlPrint output style.. See: OpenReport

After setting up the OpenReport action, the rest of the procedure can focus on generating the expected data..

END ELSE IF @buttonClicked = @BUTTON_EXCEL BEGIN
   SELECT
       'OpenReport' as Action
       , 'Excel' as OutputType
       , 'SampleTemplate.xlsx' as Template
       -- Yes, this is a truncated example of an ExcelRange used to position 32 result sets into an Excel Spreadsheet.
       -- The replace() is used to turn the internal double quotes into single quotes as needed by Excel.
       , concat('SampleExport_', @YEAR) as ReportName;
       , replace(
           '$"April"!A5:T8
           $"May"!A5:T8
           $"June"!A5:T8
           $"July"!A5:T8
           $"August"!A5:T8
           $"September"!A5:T8
           $"October"!A5:T8
           $"November"!A5:T8
           $"December"!A5:T8
           $"January"!A5:T8
           $"February"!A5:T8
           $"March"!A5:T8
           $"Q1"
           $"Q2"
           $"Q3"
           $"Q4"'
           ,'"','''') as ExcelRange;
END ELSE BEGIN
   SELECT
       'OpenReport' as Action
       , 'HtmlPrint' as OutputType
       , 'SampleBasicTemplate' as Template
       , concat('SampleReport_', @YEAR) as ReportName;
END;

-- The rest of the stored procedure body follows here.
select * from SalesLT.Product;

dbFront Configuration

Once the stored procedure is ready, there is only a small bit of dbFront configuration. All of the business logic and custom UI are defined in the stored procedure.

Create a new RunProcedure Action Button for the spReportingExample stored procedure with the following settings:

Main Tab
- Caption: "Run Procedure"
- Action Type: Run Procedure
Procedure Tab
- Procedure: "spReportngExample"
- Parameters: {dbfsp_data%}
- Output: Automatic

Benefits

The Procedure API allows you to add highly customized and complex business logic and user interactions to your database applications while leaving the technical aspect of web development to dbFront.