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

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

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

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