I have a report for Campus Summary Report .

[Former Member]

Hi,

I have a report for Campus Summary  Report .

There are TWO database table are

ONE TABLE IS:

Semester Fee Package Table

Roll NO                  Semester Program                                Fee_Package

1101                       1                              Bcs                         4000

1102                       1                              Bcs                         5000

2101                       2                              MBA                       3000

2102                       2                              MBA                       4000

-----------

Second Table

Semester Fee Payment Table

Roll No                   Semester program                                Recive_Amount                   Pending Amount

1101                       1                              Bcs                         3000                                       1000

1102                       1                              Bcs                         3000                                       2000

2101                       2                              MBA                       3000                                       0

2102                       2                              MBA                       1500                                       1500

2102                       2                              MBA                       1500                                       0

-----------------

I want to Make Report Like That

Group Header Program

Program                                No Of  Student                     Receive Amount                                  Pending Amount

Bcs                         2                                              6000                                                       3000

MBA                       2                                              3000                                                       4000

                                Total Receive Amount=      9000                                       Total Pending Amount=     7000                      

Please reply.Thanks.

[raghavendra_hullur · Accepted solution]

Hi,

Are you using a command object to create a query for this?

If so, create a query like this:

select table1.program, count(table1.rollno) no_of_student, sum(table2.Recieve_Amount) Received_Amount, sum(table2.Pending)  Pending_Amount

from Semester_ Fee_Package_Table table1, Semester_Fee_Payment_Table table2

where table1.rollno = table2.rollno

Change the database objects as per your tables and column names.

Use the database fields directly on detail section in Crystal Report.

And if you are not using a Command Object and simply want to link the tables, then follow below steps:

1. Click on new report icon on left top.

2. Select the database details by creating a connection and then select tables.

3. Link Table1 and Table 2 using RollNo field.

4. Select the fields required to be displayed. E.g RollNo, Progra from first table and Other fields from second table.

5. Select Program under Group By option.

6. Under Summaries options, select Count of rollNo, sum of Receive Amount and Sum of Pending Amount.

7. Complete report creation.

Now, you can rearrange the fields as you require in the report.

Thanks,

Raghavendra

[Former Member · Accepted solution]

thanks to Raghavendra Hullur

my question is that how to calculate pending amount in report using formula

i want to calculate pending amount like that

from semester fee table program MBA ,column Fee_Package as Total fee is (3000+4000) = 7000

from Semester Fee Payment Table column program MBA , column Recieve_Amount as total recieve amount( 1500+1500)=3000

@pen=(Total fee is (3000+4000) = 7000 ) - (total recieve amount( 1500+1500)=3000)

-------------

if i am using like above formula ,i have problem if Semester Fee Package Table in 3 students of Fee_Package, data saved there

and

from Semester Fee Payment Table in 2 Students paid Fee and 1 student fee not receive , then 2 students Receive_Amount should be calculate shown in report  , but in crystal report 3 student Receive_Amount  calculated shown(wrong calculation calculate amount if total 3 students and 2 paid fee ,1 student not paid fee),

Group Header Program

Program               No Of  Student                     Total Fee              Receive Amount                         Pending          

(Fee Package     count(                                        sum(                     Sum(                                          @pen

table                       Semester Fee Package       Semester Fee      Semester Fee

.Program)               Table.Roll NO)                      Package table.    Package Table.                                                                                                                          Fee_Package)        Receive Amount)

                           sum(Total no of Student)       sum(Total Fee)          Sum(Total Receive          Sum(total    

                                                                                                                     Amount)                             Pending)

---------------------

i want to print like this Crystal report but problem there  please help

Group Header Program

Program               No Of  Student       Total  Fee                 Receive Amount                    Pending

Bcs                         2                               9000                              6000                                       3000

MBA                       2                                 700                            3000                                         4000

--------------------------------------------------------------------------------------------------------------------

                               4                                                 9000                                                      7000

There are TWO database table are

ONE TABLE IS:

Semester Fee Package Table

Roll NO                  Semester           Program                    Fee_Package

1101                       1                              Bcs                         4000

1102                       1                              Bcs                         5000

2101                       2                              MBA                       3000

2102                       2                              MBA                       4000

-----------

Second Table

Semester Fee Payment Table

Roll No                   Semester program                            Recive_Amount                   Pending Amount

1101                       1                              Bcs                         3000                                       1000

1102                       1                              Bcs                         3000                                       2000

2101                       2                              MBA                       3000                                       0

2102                       2                              MBA                       1500                                       1500

2102                       2                              MBA                       1500                                       0

-----------------

Please reply.Thanks.

[raghavendra_hullur · Accepted solution]

Hi,

Do you mean, you are getting count of 3 for MBA, where as it should be 2 as there are only 2 students with roll numbers 2101 and 2102?

If that's the case, use distinct count on rollNumber field for getting count of students.

Also, on your question of 2 students fee received, and one student fee not received, is it again related to students with roll number 2101 and 2102 for MBA course?

Or is it something different you are looking for?

Thanks,

Raghavendra

[Former Member · Accepted solution]

no, i mean if 3 roll No(2101,2102,2103) are there in Semester Fee Package table and

Semester Fee Payment Table only 2102 and 2103 roll number fee paid, and no data exists regarding of roll number 2101,

then wrong calculation calculate Total fee amount and total receive amount

what can i do????

and other question is how to calculate pending fee using formula?

and other question is pending amount  formula  to summarize in footer ?

[raghavendra_hullur · Accepted solution]

Hi,

can you give an example of where you are getting wrong data total fee amount and total receive amount?

If you are using a group on program and display the summaries of Total Fee, Received Fee and Pending in group footer or group header, you should be seeing the sum of respective amounts for each program.

And to answer your question on calculating Pending using formula, assuming you have created a group on Program, create a formula and include content like below:

<database field for total fee> - <database field for Received Amount>

Insert  summary on above formula and select Group created for Program under "Select Location" dropdown

The above code will give you Sum of Pending Amount for each Program .

Answer to second question (Summary for Pending in report footer): Use the same formula created above and insert summary into report footer by selecting Report Footer option under "Select Location" dropdown.

Let me know how this works for you.

Thanks,

Raghavendra

[Former Member · Accepted solution]

HI,Raghavendra Hullur

i want to make financial Campus summary Report regarding their program e.g bba,bcom,bcs etc ,

where program related report that is no of student in related program,their receive amount of number of students and their pending fee,and total semester fee related program and in footer total of all these thing ..

--------------------

my database scenario is

student enrolled ,their data save in Hics_Student table,

when student enrolled their fee package set in Hics_St_Fee_Pkg table,

then

student register their semester registration in Hics_SemRegisteration table regarding their registeration number that is RegId Column of Hics_SemRegisteration.

student pay their fee if student RegId= BH-000513 pay fee regarding set amount package is 45000 amount (from Hics_St_Fee_Pkg table )

if student pay fee semester 1 of  20000 amount (Hics_Fee_Sem.recAm Column  )

then student RegId=BH-000513 regarding  package is 45000 -  20000=25000 save in (Hics_Fee_Sem.penAm column)

----------

i enter campus code that is campId column and enter semester id 1 that is semId column

then regarding their campus ,program financial report shown

---------------

if 3 student Registered of program B-01, semId 1 (column as semester Number) in Hics_SemRegisteration table

AND

1  student Registered of program Cs-02 in Hics_SemRegisteration table

then in report total no of student shown is 3 OF  program B-01 that is registered in Hics_SemRegisteration table

and 1 student of cs-02.....

3 of students registered  , only 2 students fee paid of semester 1 and 1 students not paid

but

problem in report is only show 2 students,not showing 3, that was my problem in report and wrong calculation

what can i do?

please help ,i send you snap shoot of my project

-------

[raghavendra_hullur · Accepted solution]

Hi,

Few questions:

1. Do you have any additional groups apart from ProgramId?

2. Is there any additional report filter logic used under Record Selection?

3. How are you determining whether a student has paid fee or has pending amount? Is it based on total fee - received fee only or any other column is available to check about Pending fee status?

Is it possible to share the report with saved data (by selecting "Save Data With Report" checked under file menu)?

If so, please save data with report and save a copy of it (say on desktop). Then rename the file as .txt instead of .rpt. and then share it either as an attachment or thru email. Zip it if the size is more than 1 MB.

Email ID is available in profile.

Thanks,

Raghavendra

[Former Member · Accepted solution]

i send you mail,check mail

[raghavendra_hullur · Accepted solution]

Hi,

There is an equi join between Hics_Fee_Sem and Hics_St_Fee_Pkg on RegId column.

Do you have data for RegId "BH-000515" in Hics_Fee_Sem table? I do not see one from the screenshots shared.

If the above said RegId is not part of Hics_Fee_Sem table, then that record will not be considered for report output as it will not be retrieved due to the equi join with HICS_St_Fee_Pkg table.

In case you want to see that as part of your report output, you have to navigate to Database Expert --> Links and right click on the link between above said tables for RegId column and select Left Outer Join (Or Right Outer Join based on from which table that particular record is missing) and check the output.

Thanks,

Raghavendra

[Former Member]

Hi

Link your tables in links tab of database expert using primary key(I think roll no) then go inside the report and do this.

1.Create a group on Program by going to insert->group->Apply summary for received amount and select summary as sum and check the option add to all group levels.

2.Do the same for pending also

3.Do the same for NoOf Students and take summary as count

3.Then move your summaries from group footer to group header

[Former Member]

thanks to  Vissu Pangam

my question is that how to calculate pending amount in report using formula

i want to calculate pending amount like that

from semester fee table program MBA ,column Fee_Package as Total fee is (3000+4000) = 7000

from Semester Fee Payment Table column program MBA , column Recieve_Amount as total recieve amount( 1500+1500)=3000

@pen=(Total fee is (3000+4000) = 7000 ) - (total recieve amount( 1500+1500)=3000)

-------------

if i am using like above formula ,i have problem if Semester Fee Package Table in 3 students of Fee_Package, data saved there

and

from Semester Fee Payment Table in 2 Students paid Fee and 1 student fee not receive , then 2 students Receive_Amount should be calculate shown in report  , but in crystal report 3 student Receive_Amount  calculated shown(wrong calculation calculate amount if total 3 students and 2 paid fee ,1 student not paid fee),

-----------------------

Group Header Program

Program               No Of  Student                     Total Fee              Receive Amount                         Pending          

(Fee Package     count(                                        sum(                     Sum(                                          @pen

table                       Semester Fee Package       Semester Fee      Semester Fee

.Program)               Table.Roll NO)                      Package table.    Package Table.                                                                                                                          Fee_Package)        Receive Amount)

                           sum(Total no of Student)       sum(Total Fee)          Sum(Total Receive          Sum(total    

                                                                                                                     Amount)                             Pending)

---------------------

i want to print like this Crystal report but problem there  please help

Group Header Program

Program               No Of  Student       Total  Fee                 Receive Amount                    Pending

Bcs                         2                               9000                              6000                                       3000

MBA                       2                                 700                            3000                                         4000

--------------------------------------------------------------------------------------------------------------------

                                4                                                 9000                                                      7000

There are TWO database table are

ONE TABLE IS:

Semester Fee Package Table

Roll NO                  Semester           Program                    Fee_Package

1101                       1                              Bcs                         4000

1102                       1                              Bcs                         5000

2101                       2                              MBA                       3000

2102                       2                              MBA                       4000

-----------

Second Table

Semester Fee Payment Table

Roll No                   Semester program                            Recive_Amount                   Pending Amount

1101                       1                              Bcs                         3000                                       1000

1102                       1                              Bcs                         3000                                       2000

2101                       2                              MBA                       3000                                       0

2102                       2                              MBA                       1500                                       1500

2102                       2                              MBA                       1500                                       0

-----------------

Please reply.Thanks.