• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Cross tab report woes...

Hi,

Given the sample data:

215AK      215AK4602      1      0      Stock Room
215AK      215AK4603      3      0      Stock Room
215AK      215AK4604      0      0      NOT COUNTED
466IR      466IR1002      0      0      0              NOT COUNTED
466IR      466IR1003      1      0      0              Shop Floor
466IR      466IR1003      1      0      0              Stock Room

Using a pivot table in Excel, I have managed to convert this into the format I require. This looks something like the following (ignore the figures, they do add up correctly in the real data - if it will help I can email the excel file):

215AK      215AK4601  Sum of Counted Quantity   0         7      1      7
              Sum of RJ Quantity               0         0      0              0  
      215AK4602  Sum of Counted Quantity   1         1           6              8
                                  Sum of RJ Quantity               0        0      0              0
...

215AK Sum of Counted Quantity                               0         8                 7      15
215AK Sum of RJ Quantity                     0         0      0      0
466IR       466IR1003    Sum of Counted Quantity  0         2          8                6

etc.

However, I do not seem to be able to replicate this in a Crystal report using a cross tab report. In addition, the report I have only lets me perform a count on each row summary (not a sum) which is pretty useless.

Any ideas?

Thanks.
0
paulsidebottom
Asked:
paulsidebottom
  • 11
  • 10
1 Solution
 
frodomanCommented:
You can only perform a count because it isn't numeric data in Crystal - this may be because of the way it's stored in your database or how you've set up your report, there's no way to tell w/out looking at the report.

0
 
paulsidebottomAuthor Commented:
Thanks, I have changed my schema data type to int and that appears to have resolved the sum problem - although I'm still struggling with the cross tab: the figures dont even add up correctly!

Any ideas? thanks.
0
 
frodomanCommented:
Make sure you've changed all of the summary types to SUM and you don't have any COUNT or MAX, etc. left in there.  I've never seen Crystal have a problem with basic math so I highly expect it's something that's set up incorrectly.  If you can tell me how you have the crosstab expert set up and what's wrong I'll let you know if I see anything obvious.
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
paulsidebottomAuthor Commented:
Cheers - I've checked the fields and all the summary fields are set to sum and not count etc. however, interestingly I've noticed that the summary fields are displaying 1 where there is a value in that "cell" - i.e. it looks like a count of the number of fields not the actual values.

What I effectively need is a way of literally just putting the data in the grid not performing any sort of count/sum on the data.
0
 
frodomanCommented:
So you just want a crosstab but with the sums suppressed?
0
 
paulsidebottomAuthor Commented:
think so - I want a cross tab, with the values shown in the fields (not sums) - if i dont add any summary fields then all i get is the total fields which isnt much use.

Thanks.
0
 
frodomanCommented:
Try this:  Rt-click on the crosstab and select Crosstab Expert.  Click the "Customize Style" tab and the check the boxes for Suppress Row Grand Totals and Suppress Column Grand Totals.

If that doesn't give you what you want, then maybe you should stick with a standard report and forget the crosstab option?
0
 
paulsidebottomAuthor Commented:
Tried that, but the figures in the body of the report are still wrong (the totals would also be useful so I dont really want to suppress them).

I would use a standard report but dont believe its possible to achieve this without a cross tab report?

As per previous sample:

215AK     215AK4602     1     0     Stock Room
215AK     215AK4603     3     0     Stock Room
215AK     215AK4604     0     0     NOT COUNTED
466IR     466IR1002     0     0     0              NOT COUNTED
466IR     466IR1003     1     0     0              Shop Floor
466IR     466IR1003     1     0     0              Stock Room

I need the locations (Stock Room etc) to be displayed as the column headers with a sum for each article (the rows)(e.g. 466IR1003) to be displayed in each column. The report should be group by column 1 - e.g. 215AK etc.

Is this possible without resorting to a cross tab?

Thanks
0
 
frodomanCommented:
No, you will need a crosstab - sorry, but it was hard from your first posting for me to figure out what you wanted for the end result.

0
 
paulsidebottomAuthor Commented:
Apologies, its obviously difficult to explain without someone having the full picture. Beginning to wonder if its even possible with a cross tab.

Thanks,
0
 
frodomanCommented:
It sounds like it should be.  Can you let me know how you have the crosstab set up - go into the Crosstab Expert and tell me what's in the columns, rows, and summary boxes?
0
 
paulsidebottomAuthor Commented:
Its setup as follows:

Rows: ArticleNumber
Columns: Location
Summary Fields: Sum of Requested Count and Sum of RJ Qty

It looks to me like its treating the values as strings rather than integers hence I'm getting a 1 (boolean value?) in each field where their should be data i.e. it cant do a sum on a string.

That said, I figure the report wouldn't give me the SUM option if the field werent an integer?

Thanks
                           
0
 
frodomanCommented:
So {Requested Count} is one field and {RJ Qty} is another field, correct?  Just want to make sure I understand because the word "Count" threw me off a little...

Are there null values in the data that might be messing it up?
0
 
paulsidebottomAuthor Commented:
Correct, there are two summary fields - both integers.

There are no null values.

The following is the XSD I used to create the datasource (the report datasource is a .NET datatable):

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:mstns="http://tempuri.org/~vs411.xsd" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="http://tempuri.org/~vs411.xsd" id="NewDataSet" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="RequestedCountResults">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" name="Article" type="xs:string" />
        <xs:element minOccurs="0" name="CountedQuantity" type="xs:int" />
        <xs:element minOccurs="0" name="RJQuantity" type="xs:int" />
        <xs:element minOccurs="0" name="Location" type="xs:string" />
        <xs:element minOccurs="0" name="OriginalArticle" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element msdata:IsDataSet="true" msdata:Locale="en-GB" msdata:EnforceConstraints="False" name="NewDataSet">
    <xs:complexType>
      <xs:choice maxOccurs="unbounded">
        <xs:element ref="mstns:RequestedCountResults" />
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>
0
 
frodomanCommented:
Sorry, I'm completely stumped on this one.  The only thing I can suggest is to connect your dataset to a datagrid just to make sure it's loading what you think it's loading.  Other than that I have no idea what's going on...

0
 
paulsidebottomAuthor Commented:
Thanks - will try. On a side note, I wrote a routine to extract my datatable to CSV and then report off that - this seems to make the cross tab work perfectly! I therefore think it might be a problem with the way in which crystal interprets the schema files (possibly treating numerics as text at runtime).
0
 
frodomanCommented:
That's interesting...  I wonder if you're correct about everything being treated as text...

What datasource are you pulling your data from?
0
 
paulsidebottomAuthor Commented:
I'm using a .NET datatable - I used the schema shown above to generate the report definition.

I believe it is reading the schema correctly since when I changed the quantity columns to the integer datatype it allowed me to perform a sum (which was previously unavailable).

I now have another battle on my hands - since changing to a CSV file as the datasource, all was working fine but now I'm getting a prompt for username and password (which hasnt been set). This problem is made worse since I can no longer view the datasource from within crystal (I get a login failed exception)...
0
 
paulsidebottomAuthor Commented:
Sorted!

Changed back to using the dataset, turns out (as expected) crystal hadn't updated its schema to represent the change from string to int datatypes (even though performing a verify database insisted it was upto date).

Discovered this by loading the schema it was using into a .NET dataset and then using the datasets writeXMLSchema method. This showed that it was still set to string.

To fix, I just changed the datasource for my report to the schema generated from the dataset - this way the two tallied up.
0
 
frodomanCommented:
Sweet!
0
 
paulsidebottomAuthor Commented:
Thanks for your assistance.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now