Question

Photo of Michael Garrison

0

Dynamic reports

3

In the "Taking Off With Reporting" documentation, an example of a report is given of "a list of attendees over the age of 18 who began attending within the last two years", but the execution of that particular report is never shown. Specifically, I'm wondering about the "Began attending in the last two years" criteria- is there a way that I haven't found to set a date reference like that so that it's always up-to-date? The only way I can find to do that is to manually set a date two years ago - but if I run that report again next year, it's going to (obviously) show people who began attending within the last THREE years. I'm trying to find a way that I can specify "last two years" in the data view so that it's a constantly-moving window.

For instance, we'd like to have reports for things like "adults whose first visit was in the last month". Do we have to go and manually set the date in the data view every month before running the report, or is there a way (liquid?) to set the parameter to be "the 1st of last month"?

On a related theme, I'm trying to develop contact list reports for our small groups, since the group viewer only shows name and e-mail addresses. Do I have to create a separate data view and report for each small group manually, or is there some way to allow selection of the desired small-group at report time? I've been excited at the prospect of flexible/reusable data views for reports, but as I'm digging into them it appears to me the only reuse is in being able to include another pre-defined set of hard-coded filters - useful, but still pretty rigid. I feel like I'm missing something, but I don't know what. Is there another way to solve what I'm trying to do here?

  • Photo of Rock RMS

    0

    McKinley v3.0 will include an enhancement to allow filtering date fields by a relative date offset. For example, a First Visit date within last 30 days would look like this...

    datefilter.png

    For the dynamic contact list, you can use the HTML Content Detail block in combination with the Dynamic Data List block. We temporarily added an example of this to the Rock Demo site. The Group Viewer page has a 'Contact List' button below the member list which is an HTML Content Detail block that evaluates the querystring to build a link to a new Contact List page which contains a dynamic data block that evaluates query string parameter for it's sql parameter. The "Dynamic Data & Workflows" video walks through how to do this also.

     

    • Michael Garrison

      Michael Garrison

      That enhancement is exactly what will be useful- thanks!


      I'm seeing the light now that everything on the internal pages doesn't have to work within the existing tools (Reporting, etc) but using dynamic blocks and new pages we can expand on what's available. THANKS for the working example in the rocksolidchurchdemo site, but I'm having two issues with it-



      1. The GroupId parameter from the address bar is incorrectly reporting itself on the button calling {{ PageParameter.GroupId }}. In my case when I'm viewing ~/page/113?GroupId=4096&ExpandedIds=4095%2C5905 the button links to ~/page/360?GroupId=41.

      2. If I go to /page/360?GroupId=4096, the list of members is correct but only the top member has a work phone listed- everyone else's phone numbers are blank.
        I'll keep poking things to see if I can get them working but I'll be grateful to anyone who can spot my issues...
        Thanks!

      • Michael Garrison

        Michael Garrison

        The answer to my first issue was that I forgot - again - to set the HTML block cache duration to 0. Easy fix. Now I'll work on the second issue.

        • Michael Garrison

          Michael Garrison

          And the answer to the second issue is that it doesn't look like data that we imported using Excavator has "Formatted Number" - just "Number" is set in the [PhoneNumber] table and "Formatted Number" is null. So the provided demo code is (of course) perfect and the issue is on my side.


          In case someone else finds themselves with the same issue, replace lines 5-7 with the following:
          ( SELECT ISNULL([NumberFormatted],[Number]) FROM [PhoneNumber] WHERE [PersonId] = P.[Id] AND [NumberTypeValueId] = 13 ) AS [Home],
          ( SELECT ISNULL([NumberFormatted],[Number]) FROM [PhoneNumber] WHERE [PersonId] = P.[Id] AND [NumberTypeValueId] = 12 ) AS [Cell],
          ( SELECT ISNULL([NumberFormatted],[Number]) FROM [PhoneNumber] WHERE [PersonId] = P.[Id] AND [NumberTypeValueId] = 136 ) AS [Work],

        • Photo of Cody Tindall

          0

          Michael,

          I had to create somthing like this personally just for preparing our bulletins. I setup a dynamic data block on a blank page though because I couldn't get what I needed from the existing report section. If you drop just the SQL query in it will display like a grid with the export option but it wouldn't be too hard to format the results if you needed it to be a little less.. 'Excel like'.

          **Copy this into the SQL query section**

          DECLARE @YearsGoneBy INT

          SET @YearsGoneBy = 2       
          -- Query to find person(s) created in the last 2 years

          SELECT  [Id], [LastName], [NickName], [CreatedDateTime]
          FROM    [Person]
          WHERE YEAR(CreatedDateTime) >= (YEAR(GETDATE()) - @YearsGoneby)

          **Stop copying**

          Let me know if you need any help setting up the block!

          • Michael Garrison

            Michael Garrison

            Thanks- you helped me realize that extending the built-in infrastructure by creating blank sub-pages is going to be the answer to a lot of our needs going forward. Have a great weekend!

          • Photo of Michael Garrison

            0

            For posterity's sake (since the example posted on the rocksolidchurchdemo site is gone), here is the Dynamic Data List Block query, which was put on a new blank page to create the dynamic contact list:

            SELECT
    P.[Id],
    P.[NickName] + ' ' + P.[LastName] AS [Name],
    P.[Email] AS [Email],
    ( SELECT [NumberFormatted] FROM [PhoneNumber] WHERE [PersonId] = P.[Id] AND [NumberTypeValueId] = 13 ) AS [Home],
    ( SELECT [NumberFormatted] FROM [PhoneNumber] WHERE [PersonId] = P.[Id] AND [NumberTypeValueId] = 12 ) AS [Cell],
    ( SELECT [NumberFormatted] FROM [PhoneNumber] WHERE [PersonId] = P.[Id] AND [NumberTypeValueId] = 136 ) AS [Work],
 ( SELECT TOP 1 ISNULL([Street1],'') + ' ' + ISNULL([Street2],'') + ' ' + ISNULL([City],'') + ', ' + ISNULL([State],'') + ' ' + ISNULL([PostalCode],'')
  FROM [GroupMember] FM                   -- Family Member
  INNER JOIN [Group] F ON F.[Id] = FM.[GroupId] AND F.[GroupTypeId] = 10       -- Family
  INNER JOIN [GroupLocation] FL ON FL.[GroupId] = F.[Id] AND FL.[GroupLocationTypeValueId] = 19 -- Home Location
  INNER JOIN [Location] L ON L.[Id] = FL.[LocationId]            -- Address
  WHERE FM.[PersonId] = P.[Id]
 ) AS [Address]
FROM [GroupMember] M
INNER JOIN [Person] P ON P.[Id] = M.[PersonId]
WHERE [GroupId] = @GroupId
AND M.[GroupMemberStatus] = 1                   -- Active

            You'll want to hide the Id column, set Parameters to GroupId=0 and set the Selection URL to ~/Person/{Id}

            Now add a button using HTML to your group viewer page which points to ~/page/###?GroupId={{ PageParameter.GroupId }} , replacing ### with whatever is the PageId of the page you put the dynamic block on.

            edit 7/23/2015: added ISNULL() wrappers around each of the address columns- SQL Server sets the entire string to NULL if you try to concatenate with a null value- which many of our Street2 values were.