cancel
Showing results for 
Search instead for 
Did you mean: 

re: Confused with the logic.Plzz Help.

Former Member
0 Kudos

Hi All,

I am a newbie to Crystal Reports. I am generating a work center report and have some issues with it.

I have an Activity_type and Activity_rate fields grouped by Person responsible for each Plant. I need to get the Activity_type by date.

Logic to do this:

table1.ARBID=table2.OBJID and

table2.OBJID=table3.OBJID and

table1.DATE >= table3.Date

[Imp note: Table1.ARBID is not linked to Table2.OBJID(if i link them, all other data is not getting displayed)].

Also, Logic for Activity_Rate:

Select Table4.TKG001

from table4

where table4.objectno = @form_act_Objno

How can i work with this logic in Crystal reports.

Any kind of advice is appreciated.

Thanks In Advance!!

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

HI Jason,

Thanks for your reply. I am using SAP tables to generate a report. But again i am not familiar with SAP. Is there any way, i can incorporate this SQL statement's logic in Crsytal reports?(using formula field)

Also, table1 and table2 are linked but the fields i am connecting with are not linked,even though they are similar. Even if i link them, my data in the other fields are not getting displayed.

I hope, i haven't confused you.

Thank you.

Former Member
0 Kudos

Well there's nothing to special about in that SQL. In fact there's nothing there that can't be done by adjusting the link properties in the Links tab of the Database Expert. Just right click the line that joins the tables.

Also... If you are allowing CR to create your links for you, DON'T!!! It does a miserable job of it. Create all of your links manually.

Also #2... Not every table should to be linked to every other table, but 99.9% of the time ALL tables should be linked together.

Having an unlinked table or group of tables can cause a Cartesian product in your data set.

Former Member
0 Kudos

I think I have done auto-linking. Thank you. I'll try that and get back to you.

Former Member
0 Kudos

You can simply join your tables on the fields mentioned in 1st two conditions of your question. Third condition can be stated in filter criteria. The fourth condition can be stated in filter or formula based on how you want to use it. Tables shouldn't be joined on any other condition.

Apart from that, as the # of tables are not that many, if you don't want to join them, leave them as it is. Put all your conditions in filter criteria. This will slow down the things, but still provides you the required results.

Hope this works for you

Former Member
0 Kudos

Thank you. I got it.

I really appreciate your effort.

Former Member
0 Kudos

Ragini,

If you are building your data set by adding tables and linking them in the Database Expert then you drawl lines from key field to key field. Right click the link lines and alter the link properties as needed.

The fact that you you used SQL to illustrate your problem leads me to believe that you may actually know SQL. If that's the case you can use a SQL Command as your data source using the native SQL syntax for your database.

something like this:


SELECT
Fieild List
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t1.ARBID = t2.OBJID
INNER JOIN Table3 AS t3 ON t2.OBJID = t3.OBJID AND t1.DATE >= t3.DATE

Not sure where Table 4 is coming into the mix... but you can easily pass parameter values back to a SQL Command:


where table4.objectno = '{?form_act_Objno}'

If @form_act_Objno is a formula field... You can't directly pass the results of a formula back to a SQL Command... (You can get to pass into the SQL using sub-report parameters, but it's far from intuitive, requires the use of sub-reports, and id outside the scope of do-ability for most new comers).

If it's a user supplied parameter value... no problem.

HTH,

Jason