Limit on the No of fields in a crosstab

[mgakhar]

I have a report with a crosstab which has approx ~100 columns. When I run the report, which takes about 8 minutes to run it terminates before it can display the output.

However, if I place all these 100 fields directly in the detail section of the report it displays the output to me.

I've installed all the service packs for CR10 but the problem still persists.

Any idea what the problem could be? Or is there a limit on the number of fields we can have on a crosstab?

Thanks,
MG.

 

[synapsevampire]

A cross-tab doesn't return detail at the detail level data, they aren't the same thing.

Ratehr than explaining what you tried and what doesn't work, try posting what you have and what you want...

Database/connectivity
Example data (show the appropriate part of the recordset returned)
Expected output (what should be the result based on the above?)

-k

 

[mgakhar]

Synapse,
Like I mentioned in my previous post, I have a crosstab with approx 100 fields which I have placed in my report header section. When I run my report, CR abruptly terminates without displaying any output.

I know there is no problem in my query coz if instead of using a crosstab, I simply dump all 100 fields into the Detail section, it displays all records.

Its only when I use a crosstab to display this data that CR simply shuts down. The reason that I need to use a crosstab is explained in this thread -

http://www.tek-tips.com/viewthread.cfm?qid=1165931&page=1.

Thanks,
MG.

 

[synapsevampire]

Your link to the other post shows a few columns, not 100+, I don't see the correlation.

In either case, you might consider building out a more intelligent data source using a View or SP rather than hacking your way through it this way.

As for Crystal terminating, note that you've posted NOTHING technical about your database type of connectivity, and some types will crash Crystal.

From what I see of the rpevious post, your requirements seem simple, but that has nothing to do with this post as far as I can see.

Try posting technical information and less text descriptions:

Crystal version
Database/connectivity used
Example data
Expected Output

I don't address problems from a purely Crystal standpoint as that's often the least efficient means and doesn't allow for long term thinking.

-k

 

[mgakhar]

CR Version - 10
Database - Proprietary OODB

the reason I provided the link was to provide a reason for using Crosstab instead of simply dumping all fields in teh detail section of the report. I dont want to post all the 100+ fields on this post as I see no value to it. The database doesnt allow for SP's or view's. But I have to display all 100+ fields in the report.

If instead of adding all 100 fields to the crosstab, I simply add say 50 fields to crosstab and run, it works fine. Thats why I asked the question if there is a limit to the number of fields that a crosstab can handle???

Also there doesnt seem to be any issue with the query or with the database connection. Like I mentioned if instead of using a crosstab, I put all 100 fields into the details section, the report works fine. Its only when I add all 100 fields to the crosstab and execute the report does the Crystal Report terminate.

So obviously there seems to some sort of a limitation with using crosstab for a huge number of fields.

If there is a fix or maybe some setup that needs to be done to resolve this?

MG

MG.

 

[synapsevampire]

I think that memory will limit you rather than Crystal.

A shame that you refuse to provide a meaningful example of what you have and what you need. I never suggested that you post 100 fields here, I suggested that you supply example data and expected output rather than arbitrarily trying to determine the architecture to be used.

Cross-tabs aren't intended to be used as detail dumps, I think that you misunderstand their purpose. They pivot data and perform aggregate functions in a table format.

If you just want 100 columns laid out alongside each other, select a printer such as the HP3500 CPS and place it in landscape (one of the settings allows for about 100").

Now run the report wizard for a standard report and select all 100 fields, it will lay them out automatically alongside each other.

-k

 

[mgakhar]

Synapse -
I cannot simply use a standar report for this purpose.

The reason that I had included a previous link was for the purpose of providing a sample data -
Output of my query is like this -

Main Item Component Qty Supplier Price
Car Tire 4 ABC 200
Car Doors 4 XYZ 1000
Car Engine 1 PPP 5000
Bike Tire 2 ABC 200
Bike Engine 1 PPP 5000
Cycle Tire 2 ABC 200

However, in addition to these 5 columns, I have another ~95 columns which have data related to the column "Component".

My output should look like this -

Component Car Cycle Bike Supplier Price
Tire 4 2 2 ABC 200
Doors 4 0 0 XYZ 1000
Engine 1 0 1 PPP 5000

plus another ~95 columns.

You mention that it could be a memory related issue. Correct me if Im wrong, but wouldnt memory be an issue if instead of using a crosstab, I simply dumped all 100 fields in the detail section and ran the report? In essence Im still reading the same amount of data. Or maybe using crosstab, CR does a whole of calculations which get suppressed??

Thanks,
MG.

 

[Turkbear]

Hi,
NO..X-tabs are more memory consuming because they use are reformatting and summarizing the data not just displaying it..That takes memory ( and temp directory space)..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Right, cross-tabs take up MORE memory because they are performing aggregate functions and custom formatting. Dumping lots of fields doesn't take much memory.

Anyway, you might use a cross-tab here if you feel it works, but remember that you will output every combination of fields that you have uniqueness on.

In youir example, the component becomes the row, and the main item becomes the column, you get rid of the qty and append the supplier and price to the end of the main item columns, is that correct?

If so, you might be able to generate a UNION ALL query in a command object to pivot the data into a more usable format and then add in a subreport for part of the data, anyway.

-k

 

[h0h0h0]

Yeah why can't you aggregate some of it on the serverside in a SQL Query?

 

[synapsevampire]

hohoho: I had suggested this, but apparently the proprietary OODB won't allow for it...

-k

 

[h0h0h0]

stupid question. If you add the second set of 50 fields does it run? Wondering if one of the aggregations is flipping crystal out.

 

[mgakhar]

Actually I've been adding 10 fields every run and now I've reached 100 fields and so far its been working. But I want to see if there is a better solution.

Synapse - To answer your question (In youir example, the component becomes the row, and the main item becomes the column, you get rid of the qty and append the supplier and price to the end of the main item columns, is that correct?
) - Main Item becomes the column, Qty field is the summary and the remaining 100 odd fields are in the row.

I dont quite understand your suggestion of using a Union All query.

Manish.

 

[synapsevampire]

In your Database Expert, see if you can select the Add Command under your connection.

Assuming that "Qty field is the summary " means that it is a count of the mainitems:

You might build out a better datasource, since you are unable to do so on the database (which is what should be done).

It's time consuming though as it would be something like:

select field1 'source', mainitem, count(mainitem)
from table
group by field1, mainitem
union all
select Field2 'source', mainitem, count(mainitem)
from table
group by field2, mainitem
union all
select Field3 'source', mainitem, count(mainitem)
from table
group by field3, mainitem
etc...

Now you have counts of the mainitem for each of the 100 fields.

In the cross-tab, inserting the now single Source field as the row, Mainitem as the column, and count as the summarized field should now produce what you seek.

The problem is that your current data isn't in a very usable format, and doesn't appear to fit the busines model very well. The 100 fields appear to be related so they are probably better served grouped together in a like entity.

You can also do this in code to produce a recordset in some standard format, or in a conventional SQL database by using a View or Stored Procedure, the advantages being that you now have an intelligent data source thatcan be used across multiple data sources.

Anyway, it appears to be working for you, so just go with the cross-tab as is.

-k