Dynamic Parameter in Crystal Report

[Former Member]

Hi experts,

I want to ask if it is possible to use Dynamic Parameter in Crystal Report, in order to use a parameter as criteria and then use its results as a filter to a second parameter. To be more specific, I have created a DatePeriod parameter and a Data parameter. I want to set DatePeriod and then Data parameter bring me a list of all InternalSN from OINS ,included in the date range, so I can choose on demand (multiple values).

Any advice would be appreciated.

Thank you in advance

[leon_laikan]

Hi Vassilis,

You can use Dynamic Parameters in Crystal Reports.

It's explained beautifully in the free booklet "How to Work with Crystal Reports in SAP Business One"

in the section on Selection Criteria Token. Just do a search and download the PDF.

You have to create your parameter fields in Field Explorer (Parameter Fields):

@DatePeriod,

@DataParam

Then you must edit the name @DataParam so that it reads as follows:

@Select internalSN from OINS JOIN .... WHERE....etc  ...

Write your SQL as you would normally would.

But note:

1.The SQL must not be too long, there's limited space.

2.There must be no space between @ and Select.

Then, you run your report in SAP B1 (Preview External Crystal Reports File)

And.. presto - You get the dropdown list from which you can select internalSN.

Hope it works

Regards

Leon

[Former Member]

Hi Leon,

Thank you for your response. The point is that I want to use the DatePeriod parameter inside the where clause. For example:  @SELECT internalSN FROM OINS  WHERE U_Date = @DatePeriod. If I use specific value instead of the DatePeriod parameter I get results, but when I use @DatePeriod I don’t. Can you please give some advice on this?

Thank you in advance

[leon_laikan]

Hi Vassilis,

On pg 27 of "How to Work with Crystal Reports in SAP Business One", you'll find the answer

---- pg 27 ----

A parameter name (that is, token) may include another parameter name. This means that a second parameter may depend on a first parameter. For example, a state parameter depends on a country parameter. If you do not choose a country, the state dropdown list has no data. If you choose

United States as the country, the states of the United States appear in a State dropdown list.

The syntax must include the first parameter name including the at sign (

@), as in the examples below. The first statement lists all countries, while the second statement calls "Country". For the second statement to work, you must have a parameter called Country.

Examples

Country@SELECT distinct Country FROM OCST

State@SELECT code, name FROM OCST WHERE (Country = '@Country')

------------------------------------------------------------

TRY THIS:

Put your @DatePeriod between ' ' as in the above example.

It works easily with non-date fields, but with dates, it may be a little tricky.

Do some tial ans error, and you should get what you want.

Thanks

Leon

[Former Member]

hi Leon,

i tried the example

Country@SELECT distinct Country FROM OCST.

The parameter will only be shown in "Preview in SAP Business One".

If i save the report by "Addin --> SAP Business One --> Save" and preview my document then the parameter will be shown as a textbox instead of a dropdown list.

Do you know what's going wrong?

best regards

Markus

[leon_laikan]

Hi Markus,

If it works with "Preview in SAP Business One", why would you want to use "Addin"?

I always use "Preview", and I developed sophisticated reports beyond my wildest dreams.

It works so well and is so powerful that I never even think of using an alternative.

Unfortunately, I cannot help you with "addin"

Best Regards

Leon

[Former Member]

hi Leon,

i mean the Add-In menu in crystal reports where i can save the report back to SAP Business One database..

In crystal reports:

- i add a new Parameter "Customer@SELECT CardCode, CardName FROM OCRD WHERE CardType = 'C' ORDER BY CardCode"

- save the report.(Crystal Report: Add-Ins --> SAP Business One --> Save)

- test with preview in crystal works (Crystal Report: Add-Ins --> SAP Business One --> Preview in SAP Business One)

- i close the crystal report

- back in SAP Business One. Press preview on document (invoice)

- crystal parameter mask opens, but the parameter Customer ist empty and there is a textbox instead of a dropdown list.

best regards

Markus

[leon_laikan]

Hi Markus,

I always used "Preview External Crystal Reports File" method to view my Crystal Reports.

I did a test using your method to see if I can reproduce  any of your problems.

I did not find any problem: Both methods worked equally well and both displayed the dropdown list (no textbox instead).

So, I do not understand your problem.

TRY THIS:

Modify your procedures slightly to do EXACTLY what I did:

In Crystal Reports

-------------------------

Add-In -> Save As -> Report

Don't do the preview part

In SAP b1

--------------

Tools -> Preview External Crystal Reports File -> Then select your File.

Your report should pop up.

Do you see the drilldown list?

If yes, your Crystal Report is working correctly.

If you now use the add-in method, there is no reason why you should not see the drilldown list.

If you don't see the drilldown list here, you won't see it using the add-on method, and you must correct your CR first.

Now, use the add-on method as follows:

In SAP B1:

--------------

Modules -> Administration ->Setup -> General ->Setup -> Report and Layout Manager

Search ... Find ---(Type your CR name)    .... Update

Then go to your module (Invoice) and click the CR name.

It should work.

Regards

Leon

[Former Member]

hi Leon,

in crystal i save my report (its a layout not a report. Its a copy of the invoice standard layout) as a rpt-File.

In SAP i opened this rpt-File by the menu Preview External Crystal Report Files.

A SAP form comes up with my paramter and its correctly shown as a drop down list.

In Crystal i save the report again.

Addin --> SAP Business One --> Save

In SAP:

Reoprt and Layout Manager, click on my Report and click on button refresh.

Sales Ar --> AR Invoice --> last recordset --> Preview

Now the crystal form opens with a crystal parameter form (not the same as before. its not a SAP form). My Parameter is shown as a an empty textbox and not as a dropdown list.

Is it possible that dynamic parameters are only available in reports and not in layouts?

best regards

Markus

[leon_laikan]

Hi Markus,

I see you are struggling a lot with your problem.

Unfortunately, I cannot help you further as I am a newbie myself, and have never worked with layouts before.

Try posting your thread in the Crystal Reports forum. You may have more success.

Best Regards

Leon

[Former Member]

Hi

Did You find the solution to this  ?

Itamar

[Former Member]

Hi,

Did you find any solution regarding the @DatePeriod?

I'm trying to create a report that user will select a date period and another parameter will show the open invoices on the selected date. So supposedly I have three dynamic parameters as shown below:

BPCode@SELECT distinct CardCode, CardName FROM OINV

Date@SELECT DocDate from OINV

Invoice@SELECT DocNum FROM OINV WHERE (CardCode='@BPCode' and DocStatus='O')

User will select the Customer, then the date and third parameter will show the open invoices of that Customer on the selected date. I have no problem with the BPCode and Invoice. Only that it shows all open invoices of the Customer. The date doesn't work at all.

Any help?

[kothandaraman_nagarajan]

Hi,

Not possibel to display dynamic parameter in crystal report. It is system limitation in SAP.