Discussions

Expand all | Collapse all

Using totals in reports

  • 1.  Using totals in reports

    Posted 02-08-2020 19:17
    I've dabbled in Microsoft Access for many years and am used to their Querys. I'm having a dickens of a time figuring out how to do similar things in Quick Base.

    I have a table that contains 3 fields:
     - Date
     - # of trucks here
     - # of trucks serviced

    I'd like a report that summarizes everything by week, showing the total number of trucks that were here each week, the total number of trucks that were serviced each week, and then the percent of trucks that were serviced each week ([Total Number Serviced]/[Total Number Here])

    I've figured out how to generate a 6-digit n​umber for each date (yyyyww) showing the year and weeknumber in that year.

    Using that yyyyww number, I can create a report showing the number of trucks here and serviced for each of those weeks (those are totals generated by the report).

    Where I'm stuck is figuring out how to come up with the percentage of trucks serviced each week.

    Help!

    ------------------------------
    Rob Reiterman
    ------------------------------


  • 2.  RE: Using totals in reports

    Posted 02-08-2020 19:26

    No problem

    Quick Base you will need to summarize these data up to another table of weeks with the key field set up be that yyyyww value. 


    I would use excel to import the next few years of weeks into a new table and set that field to be the key field. 

    Then make a relationship where one week has many shipments. 


    Then make summary fields on that relationship to summarize the number of trucks here and the number of trucks serviced. Then make your formula numeric field to calculate %



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Using totals in reports

    Posted 02-10-2020 22:18
    Hi Mark,

    Thanks for your guidance! Using your approach, I can also summarize by months and find the percent of trucks turned each month.

    Quite a different way of thinking than with MS Access - I can see the need to think through some things pretty carefully when designing an app in QuickBase - with Access' querys it's easy to get a bit sloppy in one's designing.

    Really appreciate the quick response!  Thank you

    ------------------------------
    Rob Reiterman
    ------------------------------



  • 4.  RE: Using totals in reports

    Posted 02-11-2020 07:27
    Yes, I think that "programmers" who come with knowledge about how other databases work get tripped up by that concept that a summary type operation needs a record to summarize up into, if you want to use it in subsequent calculations.  

    But along with that being considered a limitation, it is no doubt also why Quick Base is Quick.  ie, under the covers it needs to be super fast to process potentially millions of records and yet have a sub second snappy response time even in a report where thousands of Summary totals need to show in a  column in the report, where say the data table being summarized has a million records.  


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Using totals in reports

    Posted 02-09-2020 17:00
    Rob,

    MS Access.  I remember having to hook Crystal Reports......

    No matter what DB you are in, at some point you exceed the limits of its ability to natively do reporting.  Most DB's have no native capability and you have to use a reporting tool to get to anything.    If you are reporting needs are exceeding QB have you considered Tableau or PowerBI?   There is a PowerBI connector to Quick Base so you can get to some basic data pretty easy.   

    If you want to curate to optimize you will need to do an extract which we can help you with.  Reach out if a SQL answer is appropriate.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 6.  RE: Using totals in reports

    Posted 02-10-2020 22:23
    And thank you, Don, for your idea about PowerBI and Tableau - I haven't heard of either of those before. Where could I find more information about PowerBI and how it connects to Quick Base? And about doing an extract? Those types of things sound like they'll be very helpful for the types of apps I'd like to develop for the company I work for.

    Really appreciate the help you and Mark have given!

    ------------------------------
    Rob Reiterman
    ------------------------------



  • 7.  RE: Using totals in reports

    Posted 02-11-2020 07:19
    Rob,

    The number of options in the Business Intelligence/Business Analytics is very large.  Two of the most popular are Tableau and PowerBI.  I have different clients that use each of them.

    Tableau is now owned by SalesForce.   www.Tableau.com
    PowerBI is owned by MicroSoft  https://powerbi.microsoft.com/en-us/

    PowerBI has a free version.  Are you going to be the consumer of this report or do you need to distribute the info?  The smaller the number of users for the Analytics part of your system you can avoid additional subscriptions or having to embed the Analytics back in Quick Base

    There are three ways to get data over to the tools.

    CSV Exports.   If your data is flat and you do not need to refresh it often, then you can do the infamous Save as a Spreadsheet option in Quick Base

    The PowerBI connector 
    https://help.quickbase.com/user-assistance/power_bi_with_quick_base.html

    Extract and make a SQL DB also known as Sqlization.  This is the gold standard but is the most work.  Once your QB data is out then you can do anything you could want but that is conventional development and not as fast or easy as Quick Base work. 



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------