none
Where and how to create complex calculations in rdlc using C#2005? RRS feed

  • Question

  • Hi there

    I am busy with my first rdlc report, and I am stuck on the following issue(s):

    Scenario:
    I am using two SQL tables, Exposure and Position. I have two seperate table areas in the report, next to one-another. (Can we have one data region addressing more than one dataset?)
    My report looks at the data within a date range specified by the user at runtime. (This works)
    The report groups and sorts the detailed information per date. (This works)

    Problems:
    1.    If the Exposure table has entries for the 15th, 19th, 20th and 30th of a month, and the Position table has entries for the 15th, 19th and 30th. The Position 30th is on the same line as the Exposure 20th, I need the dates to be aligned next to each other. If there are no values for a date in either dataset, then a blank line is expected. How do I manage this?
    2.    I have no problem getting totals, averages, etc. However on some of the columns I need weighted averages. E.g. We have a Rate and Amount for each record (in both tables). For a given date, I get the Total Amount for that date, but the average must be weighted against that Total. For each record with a specific date I multiply the Rate with the Amount, after all rows were processed I divide the result by the Total Amount. Where and how do I enter this formula? How can I use an existing textbox in the report within the expression/calculation of another textbox of the same report?

    Many Thanx.
    It's not the blowing of the wind that determines your destination, it's the set of the sail. J Rohn
    • Edited by HennieSmit Wednesday, October 22, 2008 9:17 AM
    Wednesday, October 22, 2008 7:43 AM

Answers

  • well Sum functions are limited to there scope.  And sum functions are designed to work within tables.

    I've never tried to do a sum function against a reportitem.  Usually when I built reports I did as much sum/calculations in the report as possible, to avoid doing them in SQL,  but some things you just have to do in SQL before hand.

    My problem with helping you is I really don't understand the formula your trying to do.  Or the report layout your attempting to create.

    Out of all the expressions you've posted though, this is the only one that looks like it would potentially work

    =SUM((Fields!SpotRate.Value * Fields!Amount.Value) / SUM(Fields!Amount.Value)) 

    But it still didn't.   Could you do this instead?

    (SUM(Fields!SpotRate.Value) * Sum(Fields!Amount.Value)) /  Sum(Fields!Amount.Value)








    Living my life at 123mph in 11.15 seconds
    • Marked as answer by HennieSmit Wednesday, October 29, 2008 6:45 AM
    Tuesday, October 28, 2008 4:58 PM

All replies

  • to refer to other controls in the report, use   "ReportItems!Textbox1.Value"

    Can you write out an example of the formula you need to make, with some mathmatical notation...maybe I can better understand the values your trying to get to and then we could help you create the formula.

    Not sure if this is what your trying to do   but you can do   AVG(Fields!Rate.Value * (Sum(Fields!Amount.Value)))

    Provided none of the values are null, if they are null, then you'll have to potentially insert some conditional statements, but keep in mind that the reportviewer evaluates both sides of conditional operatations before attempting to perform the condition check.

    In regards to making sure your rows line up.  From the SQL side, I'd always return a set number of rows if possible for the dates.  And then in the report, I'd set a rows visibility to be conditon on particular field having a value, if no value, then it would be hidden, if a value is present, then we would show the row.
    Living my life at 123mph in 11.15 seconds
    • Edited by Blasty Thursday, October 23, 2008 12:52 PM grammar
    Thursday, October 23, 2008 12:51 PM
  • Thanx

    Although the report runs for a given daterange, we require the detail to display totals for each date where applicable.

    We multiply the Rate with the Amount for each record and divide the answer by the Total Amount for the day. The Rate is then a Weighted Average on the Amount.

    =SUM((Fields!SpotRate.Value * Fields!Amount.Value) / SUM(Fields!Amount.Value)) 

    However this approach gives me the following compiling error: "The Value expression for the textbox 'SpotRate' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue).  Aggregate functions cannot be nested inside other aggregate functions."

    Since SUM(Fields!Amount.Value is already used in the report, I can address that field.

    =SUM((Fields!SpotRate.Value * Fields!Amount.Value) / tblPositions!PosAmount.Value) 

    This apporach gives the following compiling error: "The Value expression for the textbox ‘SpotRate’ contains an error: [BC30451] Name 'tblPositions' is not declared."

    Then I changed the line to the following

    =SUM((Fields!SpotRate.Value * Fields!Amount.Value) / "tblPositions!PosAmount.Value") 

    It compiled without error, but at run-time all rows for this field contained the text "#Error". Where should I declare the table, in order for me to use it?

    Many thanx.

    It's not the blowing of the wind that determines your destination, it's the set of the sail. J Rohn
    Friday, October 24, 2008 7:32 AM
  • =SUM((Fields!SpotRate.Value * Fields!Amount.Value) / tblPositions!PosAmount.Value) 

    the above didn't compile because   "tblPositions" is invalid.   you can't refer to another report item in that manner.

    If you want to refer to something in another datasource, you have to use the first or last function as an example:

    First(Fields!PosAmount.Value, "TheOtherDataSource")


    Or  =ReportItems!TextBox1.Value

    The reason that your last statement compiled 

    =SUM((Fields!SpotRate.Value * Fields!Amount.Value) / "tblPositions!PosAmount.Value")

    is because  "tblPositions!PosAmount.Value"  is merely a string, and as far as the system is concerned, no different then putting in  "HappyMeal".

    Anytime you try to perform mathmatic operations against a non-numeric value you will usually get #Error in that field.






    Living my life at 123mph in 11.15 seconds
    Friday, October 24, 2008 8:59 PM
  • Thanx

    The "tblPositions" is a table report item I have added to the report. I was under the impression that where you mentioned "ReportItems", I should state the actual report item. My apologies.

    I have changed the expression as follows:
    =SUM((Fields!SpotRate.Value * Fields!Amount.Value) / ReportItems!PosAmount.Value) 
    but now I get the follwing error message: "The Value expression for the textbox 'SpotRate' uses an aggregate function on a report item.  Aggregate functions can be used only on report items contained in page headers and footers."

    Regarding your 1st reply, there is no way of knowing whether a specific date within the date range will have any values in either of the tables. I guess I will to come up with some algorithm where the report checks for dates, and then see whether the same dte is also present in the other table. I'll see if can wrap my mind around something.

    It's not the blowing of the wind that determines your destination, it's the set of the sail. J Rohn
    Saturday, October 25, 2008 8:32 AM
  • I have gone ahead and added another field to the report, within the 2nd table.

    After entering the following the expression to that field:
    =SUM(ReportItems!ExpAmount.Value - ReportItems!PosAmount.Value) 
    I get the following compiler errors:"The Value expression for the textbox 'UnhedgedAmount' uses an aggregate function on a report item.  Aggregate functions can be used only on report items contained in page headers and footers." and "The Value expression for the textbox ‘UnhedgedAmount’ refers to the report item ‘ExpAmount’.  Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.".

    Surely these two statements are contradicting one another?

    Am I missing something big, are RDLC reports difficult to create, or am I just dof?

    Thanx.
    It's not the blowing of the wind that determines your destination, it's the set of the sail. J Rohn
    Monday, October 27, 2008 7:54 AM
  • well Sum functions are limited to there scope.  And sum functions are designed to work within tables.

    I've never tried to do a sum function against a reportitem.  Usually when I built reports I did as much sum/calculations in the report as possible, to avoid doing them in SQL,  but some things you just have to do in SQL before hand.

    My problem with helping you is I really don't understand the formula your trying to do.  Or the report layout your attempting to create.

    Out of all the expressions you've posted though, this is the only one that looks like it would potentially work

    =SUM((Fields!SpotRate.Value * Fields!Amount.Value) / SUM(Fields!Amount.Value)) 

    But it still didn't.   Could you do this instead?

    (SUM(Fields!SpotRate.Value) * Sum(Fields!Amount.Value)) /  Sum(Fields!Amount.Value)








    Living my life at 123mph in 11.15 seconds
    • Marked as answer by HennieSmit Wednesday, October 29, 2008 6:45 AM
    Tuesday, October 28, 2008 4:58 PM
  • Thanx

    With my inability to explain my problem properly, you were actually able to help me with one of my problem.

    The code that I have used is as follows:
    =(SUM(Fields!FEC_Rate.Value *   
     (Fields!Bought.Value - Fields!Sold.Value))) /   
     SUM(Fields!Bought.Value - Fields!Sold.Value) 

    The Amount field is the result of Bought less Sold. For accurary in the report I have to use Bought - Sold instead of Amount. My goal is to get a running total of Rate * (Bought - Sold), once I have the total for the day, I divide my answer by the total of Bought - Sold for that day.

    I will close this thread as resolved, and start a fresh one of the other questions.

    Thanx for all your patience.

    It's not the blowing of the wind that determines your destination, it's the set of the sail. J Rohn
    Wednesday, October 29, 2008 6:44 AM