I'm trying to create a revenue reporting for multiple stores.
The source data is queried from a web-api for each store seperately and structured like:
| Date | Invoice# | Customer | PayMethod | Total | serviceCat1 | serviceCat2 | prodCat1 | prodCat2 |
| 01.01.2022 | 1 | A | cash | 1000 | 200 | | 800 | |
| 01.01.2022 | 2 | B | cc | 25 | | | | 25 |
| 02.01.2022 | 3 | C | paypall | 333.5 | | | 233.5 | 100 |
| 02.01.2022 | 4 | D | cash | 666 | | 666 | | |
How can I bring that into a form that I have the product/service categories in a "category" and the corresponding value in a "amount" column -> creating additional rows if multiple categories are used for one original entry like:
| Date | Invoice# | Customer | PayMethod | Category | Amount |
| 01.01.2022 | 1 | A | cash | serviceCat1 | 200 |
| 01.01.2022 | 1 | A | cash | prodCat1 | 800 |
| 01.01.2022 | 2 | B | cc | prodCat2 | 25 |
| 02.01.2022 | 3 | C | paypall | prodCat1 | 233.5 |
| 02.01.2022 | 3 | C | paypall | prodCat2 | 100 |
| 02.01.2022 | 4 | D | cash | serviceCat2 | 666 |
...so I can create a report that is filterable by "category".
Some shops have 20+ "categories", so filtering each column by !=NULL and then appending the 20+ resolving seperate tables isn't really an option given the >10 shops...
Any help/hints is highly apprechiated!
