Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trouble with cross tab

Posted on 2011-03-04
7
Medium Priority
?
603 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

705 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