Report viewer RDLC report grouping

    Question by:
    On

    Topics:

    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!
    Good Question?
    0
     

    ?

    The member who asked this question verified this comment provided the solution that solved their problem.

    Accepted Solution on 2007-11-29 at 15:26:48ID: 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...

    verified

    answer

    Enjoy your free answer!

    Join now to get more tech resources.

    Become a member

    Comments

     
    PFrog

    Expert Comment

    2007-11-28 at 09:04:18ID: 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()


    Good Comment?
    0
     
    PFrog

    Expert Comment

    2007-11-28 at 09:09:01ID: 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!

    Good Comment?
    0
     
    cswebdev

    Author Comment

    2007-11-28 at 19:46:14ID: 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!
    Good Comment?
    0
     
    PFrog

    Expert Comment

    2007-11-29 at 03:34:34ID: 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...
    Good Comment?
    0
     
    cswebdev

    Author Comment

    2007-11-29 at 13:42:48ID: 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!
    Good Comment?
    0
      

    Accepted Solution

    2007-11-29 at 15:26:48ID: 20378401Best
    PFrog earned 500 total points
    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...
    Good Solution?
    0
     
    cswebdev

    Author Closing Comment

    2007-11-29 at 16:56:04ID: 31411452
    very helpfull!
    Good Comment?
    0

    Featured Post

     Java Android Coding Bundle

    Whether you're an Apple user or Android addict, learning to code for the Android platform is an extremely valuable, in-demand skill. It all starts with Java, the language behind the apps and games that make Android the top platform it is today.

    Ask Your Tech Question. Get Expert Solutions.We will email you as soon as we have your answer.

    We will never share this with anyone.

    Select topics

    You may select up to five topics.

    Top Expert Contributor

    Essential articles and videos from the Experts

    1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
    Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
    After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
    This video Micro Tutorial is the second in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles a…

    More valuable questions with Expert answers

    Question: Hi, I generated memory consumption page using SQL server default statistics report. Kindly some one give me about what is these stats and how that impact the resources base on this report. eg. is there any issue with memory usage by the current...

    Answer: Can't explain everything because I don't know all the stats. Will focus only in what I think is more important: Page life expectancy - "“Number of seconds a page will stay in the buffer pool without references”:So, a bigger the number, better....

    Question: Hello, I have a client running Microsoft SQL 2000 and disk space is low. I am running TreeSize and found that my SQL DATA is high. Not sure if I need to truncate data or how I can clean it up? I am trying to locate disk reports within SQL 2000...

    Answer: If it's "FULL" recovery mode, you'll need to truncate the log before you can shrink it: BACKUP LOG [Test2] --db_name WITH TRUNCATE_ONLY CHECKPOINT DBCC SHRINKFILE( 2, 1024 )

    Question: Hi guys, As the title suggests, I have created a shared data source on the report server . Is it possible to link to this data source on a report I have created on my local machine? If so how is this done? Or does the report need to be...

    Answer: You don't need to, nor can't, access the shared data source on the report server. You need to create a shared data source in each of your projects, each pointing to wherever they need to point to. This data source will only be used during...

    Question: Experts, I have a table has data in scattered columns (Dx1 - Dx12). Can someone tell me how to move the data from all respective columns to ensure that columns 1 -4 are populated when they are blank. See attached spreadsheet. ( I would like...

    Answer: Hi there Morinia, If you want to do it is SAS, then the following process is probably the way to go. I have used the new PROC DS2 and PROC FEDSQL because they offer a better programming environment with a better check on undeclared variables...

    201607-LO-Qu-086

    Extend your technology team with the Experts Exchange community.

    — trusted by —

    Who answers my questions?Our community has technology experts around the world.

    Doug Walton

    2

    Articles

    7

    Solutions

    Vikas Garg

    6

    Articles

    310

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • MS SQL Server 2005
    • Query Syntax
    • SSIS

    nareshnookala

    1

    Articles

    1

    Solutions

    Tone&#39; Shelby

    1

    Articles

    134

    Solutions

    Expert in:

    • DB Reporting Tools
    • MS SQL Server
    • MS SQL Server 2005

    RELATED TOPICS view all topics

    1. Crystal Reports
      (34,352)
    2. MS SQL Server
      (155,325)
    3. MS SQL Server 2005
      (71,254)
    4. SSRS
      (8,573)
    5. Databases-Other
      (53,221)
    6. .NET Programming
      (130,878)
    7. MS SQL Server 2008
      (48,047)
    8. Oracle Database
      (77,503)
    9. Query Syntax
      (46,835)
    10. MS Access
      (214,533)
    Receive Monthly Emails of Tech News and Trends from Experts Exchange