Cannot Reference and SUM fields from Table Group in Footer Expression!
I'm gonna try this again, Microsoft, Please help! There has to be a solution for this!!!! How can I get around the limitation in SSRS 2005 of being able to SUM on a Table's Group field from my group footer expression! Keep in mind, I'm talking about a table here, not the global Report Footer, a Table object's footer! It's driving me nuts My table footer GrossGoal_gt 's field expression (non of these work and throw the error below): =SUM(Fields!GrossGoal1.Value, "CustomerNumber_Grp") neither does this: =RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp") neither does this: =SUM(ReportItems!GrossGoal1.Value, "CustomerNumber_Grp") - this one then gives me yet a different error besides the scope error: Aggregate functions can be used only on report items contained in page headers and footers. or this: =RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp") The field it's referencing's (textbox GrossGoal1) value is: =((Fields!FeeGoal.Value) / Fields!FeeSchedule.Value) * 100 Error: [rsFieldReference] The Value expression for the textbox GrossGoal_gt refers to the field GrossGoal1. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope. and [rsInvalidAggregateScope] The Value expression for the textbox GrossGoal_gt has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. I did that! I set it to my Group name of my table!!!! Am I doing this wrong??????? http://www.eggheadcafe.com/forums/ForumPost.asp?ID=59694&INTID=9 I am having to produce a stupid work around which is create a new dataset, tie it to a stored proc which then does some crazy calculations which is a headache in SQL which sums up my column's total using SQL in the stored proc instead of Reporting Services being able to reference my stupid Group field in the footer in order to sum for a grand total in the footer for that group column. Related threads I have posted: http://database.ittoolbox.com/groups/technical-functional/sql-server-l/936792 Here's are other people with the same problem but no answers out there from my searches! http://database.ittoolbox.com/groups/technical-functional/sql-server-l/scope-problem-in-matrix-945882 http://www.sqlblogs.com/top/ng/group~24/~131100~__Scope-problem-with-SUM-on-group-footer/index.aspx http://www.sqlblogs.com/top/ng/group~24/~130065~__MVPs-and-MS-support--Where-are-you~~~-on-dificults-questions___/index.aspx http://www.developmentnow.com/g/115_2005_2_0_0_455511/GroupingSum-Question.htm http://www.sqlblogs.com/top/ng/group~24/~129523~__RunningValue-Error/index.aspx Why doesn't this work MS ? Scope Because you may have multiple groups defined, you can specify the scope an aggregate function is to use. Scopes are names that you can pass to aggregate functions to indicate how you want to calculate the aggregates. For example, for tables that have more than one group defined, you may want to refer to an outergroup from an innergroup header row. The Nothing scope tells a function to use the outermost scope. For a simple table data region with no grouping, there is only one scope. To identify scopes that are valid for a report item grouping, select the item, open the Properties dialog box, click the Groups tab, and view the list of group labels that can be used to identify scopes. Why am I putting this in big letters? Because I'm about to pull my hair out!!!! Microsoft states this below (http://msdn2.microsoft.com/en-us/library/ms157328.aspx) but it doesn't work for me when I try it (above) and I get those errors above when I try this for my table group: Report Functions Reporting Services provides additional report functions that you can use to manipulate data within a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services). The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region: Copy Code=Sum(Fields!LineTotal.Value, "Order")
February 24th, 2006 6:30am

Based on your previous posts, the problem seems to be that each detail row of the table has an amount (not sure which data set field: maybe Fields!FeeGoal.Value?) and a percent (maybe Fields!FeeSchedule.Value?). Then there is a derived amount expression: "=(Fields!FeeGoal.Value / Fields!FeeSchedule.Value) * 100" in a text box (GrossGoal1). What if, instead, you add a Calculated Field like GrossGoal to your data set, defined by the same expression:"=(Fields!FeeGoal.Value / Fields!FeeSchedule.Value) * 100"? In that case, the detailed row amount can simply be: "=Fields!GrossGoal.Value", and both the group and footer expressions can be: "=Sum(Fields!GrossGoal.Value)".
Free Windows Admin Tool Kit Click here and download it now
February 24th, 2006 9:29am

Deepak, I've definitely tried that work around with little success if any. Most of the time, Visual Studio crashes when trying to SUM on a calculated Dataset field that is also very complex in itself! Also Proj Fee (what I am referring to above for an example of what I'm currently stuck on)is not just a single value, it is a complicated calculation that includes many in it's expression. That's why this report is so crazy and why I really need to be able to bypass all these workarounds to be able to direcly reference fields from the footer and use aggregate functions on those fields. As my report goes from right to left, the fields are not jus straight values, they have a ton of combined fields and calculatiosn within each field so each field really builds on the other...keep in mind this used to be a complex Excel Spreadsheet that they wanted me to automate! the fact is, myself and others see this as a huge problem and limitatio that is keeping us from delivering highly complicated reporting via Reporting Services 2005. We do not want to create wacky ways around this....if that's the case, we might as well go back to Crystal at this point ! Yes, SSRS is great but this limitation is HUGE! Here's Project Fee...yes, it's a nightmare and you'll see now why you can't just assume this work around or that when you get this complicated in your reports; We need to be able to SUM Group Header fields (not detail) in our Footer. I am not using the detail section of my Group, I don't need it. ProjFee Expression is: =(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) + (SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100) You try putting this stripped down as a new calculated field in your dataset and then try doing a SUM on this in the footer of your table and watch Visual Studio Crash!!! It's loads of fun
February 24th, 2006 10:54am

Here is my report file....open it, take a look at the last report at the bottom. The ProjFee in Footer..I want this to sum up my ProjFee Group Column: http://photopizzaz.biz/myreport.zip I've also tried these combinations using straight formulas (changing the SUM around in the past has worked so far up until now) with no luck...all the totals come out wrong: http://www.photopizzaz.biz/calcs_tried_in_projfee_footer_expression.doc What I'm trying to achieve like eveyrone else is to have success putting something like these into my ProjFee Footer textbox expression: =SUM(Fields!GrossGoal1.Value, "CustomerNumber_Grp") Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. =RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp") Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. =SUM(ReportItems!GrossGoal1.Value, "CustomerNumber_Grp") - this one then gives me yet a different error besides the scope error: Error: Aggregate functions can be used only on report items contained in page headers and footers. =RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp") Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
Free Windows Admin Tool Kit Click here and download it now
February 24th, 2006 11:04am

If anyone needs test data to test my report, I'd be happy to provide it so I can figure out this problem, just let me know. It will be non-confidential test data that will work.
February 24th, 2006 11:23am

You may want to try using a custom function and a global variable to store a running total(sort of a SUMIF(value, groupvalue)) that you would create in the code window. See http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/ Actually all of your complex calculations might be simplified by encapsulating in vb functions. Here's more from Teo's book. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp?frame=true
Free Windows Admin Tool Kit Click here and download it now
February 24th, 2006 8:04pm

Yes, but Andrew, I need the SUM in my footer to be summing up the group totals in my group field column...an outside function isn't gonna help me here because Commission % has to be used in the group calculation, I can't use Commission % in my GT because it's not possible to sum a bunch of % then do a calcuation like this. Also, I was told that the group footer takes in the whole dataaset? not just the group totals. Anyway, I think this is crazy first of all that I haven't had an MS staff reply to this problem, and second the documentation says we should be able to reference a groupname as the scope in a field expression from a table footer yet their errors tell a different story. Furthermore, there are at least from my searches, no examples of referencing a groupname or other names other than a dataset in BOL for doing what I want but the error says you should be able to. Where's the documentation on this scope? I only saw a brief comment and the most simplest example in BOL which isn't really helping anyone. Can someone from MS please comment who is in the SSRS 2005 team!! A lot of use are wondering what the deal is here and have for quite some time.
February 27th, 2006 8:00am

Microsoft states this below (http://msdn2.microsoft.com/en-us/library/ms157328.aspx) but it doesn't work for me when I try it (above) and I get those errors above when I try this for my table group: Report Functions Reporting Services provides additional report functions that you can use to manipulate data within a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services). The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region: Copy Code=Sum(Fields!LineTotal.Value, "Order") Ok, so why doesn't this work for me? I tried to reference my group's name in my footer expression to SUM one of my group's columns as stated all along
Free Windows Admin Tool Kit Click here and download it now
February 27th, 2006 9:55am

You should check that whether your sql or view have the column named "GrossGoal1".
March 3rd, 2006 5:06am

Sorry for being rude but I just said 1000 times, it's not a field from my dataset! It's a named textbox in my Report table's group. If you right-click a field in your group in your report, you have to name it something...i.e. textbox50 to something lik GROSSGOL I want to do a SUM on that column textbox that resides in my table's group...not the Dataset! Everyone keeps thinking I'm talking about a Dataset field, no, it's a Table Group Field!! Create a table in SSRS 2005 OK. Then, create a group in that table. Ok, then add some fields to that group Header in your table. Rename one of them to GrossGoal by going to the properties of one of your table's textboxes (which contain expressions like referencing dataset fields!). Ok, then in your Table footer, try to do a SUM(name of the textbox in your group that you just renamed) that's what I'm trying to do! Please Download and Look at my freakin .rdl that I provided, don't just guess what I'm trying to do here, I've provided probably 10x the required information! If you look at my .rdl, right click on the fields and see their properties, then look at the expression for the footer field! It's trying to reference the field above it which is a group field! and one more time, because of the fact that I'm dealing with a commission %, simply pasting the same expression from my group to my footer will not work!! trust me...this is a very complex calculation that willl not come out right going that route like all the books tell you to do!
Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2006 5:24am

Looks like there is a confusion on the scope argument in aggregate functions. What the scope argument tells RS is to summarize all detail data in the specified scope, not summarizing all the aggregated values on these scopes. For example, if you have a table (with table footer) and a table group. (looks like the site that host your report is currently down, so I can't look at your particular case). The hiearchy of the report looks like: Report/DataSet Table (table header/footer rows) TableGroup (tablegroup header/footer rows) In the TableGroup (group header/footer), you can define an aggregate with scope 1)none (the default is the current scope - TableGroup) or 2) TableGroup or 3) Table or 4) Nothing (the outermost scope, meaning the entire dataset, essentially the same as the table scope, in this case). If you are in the table footer, you can define an aggregate with scope 1) none (equivalent to table) or 2) Table or 3) Nothing. As you can see here, you can reach out to outer/containing scopes from the current location, but not the opposite way. This error message "The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set." basically means this. From what you described in the posts, looks like you are trying to sum a set of calculated values (involve aggregates)from the group footers in your table footer. "Aggregate of aggregates" is a feature we don't currently support. It's a high priority item on our wishlist for a future release. For now, there is a workaround for this that involves using custom code(similar to what Andrew has suggested) and can be quite tricky to get it to work.The idea is that youwould passa detail field value to as an argument to the custom function, do the calculation in the function, add it to a variable you define in the custom code, and at the end (in the table group),returnthe variable value which is the grand total you are looking for. Without looking at your particular scenario, I'm not entirely sure if this approach would work for you. But hopefully it can give you some pointer to start with if you want to try it. Below is an old sample I provided to one of our customers to workaound a similar problem. Hope it's of some help to you. <?xml version="1.0" encoding="utf-8"?><Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <RightMargin>1in</RightMargin> <Body> <ReportItems> <Textbox Name="textbox1"> <Style> <PaddingLeft>2pt</PaddingLeft> <FontFamily>Times New Roman</FontFamily> <BackgroundColor>Brown</BackgroundColor> <BorderWidth> <Bottom>3pt</Bottom> </BorderWidth> <BorderColor> <Bottom>Black</Bottom> </BorderColor> <BorderStyle> <Bottom>Solid</Bottom> </BorderStyle> <FontSize>18pt</FontSize> <TextAlign>Center</TextAlign> <Color>White</Color> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> <FontWeight>700</FontWeight> </Style> <ZIndex>1</ZIndex> <rd:DefaultName>textbox1</rd:DefaultName> <Height>0.33in</Height> <CanGrow>true</CanGrow> <Value>Report1</Value> </Textbox> <Table Name="table1"> <Style> <BackgroundColor>White</BackgroundColor> <BorderWidth> <Top>3pt</Top> </BorderWidth> <BorderStyle> <Top>Solid</Top> </BorderStyle> </Style> <Header> <TableRows> <TableRow> <Height>0.21in</Height> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox2"> <Style> <PaddingLeft>2pt</PaddingLeft> <BorderWidth> <Bottom>2pt</Bottom> </BorderWidth> <BorderColor> <Bottom>DarkRed</Bottom> </BorderColor> <BorderStyle> <Bottom>Solid</Bottom> </BorderStyle> <TextAlign>Right</TextAlign> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> <FontWeight>900</FontWeight> </Style> <ZIndex>7</ZIndex> <rd:DefaultName>textbox2</rd:DefaultName> <CanGrow>true</CanGrow> <Value>Order ID</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox4"> <Style> <PaddingLeft>2pt</PaddingLeft> <BorderWidth> <Bottom>2pt</Bottom> </BorderWidth> <BorderColor> <Bottom>DarkRed</Bottom> </BorderColor> <BorderStyle> <Bottom>Solid</Bottom> </BorderStyle> <TextAlign>Right</TextAlign> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> <FontWeight>900</FontWeight> </Style> <ZIndex>6</ZIndex> <rd:DefaultName>textbox4</rd:DefaultName> <CanGrow>true</CanGrow> <Value>Freight</Value> </Textbox> </ReportItems> </TableCell> </TableCells> </TableRow> </TableRows> <RepeatOnNewPage>true</RepeatOnNewPage> </Header> <Details> <TableRows> <TableRow> <Height>0.21in</Height> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox3"> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>1</ZIndex> <rd:DefaultName>textbox3</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <CanGrow>true</CanGrow> <Value>=Sum(Code.MyFunc(Fields!OrderID.Value, Fields!Freight.Value))</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="Freight"> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <rd:DefaultName>Freight</rd:DefaultName> <CanGrow>true</CanGrow> <Value>=Fields!Freight.Value</Value> </Textbox> </ReportItems> </TableCell> </TableCells> </TableRow> </TableRows> <Visibility> <ToggleItem>OrderID</ToggleItem> <Hidden>true</Hidden> </Visibility> </Details> <DataSetName>Northwind</DataSetName> <Width>2in</Width> <TableGroups> <TableGroup> <Header> <TableRows> <TableRow> <Height>0.21in</Height> <TableCells> <TableCell> <ReportItems> <Textbox Name="OrderID"> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>5</ZIndex> <rd:DefaultName>OrderID</rd:DefaultName> <CanGrow>true</CanGrow> <Value>=Fields!OrderID.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox5"> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>4</ZIndex> <rd:DefaultName>textbox5</rd:DefaultName> <CanGrow>true</CanGrow> <Value>=Fields!Freight.Value</Value> </Textbox> </ReportItems> </TableCell> </TableCells> </TableRow> </TableRows> </Header> <Grouping Name="table1_OrderID"> <GroupExpressions> <GroupExpression>=Fields!OrderID.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!OrderID.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> </TableGroup> </TableGroups> <Top>0.33in</Top> <Footer> <TableRows> <TableRow> <Height>0.25in</Height> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox6"> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>3</ZIndex> <rd:DefaultName>textbox6</rd:DefaultName> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox7"> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>2</ZIndex> <rd:DefaultName>textbox7</rd:DefaultName> <CanGrow>true</CanGrow> <Value>=Code.MyFunc(Nothing, Fields!Freight.Value)</Value> </Textbox> </ReportItems> </TableCell> </TableCells> </TableRow> </TableRows> </Footer> <TableColumns> <TableColumn> <Width>1in</Width> </TableColumn> <TableColumn> <Width>1in</Width> </TableColumn> </TableColumns> </Table> </ReportItems> <Style /> <Height>1.21in</Height> </Body> <TopMargin>1in</TopMargin> <DataSources> <DataSource Name="Northwind"> <rd:DataSourceID>5b047e43-435e-4e68-bf7b-70eaf0f45c53</rd:DataSourceID> <ConnectionProperties> <DataProvider>SQL</DataProvider> <ConnectString>data source=.;initial catalog=Northwind</ConnectString> <IntegratedSecurity>true</IntegratedSecurity> </ConnectionProperties> </DataSource> </DataSources> <Code> Dim orderIDs As System.Collections.Hashtable Dim total As Double Function MyFunc(ByVal orderID As Object, ByVal freight As Object) As Double If (orderIDs Is Nothing) Then orderIDs = New System.Collections.Hashtable End If If (orderID Is Nothing) Then MyFunc = total Else If (Not orderIDs.Contains(orderID)) Then total = total + freight orderIDs.Add(orderID, freight) End If MyFunc = total End If End Function</Code> <Width>5in</Width> <DataSets> <DataSet Name="Northwind"> <Fields> <Field Name="OrderID"> <DataField>OrderID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="Freight"> <DataField>Freight</DataField> <rd:TypeName>System.Decimal</rd:TypeName> </Field> <Field Name="Expr1"> <DataField>Expr1</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="UnitPrice"> <DataField>UnitPrice</DataField> <rd:TypeName>System.Decimal</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>Northwind</DataSourceName> <CommandText>SELECT Orders.OrderID, Orders.Freight, [Order Details].OrderID AS Expr1, [Order Details].UnitPriceFROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderIDWHERE (Orders.OrderID IN (10248, 10250))</CommandText> </Query> </DataSet> </DataSets> <LeftMargin>1in</LeftMargin> <rd:SnapToGrid>true</rd:SnapToGrid> <rd:DrawGrid>true</rd:DrawGrid> <Description /> <rd:ReportID>1e9e64e8-3f34-4760-ae9a-0789ad4c44c7</rd:ReportID> <BottomMargin>1in</BottomMargin> <Language>en-US</Language></Report>
March 5th, 2006 2:42am

Fang Wang, this is a huge limitation, any idea when this will be supported? If it was, I would have been able to save literally a month worth of wasted time. Basically I ended up having to do this sum in SQL which was painful because it included about 7 complicated SUMS before it which were wrapped up into this final Column SUm and I do not wish to have to code SQL like that again. On the other hand custom code as you stated is not easy and definitely something I would not wish on anyone just to sum up a Column in this situation. thanks for th workaround and the FYI on the non-supported note though! Much appreciated. Lets hope for all of us (many who have posted this problem in other forums) that MS comes up wtih a SP which will fix this and provide this very powerful option in the future.
Free Windows Admin Tool Kit Click here and download it now
March 5th, 2006 8:35am

and another complaint, it is endless, this time with a matrix! http://database.ittoolbox.com/groups/technical-functional/sql-server-l/scope-problem-in-matrix-945882
March 7th, 2006 6:28pm

First of all enough with the caps / bold and large text. We understand your problem, and quite frankly the world is not going to end. Secondly, the solution (workaround) was provided and DOES work. Please do some reading on how to use and control flow using custom code in rs. Jon
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2006 12:24am

Not sure if you have gone through the entire solution above, however this is another possible solution if that didn't work (from my blog at http://sqlrs.blogspot.com) One common problem in reporting and BI solutions is how to incorporate data from both an OLAP cube and relational tables. The UDM in SQL 2005 attempts to solve this, however it really means you still need to build the information into your cubes and dimension attributes.What if you don't want to or can't?Reporting Services provides a Custom Code tab within the Report Properties page. You can access various VB.NET objects and system assemblies, and reference external assemblies. One of the internal assemblies is the Dictionary object.Steps to lookup values from a reference table in SQL:Drag a list onto the report.Drag a textbox into the list, or a field from the relational dataset. Modify the textbox to contain =Code.setValue(Fields!KeyField.Value, Fields!ValueField.Value)Create another list below. Drag another textbox into the list. Modify the textbox expression to hard-code the key for now. =Code.getValue("MyKey")In the Code Properties window, try the following:public dict as new System.Collections.Generics.Dictionary(Of System, System)function setValue(value as object, value2 as object) as objectdict.Add(value,value2)return valueend functionfunction getValue(value as object) as objectreturn dict(value)end functionAfterwards, you can hide the list box (or table or whatever) that loads the variable with the setValue function. The dictionary still gets populated.If you have properly bound a table to the first list control, you should be able to lookup results in the second table. This can be applied in many scenarios, including adding relational reference data to MDX results, and creating a relationship between two separate datasets.I'd be interested to know if anyone uses this. It seems to have many different applications. One could possibly involve showing two sets of information, for things like variances or budget vs. actual data. If a value doesn't exist in the dictionary, the original field could be returned. If it does exist, the adjustment could be returned. Note that Generics is .NET 2.0 - for 2000 you may need to use a different syntax but the concept is the same. Basically you're using a dictionary object (could be a hash table or whatever) to store a value by a key. Then you're looking up that value in a table (or list or whatever) to do further calculations. Oh, and Flavaflav, I really hope you don't wear a clock around your neck & have gold teeth. cheers, Andrew
April 20th, 2006 1:06am

Jon, Reason for the caps... a) this problem has been posted all over the internet and MS has no solution, therefore it's legit enough for MS to get a better way than your work around which is hidious b) I have not received responses on this in other posts in this forum, so I thought to really get some attention and get some answers from MS after repeatedly trying to do so with no luck. let's face it although annoying, caps was effective in this time of need. Who wants to do some wierd ass work around for simply summing up a column in a table? >>>We understand your problem, and quite frankly the world is not going to end. that depends buddy; if you have a moron boss who doesn't understand or care about this limitation and expects results...you have no idea what I have had to deal with at my current culture....small company BS >>Please do some reading on how to use and control flow using custom code in rs. and a link please so I don't have to search the entire BOL for what you are trying to get me to read?
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2006 1:48am

Andrew, thanks much...I will try your work around...and appreciate the time you took to post. I guess I don't have a choice but use some funky work aroundsuntil MS improves and allows users to somehow, someway, do a =SUM(tablefield) in the table footer easily!
April 20th, 2006 1:53am

alrighty jon (just for you), I took out the caps (being only one sentence in the entire thread) so now it looks nice and pretty. If you guys don't want such outrageous font, then limit what we can do...the size, bold, etc. otherwise I can post as I choose. Can you tell I just don't need comments like yours? better off next time not posting then if you are just going to start a fire
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2006 2:04am

How about creating a second table in the report that uses the same datasource, and uses groups/filters to accomplish what you want.I'm facing a similar issue, and this is the approach I'm taking. I am not sure how performance will be...
October 4th, 2006 5:17pm

>>>How about creating a second table in the report that uses the same datasource yes, ok, but why not just be able to do a SUM instead of all this hokey sh%% work arounds is my complaint!
Free Windows Admin Tool Kit Click here and download it now
October 4th, 2006 5:19pm

My test shows that the approach of a second table that references the same datasource works well, and you don't get a performance hit, since the data is only retrieved once. That is good news, and for me this is a reasonable approach. I have found every tool will have limits. The important thing is to find a tool that gives you many ways to solve a problem. Good luck Flavor. BTW, Love your show.
October 5th, 2006 4:58am

There are some things you shouldn't have to figure out other ways. A Simple Sum on a column is one of them. Furthermore, when you're coding a report as complicated as I was, you'll understand that you don't need another week to figure out how you're gonna do something as simple as get a SUM as we've all been trying to do. The report I was doing was a huge excel spreadsheet with hundreds of calculations. Each calculation was complex and rolled up and up. You cannot simply just create totals in another table so easily as you are stating per the type of report I coded. I had 1000 lines of SQL in the stored procs behind it. And th at was the most simple way to do so. When you're dealing with a finanical company, they don't understand that some things are better left to Excel, not a Reporting environment. Of course it doesn't help if your boss is an idiot either.
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2006 4:53pm

That is why we get paid the "big" bucks:)
October 5th, 2006 5:51pm

No, I hate that statement. First off, some garbagemen make more than us. Not all IT folkes make a ton. Second, I like to spend time with my family, not sit around and have to think of crazy work arounds for something as stupid as this and as simple as this should be. I've got plenty of challenges like this, I don't need a ton more,I have a life to live unlike some folks. let me guess, you're still y oung? I'm 30 and don't feel like dealing with this sort whatI call lack of functionality that a report should do by nature...it's a SUM!! I'll refrain from further comments but that comment is naive. life is short, I don't intend on solving EVERY shortcoming in the programming world, my list is long enough.
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2006 9:06pm

You could always switch to one of those high-paying, low stress garbage collector jobs!
October 5th, 2006 9:11pm

what a jerk!
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2006 9:42pm

Hello If ur still facing the same problem with sum( ) - u can try 1 more thing. Just check whether tat text box is returning some numeric value or not. Because sum( ) requires only numeric value as u know. U can use isnumeric( ). After that try to first convert the textbox value in decimal or float and then apply Sum( ) so the expression would be =sum(CDEC(Textbox.value)) If it works - mail me @ saurabhnil@gmail.com All the very best...
November 18th, 2008 9:40am

Hi, After working with this issue I found that the post from "Saurabh N" was working :-) The only thing to remember is to put in a '0' value in the if statement because it can't calc on a null value. Regards, Anders JeppesenAnders Jeppesen
Free Windows Admin Tool Kit Click here and download it now
July 1st, 2009 9:10am

I am still using 2005 SQL Reporting Services. I am getting same problem. I went through many forums but didn't get any solution. Actually, I also want to use the text box of Table in a report. Suppose, there are two groups say group1 and group2 and group2 is under group1. Lets say we have to Display the Subtotal in group 2 and grand total in group 1.I used expression =Sum(Fields!Amount,"group2") to display Sub total and I renamed the text box of this value to "txtSubTotal". Now I am going to calculate grandTotal by using this text box value. I need to re-use txtSubTotal value by using like Sum(ReportItems!txtSubTotal.Value,"group1") but It doesn't work. Yes, I can use =Sum(Fields!Amount,"group1") for GrandTotal but I don't want to do that. This is only a simple example I am saying you. In my real case I have to use the Sub total text value to calculate the grand total. I don't know is it possible in sql reporting services 2005 or not. If possible how? and Is there any other way to achive this issue? support and search
August 21st, 2009 11:21pm

NoEgo, I'm having same exact problem like you had. I'm grouping on date and i've to show subtoal in group footer and grand total in table footer. I'm not able to get grand total in table footer. there is a column in my report "Receipt" Receipt on details =IIf(First(Fields!Type1.Value="R"), Sum(Fields!Amount.Value), "$0.00") Receipt on group footer =IIf(first(Fields!Type1.Value= "R"),Sum(Fields!Amount.Value),"") Buti instead on doing this in footer Receipt on table footer. =IIf((Fields!Type1.Value="R"), Sum(Fields!Amount.Value), "$0.00") it does "Sum(Fields!Amount.Value), " Since you have done this in past. could you pls. help me out with this. email: pratul_sri@hotmail.com Thanks, Pratul.
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2010 6:37pm

why don't you try =sum(fields!yourfield.Value,"yourTableName"). this will total up only the displayed values in the table
July 20th, 2010 7:39pm

it's not working for me i have same problem 1 =IIf((Previous(Fields!total.Value) <> Fields!total.Value),sum(Fields!total.Value),0) it does not retun correct value 2.sum(Fields!total.Value,GroupName) is any other way........ please reply
Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2010 9:05am

"why don't you try =sum(fields!yourfield.Value,"yourTableName"). this will total up only the displayed values in the table" -- Not True! I think he did. This will total ALL the fields from the dataset (or whereever) not the group fields (ie in the TextBoxes) for the table. We discovered to our horror that scope only works for data coming from a datatable, it's impossible to sum up groups with ReportViewer (whether or not you use the scope name. = sum(fields!yourfield.Value,"yourTableName") = sum(fields!yourfield.Value,"yourGroupName") = sum(fields!yourfield.Value,"anything at all") no matter which one you use, it will ALWAYS sum the data fields from the datatable (and not what you see in your group output). Use Crystal Reports web viewer. MS ReportViewer cannot handle aggregates of Groups (making it useless as a professional tool). The only work around is to create another query with subtotals for each grouping and make another call to the database.
August 5th, 2010 12:33am

Hi Saurabh, I used like this SUM(CDEC(ReportItems!TxtTotalImpact.value)) in the report group footer. TxtTotalImpact is the text box in the detail row of the report. BUT, it did not work for me. I have read this entire thread and I do have same issue that NoEgo mentioned from the begining. Can you please send any sample or elaborate your answer? I think, this issue seems to be complex. Please share your experience with us w.r.t this issue; it could be helpful for all who have face the same issue. Thanks, Jey
Free Windows Admin Tool Kit Click here and download it now
November 26th, 2010 6:28am

Hi FangWang, Its good news from you that the next release of SSRS may resolve this issue (it was in your wish list). As you have mentioned this in the early 2006, does it implemented in SSRS 2008 or 2008 R2 version? Note: I am using Report Builder 2.0, but I am still facing this issue. Thanks, Jey
November 26th, 2010 6:42am

In table footer ---------------- write below formula ="Tot:" & Sum(Cint(Expression)) ex: ="Tot:" & Sum(Cint(IIF(Fields!numdueinst.Value=0,0,Fields!numdueamount.Value+Fields!numdamagesrec.Value))) In report footer ---------------- formula: =Sum(cint(textboxname.value)) ex: =Sum(CInt(ReportItems!textbox87.Value)) I have four groups If Any Problems please send query
Free Windows Admin Tool Kit Click here and download it now
March 24th, 2011 4:27am

In table footer ---------------- write below formula ="Tot:" & Sum(Cint(Expression)) ex: ="Tot:" & Sum(Cint(IIF(Fields!numdueinst.Value=0,0,Fields!numdueamount.Value+Fields!numdamagesrec.Value))) In report footer ---------------- formula: =Sum(cint(textboxname.value)) ex: =Sum(CInt(ReportItems!textbox87.Value)) If Any Problems please send query
March 24th, 2011 4:29am

Hi, You can resolved that problem here: http://beyondrelational.com/blogs/jason/archive/2010/07/03/aggregate-of-an-aggregate-function-in-ssrs.aspx Regards, Diego V.
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2011 11:51am

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

Other recent topics Other recent topics