Solved

Sum formula for one record used accross many group summaries

Posted on 2013-06-19
9
343 Views
Last Modified: 2013-06-20
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
0
Comment
Question by:IO_Dork
  • 5
  • 4
9 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39260931
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
 

Author Comment

by:IO_Dork
ID: 39262876
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39262986
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
 

Author Comment

by:IO_Dork
ID: 39263019
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:IO_Dork
ID: 39263038
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39263063
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
 

Author Comment

by:IO_Dork
ID: 39263207
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39263231
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
 

Author Comment

by:IO_Dork
ID: 39263295
ok.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now