reporting services valid values query based

I have a parameter that is query based. I have created my own UI to list out
the text boxes per parameter required by the report. Now comes the problem of
a query based parameter. How to I get the queary based valid values to
populate my drop down list boxEddie,
If I understand your correctly I may have some ideas that will help you with
query based Parameters, but first please clarify the reason you built a UI
for users to pass parameters into the report?
thx.
"Eddie" wrote:
> I have a parameter that is query based. I have created my own UI to list out
> the text boxes per parameter required by the report. Now comes the problem of
> a query based parameter. How to I get the queary based valid values to
> populate my drop down list box|||I have several clients that use browsers which the report viewer doesn't
behave well.
I did figure it out. The valid values come back as array when you call the
GetReportParameter method and set the "ForRendering" to TRUE.
So all I did was add the values to ListItems and populated a drop down.
Also, a nifty thing was to find all the Date type parameters and lock the
text box and provide a calendar. This allowed me to use our application's
Localization methods to handle all the different Date formats.
"RS_CZAR" wrote:
> Eddie,
> If I understand your correctly I may have some ideas that will help you with
> query based Parameters, but first please clarify the reason you built a UI
> for users to pass parameters into the report?
> thx.
> "Eddie" wrote:
> > I have a parameter that is query based. I have created my own UI to list out
> > the text boxes per parameter required by the report. Now comes the problem of
> > a query based parameter. How to I get the queary based valid values to
> > populate my drop down list box

Reporting Services SP using Dynamic Queries

Hey i am using Stored Procedures in Sql Reporting Services.
This SP has some parameters and based on these parameter's value Final query is being created at run time. But when i use this SP in VS.Net Reporting Services as dataset then it doesnt show its fields.

Here I am writing some part of my SP to show how things are working at my end. @.Country , @.StartDate , @.EndDate are SP parameters and based on these parameter’s value, some part of query is being added to main query. So in this case again I am not able to get fields in report.

IF LTRIM(RTRIM(@.Country)) IS NOT NULL

BEGIN
SET @.CountryQuery = " AND ID3.id_value LIKE '" + LTRIM(RTRIM(@.Country)) +"' "
END

IF LTRIM(RTRIM(@.StartDate)) IS NOT NULL AND LTRIM(RTRIM(@.EndDate)) IS NOT NULL
BEGIN
SET @.DateQuery = " AND ( "+@.FieldName+" >= '" + LTRIM(RTRIM(@.StartDate)) + "' AND "+@.FieldName+" <= '" + LTRIM(RTRIM(@.EndDate)) + "' ) "
END

SET @.Query = " SELECT DISTINCT field1, field2, field3 FROM Table1 T1 INNER JOIN Table2 T2 ON T1.id_fk = T2.id_code " + @.CityQuery + " WHERE tr_status =1" + @.DateQuery

EXEC (@.Query)

Even I have used temp table in SP and stored values in it but could not able to get desired result (get fields in report).

PLease help me
Thanks

Hi,

Try these steps:

1. In your DataSet put the query with fixed values (i.e. without parameters):

SELECT DISTINCT field1, field2, field3 FROM Table1 ...

2. Execute the Query

3. Go to the "DataSets Window" and select "refresh"

4. Now you should replace the query above with the Stored Procedure command

I think this solves your problem.

--

Telmo Moreira

Reporting Services Soap API Internal Parameter

Using the web service how do i tell if a report parameter is internal, or hidden?A hidden parameter has a PromptUser value of true, but the Prompt is empty. An internal parameter has the PromptUser set to false.

Reporting Services Select All

When Building a report in the Business Intelligence Studio /w a select parameter it gives you a select all option. When you publish the report to your report server you no longer have this option?Most likely, SP1 has been applied to your server and not to your client. See http://blogs.msdn.com/bwelcker/archive/2006/08/14/700189.aspx for the story of "Select All".sql

Reporting Services parameters not in correct order

Hi,

SQL Server Reporting Services 2005.

I have 13 parameters, ordered correctly within the Report Parameter screen.

When displayed in the Preview tab they are all ordered correctly, but when viewed in the application the first 4 are at the top but ordered incorrectly. The remainder are ordered correctly.

I have tried reordering, saving, deploying, viewing and then doing the same but in the correct order without any success.

Please can someone suggest how I can get the parameters to appear in the correct order within the application?

Thanking you,

dwemh

dwemh

Do you mean the alignment of report parameters within ReportViewer

control parameters area? If so, may be custom control for parameters

setting is worth developing. As I know, GetReportParameters() web method returns parameter collection, saving original order.

|||

The parameters are displayed in the order they appear on the report parameters page in the report designer tool. If you have previously published the report, you may need to delete it and replublish for any order changes to be reflected on the server.

Other than that, parameters are always layed out left to right, top to bottom by default (this adapts to your globalization settings). If that doesn't meet you needs, you'll need to follow Anatoly's advice and provide a custom application to display them how you want them.

-Lukasz

|||

Well. this bring me closer to a question I had since days.. Can we have more than 2 parameters displayed in a single row on report (when browsed ). The parameters by default - seems to be lined up in an array of 2 columns, ad N rows - which is frustrating because so much of space is left blank (space just left of <View Report> button.

How did you managed to get 4 parameters in one single row ?

Thanks for ideas.

|||

Yup, can only have 2 columns in our UI and we don't expose a property to set the number of columns. I've added a design change request to our internal system to try to get this addressed.

Right now, the only way you can get more parameters is to build a custom UI.

-Lukasz

sql

Reporting services parameter([rsFieldReference] The Value expression for the...)

@.StartDate is a report parameter and CustomerNo is the field I want to render on the report,I want to combine the sql ,but it display the error message below when I preview the report.If I query in the design form and input the value of the parameter ,it run correctly .

Data sql:

declare @.sql nvarchar(4000)
declare @.sqlWhere nvarchar(4000)

set @.sql='select CustomerNo from table1 '
set @.sqlWhere=''

if @.StartDate<>''
begin
set @.sqlWhere =@.sqlWhere + ' StartDate=''' +@.StartDate + ''''
end

if ltrim(rtrim(@.sqlWhere))<>''
begin
set @.sqlWhere =' where ' + @.sqlWhere
end
set @.sql=@.sql+@.sqlWhere

exec sp_executeSQL @.sql

Error message:

[rsFieldReference] The Value expression for the textbox ‘CustomerNo’ refers to the field ‘CustomerNo’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

I'm a beginner .Help me.

|||

Please give me a answer.

|||

I wrote a procedure and the problem is resolved.

reporting services parameter problem

I have a report that uses as available values for a parameter a query. My problem is that the query return a list of available months, however, sometimes, i would like the user to choose null when they want to see all months in the report. but the query does not have a month null so null is not allowed.
Is there anything I can do? I cannot add a null record in the table itself.
thank you, dreyYou can either allow null for a parameter or use a UNION to add All Months to the available options. (Adding All Months tends to give a better end-user experience.) Note that the following query (because of the UNION) isn't valid in the graphical query designer. You'll be prompted to switch to the generic query designer. You'll have to modify the query to match your particular DB schema.

SELECT MonthNumber, MonthName
FROM Months
UNION
SELECT -1, 'All Months'

Now you've got your parameter values populated. If you're using the 'All Months' option, you'll want to uncheck "Allow null values".

You should now modify your dataset query to:

SELECT column1, column2, column3
FROM table1
WHERE (MonthNumber = @.MonthNumber OR @.MonthNumber = -1)

If you select a month, you will get the appropriate data back for that month. If you select All Months, then MonthNumber = -1 will always be false (since -1 isn't a valid month number), but the second part will evaluate as true for all months.

Hope that helps.

reporting services matrix subtotal

I have a matrix and have used the switch function to select the measure group that is displayed (based on selecting a report parameter). All works fine except the subtotal only shows the first row data.

e.g.

2005

2006

2007

Sales

Sales

Sales

a

2

2

2

b

4

4

4

c

6

6

6

TOTAL

2

2

2

The switch function used to select the measure is:

switch(Parameters!Measure.Value="Gross Sales",Fields!GrossSale.Value,Parameters!Measure.Value="Net Sales",Fields!NetSale.Value,Parameters!Measure.Value="Sales Quantity",Fields!SaleQuantity.Value,Parameters!Measure.Value="Sales Rebate",Fields!SalesRebate.Value)

Any idea why this would happen?

Thanks

Looks like you're not aggregating the applicable measure - how about trying this modified cell expression:

switch(Parameters!Measure.Value="Gross Sales",Aggregate(Fields!GrossSale.Value),Parameters!Measure.Value="Net Sales",Aggregate(Fields!NetSale.Value),Parameters!Measure.Value="Sales Quantity",Aggregate(Fields!SaleQuantity.Value),Parameters!Measure.Value="Sales Rebate",Aggregate(Fields!SalesRebate.Value))

|||

Deepak,

Thanks for your help.

all is fine now.

Reporting Services Login Error

An error has occurred during report processing. (rsProcessingAborted) Get Online Help Logon failed. (rsLogonFailed) Get Online Help The parameter is incorrect.

I can view my report inside of visual studio 2003 no problem, but when I navigate to the http://servername/Reports/Pages/Folder.aspx and click on my report to load inside of IE I get the error. I have serached all over but not able to get a correct answer.
Any help woould be greatly appreciated.
Thanks

check your datasource to make sure it deployed properly and that the report is using the proper datasource (in properties)|||Yes.. its using the Source and be able to see the corrcet datasource in SQL manager. I am thining its more Login Setup, but there is not enough documents.|||It is definitely a data souce error, although it is a strange message. What type of credentials are used to connect to the data source?