Details Section format, paylip-like report. landscape division

Hi..

I wonder if it is possible to create a report like payslip however
the there should only be 2 employees' payslip per page.
like in the image here.

Should I make a subreport here?

Attachment 120279

I guess it's achievable in Crystal Report, but you will be doing a very lot of work...

you can use a Subreport. And a lot of formulas to ensure that you can have 2 Employees per page.

---

The 1st thing you need to solve is, how will you take 2 employess? Are there Employee IDs in numeric order?

What if Employee 2 is deleted and Employee 1 is the first on the list, how will CR take the next employee?

It's hard to think though... *sigh*

Quote:

---

Originally Posted by terry002

The 1st thing you need to solve is, how will you take 2 employess? Are there Employee IDs in numeric order?

What if Employee 2 is deleted and Employee 1 is the first on the list, how will CR take the next employee?

It's hard to think though... *sigh*

---

I will be pulling up the data using a query so there is a possibility that there will be lots of employees. and there will only be one record per employee. It doesnt matter if an employee is deleted.

here's the catch how can you tell the 2nd Subreport that he will be printing the next record?

That's what I'm worrying about... :confused:

That's where I'm getting trouble. If there will only be one employee per page, that's a piece of cake

That's true... man that's the hard part.... :(

Is it requested by the client?

You can do it actually, now I know,

You need to put your records to be printed inside a VIRTUAL TABLE in your SQL, with a TRACE NO. (1-# of records in your virtual table)

With that you can put a formula in your CR that it will print the next TRACE NO. (From your virtual table)... Something like that logic...

With that kind of scenario, you can use the 2 subreports.

I don't have so much time since we are just halfday today here in the office and will be leaving in a while.:wave:

yeah.
inserting another Details Section will not help as well.

are u in the philippines?

Another tip:

Use GLOBAL VARIABLE, because you will use it in both of your SUBREPORTS... (UPDATE IT PER SUBREPORT)

Can't elaborate it yet, but I know it will work.

---

To answer your question (thumbs up)

I know you're from the Phils. from your 1st post about your payslip some time ago.

will try researching your suggestions. hoping for luck :) Thanks!

Your Virtual Table will be like your Payslip Table

You will just add 1 column for your tracing number 1 to Number of records.

That will be your guide in printing your payslip. and tell the CR which records it will print. ;)

---

Will be logging back next week... gtg! goodluck! ^_^

Ah sorry..this is going to be a different format.

There will be multiple records per employee in this case.



Attachment 120283

Quote:

---

There will be multiple records per employee in this case.

---

So did you finished working on it? :)

not yet,,,just figured out that the query will return multiple records per employee

Quote:

---

Originally Posted by bjay_tiamsic26

not yet,,,just figured out that the query will return multiple records per employee

---

It means you have 2 tables?

Am I correct?

You can still use 2 subreports then. :ehh:

---

I think we are the only two who talks in this thread, so we can use our native language then... :bigyello:

ahaha, i guess so.
hmm no...
At first i thought there will only be one record per employee but now
there will be multiple...

ganto po kakalabasan..

Attachment 120457

there are specifically 2 employees in that table (the image)

Ganito gawin mo 2 subreports (pero same format):

Dapat yang data mo naka virtual table yung isa.

I guess yung 1st column mo dyan e yung Employee number (key mo yan sa inner join mo).

Sa virtual table gagawin mo, same ng Employee table Schema, the difference is maglalagay ka ng isang (1 ) column (auto-Number) this will be your key in telling the Crystal Report kung anu priprint nya sa page na yun. (kunin mo lang ang kailangan mo)

Basically you will start with ID 1 and 2 para sa 1st page and 3 and 4 sa 2nd Page.

Tapos from ijoin mo yung virtual table mo dyan sa table na yan

Sample Data:

Rowstamp---|---Emp No.---|---Name---|---Time In---|---Time Out---|
1 |900151 |Juan Cruz | 15:30 |16:45 |
1 |900151 |Juan Cruz | 14:00 |16:45 |
1 |900151 |Juan Cruz | 15:30 |16:45 |

Ngayon multiple data yan, lalagay mo Header part yung dapat 1 lang ang lalabas.

Tapos sa Detail part yung sinasabi mong multiple data.

sge po try ko..
1st time ko magvirtual table, pero nakakuha nko reference..
ill get back on this thread po pag may problem or nasolve ko na hehe
salamat!

Unti untiin natin, gawin mo muna yung sa Virtual table before tayo punta sa next step, mahirap magmadali. masmaganda kung pulido ang ginagawa saka tayo punta sa next.

Tutorials:
http://www.mssqltips.com/sqlserverti...ble-variables/

I was able to create a virtual table na po :)

DECLARE @virtualTKAA TABLE
(
ID int IDENTITY not null,
EmployeeNo nvarchar(10),
EmployeeName nvarchar(120)
)


INSERT INTO @virtualTKAA (EmployeeNo,EmployeeName)
select distinct EmpNo, (Select LastName +', ' +FirstName from dbo.viewEmploymentHistory where EmployeeNo = tkAA.EmpNo ) as EmpName from [dbo].[TimeKeeping_Details_AA] tkAA where cutoffid=2

Select * from @virtualTKAA

I have two main tables po na pnagkukunan,
Timekeeping_Details ung multiple values
Timekeeping_Summary yan po ung mga summation, so isang record sa isang employee lang

pano ko po sasabhin kay crystal report na 1 & 2 for page 1?

Quote:

---

Originally Posted by bjay_tiamsic26

I have two main tables po na pnagkukunan,
Timekeeping_Details ung multiple values
Timekeeping_Summary yan po ung mga summation, so isang record sa isang employee lang

---

never mind this reply po :D

Kaya 2 sub reports gagamitin mo, kasi may GLOBAL variable ka, which contains your "Auto-number" ID. (starts from 1 to number of records)

bawat galaw ng Sub Report, nag uupdate yung Variable na yun, which yun ang gagamitin mo para sa sub reports.

For Example (initializing Global Numbervar from your Main Report to 1):

Sub report 1 (hahanapin nya si ID 1):
Global Numbervar ID;

//After magprint ni Sub Report 1
ID := ID + 1
// so it means ID = 2

Subport report 2 (hahanpin nya si ID 2):
Global Numbervar ID;

//After magprint ni Sub Report 2
ID := ID + 1
//magiging ID = 3

Tuloy natin bukas ang pag uusap, out na kasi namin sa office. ^_^

sge po..

ill try to research incrementing global variable.

may function po ba para malaman ni crystal report na tapos na magprint si Subreport 1 sa page 1?

I haven't tried it yet actually, parang pure logic pa lang yan, kasi di pa naman pinapagawa sa akin ang ganyang project. try ko minsan.

Update:

Di pala pwede gamitin yung FORMULA (SHARED VARIABLES) sa Subreports, hahaha! sakit din pala sa ulo pinapagawa ng client mo... :P

Ei @bjay_tiamsic26...

just found a solution for your INQUIRY... :bigyello:

tried and tested...

Attachment 120765

Wow!

Will you share it? hehe .. pleaseeeeeee

Requirements:

2 - tables
1 - Subreport
1 - Crystal Formula

---

1st table - (same fields from your Main Source), with additional field ID or something that MUST be AUTO INCREMENTED
2nd table - same fields as your 1ST TABLE, the difference is the ID field is NOT AUTO INCREMENTED. Why? because it will be your EVEN ID TABLE...

---
1. INSERT all the data you need from your 1st table, I'm sure it will be numbered automatically.(1 - # of records)
2. From the 1st table make use of INSERT SELECT statement and INSERT all the EVEN IDs by using the WHERE Clause "WHERE ID mod 2 = 0"

---

In your Crystal Report:
1. Make your MAIN REPORT as table 1. (be sure to SELECT ONLY THE ODD IDs)
2. Create a FORMULA, that will be the LINK to your SUBREPORT. formula must be (ID of your MAIN REPORT + 1)
2. Create a SUBREPORT for your EVEN TABLE then CHANGE THE SUBREPORT link to your formula...

Like the one attached above... Napansin mo ba yung System ID sa right ay same sa FORMULA sa kanan>?;)

Madali lang pala... ginawa ko lang ng 10 mins.

Kung Auto-Incremented ang field mo sa 1ST TABLE, dapat mareset mo sya sa 1... para wala problema ang source mo.

still need to create a virtual table in this case correct?
Do you have sample of the project?

Nope I don't have a sample project,

I just tested the database tables that I have here in the office.

About Virtual tables, if you want to use that kind of style, you need to make your SOURCE as a Stored Procedure.

But if you don't know how, you need to use a Physical table, which the records are deleted and refreshed everytime you print your payslip.

can you guide me step by step?
do you have hangout hehe?

naka-provide na yung step by step, nasa taas na... :rolleyes:

Is there other way to make an additional field for my Main Table than creating a virtual table?

Quote:

---

1st table - (same fields from your Main Source), with additional field ID or something that MUST be AUTO INCREMENTED

---

my maintable actually has an incrementing ID named AA_TkSummaryID

Here is my main table for the Main Report

Attachment 120805

And here is my 2nd table, need to be in the subreport

Attachment 120807

Is your way can fit on this? :)

Create ka ng new table,

yung main report mo, papasok mo yung data na andyan sa Main Table mo,

Tapos lagyan mo ng Integer na nag-au-auto increment.

yung 2nd report mo? naka-join sa main report mo?

Quote:

---

Originally Posted by terry002

yung 2nd report mo? naka-join sa main report mo?

---

you mean the subreport?.. no, hindi ysa naka-join sa main report.

I mean yung Main table mo, yung Details nya is yung nasa main report mo?

So mangyayari dyan, yung 1st table mo i-jojoin mo sa 2nd table mo, <main table>.AAEmpno = <2nd table>.Empno

Yes Main Table is ung nasa Main Report.

So need ko sya ijoin?
I was thinking na ung EmployeeNo sa Main Report un ung gagawin kong parameter para sa subreport

yes po Maintable is ung main report tas sa details nya may subreport, para sa subreport na to ung 2nd table.
I was thinking ung EMployeeNo na nasa main report ko ung kukunin kong parameter para sa subbreport.

Pwede ba un?

Main report lang ang i-duduplicate at triplicate mo....

Duplicate is for the Main Report Fields + the Autoincrement no.

Triplicate is for the EVEN IDs...

yes pwede yun, susubreport mo yung details mo,

bale 3 sub reports gagawin mo... ^_^

1 - for the EVEN IDs (parang main report mo)

2 - for the details of the subreports.

ganto po Attachment 120813

Header sa 2nd RIGHT PIC MO,

gagawin mo din sub report yan... para sa EVEN IDs mo.

yung blue na box.

Attachment 120815

From Left to Right:

L-R

1st pic:

Original table (named TBLUSERS)

2nd pic:
Duplicate table from the Original Table with AUTO-INCREMENT (named TBLUSERSTEST)

3rd pic:
Triplicate table from the Original Table without AUTO-INCREMENT (named TBLUSERSEVEN)

Popo-pulate mo yung 2nd table mo from the 1st table, pansin mo same sila ng Fields, dumagdag lang yung ID...

Pero may ibang purpose...

Check mo yung mga name ng Tables.

Attachment 120817

Sample data...

L-R

Original table

Duplicate Table

Even Table

Attachment 120819