--- title: "Community Query Library" canonical: "https://kb.myframeworks.com.au/space/FRAM/28380068/Community%20Query%20Library" format: markdown --- # Overview Here you will find a list of community-shared query reports that are not part of the regular deployed and supported product. This List of queries can be downloaded and added to the Query Builder which is a flexible, user-friendly database query tool that can be used to interrogate data from the underlying database(s). > ✅ # How to load a new query to the library > ✅ > ✅ To share a Query with the Sterland Community please email [communitylib@sterland.com.au](mailto:communitylib@sterland.com.au?subject=New%20Community%20Query%20to%20be%20added%20to%20the%20Library) and in the email: > ✅ > ✅ 1. Add the **name of your report**. > ✅ 2. Give a **brief description** of what the report does. Maybe a small example. > ✅ 3. Add **Who created it**. Name and Company name would be great, as it's nice to be recognised for your hard work. > ✅ 4. Attach a copy of the **.nqy file **to be** **added to the page > ❌ The queries listed below are not covered in the Sterland support agreement. # Repository of Query Reports To download and use a Query Report, click on the link in the Report Download Link column and save the .nqy file to your computer to be imported into the query tool. ### Stock Valuation for Type 0 Excess - By Branch **Description: Excess Type 0 (Stocked)** (Excluding items created in last 12 months) These products are products that you range/stock. Based on average monthly sales, this will give you a view of **what items you have a quantity greater than 12 months average sales.** > **Example** > > Average monthly sales 5 (12 mths x 5 = 60). SOH = 100. Excess = 40. > > Excess = SOH greater than Monthly average sales x 12. **Creator: **Mark Stewart **Report Download Link: **[Stock Valuation for Type 0 Excess - By Branch.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Stock%20Valuation%20for%20Type%200%20Excess%20-%20By%20Branch.nqy?version=1&modificationDate=1727312205592&cacheVersion=1&api=v2) --- ### Stock Adjustment **Description: **This query will list **Stock Adjustments done** in Frameworks or Prostix based on the **date** and **GL code** entered.  It lists the **branch**, **item**, **adjustment** **quantity**, **line** **cost**, **reason**, **reference** and **user**, then **totalled by product group.** ![image-20241001-073445.png](media://20c1f69a-195d-4c09-8d89-322e3c8a171c) **Creator:** James Grech - Porters **Report Download Link: **[Stock Adjustment Report.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Stock%20Adjustment%20Report.nqy?version=1&modificationDate=1727312204998&cacheVersion=1&api=v2) --- ### Products Changed to Not Stocked in a Branch **Description: **This query lists items where the branch record has changed from 0 (stocked) to 1 (not stocked).  Also includes the quantity on hand and the prime bin location. ![image](media://4d559ae7-0d80-496d-bf62-0ea98bdd24a8) **Creator:** James Grech - Porters **Report Download Link: **[Products Changed to Not Stocked in a Branch.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Products%20Changed%20to%20Not%20Stocked%20in%20a%20Branch.nqy?version=1&modificationDate=1727312204764&cacheVersion=1&api=v2) --- ### Products Changed to Runout (Type3) **Description: **This query lists products that have changed to product type 3 (Run Out) in the master inventory database.  It shows which branches have stock and the quantity.  Users enter in a date range. ![image-20241001-073606.png](media://b4dd7c1c-a948-4577-be59-885d1e9b22ba) **Creator:** James Grech - Porters **Report Download Link: **[Products Changed to Runout (Type3).nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Products%20Changed%20to%20Runout%20(Type3).nqy?version=1&modificationDate=1727312204112&cacheVersion=1&api=v2) --- ### Goods In Transit - Branch Transfers **Description: **The Goods in Transit – Branch Transfer reports branch transfers that have been released at the supply branch and not yet stock receipted in the receiving branch. It asks for the branch number. ![image](media://ac470194-f5a1-4c5b-ace0-6e72af161bfe) **Creator:** James Grech - Porters **Report Download Link: **[Goods In Transit - Branch Transfers.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Goods%20In%20Transit%20-%20Branch%20Transfers.nqy?version=1&modificationDate=1727312203509&cacheVersion=1&api=v2) --- ### Stocktake - Items in Branch Not Counted **Description: **This report allows you to identify Branch Stock of Items not counted in a stocktake. This is also known as ghost Items or items missed in the stocktake Count. **Creator:** Daniel Hackier - Bretts **Report Download Link: **[Stocktake - Items In SOH but not counted.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Stocktake%20-%20Items%20In%20SOH%20but%20not%20counted.nqy?version=1&modificationDate=1727312202191&cacheVersion=1&api=v2) --- ### Customer on Hold or at Risk **Description: **This report provides a list of customers who are either on hold or close to reaching their credit limits (An Alert). This report gives Sales Reps insight into their Customer's Credit Situation and potentially assists in having conversations regarding payment options. **Creator:** Daniel Hackier - Bretts **Report Download Link: **[Customer on Hold or At Risk.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Customer%20on%20Hold%20or%20At%20Risk.nqy?version=1&modificationDate=1727312201916&cacheVersion=1&api=v2) --- ### Reorder branch details with locations **Description: **This report contains all branch details that affect auto-reorder, used to find stock lines not set up for reorder correctly. **Creator:** James Davidson - Sapphire Hardware **Report Download Link: **[Reorder Branch detail with location.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Reorder%20Branch%20detail%20with%20location.nqy?version=1&modificationDate=1727312200229&cacheVersion=1&api=v2) --- ### Gap scan report **Description: **This report is used after items out of stock have been scanned via PDA into a stocktake card and shows reorder details so errors can be corrected. **Creator:** James Davidson - Sapphire Hardware **Report Download Link: **[Gap Scan Report.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/GAP%20SCAN%20REPORT.nqy?version=1&modificationDate=1727312200755&cacheVersion=1&api=v2) --- ### COD & rewards account with balance owing **Description: **This report is used to find cash accounts that have been used as a normal debtor account. **Creator:** James Davidson - Sapphire Hardware **Report Download Link: **[COD and Rewards accounts with balance owing.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/COD%20%26%20Rewards%20accounts%20with%20balance%20owing.nqy) --- ### Price overrides **Description: **This Report contains a substantial amount of detail about price overrides, who, how much, margin lost, etc, etc (best run via schedule at night as it will access the trndet fields). **Creator:** James Davidson - Sapphire Hardware **Report Download Link: **[Price Overrides by branch.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Price%20Overrides%20by%20branch.nqy?version=1&modificationDate=1727312200481&cacheVersion=1&api=v2) --- ### Future Price report **Description: **This report is a quick way to see all stock lines with a future cost or sell change and the variance. **Creator:** James Davidson - Sapphire Hardware **Report Download Link: **[Future Price report.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Furture%20Price%20report.nqy?version=1&modificationDate=1727312201022&cacheVersion=1&api=v2) --- ### EPC compare to stockfile **Description: **This report uses barcodes to match stock lines and Epc items, can set % cost difference to match, Epc supplier etc. **Creator:** James Davidson - Sapphire Hardware **Report Download Link: **[epc compare to stock file.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/epc%20compair%20to%20stock%20file.nqy?version=1&modificationDate=1727312201318&cacheVersion=1&api=v2) --- ### Sales Customer Invoice Export **Description: **Export of customer invoices daily – Customer can then import directly into their system **Creator:** Matt Celima - Burdens Plumbing **Report Download Link: **[Sales Customer Invoice Export.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Sales%20Customer%20Invoice%20Export.nqy?version=1&modificationDate=1727312199962&cacheVersion=1&api=v2) --- ### Contract Review **Description: **Review customer or national contract via Excel spreadsheet showing fully rebated cost/GP **Creator:** Matt Celima - Burdens Plumbing **Report Download Link: **[Contract Review.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Contract%20Review.nqy?version=1&modificationDate=1727312199692&cacheVersion=1&api=v2) --- ### Customer Contract Pricing for Quarterly Pricing Review **Description: **Compares customer special pricing to the customer's normal discount group pricing, including GP%. The fields also show the sales rep for the customer and qty sold on the contract. Run this report every quarter and give it to the sales reps and managers to review their customers special pricing. **Creator:** Sarah Elliot - Thomsons ITM **Report Download Link: **[Customer Contract Pricing for Quarterly Pricing Review.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Customer%20Contract%20Pricing%20for%20Quarterly%20Pricing%20Review.nqy?version=1&modificationDate=1727312199448&cacheVersion=1&api=v2) --- ### Inactive and Excess **Description: **This report shows you all inactive products from date range – no sales, quotes, SOH **Creator:** Tom Johnston - Sterland (for Burdens) **Report Download Link: **[Inactive and Excess.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Inactive%20%26%20Excess.nqy?version=1&modificationDate=1727312199129&cacheVersion=1&api=v2) --- ### Stocktake Adjustment Product Detail **Description: **This report uses the stock_adj table to reproduce a stocktake variation with a product detail report after a stocktake has been applied. The stocktake date applied is the Date parameter required. **Creator:** Aaron Zambelli - Williams Group Australia **Report Download Link: **[Stocktake adjustment product detail.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Stocktake%20adjustment%20product%20detail.nqy?version=1&modificationDate=1727312198877&cacheVersion=1&api=v2) --- ### Quote Cost Worksheet **Description: **This is a report that pulls a quote from frameworks and displays the costs, and GP, to either print or export as a spreadsheet. **Creator:** James Grech - Porters **Report Download Link: **[Quote Cost Worksheet.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Quote%20Cost%20Worksheet.nqy?version=1&modificationDate=1727312198611&cacheVersion=1&api=v2) --- ### Sales by Date by Branch **Description: **This report replaces the old Prostix 9-9-D report. Summarises sales $ and GP% per branch per day into Account, Cash, Delivery and Pick Ups. **Creator:** Maggie Game - Bowens **Report Download Link: **[Sales By Date by Branch.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Sales%20By%20Date%20by%20Branch.nqy?version=1&modificationDate=1727312198337&cacheVersion=1&api=v2) --- ### Till Log – Negative Account Payments **Description: **Till log list of only negative account payments for today. **Creator:** Maggie Game - Bowens **Report Download Link: **[Till Log -Negative Account Payments.nqy](https://sterlandsupport.atlassian.net/wiki/download/attachments/28380068/Till%20Log%20-Negative%20Account%20Payments.nqy?version=1&modificationDate=1727312198024&cacheVersion=1&api=v2) --- ### Back Orders Created **Description: **Define the date and branch for a list of back orders generated. **Creator:** Darren Donald - Sunshine Mitre 10 **Report Download Link: ** --- ### Back Orders x Customer **Description: **Define date, branch and customer number for a list of back orders generated **Creator:** Darren Donald - Sunshine Mitre 10 **Report Download Link: ** --- ### Negative Cash Sales **Description: **The report shows the branch, account, Customer, product number and description, and the user id for the transaction. **Creator:** James Grech - Porters **Report Download Link: ** --- > ℹ️ Some filters within the reports may be specific for the businesses that have provided them, please check and update accordingly. # Using a Query Report > ⚠️ To create your queries, you will require a developer's license from Sterland. To use one of the above query reports, you will need to have access to the Query Runtime tool, which may be located on your Frameworks server If you are already using the query tool, you will need to: 1. Download and save the query report you want to use. 2. Open the **Query tool.** 3. Import the **.nqy **file. > ℹ️ If you are using the Query Runtime tool, you will need to select an existing query then go to file and import. 4. Run the Query. > ✅ Refer to [Publishing a Report](https://sterlandsupport.atlassian.net/wiki/spaces/FRAM/pages/28392124) to run the imported query from within Frameworks. # Additional Information > ✅ For more information and help about getting a developer's license and query reports, visit our [Support](https://kb.myframeworks.com.au/page/support) page.