Home » Archives for admin

Author: admin

Learn how to switch output modes when running a report in Sage Intelligence

Did you know that apart from being able to run reports out to Excel, Sage Intelligence allows you to switch output modes which can be useful when designing and debugging reports?  Two additional options are provided, output to Screen and output to SQL.
Output to Screen does away with rendering a report to Excel and instead, outputs the data to a dialogue window.  This is useful if you would like a quick way to view the data that is output for a report.  Output to SQL also does away with rendering to Excel and displays the full SQL query that is executed for a report in a dialogue window.  This is useful if you would like to view the SQL query in case you are receiving errors while running a report or would like to modify the query.
To switch the output mode, do the following:
1. In the Report Manager, right click on the Home object and select “Switch Output Mode” from the menu that appears.

2. From the dialogue that appears select the option you want to use and click OK.

3. Now when you run out a report, it will be output based on the option you selected.
The following image is an example of a Sales report run out to Screen mode.  Take note that from this dialogue, you can export the data to Excel.  This is different to running your report out to Excel.

The following image is an example of the same report run out to SQL.  From here you can test it to see that it executes successfully against your database, and you can continue in which case the data will be displayed in the same output screen as above.  Take note that you can’t edit the SQL in this view.  If you want to be able to make changes to the query, you will need to do so in the Sage Intelligence Connector module.

The post Learn how to switch output modes when running a report in Sage Intelligence appeared first on Sage Intelligence.

Powered by WPeMatico

Using Microsoft Excel as a Data Source in Sage Intelligence

You may be aware that Sage Intelligence allows you to connect to any ODBC compliance data source, meaning that you can access and report on information from just about anywhere in your organisation.  Included is the ability to use Microsoft Excel as a data source, which is useful in cases where your business management or accounting solution doesn’t allow you to capture specific information you need to keep track of.
As an example, in my business, a number of my sales are initiated through Social Media and I want to keep a record of this.  Although I could probably capture this in a comment field in my accounting system, I have chosen to use Excel.  An instance of the information is shown below and in this tip, I’m going to show you how you can pull the information into Sage Intelligence.

The first step to using Excel as a data source is preparing the workbook by creating a named range of the data you want to report on.  To do this:
1. Select the data you want to include. Take note that it needs to be formatted as a normal data range with column headings.
2. Then in the cell reference field, enter a name to represent (refer to) the information and press enter. I’m going to call mine ‘SocialSalesData’.

3. Next, save the workbook, but be sure to set the save as type to Excel 97-2003 Workbook / .xls.

The workbook is now set up and you can connect to the information in it using the Sage Intelligence Connector module.
1. In the Connector, locate the ODBC Driver for Excel connection type.
2. Select it and then click Add Connection on the Home tab on the menu ribbon.

3. In the Connection Info dialogue, give your connection a name.
4. Under the Excel Workbook field, navigate to your Excel file that you saved.
5. Click Add.

6. Your connection will be shown under the connection type. Add a container to it by selecting it and clicking Add Container on the Home tab.
7. From the Container Type dialogue, select Table and click OK.
8. Then, under the Publish Data dialogue, you’ll see the named range you defined in your workbook brought through as a table. Select it and click OK.

9. Your container will be added under the connection, and the last thing to do is add your expressions by selecting the container and clicking Add Expressions on the Home tab.
10. From the Expression Type dialogue, select Data Field(s) and click OK. Then, under the Publish Fields dialogue, you’ll see all the fields that were present in your workbook.  Select the ones you want to include in your container and click OK.

Your container is now complete, and you can make use of it in the Report Manager like you would any other container.  Once you have defined a report from it, instead of just using it on its own, you could combine it with your sales report for example, through the use of a Union Report.

If you would like more information of connecting to different data sources through the Sage Intelligence Connector module, then take a look at the following webcast:
Power Up your BI across your Organisation
The post Using Microsoft Excel as a Data Source in Sage Intelligence appeared first on Sage Intelligence.

Powered by WPeMatico

How to work with numbers containing more than 15 digits in Excel

Numbers containing more than 15 digits in Excel are not often used, however some users might use them when recording credit card numbers, account numbers, stock codes, etc.
Applies To: Microsoft® Excel® for Windows 2013 and 2016.
Excel can’t handle more than 15 digits per cell, and so when these numbers are entered, Excel stores the first 15 digits and replaces all remaining digits with zeros.
For example; the number 1234567891234567 is stored as 1234567891234560
   
As these large numbers are NOT going to be used in a calculation, the issue is easily resolved, by simply formatting the cell to Text before entering the numbers.
Here’s how to do it:
Select the cells of the columns where the numbers will be stored.
On the Home tab, select the Number Format drop-down.

Scroll all the way to the bottom of the list and select Text.

Enter required numbers as usual.

A note to remember: if you copy and paste numbers from a different area to these formatted cells, you’ll need to use Paste Special Values or Paste as Text to retain the Text format in the cells. However, if used correctly, this will enable you to use more than 15 digits in Excel.
The post How to work with numbers containing more than 15 digits in Excel appeared first on Sage Intelligence.

Powered by WPeMatico

Making use of Wildcards in your Reporting Trees

Reporting Trees is a feature of the Financial Report Designer, and allows you to model organisational hierarchies based on the structure of your business.  You can then filter your layouts by the different units in your trees to view your data in ways that make sense to you.
Sage Intelligence makes use of wildcards to easily define your Reporting Tree units.  For example, a question mark can be used as a placeholder for a single character in an account segment, like ????-100-10, and can also be used in conjunction with another wildcard character.

This functionality has been enhanced over different releases of Sage Intelligence, and to help you understand what applies to you, we’ve created the Reporting Tree Wildcard Matrix.
You can get a copy of it here.
Once open, select the Core Version that applies to you and the associated Wildcard details will be displayed.  If you don’t know what Core Version you’re on then don’t worry as the steps to finding out are also provided in the workbook.

The post Making use of Wildcards in your Reporting Trees appeared first on Sage Intelligence.

Powered by WPeMatico

How to fix wrapped data labels in a pie chart

When creating a pie chart and adding data labels to the slices, the wording can sometimes split into two lines—giving you wrapped data labels that aren’t visually appealing. In the below example, Administration has been split into two words, each on a separate line. The labels are wrapped and the results appear incorrectly.

Applies To: Microsoft® Excel® for Windows 2013 and 2016.
You are welcome to download the workbook to practice.
This issue is easily resolved, by simply turning off word-wrapping:
1. Right click on the data label and select Format Data Labels

2. Select Text Options > Text Box > and un-select Wrap text in shape.

3. The data labels resize to fit all the text on one line.

4. Alternatively, by double-clicking a data label, the handles can be used to resize the label to wrap words as desired.

This can be done on all data labels or on an individual slice data label. It also ensures that the labels are correctly displayed on the chart.
The post How to fix wrapped data labels in a pie chart appeared first on Sage Intelligence.

Powered by WPeMatico

How to view statistics on the status bar in Excel

This tip helps you discover how to quickly display statistics on the status bar by simply highlighting data.
You are welcome to download the workbook to practice.
Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016.
Excel allows for up to 6 statistic options to be viewed on the status bar, namely; Average, Count, Numeric Count, Minimum, Maximum, and Sum.
In order to view the stats, you will need to right click on the status bar and click to enable the stats option you would like to see on the status bar.

Once you highlight the data, the status bar will show the stats.

This allows you to easily view stats without having to write a formula.
The post How to view statistics on the status bar in Excel appeared first on Sage Intelligence.

Powered by WPeMatico

Run multiple versions of the same report, with different parameters, all at once

There may be times when you want to run out the same report but with different parameters.  For example, you may have a financial report that you want to run individually for three separate fiscal years.  Typically, you would need to run out the report three times, once for each year.
Did you know that by making use of a batch file, you can automate this process?  Furthermore, if you ever want to set up a scheduled task for your reports, you only have to set up one.
I’m going to show you how to do this using the example of a sales report that contains the information for three of my sales reps.  I want to be able to run the report out on a monthly basis and share it with each of them.  I also don’t want the data for them to be combined.  Through the use of a batch file I’ll be able to execute the running of the report three times, once for each sales rep, through a single action, with the data filtered for each one.
1. In the Connector, I’m going the add a Pass Through Variable that I’ll use to store my sales rep name entered as a Parameter when the report is run. This isn’t necessary but will make identifying which sales rep a report is run for in step 4 easier.

2. Then in the Report Manager, I’m going to add the Parameter (making use of the Pass Through Variable) to capture the sale rep name.

3. I’m also going to add a filter, assigning my SalesPersonName field to my Pass Through Variable. This will filter the data in the report by the given sales rep at run time.

4. The next step is to set an Output File for the report through the advanced properties. This will save a copy of it to a Reports folder stored on my desktop when run.  I’ve also included the ‘Date’ system variable and my sales rep Pass Through Variable in the name.  This will allow me to identify the date the report is run on and the sales rep it was run for when saved to the folder.

Take note that if you don’t have access to the Connector module then you won’t be able to make use of a Pass Through Variable.  You could then just use a Parameter to filter the required field in your report and make use of the ‘Time’ system variable in your Output File name to distinguish between the different report runs.
5. Following that I’m going to generate a Scheduler Command for the report by right clicking on it and selecting Generate Scheduler Command. This creates an executable command for the report that can be used by the Windows operating system.

6. I’m then asked to enter a default value for my Parameter that will be used by the Scheduler Command when it is executed. I’m going to enter one of my sales rep’s names.  The particular one doesn’t matter.

7. After clicking okay the Scheduler Command is created and saved to my Windows clipboard. I can then click OK again.

8. Following that I can create my batch file. Using the Windows Notepad application, after opening it, I can press Ctrl+V on my keyboard.  This will place the Scheduler Command into it.  I’m also going to copy it again in Notepad and paste it another two times.

9. You’ll see there’s a section which includes the Parameter for the report run and holds the value that will be used for it. At the moment this is ‘Dave’.  I’m going to go ahead and edit the name in the second and third commands to hold the name of my other two reps.  These are ‘Fred’ and ‘Tom’.

10. The last thing to do is save the file, which I’m going to do to my Reports folder. It’s important that you give it the extension ‘.bat’.  There are other commands you can add to a batch file to make it more interactive or provide feedback as to when each command finishes executing.  A quick google search can provide information on this but I’m not going to worry about this now.

Note that the file can be saved to any convenient location, it doesn’t have to be saved to the same folder that your reports will be saved to.  I have just done this for convenience.
11. Now, if I open up my Reports folder I can see the batch file there and if I double click it, it will execute. A windows command window will appear detailing the execution of each command.  And once each one completes, the produced report is saved to the folder, with the date and respective sales rep included in the name.

Normally, if you wanted to set up a scheduled task to complete this, you would need to create three separate tasks.  Now, all you need to do is create a single task that executes the batch file.  If you would like more information on creating scheduled tasks then take a look at the video here.
The post Run multiple versions of the same report, with different parameters, all at once appeared first on Sage Intelligence.

Powered by WPeMatico

How to print and display formulas in an Excel workbook

Instead of displaying values, you can actually display formulas as a way of reviewing the spreadsheet. In this tip, we explain how that can be done.
You are welcome to download the workbook to practice.
Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016.
To show all formulas in a worksheet, hold down the Ctrl key and press the ~ (tilde) key on the keyboard. You can also easily print the worksheet by holding down the Ctrl key and pressing the “P” key on the keyboard, this will take you to the print dialog box.
To switch back to showing no formulas in a worksheet, hold down the Ctrl key and press ~ on the keyboard again.
To show formulas in more than one worksheet in a workbook, first select the worksheets by holding down the Ctrl key, then hold down the Ctrl key and press ~ on the keyboard.
Another way to show formulas, without using a keyboard shortcut is by selecting “Show Formulas” from the Formulas ribbon tab.
An example of a worksheet without showing formulas:

An example of a worksheet which shows formulas:

This allows you to easily view formulas at a glance. The formulas can also be printed and verified.
The post How to print and display formulas in an Excel workbook appeared first on Sage Intelligence.

Powered by WPeMatico

How to generate a barcode in Excel

This tip shows you how to generate a barcode in Excel that will enable you to track stock, sales, purchases or fixed assets.
Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016.
Excel has no built-in functionality to generate a barcode.  However, this is easily achievable by simply downloading and installing a font.

This tip will enable you to generate a barcode in Excel by using 39 barcodes.  Code 39, or Code 3 of 9 as it is sometimes referred to, is the most used barcode and is scannable by every barcode scanner.  It can encode 26 upper-case letters, 10 digits and 7 special characters.
To generate a barcode, follow these steps:
Download and install the free barcode font from idautomation. This is a fully-functional, freeware font.  Please refer to the free product section of their license agreement for additional information.
Once the font has been installed, select the cell in which you want to generate the barcode.
Enter the value for the barcode.
Change the font from your standard font style to “IDAutomationHC39M Free Version”.

Once the font has been changed, the text will be converted into a barcode.
This is an easy way to not only generate a barcode, but also one that can be printed and scanned.
The post How to generate a barcode in Excel appeared first on Sage Intelligence.

Powered by WPeMatico

Join Sally as she helps you forge ahead, making use of the Sage Intelligence Connector module

Sage Intelligence gives you a lot of value out-the-box, through the ready-to-use reports provided in the Report Manager and the free reports that you can access via the Report Utility.  If these aren’t exactly what you’re looking for then you also have the ability to tweak them to suit your needs or build your own using the standard data containers available in the product.  Sometimes this still isn’t enough and you may want to define the specific data sets you require.
Sage Intelligence allows you to do this through its Connector module, which puts you in direct contact with your Sage database.  If you would like to get to grips with the Connector, then why not join Sally in her latest video series—as she works through an example of a Receivables and Payables dashboard that she created for her business. You’ll learn the necessary skills to put together just about any set of data you desire.
You can sign up for the series by following the link below.  It consists of five fifteen-minute lessons and by registering, you’ll be provided with a new one each week in your inbox.
Join the Getting ahead with Sally guest list now!

The post Join Sally as she helps you forge ahead, making use of the Sage Intelligence Connector module appeared first on Sage Intelligence.

Powered by WPeMatico