Expression to implement Next Line Functionality in Reporting Services
Hi,
I have a drop down "Year1" which displays 2012-2017,2013-2018 and so on..
I have second drop down "Year2" which value should display according to Year1 dropwdown selected value
eg: if year1 ddl selected value is 2012-2017 ,then year2 ddl should like 2012,2013,2014,2015,2016,2017
to implement i tried in following ways, none helped me
=Left(Parameters!FiscalYear.Label, Len(Parameters!FiscalYear.Label) - InStr(Parameters!FiscalYear.Label, "-"))
& vbcrlf & Left(Parameters!FiscalYear.Label, Len(Parameters!FiscalYear.Label) - InStr(Parameters!FiscalYear.Label, "-")) +1
=Code.AddLineBreakBetweenWords("Left(Parameters!FiscalYear.Label, Len(Parameters!FiscalYear.Label) - InStr(Parameters!FiscalYear.Label, "-")) Left(Parameters!FiscalYear.Label, Len(Parameters!FiscalYear.Label) - InStr(Parameters!FiscalYear.Label, "-"))+1")
=Replace(Left(Parameters!FiscalYear.Label, Len(Parameters!FiscalYear.Label) - InStr(Parameters!FiscalYear.Label, "-")) & Chr(4) & Left(Parameters!FiscalYear.Label, Len(Parameters!FiscalYear.Label) - InStr(Parameters!FiscalYear.Label, "-"))+1, vbLf,
Environment.NewLine)
=Left(Parameters!FiscalYear.Label, Len(Parameters!FiscalYear.Label) - InStr(Parameters!FiscalYear.Label, "-"))
"\n" Left(Parameters!FiscalYear.Label, Len(Parameters!FiscalYear.Label) - InStr(Parameters!FiscalYear.Label, "-")) +1
and when i tried like below
=Left(Parameters!FiscalYear.Label, Len(Parameters!FiscalYear.Label) - InStr(Parameters!FiscalYear.Label, "-")) & Left(Parameters!FiscalYear.Label, Len(Parameters!FiscalYear.Label) - InStr(Parameters!FiscalYear.Label, "-"))+1
The result is like 2012 2013 ... its not comi in next line
Anyone has any idea about it ?Thanks, Saraswathy Kumaravelu
February 8th, 2011 8:08am
The best option would be to use Stored Procedure or T-SQL commands to populate the second parameter. Based on the value passed from first parameter, return a list of values (recordset).
I think using VBScript you cannot achieve this functionality.Suresh
My Scribbling
Free Windows Admin Tool Kit Click here and download it now
February 8th, 2011 8:15am
how do i write that can u suggest with some examples?Thanks, Saraswathy Kumaravelu
February 8th, 2011 8:27am
Hi
Saraswathy,
Do you mean you need 2 parameters, the first parameter Year contains available values like 2012-2017,2013-2018, if select the option 2012-2017, the second parameter Year2 should
contains available values like 2012,2013,2014,2015,2016,2017, then users can select one of them for the report, correct?
If I have misunderstood, please point out.
Generally, we can use cascading parameters to achieve this. Please refer to the steps below:
1.
Add parameter Year1 with the following settings
Name:
Year1
Prompt:
Year1
Data type:
Integer
Available Values:
Specify values
Label
Value
2012-2017
2012
2013-2018
2013
2.
Add parameter Year2 with the following settings
Name:
Year2
Prompt:
Year2
Data type:
Integer
Available Values:
Specify values
Label
Value
=Parameters!FiscalYear.Value
=Parameters!FiscalYear.Value
=Parameters!FiscalYear.Value+1
=Parameters!FiscalYear.Value+1
=Parameters!FiscalYear.Value+2
=Parameters!FiscalYear.Value+2
=Parameters!FiscalYear.Value+3
=Parameters!FiscalYear.Value+3
=Parameters!FiscalYear.Value+4
=Parameters!FiscalYear.Value+4
=Parameters!FiscalYear.Value+5
=Parameters!FiscalYear.Value+5
After that, available values of Year2 will be determined by the selected value of Year1.
For more about How to: Add Cascading Parameters to a Report, please refer to:
http://msdn.microsoft.com/en-us/library/aa337169.aspx
Thanks,
Tony ChainTony Chain [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
February 14th, 2011 12:49am