Home » sageintelligence

Tag: sageintelligence

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

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

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

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

8 tips to use when working with reports in the Excel Report Designer for Sage Intelligence

Sage Intelligence Reporting Cloud provides convenience to you as it lets you work in whichever environment you’re most comfortable in. You can either choose to design reports in your browser through the Online Report Designer or in Excel—through the Excel Report Designer. When making use of the Excel Report Designer, there are a number of points to take note of that will help you get the most out of your reporting.  Below I provide eight of them.
Below I provide eight of them.

Applies to:
Sage One Intelligence Reporting
Sage 50c Intelligence Reporting
1. Work within the table when using the Excel Report Designer
When making changes to a layout involving Sage Intelligence functionality, make sure you work within the table area in the worksheet, as the Intelligence Reporting functionality is designed to work within an Excel table.

2. Turn Auto Refresh off before editing the Excel workbook
Turning off Auto Refresh before editing your reports will stop the table from refreshing when you are making changes on the worksheet.  To do this, click on the Refresh icon at the bottom of the Excel Report Designer Task Pane and then select “Switch auto refresh off”.

3. Inserting columns/rows into a table
When adding a new row to the layout, select a cell in the table area below where you want to insert the new row. Then right-click and from the context menu select Insert > Table Rows Above.  Or if adding a new column to the layout, select a cell in the table area to the right of where you want to insert the new column. Then right-click and from the context menu select Insert > Table Columns to the Left.

4. Deleting columns/rows
When deleting columns or rows make sure you use the Table Delete functions similar to the Insert functions above.  To remove a column or row, select a cell in the table area of the row or column you want to remove.  Then right-click and from the context menu select Delete and then either Table Columns or Table Rows.

5. Remove unneeded rows or columns from the table
The fewer cells you have in a layout the quicker it is for the table to refresh, so removing any unneeded ones will improve performance.
6. Absolute Referencing cells when using Sage Intelligence Formulas
When using Sage Intelligence formulas, it can be essential to use Absolute Referencing.  This allows you to move or copy formulas around in a layout and still ensure that they maintain their cell referencing.

7. Use grouping to show or hide account level detail
If your layout includes account level detail, you can provide both a summary and detail level view by using Excel’s Grouping function.

8. Adding Charts to the worksheet
When adding Charts to a layout that includes grouping, it’s advisable to place them above the table area or to the right of it above any grouped rows.  You may need to insert additional worksheet rows above your table area for this.  This will prevent the Charts from either moving out of view, or being stretched if placed to the right of the table area or below it when any grouped rows are expanded. If you would like more information on adding Charts then take a look here.

These tips will help you get the most out of the Excel Report Designer when using Sage Intelligence. Have you discovered any tips of your own that you’d like to share? Let us know in the comments below!
The post 8 tips to use when working with reports in the Excel Report Designer for Sage Intelligence appeared first on Sage Intelligence.

Powered by WPeMatico

Learn how to share your Reporting Trees

If you have used the Financial Report Designer to create reports, you may have taken advantage of Reporting Trees as a way of filtering them. If you then intend on sharing these reports with other counterparts and want them to be able to filter by the same tree structures, then, rather than having them re-create the Reporting Trees themselves, you can copy and send them along with the reports.  Let’s take a look how.

1. The first thing you need to do is identify where your Reporting Trees files (.tre) are located. They are usually kept in your MetaData Repository under the Report Trees folder.  A quick way to find your MetaData path is to open the Report Manager, select the Home object and look at the MetaData Repository Location field in the properties pane.

2. Once you’ve accessed the path and then the Reporting Trees folder in your File Explorer, you’ll see the Trees you’ve created, listed there.

3. You can then copy the ones you want, add them to an email, and send to the desired recipients. Be careful to only copy the files and not remove them from the folder.
4. Once a user has received the files, they can navigate to the Reporting Trees folder in their MetaData Repository and paste them there.
5. The Trees will now appear in the Task Pane the next time the user runs out the Financial Report Designer.
If you’d like to find out more on Reporting Trees and how they can add value to your reports, check out the following recorded webcast: The X-Factor report writer series – Part 4: Taking full advantage of Reporting Trees or learn more by going to our Learning Portal.
The post Learn how to share your Reporting Trees appeared first on Sage Intelligence.

Powered by WPeMatico

How to reverse the sign of an account in your Sage Intelligence Financial Report Designer

In the Sage Intelligence Financial Report Designer, account balances are returned in their “natural format” from your Sage accounting or business management solution.  Therefore, credit accounts (with a credit balance) are shown as negative values and debit accounts (with a debit balance) are shown as positives values.  However, in financial reporting, there are times when the natural format does not suit the format of your reports, so it is necessary to reverse the sign.
Applies to: The Sage Intelligence Financial Report Designer
As an example, in an Income Statement, Revenue is naturally a credit account, and so it will be seen as a negative amount after the Sage Intelligence formula is dragged in for the row and its arguments configured.  This will also impact any other formulas in the layout that depend on it, like Variance and Gross Profit.

It, therefore, makes sense to reverse the sign of the Sage Intelligence formulas in the row.  For this, all you have to do is the following:
Select the Sage Intelligence formula you want to change.
In the formula bar, enter a minus sign after the equals and before the GL portion of the function.
Press Enter to confirm the change.

The results will now be negated. In other words, the credit balances will be shown as positive values, making the layout easier to interpret and other formulas more natural to calculate.

The post How to reverse the sign of an account in your Sage Intelligence Financial Report Designer appeared first on Sage Intelligence.

Powered by WPeMatico

Learn different ways to add and edit Sage Intelligence formulas in the Financial Report Designer

Once you know how to create your own customized reports, using the Sage Intelligence Report Designer and Microsoft® Excel®, you have the power to analyze any aspect of your financials!  At the core of this is knowing how to add and edit Sage Intelligence formulas in a report.  In this tip, I’ll show you the various methods you have to do this, allowing you to work in the way that is most convenient to you.
Applies to: Sage Intelligence Reporting on-premise solutions.
Adding Formulas
There are two ways to add formulas to your Excel worksheet using the Report Designer.  You can either drag-and-drop the formula from the Task Pane onto your worksheet, or you can type the formula name directly into a cell (the same as you would an Excel formula).  These two points are explained further as follows:
Select the desired formula from the Task Pane. Drag and drop the formula onto your Microsoft® Excel® worksheet, to the cell you would like to see the result in.

OR
Type the formula name directly into the cell. As soon as you type =GL you will see a selection of Sage Intelligence formulas to choose from.
 
Tip: If you would like to see the full list of Sage Intelligence formulas that are available to you, take a look at the Formulas tab on the Task Pane.
Editing Formulas
Once you have added the formula you would like to use, you’ll need to update its parameters so that it returns the right data.  There are two ways to edit formulas, either using the Function Arguments (fx) window or by typing directly in the formula bar.  Take a look at the below two points for a further explanation.
The Function Arguments window can be accessed by clicking on the cell containing a formula and then clicking the fx button next to the formula bar.

Take note that if there is more than one formula in a cell, for example, two are added together, you can open the Function Arguments for a particular one by selecting it in the formula bar and then clicking the fx button.

The formula parameters provided in the Function Arguments window will be used to specify what data is retrieved by the formula. Each parameter works as a filter on the underlying financial data and they are applied in the order that they are displayed.

Formulas can also be edited manually if you are familiar with the format of a particular one. Select the cell which contains the formula and then click on the formula bar and make your changes.

Knowing how to conveniently edit Sage Intelligence formulas will help make report writing a breeze.  Also, becoming familiar with other functionality like absolute and relative referencing and Excel’s autofill feature will also add to this ability.
Want to learn more about Sage Intelligence and discover how you can make the most of your smart reporting solution? Why not check out our upcoming webcasts and explore various features that can help you become a reporting pro!
The post Learn different ways to add and edit Sage Intelligence formulas in the Financial Report Designer appeared first on Sage Intelligence.

Powered by WPeMatico

Create your own Operational Report in Sage Intelligence Reporting Cloud

Do you want to create your own operational report and analyze it according to your specific reporting needs from Sage One International? Sage Intelligence Reporting & Microsoft Excel make this easy and quick to do.
Applies to: Sage One International, Microsoft Excel 2013 & 2016
How do I do this?
1. Download one of the standard Intelligence Sales reports from the Sage One International website here. This will download an Excel workbook.
2. Once finished, open the workbook. You may be asked to enable it for editing.  Click Enable Editing.
3. If this is the first time you’re opening a Sage One Intelligence report, you’ll be asked to accept the add-in in the pane on the right of the workbook. Click Trust this add-in.

4. The Intelligence Reporting Task Pane will then load and you can sign in with your Sage One login.

5. Next, select the Company & Financial Years you would like to report on and the click Load.

6. Open a new sheet in the workbook (Shift +F11) and then click on Design My Own Reports in the Task Pane.

7. Make sure the Operational tab is selected and then pick the relevant options for the choices below:
The Operational Area you would like to work with, e.g. Sales by Customer.
The different fields you would like to see, e.g. Customer Name, Item Quantity, etc.
The date range you would like to report on, e.g. 1 January 2017 to 30 April 2017.
Add any filters you would like to include, e.g. Item Type equal to Cosmetics.

8. Once finished click Create New Table. This will place the information in a table format in the worksheet.

9. You can now use this to create your report layouts using standard Excel functionality like Pivot Tables and Pivot Charts. Below is an example of one showing the sales for my top ten customers.

The post Create your own Operational Report in Sage Intelligence Reporting Cloud appeared first on Sage Intelligence.

Powered by WPeMatico