Solved

Sum formula for one record used accross many group summaries

Posted on 2013-06-19
9
347 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 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