locked
Group Total in Group Footer with PageBreak RRS feed

  • Question

  • Hi All,

    How does one get a total of certain group within a group footer a table having another group for breaking the records on number of records? I have table with two groups -

    group1 =Fields!TransportType.Value [Include Group Header,Include Group Footer,Repeat Group Header]
    group2 =Ceiling((RowNumber(Nothing))/1000), [Page break at End]

    The TotalOustanding Amount that i get for the footer row is always limited to the 1000 values. I need to display the total for all values belonging to the group at the end of the series rather than the individual page totals.

    Please help. I have been doing exhaustive searching for more than a week now and I have also explained the same in other thread which I don't know for some strange reason nobody is responding.

    Regards,
    Karthik
    • Edited by V Karthik Monday, February 8, 2010 6:48 AM
    Friday, February 5, 2010 7:37 AM

Answers

  • Hi Karthik,

    I have received your email, and understood the issue. The issue only has something to do with "table2_Group4" and "table2_Group1". To benefit other community users who might encounter this issue, i am going to rehash this case briefly.

    Scenario:
    There is a table with a amount of data and its columns include"CustomerID","CustomerType" and "Totaloutstanding". Now, run SSRS 2005 to report this table data. On this report, 
    1. In the report dataset, these data are ordered by "CustomerID", one CustomerType has one CustomerID, the CustomerID values like 1,2,3......  
    2. Insert a Table control to host these data,
    3. The data on every page of this report has only less than or equal 1000 rows. To achieve this, insert a group for the detail rows with group-on expression =(RowNumber(Nothing)-1)\1000. Assuming this group is Group1.
    4. The data on every page of this report need to be grouped by "CustomerType". To achieve this, insert a group under the Group1 for the detail rows with group-on expression =Fields!CustomerType.value, and tick these options "Include group header","Include group footer" and "Repeat group header". Assuming this group is Group2.

    5(?). How to display the subtotal based on CustomerType at the end of each CustomerType series?  Note that one CustomerType might be span multiple pages by Group1, and one page(Group1 instance) might have multiple Group2 instance.

    Solution:(for the above 5th requirement: how to display the subtotal based on CustomerType at the end of each customerType series)
    In my opnion, there are 2 main steps, one is how to calculate the subtotal based on the "CustomerType", and the other is how to hide the Group2 footer rows if they are not at the end of each CustomerType series.
    1. The possible alternatives for how to calculate the subtotal based on the "CustomerType", there are 2 options you can follow:
        1)Use semi hard-code: In the Group2 footer cell, type this expression =Switch(Fields!Customer.Value="Person",SUM(IIF(Fields!Customer.Value="Person",Fields!Totaloutstanding.Value,0),"StatisticTbl"),Fields!Customer.Value="Company",SUM(IIF(Fields!Customer.Value="Company",Fields!Totaloutstanding.Value,0),"StatisticTbl"),Fields!Customer.Value="Electronic",SUM(IIF(Fields!Customer.Value="Electronic",Fields!Totaloutstanding.Value,0),"StatisticTbl")) . Note that "StatisticTbl" is the report dataset name, and "Person","Company" and "Electronic" are the "CustomerType" examples.
        2)Use the subreport to do this:Create a new report, dataset:select CustomerType,sum(Totaloutstanding) as subtotal
                                                                                                from StatisticTbl
                                                                                                where C
    ustomerType= @cust
                                                                                                group by customerType
                                                                      report parameter:name->cust, datatype->string
                                                                      report body:drag a table control and only one detail cell via deleting other cells, and put the =Fields!subtotal in this cell. Adjust this report page size to fit on one cell of the main report Group2 footer. On the main report, in the cell displaying the subtotal of "CustomerType" of Group2 footer, insert a Subreport control to reference this newly created report and set this subreport parameter "cust" point to =Fields!CustomerType.value.
    2. After implementing any option of the first step, the subtotal based on the "CustomerType" will be displayed in each Group2 footer. Now, how to hide these footer rows not at the end of the "CustomerType" series? Click the handle of the Group2 footer row to select the entire row, and locate its Hidden property under Visibility note in the Properties box, then type the following expression to hide the footers not at the end of each "CustomerType":

    =IIF(Runningvalue(Fields!CustomerType.Value,countdistinct,"Group1")=1,IIF(Count(Fields!CustomerType.Value,"Group2")=1000,true,false),false)

    thanks,
    Jerry
    • Marked as answer by V Karthik Friday, February 12, 2010 3:27 PM
    • Unmarked as answer by V Karthik Monday, February 15, 2010 4:44 AM
    • Marked as answer by V Karthik Wednesday, February 17, 2010 9:11 AM
    Friday, February 12, 2010 2:58 AM
  • Hi,

    Please try the following expression:

    =Switch(Fields!CustomerType.Value="Person",SUM(IIF(Fields!CustomerType.Value="Person",CDbl(Fields!TotalOutstanding.Value),CDbl(0)),"OverdueStatsDetails"),Fields!CustomerType.Value="Company – traditional invoicing",SUM(IIF(Fields!CustomerType.Value="Company – traditional invoicing",CDbl(Fields!TotalOutstanding.Value),CDbl(0)),"OverdueStatsDetails"),Fields!CustomerType.Value="Company – electronic invoicing",SUM(IIF(Fields!CustomerType.Value="Company – electronic invoicing",CDbl(Fields!TotalOutstanding.Value),CDbl(0)),"OverdueStatsDetails"))

    if this still doesn't help, please check the data type of this field in the underlying database, and the detail cell textbox format property of this field on your report.  

    thanks,
    Jerry
    • Marked as answer by V Karthik Wednesday, February 17, 2010 7:28 AM
    • Unmarked as answer by V Karthik Wednesday, February 17, 2010 7:28 AM
    • Marked as answer by V Karthik Wednesday, February 17, 2010 9:29 AM
    Tuesday, February 16, 2010 7:03 AM
  • Hi Karthik,

    Yes, i should add this "new issue" in my previous post but forgot it. This is caused by the "page break at before" option. Currently, there is no option to solve it in reporting service 2005. The good news is that this is corrected in SSRS 2008.

    thanks for your understanding.
    Jerry
    • Marked as answer by V Karthik Wednesday, February 17, 2010 9:10 AM
    Wednesday, February 17, 2010 9:08 AM

All replies

  • Hi Karthik,

    Did you try using direct expression:
    Using direct scoping:
    =SUM(Fields!TotalOutStandingAmt.Value,"GroupName")


    Thanks,
    Jayesh

    Please vote for answer if it helps you.
    Friday, February 5, 2010 8:51 AM
  • Hi jayesh,

    thanks for the reply. I already tried that but still I am getting the same totals. my group2 i.e. the one that groups for the 1000 records is in the highest order of grouping followed by table1_Group1 (the one with CustomerType).

    When I change the scope to group1 , i get an error saying -The scope parameter must be set to a string constant that is equal to either the name of the containig group, the name of the containing data region or the name of a data set.

    Regards,
    Karthik
    Friday, February 5, 2010 9:04 AM
  • Hi Karthik,

    I am still confused about your question. I read your 2 threads, but i get different scenario from them, the sample you give in your another thread, the subtotals are the totals of bus group and car group and you can put the =sum(Fields!Amount.value) in the group footer cell to get them. In this thread, it seemed that the table is page break first via group-on expression =Ceiling((RowNumber(Nothing))/1000), and then grouped again by the =fields!TransportType.value, and you want to get the subgroup total based on the transporttype.So, please clarify it clearer, and let your sample make sense. If we can understand your purpose, we will do our best to provide the solution here.

    thanks for your understanding.

    Jerry
    Tuesday, February 9, 2010 9:02 AM
  • Hi Jerry,

    Thanks for the reply. The main confusion i guess has come with {Page 1} notation. I meant page break there. I will rephrase my requirements-

    As already described, our report contains a table with two sets of grouping. One group is for TransportType and other group is for inserting a page break for the records if it reaches a count of 1000. (In the illustration I took the sample as 2). Doing this I am getting individual page totals for a certain group (Bus in this case) spanning three pages followed by other group and its total (i.e. Car). I guess as I mentioned in the previous thread we can safely say the 'Car' records are in the page 4 and the Grand Total (Table Footer) at Page 5.

    What we want instead is the group total to be only at the end of the group (i.e. Page 3 - Bus Type). I am having difficulty in storing the totals across the pages and summing it at page 3 and also hiding the individual page totals.

    Hope this is clear. Please do let me know if you need any further details

    Regards,
    Karthik

    Carrying the previous Illustration-
    Current Scenario -
     
    ---------------------------------------------------------
    Transport Type|Customer Name | Amount | Order Quantity
    ---------------------------------------------------------
    Bus
                              AAA                     100         10
                              AAB                     110         10
    ---------------------------------------------------------
    Bus Total                                        210         20                          -----------Group Footer Total
    ---------------------------------------------------------
                            --Page Break--
    ---------------------------------------------------------
    Transport Type|Customer Name | Amount | Order Quantity
    ---------------------------------------------------------
    Bus
                              AAC                     150         10
                              AAD                     150         15
    ---------------------------------------------------------
    Bus Total                                        300         25                         -----------Group Footer Total
    ---------------------------------------------------------
                            --Page Break--
    ---------------------------------------------------------
    Transport Type|Customer Name | Amount | Order Quantity
    ---------------------------------------------------------
    Bus
                              AAE                      150         10
                              AAF                      150         10
    ---------------------------------------------------------
    Bus Total                                        300         20                          -----------Group Footer Total   
    ---------------------------------------------------------
                            --Page Break--

    ---------------------------------------------------------
    Transport Type|Customer Name | Amount | Order Quantity
    ---------------------------------------------------------
    Car
                              CAA                     200          10 
                              CAB                     200          10
    ------------------------------------------------------------
    Car Total                                        400          20                    ------------Group Footer Total
    ------------------------------------------------------------
                            --Page Break--

    -----------------------------------------------------------
    Grand Total                                    1210          85                   --------Table Footer
    ------------------------------------------------------------

    Expected Result -

    ---------------------------------------------------------
    Transport Type|Customer Name | Amount | Order Quantity
    ---------------------------------------------------------
    Bus
                              AAA                     100         10
                              AAB                     110         10
                                                                                     ----Group Footer supressed
                            --Page Break--
    ---------------------------------------------------------
    Transport Type|Customer Name | Amount | Order Quantity
    ---------------------------------------------------------
    Bus
                              AAC                     150         10
                              AAD                     150         15
                                                                                     ----Group Footer supressed

                            --Page Break--
    ---------------------------------------------------------
    Transport Type|Customer Name | Amount | Order Quantity
    ---------------------------------------------------------
    Bus
                              AAE                      150         10
                              AAF                      150         10
    ---------------------------------------------------------
    Bus Total                                        810         65    --Group Footer,end of records
    ---------------------------------------------------------
                            --Page Break--

    ---------------------------------------------------------
    Transport Type|Customer Name | Amount | Order Quantity
    ---------------------------------------------------------
    Car
                              CAA                     200          10 
                              CAB                     200          10
    -----------------------------------------------------------
    Car Total                                        400          20              ---Group footer Total
    ------------------------------------------------------------
                            --Page Break--
    ---------------------------------------------------------
    Transport Type|Customer Name | Amount | Order Quantity
    ---------------------------------------------------------
    ------------------------------------------------------------
    Grand Total                                    1210          85              --------Table Footer
    ------------------------------------------------------------
    Tuesday, February 9, 2010 1:53 PM
  • Hi Karthik,

    Thanks for your detailed clarification. But i still can't catch on any tricky section on this question. However, the following are the detailed steps to achieve this in SSRS 2005(I guess you are running SSRS 2005).

    1. Drag a table object onto the report body, and fill in the fields for it from the dataset. In the table footer cells under the "Amount" and "Order Quantity" columns, type the expression =SUM(Fields!Amount.value) and =SUM(Fields!OrderQuantity.value) in each cell to calculate grand total for these 2 fields. The values should be 1210 and 85 on your report. Right click the table object and select "Properties", it will pop up the table properties dialogue window, in this window, tick the "Repeat header row on each page" option in the General tab of this window. This is to repeat the table header on each page.

    2.Define the first Group(by Transport Type), right-click the detail row and select "Insert Group", type the expression =Fields!TransportType.value as the Group on expression, the group header and footer are included by default, please untick the "Include group header" option. In this group footer cells under the Amount and Order Quantity colums, type the expression =SUM(Fields!Amount.value) and =SUM(Fields!OrderQuantity.value) in each cell to calculate the "Amount" and "Order Quantity" totals of "Bus" and "Car", the total values should be 810 and 65,400 and 20 on your report.

    3.Define the second Group (page break per 2 rows),right-click the detail row and select "Insert Group", type the expression =(Rownumber("thefirstGroupname")-1)\2 as the group on expression, and untick the "Include group header" and "Include group footer" options. This step is just to enable page break per 2 rows for the first group detail rows.

    After the above steps, the report should be what your "Expected Result" digram is.

    Please feel free to let me know if you have more question.

    thanks,
    Jerry
    Wednesday, February 10, 2010 2:17 AM
  • Hi Jerry,

    Thanks for the response. The tricky part is that the grouping is not on 2 records instead its on 1000 records and when i place the page break group on the details it throws me an error as follows -

    "A group expression for the table ‘table2’ uses the RowNumber function with a scope parameter that is not valid. When used in a group expression, the value of the scope parameter of RowNumber must equal the name of the group directly containing the current group."

     
    Also the data represented is for illustration as the real data has 3 group levels which are already existing on a table.
    My apologies for not giving the correct data right away, as I wanted to carry forward the solution straightaway to the existing scenario. I see that with this approach I might have to do the whole report again, hence am providing a screenshot of the existing report -

    In brief here are the groups -
    table2_Group1- Fields!CustomerType.Value (Ticked -Include Group Header, Include Group Footer, Repeat Group Header)
    table2_Group2- Fields!CustomerNumber.Value (ParentGroup -  Fields!CustomerType.Value, Toggled by the same, Ticked - Include Group Footer)
    table2_Group3- Fields!DocumentNumber.Value (ParentGroup - Fields!CustomerNumber.Value, Toggled by the same)
    table2_Group4- Ceiling((RowNumber(Nothing))/1000) (this is what is causing the page totals to appear on Group1 Footer which I want to avoid and instead appear only on the end of Group1 records)

    As I said when I kept the page break grouping on details row it throws an error saying its out of scope and from searching through the forums i gather that the page break groupings order should always be on the top.

    For some reason I am not able to understand how to attach screenshot. Could you also please let me know how to attach them as I feel it will help me explain much better understanding?

    Please advice.

    Thanks you,

    Regards,
    Karthik


    Wednesday, February 10, 2010 5:13 AM
  • Hi Karthik,

    The error means that you did not specify the correct scope for the function Rownumber because its syntax is =RowNumber("Scope"). The scope string must be the groupname of the group(pagebreak)'s parent group. You can go to the group properties and find its group name in the general tab.

    According to your description on the group hierarchy, the function RowNumber's scope should be the group4's name because the group4 is the pagebreak group's parent group. Now, you need clarify that on which level you want to calculate the subtotal if you have more question. But in my opnion, you just need to type the sum expression in the corresponding footer cells to do this since the subtotals are for the group.

    thanks,
    Jerry
    Wednesday, February 10, 2010 9:18 AM
  • Hi Jerry,

    Few clarifications. Firstly there is no parent group for the pagebreak and its a group with only 'Page Break at End' specified. This is splitting my data into several pages and on each page i am getting the page totals which I want to avoid.

    Here is my sample data for analysis-

    'Person' and 'Total Person' (in Bold) refers to the first group - CustomerType header and footer respectively.
    CustomerNumber 123 (in italics) for instance refers to the second group - CustomerNumber with footer and sub totals are displayed here.
    Underlined values refer to the Detail Row.
    Finally, the Grand Total refers to the Table Footer with overall summary.

    My task is to supress individual page totals that is getting displayed for a particular group, in this case Person and to display the total only at the page 3.
    table2_Group4 is another group on the table with the value =Ceiling((RowNumber(Nothing))/1000) value. Now when I change the scope to any of the above i.e. Group3, Group2, or Group1 I am getting the error.

    Please let me know if you need further information. I am getting the right footer totals for each group and the task now is to supress and only display at the end of the group for which I am facing the issue. There are many other fields such as Days30, Days60,Days90 etc and am only showing here Total outstanding.

    Illustration-
    ________________________________________________________________________________
    Customer Type | Customer Number| Customer Name| Document No.| Total Outstanding
    ________________________________________________________________________________

    -----------
    --Person
    -----------
                             123                      ABC                    1579                   200
                             123                      ABC                    1500                   800                                      
                             
                          --123                      ABC                                          1000
                          +127                      DEF                                           2000
                          +....                       ....                                             ...... 
                          +....                       ....                                             ...... 
                          +....                       ....                                             ......
                          +128                      GHI                                           3000
    --------------------------------------------------------------------------------------------------
    Total Person                                                                                  6000
    --------------------------------------------------------------------------------------------------
                                               -->Page Break<--
    _____________________________________________________________________________
    Customer Type | Customer Number| Customer Name| Document No.| Total Outstanding
    _____________________________________________________________________________
    -----------
    --Person
    -----------
                          +129                      JKL                                           2000
                          +....                       ....                                             ...... 
                          +....                       ....                                             ...... 
    --------------------------------------------------------------------------------------------------
    Total Person                                                                                  2000
    --------------------------------------------------------------------------------------------------
                                               -->Page Break<--
    _____________________________________________________________________________
    Customer Type | Customer Number| Customer Name| Document No.| Total Outstanding
    _____________________________________________________________________________
    -----------
    --Person
    -----------
                          +129                      MNO                                          2000
                          +....                       ....                                             ...... 
                          +....                       ....                                             ...... 
    --------------------------------------------------------------------------------------------------
    Total Person                                                                                  2000
    --------------------------------------------------------------------------------------------------
    -----------
    --Electronic
    -----------
                          +901                     EE0                                            6000
                          +....                       ....                                             ...... 
                          +....                       ....                                             ...... 
    --------------------------------------------------------------------------------------------------
    Total Electronic                                                                              6000
    --------------------------------------------------------------------------------------------------
    ________________________________________________________________________
    Grand Total                                                                               16000
    ________________________________________________________________________

    Regards,
    Karthik
    Note- Could you also please let me know how to paste an image in the post? It would have helped my issue a lot rather than manual typing and formatting. I have seen such images in other thread. Thanks.

    Wednesday, February 10, 2010 11:44 AM
  • Hi Karthik,

    Why not calculate the subtotal in the footer cells of  the instance group "Person" and "Electronic", since you have setuped a group on "Customer Type"? Do you want to enable the page break for the overall table data or any group data on earth? If you are still not able to solve this problem, to close this issue as soon as possible, please send the following contents through email to us at this email address sqltnsp AT microsoft DOT com :

    Subject: Group total in group footer with page break.
    Attachment(don't exceed 2 MB in size): the example data in excel.
    Body: This thread link firstly, then please phrase your requirements with the example data in excel you provided in the attachment. I would suggest you use the style "list items" to state those information. Btw, what's the SSRS version you are running? In a word, the better, the clearer you write the requirements.

    Once i get this email from you, i will do my best to build this report as you requested in my test environment and sent the rdl file and its detailed steps to your emailbox.

    thanks,
    Jerry
    Thursday, February 11, 2010 2:51 AM
  • Hi Jerry,

    As requested I have sent the mail to the given address with the requisite details.

    Firstly, we are indeed calcluating the subtotals in the footer cells as seen below.

    ------------------------------------------------------------------------------------------------
    Total Person                                                                                  6000
    ------------------------------------------------------------------------------------------------

    The page break functionality is assigning the page totals for the particular group. Also, what we need is the page break for overall data in the report.

    Thanks a lot.

    Regards,
    Karthik
    Thursday, February 11, 2010 6:09 AM
  • Hi Karthik,

    I have received your email, and understood the issue. The issue only has something to do with "table2_Group4" and "table2_Group1". To benefit other community users who might encounter this issue, i am going to rehash this case briefly.

    Scenario:
    There is a table with a amount of data and its columns include"CustomerID","CustomerType" and "Totaloutstanding". Now, run SSRS 2005 to report this table data. On this report, 
    1. In the report dataset, these data are ordered by "CustomerID", one CustomerType has one CustomerID, the CustomerID values like 1,2,3......  
    2. Insert a Table control to host these data,
    3. The data on every page of this report has only less than or equal 1000 rows. To achieve this, insert a group for the detail rows with group-on expression =(RowNumber(Nothing)-1)\1000. Assuming this group is Group1.
    4. The data on every page of this report need to be grouped by "CustomerType". To achieve this, insert a group under the Group1 for the detail rows with group-on expression =Fields!CustomerType.value, and tick these options "Include group header","Include group footer" and "Repeat group header". Assuming this group is Group2.

    5(?). How to display the subtotal based on CustomerType at the end of each CustomerType series?  Note that one CustomerType might be span multiple pages by Group1, and one page(Group1 instance) might have multiple Group2 instance.

    Solution:(for the above 5th requirement: how to display the subtotal based on CustomerType at the end of each customerType series)
    In my opnion, there are 2 main steps, one is how to calculate the subtotal based on the "CustomerType", and the other is how to hide the Group2 footer rows if they are not at the end of each CustomerType series.
    1. The possible alternatives for how to calculate the subtotal based on the "CustomerType", there are 2 options you can follow:
        1)Use semi hard-code: In the Group2 footer cell, type this expression =Switch(Fields!Customer.Value="Person",SUM(IIF(Fields!Customer.Value="Person",Fields!Totaloutstanding.Value,0),"StatisticTbl"),Fields!Customer.Value="Company",SUM(IIF(Fields!Customer.Value="Company",Fields!Totaloutstanding.Value,0),"StatisticTbl"),Fields!Customer.Value="Electronic",SUM(IIF(Fields!Customer.Value="Electronic",Fields!Totaloutstanding.Value,0),"StatisticTbl")) . Note that "StatisticTbl" is the report dataset name, and "Person","Company" and "Electronic" are the "CustomerType" examples.
        2)Use the subreport to do this:Create a new report, dataset:select CustomerType,sum(Totaloutstanding) as subtotal
                                                                                                from StatisticTbl
                                                                                                where C
    ustomerType= @cust
                                                                                                group by customerType
                                                                      report parameter:name->cust, datatype->string
                                                                      report body:drag a table control and only one detail cell via deleting other cells, and put the =Fields!subtotal in this cell. Adjust this report page size to fit on one cell of the main report Group2 footer. On the main report, in the cell displaying the subtotal of "CustomerType" of Group2 footer, insert a Subreport control to reference this newly created report and set this subreport parameter "cust" point to =Fields!CustomerType.value.
    2. After implementing any option of the first step, the subtotal based on the "CustomerType" will be displayed in each Group2 footer. Now, how to hide these footer rows not at the end of the "CustomerType" series? Click the handle of the Group2 footer row to select the entire row, and locate its Hidden property under Visibility note in the Properties box, then type the following expression to hide the footers not at the end of each "CustomerType":

    =IIF(Runningvalue(Fields!CustomerType.Value,countdistinct,"Group1")=1,IIF(Count(Fields!CustomerType.Value,"Group2")=1000,true,false),false)

    thanks,
    Jerry
    • Marked as answer by V Karthik Friday, February 12, 2010 3:27 PM
    • Unmarked as answer by V Karthik Monday, February 15, 2010 4:44 AM
    • Marked as answer by V Karthik Wednesday, February 17, 2010 9:11 AM
    Friday, February 12, 2010 2:58 AM
  • Hi Jerry,

    Thanks a ton for such detailed reply. I have just one query before I start my implementation. In the steps for reproduction you have mentioned the groups being on detail row. The current groupings for my scenario are on table level. Will the solution still suffice in this case? I will follow the suggestions and would get back to you if I face any problems.

    Thank again,

    Regards,
    Karthik


    Friday, February 12, 2010 7:00 AM
  • Hi Karthik,

    Yes, any group defined on the report table is for data (detail row), not for the table self. Please implement my solution to achieve your requirement, it should be workable because i had did a test in my SSRS enviroment.

    thanks,
    Jerry
    Friday, February 12, 2010 10:02 AM
  • Hi Jerry,

    I just can't thank you enough! You are a saviour. It works like a charm..

    A mighty thanks. :)

    Cheers,
    Karthik
    Friday, February 12, 2010 3:27 PM
  • Hi Jerry,

    I have run into problem again. The actualy report that we require needs to be exported to excel sheet and though your second approach worked perfectly for me for report viewing, I can't use it because of SSRS 2005 limitation.

    Coming to the first approach, I see that when I use the expression with actual group values I get the following error continously. I tried with all possible conversions CDbl, CInst,CLng functions. -

    [rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox9’ uses an aggregate function on data of varying data types.  Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

    Please advice. Actually our labels on the report do vary with country and so do the values 'Person' , 'Invoicing', 'Electronic' depending on the language. Is there a possiblity to make these values dynamic for comparision?

    Thank you.

    Regards,
    Karthik

    Monday, February 15, 2010 4:44 AM
  • Hi,

    Please post the expression you used here.

    thanks,
    Jerry
    Monday, February 15, 2010 10:02 AM
  • here is the expression I used. I have changed the values with CInt, CDbl too but no avail.

    =Switch(Fields!CustomerType.Value="Person",SUM(IIF(Fields!CustomerType.Value="Person",CLng(Fields!TotalOutstanding.Value),0),"OverdueStatsDetails"),Fields!CustomerType.Value="Company – traditional invoicing",SUM(IIF(Fields!CustomerType.Value="Company – traditional invoicing",CLng(Fields!TotalOutstanding.Value),0),"OverdueStatsDetails"),Fields!CustomerType.Value="Company – electronic invoicing",SUM(IIF(Fields!CustomerType.Value="Company – electronic invoicing",CLng(Fields!TotalOutstanding.Value),0),"OverdueStatsDetails"))
    Thank you,
    Karthik
    Monday, February 15, 2010 10:10 AM
  • Hi,

    Please try the following expression:

    =Switch(Fields!CustomerType.Value="Person",SUM(IIF(Fields!CustomerType.Value="Person",CDbl(Fields!TotalOutstanding.Value),CDbl(0)),"OverdueStatsDetails"),Fields!CustomerType.Value="Company – traditional invoicing",SUM(IIF(Fields!CustomerType.Value="Company – traditional invoicing",CDbl(Fields!TotalOutstanding.Value),CDbl(0)),"OverdueStatsDetails"),Fields!CustomerType.Value="Company – electronic invoicing",SUM(IIF(Fields!CustomerType.Value="Company – electronic invoicing",CDbl(Fields!TotalOutstanding.Value),CDbl(0)),"OverdueStatsDetails"))

    if this still doesn't help, please check the data type of this field in the underlying database, and the detail cell textbox format property of this field on your report.  

    thanks,
    Jerry
    • Marked as answer by V Karthik Wednesday, February 17, 2010 7:28 AM
    • Unmarked as answer by V Karthik Wednesday, February 17, 2010 7:28 AM
    • Marked as answer by V Karthik Wednesday, February 17, 2010 9:29 AM
    Tuesday, February 16, 2010 7:03 AM
  • Hi Jerry,

    Everything is just perfect now. Thanks a zillion! There is just however one small problem, when I export the report to excel the total i.e. the Table footer is appearing in a separate sheet rather than the end of the report even though the third page (in my case with around 2500 records )does not exceed the 1000 limit.

    Is there any workaround for this?

    Thanks again.

    Regards,
    Karthik
    Wednesday, February 17, 2010 7:24 AM
  • Hi Karthik,

    The issue that the table footer is appearing in a separate sheet is caused because of the "page break at end" of the pagebreak group. By default, we can't define an expression to prevent page break with table footer row. But in my mind you might use the group header for each page rather than the table hear, if so, please change the pagebreak group with "page break at before", not "page break at end", this might make the table footer row appear in the third page.

    thanks,
    Jerry
    Wednesday, February 17, 2010 7:55 AM
  • Hi Jerry,

    From what I understand I need to remove the table header. Make use of the group header of the "Customer Type" and use it as Table header. After which I need to change the setting of the PageBreak grouping which is table2_Group4 in my case as "Page Break at Start" rather than "Page Break At End". 

    I did that and I see that though the report footer is now coming along with the records the first page is completely blank and my whole table begins to appear only on the next page.

    Please advice.

    Regards,
    Karthik
    Wednesday, February 17, 2010 8:13 AM
  • Hi Karthik,

    Yes, i should add this "new issue" in my previous post but forgot it. This is caused by the "page break at before" option. Currently, there is no option to solve it in reporting service 2005. The good news is that this is corrected in SSRS 2008.

    thanks for your understanding.
    Jerry
    • Marked as answer by V Karthik Wednesday, February 17, 2010 9:10 AM
    Wednesday, February 17, 2010 9:08 AM
  • Thanks Jerry for the prompt response. They have been immensely helpful.

    Regards,
    Karthik
    Wednesday, February 17, 2010 9:10 AM

  •     2)Use the subreport to do this:Create a new report, dataset:select CustomerType,sum(Totaloutstanding) as subtotal
                                                                                                from StatisticTbl
                                                                                                where C
    ustomerType= @cust
                                                                                                group by customerType
                                                                      report parameter:name->cust, datatype->string
                                                                      report body:drag a table control and only one detail cell via deleting other cells, and put the =Fields!subtotal in this cell. Adjust this report page size to fit on one cell of the main report Group2 footer. On the main report, in the cell displaying the subtotal of "CustomerType" of Group2  footer, insert a Subreport control to reference this newly created report and set this subreport parameter "cust" point to =Fields!CustomerType.value.
    2. After implementing any option of the first step, the subtotal based on the "CustomerType" will be displayed in each Group2 footer. Now, how to hide these footer rows not at the end of the "CustomerType" series? Click the handle of the Group2 footer row to select the entire row, and locate its Hidden property under Visibility note in the Properties box, then type the following expression to hide the footers not at the end of each "CustomerType":

    =IIF(Runningvalue(Fields!CustomerType.Value,countdistinct,"Group1 ")=1,IIF(Count(Fields!CustomerType.Value,"Group2 ")=1000,true,false),false)

    thanks,
    Jerry

    Hi Jerry,

    Your solution help me alot, but in my scenario, i have to use the subreport because my "CustomerType" that can expand. In your posted above, u create a subreport with the dataset that query the dataset (StatisticTbl) from main report. how can we do that? i try but no hope :(.

    Best Regards,

    Jaychen

     

    Tuesday, July 6, 2010 4:29 AM
  • Hi Jaychen,

    I can't understand your question completely. The purpose of creating the subreport is to calculate the total amount of each customertype, then display the total for each customer type through passing the customer type on the  main report. The query i provided is able to do it. If you have more question, you can give more decription.

    thanks,
    Jerry

    Tuesday, July 6, 2010 6:56 AM
  • Hi Jerry,

    That just a little confuse about your idea, it's all clear now :). sorry to waste your time.

    Regards,

    Jaychen

     

    Tuesday, July 6, 2010 7:23 AM

  • 3. The data on every page of this report has only less than or equal 1000 rows. To achieve this, insert a group for the detail rows with group-on expression =(RowNumber(Nothing)-1)\1000 . Assuming this group is Group1 .
    4. The data on every page of this report need to be grouped by "CustomerType". To achieve this, insert a group under the Group1 for the detail rows with group-on expression =Fields!CustomerType.value , and tick these options "Include group header","Include group footer" and "Repeat group header". Assuming this group is Group2 .

    5(?). How to display the subtotal based on CustomerType at the end of each CustomerType series?   Note that one CustomerType might be span multiple pages by Group1, and one page(Group1 instance) might have multiple Group2 instance.

    Solution: (for the above 5th requirement: how to display the subtotal based on CustomerType at the end of each customerType series)
    In my opnion, there are 2 main steps, one is how to calculate the subtotal based on the "CustomerType", and the other is how to hide the Group2 footer rows if they are not at the end of each CustomerType series.
    1. The possible alternatives for how to calculate the subtotal based on the "CustomerType", there are 2 options you can follow:
        1)Use semi hard-code: In the Group2 footer cell, type this expression =Switch(Fields!Customer.Value="Person",SUM(IIF(Fields!Customer.Value="Person",Fields!Totaloutstanding.Value,0),"StatisticTbl"),Fields!Customer.Value="Company",SUM(IIF(Fields!Customer.Value="Company",Fields!Totaloutstanding.Value,0),"StatisticTbl"),Fields!Customer.Value="Electronic",SUM(IIF(Fields!Customer.Value="Electronic",Fields!Totaloutstanding.Value,0),"StatisticTbl")) . Note that "StatisticTbl" is the report dataset name, and "Person","Company" and "Electronic" are the "CustomerType" examples.
        2)Use the subreport to do this:Create a new report, dataset:select CustomerType,sum(Totaloutstanding) as subtotal
                                                                                                from StatisticTbl
                                                                                                where C
    ustomerType= @cust
                                                                                                group by customerType
                                                                      report parameter:name->cust, datatype->string
                                                                      report body:drag a table control and only one detail cell via deleting other cells, and put the =Fields!subtotal in this cell. Adjust this report page size to fit on one cell of the main report Group2 footer. On the main report, in the cell displaying the subtotal of "CustomerType" of Group2  footer, insert a Subreport control to reference this newly created report and set this subreport parameter "cust" point to =Fields!CustomerType.value.

    Hi Jerry,

    I have the same problem like V.Karthik.

    1. I use the table control to host data

    3. The data on every page of this report has only less than or equal 100 rows. To achieve this, insert a group for the detail rows with group-on expression =Ceiling((RowNumber(Nothing)) / 100) . Assuming this group is Group1 .

    2. The data on every page of this report need to be grouped by "Month" . To achieve this, I insert the MonthGroup under the Group1 for the detail rows with group-on expression =Fields!Month.value (Group1 -> MonthGroup -> DetailRow).

    4. Display the subtotals based on Month at the end of each Month.   Note that one Month might be span multiple pages by Group1.

    I try the solution that you present above:

    =Switch(Fields!Month.Value="01",SUM(IIF(Fields!Month.Value="01",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="02",SUM(IIF(Fields!Month.Value="02",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="03",SUM(IIF(Fields!Month.Value="03",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="04",SUM(IIF(Fields!Month.Value="04",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="05",SUM(IIF(Fields!Month.Value="05",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="06",SUM(IIF(Fields!Month.Value="06",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="07",SUM(IIF(Fields!Month.Value="07",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="08",SUM(IIF(Fields!Month.Value="08",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="09",SUM(IIF(Fields!Month.Value="09",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="10",SUM(IIF(Fields!Month.Value="10",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="11",SUM(IIF(Fields!Month.Value="11",Fields!Completed_Interview.Value,0),"DataSet1"), Fields!Month.Value="12",SUM(IIF(Fields!Month.Value="12",Fields!Completed_Interview.Value,0),"DataSet1"))

    But i get the wrong result for Month subtotal :(.

    Any suggestion for me?

    Best Regards,

    Longwei

     

    Tuesday, August 3, 2010 3:12 AM