WondersWhy
Technical User
Hi,
I have a bit of a puzzle. I am creating a summary report that is a bit like a "balanced scorecard" type. I want to display quote, booking, and invoice for the past 5 years and provide the ability to click on a year and get a display of monthly quote, booking and invoice summary for that year, and click on a month to get a display of daily data for that month. I am fine on the drill through concepts and have even tested this successfully for quotes or bookings or shipments.... but not all three at the same time.
Here is where it gets sticky. For the quotes, I am linked to a sale quote header file. The date in question is a "faxed to customer" date. For bookings, I am linked to a sales transaction register segregating by a "transaction date" for invoicing, I am linked to the same transaction register, but have a different date and transaction type. You can see that I require three different queries. To match our accounting periods (4-4-5 lunar calendar), I have created a calendar table that provides Accounting Year, Quarter, Month, Week and Day of Week detail to properly segregate sales. I have created an alias of this table for each of the three dates that I am pulling. This all works fine and good.
I would like what would look like a crosstab report where for the first level the row titles are "Year", and at the next drill down the row titles would be "Month", etc.
Here is the problem. Please prove me wrong if you can, but I do not think I can use a crosstab technique since the data in the Quote, Bookings, and Invoice columns are the product of independent queries.
I have tried using list frame sub-reports, with some success. I have presented quotes for instance, with row titles (Year of quote) associated with quote summaries and then lay down annual summaries of the bookings and invoicing right next to the quote summaries. I can hide the year presentation on the two extra list frames so it looks like it should. I run into problems though as you drill down. Where it gets a bit ugly is when drilling down to the daily level. Though it is not common, there are occations where on a given day there may be quotes and bookings, but no invoicing activity. There will be a shift if I were to make the sub-reports only show days that have activity (my preference is to avoid presenting Saturday and Sunday as it is not normal to have activity on these days. I will need lotsa ketchup for this can of worms)
How can I create a link between three different calendars to make them all line up? I can't imagine how I could actually link them in the catalog, since there would be illogical loops. If I could somehow do this, I imagine a crosstab report would be possible.
Hopefully some of you experts can identify some basic concepts that I have yet to grasp. If there is some bad news I need to know, I am ready for that too!
Thanks for any and all advice!
Tony T. in Minneapolis.
I have a bit of a puzzle. I am creating a summary report that is a bit like a "balanced scorecard" type. I want to display quote, booking, and invoice for the past 5 years and provide the ability to click on a year and get a display of monthly quote, booking and invoice summary for that year, and click on a month to get a display of daily data for that month. I am fine on the drill through concepts and have even tested this successfully for quotes or bookings or shipments.... but not all three at the same time.
Here is where it gets sticky. For the quotes, I am linked to a sale quote header file. The date in question is a "faxed to customer" date. For bookings, I am linked to a sales transaction register segregating by a "transaction date" for invoicing, I am linked to the same transaction register, but have a different date and transaction type. You can see that I require three different queries. To match our accounting periods (4-4-5 lunar calendar), I have created a calendar table that provides Accounting Year, Quarter, Month, Week and Day of Week detail to properly segregate sales. I have created an alias of this table for each of the three dates that I am pulling. This all works fine and good.
I would like what would look like a crosstab report where for the first level the row titles are "Year", and at the next drill down the row titles would be "Month", etc.
Here is the problem. Please prove me wrong if you can, but I do not think I can use a crosstab technique since the data in the Quote, Bookings, and Invoice columns are the product of independent queries.
I have tried using list frame sub-reports, with some success. I have presented quotes for instance, with row titles (Year of quote) associated with quote summaries and then lay down annual summaries of the bookings and invoicing right next to the quote summaries. I can hide the year presentation on the two extra list frames so it looks like it should. I run into problems though as you drill down. Where it gets a bit ugly is when drilling down to the daily level. Though it is not common, there are occations where on a given day there may be quotes and bookings, but no invoicing activity. There will be a shift if I were to make the sub-reports only show days that have activity (my preference is to avoid presenting Saturday and Sunday as it is not normal to have activity on these days. I will need lotsa ketchup for this can of worms)
How can I create a link between three different calendars to make them all line up? I can't imagine how I could actually link them in the catalog, since there would be illogical loops. If I could somehow do this, I imagine a crosstab report would be possible.
Hopefully some of you experts can identify some basic concepts that I have yet to grasp. If there is some bad news I need to know, I am ready for that too!
Thanks for any and all advice!
Tony T. in Minneapolis.
