Passing parameters to sub report 1

[jrball]

Crystal 10
MS SQL DB

I have a report that has a main report and a sub report, that shows survey responses. I have four db tables I am using. One has questions, one has answers, one has voter information, and the last has voter answers.

The way that I am having to report is the main report has a select expert entry to get the instructors name to report data off of. then on the subreport links I have the voterIDs that have entered surveys on the instructor linking the main and sub reports together.
(?Pm-vts_tbVoter.VoterID = vts_tbVoter.VoterID)

Then in the subreport I am pulling all the questions and answers based on the voterids. But it's not doing exactly what I need.

I need to send the VoterID's to the subreport as a parameter or array so they are displayed on one page instead of however many VoterID's there are. It looks like it's splitting the VoterID's to sepeperate pages on the subreport. If I go into the subreport and enter a few VoterID's in the VoterID Parameter window, it does what I want, so the subreport works, I just can't figure out how to populate the VoterID parameter from the main report.

Does that make sense?

 

[jrball]

I forgot to include:

In the sub report I have the record select expert formula as:
{vts_tbVoter.VoterID} = {?Pm-vts_tbVoter.VoterID}

and the {?Pm-vts_tbVoter.VoterID} is setup to accept multiple values.

 

[lbass]

It sounds like you should be linking on instructor, not voter. If you are using a parameter for the instructor, you could link the parameter to the instructor in the subreport.

-LB

 

[jrball]

That's the crazy part. I can't link on instructor. Who-ever created the DB didn't do a very goo job at it. There isn't any record id's associated to a specific instructor. They are in the database as a answer to a question. So for example there are 15 questions on the survey, if I link on the instructor, I only get the one question that they are an answer to, and none of the other 14 come across. The only way I was able to get the sub report to work was to use the voterid to pull all the information off of.

Here's the way that taht I have to set it up.

Main Report uses two tables

vts_Answer - This uses the field of AnswerText (Where the only place the instructors name shows up in)

vts_voter - VoterID (autonumber created)

I have the select expert filter all the vts_Answer fields to pull out the instructor name I want, then tie the voterID's to it. That gives me a list of voterids to get the rest of the survey information.

Then comes the subreport in the details section of the main report.

The subreport has a select expert of {vts_tbVoter.VoterID} = {?Pm-vts_tbVoter.VoterID}

Which then I display the survey data with 4 tables

vts_tbQuestion - Lists the question text.
(example Facilitators were well prepared.)

vts_tbAnswer - Lists the question answers
(example 5 - Strongly Agree, 4 - Agree, 3 - Neutral, etc)

vts_tbVoter - Gets the voter id and date survey was completed

vts_tbVoterAnswers - Gets the answers the voter chose

 

[jrball]

I think I can get rid of the main report and just pull the voterids I need through an sql select and use them as params..and then uses those to pull the data on the sub instead of trying to pass them through the main to sub.

 

[lbass]

I don't know if you have resolved this or not. I wonder how you "pull out the instructor name I want, then tie the voterID's to it." If you want help, I think you should show a layout of the raw data.

-LB

 

[jrball]

My idea didn't solve it either.

What I don't understand is why the subreport when displayed through the main report only shows one record at a time rather than grouping them all together like it does when I run the subreport on it's own.

What is needed for raw data? I'll try my best at listing...

 

[lbass]

It is because of your link, which acts as a filter. If you unlink the subreport, it will show ALL responses every time it fires.

I don't understand why you are using a subreport (you might need to, I just am not clear on this). What was your rationale? I think it would help to show the tables you are using and the available fields for each, especially those fields that appear in other tables and thus are potential links.

Can you also explain what this report is about? Who are the voters and instructors and why are there two answer tables? I think it would help to have a general idea of what you are trying to do.

-LB

 

[jrball]

Alright I understand. Here's what I can explain.
Tables using are:

vtstb_Questions
QuestionId QuestionText
789 Please Choose your Department
908 Please Choose your Start Date
788 Please Choose your Facilitator
797 The activities and exercises facilitated my learning.
795 The content was delivered in a logical manner.
801 What did you enjoy and what did you find useful from your training course?
800 What would you recommend to improve this course?
806 Facilitators were available to answer questions I had.
807 Facilitators answered my questions in a clear manner.
808 Facilitators kept the class focused on the training material.
809 Facilitators were familiar with the material they were presenting.
810 Facilitators used the class time effectively.
811 Facilitators promoted an environment of learning.
803 Facilitators promoted participant discussion and involvement.
813 What improvements would you recommend for the course methodology/facilitator?
814 What did you find least valuable about the course methodology/facilitator?
815 What did you find most valuable about the course methodology/facilitator?
812 Additional comments:

vtstb_Answers
AnswerID QuestionID AnswerText
1006 789 DeptA
1007 789 DeptB
1008 789 DeptC
1344 908 11/15/2004
1345 908 11/01/2004
1346 908 11/29/2004
1001 788 InstructorName1
1010 788 InstructorName2
1011 788 InstructorName3
1024 797 5 - Strongly Agree
1025 797 4 - Agree
1026 797 3 - Neutral
1027 797 2 - Disagree
1028 797 1 - Strongly Disagree
1029 797 N/A Not Applicable
1030 795 5 - Strongly Agree
1031 795 4 - Agree
1032 795 3 - Neutral
1032 795 2 - Disagree
1034 795 1 - Strongly Disagree
1035 795 N/A Not Applicable
1036 801 Comments:
1037 800 Comments:
1038 806 5 - Strongly Agree
1039 806 4 - Agree
1040 806 3 - Neutral
1041 806 2 - Disagree
1042 806 1 - Strongly Disagree
1043 806 N/A Not Applicable

vtstb_Voter
VoterID VoteDate
4 11/2/2004 10:31:10 AM
5 11/2/2004 10:31:10 AM
6 11/2/2004 10:31:10 AM
7 11/2/2004 10:31:10 AM
8 11/2/2004 10:31:10 AM
9 11/2/2004 10:31:10 AM

vtstb_VoterAnswers
VoterID AnswerID AnswerText
4 1006
4 1344
4 1001
4 1024
4 1030
4 1036 I liked the training class.
4 1037 Bring Food in.
5 1038
5 1006
5 1344
5 1001
5 1024
5 1030
5 1036 I liked the training class.
5 1037 Bring Food in.
5 1038
6 1006
6 1344
6 1001
6 1024
6 1030
6 1036 I liked the training class.
6 1037 Bring Food in.
6 1038
7 1006
7 1344
7 1001
7 1024
7 1030
7 1036 I liked the training class.
7 1037 Bring Food in.
7 1038
8 1006
8 1344
8 1001
8 1024
8 1030
8 1036 I liked the training class.
8 1037 Bring Food in.
8 1038
9 1006
9 1344
9 1001
9 1030
9 1036 I liked the training class.
9 1037 Bring Food in.
9 1038

Here's how I have the report setup

Main report:
Paramter Selection based on Instructor's Name - {vts_tbAnswer.AnswerText} = {?InstructorName}

Then the subreport is in the details section of the main report with the links of:
{vts_tbVoter.VoterID} = {?Pm-vts_tbVoter.VoterID}

From there the subreport contains the questions, and answers from the voter ids that the main report is sending through.

What is happening is instead of grouping all the surveys on one page, it's giving them seperate pages for each VoterID.
When I open the subreport and enter a few voterids as parameters an run it, it's grouping them correctly. When I try to run the main report it splits them up.

 

[lbass]

So you have three tables in the main report? I don't see how the vtstb_Voter table can be linked to the others. What are your links for these three?

-LB

 

[jrball]

vts_tbAnswer.QuestionId --> vts_tbQuestion.QuestionId (Inner)
vts_tbVoterAnswers.AnswerID --> vts_tbAnswer.AnswerId (Inner)
vts_tbVoterAnswers.VoterID --> vts_tbVoter.VoterID (Inner)

 

[lbass]

There are two approaches you could try. The first is without a subreport, where you group on {vts_tbVoter.VoterID}, and you do not use the parameter in the record selection area, but instead create a formula {@Instruc}:

if {vts_tbAnswer.AnswerId} = {?Instructor} then 1

Then go to report->selection formulas->GROUP and enter:

sum({@Instruc},{vts_tbVoter.VoterID}) > 0

This would return the surveys for voters with this instructor.

The second method would be to use your main report as a subreport in the report header, where you limit the records to those with the instructor, using a parameter, i.e., the record selection formula is:

{vts_tbAnswer.AnswerId} = {?Instructor}

Then you use a variable to accumulate the voter IDs:

whileprintingrecords;
shared stringvar voterID := voterID + {vts_tbVoter.VoterID} +", ";

Then in the main report, you would lay out the fields you want to display and then go to the section expert->details->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar voterID;

instr(voterID,{vts_tbVoter.VoterID}) = 0

-LB

 

[jrball]

ok, I'm following you, but what does {?Instructor} have in it?

 

[lbass]

I was assuming you would prepopulate the parameter with 1001, 1010, and 1011 (the values from your AnswerID).

-LB

 

[jrball]

Thats's was I thought also. I didn't figure it out right away. Thanks so much!

 

[jrball]

That got me close, but now I need to group all the answers together instead of having them listed seperately on each a new page. I tried to group by QuestionText also, but it's not working. Once they are grouped together I need to graph the results. Do you know what I mean?

 

[lbass]

Which method did you end up using?

-LB

 

[jrball]

I tried the first one without the Sub

 

[lbass]

You aren't going to be able to graph directly with either of these approaches, I don't think. It's always a good idea to specify your final goal upfront, as it could determine the suggestions that are made. Let me think about this some more.

-LB

 

[lbass]

I think you will need to use the approach where you place a subreport in the report header that will collect the VoterIDs, which in turn are shared with the main report in order to suppress detail records. You will then be free to group on Questions. For charting, you will have to create a conditional summary, using a formula like:

whileprintingrecords;
shared stringvar voterID;

if instr(voterID,{vts_tbVoter.VoterID}) > 0 then
<fieldtosummarize>

You will need to create this formula in the main report, but do not place it on the report (or at least not until after you add it to the chart expert)--otherwise it will not be available for charting.

-LB