JSP + Reporting 2005

Hi,
whats the best way to implement a Java Servelet/JSP to use reporting
services 2005?
I dont want to use the anonymous access,
is there a way to do this?a kind of proxy like this...
http://www.javaworld.com/javaworld/jw-01-2005/jw-0110-sqlrs.html
but with RS 2005
greetings
MauroMauro,
Check out this lab: Implementing SQL Server Reporting Services with a Java
EE Application-Building a Reporting Services Solution Virtual Lab
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032315323&EventCategory=3&culture=en-US&CountryCode=US
Reeves
"Mauro SB." wrote:
> Hi,
> whats the best way to implement a Java Servelet/JSP to use reporting
> services 2005?
> I dont want to use the anonymous access,
> is there a way to do this?a kind of proxy like this...
> http://www.javaworld.com/javaworld/jw-01-2005/jw-0110-sqlrs.html
> but with RS 2005
> greetings
> Mauro
>
>|||tnks veeeery much!
gretings
Mauro
"Reeves Smith" <ReevesSmith@.discussions.microsoft.com> escribió en el
mensaje news:E7113029-9686-474C-BF7E-8F37A01D4ABE@.microsoft.com...
> Mauro,
> Check out this lab: Implementing SQL Server Reporting Services with a Java
> EE Application-Building a Reporting Services Solution Virtual Lab
> http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032315323&EventCategory=3&culture=en-US&CountryCode=US
> Reeves
> "Mauro SB." wrote:
>> Hi,
>> whats the best way to implement a Java Servelet/JSP to use reporting
>> services 2005?
>> I dont want to use the anonymous access,
>> is there a way to do this?a kind of proxy like this...
>> http://www.javaworld.com/javaworld/jw-01-2005/jw-0110-sqlrs.html
>> but with RS 2005
>> greetings
>> Mauro
>>

Joining two tables with repeating rows

Hi. I am trying to get data from two different tables. The first table contains user access data for access to different modules of our application. There are only records for the modules that the user has access to. So if User1 can only access 2 of the 5 modules, there will be 2 User1 records. The second table lists the modules.

UserAccess table:

UserID ModuleID AccessLevel

User1 1 1

User1 2 1

User2 1 1

Modules table:

ModuleID Description

1 Mod1

2 Mod2

3 Mod3

4 Mod4

5 Mod5

What I am trying to select is a list of all modules for each user, whether they have access or not. So basically I would like the data from the Modules table to repeat 5 rows for each user. This is a sample of the output I am trying to get:

UserID ModuleID AccessLevel

User1 1 1

User1 2 1

User1 3 Null

User1 4 Null

User1 5 Null

User2 1 1

User2 2 Null

User2 3 Null

User2 4 Null

User2 5 Null

I've tried all sorts of joins but haven't been able to get this to happen. Not sure if this is possible? Thanks!!!

The magic words you want here are "CROSS JOIN". Previously known as 'comma'.

First make yourself a table of users. If you have this in a separate table already, then great. If you don't, slap yourself, write a post-it note to do it later, and make do with:

select *
from
(select distinct UserID from UserAccess) u

Now do your cross join to the modules table.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m

Note - there's no ON clause here... you want every possible combination.

Now join this to your UserAccess table to see if they have access or not. You'll want to use a LEFT JOIN to make sure you don't eliminate the rows you already have.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m
left join
UserAccess ua
on ua.moduleid = m.moduleid
and ua.userid = u.userid

Now, ua will have null records for the times when there is no record to match (it's the way LEFT JOIN works). So you can just have a look to see if one of the records which can't be null (like userid) is null or not...

select u.UserID, m.ModuleID, case when ua.UserID is null then 1 else 0 end as AccessLevel

from

(select distinct UserID from UserAccess) u

cross join

Modules m

left join

UserAccess ua

on ua.moduleid = m.moduleid

and ua.userid = u.userid

Hope this works for you!

Rob|||

Part of the problem is that you need to have a users table. Then this becomes a bit easier of a task. So I built one in the query:

--sample tables (please include in future if you can...)
create table userAccess
(
userId varchar(8)
,moduleId int
,accessLevel int
,primary key (userId, moduleId)
)
insert into userAccess
select 'User1', 1, 1
union all
select 'User1', 2, 1
union all
select 'User2', 1, 1

create table modules
(
moduleId int
,description varchar(10)
,primary key (moduleId)
)
insert into modules
select 1, 'Mod1'
union all
select 2, 'Mod2'
union all
select 3, 'Mod3'
union all
select 4, 'Mod4'
union all
select 5, 'Mod5'

This query will get it for you:

select users.userId, modules.moduleId,userAccess.accessLevel
from (select distinct userId
from userAccess) as users --creates the users table with all users that have some access
cross join modules --cross join it with modules to get the all modules for all users set.

left outer join userAccess --then left join it to the access table to get the accessLevel column...
on userAccess.userId = users.userId
and modules.moduleId = userAccess.moduleId

|||Apparently I worked on my solution for at least 15 minutes :)|||15 minutes? Really?|||

Thank you both so much!!! I KNEW there was a way to do this but I couldn't quite get there. I do have a users table so luckily I do not have to slap myself.

Thanks again!!!

|||

You try to find out in

www.sqlzoo.com

Joining two tables

I imported two excel spreadsheets from excel to Access. Invoice Register 2005 table and Invoice Register 2006 table. These excel files were exports from peachtree.

Each client can have multiple invoices in each of these registers. Each client though does have a unique client numbers but in these tables there are duplicate client numbers because off the multiple invoices created throughout the year.

I created a query linking these two tables together and what is happening when I link the two tables using the Client ID numbers the invoices are listing more than once.

Example there is a client with 3 invoices in 2005 and 3 invoices in 2006 (different invoice numbers) so in stead of listing six invoices for this one client there are nine records. The query shows nine invoices but there are only six for this one client - it is duplicating some of these invoices twice.

What I am trying to show is the client ID and Client name and the invoice register for 2005 and 2006. Is this possible?

Lborshard

I think what you need is to UNION the two table instead of JOIN them.|||

I will read up on UNION and give it a try.

Thank you.

lborshard

|||

phe,

Thanks for the help the Union did work - what a learning experience.

The only thing now, is that I need the 2005 Invoice Column/field and the 2006 Invoice column/ field to be seperate columns as I started with in the import.

The query combined the 2005 and 2006 invoices underneath each other. Is there any way I can list these two columns in the union query?

lborshard

Originally when I linked these two seperate tables together it seemed to copy/duplicate some of the invoice. For example: one client had 3 invoices in 2005 and 3 invoices in 2006 - the client ID was the link - Instead of showing 6 individual invoice for that specific client it showed nine.

Joining datasets returned from stored procedures.

Hello,
I am restricted to using stored procedures for data access. I would like to
be able to create a "Text" Dataset named C which is a join of two "Stored
Procedure" data sets (sp1, and sp2). Any idea how I can do this. Thanks.Tyr this:
SELECT * FROM OPENQUERY(servername, 'sp_1')
union
SELECT * FROM OPENQUERY(servername, 'sp_2')
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:%23%23nvqx4YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am restricted to using stored procedures for data access. I would like
to
> be able to create a "Text" Dataset named C which is a join of two "Stored
> Procedure" data sets (sp1, and sp2). Any idea how I can do this. Thanks.
>

Joining 2 fields: Redundancy results..need help

I am working on this access DB that has been created by some consulting firm couple of years ago, which I wasnt here at that time.

The DB is being by college faculty members to store about students who study abroad..
now,
there are students who has more than one major.. and when I run query to find out how many students are studying abroad..
it shows me more than the actual number of students who are studying abroad, because it shows same student twice because of double major..
In other words, it inserts two records for a student who has 2 majors into the DB.

Now my question is, is it possible to combine 2 records into one record on query results?
I know you can do the following: SELECT StudentName = 'major + major'

but the problem is, the name for field major is the same.. so I cannot say in my query 'Major + Major' to combine 2 records. it doesnt work..

let me know if anyone has solution to this, that will be greatful...
thanks,

moradCan you post your table structure? That would help me give you more specific answers.

The short answer is to pick one of the majors as the "most important", and select only that row using a condition in the WHERE clause. If you want access to both rows, use a LEFT JOIN to get access to the second row. If you post a DDL declaration, I can give you more specific help.

-PatP|||I just attached the structure..
well, there is most important major on this database..
and all records are stored in that table...

for example.. here is my query

First Name Last Name Host Country ProgramName Sponsor
Rebecca AINSWO Griffith University Australia N/A Direct
Ronda ALEXAN Universitat at Bonn Germany N/A Western Michigan University
Matt ANDER Rikkyo University Japan N/A Western Michigan University
Matt ANDER Rikkyo University Japan N/A Western Michigan University
Nicholas Applin University of Wollongong Australia N/A Western Michigan University

I get two records of Ander! because Matt Ander has double major, thus he has double records...two records are the same except the major.

let me know what you think...
thanks|||Now I've got the stuff that I needed to get specific! Try using:SELECT *
FROM tblMajor AS a
LEFT JOIN tblMajor AS b
ON (b.SID = a.SID)
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID)
AND (a.MajId < b.MajId OR b.MajId IS NULL);I'm pretty sure that this will give you what you want.

-PatP|||it worked :) but here is the thing though..
it only selected the duplicates, what about the other records that are not duplicate..

the query doesnt show them...
what do I have to add to show the rest... let me know :)
because I tried to use the union and I couldnt t use it union because I need to have same amount of columns for two tables.

thanks.. I appreciate your help|||Crud! The syntax I posted works with real SQL, but not with Jet (the default engine supplied with MS-Access). You could use something like:SELECT a.*
, (SELECT Max(b.MajId)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);This works around an ugly limitation of the Jet database engine.

-PatP|||I tried that today at work, and it worked, but the only thing is that I wanted to change is, instead of showing what record number of the 2nd major for the student, I wanted to show the actual 2nd Major Name.

in other words I want second_major to show the actual name of the major and not the field number of 2nd major..

let me know if you have an idea..

thanks|||Picky, picky, picky... ;)SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);...should fix you right up!

-PatP|||hey pat,
thanks for great help...

Now, what I asked you about was for tblMajor.

what I am trying to do now, is run a query to list students and their majors as well as their minors..

When I tried to run your code for Majors.. it worked, and it added a new field called second major.

but now, when I try to include Minor in my query, it would show the same problem, because student can have more than one minor.

So basically, the objective is to get the following results
SID, Major, 2nd Major, Minor, 2nd Minor

And the minor table is same as major table design as shown above in my previous post.

Now what I want to know, is how to combine these two queries into one

this:
SELECT a.*, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS [Second Major]
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);

AND

SELECT e.*, (SELECT Max(f.Minor)
FROM tblMinor AS f
WHERE f.SID = e.SID
AND e.MinId < b.MinId) AS [Second Minor]
FROM tblMinor AS e
WHERE e.MinId = (SELECT Min(g.MinId)
FROM tblMinor AS g
WHERE g.SID = e.SID);

Now I was thinking of having using SID as relationship between them, but then I couldnt figure it out.. let me know if you have an idea of how to..

thanks|||The second minor throws an interesting wrinkle into the query, because it now makes a three set intersection instead of just two. You'll need to test this carefully with your data, but I think that you can use:SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMajor AS d
WHERE d.SID = a.SID
AND d.Minor <> a.Minor) AS second_minor
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);The gist of this query is that the first row you'd find if the table was sorted by SID then by MajId is assumed to contain the student's "primary" major and minor. The b and d subqueries find the largest Major and Minor that aren't the "primary" values. While this makes perfect sense to me as an outsider, it may or may not make sense in terms of your data, YMMV (your milage may vary). Test this carefully, but logically it should work.

-PatP|||I've tried the code you posted with few tweeks and I was able to get it through

SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMinor AS d, tblMinor AS e
WHERE d.SID = a.SID
AND d.Minor <> e.Minor) AS second_minor
FROM tblMajor AS a, tblProcessInfo, tblMinor
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID) AND tblProcessInfo.SID = a.SID AND tblProcessInfo.Term = '041';

Now, this query only shows the 2nd minor and not the first minor..
I tried to select tblMinor.Minor in the select statement, and that didnt help much..
let me know what you think..

thanks..|||This is a pure crap-shoot, based on the assumption that the tblMinor structure is exactly like the tblMajor structure. You'll need to test this very carefully before you "bless" this into production!!!SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS e
WHERE e.SID = a.SID
AND a.MajId < e.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMinor AS e
WHERE e.SID = b.SID
AND b.MinId < e.MinId) AS second_minor
FROM tblProcessInfo AS p
JOIN tblMajor AS a
ON (a.SID = p.SID
AND a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID))
LEFT JOIN tblMinor AS b
on (b.SID = p.SID
AND b.MinId = (SELECT Min(d.MinId)
FROM tblMinor AS d
WHERE d.SID = a.SID))
WHERE tblProcessInfo.Term = '041';If this doesn't work, I'd suggest that you create a "play" copy of your MDB file. Butcher the names and the universities to avoid giving out any usable personal information and post the MDB so I can work with your structures instead of having to guess about everything.

Better yet, see if you can find some enterprising grad student scrabbling for some way to get a few co-op dollars or even just some resum worthy experience! I'm sure that some of them would eat this kind of problem alive, and grovel for the opportunity!

-PatP|||hey pat, thanks for your great help..
I was gone for finals and projects that were due..but everything is back to normal now :)

Now, I need a logical explanation for this problem..

When I run a query of how many students were in a certain country from year 2000 to 2004 I get 490 Students (No duplication records)

And when I run a query of how many students with majors (tblMajor Does have duplication records because of having more than one major) that went to that country from 2000 to 2004.. I get 435

I am missing 50 records when I link tblMajor.SID with tblPermInfo.SID and run a query.

I just dont get it why?!
I thought for myself, that tblPermInfo maybe is giving 490 because there is duplication of records for having more than one major, but then it is not linked to tblMajor..so there is no duplication in what so ever.
but when i run a query where SID of tblMajor and tblPermInfo is matched...it only gives me 435...
so there are SIDs that are left over because there is no match btw tables..right?

What other logical reasons could there be..
let me know what you think..
thanks

kicker

Join tables in SQL 6.5 with SQL 7.0

I have a table in SQL that contains a timestamp column and I'm traying access it from a select statement in SQL 7.0, but I have a problem..., this is the error message:Server:
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata for a column. Metadata information was changed at execution time.\
but I can access other table in the same server...Consider simply not selecting the timestamp column. (Do not attempt to update or insert values into timestamp columns either.)

Timestamp columns generally do not need to be accessed for typical user purposes. Timestamp columns are system updated each time a row is inserted or updated (in a table object containing a timestamp column)

Join sql with access

Hi
How can join 2 diferents databases, one in sql and other in access to make only one sql comand?

If you want to do this from SQL Server the process is called creating a linked server. And then you'd run adistributed query to access the data. SeeAccessing and Changing Relational Data - OLE DB Provider for Jet in Books Online.
If you want to do this from Access the process is called creating a Link Table.