Multiple/optional parameters

Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.

Thanks.

Hello John,

Yes it can be done in a single report but you would have to create an extra parameter and do all the logic in the Stored procedure.

1.) Create an extra parameter in the Report Parameters called ParamYear as Datatype string.

2.) In the available values select Non-Queried and enter FisacalYear in Label and Value and also the CalenderYear.

3.) In the stored Procedure the logic would be like this:

If @.Parm = fisicalyear

Begin

End

Else

Begin

End

Hope this helps..

|||

I'm running all my queries against the cube but I guess the logic would be similar.

I should include another parameter and the first thing they select is Fiscal or Calender and then the proper hirarchies are offered to pick from?

|||It depends on the hirarchies. You can set the default value which one you want first and follow on. In the SP it gets the value for the one it selected. so hirarchies doesn't matter I believe.|||Can you hide a parameter list and then display one parameter or the other depending on the data from the first parameter?|||

Yes , it is possible. I personally did not do it but this link might be helpful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087731&SiteID=1

John, I have this saved in my Code-Sample database and this was answered in the same forum.

This is possible. I'll give an example using the AdventureWorks db. Suppose you want to have a report with all the contacts. The first filter on your report will filter on the Title column, the second will filter on LastName. When a user selected a Title in the dropdownlist, the dropdownlist will be filled with all the LastName's that have the same Title as the one selected.

Here is how to do this:

1.) create a dataset 'TITLES' with the following sql statement:
SELECT DISTINCT Title FROM Person.Contact ORDER BY Title

2.) create a second dataset 'LASTNAMES' with the following sql statement:
SELECT DISTINCT LastName FROM Person.Contact WHERE Title = @.Title

3.)create a third dataset 'CONTACTDETAILS' with the following sql statement:
SELECT * FROM Person.Contact WHERE Title = @.Title AND LastName = @.LastName

4.) Go to the Layout tab and open the \Report\Report Parameters menu

Normally you will see two parameters: Title and LastName. Make sure that Title is the first parameter.

Select the Title parameter and set the following settings:
Available values\From Query\DataSet = TITLES
Available values\From Query\Value Field = Title
Available values\From Query\Label Field = Title

Select the LastName parameter and set the following settings:
Available values\From Query\DataSet = LASTNAMES
Available values\From Query\Value Field = LastName
Available values\From Query\Label Field = LastName

Click OK to leave the Report Parameters menu.

5.)Drag a table on your report and add fields from the CONTACTDETAILS dataset to it.

If everything went well and you run your report, you will first need to select a value for the Title filter. After that, the LastName filter will be filled based on the value selected from the Title filter. When selecting a name in there, the report will be shown.

|||

I'm not quite doing the same thing.

I want a report that will drop down a list of fiscal year/months or calender year/months depending upon user input. I've thought about creating a report with 2 subreports, one a fiscal year report and one a calender year report. The subreports will be toggled from the main report. I can default to one and let the user toggle between reports. No parameters will be passed, the user will make appropriate selection in the subreport.