Cross Tab issue

[Former Member]

Hello,

I have a Cross Tab report, and I need to add another column which would appear only once as a last column after all columns in the CrossTab. How can I do this? Please let me know if you need more explanation.

Thank you,

Tanya.

[Former Member]

What are you looking to add? A constant, a calculation, blank space for manual notes, other?

Generally, you can add columns to a cross tab by basing the cross tab on an SQL Command, in which you use UNION to force the extra data needed for the column to be returned from the database.

HTH,

Carl

[Former Member]

I have an SQL statement which I am passing into Crystal through the Command.

My CrossTab is based on 2 fields from the Command - Columns are Accounts and Rows are Porperties. I also need to add 1 more column for SQFT at the very right as a last column based on another field from the Command, and I need it ONLY once in the report, see bellow:

Act1 Acct2 Acct3 Acct4 ACct5 SQFT

property2

Property3

Property4

[Former Member]

I'm not 100% sure if this will work, but here's an idea...

It looks like you are suppressing row totals, right? Assuming so, put the following in the Detail (or property id group header/footer) format (basic syntax):

WhilePrintingRecords
global propid(1000) as string
global sqft(1000) as number

dim i as number

i = 1
while i < 1000 and propid(i) &lt;&gt; " " and propid(i) &lt;&gt; {command.property}
  i = i + 1
wend

sqft(i) = {command.sqft}
formula = ""

Then in the Display String formula for the row total (stop suppressing it), put:

global propid(1000) as string
global sqft(1000) as number

dim i as number

i = 1
while i < 1000 and propid(i) &lt;&gt; " " and propid(i) &lt;&gt; GriRowdColumnValue("command.property")
  i = i + 1
wend

formula = cstr(sqft(i), "0")

Unfortunately, this has a limit of 1000 properties, which is the array size limit. You could get around that by making the arrays strings that contain lists of property id's and square feet numbers (which I will leave as an exercise :->).

HTH,

Carl

[Former Member]

Hi Carl,

Thank you for your reply, but I actually found another solution. I just created one more Cross_tab report for SQFT and put it next to the first one. It works just fine. Now I am trying to figure out how to put a shading on every other line in the Cross Tab. Is it possible?

Thanks,

Tatyana.

[Former Member]

Yes, you can shade every other line. It's a bit convoluted, but it works.

Here's a formula that I have that does just that (basic syntax):

global Stores() as number
dim i as number
dim curStore as number
 
' Get store in current row
curStore = gridrowcolumnvalue("Command.Store_Number")
 
if curStore in Stores then
  '  Store already in array, use it's index.
  i = 1
  while Stores(i) <> curStore
    i = i + 1
  wend
else
  ' Store not in array; add it and use the new index
  i = ubound(Stores) + 1
  redim preserve Stores(i)
  Stores(i) = curStore
end if
 
if (i mod 2) = 0 then 
  formula = crSilver  ' If the array index is even
else
  formula = crWhite  ' If the array index is odd
end if

This formula goes in the Background Color formula in all of the crosstab cells that you want to toggle shading in. Obviously, replace field names etc. to meet your needs.

HTH,

Carl

[Former Member]

Hi Carl.

is "curStore = gridrowcolumnvalue("Command.Store_Number")" the only place to replace the field name? I don't quite understand what you replace it with? It asks for a string. Please advise.

Thank you.

[Former Member]

By default, it is the field name used in your cross tab row or column header, minus the braces. (There may be a way to change the field so something else is used, but I'm not sure how.) See the online help for the function for a pretty good (IIRC) example.

HTH,

Carl

[Former Member]

Whatevet I put there I get the same messageover and over:

"This is not the name of a grid group for the field being formatted".

Do I only need to change it in 1 place:

' Get store in current row

curStore = gridrowcolumnvalue("")

??

[Former Member]

Yes, only one place. If you hover your cursor over the field in the grid, it will tell you exactly what should go between the quotes. My tooltip states "Row #2 Name: Command.Store_Number (number)".

HTH,

Carl

[Former Member]

Hi Carl, this CrossTab brings me a lot of problems. As I mentioned before the rows are properties and the Columns are accounts, one column is SF. When I run this report the SF comes back correct if I devide it by the count of accounts. However when I run this report for multiple properties the SF calculation is wrong. I have no idea what to do anymore. Please advise. Thank you in advance.

Tatyana.

[Former Member]

Is Square Feet constant for a property? (I'd think so...) If so, use Maximum() in the grid instead of Sum().

HTH,

Carl

[Former Member]

I came up with a better solution of this problem, and I would like to close this issue.

Thank you.

[Former Member]

Please share your better solution!

[Former Member]

Carl,

I had to modify my cross-tab by adding the SF to the Rows under Property and then suppressing rows grand totals and all unwanted cells.

Thanks,

Tatyana.