A “View” into a new Query future

Attention Query Writers and Data Analysts:

Can you relate to the struggle of using Query to find every sub-account a membership might have? Or a list of members with only the basic share? The challenge lies with the fact that product records live across 6 different tables – This is a good data practice – But not exactly easy to combine for report queries. It can certainly be done, just not easily.

Which is why I am very very very excited to see that CU*Answers has recently released a helpful new database View with every single account any member has open today.

MBRNACCTSQ – Membership View Active Accounts

You’ll find this gem in your FILExx library (xx = your CU ID) of CU*BASE®. It’s not a comprehensive set of every data point found in the 6 active account tables. Naturally, not every data point applies to every product type, but everything consistent across the records was included plus a few extra.

Views themselves have already been in play behind the scenes for quite awhile here, but until this point only as a solution internally for various programs and APIs. Now, we are beginning to develop and launch Views for data analysts in hopes that these solutions will assist with creating faster and friendlier analytics with Query.

Fancy Tricks with MBRNACCTSQ

Sometimes a tool or resource is developed here with the intent of opening new doors to insights or function that you never had before. Other times a resource is intended to make something that exists faster, better, easier. This is a faster, better, easier solution. Here’s just two ways I recently used this View in a project:

Full Member Name

Normally membership names are split into 3 unique fields – First Name, Middle Initial, and Last Name. That is a good data practice. But combining them all for a final report can get messy with currently available options…like this:

Business Memberships


Individual Memberships

What You Get ALICIA                 M  TESTMEMBER

There are currently a few round-about ways to achieve a proper-looking combination of the name columns, but none as simple or convenient. Now, only join any table with account numbers to the MBRNACCTSQ View and you instantly have the full membership name for your report, spaced correctly, and in the right order!

Membership “Freshness Date”

Quickly find the last transactional activity across an entire membership account*. Here’s how to do this in Query with this View:

  1. SORT by Account Base
  2. BREAK by Account Base
  3. Set a MAX total on the Last Transaction Date column
    1. Via the “Create Totals and Sub-Totals” option
  4. Set output to SUMMARY
    1. If output is also set to a database file, the resulting file will include a brand-new queryable column of that value for the membership’s most recent transaction date across any suffix.

*This trick does not account for any offline trial balance (OTB) products since OTB products are not included in this View.


I look forward to watching our community of data analysts find new uses for the data resources available at your credit union. Keep an eye on CU*BASE® core release summaries to catch other Views as they go live (Read the Database Changes version)

Or watch the blog, I do plan to spotlight key launches on Views and other analytics or warehousing solutions.

Share your thoughts

7 Replies to “A “View” into a new Query future”

  1. I want to learn more about the ideas related to “report writers” and data base presentation and query tools. I know you are studying how to broaden the options for analyst to look at, present, and communicate what they find in data bases. I hope you will keep telling us what you find, and how to evaluate what tools most have in common and what they might earn their props from.

  2. Excellent stuff in this blog, Annalyn. It’s obvious that you’ve learned the ins and outs of our databases while doing the same real, practical things that our credit unions are doing (or should doing) every day.

  3. My query strings have become a lot shorter because of this and other new views now available via query! Thanks for sharing some of your tricks when using this view!

    1. Cool! Thank you for the feedback, and I hope shorter = faster too. Views are going to be even bigger in the future as we work on putting together different combinations of related data for easier analysis with custom queries. An example that recently came up is loan applications – a full picture of member AND non-member AND additional signers AND denial details right now would require: LNAP – NLNAP – ADSA – DNAPPS – NDNAPPS. There’s dashboards and reports that can be used to generate tables of member and non-member applications together, but those won’t include some of the more detailed items like interest rates, terms, etc. I don’t know if/when we could get this specific idea out in production, but it’s a thought and we’ll definitely at least look into it. Let us know if you have any other ideas for data to compile into a view.

      1. Faster for sure!

        We chatted in the past about the limitations of the MNUETASGN file because it requires some connections against other back-end tables, and you thought that may be a good option for another database view. Is there any chance that could happen?

        1. Good question, and the chance that another database view is the solution here turns out to be pretty small. The limiting factor here is a system requirement that any view using data from a back-end location would need to also live in that back-end location in order to work properly.

          If I’m remembering correctly, we were talking about how MNUETASGN will make it look like a terminated employee still has some tool authorities even when you have removed everything via the Employee Security tool. Those other “authorities” were things that do not require employee security (never did) so there wasn’t a need to control them in Employee Security.

          But something potentially clarifying just came out in the most recent CU*BASE release (18.03) which I think you’ll be interested in – I’ll send you an email with the release summary details. ?

Leave a Reply

Your email address will not be published. Required fields are marked *