Is this a cross-tab? 1

[stef315]

I've never done a cross-tab report so I'm not sure that's what I need or how to get started if it is. I have a table that has customer sales data in it. There is one column in the table for the year and one for the month. I want to display a chart for each customer that shows months 1-12 as rows and the years as columns.

Can anyone help me get started please?

Thanks!

 

[BobSuruncle]

This sounds like it would be a good use of a cross-tab.
Cross-tabs can only be placed in a Report Header/Footer and/or a Group Header/Footer.
When placed in the Report Header/Footer, the cross-tab will contain a summary for ALL records retrieved from the database.
When placed in the Group Header/Footer, the cross-tab will contain a summary for just the records that are in the group. One cross-tab would be generated for each group that appears.

To create a cross-tab, first decide where it is going to be placed. If there are already objects in the section where you want the cross-tab, then create a new section below that one. This new section will be used to hold the cross-tab.

On the Insert menu, choose Cross-tab. Depending on your version, you'll either get a Cross-tab Expert(v10 and prior) or you'll get an object attached to your mouse cursor(v XI).
If it's v XI, drop the object into the proper section, and then right-click it in the upper left corner and select "Cross-tab Expert"
If it's V10 or prior, you're already there.

From the "Available Fields" list, choose a field for your summarized field, choose a field for your Column (you said 'Year') and choose a field for you Row (you said month)

If both Year and Month are from Date or DateTime type fields, you'll need to click the "Group Options" button just below the 'Rows' and 'Columns' boxes and select the appropriate breakout for the date.

When you're finished, click the OK button.

If this is v10 or prior, you'll have the cross-tab object attched to your mouse cursor. Move to where you want to place your cross-tab and left-click to drop it in place.

If this is v XI, it's already in place and you're good to go.

Whenever you want to edit the contents of your cross-tab, right-click in the upper left corner of the object, in the "Dead Zone" where there are no cells, and choose "Cross-tab Expert".


Bob Suruncle

 

[stef315]

Hi, Sorry it took me a little bit to respond. I had to print this and go over it a few times. Thanks so much for the detailed post! I'm using version 10...I should have mentioned that before.

I followed your instructions and I think I got the data I want. I'm having difficulties formatting as it seems it only lets me apply different style templates! For instance, the row labels have way too much room. They are numbers 1-12 and there's room for like an entire sentence in the box! Also, I wanted to shade every other row. I tried the grayscale template but that makes everything so small. Is there a trick to formatting these things?

I put the crosstab in the group header of the customer group. So, it's first grouped by salesperson then by customer. I wasnt clear if it mattered whether the crosstab was put in the header or the footer.

Thanks again very much!

 

[dgillz]

Once you have the crosstab inserted, just use your mouse to resize the data element that is too big or small. This is very intuitive.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[lbass]

The crosstab data will reflect the group section it is in, but whether it is in the group header or footer is irrelevant.

Please see thread767-1154875 for a method for alternating row color. Because you are using a datefield for both column and rows, go to the customize style tab in the crosstab expert and select the datefield->alias for formula->enter: Month. In the formatting formula, use:

gridrowcolumnvalue("Month")

-LB

 

[stef315]

Hmm, it didn't seem to resize but I'll look at that other thread.

I discovered another problem. For some reason it doubles the amount that should show in the grid. If the sales was $1000 it shows $2000. If I make it average it comes out but then my totals are average also.

So, really I need the grid to be actual data then I'd like the row totals to be actual totals but the column totals I want average. Is it possible to individually change these?

I also need to have a column after the column average that takes the current year divided by the average to get a % of average. Is that possible?

Oh, and my columns show the years in ascending order can I change to descending?

I know I'm asking for a lot of help. Thanks in advance.

 

[lbass]

Since you apparently have row inflation, and with your other requirements, you're probably better off creating a manual crosstab. Create a formula {@month}:

month({table.date})

Insert a group on this and then select "customize group name using a formula" and enter:

monthname(month({table.date}))

Then insert a running total for each year which uses a sum of {table.sales}, evaluate based on a formula:

(
onfirstrecord or
{table.salestransactionID} <> previous({table.salestransactionID}
) and
year({table.date}) = 2006 //change the year in each running total

Reset on change of group ({@month}). Place the running totals in the group footer for month. Then suppress the detail section.

You need to sort by whatever field is unique to each transaction, and you should also place the running total in the detail section to make sure it is evaluating correctly. I used {table.salestransactionID} in the evaluation formula to represent that unique field.

You can then use formulas to create averages and percents. If you have only the one group, you can format the group footers to alternate color, by using a section color formula (section expert->group footer->color tab->background->x+2) of:

if remainder(groupnumber,2) = 0 then crYellow else crNoColor

-LB

 

[stef315]

Was out of town so I'm just now getting back to this. I'm still confused on how to do a manual crosstab. I did the first part of your instructions lbass but got lost at:

"Then insert a running total for each year which uses a sum of {table.sales}, evaluate based on a formula:"

My table does not have transactions that have to sum. Basically, it's a summary table that contains total sales for each month for each year. Relative columns are: CustomerNumber, Month, Year, SalesAmt. There is a seperate record for each month the customer had sales. I also have it linked to the customer master table just for assigned salesperson as first grouping is on that.

I wondered if I could do a SQL view that would the information how I need it first? Or, I need to understand this manual crosstab. I did find some other posts and instructions but I got lost on lose also.

If you (or anyone) can help me understand, that would be greatly appreciated.

Thanks

 

[stef315]

Wow! Late night and somehow I figured most of it out! I reread this thread a few times and also the FAQ on manual cross-tabs.

The only two things I have left to do I believe is the alternate line shading and ommitting zero lines. I need to suppress all lines where all 4 columns (I did current year plus 3 back) are zero. I then need to suppress all the group headers/footers where all the rows are zero also. I think I can manage these but feel free to offer any suggestions.

And...thanks again for everyone's help!

 

[lbass]

At the detail level (I thought you were only displaying group sections though), you can use a detail section suppresssion formula that adds the fields together to suppress if zero, as in:

{table.field1}+{table.field2}+{table.field3}+{table.field4} = 0

At the group level, you would use:

sum({table.field1},{table.groupfield})+sum({table.field2},{table.groupfield})+sum({table.field3},{table.groupfield})+sum({table.field4},{table.groupfield}) = 0

Because you have suppressed fields, the alternating color is a little trickier. Create a formula like this:

//{sum({table.field1},{table.groupfield})+sum({table.field2},{table.groupfield})+sum({table.field3},{table.groupfield})+sum({table.field4},{table.groupfield}) = 0

//{@grpcnt};:
whileprintingrecords;
numbervar grpcnt;

if sum({table.field1},{table.groupfield})+sum({table.field2},{table.groupfield})+sum({table.field3},{table.groupfield})+sum({table.field4},{table.groupfield}) > 0 then
grpcnt := grpcnt + 1;

Basically you are adding in the opposite of your suppression criteria into the count formula. Then go to the section expert->group header (or footer?)->color->background->x+2 and enter:

if remainder({@grpcnt},2) = 0 then crYellow else crNocolor

-LB

 

[stef315]

Thanks for responding again.

I got the group suppression right (yes, I was only displaying group sections, not details).

The three sections you posted about alternating colors I'm a little confused on. Is that 3 different formulas? Where do I place the formulas?

Thanks

 

[lbass]

Sorry, I should have proofed the post better. {@grpcnt} should be placed in the group section that you are alternating color on. I accidentally left in a draft section of the post. The post should have read:

At the detail level (I thought you were only displaying group sections though), you can use a detail section suppresssion formula that adds the fields together to suppress if zero, as in:

{table.field1}+{table.field2}+{table.field3}+{table.field4} = 0

At the group level, you would use the following for section suppression:

sum({table.field1},{table.groupfield})+sum({table.field2},{table.groupfield})+sum({table.field3},{table.groupfield})+sum({table.field4},{table.groupfield}) = 0

Because you have suppressed fields, the alternating color is a little trickier. Create a formula like this:

//{@grpcnt} to be placed in the group section you want to alternate color on:

whileprintingrecords;
numbervar grpcnt;
if sum({table.field1},{table.groupfield})+sum({table.field2},{table.groupfield})+sum({table.field3},{table.groupfield})+sum({table.field4},{table.groupfield}) > 0 then
grpcnt := grpcnt + 1;

Basically you are adding in the opposite of your suppression criteria into the count formula. Then go to the section expert->group header (or footer?)->color->background->x+2 and enter:

if remainder({@grpcnt},2) = 0 then crYellow else crNocolor

-LB