Solved

Report viewer RDLC report grouping

Posted on 2007-11-28
7
23,715 Views
Last Modified: 2011-10-03
Hello,
When we create a grouping for RDLC report, we can insert a total for that grouping on the footer section by using =sum(fields!quantity.value). It works when the value in integer, but can we stack them as string and display them on single line on the footer section of the group. For example something like this:

group: Country
detail line:                           quantity
                                           2
                                           3
                                           5
group footer:        2,3,5

----------------------------------
In above example:
I am showing the data of detail line just to explain what I need, but I am going to supress it in my report and just show one line group footer which contains the data of detail line separeted by comma.
Thanks!
0
Comment
Question by:cswebdev
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:PFrog
ID: 20366908
You can use custom code to join all the values together in a string, however you will not be able to access the result in the table footer, it would have to be after the table. This is because SSRS renders the footer before the table contents.

If you want to do this, add this to your custom code

Dim AllValues as String
Public Function SetValue(Val as string) as string
    AllValues = AllValues + ", " + Val
    Return Val
End Function
Public Function GetAllValues() as string
   Return mid(AllValues,3)
End Function

In your table, replace the cell
    =sum(fields!quantity.value)
with
    =Code.SetValue(sum(fields!quantity.value))

Then, after the table, add a textbox with the value
    =Code.GetAllValues()


0
 
LVL 18

Expert Comment

by:PFrog
ID: 20366944
Further to this, if you add a list to your report, then use the list for the grouping instead of the table, then you should be able to make this work. The table would then be wthin the list, along with a row of textboxes to show the group totals.

If this doesn't make any sense let me know and I'll try again!

0
 

Author Comment

by:cswebdev
ID: 20371203
I tried applying your technique, and it works perfectly for first group, but when it moves to next group the data from previous group also gets stacked up. And I don't know where I need to reset the value to get new string list for new group. Also, could you suggest me of any possible way to display the calculated value directly under the group (I know you said we cannot put under group footer) rather than adding after the table. I am willing to use List if it is works better for my need.

Thanks!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:PFrog
ID: 20372727
To reset the string use this function

Public Function ResetValue() as string
    AllValues = ""
    Return ""
End Function

Try this:

Add a list to the report, set this to be grouped using whatever your current table gouping uses.
Remove the grouping fro your table, and put the table inside the list.
In the list, add a textbox above the table containing
    =Code.ResetValue()
In each row of the table, use
    =Code.SetValue(sum(fields!quantity.value))
Add a textbox in the list but below the table containing
    =Code.GetAllValues()

Then, the custom code runs outside of the table to it will work, but the whole lot is repeated according to the grouping of the list.

Let me know if you get any problems...
0
 

Author Comment

by:cswebdev
ID: 20377592
Hello,
I tried for a while, but it didn't work like I expected, so we ended up deciding to create a drill through report instead. I will give you the whole point if you could tell me a very easy way to count the number of records within a group excluding the counts of records with '0' in one of their fields. For example
Group: A
                                 1
                                 0
                                 9
Footer-> non-zero records: 2
Group B
                                 5
                                 8
                                 0
                                  1
Footer-> non-zero records: 3


Thanks!
0
 
LVL 18

Accepted Solution

by:
PFrog earned 500 total points
ID: 20378401
Sure, in the SQL query add an extra column
   CASE WHEN quantity=0 THEN 0 ELSE 1 END AS NonZeroCount
Then, just use
  =Sum(Fields!NonZeroCount.Value)
to get the number of non-zero records for  particular group.

I have used the list/table combo before to do what you want, and it does work, however it always takes a fair bit of persistent playing around with to get it to do what you want. Drillthrough reports (and/or sub-reports) would indeed make life significantly more simple, so long as you're ok not having all the information in a single report...
0
 

Author Closing Comment

by:cswebdev
ID: 31411452
very helpfull!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now