including complex SQL queries for report

[bidur]

 Hi all,

I am new to iReports and I am trying to create a report by extracting data from mysql Tables.

A sample example with help me a lot for something like the follwing scenario :

eg:

'person'  table  with  fields ( id, name, gender ) 

'program'  table  with fields  ( pid, id, name )

Now, I want to include the following information in the report:

A

1. The number of  person with gender='MALE' with program.name is 'BA'

2. The number of  person with gender='FEMALE' with program.name is 'BA'

3. Total number of person with rogram.name is 'BA'

 

B.

1. The number of  person with gender='MALE' with program.name is 'BE'

2. The number of  person with gender='FEMALE' with program.name is 'BE'

3. Total number of person with rogram.name is 'BE'

 

 

C.

 

1. The number of  person with gender='MALE' with program.name is not 'BE' or 'BA'

2. The number of  person with gender='FEMALE' with program.name  is not 'BE' or 'BA'

3. Total number of person with rogram.name  is not 'BE' or 'BA'

 

 

 

Thanks

[tdi120]

i do not get ur point exactly. Would u like the sql querys for that, the way to integrate the sql querys for that, or alltogether?

 

are a,b,c three different reports or three different datasets in one report?

 

 

All good things come to those who wait

qants.wordpress.com

[bidur]

 Hi tdi120,

all A,B,C should be displayed in a single report.

I am trying to integrate all these information to generate a report. Now I am not able to  extracts all these information (i.e. A,B,C) from a SINGLE mysql query so that it can be displayed in the report. A sample query example will help me a lot.

Again, is it possible to load multiple queries for a report?

 

Thanks.

[tdi120]

hi bidur,

 

you should be able to use multiple querys by using multiple datasets in your report. In order to do that, open the report inspector, select the top node, right click, and choose "add dataset"

 

Each query that you run in a dataset will return some fields. My recommandation would be to use "as" in your querys, to make sure you have distinct fieldnames in the report.

 

Imagine you have three datasets for your three querys. You can then use the fields that these querys return as values in your report

 

Is that what u need? Can i help u further?

Cheers,

 

Alex

 

All good things come to those who wait

qants.wordpress.com

[bidur]

Hi Alex,

Thanks a lot for your guidance.

 

Regards,

Bidur

[bidur]

Hi Alex,
I tried to add new dataset(query: "select pid from person").
Then the new dataset's fields displays 'pid'. Then I tried to display 'pid' in the report by dragging 'pid' to report. But this gives out an error
'Field not found : pid'.
Similar thing occurs for all the fields that i get by adding new dataset.

Thanks

Post Edited by bidur at 11/20/2009 07:30

[fheswara]

I'm facing the same problem with you and still looking for solution.

I dont think you can use new dataset because new dataset can be used only inside a Chart or CrossTab.

 

 

Thanks

[vivekkohli]

Try this query, it may work

 

SELECT
(SELECT COUNT(id) FROM person pe JOIN program pr ON pe.id = pr.id AND pr.name = 'BA' AND pe.gender = 'MALE') A1,
(SELECT COUNT(id) FROM person pe JOIN program pr ON pe.id = pr.id AND pr.name = 'BA' AND pe.gender = 'FEMALE') A2,
(SELECT COUNT(id) FROM person pe JOIN program pr ON pe.id = pr.id AND pr.name = 'BA') A3,
(SELECT COUNT(id) FROM person pe JOIN program pr ON pe.id = pr.id AND pr.name = 'BE' AND pe.gender = 'MALE') B1,
(SELECT COUNT(id) FROM person pe JOIN program pr ON pe.id = pr.id AND pr.name = 'BE' AND pe.gender = 'FEMALE') B2,
(SELECT COUNT(id) FROM person pe JOIN program pr ON pe.id = pr.id AND pr.name = 'BE') B3,
(SELECT COUNT(id) FROM person pe JOIN program pr ON pe.id = pr.id AND pr.name NOT IN ('BA','BE') AND pe.gender = 'MALE') C1,
(SELECT COUNT(id) FROM person pe JOIN program pr ON pe.id = pr.id AND pr.name NOT IN ('BA','BE') AND pe.gender = 'FEMALE') C2,
(SELECT COUNT(id) FROM person pe JOIN program pr ON pe.id = pr.id AND pr.name NOT IN ('BA','BE')) C3

[mohamad.hadadi85]

To show fields of a dataset you need to drag a table on your report and select the designed dataset for that table and follow the wizards of creating a table.