converting xml parameter to lOV's

[Former Member]

Need to convert the xML parameter input in crystal report to drop down list of values, so that it will be more user friendly..

I am not sure how to convert that in a formula field?

Any help on this?

Thanks,

[abhilash_kumar · Accepted solution]

Hi Reacha,

1) Create a parameter with the LOV in the Main report

2) Create a formula that'll build a string (xml) similar to the one your stored proc accepts

3) Insert a subreport with the stored proc as its datasource

4) Pas this formula to the stored procs parameter using the 'Change Subreport Links'

Hope this helps!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

I am not sure about the code i need to write in the formula field to link the subreport with..

Do i need to use any of the XML functions to convert the lovs to XML format in crystal?

Thanks,

[abhilash_kumar · Accepted solution]

Well, using the XML functions won't help because your stored procedure accepts an XML string in a specific format with its own row, column and body tags.

You need to pass the exact string by replacing the first name, last name with the values. Something like this:

So, for eg: if the stored proc accepts a string similar to this:

'<employeeData><EmployeeID="123"/></employeeData>'

Create a formula that'll output the above string with the values from the parameter. Like this:

"'<employeeData><EmployeeID='"&i&"'/></employeeData>'"

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

My xml format input is this.

'<Years>

<Year>

<YearNumber>2003</YearNumber>

</Year>

<Year>

<YearNumber>2004</YearNumber>

</Year>

<Year>

<YearNumber>2005</YearNumber>

</Year>

<Year>

<YearNumber>2006</YearNumber>

</Year>

<Year>

<YearNumber>2007</YearNumber>

</Year>

<Year>

<YearNumber>2008</YearNumber>

</Year>

<Year>

<YearNumber>2009</YearNumber>

</Year>

<Year>

<YearNumber>2010</YearNumber>

</Year>

<Year>

<YearNumber>2011</YearNumber>

</Year>

<Year>

<YearNumber>2012</YearNumber>

</Year>

<Year>

<YearNumber>2013</YearNumber>

</Year>

</Years>'

created a formula in crystal as below

"'<Years><Year><YearNumber><{?Years}='"&2010&"'/></YearNumber></Year></Years>'"

but when i link the mainreport formula and subreport parameter the paramters is not showing up when i run the report.

[abhilash_kumar · Accepted solution]

Hi Reacha,

I just observed that your xml input string needs to start with quotes.

Try passing a single value first using this formula. See if you can link this to the stored proc parameter

'"'&"<Years><Year><YearNumber>"&{?Years}&"</YearNumber></Year></Years>"&'"'

Let me know if this works and I'll try writing a formula that'll work for multiple values.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

when i add the above formula, getting an error message as

This array must be subscripted. For Example: Array[i].

[Former Member · Accepted solution]

Hi Abhilsh,

Sorry for the above reply..

1.created a static parameter in the blank main report.

2.created a formula called year

'"'&"<Years><Year><YearNumber>"&{?Year}&"</YearNumber></Year></Years>"&'"'

3. Linked the mainreport and subreport using the formula in the main report and subreport year parameter.

when i try to run the report getting blank report.. any idea?

I am expecting something is missing near quotations.

'<Years><Year><YearNumber>2010</YearNumber></Year><Year><YearNumber>2011</YearNumber></Year></Years>'

Thanks,

[abhilash_kumar · Accepted solution]

Hi Reacha,

Could you place the formula on the Main Report's Header and see if the output of the formula matches the input of the stored procedure.

Compare every quotation mark with the stored procs input and see if the formula is giving the right output.

Finally, copy the output of the formula > create a new blank report > paste this as the input for the stored proc. Does this work?

- Abhilash

[Former Member · Accepted solution]

My output should be like this

'<Years><Year><YearNumber>2010</YearNumber></Year><Year><YearNumber>2011</YearNumber></Year></Years>'

I am getting double quotes instead of single quotes.

formula is returning  this

"<Years><Year><YearNumber>2010</YearNumber></Year><Year><YearNumber>2011</YearNumber></Year></Years>"

When i try to change the Quotations getting a SQL server error.

[abhilash_kumar · Accepted solution]

Hi Reacha,

You see the double quotes in the output, because the formula is built that way. If you need single quotes use this:

"'"&"<Years><Year><YearNumber>"&{?Year}&"</YearNumber></Year></Years>"&"'"

- Abhilash

[Former Member · Accepted solution]

got it..

Thanks abhilash for all your help on this..

[abhilash_kumar · Accepted solution]

You're welcome!

Let me know your progress on this.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

now , i am able to pass single value into that xml parameter.

when i try to change this to accept multiple values getting an error as below

This array must be subscripted. For Example: Array[i].

[abhilash_kumar · Accepted solution]

Hi Reacha,

You would need to build an XML string with mutiple values. Try this code:

numbervar j;

stringvar s;

for j := 1 to ubound({?Year}) do

(

    s := s + "<Year><YearNumber>"&{?Year}[j]&"</YearNumber></Year>"

);

'"'&"<Years>"&s&"</Years>"&'"'

Let me know if this works!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Thanks Abhilash,

It worked

[abhilash_kumar · Accepted solution]

Awesome!

Please mark the thread as Answered.

Until next time...take care.

- Abhilash

[Former Member · Accepted solution]

When i try running the same report now.. its throws an error as "An array is required here"

[abhilash_kumar · Accepted solution]

And what part of the formula is highlighted?

- Abhilash

[Former Member · Accepted solution]

year in ubound({?year}) is highlighted

[abhilash_kumar · Accepted solution]

Hi Reacha,

Are you sure it's this part : year in ubound({?year}) ?

This is what we have in the formula : for j := 1 to ubound({?Year}) do

Anyway, could you make sure 'Allow Multiple Values' is set to 'True' for the {?Year} parameter?

- Abhilash

[Former Member · Accepted solution]

it worked.. thanks

[Former Member · Accepted solution]

Hi Abhilash,

Now for display purpose, In my subreport i need to convert the xml type data to just list separated by commas(2010,2011)

Any help is appreciated.

Thanks

[abhilash_kumar · Accepted solution]

Hi Reacha,

Create a formula like this in the Main Report:

stringvar years;

years := join({?year},",");

Now, go to 'Change Subreport links' and move this formula to the 'Fields to link to' area.

Uncheck the option 'Select data in Subreport based on'.

Edit the Subreport, and you'll observe that a parameter is automatically created in the Subreport. Just drag this parameter on the Report Header.

- Abhilash

[Former Member · Accepted solution]

It worked.. Thanks