Ken Hardwick
|
| Posted: 07/18/2001, 5:06 PM |
|
One thing I have struggled with using Code Charge was once data had been
added/updated to database, how to display the data in certain layouts.
For instance,if you have a database/table reflecting employee status and
state.
And you wanted to display counts by State by Status. With States as the rows
and Status Code in columns. And, like you might do in Excel, each row would
have a total (total count by State) and each Column would have a Total
(total count by Status Code)
Well couldn't figure out how to do this directly, so have came up with the
following idea.
Before you read the following, you may want to go see a working example at
http://www.kenhardwick.com/report
Make changes to employee status and/or state.
Then goto the report, to see the results.
Here is a rough recap of what I did...
1) Created a new table with several "text" columns.
2) Designed a CC Page with a "grid" form with appropriate column headings.
3) In before show event, created several sql statement
a)One to Delete all rows in report table
b)..Select cnt by state for each status code and add one row for each
state and post employee cnt to appropriate columns.
c)Likewise,do sql to get total by state and post to last column
d)Create a "---" row to each column
e)Sql to get total cnt by Status and create new row in database
with each cnt added to correct status column
f)Create a last row with "======="
(Which in effect, I have create a report in a database table.)
Then the grid shows all the rows just created.
Using this concept, one could create just about any type of report
you might want.
Would appreciate comments about this.
1)Suggestions for improvements
2)Would it be usefull ?
3)Suggestion of a better way to do something like this.
A zipped file with my database and ccs file is available
for download at
http://www.kenhardwick.com/report/CCReport.zip
Ken Hardwick
KenHardwick@visto.com
|
|
|
 |
Jim Ryan
|
| Posted: 07/18/2001, 7:23 PM |
|
Hi Ken,
Good job! Thanks for the example. You should submit it in the tips and
articles section on the GOTOCODE.COM website.
Have you tried to group by then subtotal each group and grand total at the
end?
Jim
"Ken Hardwick" <kenhardwick@visto.com> wrote in message
news:9j58ar$u16$1@news.codecharge.com...
> One thing I have struggled with using Code Charge was once data had been
> added/updated to database, how to display the data in certain layouts.
>
> For instance,if you have a database/table reflecting employee status and
> state.
> And you wanted to display counts by State by Status. With States as the
rows
> and Status Code in columns. And, like you might do in Excel, each row
would
> have a total (total count by State) and each Column would have a Total
> (total count by Status Code)
>
> Well couldn't figure out how to do this directly, so have came up with the
> following idea.
>
> Before you read the following, you may want to go see a working example at
>
> http://www.kenhardwick.com/report
> Make changes to employee status and/or state.
> Then goto the report, to see the results.
>
>
>
> Here is a rough recap of what I did...
> 1) Created a new table with several "text" columns.
> 2) Designed a CC Page with a "grid" form with appropriate column headings.
> 3) In before show event, created several sql statement
> a)One to Delete all rows in report table
> b)..Select cnt by state for each status code and add one row for each
> state and post employee cnt to appropriate columns.
> c)Likewise,do sql to get total by state and post to last column
> d)Create a "---" row to each column
> e)Sql to get total cnt by Status and create new row in database
> with each cnt added to correct status column
> f)Create a last row with "======="
>
> (Which in effect, I have create a report in a database table.)
>
> Then the grid shows all the rows just created.
>
> Using this concept, one could create just about any type of report
> you might want.
>
> Would appreciate comments about this.
> 1)Suggestions for improvements
> 2)Would it be usefull ?
> 3)Suggestion of a better way to do something like this.
>
> A zipped file with my database and ccs file is available
> for download at
> http://www.kenhardwick.com/report/CCReport.zip
>
>
> Ken Hardwick
>KenHardwick@visto.com
>
>
>
>
|
|
|
|
Ken Hardwick
|
| Posted: 07/19/2001, 3:36 AM |
|
Jim,
Thanks for your comment. I will plan to submit to the tips/articles section
as time permits.
As far as subgroups/subtotal, that should be quite easy to add.
Just more sql to write and add to the report table.
Ken Hardwick
Norman,OK
Jim Ryan wrote in message <9j5gab$crc$1@news.codecharge.com>...
>Hi Ken,
>
>Good job! Thanks for the example. You should submit it in the tips and
>articles section on the GOTOCODE.COM website.
>Have you tried to group by then subtotal each group and grand total at the
>end?
>
>Jim
>
>
>"Ken Hardwick" <kenhardwick@visto.com> wrote in message
>news:9j58ar$u16$1@news.codecharge.com...
>> One thing I have struggled with using Code Charge was once data had been
>> added/updated to database, how to display the data in certain layouts.
>>
>> For instance,if you have a database/table reflecting employee status and
>> state.
>> And you wanted to display counts by State by Status. With States as the
>rows
>> and Status Code in columns. And, like you might do in Excel, each row
>would
>> have a total (total count by State) and each Column would have a Total
>> (total count by Status Code)
>>
>> Well couldn't figure out how to do this directly, so have came up with
the
>> following idea.
>>
>> Before you read the following, you may want to go see a working example
at
>>
>> http://www.kenhardwick.com/report
>> Make changes to employee status and/or state.
>> Then goto the report, to see the results.
>>
>>
>>
>> Here is a rough recap of what I did...
>> 1) Created a new table with several "text" columns.
>> 2) Designed a CC Page with a "grid" form with appropriate column
headings.
>> 3) In before show event, created several sql statement
>> a)One to Delete all rows in report table
>> b)..Select cnt by state for each status code and add one row for each
>> state and post employee cnt to appropriate columns.
>> c)Likewise,do sql to get total by state and post to last column
>> d)Create a "---" row to each column
>> e)Sql to get total cnt by Status and create new row in database
>> with each cnt added to correct status column
>> f)Create a last row with "======="
>>
>> (Which in effect, I have create a report in a database table.)
>>
>> Then the grid shows all the rows just created.
>>
>> Using this concept, one could create just about any type of report
>> you might want.
>>
>> Would appreciate comments about this.
>> 1)Suggestions for improvements
>> 2)Would it be usefull ?
>> 3)Suggestion of a better way to do something like this.
>>
>> A zipped file with my database and ccs file is available
>> for download at
>> http://www.kenhardwick.com/report/CCReport.zip
>>
>>
>> Ken Hardwick
>>KenHardwick@visto.com
>>
>>
>>
>>
>
>
|
|
|
|
Nadeem
|
| Posted: 07/20/2001, 10:17 AM |
|
Hi Ken,
You did something really nice which had been asked for long time now.
Well done.
Nadeem
"Ken Hardwick" <kenhardwick@visto.com> wrote in message
news:9j58ar$u16$1@news.codecharge.com...
> One thing I have struggled with using Code Charge was once data had been
> added/updated to database, how to display the data in certain layouts.
>
> For instance,if you have a database/table reflecting employee status and
> state.
> And you wanted to display counts by State by Status. With States as the
rows
> and Status Code in columns. And, like you might do in Excel, each row
would
> have a total (total count by State) and each Column would have a Total
> (total count by Status Code)
>
> Well couldn't figure out how to do this directly, so have came up with the
> following idea.
>
> Before you read the following, you may want to go see a working example at
>
> http://www.kenhardwick.com/report
> Make changes to employee status and/or state.
> Then goto the report, to see the results.
>
>
>
> Here is a rough recap of what I did...
> 1) Created a new table with several "text" columns.
> 2) Designed a CC Page with a "grid" form with appropriate column headings.
> 3) In before show event, created several sql statement
> a)One to Delete all rows in report table
> b)..Select cnt by state for each status code and add one row for each
> state and post employee cnt to appropriate columns.
> c)Likewise,do sql to get total by state and post to last column
> d)Create a "---" row to each column
> e)Sql to get total cnt by Status and create new row in database
> with each cnt added to correct status column
> f)Create a last row with "======="
>
> (Which in effect, I have create a report in a database table.)
>
> Then the grid shows all the rows just created.
>
> Using this concept, one could create just about any type of report
> you might want.
>
> Would appreciate comments about this.
> 1)Suggestions for improvements
> 2)Would it be usefull ?
> 3)Suggestion of a better way to do something like this.
>
> A zipped file with my database and ccs file is available
> for download at
> http://www.kenhardwick.com/report/CCReport.zip
>
>
> Ken Hardwick
>KenHardwick@visto.com
>
>
>
>
|
|
|
|
Nadeem Malik
|
| Posted: 07/29/2001, 8:22 AM |
|
Hi Ken,
Indeed you did nice work. What about if MS Access is not at backend (say
DB2) where one does not have option to create Query Tables (like you did for
reporting)?
Thanks.
Nadeem
"Ken Hardwick" <kenhardwick@visto.com> wrote in message
news:9j58ar$u16$1@news.codecharge.com...
> One thing I have struggled with using Code Charge was once data had been
> added/updated to database, how to display the data in certain layouts.
>
> For instance,if you have a database/table reflecting employee status and
> state.
> And you wanted to display counts by State by Status. With States as the
rows
> and Status Code in columns. And, like you might do in Excel, each row
would
> have a total (total count by State) and each Column would have a Total
> (total count by Status Code)
>
> Well couldn't figure out how to do this directly, so have came up with the
> following idea.
>
> Before you read the following, you may want to go see a working example at
>
> http://www.kenhardwick.com/report
> Make changes to employee status and/or state.
> Then goto the report, to see the results.
>
>
>
> Here is a rough recap of what I did...
> 1) Created a new table with several "text" columns.
> 2) Designed a CC Page with a "grid" form with appropriate column headings.
> 3) In before show event, created several sql statement
> a)One to Delete all rows in report table
> b)..Select cnt by state for each status code and add one row for each
> state and post employee cnt to appropriate columns.
> c)Likewise,do sql to get total by state and post to last column
> d)Create a "---" row to each column
> e)Sql to get total cnt by Status and create new row in database
> with each cnt added to correct status column
> f)Create a last row with "======="
>
> (Which in effect, I have create a report in a database table.)
>
> Then the grid shows all the rows just created.
>
> Using this concept, one could create just about any type of report
> you might want.
>
> Would appreciate comments about this.
> 1)Suggestions for improvements
> 2)Would it be usefull ?
> 3)Suggestion of a better way to do something like this.
>
> A zipped file with my database and ccs file is available
> for download at
> http://www.kenhardwick.com/report/CCReport.zip
>
>
> Ken Hardwick
>KenHardwick@visto.com
>
>
>
>
|
|
|
|
Ken Hardwick
|
| Posted: 07/30/2001, 3:04 PM |
|
Nadeem,
I appreciate your feedback and comments...
My original concept of was to create a empty table with a number of columns
all formatted as text. Then, just before creating the report, I would delete
all rows
of data, then add new rows in specific order or have a order column...so I
would
run one query to create a "header" row, then another to create multiple
detail rows, then a "single underline" row , then a "total" row, then a
"double underline" row...ie just build the report in the table, then
displaying the table...
With Access, I was able to create queries which reduced the SQL needed to
build the table, but originally I was just doing it all without Access
Queries.
Of course, with this concept, you still need to have a "table" that you can
use
to do this. Also, you could build a text file and then retrieve the text
file similar to
retrieving rows from a database. I'm not sure how to retrieve rows of a text
file
with CC but most likely it can be done.
You are welcomed to contact me if anyquestions..kenhardwick@visto.com
Ken Hardwick
Norman,OK
"Nadeem Malik" <n.malik@usa.net> wrote in message
news:9k19of$77n$1@news.codecharge.com...
> Hi Ken,
>
> Indeed you did nice work. What about if MS Access is not at backend (say
> DB2) where one does not have option to create Query Tables (like you did
for
> reporting)?
>
> Thanks.
>
> Nadeem
>
> "Ken Hardwick" <kenhardwick@visto.com> wrote in message
>news:9j58ar$u16$1@news.codecharge.com...
> > One thing I have struggled with using Code Charge was once data had been
> > added/updated to database, how to display the data in certain layouts.
> >
> > For instance,if you have a database/table reflecting employee status and
> > state.
> > And you wanted to display counts by State by Status. With States as the
> rows
> > and Status Code in columns. And, like you might do in Excel, each row
> would
> > have a total (total count by State) and each Column would have a Total
> > (total count by Status Code)
> >
> > Well couldn't figure out how to do this directly, so have came up with
the
> > following idea.
> >
> > Before you read the following, you may want to go see a working example
at
> >
> > http://www.kenhardwick.com/report
> > Make changes to employee status and/or state.
> > Then goto the report, to see the results.
> >
> >
> >
> > Here is a rough recap of what I did...
> > 1) Created a new table with several "text" columns.
> > 2) Designed a CC Page with a "grid" form with appropriate column
headings.
> > 3) In before show event, created several sql statement
> > a)One to Delete all rows in report table
> > b)..Select cnt by state for each status code and add one row for each
> > state and post employee cnt to appropriate columns.
> > c)Likewise,do sql to get total by state and post to last column
> > d)Create a "---" row to each column
> > e)Sql to get total cnt by Status and create new row in database
> > with each cnt added to correct status column
> > f)Create a last row with "======="
> >
> > (Which in effect, I have create a report in a database table.)
> >
> > Then the grid shows all the rows just created.
> >
> > Using this concept, one could create just about any type of report
> > you might want.
> >
> > Would appreciate comments about this.
> > 1)Suggestions for improvements
> > 2)Would it be usefull ?
> > 3)Suggestion of a better way to do something like this.
> >
> > A zipped file with my database and ccs file is available
> > for download at
> > http://www.kenhardwick.com/report/CCReport.zip
> >
> >
> > Ken Hardwick
> >KenHardwick@visto.com
> >
> >
> >
> >
>
>
|
|
|
|
|
