none
interactive sorting not working on table

    Question

  • Hi I am having trouble with interactive sorting in the SQL Server Reporting Services 2008 BIDS Report Designer.  I have a table which is representation of a query performing a 1 to many Join.  I have repetition of the nine columns from the "one" side and three columns from the "many" side  after many attempts I was finally able to get the table to show up like I wanted by setting a group on the id of one side and setting all the columns from the one side to hide duplicates and displaying the details on the same line as the group entry. so it looks like this

    ID     First_Name    Last_Name    Location             IS Admin     Locations Admin For           Status

    1          Smith             Tom            Pearl Harbor       Yes              Thomson's creek              Active

                                                                                   Yes                  Baylor                          Inactive

    2           Jones            Bill              Norfolk                No                   None                            None

    3          Brown            Tim             Little Creek         Yes                  Little Creek                  Active

                                                                                   Yes                  Mayport                       Active

     

    When I set interactive sore on the columns "ID" worked as expected but nothing happened with the other columns.  I read what was purported to be a solution at http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/63ad79d8-1827-496f-bb7c-40a4ae65ee23  but got the error;

    "The tablix ‘table1’ has an invalid SortTarget “Group1”. The scope must be the current scope, a peer scope, which is a data region, or an ancestor scope. If the current scope and target scope are groupings, then both scopes must be along the same axis."  when I tried to implement it.   

    I tried googling the answer, but every search result that showed promise returned "Internet Explorer cannot display this page" error.

    So I am at a loss.  Can someone out there give me some advice on how to proceed.


    Edward R. Joell MCSD MCDBA

    • Edited by joeller Friday, December 9, 2011 7:53 PM
    Friday, December 9, 2011 7:50 PM

Answers

  • Hi,

    Kindly try below steps: (SQL 2008 BIDS)

    1. Right Click on ID column text box, where the ID Label is written (Header) -> Textbox properties -> Interactive Sort -> Sort -> Groups -> Group1, Sort by -> ID
    2. Right Click on First_Name column text box, where the First_Name Label is written (Header) -> Textbox properties -> Interactive Sort -> Sort -> Details Rows, Sort by -> First_Name
    3. Follow Step 2 for all other columns.

    I hope this will help you.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Marked as answer by joeller Monday, April 9, 2012 5:16 PM
    Monday, December 12, 2011 2:30 PM
  • Here's what I found. I did the same thing on another report Asim wanted me to do and it worked. In the interactive sorting tab for the columns that are grouped. Select radio button for groups for the group by fields, and then slected the name of the group by group in the drop down associated with that button. Then I selected the field that was being grouped by in the drop down for "sort by" Then in the columns that were not grouped, I selected the radio button for "detail rows" NOT the one for "groups". Then I selected the field that occupied those columns as the sort by field. I did not check the check box for "Apply this sorting to all groups and data region in:" But in the report that spawned this forum inquiry, there was never any fix. I suspect it had to do with the issue of the fact that each detail item only sorts within the next higher group and I was using groups to eliminate duplicates for all the fields except for the last two fields that had to be examined. Therefore any sort done by activity location didn't show any change for the report as a whole or for the group. However the last two fields should have shown something but now I can get them to display properly any more and the production report was changed to add a new need. So I have no longer any benefit to be gained from this forum inquiry. Since Asim's reply worked on my new report, I will give him credit for an answer.

    Update:  I figured it out.  If you want detail column to update across the group divide,  you have to set the radio button for groups, even though this column is not a member of the groupby fields.  Then indicate your group, but then select the list field that you want to show the sort for, NOT the one for the group.


    Edward R. Joell MCSD MCDBA


    • Edited by joeller Monday, April 9, 2012 5:34 PM Update
    • Marked as answer by joeller Monday, April 9, 2012 5:34 PM
    Monday, April 9, 2012 5:14 PM

All replies

  • Similar thread:

    http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/63ad79d8-1827-496f-bb7c-40a4ae65ee23

    Hope this helps.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Marked as answer by Lola WangModerator Monday, December 12, 2011 7:13 AM
    • Unmarked as answer by joeller Monday, December 12, 2011 1:56 PM
    Saturday, December 10, 2011 10:03 AM
  • If you had read the thread you would have seen that the last entry in it was from me and it said that following those instructions produced the following error.

    "The tablix ‘table1’ has an invalid SortTarget 'Group1'. The scope must be the current scope, a peer scope, which is a data region, or an ancestor scope. If the current scope and target scope are groupings, then both scopes must be along the same axis."

     

    Ms Wang please refrain from marking something as answered without reading the answer.  I believe that it is my right to make that determination.

     

     


     

    Edward R. Joell MCSD MCDBA


    • Edited by joeller Monday, December 12, 2011 2:01 PM
    Monday, December 12, 2011 1:57 PM
  • Hi,

    Kindly try below steps: (SQL 2008 BIDS)

    1. Right Click on ID column text box, where the ID Label is written (Header) -> Textbox properties -> Interactive Sort -> Sort -> Groups -> Group1, Sort by -> ID
    2. Right Click on First_Name column text box, where the First_Name Label is written (Header) -> Textbox properties -> Interactive Sort -> Sort -> Details Rows, Sort by -> First_Name
    3. Follow Step 2 for all other columns.

    I hope this will help you.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    • Marked as answer by joeller Monday, April 9, 2012 5:16 PM
    Monday, December 12, 2011 2:30 PM
  • ID field set to sort on group1.  First name set to sort on table1_Details_group Last_name set to sort on table1_Details_group.  Only available selections in dropdown for interactive sort 

    Dataset1, Group1, table1, table1_Details_Group

    It produces error

    [rsInvalidTargetScope] The tablix ‘table1’ has an invalid SortTarget “table1_Details_Group”. The scope must be the current scope, a peer scope, which is a data region, or an ancestor scope. If the current scope and target scope are groupings, then both scopes must be along the same axis.

    [rsInvalidTargetScope] The tablix ‘table1’ has an invalid SortTarget “table1_Details_Group”. The scope must be the current scope, a peer scope, which is a data region, or an ancestor scope. If the current scope and target scope are groupings, then both scopes must be along the same axis.


    Edward R. Joell MCSD MCDBA
    Monday, December 12, 2011 3:04 PM
  • First name set to sort on table1_Details_group Last_name set to sort on table1_Details_group. 

    Hi,

    Do not set first name and last name to be sort on any group.

    Do you see there are two radio button as Detail Rows and Groups. When you select group then below one DDL get enabled. For these fields you should select Detail Rows.

    I have tested this with Visual Studio 2008 and SQL 2008. Unfortunately, I can not upload Image as some restriction in my network.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Monday, December 12, 2011 3:12 PM
  • I am also using SSRS 2008 and BIDS 2008.  This is what I see

    Right click on ID column header textbox. Select text box properties.  Dialog window opens.  Select Interactive Sorting tab.  Dialog bos window shows radio button for groups selected.  Group in textbox shows Group1.  Under sortby select ID field from dropdown.  checkbox for apply sort to all groups and data regions in: selected.  drop down shows group1 selected. 

    Next Field Last_Name field  text box properties, Interactive sort.  Details radio button selected.  textbox below disabled.  Sort by field Last_Name selected from drop down.  checkbox for apply sort to all groups and data regions in: selected.  Dropdown shows table1_Details_Group selected. 


    Edward R. Joell MCSD MCDBA
    • Edited by joeller Monday, December 12, 2011 3:25 PM spelling
    Monday, December 12, 2011 3:23 PM
  • checkbox for apply sort to all groups and data regions in: selected.  Dropdown shows table1_Details_Group selected.

    Hi,

    I have tested with Un checking this checkbox.

    Either you can Uncheck it or select Tablix1 or name of your table.

    For both, ID and First Name.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Monday, December 12, 2011 3:34 PM
  • Both unchecking it and selecting table1 result in the situation that started this thread.  The sort on the id field works fine.  The other sorts do nothing.
    Edward R. Joell MCSD MCDBA
    Monday, December 12, 2011 4:40 PM
  • Doesn't anyone have a solution to this issue?  I would find it astonishing that MS would neglect to provide SSRS with sothing that can be done easily in MS Access.
    Edward R. Joell MCSD MCDBA
    Wednesday, December 14, 2011 2:25 PM
  • This is one of those situation where Expert's Exchange has it all over MSDN forums.  If this were experts exchange then either a moderator would have assigned an expert to handle the question or I would be able to ask a moderator to do that.
    Edward R. Joell MCSD MCDBA
    Thursday, December 29, 2011 4:52 PM
  • Here's what I found. I did the same thing on another report Asim wanted me to do and it worked. In the interactive sorting tab for the columns that are grouped. Select radio button for groups for the group by fields, and then slected the name of the group by group in the drop down associated with that button. Then I selected the field that was being grouped by in the drop down for "sort by" Then in the columns that were not grouped, I selected the radio button for "detail rows" NOT the one for "groups". Then I selected the field that occupied those columns as the sort by field. I did not check the check box for "Apply this sorting to all groups and data region in:" But in the report that spawned this forum inquiry, there was never any fix. I suspect it had to do with the issue of the fact that each detail item only sorts within the next higher group and I was using groups to eliminate duplicates for all the fields except for the last two fields that had to be examined. Therefore any sort done by activity location didn't show any change for the report as a whole or for the group. However the last two fields should have shown something but now I can get them to display properly any more and the production report was changed to add a new need. So I have no longer any benefit to be gained from this forum inquiry. Since Asim's reply worked on my new report, I will give him credit for an answer.

    Update:  I figured it out.  If you want detail column to update across the group divide,  you have to set the radio button for groups, even though this column is not a member of the groupby fields.  Then indicate your group, but then select the list field that you want to show the sort for, NOT the one for the group.


    Edward R. Joell MCSD MCDBA


    • Edited by joeller Monday, April 9, 2012 5:34 PM Update
    • Marked as answer by joeller Monday, April 9, 2012 5:34 PM
    Monday, April 9, 2012 5:14 PM
  • (Since when have you no longer been able to copy and paste text on the forum page.  Seems rather, irritating for it own sake than accomplishing anything of value.)

    Edward R. Joell MCSD MCDBA

    Monday, April 9, 2012 5:16 PM