Report Layout for Monthly Schedule

[Rich Hartnett]

Re: Report Layout for Monthly Schedule

Yeah sometimes I surprise myself. LOL

[Steve Workings]

Re: Report Layout for Monthly Schedule

Thanks Steve. I never knew you could put html code into a report, but I learn something new every day.

It looks tricky, but I will certainly give it a try and see what happens.

Don't think of it particularly as HTML code. Think of it as creating a variable, and just inserting it where you need it. The variable, in this case, happens to be HTML which means you need to treat it as such, but it offers great formatting and organizational possibilities. The first one you do will be a little tricky, but keep it simple as I've suggested. Pretty soon it'll be second nature -- just Xbasic code to generate the text string you need.

[Rich Hartnett]

Re: Report Layout for Monthly Schedule

Well as it turns out, my elation was a bit premature.

Although the idea of using the sub-reports works great, apparently there is a limit of 30, and of course there are 31 days in most months. As such, my report looks perfect if the month only has 30 or less days in it, but as soon as I add the 31st sub-report, I get an error message that says there are too many sub-reports in this report.

One sub-report away from the finish line. That sux.

If I could just figure out an easy way to filter that last group of fields to just the 31st, without using a sub-report, I would be golden.

[Stan Mathews]

Re: Report Layout for Monthly Schedule

Suggestion wouldn't work as I thought. Should have looked at the table first.

Required a little manipulation. Some work to be done on the report to dress it up and narrow the columns.

[Al Buchholz]

Re: Report Layout for Monthly Schedule

Suggestion wouldn't work as I thought. Should have looked at the table first.

Required a little manipulation. Some work to be done on the report to dress it up and narrow the columns.

Stan

Nice. I think it needs to allow for a day not having data and therefore not having a column in the crosstab table.

Although since the police don't get a day off it may not be needed for Rich's system. Maybe just for others. But that would make it more generic.

It's the crosstab improvement that Alpha knows should be done but isn't high on the priority list.

I'm thinking the append needs to look at the crosstab fieldnames and get the day of the month and then append to the wk_date table instead of crossrefing by the field_Count.

[Stan Mathews]

Re: Report Layout for Monthly Schedule

I was thinking the report column headings would be calculated fields based on the dates selected in the variables. Might need to change the variables to global for them to be seen in the report.

The report could also be ordered as per the original screenshot with a case() function like

case(Wkgroup = "Squad 1",1,Wkgroup = "Squad 2",2,Wkgroup = "Squad 3",3)

One could actually have a base table with just the wkgroup field and then add fields based on the count of fields in the crosstab, removing them after the report is run.

Not sure how to handle a report based on a varying number of fields though. Maybe have a version for 28, 29, 30, and 31 day months selecting the appropriate version as needed?

[Al Buchholz]

Re: Report Layout for Monthly Schedule

I was thinking the report column headings would be calculated fields based on the dates selected in the variables. Might need to change the variables to global for them to be seen in the report.

The report could also be ordered as per the original screenshot with a case() function like

case(Wkgroup = "Squad 1",1,Wkgroup = "Squad 2",2,Wkgroup = "Squad 3",3)

One could actually have a base table with just the wkgroup field and then add fields based on the count of fields in the crosstab, removing them after the report is run.

Not sure how to handle a report based on a varying number of fields though. Maybe have a version for 28, 29, 30, and 31 day months selecting the appropriate version as needed?

I think the base table needs to be stable since the objects on the report need to be there or an error occurs.

I'm working on a script to read the table names and build the append on the fieldnames rather than the count of fieldnames.. (I need it for another project of mine.) ;)

[Rich Hartnett]

Re: Report Layout for Monthly Schedule

Thanks to both of you guys for assisting me with this.

I don't know anything about crosstab tables, but I can certainly learn. I'm confused though as to how the data gets from my original table (i.e. "The Master Schedule") to the other tables?

And if I use this format, will it allow multiple months to be displayed at once?

They way they do it now is on an Excel spreadsheet, which makes it easy to print, but I want to change it all to web based, with an option to print.

[Stan Mathews]

Re: Report Layout for Monthly Schedule

Not sure I understand entirely because you still have to have the count to supply to the append.m_count variable but maybe

tbl_src = table.open("wk_cross")
fld_names = tbl_src.field_name_get()
fld_count = line_count(fld_names)
tbl_src.close()

DIM Append as P
a_tbl = table.open("wk_dates")
dest_names = a_tbl.field_name_get()
a_tbl.zap(.T.)
append.t_db = "wk_cross"
ON ERROR GOTO ERROR30102014095038166
append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = ""
append.type = "All"
append.m_count = fld_count
for qx = 1 to fld_count
eval("append.m_field"+alltrim(str(qx))) = word(dest_names,qx,crlf())
eval("append.m_exp"+alltrim(str(qx))) = "@WK_CROSS->"+word(fld_names,qx,crlf())
next
append.t_count = 0

a_tbl.append()


GOTO CONTINUE30102014095038166
ERROR30102014095038166:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Append Operation"+crlf()+error_text_get())
END
CONTINUE30102014095038166:
a_tbl.close()

[Al Buchholz]

Re: Report Layout for Monthly Schedule

More like this...

To test with less fields, remove some of the fields from the crosstab - but not the first field.

'Date Created: 30-Oct-2014 09:59:16 AM
'Last Updated: 30-Oct-2014 11:38:38 AM
'Created By  : SMathews
'Updated By  : al
tbl_src = table.open("wk_cross")
use_fld_ct = tbl_src.fields_get()-1
fld_names = tbl_src.field_name_get()
'get rid of the first field
fld_names=*word_remove(fld_names, 1, crlf())

tbl_src.close()

DIM Append as P
a_tbl = table.open("wk_dates")
a_tbl.zap(.T.)
append.t_db = "wk_cross"
ON ERROR GOTO ERROR30102014095038166
append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = ""
append.type = "All"
append.m_count = use_fld_ct
append.m_field1 = "WKGROUP"
append.m_exp1 = "@WK_CROSS->WKGROUP"
'for qx = 2 to use_fld_ct
'debug(1)
	ctr=0
for each fn in fld_names
	ctr=ctr+1
	qx=substr(fn,8,2)
eval("append.m_field"+alltrim(str(ctr))) = "D"+padl(alltrim(qx),2,"0")
eval("append.m_exp"+alltrim(str(ctr))) = "@WK_CROSS->"+fn
next
append.t_count = 0

a_tbl.append()


GOTO CONTINUE30102014095038166
ERROR30102014095038166:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Append Operation"+crlf()+error_text_get())
END
CONTINUE30102014095038166:
a_tbl.close()

[Stan Mathews]

Re: Report Layout for Monthly Schedule

Thanks to both of you guys for assisting me with this.

I don't know anything about crosstab tables, but I can certainly learn. I'm confused though as to how the data gets from my original table (i.e. "The Master Schedule") to the other tables?

And if I use this format, will it allow multiple months to be displayed at once?

They way they do it now is on an Excel spreadsheet, which makes it easy to print, but I want to change it all to web based, with an option to print.

At present it is based on a fixed end result table with 31 date related fields. As Al and I were noting the end result table could be recreated each time with the appropriate number of fields based on the source table. (edit by Al: except Al is saying the report base table is constant and the append operation has variable fields in it. But Stan and I will work that out.)

Don't know anything about how you are presently displaying it. The exercise I went through involved how to derive a table upon which you could base a report in the format you described. I can't see how having more than 32 fields on a report would be readable.

If you're confused about how the data gets from your table to the result table, study the events for each of the buttons on the form.

[Al Buchholz]

Re: Report Layout for Monthly Schedule

Thanks to both of you guys for assisting me with this.

I don't know anything about crosstab tables, but I can certainly learn. I'm confused though as to how the data gets from my original table (i.e. "The Master Schedule") to the other tables?

And if I use this format, will it allow multiple months to be displayed at once?

They way they do it now is on an Excel spreadsheet, which makes it easy to print, but I want to change it all to web based, with an option to print.

Rich

We'd need to add the month field to the crosstab process to get a row for each month and each wkgroup.

It's the crosstab operation that reads the data from the master schedule and produces rows and columns in an intermediate table that is appended to the report base table.

[Stan Mathews]

Re: Report Layout for Monthly Schedule

More like this...

Not bad at all. I think you need ctr=1 instead of ctr = 0 though.

[Al Buchholz]

Re: Report Layout for Monthly Schedule

Not bad at all. I think you need ctr=1 instead of ctr = 0 though.

Oops - right... because the first field is the wkgroup.

and...

use_fld_ct = tbl_src.fields_get()

gotta take the minus 1 off this expression so

append.m_count = use_fld_ct

is correct.

Thanks for the push Stan and Rich.. Now I have the process I need for another project.