LinkedIn
Twitter
Thought that the process would be easy, but when I dug into the essentials of it, the process was not easy anymore.
There is an employee expense table, that records all expenses made by staff. The employee column is a ref to the main employee table. Other concerned cols are Date and Amount.
What I want to do is summarize the thing in a report for a particular Financial year, like this:
As of now, because I thought it might help, I have built a months table to correctly store the relationships of the months because it can be too overwhelming to use VCs for all that
But I have really ran into a never ending run while trying to build the report. Since I donโt want to limit the choice of months, and the number of employees can increase / decrease indefinitely, there really are two variables here.
Maybe two start expressions? Or something else?
Solved
1 ACCEPTED SOLUTION
Well again, I figured it out after posting 
What I was trying to achieve, is in a nutshell, a case of many-many relationship. While one to one, or one to many relationships can suitably be defined within one and two tables (the constituent ones), defining many to many relationships requires a third table that holds the relation definition.
-
I created an additional month-employee table, which holds all months as many times as the number of employees, as a child of the previously mentioned โmonthsโ table.
-
Within that table I used the proper select functions to generate all the financial parameters like credits and expenses.
-
Now within the report (from the months table), I used two start expressions. One to print out all the months from a chosen financial year, defined by a slice, and within that to print out all the Related Employees and their associated receipts and expenses.
As of now, I have manually added all the details in the employee-month table and the month table, but down the line I would run a bot that would automatically add all the employee-month details when a new month begins.
Re: Iโm always open to better solutions
Well again, I figured it out after posting
What I was trying to achieve, is in a nutshell, a case of many-many relationship. While one to one, or one to many relationships can suitably be defined within one and two tables (the constituent ones), defining many to many relationships requires a third table that holds the relation definition.
-
I created an additional month-employee table, which holds all months as many times as the number of employees, as a child of the previously mentioned โmonthsโ table.
-
Within that table I used the proper select functions to generate all the financial parameters like credits and expenses.
-
Now within the report (from the months table), I used two start expressions. One to print out all the months from a chosen financial year, defined by a slice, and within that to print out all the Related Employees and their associated receipts and expenses.
As of now, I have manually added all the details in the employee-month table and the month table, but down the line I would run a bot that would automatically add all the employee-month details when a new month begins.
Re: Iโm always open to better solutions
