With many clients actively building reports within our New BI solution, we thought it may be helpful to pass along some tips and tricks around report building, using the design screen and where to find key data within the cube.
1. Reporting on Locations
We are often asked, “Which view do I use for reporting on my locations?” The main view you will want to use for reporting on your stores is found within locations and employees (called “Locations and Employees”).
Expand this view to find the different levels you may want to bring into view, such as Store, District or Region.
2. Reporting on Employees
Another common question: “When I bring in the employee view from Locations and Employees, it produces multiple lines for each employee. Why does it to this? Which view will give me a single aggregate for John’s sales?”
Within Locations and Employees, the first option in the list is called “Locations and Employees.EmployeeName”. This view will aggregate John’s invoices into one line. The reason the employee view within Locations and Employees reports John’s sales onto more than one line: It associates his sales with his invoicing location.
3. Reporting on In Stock Cost and Quantity
Wondering how to report on Total Cost or Total Quantity of In Stock as of a particular point in time?
The Inventory Cube contains measures which will allow you to view the cost on hand associated with your Locations or Product categories.
All that is required is that you include the Date.Date.YMD Filter / parameter and run the report as a particular date in time. In order to determine the cost as of a particular day, you need to tell BI as of what day you would like. For Instock reporting, also check out the BI Standard report on Instock available within our Standard Reports folder.
4. Exporting to One Excel Workbook?
Another example question: “I have two main reports: one that goes to the store managers and one to the employees. It would be ideal to be able to send the report out to the company in one Excel file. This way I don’t have to have my staff receiving two different emails containing virtually the same information. Is this possible?”
This is not possible. If you first combine these two reports into a single report, you can turn each report page into an Excel worksheet, but it is not possible to combine two reports into a single email Excel worksheet.
5. Exception Highlighting
Exception highlights are the same as conditional formatting in Excel. They can be set on the font or on the cell itself.
The below example shows how you can write a simple expression to change a target value to turn a cell red “if Less than 1” or lime green “if 100% is reached Greater than 1.” To set this:
- Click on the cell you would like to configure.
- Hit Escape (Escape gives you access to the Text Box Properties).
- Right Click. Choose Text Box Properties.
- From within this menu, choose the Fill option.
- Next to the Fill Color option, choose the Function (fx)
- Here, you can use an IF Statement, like we have below, to layer in the value you need and the associated colors.
6. Interactive Sorting
Interactive Sorting allows the end user of a report to dynamically sort a grid. When applied, arrows will appear on a given cell, allowing the report viewer to sort a grid for the cell where the sorting is applied. This works similarly to highlighting all rows and columns in Excel and applying the “Sort” command.
To set, right click on the column header of a Tablix. This is a right click not a click. This will enable a menu of options. In this menu, one option is “Text Box Properties.” Interactive Sorting is the second last option in the left hand menu. To enable:
- Click off Enable Interactive Sorting.
- Choose the column for which you want to enable this feature from the “SORT BY” drop down menu.
- Choose OK.
- Done. It’s that easy!
For questions on details covered here or for any other BI related inquires, please contact the BI Team through the iQmetrix Support & Community site.