Solved

Trouble with cross tab

Posted on 2011-03-04
7
596 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
  • 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 100

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now