Prefix parameter field

[Former Member]

I have been provided with a report that requires the parameter field to be populated with the word u2018Weeku2019 and followed by the week number i.e. Week 02

I have an automated system for exporting reports via email, and I can set the parameters to enter dates or part of dates, however I am at a loss of how to have the word u2018Week u2018 (with a space at the end, thus before the week number).

Is there anyway in which I can prefix a parameter field.....so for the parameter of u201840u2019 the Select Expert would actually be checking u2018Week 40u2019 against the database?

[Former Member · Accepted solution]

maybe pass the parameter field to a formula?

concatenate 'Week' and parameter, e.g.

"Week " + ?parameterfield

and then use the forumula field for your record selection.

Depending on whether your parameter field is a string or otherwise, use + or & as the concatenate operator.

Dom

[Former Member · Accepted solution]

This is very close to being right! but not working for some reason?

the Paramenter {?week} is looking for a string in the format of:- Week 40

So created a forumla called @FullWeek and tried:-

"Week " + {?WEEK}

"Week " & {?WEEK}

totext("Week " + {?WEEK})

totext("Week " & {?WEEK})

If I put @FullWeek in the footer, every option above that I have tried all appear to be correct, but the report is blank?

(If I change it back to selecting via the parameter and enter Week 40 it works?)

[Former Member · Accepted solution]

you mentioned in your earlier post that the parameter value would be, for example, 40.

What do you mean by:

the Paramenter {?week} is looking for a string in the format of:- Week 40

[Former Member · Accepted solution]

Drag @Fullweek and database field in report footer and check if you can find any difference.

Thanks,

Sastry

[Former Member · Accepted solution]

no worries.....you've got it!

[Former Member · Accepted solution]

yes,

The Paramenter {?week} is looking for a string in the format of:- Week 40

The parameter i am automatically entering is:- 40

However I think you may have been typing your reply when I was replying that you have resolved my issue - thanks!

P.S.

I have also already clicked resolved issue beside your name

[Former Member · Accepted solution]

i think Charliy Nash was quicker than me at typing!

[Former Member]

Hi Jonathan,

This can be done like..

In existing report Go in Database Expert Create New ConnectionDouble click on Add Command

Write SQL as follows :

Select 'Week '+Week_no As Week from Table

It will create a command object, Now create a Dynamic Parameter based on the above command object Week.

In Record Selection check the Database field agains this parameter like ..

= Totext(right(New_Parameter,2)

Hope this will help you.

Thanks,

Sastry

[Former Member]

Sure. The parameter doesn't have to be what you use for the selection. You can create a formula - in your case it would probably be either RIGHT({?WEEK},2) or VAL(RIGHT({?WEEK},2)) and use tha formula in you Select.

[Former Member]

I think this is the wrong way round - I am wanting to enter the parameter of:- 40

Then I would like to select against the database with Week 40, so I wanted to add 'Week ' to the beginning of my parameter

[Former Member]

as you say, you can select the parameter value of 40, pass that value to a formula that concatenates "Week " with 40, resulting in "Week 40".

This formula field with a value of "Week 40" can then be used as the basis for record selection.

Dom

[Former Member]

Right, I have got it to work..........Thank you all!!!

"Week " + {?WEEK}

That worked, however I had made so many changes and tries that it did not work until I had closed the reprot without saving changes and then retried this!

Thank you agin!