Pre-defined lists of values as report parameter input

Has anyone come up with a solution for the following scenario yet?
We have many reports which have input parameters that are lists of values.
As an example the user might want to report on 30 of 100 available product
codes, or 12 of 50,000 account numbers.
What might be an idea would be to store the lists of values that userâ's want
to report on, in a general purpose Lists table. One column would be a List
Name and the other a delimited list of values. This List table could then be
used as the input to a Drop Down box for the relevant report parameter.
Has anybody tried something similar, or does anyone have any other
suggestions?
(Weâ're aware that multi-select list boxes will be available in SSRS 2005,
but that would still be a bit tiresome for a user that needs to select a few
values from a long list).
Regards,
John MarshHi John,
I have done similar thing like that.
i have a lookup parameter report which is a webpage with listbox and the
value is populated by calling a param report. The param report contain 2
fileds and it like name- value pair. The report is called programttically and
the result is returned in xml format so that it can be bind to a dataset and
to tht listbox or dropdown box.
So this act as a lists of values as report parameter input..
Regards
Bava
"John Marsh" wrote:
> Has anyone come up with a solution for the following scenario yet?
> We have many reports which have input parameters that are lists of values.
> As an example the user might want to report on 30 of 100 available product
> codes, or 12 of 50,000 account numbers.
> What might be an idea would be to store the lists of values that userâ's want
> to report on, in a general purpose Lists table. One column would be a List
> Name and the other a delimited list of values. This List table could then be
> used as the input to a Drop Down box for the relevant report parameter.
> Has anybody tried something similar, or does anyone have any other
> suggestions?
> (Weâ're aware that multi-select list boxes will be available in SSRS 2005,
> but that would still be a bit tiresome for a user that needs to select a few
> values from a long list).
> Regards,
> John Marsh
>

Posting Multi-Value Parameter

Hi,

I tried Posting the values for a multi value parameter from my application to the reporting services, But the query string is not being hidden. Why is that ? since the the data is sent through the browser address bar, I am not able to send values more than the allowed lenght. Is there any work around ?

Thanks In Advance

Regards

Raja Annamalai S

Hi Raja-

Yes, you will be limited to the URL length restriction. If possible I might suggest using the Web Service to render reports rather than crafting the URL. This will not be limited by URL length.

Otherwise you can perform a POST instead of a GET on the http call. The post will send the parameter values in the body rather than appended to the URL. However, all parameters need to be in the body if you perform a POST.

Thanks, Jon

PostBack while selecting a parameter

Hi,

I'm working on a report having 2 date parameters(which uses calendar control) and a dropdownlist. But on selecting each of these parameters, the page refreshes. For eg On selecting a date from the calendar control results in a postback. The same is the case with the dropdownlist. Could you please help to resolve this issue? We need the postback to happen only on clicking the 'View Report' button.

Also, is there any way to customize the 'View Report' button. It always appears in the right hand side. Can we set the position of this button so that it appears just below the paging button?

Thanks in advance,

Sonu.

1. No, it's not possible to avoid postbacks when you enter parameters one by one.

2. There is no way to customize the position of the button but you can change the style of the button in your report manager by using the ReportingServices.css file in the following folder (probably):

C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportManager\Styles

Please refer to more details in the following link:

http://msdn2.microsoft.com/en-us/library/ms345247.aspx

Shyam

sql

Postal Codes 10001-10005 as a parameter

Dear all
I like to implement a parameter in SSRS SP2, that accepts values like
10001-10005 as postal code. This should be translated to 10001, 10002,10003,
10004, 10005.
I tried to realize this with the following code:
Function SplittingCodes(ByVal s As String) As String
Dim ar As String()
Dim subar As String()
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder
ar = s.Split(","c)
For i As Integer = 0 To ar.Length - 1
ar(i) = ar(i).Trim()
If ar(i).Contains("-") Then
subar = ar(i).Split("-"c)
For j As Integer = CInt(subar(0)) To CInt(subar(1))
sb.Append(" '")
sb.Append(j)
sb.Append("'")
If j <> CInt(subar(1)) Then sb.Append(",")
Next
Else
sb.Append("'")
sb.Append(ar(i))
sb.Append("'")
End If
If i <> ar.Length - 1 Then sb.Append(",")
Next
Return sb.ToString()
End Function
I created an sql query that uses the parameter like this:
SELECT SUM (a) as test FROM table WHERE co IN (@.pcode)
And in the dataset tab on Parameters I used this code for the parameter
@.pcode:
=Code.SplittingCodes(Parameters!pc.Value)
Unfortunately the reports gives an empty dataset back.
Any help would be apreciated!
Thanks,
MarcHello Rombooth,
The root cause of this issue is that the sql statement you use in the
report.
My suggestion is that you could create a function in the sql server side to
split the string. And you could use this function in the sql statement.
You could refer this article to create the split function.
http://www.devx.com/tips/Tip/20009
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei! This solved my problem.
Best Regards,
Marc|||Hello Marc,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Possible to use Calendar control with date parameter?

SQL 2005 on XP Pro.
I want to call up a Calendar Control when a user enters a report parameter which is a date. Is this possible? I am using the out-of-the-box SQL Srvr Reporting Services, not a custom implementation.

Thanks in advanceThis is supported. A calendar control will be displayed for date parameters that do not have a valid values list.|||

Calender control comes automatically once you define a report parameter as datetime.

Amarnath

Possible to specifying a list as a parameter to a stored procedure ?

I have been converting a VB 6 applications database queries into SQL Server 2000 stored procedures and have come up against a problem where lists are used in search conditions...For example a list of accounts are selected based on their account currency ID being equal to 1, 5, or 7. In the VB 6 query the string looks like...

SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7))

The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure?

Any help much appreciated!The answer is, "maybe".

It depends on your needs for performance. Please take a look at this discussion for more details.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15403

Essentially, if you pass the list as a comma delimited string, then you will either need to parse it inside the query or use it in a dynamic SQL query within the SP. Your other choice is to take all 20 objects as single parameters to your SP. Your IN statement would then be a large set of OR statements for each of the 20 items.

I hope this helps,

CC|||I have to write a lot of stored procedures for reports. I always declare my parameters like

level1 varchar(255)

I then look at the incoming value. I use charindex to find ';' or ',' If I find either I know I have to use "in" in the where clause and format the values correctly.

IF CHARINDEX(';',@.LEVEL1)>0
BEGIN
SET @.LEVEL1=REPLACE('('+''''+REPLACE(@.LEVEL1,';',''''+ ','+'''')+''''+')',' ','')
END

If it is prompt is equal to '%' for all I make my where clause a like, if it is a single value I use equal. The trick to making this so flexible is to use dynamic sql. If you don't know what the parameter will be before hand it seems to be the best way.

' AND ISNULL(T1.DIVISION,'+''''+'NONE'+''''+')' +
case when CHARINDEX(',',@.LEVEL1)>0 then 'in '+@.LEVEL1
else
CASE @.LEVEL1 when '%' THEN ' LIKE '+''''+@.LEVEL1+''''+'+'+''''+'%'+''''
ELSE '='+''''+@.LEVEL1+'''' END
END

Sorry for the formating. It looks better in the actual file|||Oh and I just realized something. If the incoming value is '%' then do not add a condition for it in the dynamic where clause. It makes zero sense to add anything to a where clause if you don't need to.

Possible to send parameter to Excel (via Web) to pas along through to SQL Server?

Is there anyway to pass a parameter to an Excel spreadsheet (loaded in a browser) to use as a parameter in SQL statement?

I know that the Microsoft Technologies can write Excel files and then load them, but I'm looking to take an existing workbook, parameterize the SQL statement and pass parameters via the Querystring/Form Post to it.

Why you may ask? For existing PivotTables/Charts that can be filtered BEFORE they are run.

Any/all thoughts appreciated.

BobYeah, there's a lot you can do. Check out this KB article for some ideas and code:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934|||What I'm looking to do specifically is to have a "template" of a workbook that I will pass connection/SQL Query information to, to be displayed on the fly.

Example:
A Pivot Table show results of: SELECT * FROM Products WHERE ProductID = <parameter
Web page passes a ProductID and the query is run (with the filter).

I'm assuming that I will have some sort of VBA code to hold the CORE query and I would like to be able to pass the parameter via ASP.

Any additional thoughts?

Thanx,
Bob|||Ok, I think I understand what you're trying to do. I believe this article shows you how (see the Generate XML from a Dataset for use in Excel 2003 section):

http://msdn.microsoft.com/office/understanding/excel/technicalarticles/default.aspx?pull=/library/en-us/odc_xl2003_ta/html/odc_xltransferxmltoxlwvb.asp

Possible to create an OPTIONAL multi-value field that can be left empty?

Greetings,

I have several reports for which the user has asked to have an optional muti-value parameter. They want to be able to select zero, one, many, or all values in the parameter list. The parm list is created through a query and the values are not static.

I would like to allow the user to leave the muti-value field empty if they want to allow all values to appear on the report. I've read some discussion about populating a multi-value default with the same query that produces the multi-value list values - presto, everything is selected. However, this is not a desirable solution for me because I "echo" the users parameter selections in the report heading. Selecting all values (and some parms have a lot of values) would cause the "parm feedback" section to grow large and unreadable.

In short, I don't want to tell the user they have to select everything when they really want to select nothing.

Is there any way to have a muti-value parm that won't insist the user select one or more values?

Thanks,

BCB

Make your query which is populating your parameter to have another value "All" and make that as the default value instead of an empty value that indicates all.

Shyam

Possible to change parameter layout?

Is it possible to force the parameters to line up differently? When I have 3
parameters in a report, they will show up with 2 on the top line and 1 on a
new line. I'd rather have all 3 on the same line. Can I change the layout,
without having to code a new display form?
Kaisa M. LindahlNo, this is not possible in RS 2000. You would need to write your own
parameter front-end to achieve full control over the layout.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:emNGccY9EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Is it possible to force the parameters to line up differently? When I have
3
> parameters in a report, they will show up with 2 on the top line and 1 on
a
> new line. I'd rather have all 3 on the same line. Can I change the layout,
> without having to code a new display form?
> Kaisa M. Lindahl
>

Possible bug with excel export

We're having a problem with the excel export in one of our reports. The data region in the report is filtered by the value of a parameter chosen by the user. Everything displays and works fine until it is exported to excel. When a user does this it outputs the results for the same parameter no matter what parameter is actually selected. It's specifically related to the excel export because every other one works as expected. Can anyone reproduce this problem?Are you running the latest service pack?
Can you try copying the report to another report & then exporting?|||This is actually in 2005. Copying the report to another one doesn't help and unfortunately we don't have our new test server in yet to try it on another machine.