Calculate Column Sum
Hello Example i have following tabele named Sales: ID Number price 1 5 5 2 6 10 3 18 15 4 35 20 I whant to display this data in report service like that determine in wich interval([1-15] or [15-30] or [30-60] ) must be number's value and after that caclulate sum of this columns: calculate sum of column here 11 18 35 ID Number price [1-15] [15-30] [30-60] 1 5 7 5 2 6 10 6 3 18 15 18 4 35 20 35 How can i do this?
October 13th, 2010 4:56pm

Here is one way: Develop a stored procedure which calculates in the desired ranges as data source. Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
Free Windows Admin Tool Kit Click here and download it now
October 13th, 2010 5:08pm

Seems to be simple , Let me check if i can help you Will 15 fall in first slab [1-15] or [15-30] ? Enable page Header with 3 textboxes above your calculated column's page header --- txtbx1 txtbx2 txtbx3 ID Number Price [1-15] [ 15-30] [30-60] txtcalc1 txtcalc2 txtcalc3 For the three textboxes give the following expressions : txtcalc1 expression =IIF(Fields!Number!value>=1 and Fields!Number!value<15 , Fields!Number.Value,Nothing) txtcalc2 expression =IIF(Fields!Number!value>=15 and Fields!Number!value<30 , Fields!Number.Value,Nothing) txtcalc3 expression =IIF(Fields!Number!value>=30and Fields!Number!value<60 , Fields!Number.Value,Nothing) Expressions for Page header textboxes . txtbx1 =Sum(ReportItems!txtcalc1.Value) txtbx2 =Sum(ReportItems!txtcalc2.Value) txtbx3 =Sum(ReportItems!txtcalc3.Value) Thanks Rajkumar Yelugu
October 13th, 2010 5:10pm

example 15 will fall in first slab [1-15] ? txtcalc1,txtcalc2,txtcalc3 these are names of textboxs? in my case this is nm, nm_1,nm_2 i tryed =Sum(ReportItems!nm.Value) but get this error: [rsAggregateReportItemInBody] The Value expression for the textbox 'textbox3' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers. [rsReportItemReference] The Value expression for the textbox textbox3 refers to the report item nm. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope. Build complete -- 2 errors, 0 warnings
Free Windows Admin Tool Kit Click here and download it now
October 13th, 2010 5:27pm

HI Lasha89 , You will have to give this expression =Sum(ReportItems!nm.Value) in a textbox which is in a Page header . Make sure you retry using all my steps correctly i'm sure it should work . Thanks , Rajkumar Yelugu . Rajkumar Yelugu
October 14th, 2010 10:23am

Hi Rajkumar Yelugu I made evrything as you said,I can not only calculate sum of columns now it is like that: ID Number price [1-15] [15-30] [30-60] 1 5 7 5 1 6 10 6 1 18 15 18 2 35 20 35 I whant group by name this data like that: ID Number price [1-15] [15-30] [30-60] --------> this will be main header - 1 SUM of columns must be here (11) -------->this will be group header 5 7 5 6 10 6 - 2 SUM of columns must be here (18) (35) 18 15 18 35 20 35
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2010 12:27pm

Is this what you are looking for? ID Grouping ID Number Price [1-15] [16-30] [31-60] 1 Sum 11 18 5 7 5 6 10 6 18 15 18 2 Sum 13 13 12 13 if yes.. then you can get this RDL source here http://www.oasisskinandbody.co.za/images/IDGrouping.rdlAdriaan Sullivan - http://it-burns-when-i-sp.blogspot.com/
October 14th, 2010 2:50pm

Yes i whant to do like that only i whant to change numer with price, i can do this butwhen i uploaded your rdl on my report service i get error: The report definition is not valid. Details: Unexpected XML declaration. The XML declaration must be the first node in the document, and no white space characters are allowed to appear before it. Line 1, position 5. (rsInvalidReportDefinition) ID Grouping ID Number Price [1-15] [16-30] [31-60] 1 Sum 17 15 5 7 7 6 10 10 18 15 15 2 Sum 12 13 12 12 Can you explain how calculeted sum of column?
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2010 3:06pm

Hmm.. are you on VS 2005? RDLs are not backward or forward compatible. Here is the sum formulas for each column. [1-15] =Sum(IIF(Fields!Number.value>=1 and Fields!Number.value<15 , Fields!Number.Value,Nothing)) [16-30] =Sum(IIF(Fields!Number.value>=15 and Fields!Number.value<30 , Fields!Number.Value,Nothing)) [31-60] =Sum(IIF(Fields!Number.value>=30 and Fields!Number.value<60 , Fields!Number.Value,Nothing)) You will see it is just SUM(<cell calculation>) I assume you would also want to group on ID in your table.Adriaan Sullivan - http://it-burns-when-i-sp.blogspot.com/
October 14th, 2010 3:10pm

Thank you i think that is what i whant , i'll try and after that i'll mark your answer
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2010 3:34pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics