Report on Crosstab Table

[DORaymond]

I have created a crosstab operation that creates a new table that summarizes employee hours worked. I have attached an example PDF file for reference. The crosstab works as expected and you can see from the attached file that the output table has field names that are the dates for the work week. My question is this: How do I create a report for this table that will work everytime (every week) that I need to print it when each week the field names will change? In other words, I can create a report on the table in its current state, but the next time the crosstab is run for the next week, that field names will change to the new dates, and they in turn are no longer bound to the report since the fields have changed name (date).

Thanks,
Don

[Stan Mathews]

Re: Report on Crosstab Table

The easiest thing to do is to use xbasic to rename the fields after the crosstab (Mon-Sun) and build the report after the fields are renamed. The report will then be consistent with the field names each time.

[DORaymond]

Re: Report on Crosstab Table

Thanks for the reply Stan. I have not been able to find a Function or Method that allows me to change the field name in the table structure. I see where I can create or delete fields in the table, but not to rename it. Can you point me in the correct direction?

[Stan Mathews]

Re: Report on Crosstab Table

You can get the current field names with (given a table pointer)

old list = tbl.Field_Info_Dump("n,t,w,d")

You know what the new names you want are so you build that list with

new_list = "Employee,c,12,0"+crlf()+"Mon,n,7,0"+crlf()+"Tue,n,7,0"..............

Then use table.fieldname_change("drive_path_tablename.dbf",old_list,new_list)

Corrected the new_list expression to add field type, width, and decimals.

[DORaymond]

Re: Report on Crosstab Table

Stan -I will give that a try.

Also, from reading the message board, I have also found a reference to a "mapped" table being used for formatting after a crosstab, but I do not see how that would help. Do you think that might also be a possible solution?

[Stan Mathews]

Re: Report on Crosstab Table

Donald,

Note my correction to my earlier suggestion. I had left off the type, width, and decimals sample values in the new_list expression.

I don't have any experience with mapped tables but it seems the same issue would apply. You have to know the field names to map to something and the field names change each time the crosstab is run.

old list = tbl.Field_Info_Dump("n,t,w,d") gets whatever the names are and they are replaced with new_list.

[DORaymond]

Re: Report on Crosstab Table

I have been able to more simply change the field names right in the crosstab operation definition. I substituted "cdow(Workdate)" for the field/expression and that renamed the fields to Monday, Tuesday, etc. and also ensures that the correct day is assigne

[DORaymond]

Re: Report on Crosstab Table

This works but does generate another problem. If the crosstab table underlying the report does not have hours for say Sunday, then an error is generated on the report because the field is not found. I guess I would have to make all the fields and field titles conditional so they only show when needed and would have to initially build the report on a table with all fields available.

[DORaymond]

Re: Report on Crosstab Table

Well I am pretty much back to where I started with my problem. If the crosstab table generates data for 7 days worked (Mon thru Sun), and the report is set up for the same, all is well. But, if the crosstab only generates 5 days of hours (Mon -Fri), then the report generates errors because the fields for Saturday and Sunday are not in the crosstab table. To have this reliably work, the crosstab table would have to be scanned for missing days and empty fields would have to be added for the report not to bomb. Or conversely, the report would have to be automatically altered to match the available fields (which is even worse).

How then is a crosstab table reported on if you cannot guarantee what, or how many, fields the data may generate?

[Al Buchholz]

Re: Report on Crosstab Table

Add zero valued records for each day prior to running the cross tab operation.

[DORaymond]

Re: Report on Crosstab Table

Thanks Al - that does get me closer, but for that scheme to work automatically I would have to:
1) Determine for any given pay week what dates are missing and add however many records are required with the exact dates (days) that are missing and;
2) I would have to suppress the extra, added "dummy" records from showing up in the report.

I sure I can handle #2, but #1 sounds difficult for my skill level.

[DORaymond]

Re: Report on Crosstab Table

Actually, maybe I can append records that represent seven valid days from ANY week (out of a table set up with just those records), just so the fields are there, and then suppress the extra records.

[Al Buchholz]

Re: Report on Crosstab Table

Actually, maybe I can append records that represent seven valid days from ANY week (out of a table set up with just those records), just so the fields are there, and then suppress the extra records.

Good thinking. You're getting there..

[DORaymond]

Re: Report on Crosstab Table

Adding the "dummy" records works perfect. Thanks for everyone's help.