Question

Photo of Michael Hughes

0

Basic Financial Reports

4

I have a basic understanding of how data views and reports work, but I seen to be unable to create a basic weekly report.  Trying to find what data views I would use to create a report that exports into a spreadsheet similar to the Contribution Template Report I uploaded.  I'm assuming people are able to see the spreadsheet.  If not,  the spreadsheet shows the total for each fund by campus horizontally and then totals each specific fund vertically (combined total of all campus giving to that fund).  Not as important, but would be helpful, is that it totals horizontally by transaction type (cash, check, credit card).  Sample below if attachment doesn't show.

Campus     Fund       Cash     Check    Credit Card    TOTAL

Campus1   Alms       $1,000   $ 2,000    $ 3,000           $ 6,000

Campus2   Alms       $2,000   $ 3,000    $ 4,000           $ 9,000

Campus1  Missions $1,000   $ 2,000    $ 3,000           $ 6,000

Campus2  Missions $2,000   $ 3,000    $ 4,000           $ 9,000

 TOTAL                       $6,000  $10,000    $14,000        $30,000 

  • Photo of Daniel Hazelbaker

    0

    Hi Michael,

    I'm not sure if there is a way to do that with out of the box functionality in Rock. You can accomplish it pretty with with a Dynamic Data block and some SQL like the following though:

    SELECT
	ISNULL(MAX(C.[Name]), '') AS [Campus],
	MAX(A.[Name]) AS [Fund],
	SUM(FTD.[Amount]) AS [Total]
	FROM [FinancialTransaction] AS FT
	LEFT JOIN [FinancialTransactionDetail] AS FTD ON FTD.[TransactionId] = FT.[Id]
	LEFT JOIN [FinancialAccount] AS A ON A.[Id] = FTD.[AccountId]
	LEFT JOIN [Campus] AS C ON C.[Id] = A.[CampusId]
	WHERE A.[Id] IS NOT NULL
	GROUP BY A.[Id]
UNION
SELECT
	'Total' AS [Campus],
	'' AS [Fund],
	SUM(FTD.[Amount]) AS [Total]
	FROM [FinancialTransaction] AS FT
	LEFT JOIN [FinancialTransactionDetail] AS FTD ON FTD.[TransactionId] = FT.[Id]
	LEFT JOIN [FinancialAccount] AS A ON A.[Id] = FTD.[AccountId]
	LEFT JOIN [Campus] AS C ON C.[Id] = A.[CampusId]
	WHERE A.[Id] IS NOT NULL

    This returns ALL giving, so you might want to add some date constraints if you are interested in just the last X period of time's giving.

  • Photo of Michael Hughes

    0

    Thank you Daniel for your answer.   Unfortunately, I am not familiar with using the Dynamic Data Block.   I'm not sure if I just copy and paste this into the SQL Command in the Tools section.  The danger warning screams not to lol.   How integral is understanding SQL in producing reports?  That may determine whether we can move forward with Rock RMS.
  • Photo of Daniel Hazelbaker

    0

    You can copy and paste it into the `SQL Command` page as a test run. It's a SELECT statement so it won't make any changes to the database. The Dynamic Data block will give a similar output. It's a block you put on a page and can configure the SQL that it will run.

    I would say about 90% of things you can get done with DataViews and Rock's built in Reports. SQL is not critical, but it will be helpful when you run into something custom like this that Rock doesn't do (yet). But you also have the community available to help you get some of those custom things built and/or show you alternative ways to get the information.

    You can get the totals information along with the fund names by going to the Batch screen. At the bottom it will list totals. It doesn't show campus specifically, but I would recommend prefixing your fund names by some kind of campus designation. Everywhere in Rock it will display just the fund name so you will have trouble determining which fund is for which campus otherwise. For example, our "general fund" for our Porter Ranch campus is named `(PR) Tithes & Offerings`.

    A final note on SQL. It's kind of like owning a car. If you know a little bit about car mechanics you can save yourself a lot of time and money by changing oil yourself, replacing air filters, etc. Just because you don't doesn't mean you shouldn't buy a car, it just means when it's time to do those things you have to pay somebody else to do it. So not trying to specifically talk you into sticking with Rock, but you will run into the same problem with any system. At some point it's not going to do something "the way you've always done it".

    • Jim Michael

      Jim Michael

      I would add that the Giving Analytics function in Rock replaces a huge number of things you traditionally needed a "report" for. You can do all sorts of crazy filtering for accounts, campuses, etc and export the results to Excel.

    • Photo of Michael Hughes

      0

      What page would I put the data block on?   When I read the documentation from Rock RMS about data blocks, it says I should be seeing an Admin Toolbar.  I don't see that.  We do have someone who set up the database to help with migration.  Is it possible that only he can see that toolbar and has to give me rights to it?  I appreciate all your help!

      • Daniel Hazelbaker

        Daniel Hazelbaker

        Whatever page you want it to be on. You can add it to an existing page if it fits somewhere or create a new page for it. It's possible you don't have access to see the Admin toolbar, but if you have access to the Power Tools like you mentioned then you should have access to the admin toolbar too. It should show up if you move your mouse to the bottom edge of the web browser's window.