SSRS Sales Order Ack Line Total Summary

Yahoo Archive
1

Mr. Joe: Thanks for the suggestion - that might work - I'll give it a try.


DaveO

2

I am trying to recreate the Sales Order Ack line total for the overall order.


First - I know there is a field already that calculates the extended line amounts and sums up the lines.  However - I would like to know how to do this manually.  Let's say I want to control the number of decimals or manually add a discount or commission or tax.


So - I added a textbox to the Group1 (OrderNum) footer, and set the expression to "=Sum(Fields!DocUnitPrice.Value * Fields!SellingQuantitiy.Value,"Group1"). 


Group1 = OrderNum

Group2 = OrderLine


When I try to set the grouping value to "Group2" I get an error when I try to save.


The problem I am having is that the dataset has Sales Order Release Lines.  All my Sales Order Lines have two (2) release dates.  So the Sum function is double what it should be.


I have been pulling my hair out to try and solve this one. It can't be this complicated can it?  I am just trying to get a "line" total summed up by Sales Order.


If anyone can offer a suggestion I would greatly appreciate it.


Thank you,


DaveO

Ph: 651-246-3281

3

I came up with the following solution:


Create a new Calculated field MyLineExtTotal and add a formula IIF(Fields!OrderRelNum.Value=1, Fields!DocUnitPrice.Value * Fields!SellingQuantity.Value,0)


That formula will set the new field equal to the line total ONLY for Order Release Line 1 otherwise it will set the field to Zero.


Then I was able to use a standard SUM() function to get the correct total.


I feel like this is a bit of a band aide way of doing it and presumes that there will always be a release 1.  That may not always be true.


If someone out there has a more bullet proof solution - Please share.


Thanks,


DaveO

4
Dave,

I'm a newbie, but here's a shot.

Is there a way to use SELECT DISTINCT in your query?

It might involve adding a dataset or something, and just use it for your total.

Joe
--
Joe D. Trent
Bigham Ag Equipment

On Sun, Aug 9, 2015 at 3:58 PM, daveolender@... [vantage] <vantage@yahoogroups.com> wrote:

 
<div>
  
  
  <p></p><p>I came up with the following solution:</p><p><br></p><p>Create a new Calculated field MyLineExtTotal and add a formula IIF(Fields!OrderRelNum.Value=1, Fields!DocUnitPrice.Value * Fields!SellingQuantity.Value,0)</p><p><br></p><p>That formula will set the new field equal to the line total ONLY for Order Release Line 1 otherwise it will set the field to Zero.</p><p><br></p><p>Then I was able to use a standard SUM() function to get the correct total.</p><p><br></p><p>I feel like this is a bit of a band aide way of doing it and presumes that there will always be a release 1.  That may not always be true.</p><p><br></p><p>If someone out there has a more bullet proof solution - Please share.</p><p><br></p><p>Thanks,</p><p><br></p><p>DaveO<br></p><p></p>

</div><span class="ygrps-yiv-711422689">
 


<div style="color:#fff;min-height:0;"></div>