SQ02 InfoSet Get Count of Total Number of Records that will be processed

[Former Member]

I am developing a query (SQ01) and am currently working on building an InfoSet (SQ02).

The Infoset was set up using a 'Direct read of table'. Next, I'm adding some various fields and then going to Extras and trying to define some code to get the total number of records that my query will be processing. I'm not sure if SAP pulls a filtered result set into a temporary table (by default - if so how could I reference it?) that I can reference or is just pulling in a row at a time in the record processing code, but my question is in regards to getting a record count of how many records are returned in my result set PRIOR TO going through all of the records.

Overall, I'd like to be able to have a field that says Record X of Y. I can get the X part for each line, but cannot get 'Y' until the very end. Any help or ideas would be much appreciated. I've looked around a bunch, but haven't found anything like what I'm requesting.

Query Output would look something like:

Record X1 of Y | Title1 | Amount1

Record X2 of Y | Title2 | Amount2

[Former Member]

Hi ,

One idea you can try is to write explicit select statement for the direct read table in event "Start of selection" into an internal table and then count the internal table for number of records. Then use this count in your extras field.

Regards,

Subin John

Edited by: subin john on Mar 28, 2011 4:34 PM

[Former Member]

Hi Subin,

I have tossed around this idea in my head, but am trying to figure out how to get the values and selection options from the query screen to incorporate into my Select statement within my infoset. The problem I'm running into is that my user enters a group of account numbers and an ending date that has to be pulled from the SQ01 query screen to the SQ02 infoset code. I've looked around for examples on pulling the data from the query screen, but have been unsuccessful thus far. Say for instance I have 15 specific accounts that the user is entering in and they want any records that have been submitted prior to the end of the current month and the start of the business year.

On my query screen they would enter in something like

Business Year: 2011

Reporting End Date: <= 31.03.2011 (Which equates to all records between 01.01.2011 AND 31.03.2011)

Account #s: 0000, 0001, 0003, 0005, ..., 9999 (These are a variable amount of accounts entered and could include options such as not equal to or even between ranges etc)

In my START-OF-SELECTION code I would need a select like:

NOTE: This is just a pseudo code format, not checked for syntax here

SELECT count(*)

FROM TABLE

WHERE BusinessYear = '2011' AND

RecordDate Between 01.01.2011 AND 31.03.2011 AND

Accounts IN (0000, 0001, 0003, 0005, ..., 9999).

So In this select I need to reference the values in the SQ01. How would I reference the account #'s and whether or not the user has entered an account number and said Not Equal on it etc. This select statement would have to be built on the fly, since it's not guaranteed to be the same for each run.

Thanks,

Mark

[Former Member]

Hi,

Check this thread,

It will helps to your problem

Regards,

Sekhar

[Former Member]

I finally figured this out a few weeks back and forgot to come back in and put in the update. To be able to do what I was trying to accomplish I had to do the following steps:

In SQ02, I changed my Additional Fields I had defined to Selection Criteria. To do this I clicked the Extras button then selected the Selections tab and created a 'Selection Criterion' for each field I wanted to be able to reference and retrieve the users input options/criteria.

Next I went to the Extras tab and looked at Coding for Addition in the <Code> block. To get the overall count, I changed the Code Section from Record Processing to START-OF-SELECTION. Inside this code block I then wrote my Select statement using the Selection Criterion. My Select Statement Turned out something like this:

SELECT count(*) INTO My_Variable FROM Table1

WHERE table1-field1 IN Param1 AND table1-field2 IN Param2.

**My_Variable is a variable or extra field I defined to use in my code later.

**Param1 and Param2 are the names of the Selection Criterion I created

The key in this select is to use the IN keyword for each of the parameters your user passes in from the query screen. By using the keyword IN you also get any limiting criteria the user may enter on the Query screen (SQ01). For instance if the user only wanted to see items in Parameter1 that were <= and they chose the <= Selection Option on the query screen, it would be automatically evaluated in the Where clause within the IN statement. Hopefully this will help out others looking to do something similar.