Adding-Analysis-Codes-&-Report-Filters-in-Sage-200c

Adding Analysis Codes & Report Filters in Sage 200c

In Sage 200 by Kerry-Lee Rodighiero

Spread the love

Include Analysis Codes to Sage 200c

Analysis codes were added to the 2017 update to Sage 200c and brought with them a whole new dimension to reporting. Enabling you to analyse your business by different criteria, which you get to choose.

Analysis codes can be added to your Purchase Ledger, Sales Ledger, invoices and stock items as filters.  For example, you may sell bottled water and set up analysis codes for regions. This will allow you to see which areas buy the most 500ml bottles and which buy the most 1 litres bottles.  Your analysis codes may be your sales reps, so you can monitor who sells the most 500ml bottles, or so you can pay commission on sales or profit.

All your analysis codes, which will be used in the different ledgers are set up and held in one place. Think of it as your analysis code storeroom.

Accounting System Manager > Settings > Maintain Analysis Codes

In there you will see Codes in the top section and Values in the bottom.  In the ‘Codes’ section you put the question and in the ‘Values’ section is the answer.

Q: Who are my sales reps?

A: Jane, John, Mary and Peter

maintain-analysis-code-values

We are going to add Sales Reps to our stock report so we can pull reports by Sales Reps.  Enter a line for ‘Sales Reps’ in the Codes section – and tick the box to ‘Add New on Entry’ – with that line selected, add the rows of your sales reps names in the Values section – do not mark as Default, you want to specify the rep on each stock item, or leave blank as default.

The Sales Rep analysis code is now in your storeroom.  You now have to apply that analysis code to the relevant ledger.

Do your reps look after particular customers or do they look after particular stock items? If customers, you need to apply the analysis codes to your sales ledger you would follow this path: Sales Ledger > Utilities > Ledger Setup > Maintain Analysis Codes.

We are going to apply the code to our Stock items: Stock Control > Stock Maintenance >  Maintain Analysis Codes. You can specify to make the field mandatory if you wish.

maintain-analysis-code

Note that, by default, Sage names them Analysis Code 1, Analysis Code 2, and so on.  You can rename the fields to something more meaningful, but the reports will always see the field by the original name.  The lines are numbered so it’s easy enough to figure out what they will be called on the reports.

Adding or Amending an Item

When you add a new or amend a stock item, there is a tab for analysis codes where you can specify the Value for that item:

analysis-code-stock-item

You can now include your sales rep to your stock item list view where you can view and filter by rep.

analysis-code-stock-items

See our previous blog on how to set this up: 4 Ways to Simplify your Searches in Sage 200c

Include Analysis Codes to a Sage 200c Stock Report

With that done, we now want to add the analysis code to a report.

USEFUL TIP:  No need to start from scratch, you can amend a report already in the system.  The first thing is to locate a suitable, adaptable report. We have one for Stock Profit with a filter by Product Group. To find it, run report designer from the tools menu: ‘Run Sage Report Designer’

analysis-code-run-report-designer

There are dozens of available reports, most of which you probably didn’t even know where there!  The most difficult part of designing reports is the expertise required in selecting the correct report and the correct field!

Navigate to it from the File menu > Open > Sage\reporting. Sage 200c will first look for any customised labels, layouts, letters, reports or templates in a specific order. The route it takes is:

  1. Company folder – multi-company systems can have documents customised with logos and layouts for each company. Inside that folder will be folders for each company with the EXACT name of the company as it exists in Sage 200c.
  2. Custom folder – if Sage doesn’t find a report named Stock Profit Report in the company folder it then looks in the Custom folder.
  3. Default folder – if Sage finds nothing in the Custom folder, the last stop is the default folder where all the generic reports and documents are saved. In other words, if it’s a report which has never been edited before, it will be in the Default folder.

Head over to Sage\reporting\default\reports. The length of the list will depend on the modules you have on Sage 200c. This report is about stock so it will be in the stock folder. There are dozens of reports in there; most of which you probably didn’t know existed.  Find the Stock Profit Report and double-click to launch it.

Report Designer

The report will open in Report Designer mode, note the field showing the ‘Product Group’ field.

analysis-code-report-designer

Click the Preview button on the bottom right of the screen to preview the report function

The report will launch, allowing the user to filter by Product Group. For example, select ‘Decorating’ and the report will show only items Sold (and Returned) under the Product Group Decorating.

 

analysis-code-stock-report

We are going use that useful tip and adjust this report by changing the Product Group field to Reps, so instead of pulling the profit report by category, we can pull it by the sales rep.

Step 1: Rename and save the report

Note the path: The folder has changed from Default to Custom and ‘copy’ has been added to the name. (Good to know, so that you won’t save over the original).

With the name changed we can now customise the report. Hold the Ctrl key and click on both the ‘Product Group’ field and the ‘Code’ field – you are removing both the database field and the title. You now need to add the sales rep field by pulling in the database field from the Variables pane on the left-hand side. There are a lot of options! Where do you find it?

Here’s a clue, which is a good place to start:

Anything to do with: Will be in here:
Customers SLCustomerAccounts
Suppliers PLSupplierAccounts
Nominal Accounts NLNominalAccounts
Stock items StockItems
Invoicing and Sales Orders Header – SOPOrderReturns

Lines – SOPOrderReturnLines

Purchase Orders Header – POPOrderReturns

Lines – POPOrderReturnLines

We are amending a Stock report so click on stock items, and there you will find the Analysis codes.

analysis-code-variables

Remember, I said you can rename the field to something more meaningful but the database will still see the field as AnalysisCode1, AnalysisCode2, etc? If you go and check where we set these up, you can see Sales Reps is Analysis Code 1. Click and drag Analysis Code 1 from the Variables pane onto the form, to the location where you deleted the Product Group. As you let go, you will get a popup asking to confirm a few details:

analysis-code-report-edit

Changing the Criteria

Change the default to add a title to the left and change the wording of the title. You’ll need to ungroup the title and field so you can position and resize them correctly. Right-click to Ungroup. The next step is to configure the properties for the AnalysisCode1 field. The properties pane is on the right-hand side.  You will change the ‘Criteria’ field, meaning the criteria you want to show on the report to filter by.

Currently. you are showing the criteria for Product Group. What you want is to show the report by Sales Rep. Ensure the properties pane is showing the report details. You are looking for the Criteria option. If it’s not there you may be looking at the properties of the incorrect field.

Click anywhere on the background, below the form and you will get the correct properties pane for the report:

analysis-code-properties

Click the 3 dots at the end of the Criteria row to drill down to the criteria options:

Create or Modify?

Note on the bottom there is an option to ‘Show Disabled’ criteria.  If the one you want is in that list you can enable it! If it is not you need to add a new one.  The StockItems Analysis codes are not in the disabled options so you will need to add a new one. Click ‘new criteria’ and select the filters for the database fields you want to filter on. StockItems is the table and AnalysisCode1 is a field in that table:

analysis-code-manage-criteria

Click OK and change the fields to match the screen.  You want the field to say Sales Rep, not AnalysisCode1. When the user selects Sales Rep to filter on, on the report, they will want a drop-down to select the Sales Rep’s name from. Next we will ensure that happens. The top line shows the ‘Database Field to Filter on’. Copy the contents of that field. Click Edit Query and the screen will launch. Complete the fields below, note you are copying the Query above the editable block; Paste (Ctrl + v) to add the criteria.

“SELECT DISTINCT StockItems.AnalysisCode1 FROM StockItems”

analysis-code-criteria-pane

Click test and the reps will show! The last thing to do is to disable the Product Group filter, which you no longer need. Click Modify and in the status field, select Disabled. Now you’re ready to run the report. Click the preview button on the bottom of the screen. Select Sales Rep Is: Jane Doe

analysis-code-stock-profit-report

Conclusion

Congratulations, you have configured a report using Analysis Codes in stock!

Read our blog to learn how to add your new report to your menu.

Sage 200 Training CTA

 


Spread the love