Create and Modify Reports Using Excel - Step-by-Step Guide for Data Analysis
Table of Content:
Create and modify reports by using Excel
Excel can be used for viewing and analyzing transactional data. Finance and Operations uses an out-of-the-box application called the Excel Data Connector to view, update, and edit data within an Excel workbook. You will need to download the Microsoft Dynamics Add-in to use this feature.
On certain pages, you can select the Open in Office icon on the Action Pane. From there, you can select Open in Excel or Export to Excel.
Open in Excel lets you use the data entity that is related to the form data. The columns that appear on your Excel workbook are defined in the AutoReport field group on the entity. You can also add or remove fields from the entity by using the Design button. Additionally, you can filter the data by using the Filter button. The Open in Excel feature also gives you the ability to change the data in the grid and publish the changes back to Finance and Operations apps.
The Export to Excel feature exports all the grid data to an Excel workbook. When the data is in a workbook, you can use the tools in Excel to manipulate the data and create visual charts for your reporting needs.
Optimize data source queries
Some reports in Finance and Operations apps can take a long time to run due to large queries on reports that are processing data. This unit discusses ways to optimize these queries for SSRS and Power BI reports.
To optimize your queries for SSRS reports, make sure that you are only pulling the data that you need. Unnecessary joins or additional fields that you do not need from the query will increase the time that it takes for a report to run. You should only pull data that the report will need to run.
In Power BI, large queries can also cause slow performance. To increase performance, try removing unused fields. You might want to consider avoiding unique fields with high cardinality where possible. In addition, avoid excessive slicers on data, which can also cause slow performance.
Ensure correct use of display fields
You can use grouping to group specific types of data together and expressions to format the data. With SSRS, you can also use labels to automatically translate text to multiple languages, and properties to allow you to change the font and margins of text and fields.
Having all the data simply appear on the report can lead to a cluttered display. Groupings can help you organize the data into a meaningful layout. For example, you can create a header and footer grouping that appears on a page. For transaction data, you could create a grouping with a table layout to include columns and rows to display the data. Grouping data allows you to share expressions for a data set. Groupings can help you create lists, charts, and tables.
Expressions are functions that can be used on fields to retrieve, calculate, display, group, sort, filter, parameterize, and format data on the report appearance by using Microsoft Visual Basic. You can use expressions on a field or group by right-clicking the field or group and selecting Expressions. All expressions will begin with an equal sign. You can use expressions to call data from a data set, run math functions such as rounding, data type functions to manipulate strings and dates, and perform aggregate functions such as sum. Power BI also has a formula bar for columns that can be used to perform calculations.
Any text that you use on an SSRS report should be used through labels. Labels allow the text to be automatically translated if the report is run for a country or region with a different language. This gives organizations with global locations or partners the ability to create one report that can be used globally instead of having to create an individual report for each location with a different language.
Additional properties are available for SSRS reports for fields. These properties can control the font and style of text to make it more readable. You can also control the margin sizes to determine how much space should be between each data field and group.
Excel Data Connector add-in
Microsoft Excel can change and quickly analyze data. The Excel Data Connector app interacts with Excel workbooks and OData services that are created for publicly exposed data entities. The Excel Data Connector add-in enables Excel to become a seamless part of the user experience. The Excel Data Connector add-in is built by using the Office Web add-ins framework. The add-in runs in a task pane. Office Web Add-ins are web applications that run inside an embedded Internet Explorer browser window.
