can we combine these 3 statements into one single query

SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp

SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.idSELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
UNION ALL
SELECT 2 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
UNION ALL
SELECT 3 as id, (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id|||The above query doesn't work for me and infact i want to get the percentage in a single query..
Thanks|||I'm "winging" this one wildly, but could you use:SELECT
CAST(Sum(CASE WHEN name IS NOT NULL
AND name <> '' THEN 1 END) AS FLOAT) / Count(*)
FROM dbo.empThis divides the number of names with value by the total to get the percentage of usable names.

-PatP|||How about

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX|||SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
........................

I could not execute the above query, can we write the query like that?
please correct me if i am wrong?|||Don't use Name <> NULL

No value will ever match that:

MyField > NULL will always be false
MyField < NULL will always be false
MyField <> NULL will always be false
MyField = NULL will always be false
and so on for all applicable operators...

try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.|||try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.

My bad. Use this instead:

NULLIF('', TRIM(Name)) IS NOT NULL|||Just curious, but didn't my suggestion work? What results did it produce?

-PatP|||Replace:
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
By:
WHERE LTRIM(ISNULL(name,'')) <> '' ) AS XXX

yabu.

Can this be done with a single query?

Please be polite enough o post DDL in the future. Is this what you
meant?
CREATE TABLE GradeBook
(student_name CHAR(20) NOT NULL,
subject_name CHAR(10) NOT NULL,
PRIMARY KEY (student_name, subject)
student_grade INTEGER NOT NULL
CHECK(student_grade BETWEEN 0 AND 100));
The term list has a certain meaning in Computer Science that you did
not mean. Try something like this:
SELECT student_name
FROM GradeBook
WHERE grade = 85
AND subject_name IN ('math', 'physics', 'chemistry')
GROUP BY student_name
HAVING COUNT(*) = 3;
Look up "relatioanl Division" for a more general approach to this kind
of problem."--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1143495213.186157.100040@.u72g2000cwu.googlegroups.com...
> Please be polite enough o post DDL in the future. Is this what you
> meant?
> CREATE TABLE GradeBook
> (student_name CHAR(20) NOT NULL,
> subject_name CHAR(10) NOT NULL,
> PRIMARY KEY (student_name, subject)
> student_grade INTEGER NOT NULL
> CHECK(student_grade BETWEEN 0 AND 100));
> The term list has a certain meaning in Computer Science that you did
> not mean. Try something like this:
> SELECT student_name
> FROM GradeBook
> WHERE grade = 85
> AND subject_name IN ('math', 'physics', 'chemistry')
> GROUP BY student_name
> HAVING COUNT(*) = 3;
> Look up "relatioanl Division" for a more general approach to this kind
> of problem.
>
Joe, you've fallen into the same trap that ALL the other posters fell into.
The original question was...
Find all the students who got a grade 85 or better in math, physics and
chemistry.
That is you need all three rows for a single student. And only of all three
rows are over 85 select the student. Hence why I did the solution which
uses a pivot.
Colin.

Can this be done with a single query?

Colin Dawson wrote:
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1143495213.186157.100040@.u72g2000cwu.googlegroups.com...
>
> Joe, you've fallen into the same trap that ALL the other posters fell into
.
> The original question was...
> Find all the students who got a grade 85 or better in math, physics and
> chemistry.
> That is you need all three rows for a single student. And only of all thr
ee
> rows are over 85 select the student. Hence why I did the solution which
> uses a pivot.
> Colin.
Colin,
Have you actually *tried* most of the other solutions posted here? I
think you'd be surprised. Okay, retlaws solution was wrong. Samis
solution works (it aliases the StudentGrades table three times in it's
from clause, then applies the appropriate criteria to each one). Yours
works. Erlands works (his finds ANY students who *do not* have a grade
less than or equal to 85 - though admittedly he has implicitly assumed
that the students have grades in all three subjects, and only in those
three subjects). Joe Celkos works (using, as he stated, relational
division). David's also works, though amazingly it seems to be the only
solution by anyone else that you haven't criticized.
There are many ways to skin a cat. For the OP, if this is a regularly
run thing, rather than a one off, take all of the solutions, try them
out, and see which perform best for your data.
Damien"Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
news:1143531396.557955.277170@.t31g2000cwb.googlegroups.com...
> Colin Dawson wrote:
> Colin,
> Have you actually *tried* most of the other solutions posted here? I
> think you'd be surprised. Okay, retlaws solution was wrong. Samis
> solution works (it aliases the StudentGrades table three times in it's
> from clause, then applies the appropriate criteria to each one). Yours
> works. Erlands works (his finds ANY students who *do not* have a grade
> less than or equal to 85 - though admittedly he has implicitly assumed
> that the students have grades in all three subjects, and only in those
> three subjects). Joe Celkos works (using, as he stated, relational
> division). David's also works, though amazingly it seems to be the only
> solution by anyone else that you haven't criticized.
> There are many ways to skin a cat. For the OP, if this is a regularly
> run thing, rather than a one off, take all of the solutions, try them
> out, and see which perform best for your data.
> Damien
>
I did try the ones which had been posted prior to my answer. I didn't
believe they would work and wanted to confirm it.
hmmm, I think as you're saying that some of them do work, I need to check
some of these out again. Maybe I've missed something that you've noticed.
Regards
Colin Dawson.|||Thank you all for the inputs ... very informative. I've already implemented
the pivot
solution in my development environment and it is working as expected. In my
attempt to
simplify my problem as much as possible, I may have used a too simple hypoth
etical
situation.
Since some like to see DDL, here is the table I'm using;
CREATE TABLE ComputerAttribute (
ComputerID int NOT NULL , -- FK into the Computer table
isTarget bit NOT NULL , -- Target/source computer a
ttribute.
AttributeID smallint NOT NULL , -- ID as defined in IDLookup tab
le
StringValue varchar (256) NULL , -- Attribute defined as a string
NumericValue int NULL -- Attribute defined as num
eric
)
Here is a about 1/3 of the possible values in AttributeID :
1 NetBIOSName
2 MACAddress
3 AssetTag
4 SerialNumber
5 PrimaryUser
6 Owner
7 Model
8 Vendor
9 NetBIOSDomain
10 DNSDomain
11 ComputerPUID
12 UUID
13 IPAddress
14 Country
15 State
15 Province
16 City
17 Street
18 Building
19 Floor
20 Department
21 OSName
22 Role
23 CPUType
24 MemoryMB
Below is a sample NON SYNTAXICALLY VALID SQL statement that reflect what I w
as sing in
the first place:
SELECT * FROM ComputerAttribute WHERE ComputerID IN (SELECT ComputerID WHERE
(AttributeID=MACAddress AND StringValue='00:11:22:33:44:55') AND (AttributeI
D=NetBIOSName
AND StringValue like 'usca%') AND (AttributeID=MemoryMB AND NumericValue>25
6) FROM
ComputerAttribute)
Of course the above statement will never work. As indicated, I implemented
in C# code a
pivot like solution as described in the post replies. Here is a valid SQL st
atement that
the code currently generates based frim the query selections made on a WEB p
age;
SELECT CA.ComputerID,
Q1=ISNULL((SELECT TOP 1 1 FROM ComputerAttribute WHERE ComputerID=CA.Compute
rID AND
isTarget IN (0,1) AND AttributeID=1 AND StringValue LIKE 'usca%'),NULL),
Q2=ISNULL((SELECT TOP 1 1 FROM ComputerAttribute WHERE ComputerID=CA.Compute
rID AND
isTarget IN (0,1) AND AttributeID=27 AND NumericValue > 256),NULL)
INTO #ADSFSTEMP FROM ComputerAttribute CA GROUP BY CA.ComputerID
Once I have the ComputerID values in the #ADSFSTEMP table, I use its content
to retrieve
data from other tables. I use only the ComputerID values in #ADSFSTEMP for
which all the
Q* columns are not null; "SELECT ComputerID FROM #ADSFSTEMP WHERE Q1 IS NOT
NULL AND Q2 IS
NOT NULL"
Again, thank you all for the inputs ... very informative indeed.
Gaetan.|||First of all, I'd like to make an apology. I said that most of these
answers don't work, but they do!
Some of the procedures needed tweaking so that they ran, but the solutions
do work.
Sami's, David Portas, Erland Sommarskog all return the same and correct
result.
Further to this, I figured that it would be worthwhile performing a little
further analysis to find out which is the best solution. i.e. the most
efficient.
First I ran the four solutions as a single batch, with the show actual
execution plan turned on. I'll post the actual ddl and dml at the bottom of
this post, so that you can all confirm these results.
There's the % of the batch taken by each solution
Colin's 16%
Sami's 49%
David Portas 16%
Erland Sommarskog 20%
Ok, let's take a closer look at Sami's solution. In effect, using the table
alias's it needs to perform 3 passes on the data, this will take time. Each
distinct pass is looking for a specific item, then finally joins the three
tables together to produce the result.
Erland solution he's described well himself.
Which leaves mine and David's. Running these two items in isolation they're
not identical, but they both perform only one pass on the data. The two
execution plans actually perform the same actions, but in a different order.
The real difference is the compute scalar node on the plan. In my solution
this is performing the case statements. In david's it's performing an
implicit convert to an int. (I think on the results count).
There really is nothing to choose between the two execution plans.
What about other statistics?
well set statics io on, shows that the queries are identical.
SQL Profiler, shows exactly the same.
Basically, there's absolutly nothing to choose between these two perfectly
valid results.
Regards
Colin Dawson
www.cjdawson.com
p.s. Here's the DDL
create database studenttest
go
use studenttest
Go
create table students(
student varchar(50),
subject varchar(50),
grade integer
)
Go
Insert Into students( student, subject, grade ) values ( 'student1', 'math',
80 )
Insert Into students( student, subject, grade ) values ( 'student2', 'math',
85 )
Insert Into students( student, subject, grade ) values ( 'student3', 'math',
70 )
Insert Into students( student, subject, grade ) values ( 'student1',
'physics', 90 )
Insert Into students( student, subject, grade ) values ( 'student2',
'physics', 80 )
Insert Into students( student, subject, grade ) values ( 'student3',
'physics', 75 )
Insert Into students( student, subject, grade ) values ( 'student1',
'chemistry', 80 )
Insert Into students( student, subject, grade ) values ( 'student2',
'chemistry', 90 )
Insert Into students( student, subject, grade ) values ( 'student3',
'chemistry', 95 )
Insert Into students( student, subject, grade ) values ( 'student4', 'math',
86 )
Insert Into students( student, subject, grade ) values ( 'student4',
'physics', 86 )
Insert Into students( student, subject, grade ) values ( 'student4',
'chemistry', 86 )
--Here's a student that didn't take physics, just to see if I can upset the
apple cart
Insert Into students( student, subject, grade ) values ( 'student5', 'math',
86 )
Insert Into students( student, subject, grade ) values ( 'student5',
'chemistry', 86 )
Go
--Colin's Solution
Select
s.student
From (
Select
student,
max( case when subject = 'math' then grade end ) math,
max( case when subject = 'physics' then grade end ) physics,
max( case when subject = 'chemistry' then grade end ) chemistry
From students
Group by student
) s
where s.math > 85
and s.physics > 85
and s.chemistry > 85
--Sami's solution
Select SGMaths.student
From students as SGMaths,
students as SGPhysics,
students as SGChemistry
Where SGMaths.student = SGPhysics.student
And SGPhysics.student = SGChemistry.student
And SGMaths.subject = 'math'
And SGMaths.grade > 85
And SGPhysics.subject = 'physics'
And SGPhysics.grade > 85
And SGChemistry.subject = 'chemistry'
And SGChemistry.grade > 85
--David Portas soltion
Select student
From students
Where subject In ('math','physics','chemistry')
And grade > 85
Group By student
Having Count(*) = 3;
--Erland Sommarskog solution
Select Distinct
s.student
From students s
Where Not exists (Select *
From students g
Where g.student = s.student
And g.subject in ('math', 'phyiscs', 'chemistry')
And g.grade <= 85)
Set statistics io on
--Colin's Solution
Select
s.student
From (
Select
student,
max( case when subject = 'math' then grade end ) math,
max( case when subject = 'physics' then grade end ) physics,
max( case when subject = 'chemistry' then grade end ) chemistry
From students
Group by student
) s
where s.math > 85
and s.physics > 85
and s.chemistry > 85
Go
--David Portas soltion
Select student
From students
Where subject In ('math','physics','chemistry')
And grade > 85
Group By student
Having Count(*) = 3;

Can this be done with a single query?

Colin,
Pivot operations is exactly what I needed. I applied the pivot type code aga
inst my real
SQL 2000 table and it is doing what I was looking for.
Thanks you very much.
Gaetan
On Sun, 26 Mar 2006 13:09:12 GMT, "Colin Dawson" <newsgroups@.cjdawson.com> w
rote:

>This is a simple pivot operation.
>In SQL 2000 it'll work like this.
>Select
>s.student
>From (
>Select
>student,
>max( case when subject = 'math' then grade end ) math,
>max( case when subject = 'physics' then grade end ) physics,
>max( case when subject = 'chemistry' then grade end ) chemistry
>From #student
>Group by student
> ) s
>where s.math >= 85
>and s.physics >= 85
>and s.chemistry >= 85
>
>and in SQL 2005 it works like this.
>Select
>student
>From #student s
>Pivot ( Max( s.grade ) For s.subject in ( [math], [physics], [chemistry] ) )
>as pvt
>where pvt.math >= 85
>and pvt.physics >= 85
>and pvt.chemistry >= 85
>
>The execution plan for the SQL2005 example is slightly more efficient, but
>only be removing one compute scaler from the plan. All in all, that's not
>an issue.
>
>Regards
>Colin Dawson
>www.cjdawson.com
>
>"Gaetan" <me@.somewhere.com> wrote in message
> news:m9sb221pmojvn8t9m368gnopdshmjti023@.
4ax.com...
>thx.
Colin.

can this b done by replication ?

hi all,
I have a single sql server, and two databases with similar
table names and structures for some 10-15 tables.
I want replicate the data from one db to another to keep
them in sync.
Can I do this using replication ?
Is replication possible between two db's of a single
server ?
I tried already it gives an error 18483 could not connect
to server cause distributor_admin is not defined as a
remote login at the server ?
thanks in advance
You can replicate to the same server. TO fix your distributor_admin problem
disable replication and re enable it.
This normally fixes this problem.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"San" <anonymous@.discussions.microsoft.com> wrote in message
news:007201c4dc8a$e52018a0$a401280a@.phx.gbl...
> hi all,
> I have a single sql server, and two databases with similar
> table names and structures for some 10-15 tables.
> I want replicate the data from one db to another to keep
> them in sync.
> Can I do this using replication ?
> Is replication possible between two db's of a single
> server ?
> I tried already it gives an error 18483 could not connect
> to server cause distributor_admin is not defined as a
> remote login at the server ?
> thanks in advance

Can There be a single Index

Can we make a Index on 2 or more tables

Table a:
Col1 int
col2 varchar

Table b:
Col1 int
col2 varchar

can you have a single index for two tables a and b on the column Col1.
Is this possible in SQL-Server.

As far as i know you can make an index only on one Table.

can the index be shared by two tables?
index y which is created is shared by
table a , table b and table c

I like the idea of a union an putting it in a veiw and having a index
but i dont want to make 100 views for a special index.

first of all i want to know wether the index is only on one table can be
on multiple tables.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!vinay bhushan <bhushanvinay@.mail.com> wrote in message news:<409b0dac$0$204$75868355@.news.frii.net>...
> Can we make a Index on 2 or more tables
> Table a:
> Col1 int
> col2 varchar
> Table b:
> Col1 int
> col2 varchar
> can you have a single index for two tables a and b on the column Col1.
> Is this possible in SQL-Server.
> As far as i know you can make an index only on one Table.
> can the index be shared by two tables?
> index y which is created is shared by
> table a , table b and table c
> I like the idea of a union an putting it in a veiw and having a index
> but i dont want to make 100 views for a special index.
> first of all i want to know wether the index is only on one table can be
> on multiple tables.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

No - an index exists on one table (or indexed view) only. If you have
a specific indexing problem, then you could post more details and
someone may be able to suggest a solution.

Simon|||vinay bhushan (bhushanvinay@.mail.com) writes:
> Can we make a Index on 2 or more tables
> Table a:
> Col1 int
> col2 varchar
> Table b:
> Col1 int
> col2 varchar
> can you have a single index for two tables a and b on the column Col1.
> Is this possible in SQL-Server.

By means of an indexed view, yes.

What an indexed means in practice is that you materialize the view,
so it still really one table under the covers. But you don't have
the chores to keep it updated. SQL Server takes care of that for you.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Is your question because of a particular problem? Are you getting a
performance issue joining the two table together?

Alicia

http://www.sqlporn.co.uk :o)

Can SubReports be from SubProjects in the same solution

We have a bunch of small single pupose reports (individual charts, tables, etc.) that we used as subreports across several larger reports. I want to sperate the subreports into their own folder on the Reporting Server and I tried to tdo this by creating a solution with subprojects and specifying different publishing folders. That seems to be going well, but when I want to add a subreport from one project to a report in another project Visual Studio doesn't seem to like this at all. It won't let me drag them to the layout surface.

Shoudl this work? Is there something different I need to be doing since I'm working with multiple projects now?

Thanks,

-p

I'm not sure it's going to work that way -- somebody else will have to confirm, not really my area -- but, if it does not, could you create a custom post-build step that copied the RDLs from their source folders to where they needed to go within each individual project?

I realize it's not ideal to replicate the files, but this would keep them up to date/synched.

>L<

|||

Actually -- one more thought -- I'm not sure if this utility will do what you want, but it looks like it might -- you could check it out...

http://www.sqldbatips.com/showarticle.asp?ID=73

>L<

|||

That tool is for taking one report and publising it multiple places. I want to do the exact opposite publish a set of subreports to one location and reuse them in another. The idea being that then people don't have to see the noise of all the subreports we can just use the nice well formatted project reports which include a variety of subreports as needed.

I don't think a post-build script would be very easy to maintain (or even possible) it seems that the subreport control can't refer to a report outside of its own namespace (i.e., its own folder).

|||

I understand what the tool does, and in essence the post-build script I suggested does the same thing, I just thought the tool would be more "automatic".

What I did *not* understand was why you wanted to do it (the "noise" of the subreports) -- I thought it was a maintenance issue. IOW, let's assume (as we both think so far) that a subreport can't refer to a report "somewhere else". Since we don't have control over that, both the tool and the postbuild at least make sure you don't have to *update* all the copies of a single subreport that you've had to "litter" all of your projects with, if changes have to be made.

I agree with you 100% about the "noise/litter" issue and I wish I had a better solution to offer.

FWIW, I have a similar problem: I wanted to set different deployment folders for different reports in a *single* project <shrug>. Yes I know you can do this in different configurations. I want to have my "Production" configuration deploy all reports to the same server, but different locations on that server. IOW, I would like to have a single reporting project that serves a whole bunch of different departments, each of which has a different reporting folder with its own set of permissions assigned to it.

I suspect the problems are connected. IOW, if it's really true that reports can only reference other reports in one folder, then if you could deploy the way I want to it would break the subreport references...

>L<

|||

Interesting note:

You can specify project interdependance, among subprojects, which implies that one subproject can reference another somehow. (otherwise, why would you need to set one project to be dependant on another)

|||

Of course one project can reference another <s>. That applies when (say) I build a report that references a custom code DLL or -- more commonly in my case -- I build a solution that has some black box code in C# and white box code in VB.

The "somehow" is by instancing classes from other DLLs...

I think what we've been saying in this thread is that, if the same applies to different *report* projects, we don't understand how it works and haven't gotten it to work the way we might have expected it to.

As an example, and one we haven't discussed in this thread yet: suppose I create a second report project in a solution and make it dependent on my first report project. When I want to create a report, can I use a shared data source from the first project? As far as I know... not. FWIW: I have tried referencing the shared datasource in a "solution folder" but that doesn't seem to do the trick.

>L<

|||

Ah, I had wondered what it was for. I have not delved much into custom code for RS yet. I am still primarily a SQL person.

The only solution I have found thus far is to use Source Safe to share my subreports between projects, ala the tool mentioned previously.

To get around the messy listings all these subreports creates, we're building a simple web front end to redirect the users to the correct report.

|||

I have one big project,

within that I have sub projects that publish the reports under them to a different folder on the report server... something like..

Accounts Receivable.

AR Reports

Accounts Payable

AP Reports

etc...

Data Sources has it's own folder.

If I have a AR subreport I want to share with other reports in that folder I simply share them.

If you are going to have a sub report span multiple projects then you need to copy the sub report from one project to the other which in vs2005 is simple as highlighting the sub report, ctrl-c then goin to your other project and press ctrl-v.

you can also have a sub folder in your main folder to hold sub reports.

lots of ways to do it.

|||

You can have one solution and multiple sub projects and have each sub project publish to a different server and/or folder, That seems to work just fine and I think as long as there was no overlap in the reports it would meet your needs.

For reports you want to put in multple locations you'd either need to use the tool you reccomended or copy the report into each project as a later poster reccomends.

I think we're all in agreement that there is no way (or we don't know how) to have a subreport be from a different project than the main report.

Anyway, I guess I'll go back to a single project and make up some clever naming schema so the main reports are at the top of the list and the subreports are at the bottom OR mark the subreports as Hidden. I've not played much with that. I hope I can do that from Visual Studio or if not that when I set it in the Web UI its not overwritten each time I deploy.

Thanks,

-p

|||

Are you letting your users see the reportserver directory as it stands?

You might want to consider doing a little webpage that reads a table that lists where the reports are.

My company has implemented this type of thing.

We have a webpage where the user selects which report group they want and then hit a button and get a list of reports that match...

It is two tables, a group table and a reports table. inthe reports table we have a friendly name for the report and the location to where it is..

works fine for us and the users NEVER see what we don't want them to.

|||

The Original querstion was not answered, but I worked around this by keeping all of the reports in one project and then marking the sub reports as Hidden on the server side.

This setting is preserved even after you re-dpeloy a report. Kind of hard to setup, but bearable.

I wish the original method of using projects with different target folders woudl work though, that would be best.

|||

If I understand your original question correctly, you can use a report from a different project as a subreport.

Let's say that your subreport is "MySubReport.rdl" in project "MyProjectA" and you want to use it from a project in "MyProjectB". Both projects have the same TargetServerURL and the same parent folder (e.g. TargetReportFolder is set to "MySolution/MyProjectA" and "MySolution/MyProjectB").

In Report Designer, drag and drop a subreport item onto your report. Set its subreport property to "./../MyProjectA/MySubReport" and manually set any parameters.

Hope this helps.

Can SubReports be from SubProjects in the same solution

We have a bunch of small single pupose reports (individual charts, tables, etc.) that we used as subreports across several larger reports. I want to sperate the subreports into their own folder on the Reporting Server and I tried to tdo this by creating a solution with subprojects and specifying different publishing folders. That seems to be going well, but when I want to add a subreport from one project to a report in another project Visual Studio doesn't seem to like this at all. It won't let me drag them to the layout surface.

Shoudl this work? Is there something different I need to be doing since I'm working with multiple projects now?

Thanks,

-p

I'm not sure it's going to work that way -- somebody else will have to confirm, not really my area -- but, if it does not, could you create a custom post-build step that copied the RDLs from their source folders to where they needed to go within each individual project?

I realize it's not ideal to replicate the files, but this would keep them up to date/synched.

>L<

|||

Actually -- one more thought -- I'm not sure if this utility will do what you want, but it looks like it might -- you could check it out...

http://www.sqldbatips.com/showarticle.asp?ID=73

>L<

|||

That tool is for taking one report and publising it multiple places. I want to do the exact opposite publish a set of subreports to one location and reuse them in another. The idea being that then people don't have to see the noise of all the subreports we can just use the nice well formatted project reports which include a variety of subreports as needed.

I don't think a post-build script would be very easy to maintain (or even possible) it seems that the subreport control can't refer to a report outside of its own namespace (i.e., its own folder).

|||

I understand what the tool does, and in essence the post-build script I suggested does the same thing, I just thought the tool would be more "automatic".

What I did *not* understand was why you wanted to do it (the "noise" of the subreports) -- I thought it was a maintenance issue. IOW, let's assume (as we both think so far) that a subreport can't refer to a report "somewhere else". Since we don't have control over that, both the tool and the postbuild at least make sure you don't have to *update* all the copies of a single subreport that you've had to "litter" all of your projects with, if changes have to be made.

I agree with you 100% about the "noise/litter" issue and I wish I had a better solution to offer.

FWIW, I have a similar problem: I wanted to set different deployment folders for different reports in a *single* project <shrug>. Yes I know you can do this in different configurations. I want to have my "Production" configuration deploy all reports to the same server, but different locations on that server. IOW, I would like to have a single reporting project that serves a whole bunch of different departments, each of which has a different reporting folder with its own set of permissions assigned to it.

I suspect the problems are connected. IOW, if it's really true that reports can only reference other reports in one folder, then if you could deploy the way I want to it would break the subreport references...

>L<

|||

Interesting note:

You can specify project interdependance, among subprojects, which implies that one subproject can reference another somehow. (otherwise, why would you need to set one project to be dependant on another)

|||

Of course one project can reference another <s>. That applies when (say) I build a report that references a custom code DLL or -- more commonly in my case -- I build a solution that has some black box code in C# and white box code in VB.

The "somehow" is by instancing classes from other DLLs...

I think what we've been saying in this thread is that, if the same applies to different *report* projects, we don't understand how it works and haven't gotten it to work the way we might have expected it to.

As an example, and one we haven't discussed in this thread yet: suppose I create a second report project in a solution and make it dependent on my first report project. When I want to create a report, can I use a shared data source from the first project? As far as I know... not. FWIW: I have tried referencing the shared datasource in a "solution folder" but that doesn't seem to do the trick.

>L<

|||

Ah, I had wondered what it was for. I have not delved much into custom code for RS yet. I am still primarily a SQL person.

The only solution I have found thus far is to use Source Safe to share my subreports between projects, ala the tool mentioned previously.

To get around the messy listings all these subreports creates, we're building a simple web front end to redirect the users to the correct report.

|||

I have one big project,

within that I have sub projects that publish the reports under them to a different folder on the report server... something like..

Accounts Receivable.

AR Reports

Accounts Payable

AP Reports

etc...

Data Sources has it's own folder.

If I have a AR subreport I want to share with other reports in that folder I simply share them.

If you are going to have a sub report span multiple projects then you need to copy the sub report from one project to the other which in vs2005 is simple as highlighting the sub report, ctrl-c then goin to your other project and press ctrl-v.

you can also have a sub folder in your main folder to hold sub reports.

lots of ways to do it.

|||

You can have one solution and multiple sub projects and have each sub project publish to a different server and/or folder, That seems to work just fine and I think as long as there was no overlap in the reports it would meet your needs.

For reports you want to put in multple locations you'd either need to use the tool you reccomended or copy the report into each project as a later poster reccomends.

I think we're all in agreement that there is no way (or we don't know how) to have a subreport be from a different project than the main report.

Anyway, I guess I'll go back to a single project and make up some clever naming schema so the main reports are at the top of the list and the subreports are at the bottom OR mark the subreports as Hidden. I've not played much with that. I hope I can do that from Visual Studio or if not that when I set it in the Web UI its not overwritten each time I deploy.

Thanks,

-p

|||

Are you letting your users see the reportserver directory as it stands?

You might want to consider doing a little webpage that reads a table that lists where the reports are.

My company has implemented this type of thing.

We have a webpage where the user selects which report group they want and then hit a button and get a list of reports that match...

It is two tables, a group table and a reports table. inthe reports table we have a friendly name for the report and the location to where it is..

works fine for us and the users NEVER see what we don't want them to.

|||

The Original querstion was not answered, but I worked around this by keeping all of the reports in one project and then marking the sub reports as Hidden on the server side.

This setting is preserved even after you re-dpeloy a report. Kind of hard to setup, but bearable.

I wish the original method of using projects with different target folders woudl work though, that would be best.

|||

If I understand your original question correctly, you can use a report from a different project as a subreport.

Let's say that your subreport is "MySubReport.rdl" in project "MyProjectA" and you want to use it from a project in "MyProjectB". Both projects have the same TargetServerURL and the same parent folder (e.g. TargetReportFolder is set to "MySolution/MyProjectA" and "MySolution/MyProjectB").

In Report Designer, drag and drop a subreport item onto your report. Set its subreport property to "./../MyProjectA/MySubReport" and manually set any parameters.

Hope this helps.

Can SQL server encrypt a single column within table?

Hello:
We have a SecUsers table that contains "Password" column, can SQL server
encrypt that column within that table? Please explain. If not, do we have
to buy a software that will encrypt single column in DB?
Thank you for your help.
Chai W.Is it possible for you not to store the password in the first place?
Try storing a hash (MD5 or SHA1 etc) of the password instead.
It has the benefit also, that the password is never transmitted over
the network (it must be hashed by the client application).
"Chai" <chai@.trs.state.il.us> wrote in message
news:OzJJtR7RDHA.3880@.tk2msftngp13.phx.gbl...
> Hello:
> We have a SecUsers table that contains "Password" column, can SQL server
> encrypt that column within that table? Please explain. If not, do we have
> to buy a software that will encrypt single column in DB?
> Thank you for your help.
> Chai W.
>

Can ONE report parameter update MULTIPLE query parameters?

Hi there,
Is it possible to have a single report parameter actually be used to update
several query parameters used by a stored procedure in my report. The stored
procedure I'm using requires 7 parameters ... but I can determine what the
values should be for the last six based on the value the user assigns to the
first one. Therefore, instead of forcing the user to assign all 7 ... I
wanted to be able to set the remaining six based on what they assign to the
first.
Is this possible? And if so, how?
Thanks - GGreg,
I haven't tried doing this with stored procedure queries, so I'll let
someone else respond how that works. I can tell you that you definitely can
do a text query and then reference your report parameter as necessary.
Worst case, if the query parameters can all be determined from one input,
you could create a wrapper SP with only 1 parameter and put the logic inside
this SP to call the existing SP with the 7 calculated values.
Have you gotten an error message trying to set all 7 SP parameters? I would
have expected this to be something pretty easy to do from the parameters
dialog.
Ted
"Greg" wrote:
> Hi there,
> Is it possible to have a single report parameter actually be used to update
> several query parameters used by a stored procedure in my report. The stored
> procedure I'm using requires 7 parameters ... but I can determine what the
> values should be for the last six based on the value the user assigns to the
> first one. Therefore, instead of forcing the user to assign all 7 ... I
> wanted to be able to set the remaining six based on what they assign to the
> first.
> Is this possible? And if so, how?
> Thanks - G|||True, another way even more straight forward. I do this all the time with
dates. Create 7 query parameters. RS automatically creates 7 report
parameters. Go to parameters tab (click on ..., parameters tab). For
parameter 2-7 you map to an expression that references the first parameter
and does whatever you want to it. If need be use code behind report to
really manipulate it. Then go into the Report->Parameters menu from layout
and delete the unneeded Report Parameters.This way is really cleaner than my
first suggestion.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ted K" <tedk@.nospam.nospam> wrote in message
news:FDADCBCC-8FAA-4004-A6F8-D3C1276C49AB@.microsoft.com...
> Greg,
> I haven't tried doing this with stored procedure queries, so I'll let
> someone else respond how that works. I can tell you that you definitely
> can
> do a text query and then reference your report parameter as necessary.
> Worst case, if the query parameters can all be determined from one input,
> you could create a wrapper SP with only 1 parameter and put the logic
> inside
> this SP to call the existing SP with the 7 calculated values.
> Have you gotten an error message trying to set all 7 SP parameters? I
> would
> have expected this to be something pretty easy to do from the parameters
> dialog.
> Ted
> "Greg" wrote:
>> Hi there,
>> Is it possible to have a single report parameter actually be used to
>> update
>> several query parameters used by a stored procedure in my report. The
>> stored
>> procedure I'm using requires 7 parameters ... but I can determine what
>> the
>> values should be for the last six based on the value the user assigns to
>> the
>> first one. Therefore, instead of forcing the user to assign all 7 ... I
>> wanted to be able to set the remaining six based on what they assign to
>> the
>> first.
>> Is this possible? And if so, how?
>> Thanks - G

Can ONE report parameter update MULTIPLE query parameters?

Hi there,
Is it possible to have a single report parameter actually be used to update
several query parameters used by a stored procedure in my report. The stored
procedure I'm using requires 7 parameters ... but I can determine what the
values should be for the last six based on the value the user assigns to the
first one. Therefore, instead of forcing the user to assign all 7 ... I
wanted to be able to set the remaining six based on what they assign to the
first.
Is this possible? And if so, how?
Thanks - GIf you can write T-SQL this is very easy. Go to the generic query designer:
Do something like this:
declare @.SQL varchar(255)
select @.SQL = 'select name as somename from ' + @.Database + '.dbo.sysobjects
where xtype = ''U'' order by name'
exec (@.SQL)
I know you are doing a stored procedure but the concept is the same. Note
that @.SQL is declared by @.Database isn't. That is because @.Database is
mapped to a report parameter. If you put the above in the generic query
designer and execute it you are prompted for the Database. If a report
parameter is not automatically created in the form design go to
Report->Parameters and create the report parameter then come back to the
Data tab, click on ..., go to Parameters tab and map the Query Parameter to
the Report Parameter.
I suggest first getting my example to work and understand what is happening
and then move on to your stored procedure.
Hope that helps.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:7E59E3DB-F8EE-40B1-9440-944935BD85B6@.microsoft.com...
> Hi there,
> Is it possible to have a single report parameter actually be used to
> update
> several query parameters used by a stored procedure in my report. The
> stored
> procedure I'm using requires 7 parameters ... but I can determine what the
> values should be for the last six based on the value the user assigns to
> the
> first one. Therefore, instead of forcing the user to assign all 7 ... I
> wanted to be able to set the remaining six based on what they assign to
> the
> first.
> Is this possible? And if so, how?
> Thanks - G