I need to create a PDF with multiple conditions, and based on those conditions, I can display them or not using conditionals. I have an app to track expenses, and I want to print those expenses by department, vendor, or unit. I also want to get a summary of each parameter and a general summary. I’ve done some research, but I don’t know if the templates allow the use of variables, or what you recommend for doing this, or a documentation resource you recommend. I would be very grateful.
In general, you can explore the slice option on the tables ( in your case Expenses records table) to come up with the desired subset of records to be included in the PDF report. You can then base your template on the slice.
In the sample app below from the help article Get started by using the sample apps - AppSheet Help
, the use can select the color and accordingly the chart changes because the chart is based on the slice. You can use slice in the template instead of chart. This is a basic sample app for a single user. You may need refinements in case you have multi user operation. For multi user operation, typically you may need a Users table, with one record per user to store the different selections of each user. For example one user may want report by vendor and another by department at the same time. So their respective selections need to be stored in Users table.
In addition to what @Suvrutt_Gurjar pointed you to, if you want to create reports with single category headers then see this post.
It can look complex until you understand embedded select/filter expressions that involve the use of [_THISROW-n] notation. (See this post)
An example template and its results…
REPORT_CONTROL
month.rc: <<[month.rc]>>
ref.users: <<[ref.users]>>
ref.categories: <<[ref.categories][name.category]>>
ref.departments: <<[ref.departments][name.department]>>
ref.vendors: <<[ref.vendors][name.vendor]>>
GRAND TOTAL: <<SUM([expenses.filtered][amount.expense])>>
<<Start: SELECT(EXPENSES[id.expense], AND( IN([id.expense],[_THISROW].[expenses.filtered]), [_RowNumber] = MIN( SELECT(EXPENSES[_RowNumber], AND( IN([id.expense],[_THISROW].[expenses.filtered]), MONTH([date.expense])=MONTH([_THISROW-1].[date.expense])))) ) )>>
MONTH: <<MONTH([date.expense])>>
MONTHLY TOTAL: <<SUM(SELECT(EXPENSES[amount.expense],AND(IN([id.expense],[_THISROW].[expenses.filtered]),MONTH([date.expense])=MONTH([_THISROW-1].[date.expense]))))>>
<<Start: FILTER(“USERS”,IN([id.user],UNIQUE(SELECT(EXPENSES[ref.user],AND(MONTH([date.expense])=MONTH([_THISROW-2].[date.expense]),IN([id.expense],[_THISROW].[expenses.filtered]))))))>>
USER: <<[id.user]>>
USER TOTAL: <<SUM(SELECT(EXPENSES[amount.expense],AND(MONTH([date.expense])=MONTH([_THISROW-2].[date.expense]),IN([id.expense],[_THISROW].[expenses.filtered]),[ref.user]=[_THISROW-1].[id.user])))>>
<<End>>
<<Start: FILTER(“CATEGORIES”,IN([id.category],UNIQUE(SELECT(EXPENSES[ref.category],AND(MONTH([date.expense])=MONTH([_THISROW-2].[date.expense]),IN([id.expense],[_THISROW].[expenses.filtered]))))))>>
CATEGORY: <<[id.category].[name.category]>>
CATEGORY TOTAL: <<SUM(SELECT(EXPENSES[amount.expense],AND(MONTH([date.expense])=MONTH([_THISROW-2].[date.expense]),IN([id.expense],[_THISROW].[expenses.filtered]),[ref.category]=[_THISROW-1].[id.category])))>>
<<End>>
<<End>>
This is the full expense list
A report on full list. I only implemented USERS and CATEGORIES summary expressions.
A filtered state.
A report on the filtered data.
Hope this helps.
Muchas gracias soy un poco nuevo en esto y la verdad si se me esta complicando este tema de reportes más avanzados y sobre todo porque no hay muchos recursos en español. Pero me ha sido de mucha ayuda tu explicación, en verdad gracias.