Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sum formula for one record used accross many group summaries

Posted on 2013-06-19
9
Medium Priority
?
350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 101

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 101

Accepted Solution

by:
mlmcc earned 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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 101

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 101

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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 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