Solved

Trouble with cross tab

Posted on 2011-03-04
7
597 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports 11 Ignoring Parameters 3 68
display only completed quarters in SQL script 4 48
Speed Up Crystal Report 5 47
Crystal Reports Date Calculation 10 36
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. …
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

21 Experts available now in Live!

Get 1:1 Help Now