12 Replies Latest reply: Aug 29, 2012 2:47 PM by Ethan Beaulieu RSS

    Removing synthetic table

      Not sure if someone can help me by just looking at the model I created but I want to remove the synthetic table somehow.

       

      It comes about when I bring in my master calendar. My other tables don't have a general "date" field so I used the Makedate() function and the field "CreatedDate" in each table to generate a date and call it Calendar.Date.

       

      Any help would be great.

        • Re: Removing synthetic table
          Oleg Troyansky

          It looks like all those date fields are different, they all represent different events. That means, that they need to be named differently. That will resolve the problem of the synthetic keys, but create another problem - you can't use a single Master Calendar anymore.

           

          I don't know much about your business requirements, but generally speaking, you need to pick one date as the "main" date and link it to the Master Calendar, while other dates can be "secondary" and you can populate a subset of calendar fields (such as Year, Month) just for them as needed.

           

          For example, think of this - when the user selects Year and Month on top of the screen, what data do you want to get sleected - Accounts created in the selected Month? Or Opportunities? Or Quotes? You can't say "all of the above", because those are all different data sets. You have to pick one or another...

           

          hope it helps,

           

          Oleg Troyansky

          • Re: Removing synthetic table
            Robinson Selvaraj

            Hi

             

            Can you give this a try.

            1. Create a Master Calendar (with all the Year, Month, Day, etc. using autogenerate).

            2. Dont link this to any of the tables in the data model.

            3. Create similar Year, Month and Day fields in all the tables, respectively.

            4. Now in the frontend in your expressions, you can use and if condition (set analysis preferred as it is faster) to compare the dates with the ones from the Master Calendar, for the respctive reports, so you get the desired output.

             

            Hope this helps,

             

            Best Regards,

            Robinson

              • Re: Removing synthetic table
                Michael Solomovich

                Robinson,

                 

                I use this occasionally as well.  The drawback here is the front-end performance, hence the solution is acceptable for a comparatively small data sets.  That may be the case here, we don't know.

                 

                Regards,

                Michael

                  • Re: Removing synthetic table

                    Can you give me an example of a set analysis equation to do this? I'm not very familiar with syntax and process of it.

                     

                    Thanks for all your help throughout this.

                    • Re: Removing synthetic table

                      For example, I attached what my ultimate goal will be for using this model. Kind of curious if you have any ideas on how to do this as well.

                       

                      I wanted to try and do this with a funnel chart.

                       

                      Where a Lead = a count of all Opportunities with a created date in the current year to date. (ie Opportunity.CreatedDate) Same goes for Last year to date.

                       

                      Where a Quote = a count of all Opportunities with a Quote total (ie Quote.GrandTotal) > 0 and then referencing the Lead for everything current year to date and then last year to date.

                       

                      So as you might see, leads = 20851 (YTD) in my report and then OF THOSE 20851 leads, 16829 then become Quotes. It all flows downward.

                       

                      Any ideas as to how to put these buckets and formats into a funnel chart? I understand that YTD and LYTD will be in different graphs. Plus the variances will be too.