Manual Crosstab?

[ciscowiz]

I am using CR9 with SQL Server 2000. I need a report that I thought would be a cross-tab but I am getting way to many records returned. The columns are multiple instances of one field and a lone instance of another field which is probably the issue. The columns are for multiple instances of the Description Field based on one product (DisplayAds) and the other lone column I need is for the other product (ClassifiedAds) with no description field for it. Basically it will look like this with the first 3 columns listed below the description and the 4th is the product. The report will potentially have more Description columns but the ClassifiedAd Column will always be just the one instance. The data I need is a Sum and a count for the description columns and just the count for the ClassifiedAds:
Any Cars RealEstate.... ClassifiedAds Totals
IssueDate
1-1-5 Sum($) Count(Orders)
Count(Orders)
1-3-5 ....
1-7-5 ....
...
Totals


I dont think this can be done with a Regular Cross-Tab but maybe it can. It seems like it needs to be done manually with groups being suppressed and a bunch of RunningTotal or Formula fields which is what I need some help with. If you need anymore info, let me know. Thanks in advance!
Bryan

 

[lbass]

It is unclear whether Display Ads and Classified Ads are members of one field or are two distinct fields.

If these are separate fields, then try creating two separate crosstabs, placing the Classified Ad one on the left. Then on the second crosstab, you can eliminate the grid over the labels and suppress the labels and position it so that it aligns with the first. For totals, you could create another crosstab that eliminates the column field, but uses the same row fields.

-LB

 

[ciscowiz]

DisplayAds and ClassifiedAds are the two instances of the field "Product". However, "DisplayAds" will not be a column, just the various "Description" field instances associated with the "Product" field. Only "DisplayAds" have a "Description". The "Product" field has another instance, "ClassifiedAd", which is not associated with any "Description" and should be a column by itself along with the multiple columns of the "Description" field.

So the columns would be:
fieldescription(Many instances) Fieldroduct(2 possible but only "ClassifiedAd" should be displayed)

 

[ciscowiz]

I did create 2 crosstabs before I posted here but that resulted in over 500,000 records returned, I had to stop it before it even finished. At most I should have around 12,000 records.

 

[ciscowiz]

One thing I forgot to mention that could be important:
On the cross-tab Setup Dialog the field added to the Columns box is "table.Description" but I added group options. I specified the order to only diplay the "Descriptions" I want and the rest are lumped into a group called "Special Sections". All table.Product of type "DisplayAd" have a description but the table.Product of type "ClassifiedAd" does not have a description. This means that the "ClassifiedAds" are being lumped into the "Special Sections" Column which should only be counting "DisplayAds".

 

[lbass]

If you can limit your records in the selection formula using:
(
isnull({table.description}) or
{table.description} in ["Desc1","Desc2","Desc3"]
) and
{table.product} in ["Classified Ads", "Display Ads"]

...then you could create a formula:

if isnull({table.description}) then "Classified Ads" else
{table.description}

...as your column field. This assumes that the only nulls in the description field occur when the product is "Classified Ad".

Or you could use a formula like the following for the column:

if {table.product} = "Display Ad" then {table.description} else "Classified Ad"

-LB