Hi,
We have an employee(employee are Sales Rep for the current client) Dimension tagged to our fact which stores Beverage Volumes.
The employee dim is SCD2. Thus by default if we drag employee with Volume in a report we get employee history wise Volume Sell.
Let us look at the following Volume Sale transactions (sample)
| Employee | Territory | Volume ('000 Litres) | Date |
| A1 | T1 | 200 | 22-Dec-15 |
| A1 | T1 | 100 | 23-Jan-16 |
| A2 | T1 | 30 | 26-Feb-16 |
| A2 | T1 | 50 | 02-Mar-16 |
| A3 | T1 | 250 | 05-Apr-16 |
Now the reporting requirement is as follows:-
The Volume Sales should be tagged to the latest employee for entire history of transactions.
- This means that if we run the report with Reference Date = 5-Apr-16 or greater then in report we should have only the following record:-
A3 - T1 - 630
- However if Reference Date chosen = 19-Mar-16 then
A2 - T1 - 380
- If Reference Date = 1-Jan-16 then
A1 - T1 - 200
This means tagging back Volume data to the latest employee snapshot for all possible Reference Dates (full calendar). This should not be stored in DB as then the cardinality of join will increase. Also what I have not mentioned here is Employee Dim has a hierarchy as well hence the total possible combinations, prudently speaking, should not be stored but computed at run-time.
Thus we are planning to have a dynamic SQL query (using Direct Discovery) which accepts the Reference Date (chosen in the report) and retrieve the employee dimension tagging of Volume accordingly.
We have tried using the Direct Discovery feature (dynamic SQL) to achieve this but are unable to pass the date paramater in the WHERE clause of the same. Any pointers will be helpful.
"...WHERE START_DATE = $(vStartDate)"
where vStartDate had the expression = GetFieldSelections(PromptFieldName).
