I have a report that has a simple table. I need 5 tabs in an excel export. Each tab will have the same table just filtered on one field in the dataset. Do I have to create 5 copies of the table and place page breaks etc to populate each tab, or is there a way of producing the 5 tabs using one table? If changes need to be made to the report, I would rather avoid making changes to 5 tables.
You can produce 5 tabs with one table if you have 5 unique values in your field. Create a new row grouping in your table on the field you want the filter (keep table structure the same), go to Group Properties for the group and there is a page break option for you to include a page break "between each instance of a group". This will break your pages up into individual sheets per group when you export it. However, if you have more than 5 different values in that field then you will have one sheet per unique value.
The other option you mentioned as well is to have one table per filter and manually put your filter in those tables. Then add page breaks before/after those tables.
The method you use can be based on your business requirements. If the users wish to see one tab per unique field value, then go with the first option. If the users wish to see only 5 tabs, first 4 tabs with top 4 occurrences and 5th tab is "all other values", then go with the second option.