You can create a new report that will directly appear in the Shopify web admin. Using ShopifyQL for the query itself, and the Report API accepts REST requests for manipulating the reports. As long as the shop is on Shopify Advanced or Shopify Plus this functionality is available.
Check this out for details --> https://shopify.dev/docs/admin-api/rest/reference/analytics/report#create-2020-07.
Thanks, but that not exactly the use case I'm describing. We do not want to view the report in the Shopify dashboard. We need to fetch this data for insertion into a separate data warehouse for our accounting team. Hence the need for REST access to the report data.
Bottom line is, we are looking for programmatic access to the results of the created reports.
Unless I'm mistaken I think your two primary options are the REST API or the GraphQL API. You have more control of the specific fields you need with the latter option. If it's a scheduled task of pulling the data from Shopify and pushing it over to your accounting package then it should consist of a basic API request, where the response then gets pushed over into accounting. If it's a specific requirement for your own Shopify shop then you create a private app, install it within your Shopify shop, and use the API credentials to authenticate within your private app.
Perhaps someone from the Shopify team can chime in here. But as far as I'm aware you can't access the ShopifyQL except via an embedded app SDK. This same train of thought goes back a-ways --> https://community.shopify.com/c/Shopify-APIs-SDKs/How-do-you-query-Analytics-API-without-the-embedde....
Honestly, if it's just a quick pull of daily sales counts and totals then pull via REST of GQL, iterating through the records totaling them up, and pushing over into another receiver isn't that big of a deal. I've implemented that for production apps working with at least 3-4 different provider endpoints. While it's not as efficient as a SQL-type query, it's not terribly ungainly.
Yes, I've implemented a process as you've described, getting orders for a given date and summing them. However, I need to also get refunds which apparently are NOT directly query-able by date, one must go though orders. So I can can keep banging my head against the wall trying to account for all of Shopify's business logic by querying raw data.... or they could simply expose programmatic reporting to API users.
Keep on banging your head. Also, be aware that some Shopify reports are actually not useful for accounting as they have bizarre features. So depending on them might not be in your best interest.
You say you can't get refunds? They are simply Transactions and they are indeed available, simply by providing the ID of the order they belong to. So it is pretty straight forward to download an order, and all its transactions and have as accurate a source of data as you could hope for. In some cases perhaps, even more accurate than Shopify itself!
For all the work you'd do in accessing reports to feed your system, just working with the actual data is not much more, and it puts you in the driver's seat, where you probably want to be. Will save you the headache of mistakes down the line when you assuming you were feeding the good stuff into maw but weren't.