Previous Sub Groups =================== This SQL Script will find **Previous Members** of an Involvement and will display the sub groups they were in when they were Current members of the Involvement. Copy the :ref:`script below ` and paste into a new Sql Script file in your database. Name the file ``PreviousSubGroups``. See :doc:`../CreateSqlScript`. .. note:: This will not return any current members, so if you mistakenly drop members, you will want to run this report prior to adding them back into the involvement. You have several options for running this script: #. After saving the script in your database, run the script from Special Content, then add the Involvement ID at the end of the URL. Press ``Enter`` to run it again. This will open the report in your browser and you can select `Download as Excel`. #. Use the URL **mychurch**.tpsdb.com/runScript/PreviousSubGroups/**216** where **mychurch** is your church's name and **216** is the Involvement ID from your database. This one opens in Excel. #. You can choose to `Add to Menu` after running it initially from `Special Content`. Then it will display under `Reports` in the Main Menu. After running it there, just add the Involvement ID at the end and press ``Enter``. Sample Excel Report ------------------- Notice that the 2 sub groups are on separate lines inside the cell. Select `Alignment > Wrap Text` to make that happen. .. figure:: https://i.tpsdb.com/2017-09-25_09-57-07.png :target: # | Sample Browser Report --------------------- .. figure:: https://i.tpsdb.com/2017-09-25_09-55-50.png :target: # | .. _PrevSubGroupsCode: Code ---- .. code-block:: sql /* To report on sub groups of Involvement Members who were dropped: Use the following URL mychurch.tpsdb.com/RunScript/PreviousSubGroups/216 Where 216 is the Involvement ID#. After exporting in Excel, format the SmallGroups Column as Wrap Text, then the small groups will be shown on separate lines. You can also run the report Special Content > SQL Scripts, then add the Involvement ID# at the end of the URL. At that point you can Download as Excel. */ SELECT p.Name, p.PeopleId, e.SmallGroups FROM dbo.EnrollmentTransaction e JOIN dbo.People p ON p.PeopleId = e.PeopleId WHERE OrganizationId = @p1 AND SmallGroups IS NOT NULL | | +--------------------+------------------+ | **Latest Update** | **01/28/2022** | +--------------------+------------------+ Updated script URL and file name.