cancel
Showing results for 
Search instead for 
Did you mean: 

Create Report out of Query a question

Former Member
0 Kudos

Hi,

I don't have much experiece with this one. I am using the Print Layout Designer to modify a report I created with a query.

Now all of my query results are appearing in the repetitive area1 ( the detail lines ). But there are some fields that are in the repetitive area1 that I need to move to the Page Header. But PLD does not allow me to.

I treied add a Data field and assign the table.field but it does not display value(probably becuase PLD does not know how to link that field )

Simplified query:

SELECT H.DocNum, H.Ref2, L.ItemCode FROM

OIGE H INNER JOIN IGE1 L ON

H.DocEntry = L.DocEntry

I want to display the DocNum and Ref2 on Page header and ItemCode on the repetitive area1. But all are in the repetitive area1.

I cannot use the advance Layout Designer since it does not support Parameters, and my query uses a parameter.

Thanks,

Indika.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Indika

As far as I know you can't create new data fields in the header area. It will grab the first available record in the table since there are no link from the layout specific tables. All your information is in the repetitive area.

Why do you want to print from a query if you have only 1 parameter? Would it not be better to print from the Goods Issue window where you will be able to do a proper print layout?

Regards

Danie

Former Member
0 Kudos

Hi Indika,

You have one filter parameter - the Document Number. This means that you'll always only have one document header.

Adele

Former Member
0 Kudos

Hi All,

I needed to create Invoice (smilar to Ar Invoice) using the goods issue tables. My client sometimes issues goods to his customer at zero value and as we know it is not possible on AR invoice.

If I were to print an Invoice out of the goods issue process then I would be needing to get the Ship to and Bill to informtion from the BP Master table as well.

As the the address information are not available in the Goods Issue screen , I wanted to use a query to extract them ( I gather the BP code in to the Ref2 field in the Goods Issue screen), wanting to keep all logic in one place.

Because of the B1 inablity to display header fields as I wanted I had to ditch this idea, and use the modified Goods Issue report, with a formatted search populating the address information on the goods issue screen now (which is also a better solution since the user can modify the addresses).

anyway I hope SAP will fix this issue some day, as query-reports will not be able to do complex ones.

Thanks for the assistance.

Indika.

AlexGrebennikov
Active Contributor
0 Kudos

Hi Indika!

Describe us plz how PLD should behave itself to draw variable values of DocNum and Ref2 columns in the layout header?? as for me i can't imagine it

Former Member
0 Kudos

This is how I get the report now ( all in the repetitive area)



DocNum      Ref2        ItemCode             
----------- ----------- -------------------- 
2           C30009      A00001
2           C30009      A00002

This is how I want it to be.


Page Header
Doc Num: 2
Ref2:    C30009

Lines: (repetitive area1)
ItemCode             
--------- 
A00001
A00002



AlexGrebennikov
Active Contributor
0 Kudos

DocNum and Ref2 in your case are the constants, so i suppose that columns are also the query-arguments.

if so, PLD will automaticly add all the query-arguments in the page header.

Former Member
0 Kudos

The only single query argument is the DocNum.

the query is: I do a Goods Issue and Type a customer code in the Ref2 field. now want to list the items for that customer with the customer code and doc number in header. The header is collected as a parameter when the report is run.


SELECT H.DocNum, H.Ref2, L.ItemCode FROM
OIGE H INNER JOIN IGE1 L ON
H.DocEntry = L.DocEntry
WHERE H.DocNo = '[%1]'

Message was edited by: Indika Dekumpitiya

AlexGrebennikov
Active Contributor
0 Kudos
>> SELECT H.DocNum, H.Ref2, L.ItemCode FROM
>> OIGE H INNER JOIN IGE1 L ON
>> H.DocEntry = L.DocEntry
>> WHERE H.DocNo = '[%1]'

in that case you can only see the DocNum in the page header. To implement Print Layout based on that query just click on Create Layout button of Query Manager, select layout template and layout name. that's it.

Former Member
0 Kudos

I have alredy done that: Create Report based on the query. It is this report that I need to modify so the DocNum and Ref2 appear in the Page Header.

try my query in B1. you will see what I mean.

(or try the followin with similar result)


select h.docnum, h.cardcode , l.itemcode from oinv h inner join inv1 l
on h.docentry = l.docentry 

WHERE H.DocNo = '[%1]'

AlexGrebennikov
Active Contributor
0 Kudos

And you still can't see DocNum in page header?!

plz, look at page 14-15 in https://websmp101.sap-ag.de/~sapidb/011000358700004496932004E.zip

HTH

Former Member
0 Kudos

Hi Indika,

I have tried your query and have successfully created a report as you require. I don't understand where the problem is. I followed this steps:

1. Create query (I used OINV and INV1) - I used the Query wizard as I'm getting stupid errors on my Query Generator:

SELECT T0.DocNum AS 'Document Number', T0.Comments AS 'Remarks', T1.ItemCode AS 'Item No.' FROM  [dbo].[INV1] T1 INNER JOIN [dbo].[OINV] T0 ON T1.DocEntry = T0.DocEntry WHERE T0.DocNum = [%0]

2. Create report from query

3. Open query print layout. Delete the 2 fields in the repetitive area (just leave Item Number)

4. Add 4 new fields in the header and set the properties from there.

It should work, if this is not what you require, maybe explain again

Regards,

Adele

Former Member
0 Kudos

This is exactly what I require:

where did you create your 4 new fields? I want them to be in the "Page header" area.

I re-tried this using your query and added a Data field in the "Page Header" but all it does is pick up the first available DocNum in the OINV table. (This is what I found using my query too.)

what am I doing wrong?.

Thanks,

Indika.