Does anyone here work with Crystal Reports in Mas90?
Crystal Reports in Mas90
-
-
-
Worked with Crystal Reports and a couple of different GL packages. Also worked with MAS90 before. Never done anything with the two together but am willing to try to answer any questions you have.
Kindest regards,
-
Crystal Report in Mas90
Hi,
Thanks for offering to help! I am VERY new at Crystal so please forgive this for being a very obvious question.
I have created a report that pulls payroll information. The data comes from the Earnings Register, the Check History, the Employee Master, the Check Printing Detail and Payroll Data Entry Line.
Everything works great, it shows the JOB, I added summary information by department....
HOWEVER, sigh, it is listing each check 4 times. I am ASSUMING that I have told it to at some point, maybe to list them instead of referencing them to compare??
In any case, do you have any ideas? -
So sorry for the delay in replying. I typed in my reply and hit submit 11 hours ago. IE crashed and burned and I have been unable to get back on until now. Apparently my reply never got sent. I was wondering what version of Crystal you are using?
Regards,
-
Usually, repeating records means one of couple of things.
Your table relationships (if any) are not correctly defined
Your SELECT parameters are wrong
Can you post up your current SQL code
(In Crystal, go to Database|Show SQL Query )
It will look something like this
QuoteSELECT
cust."no", cust."benefit", cust."surname",
term."csan", term."amount", term."status"
FROM
{ oj "pdunity"."dbo"."cust" cust INNER JOIN "pdunity"."dbo"."term" term ON
cust."rowno" = term."rowno_custterm_cust"}
WHERE
term."status" = 'active'
ORDER BY
cust."benefit" ASCCopy the SQL & paste it up here... It may give us some clues
-
-
SELECT
PR5_CheckHistory."Department", PR5_CheckHistory."CheckDate",
PR5_CheckHistory."CheckNumber", PR5_CheckHistory."GrossWagesThisCheck",
PR1_EmployeeMaster."LastName", PR1_EmployeeMaster."FirstName",
PR7_PayrollDataEntryLine."JobNumber"
FROM
"PR5_CheckHistory" PR5_CheckHistory,
"PR1_EmployeeMaster" PR1_EmployeeMaster,
"PR7_PayrollDataEntryLine" PR7_PayrollDataEntryLine
WHERE
PR5_CheckHistory."Department" = PR1_EmployeeMaster."Department" AND
PR5_CheckHistory."EmployeeNumber" =
PR1_EmployeeMaster."EmployeeNumber" AND
PR5_CheckHistory."Department" =
PR7_PayrollDataEntryLine."Department" AND
PR5_CheckHistory."EmployeeNumber" =
PR7_PayrollDataEntryLine."EmployeeNumber"
ORDER BY
PR5_CheckHistory."Department" ASCThis looks pretty sad I didn't write this, well in a sense I did, I was using the Report Expert. I am sorry I am at home now so I don't know the version of Crystal we are using. (I will post back in a few minutes, if I can hack into the server at work....uhm, I mean if I can acquire that information)
As for the delay, no worries, I have been working on other projects too...and now the batteries are going in my keyboard and I have tto type each letter twicee...argh. off to the store, sorrry -
I can't see any obvious error in the SQL - can you access the database direct - ie can you see if there are in fact 4 line entries.
-
I am not sure how to in Mas90. I will look into it. THANKS very much.
If I cannot figure out how to access it, how could I write a filter, or formula?, to show only 1? -
(At this point I should point out i dont' use Crystal Reports...) but given the appearance of the SQL query you should be able to use a DISTINCT clause
Select Distinct.........
as all queried fields are being matched across all tables it shouldn't result in masses of records.
-
Amended your query slightly - might be worth a punt.
Quote
SELECT
PR5_CheckHistory."Department", PR5_CheckHistory."CheckDate",
PR5_CheckHistory."CheckNumber", PR5_CheckHistory."GrossWagesThisCheck",
PR1_EmployeeMaster."LastName", PR1_EmployeeMaster."FirstName",
PR7_PayrollDataEntryLine."JobNumber"
FROM
"PR5_CheckHistory" PR5_CheckHistory,
"PR1_EmployeeMaster" PR1_EmployeeMaster,
"PR7_PayrollDataEntryLine" PR7_PayrollDataEntryLine
WHERE
(PR5_CheckHistory."Department" = PR1_EmployeeMaster."Department" AND PR5_CheckHistory."Department" = PR7_PayrollDataEntryLine."Department")
AND
(PR5_CheckHistory."EmployeeNumber" =PR1_EmployeeMaster."EmployeeNumber"
AND
PR5_CheckHistory."EmployeeNumber" = PR7_PayrollDataEntryLine."EmployeeNumber")
ORDER BY
PR5_CheckHistory."Department" ASC -
-
Where would I include the "Distinct" clause? I tried it, and must have used the wrong area, as then the report was blank.
:thanx: :read: -
From Crystal on-line help
Select Distinct Records command
Use the Select Distinct Records command to refine your report's SQL query and prevent it from returning duplicate records from the database.When this command is enabled, SQL SELECT statements are treated as SELECT DISTINCT statements. Consequently, the query looks for identical records and ensures that they are returned only once. (To be considered identical, records must match completely—not just one or two fields.)
This command is only available for ODBC, OLE DB, and SQL native drivers; it is not available for Stored Procedures. Note also that your server may automatically sort returned values if no sort is specified in Crystal Reports.
-
Hi Brandtrock,
That is what I did and now even when I refresh it, there is no data displayed. I thought perhaps I had mis-read the help file and that is why I asked where to include the "Distinct" clause.I obviously have done something to corrupt this report, when I changed the Query to what Lasw10 posted an error flashed about both the SQL and the something would be used but I dropped something on my flipping keyboard and it disappeared before I could read the error message.
So, basically I am deleting this one and starting over.
Thanks for the help guys, I am sure I will be back with this one. -
Try this then,
Once you have the original working again, go to the design tab of your report. Right click on the Checknumber field. Select Format Field from the Pop up menu. The Format Editor window that comes up has many tabs.
Select the Common tab. Near the bottom is a check box to Suppress if duplicated. Check this and refresh your report. You should only get one of each check number now.
HTH
-
Thanks Brandtrock, that last post was what I was missing! Thanks to both of you for your help, sorry I wasn't able to respond sooner; it's been a really hectic week.
Thanks again! :thanx: -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!