Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 604
  • Last Modified:

Trouble with cross tab

I am trying to create a cross tab report to show a list of my clients down the left, the tax week across the top and then the total charge and profit as the summary fields.

I have achieved the totals I wanted by grouping by client in the main report and inserting the cross tab into the group header. Unfortunately I end up with a format as per the attached screenshot (Cross Tab 1) where if there is no charge for a week it skips the cell altogether. It also summarises in a seperate cross tab for every client.

If I move the cross tab into the report header then I get the exact format I want (Cross Tab 2) but the running totals for the charge and margin stop working and only pick out the first record in the DB as opposed to totalling it all.

Any ideas how I can get the totals from the first in the format of the second?
Cross-Tab.bmp
Cross-Tab-2.bmp
0
bmhardy_05
Asked:
bmhardy_05
  • 4
  • 2
1 Solution
 
GJParkerCommented:
If you're using runnign totals for the summary values then you'll probably need to move your crosstab from teh report header to report footer section.

If that doesn't do the trick then let us know how you're crosstab is built, include detailsof any formulas and RT's, also include information of what groups you have in the report.  If you can it woudl probably help to attach your report with saved data to the question.
0
 
bmhardy_05Author Commented:
I tried putting the cross tab in the report footer and it doesn't work. It appears the same as wehn I put it in the report header.

The cross tab has the payroll week as the only column which comes as a field direct from the database. The row is just the client name which also comes as a field direct from the database.

The summarised fields are both running totals, one of which is created from a formula field They are created as follows:

rtTotalCharge (Running Total)
Field to Summarise: rspNetCharge (from DB)
Type of summary: sum
Evaluate: For each record
Reset: On change of field - rspPayrollWeek (from DB)

rtTotalMargin (Running Total)
Field to Summarise: @ftMargin (Formula field)
Type of summary: sum
Evaluate: For each record
Reset: On change of field - rspPayrollWeek (from DB)

@ftMargin (Formula Field)
{rsp.NativeNetCharge}-{@ftCost};

@ftCost (Formula Field)
{rsp.NetPay}+{rsp.ErsNI}+{rsp.HolidayPay};


When the fields calculate as I want them to it's when the report has been grouped by client name and the crosstab is sitting in the group header.

I have attached a slightly amended version with my clients names removed.
School-YTD-Comparison-V3.xls
0
 
mlmccCommented:
WHy are you using running totals?
They are calculated as the report is printed and thus are not available as totals until the report is complete so they can't be used in a cross tab.

Does the Excel file shw the raw data or what you want?

mlmcc
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
GJParkerCommented:
I assume you're using running totals because you have some duplication of data, if not then youi shoudl eb able to just use normal summaries.

If you do need to use running totals then change you're reset field to the client group instead of the week field
0
 
GJParkerCommented:
mlmcc

That statement is not correct, crosstabs and running totals are both evaluated in pass 2 during report processing (whileprintingrecords) and therefore RT;'s can be used in crosstabs as along as the data is grouped or sorted in the correct manner.
0
 
bmhardy_05Author Commented:
I see now. I think I was overcomplicating it as I had it in my mind to create the weekly totals for the client to then insert into the cross tab. The cross tab actually summarises the data for me....quite embarrassing on reflection.

Thank you very much for your help. In order to simplify I have removed the running totals from the cross tab altogether and replaced them with the original databse field for charge and formula field for margin.
0
 
GJParkerCommented:
No problem, glad you got it sorted
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now