I have a situation in which I have 2 unrelated sets of data. One has User information and one has Loan information. But I need to create a common calender so that a particular selection of date will affect both the data sets. I have created a common calender, but the problem is I get a cross join of data.
ex: Lets Say the Sum(Loan Amount) is 100.it shows 100 for each of my 10 users which is correct. But the sum now shown in a single summary box is 100*10=1000. I want that summary box not to be affected. It should show 100 irrespective of any selection of my user.
Let me know how should I approach the use of common calender for unrelated sets of data.
You can either concantenate the 2 tables into one and link the date table to that one concantenated table which is easier when you have multiple data in common or if you just have date field in common you should be able to create a date column with the same name in the Loan, User and Date Tables that will be your key between the tables.
The user may still modify the summary box if the user doesn't exist for certain dates, but you can ignore the user selection using set analysis.
