Web Elements - WESelect LOV built from Database Column

[Former Member]

<p>Hi,</p><p>We are running XIR2 (Unix). I am trying to create a sample report that uses WESelect and the select list is populated from a column in the database.</p><p>I&#39;ve based my report on the sample report WebElementsdcp.rpt. I am able to get the list of customers in the select list, but it also loads the Report header and each detail line. The information from the crystal report is below. I was unable to copy the screen prints and didn&#39;t find a place to attach the word doc in the submit form so not sure if the formula&#39;s listed below are helpful</p><p>----

---

</p><p>Copy from word doc</p><p>Problem: The List of Values for the Select Value retrieves the database column contents correctly, but it also appends the contents of the report. I&#39;ve tried to model my report using the sample report WebElementsDCP.rpt.</p><p><strong>Problem Description</strong></p><p>My Prompt / Select List should contain a list of customers. The result when running is Complete list of Customers and All Order Lines from the report</p><p><strong>Crystal Report </strong></p><p>I&#39;ve tried different scenarios, but all return the same result as above. I need to get the report detail removed from the prompt.</p><p><strong><u>Main Report:</u></strong></p><p><u>Formula for SelectCustomer</u></p><p>shared stringvar P1L1; </p><p>WESelect("Query1_Prompt2",P1L1,P1L1,{?Query1_Prompt2},"")</p><p>&nbsp;</p><p><strong>S</strong><strong>ub Report: Get Prompt Values</strong></p><p><u>Formula for GetPromptValues</u></p><p>stringvar field1:= {Query1.Customer Party Name} ;</p><p>shared stringvar P1L1; </p><p>if instr(P1L1, field1 + "|") = 0 then P1L1 := P1L1 + field1 + "|";</p><p>&#160;</p><p><u>Formula for GetPromptValuesFinal</u></p><p>shared stringvar P1L1:= P1L1[1 to length(P1L1)-1];</p><p>&#160;</p>

[JWiseman · Accepted solution]

<p>hello, thanks for the well laid out information!</p><p>from what it looks like, the subreport is running off of a query or stored proc. and i&#39;m guessing that it&#39;s a stored proc that brings back formatting data as well as columnar information...just a guess though. as the subreport uses a simple string running total, it will add to the shared variable only what is coming from {Query1.Customer Party Name} ensuring that there are no duplicates in the list.</p><p>steps to correct:</p><p>a) open the subreport up that is used to create the list</p><p>b) remove the query as the datasource location and add only the table that contains your customer party name field...i.e. ditch the query for a direct table connection.</p><p>c) use the {table name.customer party name} field in the select customer formula name</p><p>d) ensure that distinct records are brought back in the Database menu and ensure that the Record Sort Order matches ascending for the database field.</p>

[Former Member · Accepted solution]

Hi,

Thank you for the quick reply. We are trying to get through the technical hurdles this week in prep for a rapid prototype development effort next week.

Both the Main Report and the SubReport have a single Query based on a Universe.   The Main Report returns Order Lines and the SubQuery which is the source for the LOV is only a list of Customers. But they both use the same Universe.

I did a), c) and D) of your recommendation but have not replaced the universe query with the direct table name in the sub report. I will still try this.

I'm still getting the same results, where the entire content of the report (Page Header, Details and Footer Sections) are appended to the end of the select list of values. The List of values has all Customer names on individual lines, but the last line of the Select list is a long stream containing the Report Header, each Order Line and the Report/Page Footer.  The SelectCustomer formula (in the main report) does not make reference to the Order fields from the Main Report

The Page Header, Detail and Footer sections also print on pages 2 through 9999,  like you would expect.

Thanks, Dawn

[JWiseman · Accepted solution]

hi Dawn,

at this point i'm really not sure what's going on, but i'm hoping stronger coffee will help me today. when you say "it also loads the Report header and each detail line" are you talking about the

a) weselect control itself loading extra information? i.e. the control is placing extra data in the select values?  if this is the case, then you must have a universe that is appending the extraneous information...in this case you have to switch the subreport to the table right away.

 or

b) the subreport is displaying extra info? i.e. you look in the report header of the main report and your subreport is showing all the sections? if that's the case you have to suppress all of the sections by opening the subreport, right clicking on each section, going to the section expert and choose suppress. the subreport sections in these cases should not be displayed as the subreport is used only to run a query and gather up data, then pass it to the main report. ensure that in the main report you don't suppress the subreport itself or it will not run.

[Former Member · Accepted solution]

Hi,

a) was what was happening.

But, I don't know what happened, now it works. I switched directions a little and started to work with a submit button. Created a couple new formulas, and the select values now only contains the customer list, and the report detail follows immediately as expected.

Now working through issues on the submit button. Was able to resolve one of the problems on another post you had responding to (CUID vs NAME) when using path.   The link seems to work now,  but instead of rendering the report results in Infoview, it physically opens the report in the Crystal Desktop Client. Any suggestions?

Thanks for you help (You beat on-line supports response time hands down!)

Dawn

[JWiseman · Accepted solution]

<p>hey Dawn,</p><p>wow....this is 2 really bizarre behaviours in a row.</p><p>can you copy down the syntax of the url that opens up the report in the designer? </p><p>and is this happening for every url using opendocument or just one?</p><p>thanks</p><p>jamie</p>

[Former Member · Accepted solution]

Hi Jamie,

There is definitely some weird stuff going on with the Universe, so I'm tackling from a different angle and then once I have it working I'll apply the same formula's using the Universe as the datasource.

The report is now based on the tables. Everything works okay except for the passing of parameters.  In the submit button, I changed the mode to display the URL generated. When the dialog box appears after pressing the submit button, the URL is correct but the parameters aren't shown.

After pressing okay, the report parameter screen is displayed requiring the prompt value to be specified again (the value selected in the prompt is not defaulted in)

Here's the layout of the report

In the Main Report

Two Formula Fields. Both these fields have been placed in a header Section:
SelectCustomer
whileprintingrecords;
shared stringvar customerlist;
global stringvar allelements:=
WESelect ("CustomerName", customerlist, customerlist,{DIM_CST.CUSTOMER_NAME}, "") ;

SelectSubmitButton
whileprintingrecords;Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
local stringvar rptpath:=WETargetPath("rpt","CUID","okXvFP0AAC9LBrgARgGDdRxfQPc","");
global stringvar allelements:=
allelements +
WESubmitButton ("Submit",rptpath,"");
allelements + WEBuilder (allelements, 2)

One Parameter
CustomerName
Value is mapped to the Database table Customer Name (DIM_CST.CUSTOMER_NAME)

One Record Selection Criterea
DIM_CST.CUSTOMER_NAME={?CustomerName}

In the Sub report

One Formula Field which is placed in the detail section and is hidden<br />GetPromptValues
whileprintingrecords;
shared stringvar customerlist;
if not onlastrecord then customerlist := customerlist + {DIM_CST.CUSTOMER_NAME} + "|";
if onlastrecord then customerlist := customerlist + {DIM_CST.CUSTOMER_NAME};

 Thanks in Advance,

Dawn

[JWiseman · Accepted solution]

<p>hey Dawn,</p><p>sounds like you may be having an evaluation time issue...i.e. you are using a variable, allelements, in 2 different formulae. in the end, allelements ends up only being the content from the 2nd formula. that is why the prompt value & name do not show up...they are in the 1st formula and an evaluation time issue negates the first value in the string running total.</p><p>put the submit button / webuilder formula in a new lower header section than the select formula and underlay the 2 sections.</p><p>jw</p>

[Former Member · Accepted solution]

Hi Jamie,

That fixed the problem. There are a couple of odd things with the parameters but for the most part it is working.

Would you be able to recommend someone who has extensive experience in Crystal and Web Elements (ideally someone who has used both Crystal and Web elements with a Universe data source) who would be interested in a short term consulting assignment?

Thanks!

[JWiseman · Accepted solution]

<p>hello...glad that you&#39;ve things up and running.</p><p>as far as consultants i know that we have internal consultants at business objects who have implimented webelements solutions for clients. you can contact global services at <a href="http://www.businessobjects.com/company/contact_us/default.asp">http://www.businessobjects.com/company/contact_us/default.asp</a></p><p>i also know through this forum section that there are several consultancy companies using the function library now...hopefully they&#39;ll read this post and reply to it :)</p><p>jw</p>