[ASK] How to set details?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=18321
Printed Date: 02 May 2025 at 9:46pm
Topic: [ASK] How to set details?
Posted By: chasingrainbow
Subject: [ASK] How to set details?
Date Posted: 25 Dec 2012 at 7:31pm
[spoiler=tampilan report]
http://imageshack.us/photo/my-images/838/31420724.jpg/">
Uploaded with http://imageshack.us">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
|
Replies:
Posted By: Emir_W
Date 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.
-------------
Emir W
|
Posted By: chasingrainbow
Date 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
|
Posted By: chasingrainbow
Date 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
|
Posted By: chasingrainbow
Date 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
|
|