Joining text string and sub string in a prompt

[fkli222]

I am having a little trouble joining a text string to a sub string that I created in a prompt.

What I did was created a prompt with a substring to take part of a EList item [substring([Database].[EList].[Project Name],7,2) in (?ProjectYear?)].

I then went to the prompt query and added in the substrings I wanted to display in my detail filters. The substrings that it pulls represents the year but is only 2 characters (Ex. 2011 is 11, 2012 is 12).

My question is... how do I make it show in the value prompt list the whole year (Ex. 2011, 2012) instead of just 2 digits?

I tried doing "IF( substring [Database].[Elist].[Project Name],7,2)='11') THEN ('2011') ELSE (0)" but got an error.

Is there a better way to insert a text string before the substring?

Any help would be greatly appreciated. Thank you in advance!

[adam_mc]

I'm assuming you are using a Value Prompt.

In that case, (again, assuming) you have both 2-digit and 4-digit Year data items in your Prompt Query (if not create both items in the Prompt Query).
Then, select the Prompt and change the Use Value to be 2-digit year and the Display Value to be the 4-digit year. 

Hope this helps,
Adam.

[fkli222]

Yes it is a value prompt. I don't have both 2 digits and 4 digits year values in my prompt, only the 2. What I am trying to do is create the 4 digit but having trouble doing so. I have to do some kind of append "20" to the "11" or do a IF statement to get the 4 digit value but keep getting an error when trying to validate.

[adam_mc]

Add a calculation to create a 4-digit year to the Prompt Query.

Assuming you have no 1900's dates, this can be as simple as something like: '20' + substring ([Database].[Elist].[Project Name],7,2)
This will then give you 2-digit year and 4-digit year in the Prompt Query.

However, be careful, as these will give you alpha-numeric results, which you may need to turn into numeric results for use in any conditions (but, I don't know how you are using these).

When you run your Prompt Query to tabular data, you should see something like below:

2-digit year            4-digit year
--------------             --------------
11                          2011
12                          2012
.....


Then, go to the properties of the actual Value Prompt itself (click on the Prompt Value on the Page/Prompt Page) and change the Use and Display Values accordingly.
I am assuming, again, that the Value Prompt is already "pointing" to the associated Prompt Query.

Hope this helps.
Adam.

[fkli222]

Creating the calculation itself I still get an error (CCL Exception and query specification is incorrect).
'20' + substring([Database].[EList].[Project Name],7,2)

If I use try this: '20' + substring([Database].[EList].[Project Name],7,2) = '11'
it validates but when running the report it doesn't show up in my value prompt list.

I am creating these queries in the query prompt under detail filters.

Currently what works is:
substring([Database].[EList].[Project Name],7,2) = ('11')
OR
substring([Database].[EList].[Project Name],7,2) = ('12')
OR
substring([Database].[EList].[Project Name],7,2) = ('13')
OR
substring([Database].[EList].[Project Name],7,2) = ('14')
OR
substring([Database].[EList].[Project Name],7,2) = ('15')

This displays 11-15 values in my value prompt list. Theoretically, I could just add '20' + in front of each line but this makes that value blank when running the report.

Thank you again for your help.

[adam_mc]

Maybe, I'm not explaining myself well....

The purpose of the Prompt Query is to return the set of values to be used as possible filters in your query that will give you your results.
There should be no filters in the Prompt Query other than to limit the set of results returned (i.e. omit future years etc...).

What are the possible values from substring([Database].[EList].[Project Name],7,2)?
I am assuming that these are '11' , '12' , '13' etc...

If so, in the Prompt Query, simply have two calculations:

[2-Digit Year] = substring([Database].[EList].[Project Name],7,2)
[4-Digit Year] = '20' + substring([Database].[EList].[Project Name],7,2)

Then, as I've said before, go to the properties of the actual Value Prompt itself (click on the Prompt Value on the Page/Prompt Page) and change the Use and Display Values accordingly.
I am assuming, again, that the Value Prompt is already "pointing" to the associated Prompt Query.
The Use Value will then be the parameter you've defined when creating the Prompt.

Then, you will need to create a similar calculation in your result query for [Result Query.2-Digit Year] , and then have the filter [Result Query.2-Digit Year] = ?ProjectYear?

This should get you what you need, unless I've totally misunderstood what you are trying to do.
Hope this helps.
Adam.

[fkli222]

Great this works perfect. The reason I had a filter because I only wanted to show 2011-2015. There are some random values in the data that is inconsistent which I did not want to pull through. I used your method and adding a filter to only show 11-15 and it works!

Thank you again for your help, your explanation was very helpful.