Home » sageintelligence

Tag: sageintelligence

How to stop your Excel charts from disappearing

Have you ever experienced your Excel charts disappearing when you hide the columns showing the data? By default, Microsoft® Excel® shows only visible data in a chart. In this tip, we will show you how to easily overcome this problem, by following these simple steps:

1. First, lets create a simple chart with your data to demonstrate.
 

 
2. If you had to hide columns A and B, your chart will disappear. To prevent this from happening, click anywhere on the chart and from the ribbon, select Chart Tools > Design > Select Data
 

 
3. In the “Select Data Source” dialogue box, select “Hidden and Empty Cells” in the bottom left hand corner.
 

 
4. In the “Hidden and Empty Cell Settings” dialog box, check the box “Show data in hidden rows and columns”.
 

 
5. Select “OK” twice.
 
Now, go ahead and hide your data. Your chart should still display with the hidden data!

The post How to stop your Excel charts from disappearing appeared first on Sage Intelligence.

Source: Excel on Steroids

Part 1: Take a quick tour through Sage 50cloud Intelligence

 

Welcome to the first of a three-part series where you’ll discover how to get started with Sage Intelligence, so that you can easily manage your business financials. In this series, you’ll learn how to get a handle on your business’s financial reporting needs with quick and easy Sage Intelligence reporting tips.

Let’s get started

In part one, you’ll discover how to get started with Sage Intelligence. Are you using Sage 50cloud? Did you know that Sage Intelligence is available through your Office 365 license at no additional cost? This is how to get started:

First up, let’s make sure that you’ve activated Office 365 and connected your Sage 50cloud data to Sage Intelligence. Watch this short video to learn how if you haven’t done so yet, or you can check it out to make sure that you’ve done it correctly.

Ready to go? Here’s how to access Sage Intelligence:

1. Log into your Microsoft 365 account.

Once logged in, click on the top left-hand corner to access all apps (alternatively you can click “Explore all your apps” found at the bottom of the apps section).

Office_365_Apps

2. Scroll to the bottom of the list and choose “All apps” This should show all your available apps.
 

Office_365
 

3. Scroll to the bottom and click on “Sage Intelligence”
 

Office_365_SageIntelligence
 
4. A new page will load (depending on your browser, a new page OR new tab will open). Sage intelligence will now open! Your data is already linked to your Sage 50cloud data, so you are all ready to start reporting.
 

Explore:

Now that you’ve found Sage Intelligence, let’s take a quick tour.

The Options Button

At the top right-hand corner is the options button. Hover over it to see the available options. (We will take a deeper look into the options button later in the series).

SageIntelligence-Options-Button

The Help Button

Above the options button is the help button (the question mark icon). This gives you access to help files, resources and more.

1. Let’s look at the Balance Sheet Actual 1-12 (This report is the first tile on the top left).

A balance sheet provides a summary of your assets, equity and liabilities at a given point in time. It helps to show that what you own equals what has been invested or borrowed, for example; Assets = Equity + Liabilities. It’s basically a snapshot of the state of a company’s financials at a particular point in time. If your balance sheet isn’t balancing and has rather become just another sheet of numbers, then you know that there’s an issue you need to take care of and see to.

2. Hover over the ellipses button on this report(…) you will see options to Run (to run the report), Copy (to make a copy and customize your report) and Export (to export and use in another system) your report.

SageIntelligence_Ellipses
 

3. Let’s run the report by clicking the Run Report Button:
SageIntelligence-Run-Report
 

4. A window will open prompting you to enter parameters. Choose your parameters as shown below; in this case, we will choose 2019 for the “Year” and Sage – Bellwether Garden Supply in the “Company” box. Then click Run

SageIntelligence-Run-Button

5. Once your report has run out, it should look like this:

SageIntelligence-Balance-Sheet-Act
 

You can drill down into the numbers by clicking on them. These reports can be downloaded to your machine to further customize if need be; like adding charts/graphs, or forecasting.

6. Click on the Excel button at the top right-hand corner. And depending on your browser, you will be prompted to download, run or save the file.

SageIntelligence-DL-Excel

SageIntelligence-DL-Excel2
 
The Sage 50cloud Intelligence Excel add-in can be found on the right-hand side and enables you to log in with your Office 365 credentials and stay connected to the cloud while working in Excel on your desktop. This means that the changes you make to your data will automatically save so that when you run the report again, you won’t have to make the same edits.

SageIntelligence-Excel-Add-In

Go ahead and try this with your other reports and choose different parameters (years, companies and periods) to see the related figures.

Excel Online

You can also use Excel online by clicking on the Excel Online button instead. This will load Microsoft® Excel® online in another tab for you to work on your Sage 50cloud data.

Once you are in Excel Online, you should see the report in a spreadsheet complete with the different Excel sheets. In the previous example, you will see the same sheets: Drilldown, Missing Accounts and Transaction Details.

In conclusion:

It’s important to have accurate and timely financial statements available to understand and run your business effectively. Without these financial statements, you’ll be blind to the financial health of your business, and long-term sustainability would become extremely difficult.
 

Look out for Part 2, where we’ll dive a little deeper into how to customize your reports in the cloud.

 

Sage 50cloud Intelligence resources:

We’ve created a few guides and resources that will assist you in getting a handle on your business’s financial reporting needs. Learn more >

The post Part 1: Take a quick tour through Sage 50cloud Intelligence appeared first on Sage Intelligence.

Source: Sage Intelligence

Part 1: Take a quick tour through Sage 50 Intelligence Reporting

 

Welcome to the first of a three-part series where you’ll discover how to get started with Sage 50 Intelligence Reporting (on-premise/desktop), so that you can easily manage your business financials. In this series, you’ll learn how to get a handle on your business’s financial reporting needs with quick and easy Intelligence Reporting tips.

Let’s get started

Are you on Sage 50 Business Care? Did you know Intelligence Reporting is included as a benefit in your plan? In part one of this series, you’ll discover how to get started with Sage Intelligence Reporting with a quick introduction to three essential financial reports; the Balance Sheet, the Income Statement and the Trial Balance.

First, if you haven’t activated Sage Intelligence Reporting (on-premise/desktop), follow the two-step process explained in this short video to learn how. Watch Activation Video

Now that you’ve activated Intelligence Reporting, let’s open the Report Manager

  • Click on the Reports and Forms tab.
  • Scroll down to Intelligence Reporting Setup.
  • Then click Report Manager

In the Sage Intelligence Report Manager, you’ll notice how all the reports are organized in folders for easier access. The folders contain standard reports which can be copied and pasted as well as custom reports, which can also then be deleted. To see the array of reports available, double-click on a folder to expand it.

Check out the free additional reports we have available to suit your business needs. Once selected and downloaded, your new reports will be automatically imported into a “New Reports” folder in the Sage Intelligence Report Manager. Find out more >

Report-Manager

Create your financial reports using the Financial Report Designer.

The Financial Report Designer enables you to:

  • Run multiple reports at once (balance sheets, trial balance reports, income statements, etc).
  • Save your reports as templates.
  • Save time by only having to customize your report once and then gaining access to the latest data in the format you want.
  • Effortlessly create Excel-based reports that are easy to tailor based on your reporting needs.
  • Schedule your reports to run automatically

1. In the Report Manager, double click on the Financials folder to expand it. This should show all the reports within the folder.

Financial-Report-Designer

2. Now select Financial Reports Designer 1-0. To run the report, click the green Run button on the menu at the top OR you can just right click on the report and choose “Run”



3. The “Enter Report Parameters” box will appear at the top right, requesting the report parameters (don’t worry if you have no budgets – just choose the desired years you want to report on).

Parameters

4. In this case, we’ll choose all three of the years specified (Intelligence Reporting gives you the flexibility of reporting on three fiscal years at once, instead of just two) and click OK

FiscalYears

5. The report will now run and the output will be placed into Microsoft® Excel®.

The first thing you’ll see is the Home Page (the Home tab). This page contains links to Sage Intelligence, Sage Intelligence tips, help documents, etc. Use this sheet when looking for help and support. On the right, there’s a Task Pane which is what we’ll use to seamlessly run our reports.

Task-Pane


6. To run a report, simply select it and drag it to any cell within the Excel worksheet (you can drag it to any cell. It doesn’t matter if the cell already has data in it as a new worksheet will be created for the financial statement).

Let’s start with the Balance sheet:

  • In the Task Pane, click on the Balance sheet and drag it to a cell.

Balance-Sheet

  • Notice how data begins to load and a balance sheet now appears in a new worksheet. You should receive a confirmation that the layout was created successfully.

Now try this with the Trial Balance report:

  • Drag the trial balance to any cell (even if it’s a cell within the balance sheet). A new layout is generated in a new worksheet with the trial balance information. Also notice how there are now two report tabs available at the bottom of your workbook that you can use to quickly switch from one report to the other.

BalanceSheet-TrialBalance-Sheets

Now that you have two reports, try generating the Income statement Act vs Bud (don’t worry if you have no budgets set up – just run it to get the feel of it).

To run the Income statement, Act vs Bud, do the following:

  • At the top left-hand corner of each report there is a fiscal year. Click in that cell.
  • Change it to one of the three years you selected when running the report.
  • Hit “Enter”.
  • Notice the financial figures adjust accordingly. Notice how you don’t have to go back into Sage Intelligence to run the report all over again with new parameters. So, with the click of a few buttons, you can view data across different years for different financial report types all from within the same workbook.

This template can be linked back into Sage Intelligence so that the next time you run a report, all the changes will already be saved. It’s important to have accurate and timely financial statements available to understand and run your business effectively. Without these financial statements, you’ll be blind to the financial health of your business, and long-term sustainability would become extremely difficult.

Look out for Part 2 of the series, where we’ll dive a little deeper into how to customize your reports.
Get your free additional reports

Have reports to create, but no time to create them? We’ve got you covered! With a suite of additional reports available for you to access at anytime from anywhere, you’ll have more time to run and manage your business and do what you love! Find out more >

The post Part 1: Take a quick tour through Sage 50 Intelligence Reporting appeared first on Sage Intelligence.

Source: Sage Intelligence

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