V4.2 on Suite 7.9
Report: Lead Source Analysis
Aim: Count total leads, count open leads, count dead leads, count converted leads group by Lead Source
Report Set up
Leads module with Lead Source and Status field x 4
Main report has Status set to Count and group by lead source. No select statement
3 x unions - all to Leads module
Union 1 join to Status 2 in main report. Select statement where Status is one of New, In Process
Union 2 join to Status 3 in main report. Select statement where Status = Dead
Union 3 join to Status 4 in main report. Select statement where Status = Converted.
Expected Result: The counts only count their respective Statuses
Actual Result: all Counts are identical
Not sure the significance of the one row that is different
Simon,
why don’t you group by lead source and by status?
Then check show sums and show totals in presentation pivot table?
Manipulate
Pivot
As for your 2 blank rows: There might be some null and empty values in field lead_source.
Val
I understand however the report will eventually contain additional dimensions and a pivot wont allow me to achieve this. For example the end goal is to have the following columns
Lead Source
Qty Leads (count all leads)
Qty Open (count leads where status is New or In Process)
Qty Converted (count leads where status is Converted)
Qty Dead (count leads where status is Dead)
Conversion Rate (divide Converted leads by count of all leads)
Value of Leads in Pipeline (sum of all active opportunities)
Value of business closed won (sum of all closed won opportunities)
Value of business lost (sum of all closed lost opportunities)
Average value per lead (divide closed won value by count of all leads)
Val - do you think this kind of report is possible?
Simon,
do you mean like this
Val
This looks correct. Nice work 
Can you send over the structure to recreate and we’ll test here.
Here is my “manipulate” tab fo rmain module (Leads)
I dragged lead source field and grouped by
I dragged ID field 3 times applying a count distinct. Setting 0 here for 2 of them. Moving the 0 in union modules. You already have experience with this, I guess you understand.
To get the sum for Won opportunities I draggedf field opportunity > amount and I use a customSQL on it:
SUM(IF({t}.sales_stage=‘Closed Won’, {t}.{f},0))
{t} stands for table of the field I dragged, {f} stands for the field I dragged. sales_stage is the name of another field in the table.
Calculation sare fixed fields conatning a formula.
In union manipulates I only mapped lead source and ID to count
Thanks Val - That works nicely. the only anomilie is when I add a filter by Lead Assigned To. The “ID Total” becomes 0. e.g. ID Total Leads = 0 ID Total Open = 10, ID Total Converted 4. In this example I would expect ID Total Leads = 14.
How did you enter lead assigned to?
In main module you get the assigned user field (user_name in my example)
Set a reference name to be used in unioned modules
Then in Union modules drag the assigned user field, select operator reference and enter “username” (the name you set in reference in step above.
Val
