Sum formula for one record used accross many group summaries

A bit complicated so here goes...the records i am trying to pull and summarize represent trades.  Each trade is represented by one salesman (most of the time)  who earns the commission on that trade.  However, the trade platform where we enter the trade information in allows for a commission override.  That is, the trade commission amount can be shared by two salesman if needed, instead of jus the primary salesman on the account.

I am trying to create a commissions report using Crystal Reports XI.  My initial setup was to use a cross-tab to quickly sum up the commissions earned for each salesman.  This works well, except since its configured to group/summarize the rows by primary salesman, it does not factor in to the any trades that each broker was a secondary salesman.  I am hitting a wall here on how to group the cross-tab by salesman and have it total both primary and/or secondary trade commissions for each salesman.  How to have one record be displayed on more than one line so i can show that Jack's commission total represents his share of trade 1 and Jill's total also reflects her share of trade 1....stored proceedure needed? or can i accomplish this using formulas?

Let me know if you need more clearity in the matter.

see attached for sample of data
trade-commission-sample.xlsx
IO_DorkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mlmccConnect With a Mentor Commented:
I think you need to do something like

SELECT TradeID, Salesman, Commission, Salesman1, Commission1 FROM YourTable

UNION

SELECT TradeID,  Salesman2, Commission2,, " ", 0  FROM YourTable Where Not(Salesman2 Is Null)

You can then use a formula for the salesman like
If IsNull({Salesman1}) OR {Salesman1} = " " then
    {Salesman}
Else
    {Salesman1}

For the commission
If IsNull({Salesman1}) OR {Salesman1} = " " then
    {Commission}
Else
    {Commission1}

mlmcc
0
 
mlmccCommented:
Is the data in the table as shown in the spreadsheet?

Will salesman 1 always be the primary salesman?

What database is being run against?
Can you use a command as the datasource or build a view in the database?

mlmcc
0
 
IO_DorkAuthor Commented:
Q1: the spreadsheet is a made up sample, but its identical to the kinds of field in my database, just with different field names for carity.

Q2: essentially yes...Salesman 1 will always be primary salesman.  There is one field for the total billing/commission called Commission.  Once the trade is shared with another salesman the override fields take over and the main commission field is ignored - so for salesman1 we would normally look to the main commission field, but once there is a secondary salesman, we look at salesman override 1 for salesman1's commission.

Q3: its being run against a microsoft CRM (4.0) database with microsoft sql server 2008.  so i can build a view...i just have to learn how to create one and implement one.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
IO_DorkAuthor Commented:
just to clearify, all the commissions and override field are contained in the same table/entity called Trade - this contains all the trade information.

we also have a entity for accounts.


Not sure if this changes anything
0
 
IO_DorkAuthor Commented:
so my current Crystal SQL statements is as follows:

 SELECT "FilteredNew_trade"."new_primarysalesmanidname", "FilteredNew_trade"."new_commbasis", "FilteredNew_trade"."new_settleon", "FilteredNew_trade"."new_tradeid", "FilteredNew_trade"."new_secondarysalesmanidname", "FilteredNew_trade"."new_overrideinvs2comm_base", "FilteredNew_trade"."new_overrideinvs1comm_base", "FilteredNew_trade"."new_tradenumber"
 FROM   "crm_MSCRM"."dbo"."FilteredNew_trade" "FilteredNew_trade"
 WHERE  ("FilteredNew_trade"."new_settleon">={ts '2012-01-28 00:00:00'} AND "FilteredNew_trade"."new_settleon"<{ts '2012-02-11 00:00:00'})
 ORDER BY "FilteredNew_trade"."new_primarysalesmanidname", "FilteredNew_trade"."new_tradeid"

so your suggesting to incorporate the statement you posted above into my current CR sql statement or to replace it all together?
0
 
mlmccCommented:
You need to create records for the SECONDARY Salesperson.

A record can only appear in one summhttp://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_28162130.html#ary in the crosstab.

You should be able to incorporate it

 SELECT "FilteredNew_trade"."new_primarysalesmanidname", "FilteredNew_trade"."new_commbasis", "FilteredNew_trade"."new_settleon", "FilteredNew_trade"."new_tradeid", "FilteredNew_trade"."new_overrideinvs1comm_base", "FilteredNew_trade"."new_tradenumber"
 FROM   "crm_MSCRM"."dbo"."FilteredNew_trade" "FilteredNew_trade"
 WHERE  ("FilteredNew_trade"."new_settleon">={ts '2012-01-28 00:00:00'} AND "FilteredNew_trade"."new_settleon"<{ts '2012-02-11 00:00:00'})
 UNION ALL
SELECT  "FilteredNew_trade"."new_secondarysalesmanidname", "FilteredNew_trade"."new_overrideinvs2comm_base", NULL, "FilteredNew_trade"."new_tradeid", NULL, "FilteredNew_trade"."new_tradenumber"
WHERE  ("FilteredNew_trade"."new_settleon">={ts '2012-01-28 00:00:00'} AND "FilteredNew_trade"."new_settleon"<{ts '2012-02-11 00:00:00'}) AND 
NOT("FilteredNew_trade"."new_secondarysalesmanidname" IS NULL)
ORDER BY "FilteredNew_trade"."new_primarysalesmanidname", "FilteredNew_trade"."new_tradeid"

Open in new window


mlmcc
0
 
IO_DorkAuthor Commented:
exactly, when summing commission by salesman, the system wont let me utilize the record twice...on two different sum groups....tradeid 1, comm override 1 for Jack's summary, and also trade 1, comm override 2 for Jill's summary.

 So, this may seem like an obvious question, but where in CR can I paste in (or at least edit) the text of the sql statement.  The ony way I know how to create connections is using the database expert which only allows for only drag and drop between tables.
0
 
mlmccCommented:
You can't do through the tables.  You will have to base the report on a command in Crystal or a view/stored procedure in the database.

mlmcc
0
 
IO_DorkAuthor Commented:
ok.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.