Excel reports can be created using the Excel Report Wizard. However, these reports are not easily modified and usually have to be re-created when a change is needed.
A custom Pick List (or similar report) can be created that exports to Excel cleanly. This comes in handy when there are times you want an Excel report that is relatively easy to modify as needed (such as adding data fields). Without modification, reports export to Excel with messy formatting. The benefits of the process outlined in this article are twofold:
- Eliminates messy formatting when exporting to Excel
- Creates a custom report that can be modified quickly
To begin, navigate to the Standard Report Designer. This can be done via the main SI window by clicking the blue [Start] button and selecting Reports > Standard Report Designer.
Once the Report Designer is opened, click File > New.
Select “New Report Based on Existing Report”.
Select the Pick List Report and click [Next].
Give the report a name and click the [Finish] button.
Once the report is generated, unnecessary parts need to be hidden, which would be any elements in the PageHeader1, GrpDynamicHeader1, GrpDynamicHeader1, and PageFooter1. This can done by clicking and holding Ctrl key to multi-select the elements.
Once these elements are selected, go to Properties to change the “Visible” property located on the bottom-right corner of the screen.
In the GroupHeader1 section, delete the line located underneath the “Model” textbox. Select everything above the Model, Description, Part Number, and QTY textboxes. Select and drag the Model, Description, Part Number, and QTY textboxes in this section to the top of that section. The top of the textboxes should be touching the border.
Deleting the Line:
Selecting the textboxes:
Click on the Script tab at the bottom of the Window and delete the text within the section GroupHeader1_Format. Click on the Designer tab once you are finished.
Text to Delete:
Next, remove the extra spacing. Click on the notch on the left hand side of that section and bring it to the notch for the previous section. Do this for every section but the GrpModel and GroupHeader1 sections. For the PageFooter1 section, click and drag the line at the bottom of the section to section header. To get rid of the extra spacing in the GroupHeader1 section, drag the notch next to the GrpDynamicHeader1 section to meet the bottom of the textboxes in the GroupHeader1 section.
Now, line up the textboxes located within the GrpModel section. These textboxes should be the same height and there should be no spaces between them. Any spacing will disrupt the formatting of the Excel export. The same should be done for the corresponding box in the GroupHeader1 section. You can tell when the boxes are lined up via a blue line that will appear, which acts as a guide.
You can drag in any of the fields you want to appear on this report via the Data Explorer on the right side.
For this example, I will be inserting the “Wire Length”, “Location”, and “System”. For every field inserted in the GrpModel section, you will need to insert a textbox as the column header in the GroupHeader1 section. You may need to make more space for the new data, you can do this by clicking and dragging the right margin of the report.
Once you have the proper fields in your report, click File > Publish Report. Your report will be available via the Management button on the Report tab.
Run the report, and click [Export to Excel] in the Report Viewer.
The Report in Action:
The Grouping is very important for your report. It determines the quantity of your items based on the selected grouping. You will also need to change this if you are using Location or System in your report. The Grouping can be changed via the Report Definitions. Please click here for details.
If you see any empty cells in the report, this means there is some incorrect spacing between the data fields. Please make sure they are lined up back to back.
Custom report development and creation is available from the Professional Services Team, with a fee.