Scheduled vs On Demand Report I have an "On ...

(Michael) #1

Scheduled vs On Demand Report

I have an “On Demand” reporting feature implemented in many of my apps that allow the user to email themselves a report. This report consists of a single row of data from the parent table and all related rows of a child table. This “On Demand” feature is implemented via an OnChange column and an action.

In this configuration the user is only able to generate a report for one row of the parent table at a time. I would like the user to be able to generate a report for all rows in the parent table as well as the related rows in the child.

A scheduled report will create a report of all rows in a table and all the related rows. This is what I need however, I’d like to trigger this 'On Demand" through an action.

It may help to think of this as a sales situation where one table contains customers and a related child table contains customer orders.

Through an action my users are able to email themselves a report containing ONE customer and all the orders that customer has placed.

A scheduled report that will create a report containing ALL customers and each customer’s orders.

I need a way to generate this report on demand and not via a schedule.

Any ideas how I might go about implementing this?

(Praveen Seshadri (AppSheet)) #2

Hi @Michael, this is actually easier than it initially appears.

Add a third table — let’s call it “Report Request”. Give it a single row.

In your app, the action requesting the full report corresponds to making some change to this one row of this Report Request table.

In your workflow rule triggered on this change, you provide an attachment template. Make that template identical to the scheduled workflow report template you already have.

(Michael) #3

@praveen Thanks for taking the time to help me get this figured out. Using this method, I was able to give the user the on demand reporting function. Now I would like to add the ability for the user to filter what is reported on. I’ll make another post as you’ve resolved this particular issue. Thanks again!

(Praveen Seshadri (AppSheet)) #4

To add further stuff like filtering, add extra columns to the Report Request.

Now you have to use those extra columns inside your workflow template document. For example, you may want to filter the rows inside a <Start:> clause. To do so, you can access the values of the Report Request row using [_THISROW].[Column Name]

(Michael) #5

@praveen So this has me pretty well stumped…

My workflow pulls data from a worksheet titled ‘Corridor Inspections.’ The reporting filters table is ‘Report Request.’

At the moment, ‘Report Request’ has 2 relevant fields; Year & Corridor. Keep in mind that this table only has one row of data as it is used solely for filtering the reports.

I have gone through many iterations of a few expressions but none return the results I am expecting.

The user should be able to enter criteria in ONLY ONE OR BOTH fields. I’d like to add other fields later but want to get this working with just the two for now…

To summarize, the user should be able to enter into ‘Report Request’ a year OR a corridor OR both year AND corridor to return a report with data from 'Corridor Inspections.

Here is one expression I have tried. In this statement, [Status] is defined and not a user option.

OR(IN([CorInsId],SELECT(Corridor Inspections[CorInsId], AND([Status]=”Approved”,[YEAR]=ANY(SELECT(Report Request[Year],TRUE))))),

IN([CorInsId],SELECT(Corridor Inspections[CorInsId], AND([Status]=”Approved”,[Corridor]=ANY(SELECT(Report Request[Corridor],TRUE))))), IN([CorInsID],SELECT(Corridor Inspections[CorInsId], AND([Status]=”Approved”,[YEAR]=ANY(SELECT(Report Request[Year],TRUE)),[Corridor]=ANY(SELECT(Report Request[Corridor],TRUE))))))