General Functions > Reporting > Standard Interactive Reports

The Reporting Features available within the MIE include the capability of creating a Standard Interactive Report through the report designer. This provides report designers with a quicker and easier way to build a report around a SQL Query for a table-view report.

In order to create a Standard Interactive Report, the user must navigate to the Reports page and then access the Designer. Next, the user must choose a Description for the report which will also be used for the Report Title. Then, by selecting Standard Interactive Report for the Type of report, the rest of the design will adjust to show the pages relevant to a Standard Interactive Report

You will need to provide for the report is the SQL Query and a Description for the query (often based off of the report description). The SQL Query must be limited to only SELECT statements (exclude any semicolons you may regularly use in a statement) and does not support complex logic by default (such as inner selects and temp tables, etc). Once the SQL Query and Description have been defined, the Order By and Primary Table fields MUST be defined as well.

The Order By field needs to be populated with the column name(s) and the appropriate ASC or DESC depending on your desired order. The column names MUST NOT have any table prefix associated with them. This must be populated with only columns and/or the ASC or DESC keywords. An example from an Order By clause like “ORDER BY TABLE.recordID DESC, TABLE.otherValue ASC” would be “recordID DESC, otherValue ASC”

The Primary Table field must be populated with the table you wish for the CustNo restriction to be enforced on (you can require JOINED tables to match on the same CustNo as the primary table’s CustNo if you want to enforce more tables to match on CustNo manually). If you set an alias for the table you want to designate as the primary table, use the alias as the Primary Table. If you did not set an alias, use the full table name.

You are able to select which options will be available to the report users for save files through the Allow Export to the Following: section. Simply uncheck the checkboxes to the left of the file types you do not want to be available to users and they will be excluded from the save file prompt dropdown.

Note: You must not include CustNo as a column in your SQL statement as the Reporting Engine does this automatically for you and manual inclusion would result in a logical conflict.

Note: You must not include any duplicate column names in your SQL statement. If you have two columns with the same name (even if from different tables), make sure to use unique aliases for the columns.

Note: You must go through the approval process when creating a new report or modifying an existing report. Once a new report or change to an existing report has been approved, you may map the columns appropriately in the following step.

After defining the basic report components, proceed to the next (and final design step) and define which columns you would like to display in the resulting report. This page will populate with each column derived from your SQL Query and allow you to configure the function(s) performed by each column in the report. In order to use the column in any way within the report, you are going to need to give the column a report field name. This is done by populating the text field next to each column you wish to use within the report (Each column will have its value as seen in the SQL mapped to its report name. If you wish to not include the column in the report, please clear this field. It is recommended not to have any fields in your query that are not useful in the report in any way for the sake of performance.)

After giving a column a report field name, you need to make three decisions:

1.Do you want the user to be able to filter the report by that column? If so check the filterable checkbox.

2.Do you want the column to provide a drillthrough to another report or record within the MIE? If so, check either the Drillthrough checkbox or the Allow Record Edit checkbox.

a.Drillthroughs* require the SSRSToReport Parameter to be populated and the value for that parameter comes with a dropdown to select the destination report for your drillthrough. Additionally, you may choose to pass additional parameters to filter the destination report based on parameter values.

b.Allowing the user to edit a record from a report requires defining a Record Form and a Primary Key Field (PKField for short). The Record form is an integer value that represents the form in which the record can be edited within the MIE. The PKField is the field within the report that is the primary key for the record you wish to link to. The field is required to be within the SQL query and mapped to a report field name. If the column is only to be used for a PKField, then check the Exclude Column checkbox to exclude the column from being displayed within the report.

3.For any reason, if you decide that the column should no longer be shown within the report, you may opt to exclude any column from the report by checking the Exclude column checkbox.

*When setting up the parameter values for a drillthrough, you can add another parameter by clicking the Add Parameter button beside the last parameter for that drillthrough. There is a maximum of 5 functional parameters for a drillthrough.

You can remove a parameter within a drillthrough at any time by clicking on the red X symbol to the left of the parameter name. This will remove the parameter, leaving any other parameters intact.

Giving the users the ability to filter on date columns in not recommended as date filters are restricted to only the year. Other attempts to filter dates may not display the results the user would expect.

Notes:

•Please ensure that your SELECT Query runs in SQL Server Management Studio before using the query within the report designer. If the query does not work, it may take time to go through the approval process multiple times.

•When moving a Standard Interactive Report between Customers, make sure to update your column mappings within the new Customer through the designer, or the report will not show any columns and likely fail to render.

oOriginal Column Mappings will persist within the original customer after the report is moved and will still function correctly if the report is moved back to the original customer later.

•Make sure to update (save) column mappings after SQL changes within the report. If a column name is changed or if a column is no longer present in the SQL, but was previously mapped, the report will not delete the old mapping until the mappings are saved again. Failure to remove the old mapping may result in the report failing to render.

•Standard Interactive Reports will not appear in the reports page alongside CSV, Excel, and HTML reports.

•The standard interactive report designer does not create a dashlet to link to the report you are creating. You will need to create the dashlet to link to the report for yourself at the moment.

•The < Character restriction from <Reference SSRS Character Restrictions here as “SSRS Character Restrictions”> applies to the Report Title field for Standard Interactive Reports. Please avoid using this character to get the title display you desire. The > character is fine.