PS Query - Extensive use of Expression and Prompts

3 way Prompts

In this way user can able to run the query either by giving value or giving prompt value or some part of the value.

To do so we need to,

1. Create the Expression
2. Create a Prompt
3. Add criteria with Expression and Prompt
4. Change logical mapping
5. Group the criteria

Initial Query

Create the Expression

Create the Expression like below and in the Express Text give only <single quote> <space> <singlequote>

Create a Prompt

Create a Prompt for the Field where u need a Prompt while running the Query

Please note that here the Prompt Table been mentioned with “No Table Edit” as Edit Type

Add criteria with Expression and Prompt

While adding criteria first select the Expression and Condition Type should be equal to and then select the Prompt which you created earlier.

Add other Criteria like below

Make sure here the Condition Type should be “Like” and Expression 1 Type as Field (Field you are implementing Prompt)

Change logical mapping

Make the Logical operation between two Criteria’s as “OR”

Group the criteria

Group the “OR” clause both side Criteria’s, this will be mandatory when you are doing complex criteria’s

Final SQL will be like this

Running Query – Way 1 (Blank Run - Complete)

Running Query – Way 2 (Specific Run – Exact)

Running Query – Way 3 (Match Run – Contains)

Here you can use % character either left or right side to accomplice "begins with" and "ends with" kind of searches.

Running Connected Query BIP / XMLP through Peoplecode / AE Process

Hi All,

Most of us tend to find the Answer for this Question "How to I execute Connected Query XMLP report through Peoplecode or AE Peoplecode?"

Here it is, what you are seeking for,

import PSXP_RPTDEFNMANAGER:ReportDefn;
import PT_CONQRS:QUERYITEMPROMPT:*; 
import PT_CONQRS:CONQRSMGR:*; 

Global string &BU, &Invoice; 

Local array of PT_CONQRS:QUERYITEMPROMPT &prompts; 
Local File &xmlFile; &reportDefinition = "XX_CONNQRY"; 
&connQueryName = "BI_INV_PRNT_CQRY_V1";

 /* Start by creating the Connected Query Object */ 
&connQueryObject = create PT_CONQRS:CONQRSMGR("", &connQueryName); 
If Not (&connQueryObject.Open( False)) Then 
/* If it can't be opened, throw an error */ 
 Error ("Failed to open Connected Query."); 
End-If;

If Not (&connQueryObject.Validate()) Then 
/* Validate that all PSQueries within the Connected Query Exist */ 
 Error ("Unable to validate Connected Query."); End-If; 
 /* Can only be executed in Online pages */ 
<*If Not &connQueryObject.SetRunControlData("", False, False) Then 
 Warning "Unable to set the RunControl Data for Connected Query"; 
End-If;*> 

 &prompts = &connQueryObject.QueriesPromptsArray; 
/* This will return an Array of Records with the prompts for each Query within the Connected Query */ 
&promptRecord = &prompts [1].QueryPromptRecord; 
/* Set the key fields */ 
&promptRecord.BUSINESS_UNIT.Value = &BU; 
&promptRecord.INVOICE.Value = &Invoice; 

 /* Run the Connected Query to a String object */ 
&xmlString = &connQueryObject.RunToXMLFormattedString(&prompts); 

/* However, since it is "formatted" XML Publisher will insert Carriage Return and Line Feeds in the report. * We have to strip these out */ 
&xmlString = Substitute(&xmlString, Char(13), ""); /*13 = Carriage Return */
&xmlString = Substitute(&xmlString, Char(10), "");/*10 = Line Feed*/ 
&xmlString = Substitute(&xmlString, Char(9), ""); /*9 = Tab*/ 

 /* Now that the string is without formatting, write it out to an XML file. * Make sure to use a unique filename here, just in case it's ran from multiple locations */ 
&xmlFileName = &connQueryName | "_" | &BU | "_" | &Invoice | ".xml"; 
&xmlFile = GetFile(&xmlFileName, "W"); 
&xmlFile.WriteString(&xmlString); 
&xmlFilePath = &xmlFile.Name; 
/* Might be useful for Opening the XML File */ 
&xmlFile.Close(); 

 /* Now it's time for XML Publisher to do it's work. Create the Report Definition Object and Get() the report*/ 
&reportDefnObject = create PSXP_RPTDEFNMANAGER:ReportDefn(&reportDefinition); 
&reportDefnObject.Get(); 

 /* Set this if you want the output file with Dynamic Name (filetype is not required) */ 
&OutputFile = &BU | "_" | &Invoice; 
&reportDefnObject.ReportFileName = &OutputFile; 

 /* Since the Report Definition's Data Source is an XML file, we can pass in the file we created at runtime */ 
&reportDefnObject.SetRuntimeDataXMLFile("C:\temp\" | &xmlFileName);

/* Process the report, thus running XML Publisher against the XML file that was created from the Connected Query */
&reportDefnObject.ProcessReport("", "", %Date, "PDF");

/* Execute this if you are in AE to publish output in Report Manager */
&reportDefnObject.Publish("", "", "", &ProcessInstance);

/* Use the DisplayOutput() to open Output Directly */
/* &reportDefnObject.DisplayOutput(); */

/* Now to clean up the file on the App Server, don't want any junk lying around out there.
* If you need to look at the file for debugging purposes, obviously comment this out.
* The file, by default, is placed on the $PS_HOME/appserv//files directory */
&xmlFile = GetFile(&xmlFileName, "W");
&xmlFile.Delete();


Please mark a comment below, if you need any clarity..