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 Model/Builder

In a database there are 2 tables, one is student and the other is activities. A student can have multiple activities. There is a one to many relationship between student and activities based upon student_id. Not every student has an activity.
In report builder I would like all of the students to appear regardless of whether they have an activity or not. By default, only those students who have activities appear.

This appears:
Student Activity
1 Track
2 Track
4 Volleyball

I would like to see:
Student Activity
1 Track
2 Track
3 NULL
4 Volleyball

What settings need to be tweaked to make this happen?

Thanks for your assistance.Hi

What query are you using to generate this report? I created 2 tables: student, activity. Not all students have activities. If I use the following query:

select * from student
left outer join activity on student.i = activity.i

where i = student id in both the tables,
I get the list of students, activities (even for students who dont have activities mapped on the activity table)

The Left Outer Join operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.

Is this what you were looking for?

Thanks

|||Within Report Designer you can create a query and specify any type of join you need. This is IN REPORT BUILDER, where you are not able to specifiy the type of join you would like to use. The query is generated from the report model and dragging fields into the design area.

Thanks for your response though.|||Hi Chris

Can you try this out and let me know if this works?

Create a report model and within the data source view, right click and add a NEW NAMED QUERY, here you can specify a query with joins. Once this query is created, create the report model. This should also list the query that you have created, apart from the 2 tables.

Now from Report Builder, select this datasource and drag/drop your query.
Remove the columns that you dont want (I just had student id and activity)
and run report.

This will give you all records, even the students who dont have activities listed.

If you are just using a report model, I think you need to edit that and add a query into that, so you can use it in your report builder to get the required data.

Thanks.|||

Actually, you don't need to create a named query to do this. You can do it in the Report Model. In your example, you need to set Cardinality=OptionalMany on the role from Student to activity. This tells RB that not all students have activities (so don’t do an inner join or you might lose a few).

Report Model Project: Multiple Data Sources

I have setup a "Report Model Project" with multiple data sources, and
imported tables from each of these into my data source view. When I try to
use the wizard to create a report model I get an error on the "Completing the
Wizard" stage when I click "Run".
An error occurred while executing a command.
Message: Invalid object name 'dbo.<table name>'.
Command:
SELECT COUNT(*) FROM [dbo].[<table name>] t
The <table name> will always relate to the first table it attempts to
process from the secondary (non-primary) data source. I figured this out by
setting up the project from scratch with the opposite data source as primary.
It seems like it is looking in the primary data source for the table that it
should be trying to use the secondary data source to query.
Any help is appreciated. :)A report model can only reference one data source in this release. Data
source views are more flexible than supported by report models.
-Carolyn
"Greg T. Smith" wrote:
> I have setup a "Report Model Project" with multiple data sources, and
> imported tables from each of these into my data source view. When I try to
> use the wizard to create a report model I get an error on the "Completing the
> Wizard" stage when I click "Run".
> An error occurred while executing a command.
> Message: Invalid object name 'dbo.<table name>'.
> Command:
> SELECT COUNT(*) FROM [dbo].[<table name>] t
> The <table name> will always relate to the first table it attempts to
> process from the secondary (non-primary) data source. I figured this out by
> setting up the project from scratch with the opposite data source as primary.
> It seems like it is looking in the primary data source for the table that it
> should be trying to use the secondary data source to query.
> Any help is appreciated. :)

Report Model from multiple data source

Creating a Data Source View from multiple Data Sources is no problem but what happens when you try to create the actuall Report Model. Using the primary Data Source doesn't give access to other databases, since each table name starts with dbo. Is there a option/flag to set to include database name?

Report Model DSVs only support one database, to pull in data from other databases you'll need to create a Named Query.

Analysis Services, on the other hand, don't have that problem, except for being limited to one database per DSV.

Note: I'm pretty sure I'm correct, but not 100%

sql

Report Model

Can you have more than one data source view in one report model? I have data from multiple sql databases that I want to combine into a single report model for the users.

Currently only one data source per model is supported.

It may be possible however to use Data Source View editor to manually add named queries which reference other databases.

|||

Lev is right. If you want to create a model over a set of databases you need to do some manual work with your DSV.

In model designer start with generating DSV for primary database (the one containing more data). Then manually add named queries to the DSV each selecting data from another database (select * from myotherdb.dbo.tableN). For each named query manually add info about which columns should be treated as PK and which are FKs. This is important for semantic model generation that would omit tables without PKs and will note create roles between entities without FKs.
After you finish with your DSV - generate model in model designer.

Report in Multiple Columns

I have a report, let's say it has two table columns, Name and Phone. I need to format this report so that it will print Name and Phone top to bottom, and then start at the top of a second column instead of going to another page. Do I do this using body columns, and if so, can someone explain how. I have tried by changing the column property of the body to 2, but it just moved a table column over the the other body column. Am I heading down the wrong path? I am using VS 2003 to create the report.

Thanks

Setting the column property of the body is the right way to accomplish this. However, you need to check the page width value of the report. When Body.Column is 2, the report width is divided in half, if that is smaller than the body width/table width, your table'd get splitted.

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.

Report from multiple databases

Hello,
I'm trying to create 2 reports. I have SQL Server 2000 and Access 2003.
Report should be done in Access. There are 4 different databases on same SQL
Server.
I need report showing:
CustomerID
AgencyName
State
TotalDVI
TotalDRI
TotalVI
.....
Totals are counts of all files in corresponding tables for all 4 databases.
To add to this problem, one database has different names of tables and
fields in them then other 3 databases.
Databases are definitely a mess and I can't do anything about it at this time.
Is there a way to accomplish this?
--
Deki PATwo ways:
Create linked tables in Access. Write your queries and reports.
Create linked servers in SQL and use reporting services.
Either is yucky architecturally speaking.
Get the data out of access whenever phesible.
Deki wrote:
> Hello,
> I'm trying to create 2 reports. I have SQL Server 2000 and Access 2003.
> Report should be done in Access. There are 4 different databases on same SQL
> Server.
> I need report showing:
> CustomerID
> AgencyName
> State
> TotalDVI
> TotalDRI
> TotalVI
> .....
> Totals are counts of all files in corresponding tables for all 4 databases.
> To add to this problem, one database has different names of tables and
> fields in them then other 3 databases.
> Databases are definitely a mess and I can't do anything about it at this time.
> Is there a way to accomplish this?
> --
> Deki PA|||Thanks Frisco!
--
Deki PA
"FriscoSoxFan" wrote:
> Two ways:
> Create linked tables in Access. Write your queries and reports.
> Create linked servers in SQL and use reporting services.
> Either is yucky architecturally speaking.
> Get the data out of access whenever phesible.
>
> Deki wrote:
> > Hello,
> > I'm trying to create 2 reports. I have SQL Server 2000 and Access 2003.
> > Report should be done in Access. There are 4 different databases on same SQL
> > Server.
> > I need report showing:
> >
> > CustomerID
> > AgencyName
> > State
> > TotalDVI
> > TotalDRI
> > TotalVI
> > .....
> >
> > Totals are counts of all files in corresponding tables for all 4 databases.
> > To add to this problem, one database has different names of tables and
> > fields in them then other 3 databases.
> > Databases are definitely a mess and I can't do anything about it at this time.
> > Is there a way to accomplish this?
> >
> > --
> > Deki PA
>

Report Format with a Group

Is there a way to have the data rows begin on the same row as the group fields. For example if I have a student with multiple tests/scores, I want the tests/score to begin on the same line as the students name.

You could place everything on a detail row...basically no grouping level. This will display the student name as a repeating element, however.

You can then add some conditional logic to the Hidden property to only display the first occurrence of that student name.

|||Do you have an example of the logic to hide all but the first occurance? The programming side of this is a little new to me.|||

Check out the HideDuplicates feature of TextBox: http://msdn2.microsoft.com/en-us/library/ms152916.aspx.

In ReportDesigner, right click on your textbox and check "Hide duplicates" in the Textbox Properties dialog.

Report Format with a Group

Is there a way to have the data rows begin on the same row as the group fields. For example if I have a student with multiple tests/scores, I want the tests/score to begin on the same line as the students name.

You could place everything on a detail row...basically no grouping level. This will display the student name as a repeating element, however.

You can then add some conditional logic to the Hidden property to only display the first occurrence of that student name.

|||Do you have an example of the logic to hide all but the first occurance? The programming side of this is a little new to me.|||

Check out the HideDuplicates feature of TextBox: http://msdn2.microsoft.com/en-us/library/ms152916.aspx.

In ReportDesigner, right click on your textbox and check "Hide duplicates" in the Textbox Properties dialog.