Calc Col Headings in Manual x-tab 8.5

[CarpalT]

I hope I can explain this...

I Built a manual crosstab from a SQL query. The report summarizes 1,000,000 + records for the last six months like this:

July Aug Sept Oct Nov Dec
Doc # 1
Visits50 35 29 47 24 100
Time 350 420 261 1128 144 700
AveTime 7 12 9 24 6 7

Doc # 2 # Appointments
Time
Average Time
And so on..
Everything is working fine except the column headings for the month. The SQL stored procuedure returns a column assignment (1, 2, 3...) as well as a month string for each data record. Any time you run it, it gets only the last 6 complete months and assigns the columns.

I have detail bands a, b, and c for the data suppressed and group footers for the totals visible. I have a formula that can return the correct month heading but it only works in a detail band. [says she while pulling out hair]
The formula is:
whileprintingrecords
Select case {Stored Procedure;1.Col}
Case "2"
formula = {StoredProcedure1.Month}
Case else
formula = " "
End Select

If you can point me in the right direction, I would be very grateful!
Thanks!



The world is full of good people.

 

[synapsevampire]

What are you grouped on?

Try posting:

example data (what the SP is returning)
expected output based on the example data

-k

 

[CarpalT]

Thanks for your response, I hope this is the information you are looking for.

I am grouping on Doctor. I have fomulas for Col1visits, Col1time, etc through Col6xxx that basically say if comumn = 1(or 2, 3, etc.) then value of field, else 0.

I have detail bands a, b, etc for visits, time, average. They are suppressed because all I need to see is the summary.

Then I have group footer bands a,b,etc. for visits, time, average. The whole thing has worked out very well except for the column titles. The expected output is in the first posting following the phrase "like this", however it lost all the space alignment when I pasted it in here. (One thing to note is that I am getting exactly what I expect in terms of all data and summaries, except that where I have put "July", "Aug", etc in my example, in reality the heading fields are blank no matter what I try. In some cases the first field will return the correct value.
The data looks like this

Doctor#
DoctorName
Visit#
ElapsedTime
ColumnAssignment (The Sproc codes the assignment based on a date.)
MonthNameforColumn

Shouldn't I be able to put the MonthNameForCOlumn in a field above the column that has that data in it?
Again, thank you. I am in hopes of learning something today!



The world is full of good people.

 

[synapsevampire]

Since you're grouping by the Doctor, the month names are not known to the report at the group header level as the only row available is the first for the data.

One means might be to use the following formula in the report header, assuming that you're sorted by the column and starting with the lowest:

whileprintingrecords;
Stringvar StartMonth:= {MonthNameforColumn};
numbervar X;
Datevar StartDate;
For X := 1 to 6 do(
If monthname(x) = StartMonth then
StartDate := cdate(year(currentdate),x,1)
);

Now you know the starting date, and can display the monthname based on that:

Now you can use a series of formulas in the group header:

1st month:
whileprintingrecords;
numbervar StartDate;
monthname(month(startdate))

2nd month:
whileprintingrecords;
numbervar StartDate;
monthname(month(dateadd("m",1,startdate)))

3rd month:
whileprintingrecords;
numbervar StartDate;
monthname(month(dateadd("m",2,startdate)))

etc.

-k

 

[synapsevampire]

This assumed that you used standard month names, BTW.

I notice now that you aren't returning the month names, rather an abbreviation, so change the monthname functions to:

monthname({table.field,true}

-k

 

[CarpalT]

Thank you. I'm not a programmer, so it's going to take me a little bit to absorb this. I appreciate your help and will report back when I get this done - speaking optimistically!

The world is full of good people.