Use Report Footer fields in group field calculation

[Todd]

      I have a group field that I would like to use to show the percentage of users for that group in comparison to the grand total in the report footer.  Basically I'm doing a count for each group and displaying that in another column, and I want to take that count and divide it by the grand total in the report footer section and display that percentage up in the group field.  Is there any way to do this?  I know the reports are generated from the top down, so I didn't know if you could use a field that doesn't even exist yet at the group level.  Please let me know if you have any suggestions.  Thanks...

[Steve]

Hello Todd,

This functionality is not available as out of the box feature, however it can be achieved with some manual calculations.
One approach for calculating percent of total count is illustrated in the attached example report. The data used in the example is from AdventureWorks, which we suppose that you have already installed with our examples. In the attached Products DataSet you can change connection string according to your Reporting installation.

The idea is that the percent is the ratio of the items in the group / items total count. Items total count we get from the value of the textbox in the Reportheader/Footer section. In the GroupHeader/Footer we place textbox that calculate items in the group. We use simple Count() function but it is possible to evaluate more complex expression in order to count only certain field values.

Please note that:

• We use section ItemDataBound event in order to ensure that all children items (textboxes in our case) are already data-bound
• textboxes used only for calculations can be set to invisible, i.e Visible=false.

Hope these help.

All the best,
Steve
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Todd]

Actually I do not have the AdventureWorks loaded on my install of Telerik Reports, so this example is hard to follow.  We are using vb.net also, so if you had a good vb.net solution example that would help out a ton! 

Basically all my report has is 3 sections -  pageheader, groupheader and a reportfooter.  No detail at all, I just group the data in the groupheader section and want to pull from the reportfooter section in order to calculate the average field in my groupheader section.  Any further help would be greatly appreciated...thanks!

[Steve]

Hi Todd,

You can change your Reporting installation to include the examples, which would install the adventureWorks database for you or download it from the source directly.
You can use our code converter to easily convert the code to VB.NET. Our sample does not use a detail section as well and is according to your description.

Best wishes,
Steve
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Todd]

Ok thanks a lot, I appreciate the help! 

[Todd]

Ok, I tried to plug in the code you gave me to do my calculation and am getting the following error:

Error:

Index was outside the bounds of the array. | at LSReports.LoginMetric.GroupHeaderSection1_ItemDataBound(Object sender, EventArgs e) in C:\LMS2005\LSReports\Evaluation\LoginMetric.vb:line 28

Here is the code that I am using and it bombs out on the Dim totalCount line.  My ReportFooter count field is named TotLogins, but it has an expression of count(fields.logins) to get its value.  Not sure if this is an issue or not.  PercentUsers is my field at the group level, but it errors out before even getting to the calcualation.  Let me know what you think, we are all new to Telerik at our company so this is the first time we have tried to calculate a percentage based on report total fields!  Thanks a lot...



Private

Sub GroupHeaderSection1_ItemDataBound(ByVal sender As Object, ByVal e As EventArgs) Handles GroupHeaderSection1.ItemDataBound

Dim GroupHeaderSection1 As Telerik.Reporting.Processing.GroupSection = TryCast(sender, Telerik.Reporting.Processing.GroupSection)

'get the reportheader total count textbox.

Dim totalCount As Telerik.Reporting.Processing.TextBox = TryCast(GroupHeaderSection1.Report.Items.Find("TotLogins", True)(0), Telerik.Reporting.Processing.TextBox)

Dim categoryCount As Telerik.Reporting.Processing.TextBox = TryCast(GroupHeaderSection1.Items.Find("UserCount", True)(0), Telerik.Reporting.Processing.TextBox)

Dim PercentUsers As Telerik.Reporting.Processing.TextBox = DirectCast(sender, Telerik.Reporting.Processing.TextBox)

PercentUsers.Value = categoryCount.Value / totalCount.Value

End Sub

[Steve]

Hi Todd,

I've attached VB.NET version of the sample I've sent previously that works as expected. Give it a spin and let me know if further help is needed.

Kind regards,
Steve
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[ps]

The implementation helped me. But it is no longer working in 2008 Q3. Please suggest !!

[Meena]

I followed the sample code that you provided to calculate the percent and it did work for me. Now I recently upgraded to Q3 2008 and it doesn't work now. "Report.Items.Find" doesn't return the item.

TryCast

 

(productCategoryHeader.Report.Items.Find("totalCount", True)(0), Telerik.Reporting.Processing.TextBox)

Can you please check on this and let me know if something is changed.
Thanks
Meena

 

[Steve]

Hi guys,

I've just tried this on my end with the Q3 version and did not have any problems (see attached screenshot).

Best wishes,
Steve
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Emilie]

Can anyone translate from VB to C# to get the total amount at group footer section?

[Mike]

Hi Team,

I am trying to build a report that can get Aggregates of all group wise to total..

and also wanted to calculate % of each group with respective to Overall agreegate count.

I have tried using Sum[Field.Count] / Sum[Field.count] on the group section which at any instant is becomming equals
but not taking the overall Agreegate count of the filed to calulate %

Found that your report example show's exactly what we need.. can you suggest a direction with expressions used.. or
provide us with sample project of the same

Thanks in advance
-M

[Steve]

Hi Mike,

In Q1 2010, we've added support for user-defined aggregate functions, which you can use to achieve your requirement. For more info see User Aggregate Functions.

Best wishes,
Steve
the Telerik team