Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: [ASK] How to set details? Post Reply Post New Topic
Author Message
chasingrainbow
Newbie
Newbie
Avatar

Joined: 31 Oct 2012
Location: Indonesia
Online Status: Offline
Posts: 5
Quote chasingrainbow Replybullet Topic: [ASK] How to set details?
    Posted: 25 Dec 2012 at 7:31pm
[spoiler=tampilan report]


Uploaded with
ImageShack.us
[/spoiler]

I'm stuck here till.
I want details on parallel between "Penerimaan" and "Potongan" as shown in the detail report header above.
how?

funtion penerimaan:

IF {Command.Plus_Minus} = "+" then
    formula = {Command.DS_Desc}
end if


funtion potongan :
IF {Command.Plus_Minus} = "-" then

    formula = {Command.DS_Desc}
end if


[spoiler=query]

DECLARE @sqlCommand varchar(1000)
DECLARE @SQLParam varchar(200)
DECLARE @SQLParamValue varchar(200)
DECLARE @SQLParamResult varchar(200)
DECLARE @SQlQuote char(1)
DECLARE @Comp_ID varchar(4)
DECLARE @Periode varchar(7)
DECLARE @prd varchar(1)


SET @SQLQuote = char(39)
SET @Comp_ID = '{?Comp_ID}'
SET @Periode = '{?Periode}'
SET @prd = {?Prd}
SET @SQLParamValue = '{?SQLParamValue}'
SET @SQLParam = '{?SQLParam}'
SET @SQLParamResult = ' AND ' + @SQLParam + ' = ' + @SQLQuote + @SQLParamValue + @SQLQuote

if(@SQLParamValue = '')
SET @SQLParamResult = ''

SET @sqlCommand = 'SELECT a.login_id,a.DS_Code, b.Plus_Minus, b.Taxed, b.DS_Desc, a.Curr_ID, b.DS_Urutan, SUM(a.Amount) As Amount , d.Nama_Divisi, e.Dept_Name, c.ID_Divisi, c.Dept_ID, ' +'''+ IDR +'''+ 'As Curr_ID
     FROM PrSalary a
      INNER JOIN Pr_DetailSalary b ON a.Comp_ID=b.Comp_ID AND a.DS_Code=b.DS_Code
      INNER JOIN Tbl_Prl_General_Info c ON a.Comp_ID=c.Comp_ID AND a.Login_ID=c.Login_ID
      INNER JOIN Divisi d ON c.Comp_ID=d.Comp_ID AND c.ID_Divisi=d.ID_Divisi
      INNER JOIN TDept e ON c.Comp_ID=e.Comp_ID AND c.ID_Divisi=e.ID_Divisi AND c.Dept_ID=e.Dept_ID
     WHERE a.Comp_ID=' + @SQLQuote + @Comp_ID + @SQLQuote + ' AND a.Periode=' + @Periode + ' AND a.Prd='+ @prd +'
     '+ @SQLParamResult +'
     GROUP BY a.login_id,a.DS_Code, b.Plus_Minus, b.Taxed, b.DS_Desc, a.Curr_ID, b.DS_Urutan ,d.Nama_Divisi, e.Dept_Name, c.ID_Divisi, c.Dept_ID
     ORDER BY a.login_id, b.DS_Urutan'

EXEC(@sqlCommand)

[/spoiler]

thanks gan,                            
those you can do, those you cant learn
IP IP Logged
Emir_W
Senior Member
Senior Member
Avatar

Joined: 25 Apr 2010
Online Status: Offline
Posts: 228
Quote Emir_W Replybullet Posted: 26 Dec 2012 at 6:49pm
hi Chasingrainbow,

i just know in SQL, so try to understand your issue.
i think you can do something like this:
----------------------------
select
  a.field_a, a.field_b, b.field_a, b.field_c, ...,
  penerimaan=if(table.plusminus='+', command.ds_desc, ''),
  potongan=if(table.plusminus='-', command.ds_desc,''),
  c.field_a, ..., d.field_a, etc., etc.
from
.....
-----------------------------------------------
in this case CR will read those fields (penerimaan and potongan) from the Dataset.


hope it help.




Edited by Emir_W - 26 Dec 2012 at 6:49pm
Emir W
IP IP Logged
chasingrainbow
Newbie
Newbie
Avatar

Joined: 31 Oct 2012
Location: Indonesia
Online Status: Offline
Posts: 5
Quote chasingrainbow Replybullet Posted: 27 Dec 2012 at 10:10pm
Originally posted by Emir_W


hi Chasingrainbow,i just know in SQL, so try to understand your issue.i think you can do something like this:----------------------------select   a.field_a, a.field_b, b.field_a, b.field_c, ...,   penerimaan=if(table.plusminus='+', command.ds_desc, ''),  potongan=if(table.plusminus='-', command.ds_desc,''),  c.field_a, ..., d.field_a, etc., etc.from.....-----------------------------------------------in this case CR will read those fields (penerimaan and potongan) from the Dataset.hope it help.


thanks for reply mr emir, follow your idea, but the result still got a blank column.

-------------------------------------------------
|penerimaan              |     potongan        |
-------------------------------------------------
|Allow. J-Tek                 |                   |
|Allow. Tax                 |                   |
|Bonus Allowance         |                   |
|Communication D         |                   |
|Communication Allowan         |                   |
|Frame Glasses                 |                   |
|Glasses for Family         |                   |
|                   |     Pot. Housing    |
|Housing Allowance         |                   |
|Hospital for Family         |                   |
|Hospital for Employee         |                   |
|Shortage Tunj. Jabatan         |                   |
|Kedukaan Kel. Karyawan         |                   |
|Kedukaan Karyawan         |                   |
|Lensa                         |                   |
|Maintenance Allowance         |                   |
|Meal Monthly                 |                   |
|Meal Daily                 |                   |
|Medical                 |                   |
|Meal Overtime                 |                   |
|Main Salary                 |                   |
|Overtime                 |                   |
|                   |     PPH 21          |
|                   |     Pot. Koperasi   |
|                   |     Pot. Lainnya    |
|                   |     Pot. J-Tek      |
|Retirement                 |                   |
|Subsidy Allowance         |                   |
|Shortage Jamsostek Allow   |                   |
|Shortage Main Salary         |                   |
|Shortage Overtime         |                   |
|Shortage Transport         |                   |
|Tunj. Persalinan         |                   |
|Shortage Maintenance Allow |                   |
|Shortage Meal Overtime         |                   |
|Sumbangan Nikah         |                   |
|Shortage Transport Overtime|                   |
|THR Allowance                 |                   |
|Tunj. Jabatan                 |                   |
|Tunj. Profesi                 |                   |
|Transport Monthly         |                   |
|Transport Daily         |                   |
|Transport OT               |                   |
----------------------------|-------------------|               


my expect


-------------------------------------------------
|penerimaan              |     potongan        |
-------------------------------------------------
|Allow. J-Tek                 |   Pot. Housing    |
|Allow. Tax                 |   PPH 21          |
|Bonus Allowance         |   Pot. Koperasi   |
|Communication D         |   Pot. Lainnya    |
|Communication Allowan         |   Pot. J-Tek      |
|Frame Glasses                 |                   |
|Glasses for Family         |                   |
|Housing Allowance         |                   |
|Hospital for Family         |                   |
|Hospital for Employee         |                   |
|Shortage Tunj. Jabatan         |                   |
|Kedukaan Kel. Karyawan         |                   |
|Kedukaan Karyawan         |                   |
|Lensa                         |                   |
|Maintenance Allowance         |                   |
|Meal Monthly                 |                   |
|Meal Daily                 |                   |
|Medical                 |                   |
|Meal Overtime                 |                   |
|Main Salary                 |                   |
|Overtime                 |                   |
|Retirement                 |                   |
|Subsidy Allowance         |                   |
|Shortage Jamsostek Allow   |                   |
|Shortage Main Salary         |                   |
|Shortage Overtime         |                   |
|Shortage Transport         |                   |
|Tunj. Persalinan         |                   |
|Shortage Maintenance Allow |                   |
|Shortage Meal Overtime         |                   |
|Sumbangan Nikah         |                   |
|Shortage Transport Overtime|                   |
|THR Allowance                 |                   |
|Tunj. Jabatan                 |                   |
|Tunj. Profesi                 |                   |
|Transport Monthly         |                   |
|Transport Daily         |                   |
|Transport OT               |                   |
----------------------------|-------------------|
      
those you can do, those you cant learn
IP IP Logged
chasingrainbow
Newbie
Newbie
Avatar

Joined: 31 Oct 2012
Location: Indonesia
Online Status: Offline
Posts: 5
Quote chasingrainbow Replybullet Posted: 01 Jan 2013 at 7:57pm
I want to make like this sir


so if there are 2 different querynya details

for example:
penerimaan
SELECT * FROM WHERE plus_minus pr_salary = '+'

potongan
SELECT * FROM WHERE plus_minus pr_salary = '-'

report      on the top of the programming language, I use it because I was stuck in cr. so it could be like that. hehe

whether crystal reports in programming like that?
those you can do, those you cant learn
IP IP Logged
chasingrainbow
Newbie
Newbie
Avatar

Joined: 31 Oct 2012
Location: Indonesia
Online Status: Offline
Posts: 5
Quote chasingrainbow Replybullet Posted: 09 Jan 2013 at 6:20pm
SOLVED , i'm insert data in new table from SP
thanks Mr. Erik
those you can do, those you cant learn
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.