PO Spend Report - Filter data by date

Question

Hi everyone,I have a relationship with Company&lt;Purchase OrdersI am summarizing a numeric field on the Company Table from the Purchase Orders Table for Total Committed CostsThis way I can see a value for how much we are spending by manufacturer.Is there a way to dynamically select the date used for that relationship of the Purchase Order on my report in the Companies table?&nbsp; I know I can create two relationship summary fields, one for this year and one for last year.&nbsp; but what if i wanted to pull 3 years of data?&nbsp; I need to create a new summary field and a new date?&nbsp; Is there a way to dynamically filter this?Thanks!------------------------------Ivan Weiss------------------------------

blakeharrison · Answer

I'm assuming you're referring to the embedded report the Company record, but I'm not clear on the relation to the summary fields. If you want to always show 3 years of data in an embedded report, you can do this a couple of different ways. Possibly the easiest would be to create a Formula Checkbox field on your PO table and build your criteria there. For this example, I would probably go with something like this:
Year([PO Date])&gt;=Year(Today())-3​
You would then use this field in the filter for the Embedded Report.------------------------------Blake Harrisonbharrison@datablender.ioDataBlender - Quickbase Solution ProviderAtlanta GA404.800.1702 / http://datablender.io/------------------------------

ivanweiss · Answer

Hi Blake, let me clarify the ask a little bit to make it clearer....

I am not using an embedded report but created a report showing the company name, their web site, services they provide (which is a field I have), and the summary field from the purchases order table showing committed spend.

Currently, the way I did this is to create two summary fields and two reports:

First summary field summarizes the dollar amount of all PO's from the PO table where PO date is during the current year. That gives me the current committed spend this year.

The second summary field summarizes the dollar amount of all PO's from the PO table for any date. That gives me the total committed spend all time for the vendor.

What I was getting at is sometimes I want to pull spend reports on different date ranges. I have a date field on the PO called PO Date. I do not want my VP of Supply to need me to create the report and date range on the backend all the time. I was hoping he could use a dynamic filter on the report to change that PO date range so it would summarize the data based on whatever selection he puts in there.

------------------------------
Ivan Weiss
------------------------------

markshnier__you · Answer

IvanI see two alternatives.1. Create an &lt;ask the user&gt; Report on the PO table and make a summary report there, by company.2. Create a single Record in a &nbsp;single table with Record ID = 1 and have the date range the. &nbsp;Make a relationship to all POs. &nbsp;Make a summary totals by company using that global date filter. &nbsp;Put the embedded report of companies as a Report Link field on the single global date record.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

PO Spend Report - Filter data by date

Related Content

Embedded Grid Edit Report Pre-filtered Conditional Dropdown

Trying to create a report with an ask users filter, but with a multiselect

Report Formulas

Restful API Report digesting JSON

Summary Reports