cancel
Showing results for 
Search instead for 
Did you mean: 

Adding a dummy column for text in crosstab report

Former Member
0 Kudos
1,044


I created a report using crosstab to show trended months per column & details per row.  Now I'm trying to add additional columns to the crosstab report to display other pertinent info (not calculations, just text pulling from the database).  I read from various threads in the forum that I can create dummy columns to accomodate this, but I don't know how to do so.  Can someone please guide me in doing so?  If creating dummy columns is not a solution, please advice on what's the best way to add text columns to a crosstab report.  Any help is truly appreciated.  Thank you.

Original report:

Emp Name     Jan     Feb     Mar     Apr     May     Total

Doe, John     20     17      30       28     33       128

Wish to accomplish:

Emp Name     Emp#     Dept#     Yrs Employed     Jan     Feb     Mar     Apr     May     Total

Doe, John   12345     6010          8             20     17      30       28     33       128

Accepted Solutions (0)

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Fione,

While you can add columns (a.k.a Calculated Member) to an existing structure of the crosstab, there isn't an 'easy way' to add values to those columns from fields that aren't part of the crosstab.

If you aren't worried about the look of the crosstab, then you can try this:

1) Add the additional fields as 'Rows' in the crosstab

2) Go to the Crosstab Expert > Customize Style > Select the last field under 'Rows' and check the 'Suppress Subtotal' option.

3) Repeat Step 2 for upto the second field from top under 'Rows'

4) You can then manually add a Text Object on top of these new (supposedly) columns for headings.

-Abhilash

Former Member
0 Kudos

Thank you for your prompt response.  I really appreciate it.

I understand that I can use Calculated Member to insert columns, but what I'm trying to accomplish is to add columns for TEXT.  I'm not doing any calculations with the added columns.  Can I do that with Calculated Member?  How do I populate the added columns with "string" data pulling from the same database?  Please advice.  Thank you.

abhilash_kumar
Active Contributor
0 Kudos

Hi Fione,

I don't have access to CR at the moment but this is how I can think of doing this:

1) Create a Subreport that points to the same table as the Main Report

2) The Subreport should return the same number of Employees and in the same order as the Main Report - you can do this by creating a group on the Emp field just like the 'Row' in the Crosstab

3) Create separate array variables - one each for Emp#, Dep# etc that accumulates values for each employee and inserts it against an index of the array

4) Suppress all sections inside this subreport and place this Subreport on the Report Header of the Main Report

5) Reduce the size of this subreport so that it isn't bigger than a dot and DO NOT suppress the section that holds this subreport

6) Insert Calculated Members in the Crosstab for each column you need

7) Use the array variable meant for the respective column. You just need to print the value at each index of the array based on the 'currentrowindex+1' function.

-Abhilash

Former Member
0 Kudos

Thank you for the detailed instructions in resolving the issue.

Is this the ONLY way I can add additional "text" columns into a crosstab report?  It looks pretty complicated & each time I need to insert another text column to the report, I have to go thru these steps again.  And the more subreports I insert into the main report, the likelihood of having error increases.

Is there any other "go-around" ways to accomplish the same result?  Please advice.  Thanks.

abhilash_kumar
Active Contributor
0 Kudos

This is the only way I can think of doing this. Let us hope someone else has other ideas.

cc: , , .

-Abhilash

DellSC
Active Contributor
0 Kudos

The "Calculated" columns are for of the summary (intersection of columns and rows) part of the cross-tab so that won't get you what you're looking for.  Since it appears that you want the data as part of the employee info, here's what I would try:

1.  In the Cross-tab Expert, add the Emp# and Dept# fields to the "Rows" list.

2.  Since each of these only occurs once, turn off subtotals for the Emp# and Dept# "groups".

You should now see the information that you're looking for. in the format that you want.

-Dell

ido_millet
Active Contributor
0 Kudos

Yes, there is a simpler way.  use the 'Display String' property expression.

Former Member
0 Kudos

Ido Millet,

With me being so "Green" as a Crystal Reports user, I'm not sure I know what a "Display String" property expression is.  Can you please give me the detail steps on how to use it so that I can get the desired output?  I'd really appreciate it.  Thank you.