Details Section format, paylip-like report. landscape division

[bjay_tiamsic26]

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?

[terry002]

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*

[bjay_tiamsic26]

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.

[terry002]

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...

[bjay_tiamsic26]

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

[terry002]

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

Is it requested by the client?

[terry002]

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.

[bjay_tiamsic26]

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

[bjay_tiamsic26]

are u in the philippines?

[terry002]

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.

[bjay_tiamsic26]

will try researching your suggestions. hoping for luck Thanks!

[terry002]

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! ^_^

[bjay_tiamsic26]

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

There will be multiple records per employee in this case.

[terry002]

There will be multiple records per employee in this case.

So did you finished working on it?

[bjay_tiamsic26]

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

[terry002]

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.

---

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

[bjay_tiamsic26]

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..



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

[terry002]

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.

[bjay_tiamsic26]

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!

[terry002]

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/

[bjay_tiamsic26]

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

[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

[bjay_tiamsic26]

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

[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

[terry002]

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. ^_^

[bjay_tiamsic26]

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?

[terry002]

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.

[terry002]

Update:

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

[terry002]

Ei @bjay_tiamsic26...

just found a solution for your INQUIRY...

tried and tested...

[bjay_tiamsic26]

Wow!

Will you share it? hehe .. pleaseeeeeee

[terry002]

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.

[bjay_tiamsic26]

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

[terry002]

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.

[bjay_tiamsic26]

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

[terry002]

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

[bjay_tiamsic26]

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

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

[bjay_tiamsic26]

Here is my main table for the Main Report



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



Is your way can fit on this?

[terry002]

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?

[bjay_tiamsic26]

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

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

[terry002]

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