I want to show 3 reports on the basis of 3 different date fields which are present in 3 different tables and i want to connect my calendar field to these 3 fields seperately for that i used one more table called calendar Link in which i am bringing all the primary keys of these 3 tables and date field of master calendar table this concept is not working in my case because 3 tables are connected with some relation and while doing this method, it generates loops ..... can any one tel me how to avoide these loops or any other method to that ?
In some cases you might be able to concatenate the tables into one with some sort of "type" field distinguishing them, and use the same date field for all three that way. That's not always practical, of course.
Yes even i concatenated two tables but the thired one is not directly linked to first one but it is linked via one more table... then in that case it difficult to concatinate all tables togather.....
and you told me to do concatenation on the basis of some type.... can you explain more about that?
Make a calender table by concatenating those 3 date fields and keep it unlinked. Write expressions in those charts by using this coomcalender datefields. If u tell me what chart u r making i can tell u the expression also.
Type, ID, Customer, Product, Date, Order Date, Shipment Date, Invoice Date Order, ORD456, Bob , Apples , 20120417, 20120417 , , Shipment, SHP789, Bob , Apples , 20120422, , 20120422 , Invoice, INV123, Bob , Apples , 20120423, , , 20120423
Though really, this is probably also an example of why this may not always be practical. We might, for instance, want to connect the shipment and the invoice back to the order. When they're all in the same table like this, that's not easily done.
A common approach is to create a date island, just a calendar disconnected from all your data. Then use the island date as a dimension, and do things like sum(if("Order Date"="Island Date", "Amount")). The main problem with that approach is poor performance on large data sets, though it works just fine on smaller data sets. I suspect that's what Sweth_24 was suggesting, though I can't quite tell.
