Below is a full knowledge-base article you can drop into your docs or help center.

---

SQL Data Reports – Creating, Editing, and Running Custom SQL Reports

The SQL Data Reports feature lets you build your own custom reports using SQL, save them for reuse, and export the results directly to Excel or CSV. It also supports dynamic prompts, so you can ask the user for values (like dates, SKUs, or customer IDs) at run time.

This article explains:

1. What the SQL Data Reports screen does
2. How reports are stored
3. How to run an existing report (Excel / CSV)
4. How to create a new report
5. How to edit and delete existing reports
6. How to use dynamic prompts with {placeholders}
7. Tips for writing your own SQL queries
8. Troubleshooting and common questions

---

1. Overview of the SQL Data Reports Screen

The SQL Data Reports form has three main areas:

1. Report Name list (left)
   • Lists all saved reports (e.g., Coupon Redemptions by Coupon Code, Purchase Order History by SKU).
   • Click a name to select a report.
2. Preview SQL box (right)
   • Shows the SQL statement associated with the selected report.
   • You can edit this SQL when you are in edit mode (after clicking Save SQL or Add New).
3. Buttons (bottom)
   • Open in Excel – runs the selected report and exports the results to an .xlsx file.
   • Open CSV – runs the selected report and exports the results to a .csv file.
   • Delete – deletes the selected report definition.
   • Save SQL – saves changes you’ve made to the SQL for the selected report.
   • Add New – creates a brand-new report definition.

---

2. How Reports Are Stored

Each report is stored in a table (typically named SQLReports) with at least:

• ID – Numeric primary key used internally.
• ReportName – Friendly name that appears in the Report Name list.
• SQLStatement – The actual SQL text that will be run when the report is executed.

You don’t need to interact with this table directly—everything is managed through the SQL Data Reports form—but it’s useful to know what’s happening behind the scenes.

---

3. Running an Existing Report

3.1 Selecting a Report

1. Open the SQL Data Reports screen.
2. Click a name in the Report Name list (left).
   • The corresponding SQL appears in the Preview SQL box (right).

If no report is selected and you click Open in Excel or Open CSV, you’ll see a message asking you to select a report first.

3.2 Running to Excel (Open in Excel)

1. With a report selected, click Open in Excel.
2. The system:
   • Retrieves the saved SQL.
   • Processes any dynamic prompts ({…}) in the SQL (see Section 6).
   • Ensures the query is a SELECT, TRANSFORM (crosstab), or PARAMETERS query.
     

     Action queries like UPDATE, DELETE, INSERT are not allowed for safety.

   • Creates a temporary query (z_TempExport) from your SQL.
   • Exports the results to an Excel file in your Documents folder.
     • File name format:
       ReportName_YYYYMMDD_HHNNSS.xlsx
       (for example: Purchase Order History by SKU_20251113_142305.xlsx)
   • Opens the generated Excel file automatically.
3. After the export, the temporary query is deleted to keep the database clean.

3.3 Running to CSV (Open CSV)

The Open CSV button works similarly:

1. Click Open CSV with a report selected.
2. The system:
   • Runs the same SQL and dynamic prompt logic.
   • Exports the results to a CSV file in your Documents folder.
   • Opens the CSV file in your default program (often Excel).

File name format:
ReportName_YYYYMMDD_HHNNSS.csv

Use CSV if:

• You want a simple, plain-text export.
• You plan to import the data into another system.

---

4. Creating a New SQL Data Report

You can create your own report from scratch or by copying an existing one.

4.1 Starting a New Report

1. Click the Add New button.
2. You’ll see an Input Box asking for the report name (for example: Sales by State Last Month).
3. Enter a clear, descriptive name and click OK.

Behind the scenes:

• A new record is created in SQLReports.
• The form refreshes, selects the new report, and places the cursor in the Preview SQL box so you can start typing your SQL.

4.2 Entering the SQL

In the Preview SQL box:

1. Type or paste your query.
2. Make sure the statement is one of:
   • SELECT …
   • TRANSFORM … (for crosstab queries)
   • PARAMETERS … followed by SELECT …

Examples:

Simple SELECT:

SELECT OrderNumber, OrderDate, GrandTotal
FROM Orders
ORDER BY OrderDate DESC;

Crosstab by Month and State:

TRANSFORM Sum(Orders.GrandTotal) AS SumOfGrandTotal
SELECT Orders.ShipState
FROM Orders
GROUP BY Orders.ShipState
PIVOT Format([OrderDate], "yyyy-mm");

4.3 Saving the Report

1. After entering or editing the SQL, click Save SQL.
2. The SQL text is saved into the SQLReports table for the currently selected report.
3. You can now use Open in Excel or Open CSV to run it.

If there are syntax errors in your SQL, you’ll receive an error message when the query is created or when the export runs. Use that message to adjust your SQL, then click Save SQL again.

---

5. Editing or Deleting Existing Reports

5.1 Editing a Report

1. Select the report in the Report Name list.
2. The existing SQL appears in the Preview SQL box.
3. Make your changes directly in the Preview SQL box.
4. Click Save SQL.

Your changes will be saved under the same report name (the ReportName field does not change unless you edit it via the table or a separate maintenance screen).

Tip: If you want to create a variation of a report (e.g., same structure but with different filters), you can:

1. Select an existing report.
2. Copy the SQL from the preview box.
3. Click Add New, name the new report.
4. Paste and adjust the copied SQL.
5. Click Save SQL.

5.2 Deleting a Report

1. Select the report in the Report Name list.
2. Click Delete.
3. Confirm the deletion when prompted.

This removes the report definition from the SQLReports table. It does not delete any database tables or data—only the report definition.

---

6. Using Dynamic Prompts with {placeholders}

One of the most powerful features of SQL Data Reports is the ability to prompt the user for values when the report runs.

6.1 How It Works

• Anything typed inside curly braces { } in the SQL is treated as a placeholder.
• When you run the report (Excel or CSV), the system:
  1. Scans the SQL text for {…}.
  2. For each placeholder, shows an InputBox.
     • The text inside the braces is used as both the Prompt and the Title of the box.
  3. Whatever the user types is substituted into the SQL in place of {…} (the braces are removed).

If the user cancels or leaves the value empty, they are asked whether they want to cancel the entire report run.

⚠️ Important: The system does not automatically add quotes or # signs around the entered value. You must include those in your SQL around the {…} placeholder if your field type requires them.

6.2 Example – Text Input (like SKU)

Stored SQL:

SELECT POHistory.PONumber,
       POHistory.Quantity,
       POHistory.Cost,
       POHistory.LocalSKU,
       POHistory.Date,
       POHistory.TimeStamp
FROM POHistory
WHERE POHistory.LocalSKU = "{Enter SKU}"
ORDER BY POHistory.Date DESC;

When you run the report:

1. You see an InputBox with the prompt/title: Enter SKU.
2. You type: ABC123.
3. The final SQL sent to the database becomes:

WHERE POHistory.LocalSKU = "ABC123"

6.3 Example – Date Input

Stored SQL:

SELECT *
FROM Orders
WHERE OrderDate = #{Enter an Order Date}#;

At run time:

1. InputBox prompt/title: Enter an Order Date.
2. You type: 11/13/2025.
3. Final SQL:

WHERE OrderDate = #11/13/2025#;

Here the # signs are outside the {} so they remain in the final query and correctly mark the value as a date literal.

6.4 Example – Date Range

Stored SQL:

SELECT *
FROM Orders
WHERE OrderDate BETWEEN #{Start Date}# AND #{End Date}#
ORDER BY OrderDate;

Running the report:

1. Prompt 1: Start Date
2. Prompt 2: End Date

If you enter 11/01/2025 and 11/30/2025, the final SQL becomes:

WHERE OrderDate BETWEEN #11/01/2025# AND #11/30/2025#

6.5 Example – Numeric Input

For numeric values, don’t put quotes or # around the placeholder:

SELECT *
FROM Orders
WHERE GrandTotal >= {Minimum Total}
ORDER BY GrandTotal DESC;

If you enter 100, the final SQL is:

WHERE GrandTotal >= 100

---

7. Tips for Writing SQL for Data Reports

7.1 Start with the Access Query Designer

If you’re not comfortable writing SQL from scratch:

1. Open the Access Query Design window.
2. Build your query using the visual designer.
3. Switch to SQL View.
4. Copy the SQL into the Preview SQL box in the SQL Data Reports form.
5. Add any {placeholders} as needed.
6. Click Save SQL.

This gives you the best of both worlds: a visual builder plus reusable, dynamic reports.

7.2 Keep Queries Readable

• Use line breaks and indentation:

  SELECT OrderNumber,
         OrderDate,
         ShipState,
         GrandTotal
  FROM Orders
  WHERE OrderDate >= #{Start Date}#
    AND OrderDate <  #{End Date}#
  ORDER BY OrderDate DESC;
  

• End with a ; (semicolon) if you like—it’s optional but neat.

7.3 Use Aliases for Friendlier Column Names

Excel will show the field names exactly as you define them. Use aliases for readability:

SELECT OrderNumber      AS [Order #],
       OrderDate        AS [Order Date],
       ShipState        AS [Ship State],
       GrandTotal       AS [Total Amount]
FROM Orders;

7.4 Be Careful with Filtering Logic

• Use parentheses to control the logic of AND/OR:

  WHERE ShipState IN ("PA","NJ","DE")
    AND OrderDate >= #{Start Date}#
  

  vs

  WHERE ShipState IN ("PA","NJ","DE")
    AND (OrderDate BETWEEN #{Start Date}# AND #{End Date}#)
  

7.5 Limit the Result Size When Testing

When designing new reports, start small:

SELECT TOP 100 *
FROM Orders
ORDER BY OrderDate DESC;

Once you’re confident the query is correct, remove the TOP 100 to run against the full data set.

7.6 Only Use Safe Query Types

The system intentionally restricts you to:

• SELECT
• TRANSFORM
• PARAMETERS (with a following SELECT)

This prevents accidental updates or deletions. If your query starts with UPDATE, DELETE, or any other action statement, it will be blocked with a message like:

“This SQL must be a SELECT or TRANSFORM (crosstab) query to export.”

---

8. Troubleshooting & FAQs

8.1 “Please select a report name first.”

You clicked Open in Excel, Open CSV, Save SQL, or Delete without a report selected.
→ Click one of the report names in the left list first.

8.2 “No SQLStatement found for the selected report.”

The report record exists but has an empty SQL field.

• Click Save SQL after entering valid SQL, or
• Edit the report in the SQLReports table, or
• Delete the report and create a new one.

8.3 “This SQL must be a SELECT or TRANSFORM (crosstab) query to export.”

The first word of your SQL is not SELECT, TRANSFORM, or PARAMETERS.

• Open the query in the Preview SQL box and adjust it so it starts with one of these keywords.

8.4 “Error XXX: …” when running or exporting

Access detected a problem with your query. Common causes:

• Table or field names misspelled.
• Missing quotes or # around date/string literals.
• Incorrect use of {placeholders} (for example, forgot to add quotes around string placeholders).

Double-check your SQL and test it in Query Design → SQL View if needed.

8.5 The prompt appears, but the query returns no results.

The SQL ran, but your filter values might be too strict or not match any data. Try:

• Broadening your date range.
• Checking for typos in SKUs or IDs.
• Removing some filters to confirm the base query works.

---

Summary

The SQL Data Reports feature turns your Access or SQL database into a flexible reporting engine:

• Save named reports with reusable SQL.
• Export results to Excel or CSV with one click.
• Use {placeholders} to prompt users at run time for SKUs, dates, ranges, and more.
• Build queries visually, then refine and store them in the form.

With a handful of well-designed SQL Data Reports, you can give your users quick access to ad-hoc analysis, historical information, and operational data—without needing to constantly modify canned reports.