Monthly Reports

I’m not sure if I am missing something, but trying to run a monthly report feels to me as essentially unusable. I created a workaround, but even that has proved a challenge.

I have to produce a report each month for someone to enter some data into another system for totals from the previous month.

I was hoping to have a simple page that allows someone to just select the month they want to filter the report on, and off it would go. The date selection is so challenging to set a monthly range.
To select say last august, you have to select “is after 31st July at 23:59” and “is before 1st September at 00:00” - this is just no intuitive for non tech users, and 2 years in to using this app, no users have ever understood this. I have never seen anything so complicated, so agree, this does not work for end users.

My workaround was to create a fixed report using the 'is the previous 1 month" and creating a task that emails the user a few days into the month to tell them to go and run the report. They then print the page, save as a PDF and save the data this way thus having something they can go back on.

I also send myself an email a few days before reminding me to change the hearder of the report so the date is current - e.g. this month I have changed the header to “Data for December 2022”. I have spend about 10 hours trying to work out how to do display in javascript the previous months name - but whilst i have found a script to do it, it only activates on a button - but i dont know anything about javascript to stop this from being triggered by a button, and just display it on header.

I know i could create manual reports and set manual date ranges each month, so the users can just select one, but i really don’t want to be having to go in each month to create a new report.

Surely there is a simpler way to select a whole month to filter a report?

This works, but it’s hardly slick,

the previous month. My work around is

Hi Andrew

Here is how I create a current month filter to see our scheduled Jobs for the month.





You would probably also require a year filter using the same method for your scenario. Then you’d want to create a field using Conditional Rules to “convert” each month number to a month name. Finally, create 1 more field and concatenate your report name, and month name and year for your report title.

You could use the same method to find the previous month and have a static filter label/Link Text of something like “Last month”. Beyond something simple like this, what I don’t know how you’d do it, or if it is even possible, is to create filters with dynamic labels/Link Text combining the month name and year or even something simple like “01/23”. Maybe other more knowledgeable members might be able to suggest something here.

I hope this helps.

1 Like

Hi Andrew,

I’m very proud to offer you a pretty nice solution to your monthly report filtering request. I’ve just completed the code to add Date pickers to any view, without any code from your part.

All you have to do is:

  1. copy two files (Javascript and CSS) to your Builder
  2. In the Builder, go to the page of interest and in the title, at the end, add this text: DATETIME_PICKERS

This text will be invisible in the App, and it will trigger a special behavior where it adds Start date and End date calendar pickers above the table. When you select a Start date, the End date will automatically set itself to the last day of that month and the table will filter itself instantly!

You can even place your focus on the month field of the Start date and use the up/down arrow to see months scrolling around and display filtered data in real-time.

Eventually, I’ll also support weekly and daily periods.
UPDATE: Jan 16, 2022: I have completed the Monthly, Weekly and Daily period, and it appears as radio buttons, with live updates. See docs for more details.

With the KTL, it took me only 8 hours to code all this.
I strongly suggest you try it, you won’t be disappointed.
Get it here: Knack Toolkit Library - on GitHub

It’s free and Open Source above all. So you can inspect the code at will, learn from it, copy it, contribute, or whatever you want.

If you need assistance let me know.

Cheers,
Normand D.

1 Like

UPDATE!!!

I’ve just added another cool capability to that feature: you can now have more a complex filter involving multiple conditions (additional fields), and the Start/End dates will merge themselves with the rest. As long as the operator is AND, as OR do not work with date ranges.

Enjoy,
Norm

1 Like

Hi Dean,

This is super useful thanks, i hadn’t thought of going down this route, but i like your thinking!
It’s seem a huge amount of hassle for what is such a basic concept, but i can see it will work really well.
Cheers

Andrew

1 Like

Hi,

This looks a really useful solution, i will have a proper look and play with it.

I see it’s open source and really appreciate people here putting time into things like this - is there a way we can give you a token of thanks (along lines of buymeacoffee.com) - maybe we should create a knack site for buying contributors virtual coffees ?

Will have a play and report back

cheers

Andrew

1 Like

Hey Andrew,

Thank you so much for the nice comments, it’s really appreciated.

The coffee idea is not bad :sleeping: => :face_with_spiral_eyes: => :flushed:

But here’s what would make me really happy, two simple things:

  1. Use the KTL in your apps, test it thoroughly, report bugs, etc. If you can code, I could use a few collaborators.
  2. Refer and talk about it here Knack forums and wherever else.

Cheers,
Normand

Andrew,

Recently, I suggested to Knack that they need a Report Builder.

Graphs and Charts are NOT reports. This is a common conceit used by any number of SAAS & RAD tools. They lack a true report builder so the call Graphs “Reports”. Ridiculous! Again, it’s not just Knack that is doing this, lots of tools use the same “convention”.

Let’s spell it out: Knack needs a REPORT BUILDER.

A Report Builder should INCLUDE, but not necessarily be limited to:

  • A printable, customizable, banded, multipage document
  • Report header and footer (that can be justified vertically)
  • Page header and footer (repeats on all pages)
  • Multiple optional groupings by field value(s)
  • Group headers and footers (repeats on all pages where groups apply)
  • Headers and footers should allow the inclusion of field values, text, page numbers, summary values based upon groups, current date/time display options, embedded images, etc.
  • Report sections should allow the inclusion of embedded images (e.g. logos) and borders
  • Font style selection including type, weight, color, size and other attributes should be allowed.
  • Dynamic calculated values should be allowed
  • Linked sub-reports should be allowed
  • Automatic conversion to PDF format
  • Option to automatically email PDF to selected recipients
  • and much more…

I’m not going to tell Knack how to build this other than to say they could start with some basic functionality such as report header/footer, current date/time options and page numbering. Then add features as they roll it out.

If Knack were to build this they could SMASH the competition! Business wants business reports! Go for it!

And thanks.

3 Likes

Normand,

What you have done is great. I’ve only had a chance to quickly scroll through your documentation. Somewhere down the road I’ll find an opportunity to try to assimilate what you offer. In the mean time, Knack can take your offering as inspiration and build in this much-needed functionality.

Thanks!

1 Like

Hi Peter,

You make a good point, and i look at my ‘reports’ and they are a mix of pivot tables, charts, lists etc etc all managed via filters, etc. I am always nervous that to replicate a new monthy report i have to copy the page and elements, which when you copy not all elements are copied (e.g. filters), and the margin for error is huge. I know we ask a lot of no code, and the more we learn and develop we get closer to being able to code, so it’s a fine balance, but I would say reporting is a big win for even entry level users.

Andrew,

Just to underline the point, another RAD tool I use has a robust report builder. And it is entirely no-code. Drag and drop basically. It does have options to add code to events if you wish. But the code option is rarely used or needed.

1 Like

The difference between ‘No-Code’ vs ‘Some code’ is enormous. It really is so frustrating for users who are busy running their businesses and dont have JS or CSS in their locker, to say ‘Ah well you can add these functions and twiddle this and push that’ and you have the system of your dreams. I suspect that like many users we end up with something 90% fantastic, and 10% hand cranking. Our thing is label printing, as we use Knack as an order and production management tool. It works brilliantly right up to the point where we want to print formatted labels to put on our products, which we do by exporting todays data in a csv and then using a mail-merge in Word. Ludicrous. I know it can be done easily in JS, but the reason we have gone ‘no-code’ in the first place is because I don’t code, I run my business all day.

1 Like