I am exploring whether it is possible to create a report in 3.0 that accepts variables so that a business user can chose an input and the output is calculated accordingly. (I am working in 2.7 but trying to use the 3.0 methodology).
Two examples of what I would like to do:
- A business user selects two dates in a report and the difference in value of a metric (say order value) between those two dates is calculated.
- Calculate total order value over time in a single currency using fixed fx rates from a table with orders in multiple currencies. I would like the business user to be able to change the dates from which the specific fx rates should be taken, ideally in a drop down/filter.
My first example could be solved in 2.0 using SQL query variables, I am fairly certain.
My second example I am thinking might be solved if I was able to create a many to many relationship between an order table and a fx by date table with currency as key. With a filter only allowing one FX date to be selected I could create a business calculation that multiplies order value with the FX of the selected date… Not sure if this would work and since many to many relationships is not supported I cannot test it.
Any recommendations, workarounds, updates on when these features (variables, many to many relationships) might be planned would be appreciated.