Home » Archived » BIRT » question on report design
| question on report design [message #892330] |
Wed, 27 June 2012 18:09  |
 Aaron Titus
Messages: 10
Registered: March 2012 |
Junior Member |
|
|
I am a BIRT newbie, and so far things are going very well. I have implemented a number of reports with success. I have a question with respect to a specific report requirement. We have a web survey that collects the answers to multiple choice questions. There is one database record per survey we received back, and one column in per question in each record that holds the response. Example:
CREATE TABLE SURVEY (
RESPONDER_EMAIL VARCHAR(120),
QUESTION01 CHAR(1),
QUESTION02 CHAR(1),
...
QUESTION11 CHAR(1)
);
I need to create a summary report that would list how many surveys came back with a given response for each question. Example:
Survey Response Report for May 2012
Total Surveys Received: 129
Question01:
Response 'A': 11, Response 'B': 89, Response 'C': 21, Response 'D': 8
Question02:
Response 'A': 111, Response 'B': 18, Response 'C': 0, Response 'D': 0
...
Question11 :
Response 'A': 3, Response 'B': 21, Response 'C': 75, Response 'D': 30
How would something like this be done in BIRT? I have the BIRT Field guide and I've been reading through the examples but these focus around the more classic "sales per month" type aggregates but I can't seem to find any examples for something like this. Can someone point me in the right direction?
|
|
|
| Re: question on report design [message #892335 is a reply to message #892330] |
Wed, 27 June 2012 18:55  |
Erik Marke
Messages: 2
Registered: June 2012 |
Junior Member |
|
|
Dear Aaron
I would have choosen a different table layout, and have a record by survey_id and question, so you would end up with 11 records per survey.
But let say it is a given. I am missing the survey month and year. Lets assume for my feedback that you just missed it.
Using DB2 you need to union the different results to have seperate records and than you can you this as a base for your table and group it on month and questions and response rate use aggregation function on count of the rating or sum the count
select s.surveydate, 'Question01:' as question_nbr, QUESTION1 as RATING, count(*) as count
from SURVEY s
where s.surveydate=201205 and question01 is not null
group by s.surveydate, QUESTION1
union all
select s.surveydate, 'Question02:' as question_nbr, QUESTION2 as RATING, count(*) as count
from SURVEY s
where s.surveydate=201205 and s.question02 is not null
group by s.surveydate, QUESTION2
union all
select s.surveydate, 'Question03:' as question_nbr, QUESTION3 as RATING, count(*) as count
from SURVEY s
where s.surveydate=201205 and s.question03 is not null
group by s.surveydate, QUESTION3
... and so on until 11....
Oracle has the function of unpivot the flat table you seem to have.
|
|
| | | |
| Re: question on report design [message #892586 is a reply to message #892330] |
Thu, 28 June 2012 15:52 |
|
There are a lot of ways to do this. You could add a set of computed
columns that aggregates each column using a count. You could add a
table and use the aggregation report item, or use a crosstab. I took
the second approach in the attached example.
Jason
On 6/27/2012 2:09 PM, Aaron Titus wrote:
> I am a BIRT newbie, and so far things are going very well. I have
> implemented a number of reports with success. I have a question with
> respect to a specific report requirement. We have a web survey that
> collects the answers to multiple choice questions. There is one database
> record per survey we received back, and one column in per question in
> each record that holds the response. Example:
> CREATE TABLE SURVEY (
> RESPONDER_EMAIL VARCHAR(120),
> QUESTION01 CHAR(1), QUESTION02 CHAR(1),
> ...
> QUESTION11 CHAR(1)
> );
>
> I need to create a summary report that would list how many surveys came
> back with a given response for each question. Example:
> Survey Response Report for May 2012
>
> Total Surveys Received: 129
>
> Question01:
> Response 'A': 11, Response 'B': 89, Response 'C': 21, Response 'D': 8
>
> Question02: Response 'A': 111, Response 'B': 18, Response 'C': 0,
> Response 'D': 0
>
> ...
> Question11 :
> Response 'A': 3, Response 'B': 21, Response 'C': 75, Response 'D': 30
>
> How would something like this be done in BIRT? I have the BIRT Field
> guide and I've been reading through the examples but these focus around
> the more classic "sales per month" type aggregates but I can't seem to
> find any examples for something like this. Can someone point me in the
> right direction?
>
>
|
|
|
| Re: question on report design [message #892590 is a reply to message #892330] |
Thu, 28 June 2012 15:52 |
|
There are a lot of ways to do this. You could add a set of computed
columns that aggregates each column using a count. You could add a
table and use the aggregation report item, or use a crosstab. I took
the second approach in the attached example.
Jason
On 6/27/2012 2:09 PM, Aaron Titus wrote:
> I am a BIRT newbie, and so far things are going very well. I have
> implemented a number of reports with success. I have a question with
> respect to a specific report requirement. We have a web survey that
> collects the answers to multiple choice questions. There is one database
> record per survey we received back, and one column in per question in
> each record that holds the response. Example:
> CREATE TABLE SURVEY (
> RESPONDER_EMAIL VARCHAR(120),
> QUESTION01 CHAR(1), QUESTION02 CHAR(1),
> ...
> QUESTION11 CHAR(1)
> );
>
> I need to create a summary report that would list how many surveys came
> back with a given response for each question. Example:
> Survey Response Report for May 2012
>
> Total Surveys Received: 129
>
> Question01:
> Response 'A': 11, Response 'B': 89, Response 'C': 21, Response 'D': 8
>
> Question02: Response 'A': 111, Response 'B': 18, Response 'C': 0,
> Response 'D': 0
>
> ...
> Question11 :
> Response 'A': 3, Response 'B': 21, Response 'C': 75, Response 'D': 30
>
> How would something like this be done in BIRT? I have the BIRT Field
> guide and I've been reading through the examples but these focus around
> the more classic "sales per month" type aggregates but I can't seem to
> find any examples for something like this. Can someone point me in the
> right direction?
>
>
|
|
|
| Re: question on report design [message #892591 is a reply to message #892586] |
Thu, 28 June 2012 15:54 |
|
BTW
I like Erik's approach better, but if you can not push back the logic
you can use a similar approach to the example I posted.
Jason
On 6/28/2012 11:52 AM, Jason Weathersby wrote:
> There are a lot of ways to do this. You could add a set of computed
> columns that aggregates each column using a count. You could add a
> table and use the aggregation report item, or use a crosstab. I took
> the second approach in the attached example.
>
> Jason
>
> On 6/27/2012 2:09 PM, Aaron Titus wrote:
>> I am a BIRT newbie, and so far things are going very well. I have
>> implemented a number of reports with success. I have a question with
>> respect to a specific report requirement. We have a web survey that
>> collects the answers to multiple choice questions. There is one database
>> record per survey we received back, and one column in per question in
>> each record that holds the response. Example:
>> CREATE TABLE SURVEY (
>> RESPONDER_EMAIL VARCHAR(120),
>> QUESTION01 CHAR(1), QUESTION02 CHAR(1),
>> ...
>> QUESTION11 CHAR(1)
>> );
>>
>> I need to create a summary report that would list how many surveys came
>> back with a given response for each question. Example:
>> Survey Response Report for May 2012
>>
>> Total Surveys Received: 129
>>
>> Question01:
>> Response 'A': 11, Response 'B': 89, Response 'C': 21, Response 'D': 8
>>
>> Question02: Response 'A': 111, Response 'B': 18, Response 'C': 0,
>> Response 'D': 0
>>
>> ...
>> Question11 :
>> Response 'A': 3, Response 'B': 21, Response 'C': 75, Response 'D': 30
>>
>> How would something like this be done in BIRT? I have the BIRT Field
>> guide and I've been reading through the examples but these focus around
>> the more classic "sales per month" type aggregates but I can't seem to
>> find any examples for something like this. Can someone point me in the
>> right direction?
>>
>>
>
>
|
|
|
Goto Forum:
Current Time: Wed Apr 24 18:18:29 GMT 2024
Powered by FUDForum. Page generated in 0.04612 seconds |
] 
Search
Help
Register
Login
Home
