Skip to Content
0

Crystal Layout with sub report

Apr 04, 2017 at 11:28 AM

236

avatar image
Former Member

Hi Experts,

I am creating a crystal layout of Sales Order with commands.

This sale order contain its header and line items. So i added a command by the query.

Main Report - Command Query:

SELECT distinct a.[DocEntry], a.[DocNum], a.[DocType], a.[DocStatus], a.[DocDate], a.[DocDueDate],a.[CardCode],a.[CardName],a.[Address],a.[NumAtCard],
a.[DocCur],a.[DocTotal],a.[DocTotalFC],a.[PaidToDate],a.[PaidFC],a.[Ref1],a.[Ref2],a.[Comments],b.[DocEntry], b.Itemcode, b.Dscription, b.Quantity, b.Price, b.LineTotal FROM [ORDR] a inner join [RDR1] b on a.DocEntry = b.DocEntry
where a.DocEntry = {?DocEntry}

I set the header fields at the report header and line fields at the details area. Till this it is ok.

I have a PO number stored in the ORDR table and i want to fetch the data (line items) of the PO in the subreport. So i inserted one more section in the details area. Now how can i pass the PO number stored in ORDR to get the PO details in the subreport.

Please help me.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Ian Waterman Apr 04, 2017 at 12:48 PM
0

I assume you have placed subreport in the lower detail section

Right click Subreport and select Edit subreport links

Assuming PO Num is a.DocNum drag that into the top right pane.

In lower left drop down select corresponding filed from subreport.

Main report and SR are now linked by this data, if you open Select expert in SR you will see Crystal has created a new parameter which is sued to filter SR data.

Ian

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi, Thanks for reply.

I added a command in the subreport which fetches the PO data (line items) and passing the po number from main

report to subreport which is stored in the sales order table. As i placed the sub report in the details area, i am getting

the subreport result but twice (duplicated). I don't know what to do. can anyone help me.

subreport.jpg (76.6 kB)
0
Ian Waterman Apr 04, 2017 at 02:47 PM
0

What do you mean?

Is the subreport duplicated or the data in the subreport duplicated.

How many detail lines do you get in main report for each PO?

Ian

Show 6 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi, No! subreport itself is duplicating.

What you mean detail lines in main report for each PO.?

Agenda of the layout is to get the sales order layout including PO details which i had linked with SO by UDF.

So as soon as i preview layout, i should get the line of SO in details area (1) and PO lines in details area (2).

Please suggest.

duplicating.jpg (28.6 kB)
0

If the SR is duplicating then you need to look at links or select statement.

When you run the SQL from the SR do you get duplicate fields?

Alternatively take the lazy approach and set report options to bring back distinct records.

OR

Group SR and use that group footer to display data thereby eliminating duplicates.

Ian

0
Former Member

For subreport i am using the below query which give me only specific report when passing paramter from main to sub report.

Select distinct a.DocNum, b.DocEntry, b.ItemCode, b.Dscription, b.Quantity, b.Price, b.LineTotal
from OQUT a inner join QUT1 b on a.DocEntry = b.DocEntry

It is duplicating the complete sub report section as i shown above in the picture.

I tested subreport individually it is giving me correct result. But when i test with main report, it is duplicating.

Is there anything i can manage in the main report for duplicating subreport.?

I did not understand your solution, can you please explain me bit in detail.

Alternatively take the lazy approach and set report options to bring back distinct records.

OR

Group SR and use that group footer to display data thereby eliminating duplicates.

0
Former Member

Just now i checked it with moving the subreport down to footer section. It is showing me correct.

But how can i use my subreport without duplicating in the detail section not in the footer section?

0

My misunderstanding. I thought you meant SR was duplicating data.

So your detail line is duplicating?

Remove subreport and try to identify cause of duplicating records.

Lazy options

1.Go to File -> Report Options check select distinct records

2. Group data on another field within current grouping and use that group header to display data and hold SR instead of the detail line.This will eliminate dups but will cause issues with Summaries as they will include duplicated data, you will have to replace with Running Totals which only evaluate on change of your new group.

Ian

0
Former Member

crystalr.jpg

When i am trying the preview the crystal layout from SAP. It is asking me to pass the parameter. DocEntry.

Why it is not taking automatically, can you please let me know.?

crystalr.jpg (124.9 kB)
0
Ian Waterman Apr 06, 2017 at 12:42 PM
0

Not familiar with SAP and how it passes Parameters.

Sorry can't help, you will need to contact SAP forum and ask how reports are integrated into application.

Ian

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 06, 2017 at 01:11 PM
0

guys,

I have created a crystal layout (through OLEDB Connection) for Sales Order with subreport (with common paramter 'DocEntry'). When i assigned the layout to the sales order and viewing as layout preview it is asking me to enter the parameter.

My question, why it is not taking running DocEntry from SAP B1 sales order automatically. Is there anything i have to supply in layout?

Can anyone help me?


cr2.jpg (274.4 kB)
Show 4 Share
10 |10000 characters needed characters left characters exceeded

That's not the parameter screen that is the database logon screen.

SAP application should store the DB logon and provide link to execute report without user intervention.

Ian

0
Former Member

Hey! i shared the above screen to show my OLDEB connection for layout not for parameter.

Let me share you the parameter screen, while i am previewing the sales order from SAP B1.

0
Former Member

Your Parameter`s name should be @DocKey

0
Former Member

Hi Ahmed,

DocKey@ is working only after i save the document (Quotation) otherwise not working.

means preview before adding the document is not working.

0