Solved

Trouble with cross tab

Posted on 2011-03-04
7
602 Views
Last Modified: 2012-05-11
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
Comment
Question by:bmhardy_05
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 19

Expert Comment

by:GJParker
ID: 35036603
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
 

Author Comment

by:bmhardy_05
ID: 35036974
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35037136
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 19

Accepted Solution

by:
GJParker earned 500 total points
ID: 35037181
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
 
LVL 19

Expert Comment

by:GJParker
ID: 35037247
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
 

Author Comment

by:bmhardy_05
ID: 35037268
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
 
LVL 19

Expert Comment

by:GJParker
ID: 35037288
No problem, glad you got it sorted
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question