dynamic prompts in crystal reports 2008

[Former Member]

I have a crystal report 2008 built out of stored procedure(Procedure is using SQL server 2008 database).

there is a datetime parameter in stored procedure.when it is coming to crystal reports it is showing as datetime but when i change the parameter type to dynamic the paramter datetype is changing to string from datetime, i am not able to change that since it was disabled.

I am changing that to dynamic because i would like to use LOV for that.

Any suggestions on this?

thanks!

[abhilash_kumar · Accepted solution]

HI Reacha,

Why have Datetime values as LOV when you have the calendar picker?

Anyway, you would need to associate the LOV with a database field and since it is a stored procedure it gets difficult.

Create another command object that gets the datetime field alone and make sure you do not link it with any fields from the stored proc.

Then, right-click the parameter in the Field Explorer and associate the field from the command object with this LOV.

Hope this helps!

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

the user wants to have a drop list of values from database and
that is reason i was not using the calendar picker.

I did not understand what you were saying.

I created a report using stored procedure.stored procedure has 4 parameters and when i create a crystal report this parameters comes up in report

Now i want to change one of the datetime parameter from static to dynamic but when i change that the parameter is automatically changing its datatype from datetime to string.

[abhilash_kumar · Accepted solution]

Alright, here's what I'm trying to say:

1) Create the report with the stored proc

2) Create a command object by clicking on the Add Command button

3) Create a SQL command similar to this:

Select "date_field" from database.

4) Remove any links that CR automatically does:

5) Click OK on all warning messages until you get to the Report Canvas

6) Once you're there, right-click the parameter name in the Field Explorer and click on 'Edit Parameter':

7) Then Select 'Dynamic' as the 'List of Values' and in the Value option choose the field from the command object:

😎 Then, select the 'Click to create parameter' button:

9) And, you should get a prompt page similar to this when you refresh the report:

Let me know how this goes!

-Abhilash

[Former Member · Accepted solution]

thanks.. it worked..

I have one more question about passing mutilple values to the stored procedure from crystal reports.

[abhilash_kumar · Accepted solution]

Hi Reacha,

Does your stored proc accept multiple values?

And if it does, in what format?

-Abhilash

[Former Member · Accepted solution]

No, it won't accept mutliple values but i want to give it as a comma separated list so that i can manipulate that in procedure.

[abhilash_kumar · Accepted solution]

Well, first you need to make sure the stored proc accepts a comma separated string.

Once that is sorted out, the report in question would need to be added as a subreport. The Main report would just act like as a container holding the subreport and creating the comma separated string to be passed to the subreport and eventually to the stored proc.

So, you would create a DateTime prompt in the Main Report and using a formula concatenate the values. This formula would then need to be passed to the subreport.

-Abhilash

[Former Member · Accepted solution]

I have added the blank mainreport, created a parameter field and a formula field which uses the join condition "'"&Join({?years},"','")&"'", linked the main report and subreport using the parameter field for year.

The issue is when i try to run this report,it is not getting any data instead of that it is showing up the blank report.

Thanks,

[Former Member · Accepted solution]

small correction to the above reply.

my procedures takes XML as input parameter.

In the Mainreport i need to create a formula in xml format.

Can we use XML as an input in crystal?

[abhilash_kumar · Accepted solution]

Hi Reacha,

Yes, ToXMLData, ToXMLColumn and ToXMLRow are the XML functions that you can use.

So, you would need to build a string using one of these formulas.

-Abhilash

[Former Member · Accepted solution]

I have created the blank mainreport which has the parameter for year(Multivalue parameter)

and a formula for year as

toXMLData( toXMLRow ( toXMLColumn ('{?year}') + toXMLColumn ('{?year}') + toXMLColumn ('{?year}') ) )

Linked the mainreport and subreport using the formula and the subreport parameter(parameter from procedure) but when i run the report i am not able to see this parameter.

Am i missing something here?

thanks!

[abhilash_kumar · Accepted solution]

Hi Reacha,

Could you create a simple report with the stored proc as its datasource and pass a comma separated string of dates, similar to what the formula might output?

Does that work?

Also, on the report you created above, what happens when you place the formula that is sent as the parameter on the Subreport's Header? Does it generate the right XML string?

-Abhilash

[Former Member · Accepted solution]

when i create a simple report out of that procedure it returns blank report.

I am inputting my values into that parameter as 2010,2011,2012.

I dont know how to input values into that XML parameter.I think that is the reason for the report coming up as blank.

when i create the formula using the toxml functions and after linking  that to subreport using that formula.. when i run the report it is skipping that parameter(that parameter is not prompting for any values)

[Former Member · Accepted solution]

Thats for your help..

[Former Member]

Abhilash;

I'm using Crystal Report 2013, Support Pack 1 and I want to do the same - passing Dynamic Parameter to a Server 2008 SP that is used for the report. If I use LOV as static parameter, I can see the DropDown list, but when I use "command" to select the value from a table, I do NOT see the DropDown list. Not sure what I have done wrong.

Thanks

Randy

[former_member183750]

Call sales and get them to give you a link to the latest Service Pack - SP 5.

Sales contact info:

ph: 866-681-3435

http://www.sap.com/contactsap/directory/index.epx

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member]

Hi Abhilash,

In my report now i have one command object and one procedure, now i want to add an another command object from different database connnection but when i try to do that was getting an error as

Database Connection Error: Cannot obtain error message from the server

Is there any other way to do this?

Thanks

[abhilash_kumar]

Hi Reacha,

Instead of adding that command object to this report, could you create a new report using just that command?

Does that work?

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

The Main issue here is i cannot insert a subreport in an another subreport.

My Mainreport is just a blank report with year formula to convert the list of values to XML type.

My subreport is using the stored procedure and linking the mainreport and subreport using the year formula in mainreport to year parameter in subreport.

Now to get some other information i had to create subreport but i dont know whether it is possible to use a a subreport in a subreport.

Any ideas?

Thanks

[abhilash_kumar]

Hi Reacha,

No you cannot add a Subreport in a Subreport and I never asked you to do that!

What I wanted you to do is, create a completely separate report, add the command object (the same command object that you added on your original report due to which you get the error) and see if you get an error.

If you do, then we know it's something with this command object that is causing the issue.

- Abhilash

[Former Member]

I have tried to add a command object but the command object is from using different database connection and tthrowing an error.

[abhilash_kumar]

So, you mean it throws an error when you create a separate report too?

What is the error?

- Abhilash

[Former Member]

No, when i add that command object to existing subreport it throws an error as

Database Connection Error: Cannot obtain error message from the server

[abhilash_kumar]

Alright, so, you do not get an error when you create a new report using this command object, isn't it?

Are there any more lines to that error message?

- Abhilash

[Former Member]

No other error.. just ggetting the above error

[abhilash_kumar]

Hi Rupa,

Thank you for creating the ticket!

As discussed, the best thing to do would be to ask the Stored Proc developers to add the field in the procedure.

Otherwise, it will be a huge hit for Crystal Reports; either it'll keep running for hours or it will crash due to insufficient memory.

Hope this helps!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces