Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » question on report design
question on report design [message #892330] Wed, 27 June 2012 18:09 Go to next message
Aaron Titus is currently offline Aaron TitusFriend
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 Go to previous messageGo to next message
Erik Marke is currently offline Erik MarkeFriend
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 #892354 is a reply to message #892335] Wed, 27 June 2012 20:53 Go to previous messageGo to next message
Aaron Titus is currently offline Aaron TitusFriend
Messages: 10
Registered: March 2012
Junior Member
Thank you Erik for the reply. While storing the data that way might make the generation of the report easier, it will make the rest of the application which deals with the survey processing more complex. I'm not sure this is a good trade off. I see understand how this SQL will return the correct result, and I thank you for that. However this will be a monster SQL query... and as we add questions its only going to get worse.

Is there any way that this could be handled within the BIRT report itself that does not involve pushing the logic back to SQL? A very simple java program could read the rows in one pass and compute a tally... is there a way to somehow leverage that concept in BIRT report?
Re: question on report design [message #892391 is a reply to message #892354] Thu, 28 June 2012 04:15 Go to previous messageGo to next message
Erik Marke is currently offline Erik MarkeFriend
Messages: 2
Registered: June 2012
Junior Member
you are welcome, Java not me Wink
Just one point of consideration, if you would want to add one question in your data modell you would have to adjsut the table and add additional columns, but you might have planned for this and just added some dummy columns. your design might not be that flexible once you come into the first lyfecycle and need to do adjsutment in regards of the survey questions, have additional survey with different questions, .... Therefore reviewing your mid-longterm requirements might be of benefit to you on the long run.

good luck.
Re: question on report design [message #892504 is a reply to message #892391] Thu, 28 June 2012 13:29 Go to previous messageGo to next message
Aaron Titus is currently offline Aaron TitusFriend
Messages: 10
Registered: March 2012
Junior Member
The web application uses JPA. Addition of additional columns is quite painless.
Re: question on report design [message #892586 is a reply to message #892330] Thu, 28 June 2012 15:52 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

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 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

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 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

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?
>>
>>
>
>
Previous Topic:'Internal error' when opening PDF preview window
Next Topic:Zero Display ...
Goto Forum:
  


Current Time: Sat Nov 22 10:33:29 GMT 2014

Powered by FUDForum. Page generated in 0.09243 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software