Cross Tab Count Divided by X

[JCruz063]

Hi All,
I have a CR 10 (Developer Edition) cross tab that displays the "Count" of some field in my table (I'm reporting off a SQL Server database, using Visual Studio .NET with C#). Instead of displaying the "Count" summary, I would like my cross tab to show that Count divided by 3. Actually, the Count would not be divided by the constant 3, but it would be divided by some variable that is subject to change in each column of the cross tab (and it's likeyly to be 3). How do I go about doing that?

I tried adding a formula, called MyFormula, that looks like this:

Count({MyTable.MyField})/3;

But, this approach suffers from two shortcomings. (1) I'm always dividing by 3 and I should divided by the number of times that the column value appears in the Row value. (2) When I add this to the Summary fields of the Cross Tab, the Cross Tab says "Sum of @MyFormula". When the cross tab prints, I get numbers that are way too large. I'm expecting to get numbers no greater than 60. When the cross tab prints, I get data in the millions, like 7,866,765.

Can anybody help me out?

Thanks!

JC

 

[pelajhia]

'number of times that the column value appears in the Row value'

...I am not sure this is very clear, at least to me.
Can you provide some example data?

ex:
col1 col2 your_formula_col
1 1 expected result
2 1 expected result
3 2 expected result

 

[JCruz063]

pelajhia,
Thanks for your reply...

It's very simple. I may have wrote it wrong but this is what it means: When you create a cross tab (with the cross tab expert), you put fields into a list box called "Rows". These fields appear on the left of the cross tab as, surprise, rows. You also have to put fields into another list box called "Columns", and those fields appear as the columns of the cross tab. The data that appears in the cross tab is what you put in the list box called "Summary". The fields in the Summary list box are usually a Count, Sum, Average, etc, and every summary is computed for each match of Row X Column. So, for example, when the value in the Row = "John Smith", and the value in the column is "April 29, 2004", the cross tab will give you a value, let's say a Count, that will be the number of times that the column value, "April 29, 2004", appeared in the row value, "John Smith". The column and row fields may come from the same or different tables in the database, but the idea is the same.

If we assume that the field name where the value "John Smith" is found is "Name" and the field name where the value "April 29, 2004" is found is "Date", then we could say this a little differently: the number of records with the Date value "April 29, 2004", where the Name is "John Smith". I hope this clears things up a little bit. Keen in mind that all I'm saying is what a cross tab naturally does! So, if you know what a cross tab does, you know what I'm trying to say - perhaps I'm just not saying it in the clearest way .

My cross tab has only one row field and one column field and I would like to print, for each match of Row X Column, the Count of the summary field divided by some variable. Any ideas?

Thanks again!

JC