Report Parameters

In the report parameters dialogue box I have set up a Non Queried list, when
someone selects Grand Rapids(label) I want to return multiple values, 21 OR
22 OR 23, these are the Grand Rapids Department ID's. Can this be done? If
so what is the correct syntax?
Label Value
Grand Rapids 21 OR 22 OR 23
WHERE (Acclaim.Staff.DepartmentID = @.DepartmentID)Why not make it a string parameter that returns "21,22,23"
and make the where clause like:
WHERE (Acclaim.Staff.DepartmentID in @.DepartmentID)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sampson" <Sampson@.discussions.microsoft.com> wrote in message
news:F361919F-A515-4AE2-B7FA-0D68B6842542@.microsoft.com...
> In the report parameters dialogue box I have set up a Non Queried list,
when
> someone selects Grand Rapids(label) I want to return multiple values, 21
OR
> 22 OR 23, these are the Grand Rapids Department ID's. Can this be done?
If
> so what is the correct syntax?
> Label Value
> Grand Rapids 21 OR 22 OR 23
>
> WHERE (Acclaim.Staff.DepartmentID = @.DepartmentID)
>|||That will not work.You would think it would but it doesn't (most likely on
purpose to prevent injection attacks). You can do a dynamic query. Go into
generic query mode and do this:
= "Select blah from sometable where (Acclaim.Staff.DepartmentID in (" &
Parameters!DepartmentID.value & ")"
Of course if it is not a list of integers you then need to add parse this
(you could write code behind to do this) and put in the single quotes.
The other alternative is pass the parameter to a stored procedure and do it
from there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uAOOxf2RFHA.2528@.TK2MSFTNGP10.phx.gbl...
> Why not make it a string parameter that returns "21,22,23"
> and make the where clause like:
> WHERE (Acclaim.Staff.DepartmentID in @.DepartmentID)
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Sampson" <Sampson@.discussions.microsoft.com> wrote in message
> news:F361919F-A515-4AE2-B7FA-0D68B6842542@.microsoft.com...
> > In the report parameters dialogue box I have set up a Non Queried list,
> when
> > someone selects Grand Rapids(label) I want to return multiple values, 21
> OR
> > 22 OR 23, these are the Grand Rapids Department ID's. Can this be done?
> If
> > so what is the correct syntax?
> >
> > Label Value
> > Grand Rapids 21 OR 22 OR 23
> >
> >
> > WHERE (Acclaim.Staff.DepartmentID = @.DepartmentID)
> >
>

Report parameter won't show values with stored procedure results

Hi,
I wrote a stored procedure in C# to return results that I could use to
populate a report parameter list. The problem occurs when I set up the
parameter. Under "Report Parameters -> Available Values -> From Query", I
can set the dataset fine but no entries appear under the Value field or the
Label field. When I run the query (whether it's thru the data tab or in SQL
server management Studio or the SQL Server project), I get the correct
results. If I try to type in the name of the field, I get an
rsInvalidDataSetReferenceField error when I preview the report.
Below is the steps I took to get to where I'm at:
1.
The stored procedure was supposed to extract certain areas from the area
path, with the TFSWarehouse as my data source. The areas of interest were
top-level areas, i.e. \\TeamProject\TopLevelArea\SubAreas... The
CommandText attribute of my SqlCommand instance is as follows:
@."SELECT DISTINCT Area.[Area Path]
FROM Area INNER JOIN
[Current Work Item] ON Area.__ID = [Current Work
Item].Area";
2.
The results are returned in an SqlDataReader. I read thru each record of
the reader and manipulate some of the data and send it back using
SqlContext.Pipe.SendResultsStart/Row/End when appropriate. The structure of
the record consists of one column, called "TopLevelArea".
3.
I compiled it into an assembly using the Visual Studio command prompt, ran a
T-SQL query to create the procedure, so that it appears in the Stored
Procedures list. And then I created a dataset whose command type is
StoredProcedure, and it contains the name of the procedure. Then I set up
the parameter and that's where everything went all wrong.
Please help.Hi Winkles,
Would you please try to check the DataSet Properties?
Make sure the Field have set a proper name in the data tab of the Report
Project.
Also, please make sure you have set the correct dataset name when you
config the report parameter.
If this does not help, would you please send the sample stored procedure to
me for troubleshooting? Thanks!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello Winkles,
I have tested on my side with your code.
You need to set the Field tab in the DataSet properties.
In the Field Name, type TopLevelArea, and type TopLevelArea in the Value.
(Remove the equal mark). And then, you could get the Field name in the
Parameter.
Please do the above and let me know the result.
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hi Wei,
It worked!!! Thank you very much! I had been including the equal sign
before.
The 'TopLevelArea' in Field Name...does that correspond to the metadata
column in the stored procedure? What does the TopLevelArea in the 'Value'
column match up with? Or do they both refer to the same thing?
Thanks again.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:1BHmMqBwGHA.5976@.TK2MSFTNGXA01.phx.gbl...
> Hello Winkles,
> I have tested on my side with your code.
> You need to set the Field tab in the DataSet properties.
> In the Field Name, type TopLevelArea, and type TopLevelArea in the Value.
> (Remove the equal mark). And then, you could get the Field name in the
> Parameter.
> Please do the above and let me know the result.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
>|||Hi Winkles,
Yes, it correspond to the metadata column in the stored procedure.
The TopLevelArea in the Value column is the Friendly Name you could use in
the Report. You could modify it with other name.
Here is the article for your reference:
Fields
Each dataset in a report contains a list of fields. Typically, the fields
refer to columns or fields returned by the query in the dataset. Fields
that refer to database fields contain a pointer to the database field and a
name property. You can use the name property to provide a friendly name in
place of the name of the database field. In addition to database fields,
the fields list can contain calculated fields. Calculated fields contain a
name and an expression. The expression can be as simple as a concatenation
of two database fields (for example, first name and last name), or it can
be used to perform complex calculations.
Some query languages are flexible enough so that a query can be written to
return friendly field names and perform calculations, making changes to the
fields list unnecessary. The fields list is especially useful when using a
database or query language that does not provide this flexibility.
http://msdn2.microsoft.com/en-us/library/ms160324.aspx
If you have any questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Supportsql

Report Parameter to return a "NULL" value

All
I am writing a report by salesperson and have the salespersons name as
a report Parameter however in the database it can return a NULL value
is the sale was genereated by someone other than the sales team. In
order for me to get the correct totals i need to include the NULL
value but if i select NULL it returns a blank report?
Any ideas?
cheersOn Jun 18, 3:38 am, blueboy <matt_me...@.hotmail.com> wrote:
> All
> I am writing a report by salesperson and have the salespersons name as
> a report Parameter however in the database it can return a NULL value
> is the sale was genereated by someone other than the sales team. In
> order for me to get the correct totals i need to include the NULL
> value but if i select NULL it returns a blank report?
> Any ideas?
> cheers
If I understand you correctly, have you tried using a value similar to
"Non Applicable" or some other descriptor that represents the null
value in the report parameter. That way, in the stored procedure/query
that is sourcing the report you can check for "...if @.SalesPerson ='Non Applicable' begin select * from table_x where salesperson is
null... end..." Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report pagination for charts

Hi,

We have developed few reports displaying data using chart layout. In the Data tab, we have specified MDX query that will return top 10 records.

But now, instead of restricting to just top 10 records, we would like to display all records and go in for pagination.

Is there some setting in the chart properties, where in I can display the first n records in first page, and the next n records( if available) in the next page and so on?

Can I specify the value of n somewhere in the propeties?

I read through many posts regarding pagination but those couldn't help me much.

Please help me in solving this problem.

Any help would be appreciated.

Thanks in advance!

No response yet!:(|||

One approach is the following:

Step 1: Add a table to the report
Step 2: Group by a number of rows
Right-click on the table and select Properties. Add a table group (with a group header, but no group footer)
Enter this for the group expression: =Ceiling(RowNumber(Nothing)/10)
This will cause the table to group on every ten rows. So you'll get a separate table group for every ten rows.
Step 3: Add a chart in the table group header
Design your chart.

Note: this approach is similar to the table inline charts approach discussed in the following whitepaper: http://msdn2.microsoft.com/en-us/library/aa964128.aspx

-- Robert

|||

Thanks a lot Robert!

I got the idea from the solution provided by you and did the following:

Step 1: Add a list to the report

Step 2: Added group expression for the list to group the data

Grouping =Ceiling(RowNumber(Nothing)/10)

Set the following properties for list:

KeepTogether: False

PageBreakAtEnd: True

Step 3: Added chart to the list

Set the property, PageBreakAtEnd: True for the chart.

The above steps solved my problem of report pagination for charts.

Thanks once again!

sql

Report pagination for charts

Hi,

We have developed few reports displaying data using chart layout. In the Data tab, we have specified MDX query that will return top 10 records.

But now, instead of restricting to just top 10 records, we would like to display all records and go in for pagination.

Is there some setting in the chart properties, where in I can display the first n records in first page, and the next n records( if available) in the next page and so on?

Can I specify the value of n somewhere in the propeties?

I read through many posts regarding pagination but those couldn't help me much.

Please help me in solving this problem.

Any help would be appreciated.

Thanks in advance!

No response yet!:(|||

One approach is the following:

Step 1: Add a table to the report
Step 2: Group by a number of rows
Right-click on the table and select Properties. Add a table group (with a group header, but no group footer)
Enter this for the group expression: =Ceiling(RowNumber(Nothing)/10)
This will cause the table to group on every ten rows. So you'll get a separate table group for every ten rows.
Step 3: Add a chart in the table group header
Design your chart.

Note: this approach is similar to the table inline charts approach discussed in the following whitepaper: http://msdn2.microsoft.com/en-us/library/aa964128.aspx

-- Robert

|||

Thanks a lot Robert!

I got the idea from the solution provided by you and did the following:

Step 1: Add a list to the report

Step 2: Added group expression for the list to group the data

Grouping =Ceiling(RowNumber(Nothing)/10)

Set the following properties for list:

KeepTogether: False

PageBreakAtEnd: True

Step 3: Added chart to the list

Set the property, PageBreakAtEnd: True for the chart.

The above steps solved my problem of report pagination for charts.

Thanks once again!

Report Headers showing on blank page

I hope someone will have time to answer a really basic RS question.

I have a simple report, the query will only return a few rows (16 - 20), my header is repeating on a 2nd blank page. I'm sure it's because I grouped on a column from the record set. I can not find where you delete the grouping.

Anyone have any good suggestions on RS books?

Thanks

Hey Marc,

There are a couple of places to check:

When you click on the table, are there rows that have little numbers on them (1,2, etc)? If so, you should be able to right-click on these and select Delete Groups.

Another place could be the Properties of the table (Select the table, right click on the Properties option). Select the Grouping tab. Delete any of the groups in there (usually named something like "details_group")

Good luck!

Jessica

Report from prcedure that return multiple recordsets

Hi there,

I am creating a report from a stored procedure that returns

multiples record sets. For an example, my stored procedure is as follows

CREATE PROCEDURE MYPROCEDURE
AS

BEGIN
SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3
END

Now lets say I want to create a report that will display the

result of the query no 2 (which is SELECT * FROM TABLE2 in this example). How

can I do this? Is there any way to bind the second record set to the report

dataset with out changing the stored procedure?
I will appreciate any kind of suggestions on this

Thanks
Moim

yes,

you can do this in ur procedure itself. based upon what criteria you decide which query to run, pass that criteria to procedure as a parameter, and then use if condition to run the query

|||Suresh,
Thanks for your reply. But I am sorry I dont have a chance to modify the Stored procesdure. Those are written for a legacy software and they are running good for couple of years.
So can you or any one else find me an alternate to do this?

Anyway, thanks shuresh.

Thanks
Moim|||

Moim,

Unfortunately, SSRS doesn't support multiple recordsets being returned from a stored procedure. It will use the first resultset returned only.

Can you break up your master stored proc into 3 smaller stored procs, each called by separate reports with it's own resultset?

Jarret

|||Hmmm..seems I have to do that Jarret. Anyway thanks for your reply.|||

Can you mark this one as answered so others can see that multiple recordsets aren't supported?

Thanks.

Jarret

|||Sure. Thanks|||

Thanks Jarret for the answer. I did that earlier , but couldn't get my procedure to work by passing parameters to get the resulting resultset. Insted I have created different procedures and made it to work.

I didn't know that this is a constraint with SSRS.