Report Grid from Query with Parameters

There are three components to executing a Report Grid from Query with Parameters on a page in Arena.

  • An Advanced HTML Text module which has a form with parameter input fields.
  • A SQL Stored Procedure which uses the parameters.
  • A Report Grid from Query module to display the results.

Advanced HTML Text Module

To take advance of some hidden secrets in Arena there is a module setting we need to change for the code below to work.  Under the Module Settings change Evaluate Query String to be True.

This now allows us to use the parameter page=##PAGE## in our JavaScript and Arena will automatically insert the correct page number.

In case you’re not familiar with ASP, the entire page is technically displayed  as one big form.  Which makes inserting a standard HTML form a little tricky.  Tricky, not impossible thanks to JavaScript!  (The opening JavaScript function is explained in this post.  It’s completely optional, but to summarize it let’s your form use the Enter key.)

<script>
$(function() {
     $("div[id$='pnlOneTextBox']").attr("onkeypress", "");
     $("div[id$='pnlTwoTextBoxes']").attr("onkeypress", "");
     $(document).keypress(function(e) {
         if (e.which === 13) {
             $("#btnApply").click();
             return false;
         }
         return true;
     });
 });
</script>

<script language="javascript">
function mypopup(tranid)
	{
		mywindow = window.open("default.aspx?page=3375&tranid=" + tranid, "mywindow", "location=no,status=no,width=950,height=350");
	}
</script>
<script language="javascript">
function getQueryVariable(variable) {
  var query = window.location.search.substring(1);
  var vars = query.split("&");
  for (var i=0;i<vars.length;i++) {
    var pair = vars[i].split("=");
    if (pair[0] == variable) {
      return pair[1];
    }
  }
  alert('Query Variable ' + variable + ' not found');
}

function applyFilter()    {
	var FromDate = document.frmMain.tbFromDate.value;
	var ThroughDate = document.frmMain.tbThroughDate.value;
	var EventName = document.frmMain.tbName.value;
	window.location = "default.aspx?page=##PAGE##&StartDate=" + FromDate + "&EndDate=" + ThroughDate + "&EventName=" + EventName ;
}

function clearFilter()  {
	window.location = "default.aspx?page=##PAGE##" ;
}
</script>

Now that we have our JavaScript in place, we build the form fields.  Remember, we can’t actually declare or close a form tag in HTML because it’s ASP.

<table>
    <tbody>
        <tr>
            <td align="right" class="formLabel">From:</td>
          <td><input name="tbFromDate" id="tbFromDate" class="formItem" /></td>
            <td class="smallText"> (mm/dd/yyyy)</td>
            <td class="smallText">&nbsp;</td>
      </tr>
        <tr>
            <td align="right" class="formLabel">Through:</td>
          <td><input name="tbThroughDate" id="tbThroughDate" class="formItem" /></td>
            <td class="smallText"> (mm/dd/yyyy)</td>
            <td class="smallText">&nbsp;</td>
      </tr>
		<tr>
            <td align="right" class="formLabel">Event Name:</td>
          <td><input name="tbName" id="tbName" class="formItem" /></td>
            <td class="smallText"> Copy from below.</td>
            <td class="smallText">&nbsp;</td>
      </tr>
        <tr>
            <td><br />
            </td>
            <td style="padding-left: 3px; padding-top: 4px;"><button id="btnApply" onclick="applyFilter(); return false;" class="smallText">Apply Filter</button></td>
            <td style="padding-left: 3px; padding-top: 4px;"><button id="btnClear" onclick="clearFilter(); return false;" class="smallText">Clear Filter</button></td>
            <td class="smallText" style="padding-left: 3px; padding-top: 4px;"><a href="default.aspx?page=3424">Sort for Reconciliation</a></td>
        </tr>
    </tbody>
</table>

If you’re using the code to block the Person Quick Search module from stealing the Enter key, make sure you’re Button’s ID matches.  In this case the ID is btnApply.  Also, for now you can disregard Line 69.  I’ll cover this later and explain why I have a hard-coded page ID.

SQL Stored Procedure

Next, we need to make sure we have something to do with the parameters.

If you’re using my example you’ll need to make a custom table and view before proceeding.

Custom Table

CREATE TABLE [dbo].[cust_ohc_tran_to_gl](
	[transaction_id] [int] NOT NULL,
	[transaction_detail] [varchar](100) NOT NULL,
	[transaction_date] [datetime] NOT NULL,
	[gl_date] [date] NULL,
	[gl_num] [int] NULL,
	[date_cleared] [date] NULL,
	[reconciled] [int] NULL,
 CONSTRAINT [PK_cust_ohc_tran_to_gl] PRIMARY KEY CLUSTERED
(
	[transaction_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Custom View

CREATE VIEW [dbo].[cust_evnt_v_eventTransactionList]
AS
SELECT     T.transaction_id, T.date_created, T.date_modified, T.created_by, T.modified_by, T.person_id, T.transaction_date, T.payment_type_luid, T.payment_method_luid,
                      T.transaction_amount, T.transaction_detail, T.notes, T.guid, T.gateway_account_id, T.transaction_type, T.repeating_payment_id, T.success, T.exported,
                      R.registration_id, R.profile_id, E.profile_name, R.owner_id AS registration_person_id, P.last_name + ', ' + P.nick_name AS registration_person_name,
                      dbo.evnt_funct_registration_registrants(R.registration_id) AS registrants, ISNULL
                          ((SELECT     SUM(rcp.payment_amount) AS Expr1
                              FROM         dbo.evnt_registrant_cost_payment AS rcp INNER JOIN
                                                    dbo.evnt_registrant_cost AS rc ON rc.registrant_cost_id = rcp.registrant_cost_id INNER JOIN
                                                    dbo.evnt_registrant AS r ON R.profile_id = rc.profile_id AND r.person_id = rc.person_id INNER JOIN
                                                    dbo.evnt_registration AS rg ON rg.registration_id = R.registration_id INNER JOIN
                                                    dbo.evnt_event_profile AS ep ON rg.profile_id = ep.profile_id
                              WHERE     (rcp.transaction_id = T.transaction_id)), 0) + ISNULL
                          ((SELECT     SUM(rfp.payment_amount) AS Expr1
                              FROM         dbo.evnt_registrant_fee_payment AS rfp INNER JOIN
                                                    dbo.evnt_registrant_fee AS rf ON rf.registrant_fee_id = rfp.registrant_fee_id INNER JOIN
                                                    dbo.evnt_registrant AS r ON R.profile_id = rf.profile_id AND r.person_id = rf.person_id INNER JOIN
                                                    dbo.evnt_registration AS rg ON rg.registration_id = R.registration_id INNER JOIN
                                                    dbo.evnt_event_profile AS ep ON rg.profile_id = ep.profile_id
                              WHERE     (rfp.transaction_id = T.transaction_id)), 0) AS applied_amount, PT.lookup_value AS payment_type, PM.lookup_value AS payment_method,
                      dbo.cust_ohc_tran_to_gl.gl_date, dbo.cust_ohc_tran_to_gl.gl_num, P.nick_name, P.last_name, CPSec.nick_name AS SecNick, CPSec.last_name AS SecLast,
                      dbo.cust_ohc_tran_to_gl.date_cleared, dbo.cust_ohc_tran_to_gl.reconciled
FROM         dbo.core_person AS P RIGHT OUTER JOIN
                      dbo.secu_login AS SecLog INNER JOIN
                      dbo.core_person AS CPSec ON SecLog.person_id = CPSec.person_id RIGHT OUTER JOIN
                      dbo.evnt_event_transaction AS ET INNER JOIN
                      dbo.pmnt_transaction AS T ON T.transaction_id = ET.transaction_id INNER JOIN
                      dbo.evnt_registration AS R ON R.registration_id = ET.registration_id INNER JOIN
                      dbo.core_profile AS E ON E.profile_id = R.profile_id INNER JOIN
                      dbo.cust_ohc_tran_to_gl ON ET.transaction_id = dbo.cust_ohc_tran_to_gl.transaction_id ON SecLog.login_id = T.created_by ON
                      P.person_id = R.owner_id LEFT OUTER JOIN
                      dbo.core_lookup AS PM ON T.payment_method_luid = PM.lookup_id LEFT OUTER JOIN
                      dbo.core_lookup AS PT ON T.payment_type_luid = PT.lookup_id

GO

Stored Procedure

CREATE PROCEDURE [dbo].[cust_ohc_transact_export_view]
@StartDate AS DATE,
@EndDate AS DATE,
@EventName AS VARCHAR(250)

AS

DECLARE @StartDateValue AS DATE,
@EndDateValue AS DATE,
@EventNameValue AS VARCHAR(250)

IF @StartDate <> ''
	BEGIN
		SET @StartDateValue = @StartDate;
	END
	ELSE
	BEGIN
		SET @StartDateValue = DATEADD(d,-3,GETDATE());
	END

IF @EndDate <> ''
	BEGIN
		SET @EndDateValue = @EndDate;
	END
	ELSE
	BEGIN
		SET @EndDateValue = DATEADD(d,1,GETDATE());
	END

IF @EventName <> ''
	BEGIN
		SET @EventNameValue = @EventName;
	END
	ELSE
	BEGIN
		SET @EventNameValue = '%';
	END

SELECT
	 CONVERT(varchar, etl.transaction_date, 101) AS [Transaction Date]
	, etl.payment_method AS [Payment Method]
	, etl.transaction_detail AS [Transaction Detail]
	, '$' + CONVERT(varchar, CONVERT(money, etl.transaction_amount), 1) AS [Amount]
	, '$' + CONVERT(varchar, CONVERT(money, etl.applied_amount), 1) AS [Applied Amount]
	, etl.profile_name AS [Event]
	, etl.registration_person_name AS [Registraion]
	, etl.registrants AS [Registrants]
	, CASE
		WHEN DATALENGTH(etl.notes) < 51 THEN etl.notes
		ELSE CAST(etl.notes AS varchar(50)) + '...'
	END AS [Notes]
	, CONVERT(varchar, etl.date_created, 101) + ' By ' + etl.SecNick + ' ' + etl.SecLast AS [Date Created]
	, etl.gl_num AS [GL Number]
	, CONVERT(varchar, etl.gl_date, 101) AS [Date Exported]
	, CONVERT(varchar, etl.date_cleared, 101) AS [Date Cleared]
	,[Reconciled] = CASE
		WHEN etl.reconciled IS NULL THEN '&nbsp;'
		WHEN etl.reconciled = 0 THEN '&nbsp;'
		ELSE '<div align="center"><img src="images/check.gif"></div>'
	END
	, '<button id="btnEdit" onclick="mypopup(' + CONVERT(varchar, etl.transaction_id) +  '); return false;" class="smallText">Edit Details</button>' AS [ ]
FROM
	cust_evnt_v_eventTransactionList AS etl
WHERE
	etl.profile_name LIKE @EventNameValue
	AND etl.transaction_date BETWEEN @StartDateValue AND @EndDateValue
ORDER BY
	transaction_date DESC
GO

Still with me?  Good, we’re almost done!

Report Grid from Query

Okay, now to get the results back on the page!  Below is a screen shot of the Report Grid from Query module settings.

You’ll see that the Parameters are formated for SQL with the @ symbol even though the JavaScript does not include it in the URL string.

Results

Now we should have a page with input fields and a grid with results.  It’s also important to note, if the Stored Procedure requires parameters to execute, the report grid will be blank until they are submitted.

5 thoughts on “Report Grid from Query with Parameters

  1. Kurt Meredith

    Trey,

    I have set up the HTML module, created the Table, View and Stored Procedure, and set up the Report Grid From Query as specified. However, I keep getting an exception (Procedure or function cust_ohc_transact_export_view has too many arguments specified.) when I try to view the page in our Arena site. Do you have any idea what may be causing this exception? The content of the “Parameters” field in my Report Grid from Query module is, “@StartDate=;@EndDate=;@EventName=;”, which seems to be the correct number of Parameters. An example of the querystring that the form is creating, which throws the error is, ” https://arenadev/default.aspx?page=3788&StartDate=01%2f01%2f2012&EndDate=05%2f01%2f2012&EventName=High+School+Beach+Camp+2012“. (I disabled the Report Grid from Query module in order to test the form in the HTML module without throwing the exception)

    Thanks,
    Kurt

  2. TreyTrey Post author

    Hey Kurt,
    There are two places I’d double-check for the “too many arguments” error.
    1) The RGFQ Module’s Settings. Even though the URL won’t contain the “@” symbol, my parameter list module setting looks like this: @StartDate=;@EndDate=;@EventName=;
    2) The SP itself. In my SP (and before the “AS” statement) I have: @StartDate AS DATE, @EndDate AS DATE, @EventName AS VARCHAR(250). Those match the 3 listed Parameters in my module setting. Then (after the “AS” statement) I have: DECLARE @StartDateValue AS DATE, @EndDateValue AS DATE, @EventNameValue AS VARCHAR(250). I then use some cases like:
    IF @StartDate <> ”
    BEGIN
    SET @StartDateValue = @StartDate;
    END
    ELSE
    BEGIN
    SET @StartDateValue = DATEADD(d,-3,GETDATE());
    END
    This way if there is no StartDate parameter in the URL string, it defaults to three days ago. Using the “<> ”” (is not blank) is the key to cases and how to keep your SP from not being happy about not having a parameter passed in the URL.
    I hope this helps!
    Trey

  3. David Ellis

    Kurt,
    If you’re on 2011.2.x or higher and your RGFQ module settings use parameters, Arena automatically adds an @OrganizationID integer parameter to them (unless there’s already an @OrganizationID parameter in your module settings). You can basically just add an “@OrganizationID INT = 1” parameter to the end of your parameter list for your stored procedure and if you want to integrate the parameter into the details of your proc you’d make it a bit more friendly for any multi-tenant users who’d be implementing it, but if this is an in-house only type proc, just leave it as a parameter and ignore the rest since you’re just going to be a single-tenant DB anyway
    –David

  4. Kurt Meredith

    Trey,

    I have made sure that the parameter list is correct. Then I decided to try circumventing the Arena module altogether by opening SQL Server Management Studio and executing the stored procedure directly from there.

    I used the command:
    exec dbo.cust_ohc_transact_export_view @StartDate=’01/01/2012′,@EndDate=’05/01/2012′,@EventName=’%’

    The command did not throw an error, but it also did not return any rows. Perhaps our db does not contain the data that your example intends to display. I don’t know if that is what may have caused the exception though. I’ll have to revisit the tutorial later, when I can create my own test query that I am sure will return some data.

    Thanks!
    Kurt

  5. Kurt Meredith

    Trey,

    One other thing that I noticed which I do not think is what is causing the exception, but which I am curious about; why does your example of the Parameter setting in the Report Grid from Query module have a semicolon at the end of the delimited list? It is not just your example; I have seen it done this way in a couple of other examples on community sites as well. At first glance it seems like this would make the number of items being passed appear–to the code at least–to contain an extra item, although when I tried to enter the string without the trailing semicolon, the exception was still thrown. I guess it does not matter, but since I saw other examples done this way, I was just wondering.

    Kurt

Leave a Reply