User Input for Reporting Services-based Reports

You need a Form to collect user input for Named Parameters for the SQL query for Reporting Services based Reports.

Technique

A Client Method-based Report is used to present a Form to collect the user input. The user input is mapped to a query_string for the Report Server request via the RSGatwway.aspx page.

Procedure for building a user input Form for Reporting Services-based Reports with Named Parameters:

1. Login to Aras Innovator as admin.
2. Navigate to the Administration->Forms off the Main Tree.
3. Open a new Form Item.

4. Set the Form Item properties as follows:

   1. Name = My Report (this can be any name you want)

   2. Add a form event that fires onLoad

      • Name the Method ‘My Report_setDefaults’ (this can be anything you want).

   3. Add fields to the form that will match the input you are requesting.

      • Name the fields appropriately (‘PartNumber’, ‘Description’, ‘Cost’)

   4. Add a Button field with a label ‘Submit’

      1. Create a Field Event that fires onClick.
      2. Name the Method ‘My Report_OnClick’ (this can be anything you want)
   5. Save, unlock, and close.
5. Navigate to the Administration->Methods off the Main Tree.
6. Edit the My Report_OnClick Method Item.

7. Set the method code as shown.

   • Set document.forms[0].<FieldNameOnForm>.value to the names of the fields on the My Report Form.

     var retVal=[];
// retVal will be used in MyReport_OpenDialog
// document.forms[0].<FieldNameOnForm>.value
retVal[“PartNum”] = document.forms[0].PartNumber.value;
retVal[“Desc”] = document.forms[0].Description.value;
retVal[“Cost”] = document.forms[0].Cost.value;
parent.args.dialog.result = retVal;
parent.args.dialog.close();
     

8. Save, unlock, and close.
9. Create another Method Item (‘MyReport_OpenDialog’, this can be called whatever you want).

10. Set the method code as shown:

    1. Update var reportName with the name of your Report as deployed to the Report Server.
    2. The URL query strings (“&np:”) should reference the parameter as defined in the report.

    var inn = this.getInnovator();

    var ReportInputForm = inn.newItem(“Form”, “get”);
ReportInputForm.setAttribute(“select”, “id, width, height”);
ReportInputForm.setProperty(“keyed_name”, “My Report”);
ReportInputForm = ReportInputForm.apply();
var param =
{
title: “My Report”,
formId: ReportInputForm.getID(),
isEditMode: “1",
aras: aras,
opener: window,
innovator: inn,
// use param.item only if the Report is of Type=Item
//and you need to pass the context item to the dialog
//item : this
dialogHeight: ReportInputForm.getProperty(‘height’,'800'),
dialogWidth: ReportInputForm.getProperty(‘width’,'600'),
content: ‘ShowFormAsADialog.html’
};
function callback (dialogWrapper)
{
var result = dialogWrapper.result;
if (!result){ return }
var reportName = “My Report"; // Your Report Name Here
var url = top.aras.getServerBaseURL() + “RSGateway.aspx?irs:Report=" +
reportName +
"&np:Item_Number="+result.PartNum +
"&np:Description="+result.Desc +
"&np:Cost="+result.Cost;
var w = window.open();
w.location = url;
return "";
}
var wnd = top.aras.getMainWindow();
wnd = wnd === top ? wnd.main : top;
wnd.ArasModules.Dialog.show(“iframe”, param).promise.then(callback);
    

11. Save, Unlock, and Close
12. Edit the My Report_SetDefaults Method Item.

13. Set the method code as shown:

    var fields = [“PartNumber”, “Description”, “Cost”];
var values = ["%", "%", "%"];
for (var i = 0; i < fields.length; i++){
// currentField = <FieldID> + “span”
var currentFieldID = getFieldByName(fields[i]).id;
// currentField = <Actual field ID>
currentFieldID =
currentFieldID.substring(0,currentFieldID.indexOf(‘s’));
var currentField = document.getElementById(currentFieldID);
currentField.value = values[i];
}
    

14. Save, unlock, and close.
15. Navigate to the Administration->Reports off the Main Tree.
16. Open a new Report Item.
17. Set the Report Item properties as follows:

• Name = My Report (this can be anything you want).
• Type = Generic, ItemType, depending on when you want the Report to appear on the Report menu; Generic always, ItemType only when that ItemType is selected off the Main Tree.
• Location = Client; this is required because the My Report_OpenDialog Method returns an HTML page with JavaScript that needs to be parsed and Client-side Report only writes the transformed page to the browser window and thus the JavaScript is not parsed.
• Target = None; the windows will be created by My Report_OpenDialog.
• Method = My Report_OpenDialog
• Report Query = Leave blank; it is currently not used for this purpose

The Report Item should now appear on the Report menu and when selected the User Input Dialog should open. When the user clicks Submit the user input is mapped as a query_string and the actual Report is requested from the Report Server via the RSGateway.

If you want to add validation, add this either in My Report_onClick or in MyReport_OpenDialog, after the modal dialog is returned.