Hey there,
I am trying to create a high-level sales dashboard in BO Crystal Reports XI (version 11.5). It's dashboard that not only gives metrics at a high-level but also lets you do drill-down analysis to get to the low-level details.
Platform:
---------
Database: Oracle 8i
Reporting tool: BO CR XI
Portal: (which uses Crstyal Reports Server)
Client requirements:
----------------------
- a YTD and LYYTD view of sales compared with Budget data of all the months starting from January to current month
- a MTD and LYMTD view of sales compared with Budget for that month
- Drill-down requirements: Department > Imprint > Subject > ISBN
(I work for a publisher, and we sell books)
Drill-down levels/Desire Output:
------------------
Level 1:
YTD_Sales YTD_Budget LYTD_Sales LYTD_Budget
Level 2:
Imprint_A Imprint_B Imprint_C
Level 3:
Subject_X Subject_Y Subject_Z
Level 4:
This is the lowest level of details at the product level.
Note: Imprint and Subject is a hierarchical organization of our products, which are primarily books
Data Sources:
-------------
SALES table
* relevant columns (SALES.DATE, SALES.MONTH, SALES.ITEM(ISBN#), SALES.GROSS, SALES.RETURN)
PRODUCT table
* relevant columns (PRODUCT.ITEM, PRODUCT.DEPARTMENT, PRODUCT.IMPRINT, PRODUCT.SUBJECT)
BUDGET table
* relevant columns (BUDGET.DEPARTMENT, BUDGET.MONTH. BUDGET.GROSS, BUDGET.RETURNS, BUDGET.NET)
Join Structure:
----------------
SALES.ITEM = PRODUCT.ITEM
SALES.MONTH = BUDGET.MONTH (to give me that month's budget)
PRODUCT.DPEARTMENT = BUDGET.DEPARTMENT (to give me that department's budget)
Note: I am creating this report only for one department, so I have already filtered for that department
Some points to note:
---------------------
- The BUDGET tables contains budget amounts on a monthly basis for every department, going back 3 years
- When I compare YTD Sales with YTD Budget, I simply aggregate all the budget amounts from January to current month and compare that YTD Actual Sales (or Returns or Net)
Challenges:
------------
- Do I create ONE major join which selects records going back to Last Year January. Then, using grouping, running totals, and other functions/formulaes should I create the above desired output. Or should I create a bunch of sub-reports; one for YTD, one for MTD, and one LYYTD, and one for LYMTD. And then put all these sub-reports together into a main report. What do you guys think?
- If I use ONE major join, the drawback is that my first grouping is by the IMPRINT field and not the MONTH field of SALES table, and that doesn't allow me to aggregate the BUDGET.GROSS field using a running total (with a evaluation condition on "change of BUDGET.MONTH) because I am not grouping by MONTH field but rather the IMPRINT field
- If I use ONE major join, then I can't show YTD and LYTD side by side in a table. The output for each year would show one below the other, since I will eventually have to group by the SALES.YEAR field to show for both YTD and LYYTD.
- Generally speaking, what is the overhead with having too many sub-reports.
- I'll appreciate any helpful comments.
PS: SynapesVampire !!! this is for you. As for the rest of you, I don't expect you to answer this thread since its too wordy, and I know that you all have FULL TIME jobs and FULL TIME PERSONAL LIVES to live.
L8s
Mirogak.
I am trying to create a high-level sales dashboard in BO Crystal Reports XI (version 11.5). It's dashboard that not only gives metrics at a high-level but also lets you do drill-down analysis to get to the low-level details.
Platform:
---------
Database: Oracle 8i
Reporting tool: BO CR XI
Portal: (which uses Crstyal Reports Server)
Client requirements:
----------------------
- a YTD and LYYTD view of sales compared with Budget data of all the months starting from January to current month
- a MTD and LYMTD view of sales compared with Budget for that month
- Drill-down requirements: Department > Imprint > Subject > ISBN
(I work for a publisher, and we sell books)
Drill-down levels/Desire Output:
------------------
Level 1:
YTD_Sales YTD_Budget LYTD_Sales LYTD_Budget
Level 2:
Imprint_A Imprint_B Imprint_C
Level 3:
Subject_X Subject_Y Subject_Z
Level 4:
This is the lowest level of details at the product level.
Note: Imprint and Subject is a hierarchical organization of our products, which are primarily books
Data Sources:
-------------
SALES table
* relevant columns (SALES.DATE, SALES.MONTH, SALES.ITEM(ISBN#), SALES.GROSS, SALES.RETURN)
PRODUCT table
* relevant columns (PRODUCT.ITEM, PRODUCT.DEPARTMENT, PRODUCT.IMPRINT, PRODUCT.SUBJECT)
BUDGET table
* relevant columns (BUDGET.DEPARTMENT, BUDGET.MONTH. BUDGET.GROSS, BUDGET.RETURNS, BUDGET.NET)
Join Structure:
----------------
SALES.ITEM = PRODUCT.ITEM
SALES.MONTH = BUDGET.MONTH (to give me that month's budget)
PRODUCT.DPEARTMENT = BUDGET.DEPARTMENT (to give me that department's budget)
Note: I am creating this report only for one department, so I have already filtered for that department
Some points to note:
---------------------
- The BUDGET tables contains budget amounts on a monthly basis for every department, going back 3 years
- When I compare YTD Sales with YTD Budget, I simply aggregate all the budget amounts from January to current month and compare that YTD Actual Sales (or Returns or Net)
Challenges:
------------
- Do I create ONE major join which selects records going back to Last Year January. Then, using grouping, running totals, and other functions/formulaes should I create the above desired output. Or should I create a bunch of sub-reports; one for YTD, one for MTD, and one LYYTD, and one for LYMTD. And then put all these sub-reports together into a main report. What do you guys think?
- If I use ONE major join, the drawback is that my first grouping is by the IMPRINT field and not the MONTH field of SALES table, and that doesn't allow me to aggregate the BUDGET.GROSS field using a running total (with a evaluation condition on "change of BUDGET.MONTH) because I am not grouping by MONTH field but rather the IMPRINT field
- If I use ONE major join, then I can't show YTD and LYTD side by side in a table. The output for each year would show one below the other, since I will eventually have to group by the SALES.YEAR field to show for both YTD and LYYTD.
- Generally speaking, what is the overhead with having too many sub-reports.
- I'll appreciate any helpful comments.
PS: SynapesVampire !!! this is for you. As for the rest of you, I don't expect you to answer this thread since its too wordy, and I know that you all have FULL TIME jobs and FULL TIME PERSONAL LIVES to live.
L8s
Mirogak.