C
CSDunn
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what a spreadsheet might show, the
column names will actually be dynamic, based on data from a SQL Server 2000
database. The row data will also come from the same database. So in this
case, I will have a main report and a subreport. I've already tried this
arrangement for the data I will be presenting, and I get the results I
expect. so everything seems to work okay. The main report displays a single
'row' of data that make up the column headings, and the sub report displays
many rows that make up the main records.
The thing I need to figure out is how to make the physical number of
displayed 'column names' in the main report and associated 'data fields' in
the sub report change based on the report data I need to show. Right now, I
just have static fields in the main and sub report to show the data, but not
all of the fields are populated between the variations of returned data.
The Record Source for the main report is a stored procedure that takes a
@TestShortName parameter. The Record Source for the subreport is also a
stored procedure. The query for the stored procedure of the sub report is
slightly different, but it still takes an @TestShortName parameter. This
@TestShortName parameter equates to the TestID of a Student Test for both
procedures. Each student test has a different number of questions. The main
report is designed to display a questionID (QID) based on the @TestShortName
parameter. So for an @TestShortName of 'SFM2' there would be QID's 1 through
10. But for an @TestShortName of 'HMLM', there would be seventeen total
QID's of A1 through B7. SFM2 would require that there be ten fields in the
main report to show all ten QID's, but HMLM would require that there be
seventeen fields in the main report. The sub report would require the
different number of fields between the two @TestShortName parameters, ten
for SFM2, and seventeen for HMLM. However, the sub report shows question
answers for a given @TestShortName, not QID's. So given different
@TestshortNames, the data returned would look like this:
@TestShortName = 'SFM2':
1 2 3 4 5 6 7 8
9 10 (QID's in the main report)
(studentID) (sName) C I I C I C C C I I
(corresponding question answers in the sub report)
**********************************************
@TestShortName = 'HMLM'
A1 A2 A3 A4 A5 A6 A7
A8 A9 A10 B1 B2 B3 B4 B5 B6 B7 (QID's)
(studentID) (sName) C I I I C C C C
I I C C C I C C C (answers)
I know that I'll need to have the report set up in Landscape to accomodate
the varying results in the main and sub reports, but how do I set up both
reports so that I only have the required number of main and sub report
fields as required by the @TestShortName parameter? It seems like there
should be some sort of Loop structure that could count the number of
distinct QID's given a particular @TestShortName, and then display each
value in a label or text box. Maybe the same for the sub report for question
answers.
The stored procedures are set up to create 75 fields for any @TestShortName.
If an @TestShortName in the main report results in only the first ten QID's
having values, then the other 65 QID's will be NULL. The same would hold
true for the child records in the sub report. So the Loop would have to
ignore NULL values for the main and sub report. Also, for a given
@TestShortName, there can also be NULL values for the first ten QID's, then
the remaining 65 QID's could have values. The child records in the sub
report would match this as well.
I haven't done very much with coding as far as Access Reports are concerned,
and I need my 'hand held' with this one, so here are some questions:
1. How do I handle the communication between the Access ADP report and the
SQL Server Stored Procedures in the main and sub report? The procedures are
called 'MMQuestionDetailsMain_sp' and 'MMQuestionDetailsSub_sp' and both
take @TestShortName nvarchar(8). I don't suppose I would need to set up a
seperate ADO connection since I already have an OLE DB connection through
the ADP file.
2. Once I have established communication with the Stored Procedure, and have
established how I will send the parameter value for @TestShortName to the
SP, how do I handle the data that comes back from the procedure for the main
report so that the data values become the label or field names in the
report, and that any NULL values are ignored? Would I use an Object Array?
It has been suggested to me that one way to handle the NULL values would be
to use FindFirst. How might I otherwise handle the NULL values?
3. Once I have handled the data back from the SP, how do I set up the
dynamic action I need for the labels/fields in the main and sub report?
Please let me know if you have any ideas on this.
Thanks for your help!
CSDunn
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what a spreadsheet might show, the
column names will actually be dynamic, based on data from a SQL Server 2000
database. The row data will also come from the same database. So in this
case, I will have a main report and a subreport. I've already tried this
arrangement for the data I will be presenting, and I get the results I
expect. so everything seems to work okay. The main report displays a single
'row' of data that make up the column headings, and the sub report displays
many rows that make up the main records.
The thing I need to figure out is how to make the physical number of
displayed 'column names' in the main report and associated 'data fields' in
the sub report change based on the report data I need to show. Right now, I
just have static fields in the main and sub report to show the data, but not
all of the fields are populated between the variations of returned data.
The Record Source for the main report is a stored procedure that takes a
@TestShortName parameter. The Record Source for the subreport is also a
stored procedure. The query for the stored procedure of the sub report is
slightly different, but it still takes an @TestShortName parameter. This
@TestShortName parameter equates to the TestID of a Student Test for both
procedures. Each student test has a different number of questions. The main
report is designed to display a questionID (QID) based on the @TestShortName
parameter. So for an @TestShortName of 'SFM2' there would be QID's 1 through
10. But for an @TestShortName of 'HMLM', there would be seventeen total
QID's of A1 through B7. SFM2 would require that there be ten fields in the
main report to show all ten QID's, but HMLM would require that there be
seventeen fields in the main report. The sub report would require the
different number of fields between the two @TestShortName parameters, ten
for SFM2, and seventeen for HMLM. However, the sub report shows question
answers for a given @TestShortName, not QID's. So given different
@TestshortNames, the data returned would look like this:
@TestShortName = 'SFM2':
1 2 3 4 5 6 7 8
9 10 (QID's in the main report)
(studentID) (sName) C I I C I C C C I I
(corresponding question answers in the sub report)
**********************************************
@TestShortName = 'HMLM'
A1 A2 A3 A4 A5 A6 A7
A8 A9 A10 B1 B2 B3 B4 B5 B6 B7 (QID's)
(studentID) (sName) C I I I C C C C
I I C C C I C C C (answers)
I know that I'll need to have the report set up in Landscape to accomodate
the varying results in the main and sub reports, but how do I set up both
reports so that I only have the required number of main and sub report
fields as required by the @TestShortName parameter? It seems like there
should be some sort of Loop structure that could count the number of
distinct QID's given a particular @TestShortName, and then display each
value in a label or text box. Maybe the same for the sub report for question
answers.
The stored procedures are set up to create 75 fields for any @TestShortName.
If an @TestShortName in the main report results in only the first ten QID's
having values, then the other 65 QID's will be NULL. The same would hold
true for the child records in the sub report. So the Loop would have to
ignore NULL values for the main and sub report. Also, for a given
@TestShortName, there can also be NULL values for the first ten QID's, then
the remaining 65 QID's could have values. The child records in the sub
report would match this as well.
I haven't done very much with coding as far as Access Reports are concerned,
and I need my 'hand held' with this one, so here are some questions:
1. How do I handle the communication between the Access ADP report and the
SQL Server Stored Procedures in the main and sub report? The procedures are
called 'MMQuestionDetailsMain_sp' and 'MMQuestionDetailsSub_sp' and both
take @TestShortName nvarchar(8). I don't suppose I would need to set up a
seperate ADO connection since I already have an OLE DB connection through
the ADP file.
2. Once I have established communication with the Stored Procedure, and have
established how I will send the parameter value for @TestShortName to the
SP, how do I handle the data that comes back from the procedure for the main
report so that the data values become the label or field names in the
report, and that any NULL values are ignored? Would I use an Object Array?
It has been suggested to me that one way to handle the NULL values would be
to use FindFirst. How might I otherwise handle the NULL values?
3. Once I have handled the data back from the SP, how do I set up the
dynamic action I need for the labels/fields in the main and sub report?
Please let me know if you have any ideas on this.
Thanks for your help!
CSDunn