automationamateur
Board Regular
- Joined
- Jan 3, 2011
- Messages
- 166
I have a table with underlying data that looks like this (see 1st screen shot below, if this isn't the correct way to attached a screen shot or you can't see screen shot let me know). I currently produce a report out of excel that look like this (see 2nd screen shot below). I want to create a report that looks just like my excel report. On my excel report I have three sections, the first is revenue, with "buckets" running down the left and months across the top, second section is expense, the third is margin ($'s and %), the 3rd portion is calculated 'margin $' = revenue-expense and 'margin %' = (revenue-expense)/revenue. Simple enough. Looking at my excel report I think I need to create a crosstab query. I have searched all over the web and found leads in various directions.
My Desired end result: I will have a form that user selects a client # and a date range (ie Jan 2012 - Mar 2012), form will have an on click event that fires report (report will fire underlying query). Report will show the format (shown below) with only the given months (as column headers) that the user selects.
What I have done so far: I have built a form with client # combobox and date boxes. My query that will feed the desired form has it's parameters tied to this form.
My problem:
-Do I need two crosstab queries, one for revenue, one for expense (I've already done this just in case)?? -How do I create a crosstab report that shows revenue in one section, expense in the next so I can then add a 3rd margin section that has formulas calculating margin off of revenue and expense. (if you suggest subreports, is it possible to do calculations across multiple subreports?)
-How do I get my report column headers to dynamically change based on user defined date parameters. (ie one report run could be 2 months, the next could be 12, I would like the report to expand and contract given the # of months, if possible)
Please help!!!
As always help is greatly appreciated!
-- removed inline image ---
-- removed inline image ---
My Desired end result: I will have a form that user selects a client # and a date range (ie Jan 2012 - Mar 2012), form will have an on click event that fires report (report will fire underlying query). Report will show the format (shown below) with only the given months (as column headers) that the user selects.
What I have done so far: I have built a form with client # combobox and date boxes. My query that will feed the desired form has it's parameters tied to this form.
My problem:
-Do I need two crosstab queries, one for revenue, one for expense (I've already done this just in case)?? -How do I create a crosstab report that shows revenue in one section, expense in the next so I can then add a 3rd margin section that has formulas calculating margin off of revenue and expense. (if you suggest subreports, is it possible to do calculations across multiple subreports?)
-How do I get my report column headers to dynamically change based on user defined date parameters. (ie one report run could be 2 months, the next could be 12, I would like the report to expand and contract given the # of months, if possible)
Please help!!!
As always help is greatly appreciated!-- removed inline image ---
-- removed inline image ---
