We have an existing homegrown app (based around PHP and MySQL) that we are extending. As the work progress our need for reporting changes, and we have started working with Power BI to help figure out exactly what we need. So far so good, until now.
I need to show the Sales Reps Targets versus their Actual amounts for their Estimates and Invoices.
In our app's database are stored the target amounts each Sales Rep has to hit for their Estimates and their Invoices. The format is as follows (not my design choice, just what I have to work with):
Id | TargetType | UserId | TargetData
1 | INVOICE | 2 | a:12:{i:1;s:3:"300";i:2;s:1:"0";i:3;s:4:"3900";i:4;s:4:"6400";i:5;s:4:"9300";i:6;s:5:"12900";i:7;s:5:"13500";i:8;s:5:"12900";i:9;s:5:"11600";i:10;s:5:"12900";i:11;s:5:"10300";i:12;s:4:"6100";}
2 | ESTIMATE | 56 | a:12:{i:1;s:4:"1800";i:2;s:1:"0";i:3;s:5:"23400";i:4;s:5:"38400";i:5;s:5:"55800";i:6;s:5:"77400";i:7;s:4:"8100";i:8;s:5:"77400";i:9;s:5:"69600";i:10;s:5:"77400";i:11;s:5:"61800";i:12;s:5:"36600";}
3 | INVOICE | 56 | a:12:{i:1;s:3:"600";i:2;s:1:"0";i:3;s:4:"7800";i:4;s:5:"12800";i:5;s:5:"18600";i:6;s:5:"25800";i:7;s:5:"27000";i:8;s:5:"25800";i:9;s:5:"23200";i:10;s:5:"25800";i:11;s:5:"20600";i:12;s:5:"12200";}Where:
"Id" is the record id of that table,
"TargetType" is INVOICE or ESTIMATE, the type of target the information is about
"UserId" is the record id of the user this will apply to
"TargetData" is my nemesis and is some sort of array
I believe I have figured out to manually parse TargetData, but can not figure out how to get Power BI to do it, or if that's even possible.
"a" is the array name
"12" indicates there are 12 items (one for each month)
Then starting after the opening curly brace "{" the inner array of each month's target values.
"i" is the index value, representing the month number
"s" I am not sure what the number following it represents, but it seems to relate to the number of digits that follow
target_value in double quotes, such as "6400"
I've tried sourcing this as JSON data, but that doesn't seem quite right, or I'm going about it the wrong way.
Once this is done, I'll have to correlate these target numbers with the actual numbers the sales reps have generated, which looks to be another challenge due to the different granularities.
But first: Any ideas with this? What am I missing?
Thanks in advance,
Dion
