Help with Crystal Reports

Hi, ,am fairly new to crystal reports......pretty much new...

I have a simple report that is to be done......

I need to generate a report....with columns...emp_num,name,ssn

I pull up these columns thru sql server 2000 from Employee table...

The tricky part is here....

The report has to be in such a way that...

1.)First it prompts asking if u have ssn number with u ,if yes enter the ssn no., display the name and emp_num corresponding to that,

2.)IF NO...prompt the user asking for emp_num, if yes ,enter the emp_num ,display the name and ssn corresponding to that...

I've written a stored procedure for this, but this is taking only one value..while connecting to the crystal....

Plz help me on this.......I dont think you can prompt user this way
Are you calling the report from any front end?

Help with crosstab

I need to create a crosstab report. I have never done it before and need some help with it. I would appreciate any help and guidance.

I have a report that has the grouping as below

Region
Sector
Interval
Area
Crew

I need to add a crosstab report in the interval group header that will summarize the data by area and crew. I go to Insert crosstab and select Area as my column heading and Crew as my rows. Then I want to use @.PercentComplete formula as the summarized field but I don't see it in available fields and even if I create new formula from within the crosstab window I still don't see it. Any suggestions as to why I am not seeing this formula. Formula is as below

If {@.ScheduledTasks} = 0 then
"N/S"
Else If {@.TotalTasks} = 0 then
"N/D"
Else
cStr( (sum({@.TimelyCOmplete},{@.Group_CrewUnit})/(sum({@.TimelyCOmplete},{@.Group_CrewUnit}) + sum({@.MissedTasks},{@.Group_CrewUnit}) + sum({@.LateComplete},{@.Group_CrewUnit}))) * 100, 2)

Sample data for Crosstab is below

Crew Area1 Area2 Area3 %Complete
AAA 100 100 N/S 97.61
BBB 100 N/S N/S 100.00
CCC 0.00 100 N/S 81.25
DDD N/S 100 N/S 100
EEE N/S 96.87 N/D N/D
-- --- ---
%Complete 98.28 100.00 N/Danyone?

Help with conditional Count formula

I am trying to write a simple formula that will count all of the Bankrupt status that appear in my report but cannot seem to get anything to work properly.

The pseudocode formula I have attempted was

Count ({Field}) where {Field} = "BANKRUPT"

I realize Where is not an operator, but its in essence what I am looking at.
I have attempted Do While and While Do but I only get true returns which shouldn't occur as no bankrupts where present.

If someone could help me I would greatly appreciate it.Create a running total, let's call it 'BANKRUPT':

Fields to Summarize: {your_table.field}
Type of summary: Count
Evaluate: check 'use a formula' then ->x+2 button and enter your code:

{your_table.field} = "BANKRUPT"

Reset: check 'Never', if you want to summarize it for a whole report or
check 'On change of group' and select the group you would like to do this calculation for.|||Thanks for the help!

I got it working now! :)

help with charts

I can't figure out how to turn off the document mapping of my report. I
have two charts on my report and that is it. Any ideas?
Thanks in advance..
MissyAlso,
My chart is showing dates on the x axis. It won't show all dates for
some reason, any ideas?
I am using 2 parameters to get a beg date and an ending date.
aggiechick717 wrote:
> I can't figure out how to turn off the document mapping of my report. I
> have two charts on my report and that is it. Any ideas?
> Thanks in advance..
> Missy

Help with breaking out codes in a crystal report

Has anyone ever had a scenario where a calculations was showing totals but you had to break that down to the actual codes making up those totals?

For instance on a report, I have by Office by Employee totals for Hours and Wages but need a calculation of some kind to break those out to the individual codes and totals. how can I do that? See example below:

Current Report:

Naples Office Hours Dollars HrsTot $Tot

Smith, Tom 40:00 $400.00 40:00 $400.00

Desired Report:

Naples Office REG OT SICK Bonus Comm HrsTot $Tot

Smith, Tom 10:00 2:00 1:00 $20 $10 14:00 $30That information has to be available in your datasource. If so, you can change your groupings and such to get the information, but you might need a subreport if you want to display both together (depending on how the aggregations are happening).

Help with an SQL query

I'm just starting to get involved with SQL queries as a result of some
help desk software we're using and my CIO is asking for a report that I
can't sem to get.
Here's some sample data:
<pre>
refnumber assignee user problem open date
1 bob user1 text1 12/29/2005
2 sally user2 text2 12/29/2005
3 bob user3 text3 12/29/2005
4 sally user1 text4 12/29/2005
5 bob user2 text5 1/5/2006
6 bob user3 etc 1/5/2006
7 sally user5 1/5/2006
8 bob user4 1/15/2006
9 sally user5 1/15/2006
0 bob user4 1/15/2006
</pre>
And here's the report I need (average number of tickets opened per
analyst over the last 4 ws):
<pre>
wbegin wend avg tickets
12/25/2005 12/31/2005 2
1/1/2006 1/7/2006 1.5
1/8/2006 1/14/2006 0
1/15/2006 1/21/2006 1.5
</pre>
Normally I just use MS Access to create the SQL for me and then I can
tweak it as I need to but this one is a little too complex for that.
The date calculations are just throwing me completely. Anyone done
something similar?wkwork (wkwork@.movieland.com) writes:
><pre>
> refnumber assignee user problem open date
> 1 bob user1 text1 12/29/2005
> 2 sally user2 text2 12/29/2005
> 3 bob user3 text3 12/29/2005
> 4 sally user1 text4 12/29/2005
> 5 bob user2 text5 1/5/2006
> 6 bob user3 etc 1/5/2006
> 7 sally user5 1/5/2006
> 8 bob user4 1/15/2006
> 9 sally user5 1/15/2006
> 0 bob user4 1/15/2006
></pre>
> And here's the report I need (average number of tickets opened per
> analyst over the last 4 ws):
><pre>
> wbegin wend avg tickets
> 12/25/2005 12/31/2005 2
> 1/1/2006 1/7/2006 1.5
> 1/8/2006 1/14/2006 0
> 1/15/2006 1/21/2006 1.5
></pre>
I will first have to assume that you have a table with analysts
to which assignee refers. That is, I assume that if bob does not
open any ticket a certain w, and Sally submits three, the
average should be 1.5.
Next, I am introducing a table dates. This table simply has all
dates from 1990-01-01 to 2150-01-01 or whatever. You would have to
fill this table yourself.
SELECT wbegin = d.thedate, wend = dateadd(DAY, 6, d.thedate),
avg(cnt)
FROM (SELECT d.thedate, a.assignee, cnt = COUNT(t.Sunday) * 1.0
FROM thedates d
CROSS JOIN analysts a
LEFT JOIN (SELECT dateadd(day, -datepart(dw, opendate) + 1,
opendate) AS Sunday,
assignee
FROM tickets) AS t ON d.thedate = t.Sunday
AND a.analyst = t.assignee
WHERE datename(wday, d.thedate) = 'Sunday') AS x
This query is not tested.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Wow - thanks Erland. There's a lot in there that I've never seen before
so I'll have to take some time to digest it and try to fit it to my
tables.
Could I ask you to explain the innermost select:
SELECT dateadd(day, -datepart(dw, opendate) + 1,
opendate) AS Sunday,
assignee
Looks like datepart pulls the (day? day of the w?), adds 1 to it,
then dateadd subtracts that number of days from "opendate". Does that
give us the "Sunday" before the current w?
FROM tickets) AS t ON d.thedate = t.Sunday
AND a.analyst = t.assignee
Looks like it's pulling from "tickets" (my original table), calling the
whole array "t" and I'm not sure what the ON means.
Thanks and forgive my inexperience. :-)|||I just realized something else about your reply too:
The "user" and the "assignee" actually both refer to the same table
(the contact table: ctct). If I average over all names in that table,
it would be wrong. I just need to average over contacts of the
"analyst" or "administrator" type. In fact it might be easier to just
average those who have opened tickets and leave off all who have not
(in which case we can disregard the contact table and just count the
unique reference numbers in the ticket table).|||On 18 Jan 2006 14:30:50 -0800, wkwork wrote:

>I'm just starting to get involved with SQL queries as a result of some
>help desk software we're using and my CIO is asking for a report that I
>can't sem to get.
>Here's some sample data:
><pre>
>refnumber assignee user problem open date
> 1 bob user1 text1 12/29/2005
> 2 sally user2 text2 12/29/2005
> 3 bob user3 text3 12/29/2005
> 4 sally user1 text4 12/29/2005
> 5 bob user2 text5 1/5/2006
> 6 bob user3 etc 1/5/2006
> 7 sally user5 1/5/2006
> 8 bob user4 1/15/2006
> 9 sally user5 1/15/2006
> 0 bob user4 1/15/2006
></pre>
>And here's the report I need (average number of tickets opened per
>analyst over the last 4 ws):
><pre>
>wbegin wend avg tickets
>12/25/2005 12/31/2005 2
>1/1/2006 1/7/2006 1.5
>1/8/2006 1/14/2006 0
>1/15/2006 1/21/2006 1.5
></pre>
>Normally I just use MS Access to create the SQL for me and then I can
>tweak it as I need to but this one is a little too complex for that.
>The date calculations are just throwing me completely. Anyone done
>something similar?
Hi wkwork,
Here's a different solution. Note that it requires a numbers table. See
http://www.aspfaq.com/show.asp?id=2516 for how to create one.
DECLARE @.startdate datetime, @.enddate datetime
SET @.startdate = '20051225'
SET @.enddate = '20060121'
SELECT DATEADD(day, 7 * (N.Number - 1), @.startdate) AS wbbegin,
DATEADD(day, (7 * N.Number) - 1, @.startdate) AS wend,
1.0 * COUNT(t.OpenDate) / (SELECT COUNT(DISTINCT assignee)
FROM TheTable)
FROM Numbers AS N
LEFT JOIN TheTable AS t
ON t.OpenDate >= DATEADD(day, 7 * (N.Number - 1), @.startdate)
AND t.OpenDate <= DATEADD(day, (7 * N.Number) - 1, @.startdate)
WHERE N.Number >= 1
AND N.Number <= DATEDIFF (day, @.startdate, @.enddate) / 7 + 1
GROUP BY N.Number
Hugo Kornelis, SQL Server MVP|||wkwork (wkwork@.movieland.com) writes:
> Wow - thanks Erland. There's a lot in there that I've never seen before
> so I'll have to take some time to digest it and try to fit it to my
> tables.
> Could I ask you to explain the innermost select:
> SELECT dateadd(day, -datepart(dw, opendate) + 1,
> opendate) AS Sunday,
> assignee
> Looks like datepart pulls the (day? day of the w?), adds 1 to it,
> then dateadd subtracts that number of days from "opendate". Does that
> give us the "Sunday" before the current w?
The intention is that the expression moves back opendate to the first
day of the w. In the example, I have assumed that this is Sunday,
as I noted that your formatted your dates according to the rules unique
to the US, and I am under the impression that in the US the convention
is that ws start on Sundays.
If you were to issue the command SET DATEFIRT 1, to state that your
ws begin on Mondays, the query would then give you ws Monday to
Sunday.
And, yes, dw is day of w. This is covered in Books Online, for the
datetime functions.

> FROM tickets) AS t ON d.thedate = t.Sunday
> AND a.analyst = t.assignee
> Looks like it's pulling from "tickets" (my original table), calling the
> whole array "t" and I'm not sure what the ON means.
The "t" is not an array. This is a derived table. A derived table is
conceptually a temp table within the query, but it is never materialised,
and SQL Server may recast the actual computation order as long as the
result is the same. Here the derived table is used to give us a table
where all opendates are on the first day of the w.
ON is part of the join operator:
SELECT ..
FROM tblA a
JOIN tblB b ON a.col = b.col
To some extent this is the same as a WHERE clause, and the above query
can also be written as
SELECT ...
FROM tblA a, tblB b
WHERE a.col = b.col
However, the query I posted had an outer join, for outer join it matters
whether a condition is in the ON clause or in the WHERE clause.
SELECT ..
FROM tblA a
LEFT JOIN tblB b ON a.col = b.col
AND b.col2 = 1
Here it is the intention is to get all rows in A, and attach the data from
tblB where there is a match with tblA and col2 is 1, else the rows from
tblB should have NULL in all columns.
I used LEFT JOIN in the query, to cover the case that some ws may
not have any tickets opened at all.

> The "user" and the "assignee" actually both refer to the same table
> (the contact table: ctct). If I average over all names in that table,
> it would be wrong. I just need to average over contacts of the
> "analyst" or "administrator" type. In fact it might be easier to just
> average those who have opened tickets and leave off all who have not
> (in which case we can disregard the contact table and just count the
> unique reference numbers in the ticket table).
That's what you get for inclucing partial information. It sounds like
you would need to add a filter:
AND usertype IN ('analyst', 'administrator')
Finally, a standard recommendation for this type of questions is that
you include:
o CREATE TABLE statements for the tables involved.
o INSERT statements with sample data.
o The desired result given the sample.
If you do this, it is very easy to copy and past into a query window, and
you get and a reply with a query that has been tested.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with an import to SQL

Hi.

Please try to bear with this SQL "newbie" here ;)

I am trying to figure out how to import the FCC ULS report results into a
database. I believe what I need is a JOIN statement. To see the data
for yourself, visit http://s91748743.onlinehome.us/wndv619_example.txt

(I chose this query because it's near where I live. If you want to run
your own search, the URL is
http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp)

(NOTE:
The FCC webpage with the query at
http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp does just
what I am trying to do, but I'd like to keep just certain records locally
so that searches are faster and are not dependent on being on a computer
with an internet connection.)

I'll try to briefly explain what you are seeing.

The .txt file referenced above is a flat text file, pipe-delimited.

The very first field is a two-letter code denoting the table type. Some
examples are HD (Header), FR (Frequency), and EM (Emission Type).

The second field is a unique entity number.

The fourth field contains the call sign (WNDV619).

After that, the content varies significantly, based on the first field's
table type.

The FCC website lists the column headings for each table type. For
example, in a HD (Header) table, the first field is the table type, the
second is the entity number, the fourth field is the call sign, the
seventh field is some date, etc. The SQL statements (I believe there are
some syntax errors) to create all of those table types can be found at
http://wireless.fcc.gov/uls/data/do...n/pa_ddef16.sql

Further specs on the tables can be found at http://wireless.fcc.gov/cgi-
bin/wtb-datadump.pl

(This is so overwhelming...)

What I would like to do is (either in Access if possible--yeah,right--or
via a web-type query in .asp or .html) to be able to click on a call
sign, and have a new table appear with choices of table types, then to be
able to click on the table type, and have a new table appear with those
choices, etc.

Some entity numbers have more than one call sign. Some call signs have
more than one frequency (as in the example above), etc.

Is there an explanation of how to do this already? (What terminology
describes that which I am trying to accomplish? Linked tables?)

Note that with the number of table headers available (that first column,
2-letter code) there can be quite a few tables just on one entity number
and/or call sign.

Can anybody help?

--
NOTE: To reply, DELETE the obvious word in my e-mail address you need to
DELETE in order to reply.One option is to create a different format file for each table type
code. Then, using bulk insert or bcp, you can do multiple passes and
each time load a particular data file to its corresponding table.
You can also try to load the text file into a staging area. You may
then process each row in that table through a client app or from a
stored procedure.

As far as your other questions are concerned, you can check Books
Online and other documentation on primary keys, foreign keys,
normalization, etc.

TeleTech <tele_tech1212DELETE@.yahoo.com> wrote in message news:<Xns950087502A22Bteletech1212@.207.69.154.205>...
> Hi.
> Please try to bear with this SQL "newbie" here ;)
> I am trying to figure out how to import the FCC ULS report results into a
> database. I believe what I need is a JOIN statement. To see the data
> for yourself, visit http://s91748743.onlinehome.us/wndv619_example.txt
> (I chose this query because it's near where I live. If you want to run
> your own search, the URL is
> http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp)
> (NOTE:
> The FCC webpage with the query at
> http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp does just
> what I am trying to do, but I'd like to keep just certain records locally
> so that searches are faster and are not dependent on being on a computer
> with an internet connection.)
> I'll try to briefly explain what you are seeing.
> The .txt file referenced above is a flat text file, pipe-delimited.
> The very first field is a two-letter code denoting the table type. Some
> examples are HD (Header), FR (Frequency), and EM (Emission Type).
> The second field is a unique entity number.
> The fourth field contains the call sign (WNDV619).
> After that, the content varies significantly, based on the first field's
> table type.
> The FCC website lists the column headings for each table type. For
> example, in a HD (Header) table, the first field is the table type, the
> second is the entity number, the fourth field is the call sign, the
> seventh field is some date, etc. The SQL statements (I believe there are
> some syntax errors) to create all of those table types can be found at
> http://wireless.fcc.gov/uls/data/do...n/pa_ddef16.sql
> Further specs on the tables can be found at http://wireless.fcc.gov/cgi-
> bin/wtb-datadump.pl
> (This is so overwhelming...)
> What I would like to do is (either in Access if possible--yeah,right--or
> via a web-type query in .asp or .html) to be able to click on a call
> sign, and have a new table appear with choices of table types, then to be
> able to click on the table type, and have a new table appear with those
> choices, etc.
> Some entity numbers have more than one call sign. Some call signs have
> more than one frequency (as in the example above), etc.
> Is there an explanation of how to do this already? (What terminology
> describes that which I am trying to accomplish? Linked tables?)
> Note that with the number of table headers available (that first column,
> 2-letter code) there can be quite a few tables just on one entity number
> and/or call sign.
> Can anybody help?|||TeleTech (tele_tech1212DELETE@.yahoo.com) writes:
> I am trying to figure out how to import the FCC ULS report results into a
> database. I believe what I need is a JOIN statement. To see the data
> for yourself, visit http://s91748743.onlinehome.us/wndv619_example.txt

I'm afraid that importing this data is not entirely trivial matter.
The way I would do it would be to write a Perl script that read
one row at a time, splitted the data into columns, looked at the first
field to find the target table.

The reason I would use Perl is simply because this is the client language
I know best. You should be able to this in VBscript, C++ or whatever.

I don't know if DTS has some built-in support for this kind of file, but
I doubt. But since I don't know DTS, don't take it from me. The friendly
folks in microsoft.public.sqlserver.dts would know for sure.

> What I would like to do is (either in Access if possible--yeah,right--or
> via a web-type query in .asp or .html) to be able to click on a call
> sign, and have a new table appear with choices of table types, then to be
> able to click on the table type, and have a new table appear with those
> choices, etc.

I'm not really sure what sort of help you are asking for here, but it
sounds like something which is more apt for comp.databases.ms-access or
some web-programming newsgroup.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Help with addition to Missing date query

Hi Everyone,
I hope someone can help out some. I have a report that displays the days our
clients missed there sessions. That was working great, and I've got some new
requirements now for the report. They needed to store the number of days the
client is suppose to attend, so I created a field called ClientSchedule that
stores a value 1-7 (days as week). The thing that has got me now is that they
also want the report to represent this new field. So the report needs to not
include the client in the report if they have attended for there scheduled
days for that week. But the kicker is that the report is based on a date
range and that range is variable, it could be set to one week, two weeks, 1
1/2 weeks, ect.. I have a query that partially works, but I could not figure
out how to work out the missed days within a week with a variable date range.
Somehow it needs to compare the number of days the client is scheduled
(ClientSchedule) with the number of days attended for a week, if days
attended is lower than ClientSchedule for that week, include the missed days
in the report(including all weeks that may be part of the Date range the user
selected).
The base query I have is as follows:
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@.PatientId varchar(20),
@.CounselorId int,
@.FromDate smalldatetime,
@.ToDate smalldatetime
AS
SET NOCOUNT ON
DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
Delete from RangeTest
if @.PatientId = -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate
Print 'First Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
Print 'Second Set'
end
end
if @.PatientId > -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate and PatientId = @.PatientId
Print 'Third Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
and PatientId = @.PatientId
Print 'Last Set'
end
end
Delete from Range
if @.PatientId = -1
begin
Insert into Range
SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance) Test ON 1
= 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.PatientId > -1
begin
Insert into Range
SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where
Patientid = @.PatientId) Test ON 1 = 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.CounselorId = -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID = dbo.nf_GetLastDayVisited.PatientId
where PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
if @.CounselorId <> -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID = dbo.nf_GetLastDayVisited.PatientId
Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
GO
I have worked on it for several hours and not sure where to take it from
here. Do I have to start to use a curser, or is there a way to do this with
just a modification to the query I have so far. Thanks for any suggestions.
MichaelHi Michael
Have you thought of using a calendar table for this ?
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
John
"Michael" wrote:
> Hi Everyone,
> I hope someone can help out some. I have a report that displays the days our
> clients missed there sessions. That was working great, and I've got some new
> requirements now for the report. They needed to store the number of days the
> client is suppose to attend, so I created a field called ClientSchedule that
> stores a value 1-7 (days as week). The thing that has got me now is that they
> also want the report to represent this new field. So the report needs to not
> include the client in the report if they have attended for there scheduled
> days for that week. But the kicker is that the report is based on a date
> range and that range is variable, it could be set to one week, two weeks, 1
> 1/2 weeks, ect.. I have a query that partially works, but I could not figure
> out how to work out the missed days within a week with a variable date range.
> Somehow it needs to compare the number of days the client is scheduled
> (ClientSchedule) with the number of days attended for a week, if days
> attended is lower than ClientSchedule for that week, include the missed days
> in the report(including all weeks that may be part of the Date range the user
> selected).
> The base query I have is as follows:
> CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
> @.PatientId varchar(20),
> @.CounselorId int,
> @.FromDate smalldatetime,
> @.ToDate smalldatetime
> AS
> SET NOCOUNT ON
> DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
> Delete from RangeTest
> if @.PatientId = -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate
> Print 'First Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> Print 'Second Set'
> end
> end
> if @.PatientId > -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate and PatientId = @.PatientId
> Print 'Third Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> and PatientId = @.PatientId
> Print 'Last Set'
> end
> end
>
> Delete from Range
> if @.PatientId = -1
> begin
> Insert into Range
> SELECT Fulldates.*
> FROM (
> SELECT Test.CustId, R.range_date
> FROM (
> select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
> from (select 0 b1 union select 1 b1) t1
> cross join (select 0 b2 union select 2 b2) t2
> cross join (select 0 b3 union select 4 b3) t3
> cross join (select 0 b4 union select 8 b4) t4
> cross join (select 0 b5 union select 16 b5) t5
> cross join (select 0 b6 union select 32 b6) t6
> cross join (select 0 b7 union select 64 b7) t7
> cross join (select 0 b8 union select 128 b8) t8
> cross join (select 0 b9 union select 256 b9) t9
> cross join (select 0 b10 union select 512 b10) t10
> cross join (select 0 b11 union select 1024 b11) t11
> cross join (select 0 b12 union select 2048 b12) t12
> where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
> FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance) Test ON 1
> = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.PatientId > -1
> begin
> Insert into Range
> SELECT Fulldates.*
> FROM (
> SELECT Test.CustId, R.range_date
> FROM (
> select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
> from (select 0 b1 union select 1 b1) t1
> cross join (select 0 b2 union select 2 b2) t2
> cross join (select 0 b3 union select 4 b3) t3
> cross join (select 0 b4 union select 8 b4) t4
> cross join (select 0 b5 union select 16 b5) t5
> cross join (select 0 b6 union select 32 b6) t6
> cross join (select 0 b7 union select 64 b7) t7
> cross join (select 0 b8 union select 128 b8) t8
> cross join (select 0 b9 union select 256 b9) t9
> cross join (select 0 b10 union select 512 b10) t10
> cross join (select 0 b11 union select 1024 b11) t11
> cross join (select 0 b12 union select 2048 b12) t12
> where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @.ToDate) r
> FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where
> Patientid = @.PatientId) Test ON 1 = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.CounselorId = -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID => dbo.nf_GetLastDayVisited.PatientId
> where PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> if @.CounselorId <> -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID => dbo.nf_GetLastDayVisited.PatientId
> Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> GO
> I have worked on it for several hours and not sure where to take it from
> here. Do I have to start to use a curser, or is there a way to do this with
> just a modification to the query I have so far. Thanks for any suggestions.
> Michael
>

Help with addition to Missing date query

Hi Everyone,
I hope someone can help out some. I have a report that displays the days our
clients missed there sessions. That was working great, and I've got some new
requirements now for the report. They needed to store the number of days the
client is suppose to attend, so I created a field called ClientSchedule that
stores a value 1-7 (days as week). The thing that has got me now is that the
y
also want the report to represent this new field. So the report needs to not
include the client in the report if they have attended for there scheduled
days for that week. But the kicker is that the report is based on a date
range and that range is variable, it could be set to one week, two weeks, 1
1/2 weeks, ect.. I have a query that partially works, but I could not figure
out how to work out the missed days within a week with a variable date range
.
Somehow it needs to compare the number of days the client is scheduled
(ClientSchedule) with the number of days attended for a week, if days
attended is lower than ClientSchedule for that week, include the missed days
in the report(including all weeks that may be part of the Date range the use
r
selected).
The base query I have is as follows:
CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
@.PatientId varchar(20),
@.CounselorId int,
@.FromDate smalldatetime,
@.ToDate smalldatetime
AS
SET NOCOUNT ON
DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
Delete from RangeTest
if @.PatientId = -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate
Print 'First Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
Print 'Second Set'
end
end
if @.PatientId > -1
begin
if @.FromDate = @.ToDate
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate = @.ToDate and PatientId = @.PatientId
Print 'Third Set'
end
else
begin
Insert into RangeTest Select PatientID, AttendDate from Attendance
where AttendDate between @.FromDate and @.ToDate
and PatientId = @.PatientId
Print 'Last Set'
end
end
Delete from Range
if @.PatientId = -1
begin
Insert into Range
SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10
+b11+b12 <= @.ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance) Test ON 1
= 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.PatientId > -1
begin
Insert into Range
SELECT Fulldates.*
FROM (
SELECT Test.CustId, R.range_date
FROM (
select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10
+b11+b12 <= @.ToDate) r
FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where
Patientid = @.PatientId) Test ON 1 = 1
) FullDates
LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
FullDates.Range_Date = Test.VisitDate
WHERE Test.CustId IS NULL
ORDER BY FullDates.CustId, FullDates.range_date
end
if @.CounselorId = -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID =
dbo.nf_GetLastDayVisited.PatientId
where PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
if @.CounselorId <> -1
begin
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName,
dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
PATIENTS.FirstName as pName,
Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
COUNSELORS.C_LastName,
dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID =
dbo.nf_GetLastDayVisited.PatientId
Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
ORDER BY pName
end
GO
I have worked on it for several hours and not sure where to take it from
here. Do I have to start to use a curser, or is there a way to do this with
just a modification to the query I have so far. Thanks for any suggestions.
MichaelHi Michael
Have you thought of using a calendar table for this ?
http://sqlserver2000.databases.aspf...ndar-table.html
John
"Michael" wrote:

> Hi Everyone,
> I hope someone can help out some. I have a report that displays the days o
ur
> clients missed there sessions. That was working great, and I've got some n
ew
> requirements now for the report. They needed to store the number of days t
he
> client is suppose to attend, so I created a field called ClientSchedule th
at
> stores a value 1-7 (days as week). The thing that has got me now is that t
hey
> also want the report to represent this new field. So the report needs to n
ot
> include the client in the report if they have attended for there scheduled
> days for that week. But the kicker is that the report is based on a date
> range and that range is variable, it could be set to one week, two weeks,
1
> 1/2 weeks, ect.. I have a query that partially works, but I could not figu
re
> out how to work out the missed days within a week with a variable date ran
ge.
> Somehow it needs to compare the number of days the client is scheduled
> (ClientSchedule) with the number of days attended for a week, if days
> attended is lower than ClientSchedule for that week, include the missed da
ys
> in the report(including all weeks that may be part of the Date range the u
ser
> selected).
> The base query I have is as follows:
> CREATE PROCEDURE [dbo].[nf_GetAbsentClients]
> @.PatientId varchar(20),
> @.CounselorId int,
> @.FromDate smalldatetime,
> @.ToDate smalldatetime
> AS
> SET NOCOUNT ON
> DECLARE @.Test TABLE (custId varchar(20), VisitDate DateTime)
> Delete from RangeTest
> if @.PatientId = -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate
> Print 'First Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> Print 'Second Set'
> end
> end
> if @.PatientId > -1
> begin
> if @.FromDate = @.ToDate
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate = @.ToDate and PatientId = @.PatientId
> Print 'Third Set'
> end
> else
> begin
> Insert into RangeTest Select PatientID, AttendDate from Attendance
> where AttendDate between @.FromDate and @.ToDate
> and PatientId = @.PatientId
> Print 'Last Set'
> end
> end
>
> Delete from Range
> if @.PatientId = -1
> begin
> Insert into Range
> SELECT Fulldates.*
> FROM (
> SELECT Test.CustId, R.range_date
> FROM (
> select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
> from (select 0 b1 union select 1 b1) t1
> cross join (select 0 b2 union select 2 b2) t2
> cross join (select 0 b3 union select 4 b3) t3
> cross join (select 0 b4 union select 8 b4) t4
> cross join (select 0 b5 union select 16 b5) t5
> cross join (select 0 b6 union select 32 b6) t6
> cross join (select 0 b7 union select 64 b7) t7
> cross join (select 0 b8 union select 128 b8) t8
> cross join (select 0 b9 union select 256 b9) t9
> cross join (select 0 b10 union select 512 b10) t10
> cross join (select 0 b11 union select 1024 b11) t11
> cross join (select 0 b12 union select 2048 b12) t12
> where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10
+b11+b12 <= @.ToDate) r
> FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance) Test ON
1
> = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.PatientId > -1
> begin
> Insert into Range
> SELECT Fulldates.*
> FROM (
> SELECT Test.CustId, R.range_date
> FROM (
> select (@.FromDate + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) AS range_date
> from (select 0 b1 union select 1 b1) t1
> cross join (select 0 b2 union select 2 b2) t2
> cross join (select 0 b3 union select 4 b3) t3
> cross join (select 0 b4 union select 8 b4) t4
> cross join (select 0 b5 union select 16 b5) t5
> cross join (select 0 b6 union select 32 b6) t6
> cross join (select 0 b7 union select 64 b7) t7
> cross join (select 0 b8 union select 128 b8) t8
> cross join (select 0 b9 union select 256 b9) t9
> cross join (select 0 b10 union select 512 b10) t10
> cross join (select 0 b11 union select 1024 b11) t11
> cross join (select 0 b12 union select 2048 b12) t12
> where @.FromDate+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10
+b11+b12 <= @.ToDate) r
> FULL join (SELECT DISTINCT PatientId as CustId FROM Attendance where
> Patientid = @.PatientId) Test ON 1 = 1
> ) FullDates
> LEFT JOIN RangeTest Test ON FullDates.CustId = Test.CustId AND
> FullDates.Range_Date = Test.VisitDate
> WHERE Test.CustId IS NULL
> ORDER BY FullDates.CustId, FullDates.range_date
> end
> if @.CounselorId = -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstNam
e,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId
)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID =
> dbo.nf_GetLastDayVisited.PatientId
> where PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> if @.CounselorId <> -1
> begin
> SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstNam
e,
> dbo.nf_GetLastDayVisited.LastDayVisited, PATIENTS.LastName + ', ' +
> PATIENTS.FirstName as pName,
> Range.AttendDate, COUNSELORS.C_ID, COUNSELORS.C_FirstName,
> COUNSELORS.C_LastName,
> dbo.nf_FormatPhone(PATIENTS.HomePhone) as HomePhone
> FROM ((PATIENTS RIGHT JOIN Range ON PATIENTS.PatientID = Range.PatientId
)
> LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID)
> LEFT JOIN dbo.nf_GetLastDayVisited() ON PATIENTS.PatientID =
> dbo.nf_GetLastDayVisited.PatientId
> Where COUNSELORS.C_ID = @.CounselorId and PATIENTS.ActiveClient = 1
> and Range.AttendDate >= dbo.nf_GetLastDayVisited.DateOfAdmission
> ORDER BY pName
> end
> GO
> I have worked on it for several hours and not sure where to take it from
> here. Do I have to start to use a curser, or is there a way to do this wit
h
> just a modification to the query I have so far. Thanks for any suggestions
.
> Michael
>sql

Help with a SQL Query using temp tables

Hi All,

I have 4 temporary tables that hold criteria selected through a report wizard.
I've created a SQL statement and used the four tables in my WHERE/ AND clauses but the results retuned are not being filtered correctly.

Would somebody be kind enough to help me out please.

To briefly summarise, I have created a SQL statement that returns all rows in my recordset, I now need to implement some additional SQL to filter the recordset using my temporary tables, which contain the filters as follows:

(1) Temp table 1 (##tblTempAssetFilt) is mandatory and will always contain at least one row.
(2) Temp table 2 (##tblTempRepairTypeFilter) is optional and may never contain any rows. If this is the case then I have no reason to filter my resultset against this table.
(3) Temp table 3 (##tblTempRepairFilter) / Temp table 4 (##tblTempRepairElementFilter) are both optional, only one of these tables will contain data at one time. Again, as an optional filter the tables may never contain rows, and thus need to be ignored.

I have the following SQL, can somebody tell me how I would go about filtering the recordset using the temporary tables. The creation of the temporary tables occurs at the beginning so will always exist even when no rows have been inserted.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
WHERE ActualWork.intAssetID IN (Select intAssetID From ##tblTempAssetFilter) AND Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter)
AND Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter)
AND Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter)

Any filtering must be based on the recordset filtered by temp table 1, which is a mandatory filter. Rows will always exist in this temp table.

Please help, not having much joy with this. Many thanks.Basically all I did was move your manditory temp table out of the where clause and add ORs to the where so that if a table was empty you would evaluate to TRUE for that table. Maybe not the best solution but this should get you going.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
join ##tblTempAssetFilter [TempAssetFilter] on ActualWork.intAssetID = TempAssetFilter.intAssetID
WHERE (Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter) or not exists(select * from ##tblTempRepairTypeFilter)
AND (Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter) or not exists(select * from ##tblTempRepairFilter)
AND (Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter) or not exists(select * from ##tblTempRepairElementFilter)|||Paul,

Thank you for your reply, this has indeed fixed my problem. I am now returning a recordset with the desired results using my temporary tables.

Thanks again.|||Cool!

Here is a twist, if you do not need global temp tables I would switch to using table variables, there are some restrictions to using table variables but they can be much faster.

Also, if you can pre test the emptiness of your tables and store the reslts in a bit variable you can speed things up even more. As it is you are testing for empty temp tables each time you move to a new row.

Help with a Problem:Reporting Services Session Management

Ok, this is a two fold problemSharepoint & Reporting Services)
I have a Report Image that I call from Reporting Services and I render it in
a Sharepoint Webpart, I need to manage the Session from reporting sevices as
when I try to manage it from the Sharepoint Web Part, Reporting Services says
it can not find the image/data, as the report session has changed, and has
new data, I need to make the Report Image Session able to maintain the prior
session.
I was wondering if there was a way to capture the session variable from
Reporting Services and keep it in the webpart? or capture it in someway to to
maintain the sessionon the data call.
Any one out there know how to capture this or have run into this problem, or
might know a solution?
If you are using the Reporting Services Web Service to retrieve the report by calling the Render method on the ReportingService object in your web part, you can capture the Report SessionId from the SessionHeaderValue.SessionId property on the ReportingSe
rvice object after the call and use it later when you are retrieving the image via the RenderStream method.
see: http://msdn.microsoft.com/library/de...ce_lz_6x0z.asp
Currently I am using a Web Part to display the report and a seperate .aspx file to render the images in the report.
I call the Render Method of the Web Service with the and set the deviceinfo for HTMLFragment(true),StreamRoot(/myVirtualDirectory/ImageRender.aspx?sessionid=&reportname=xx¶m1=y y&streamid=)
After the call the output html will contain image tags with src attribute equal StreamRoot concatinated with the streamId (i.e. streamid has to be the last item in the query string)
I populate the sessionid in the above image url with a string replace.
CODE:
public static string RetrieveReport(string reportPath, ParameterValue[] parameterValues, string sharePointHelperUrl, bool useDefaultCredentials)
{
string outputHtml=string.Empty;
ReportingService rs = new ReportingService();
rs.Credentials = GetCredentials(useDefaultCredentials);
ItemTypeEnum reportType = rs.GetItemType(reportPath);
if(reportType==ItemTypeEnum.Report)
{
// Render arguments
byte[] result = null;
// Prepare Render method's parameters.
/*
* The devInfo parameter is set to hide the tool bar, hide the parameters, return the fragment Html
* and prefix all images with the Url to our helper aspx file with the query string holding all data
* required to retrieve the correct image from the Reporting Service
* */
string imageUrl = System.Web.HttpUtility.HtmlEncode(
string.Format("{0}/getreportimages.aspx?{1}={2}&{3}={4}&{5}={6}&{7}=" ,
sharePointHelperUrl,
"RSSessionID",
"",
"ReportPath",
reportPath,
"Parameters",
getReportParametersForQueryString(parameterValues) ,
"StreamID"
)
);
string devInfo = @."<DeviceInfo><Toolbar>False</Toolbar><StreamRoot>" + imageUrl + "</StreamRoot><Parameters>False</Parameters><HTMLFragment>True</HTMLFragment></DeviceInfo>";
string format = "HTML4.0";
string historyID = null;
DataSourceCredentials[] credentials = null;
string showHideToggle = null;
string encoding;
string mimeType;
Warning[] warnings = null;
ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;
rs.SessionHeaderValue = new SessionHeader();
result = rs.Render(reportPath, format, historyID, devInfo, parameterValues, credentials, showHideToggle, out encoding, out mimeType, out reportHistoryParameters, out warnings, out streamIDs);
outputHtml = System.Text.Encoding.UTF8.GetString(result, 0,result.Length);
/*
* The SessionHeaderValue.SessionId is not available until the Render method of the Reporting Service
* has been called. This is required when calling the RenderStream method, this method is called by the
* supporting aspx page. This page is requested by the client to render images. Replace Query String
* "RSSessionID" with "RSSessionID" and the SessionHeaderValue.SessionId for each image in the rendered
* output.
* */
outputHtml = outputHtml.Replace("RSSessionID=", "RSSessionID=" + rs.SessionHeaderValue.SessionId);
}
return outputHtml;
}
NOTE:The /myVirtualDirectory/ has to excluded from SharePoint so it is processed as a normal aspx.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Help with a CURSOR

Here's my dilema. I'm trying to create a SQL script that will report back
to me every table that has a different row count from one database to
another. I believe the best way to accomplish this is using a CURSOR to
fetch through the tables in the DB and print those where the record count
differs. Here is what I'm trying to do:
DECLARE @.Table nvarchar(40)
DECLARE Table_CURSOR CURSOR FOR
select sysobjects.name
from sysobjects, syscolumns, systypes
where syscolumns.id = sysobjects.id and syscolumns.xtype
= systypes.xtype
and sysobjects.xtype = 'U'
group by sysobjects.name
order by sysobjects.name
OPEN Table_CURSOR
FETCH NEXT FROM Table_CURSOR
INTO @.Table
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM Database1..[@.Table] WHERE ID NOT IN (SELECT ID
FROM Database2..[@.Table])) > 1
BEGIN
Print @.Table
END
FETCH NEXT FROM Table_CURSOR
INTO @.Table
END
CLOSE Table_CURSOR
DEALLOCATE Table_CURSOR
However, I get errors on "Invalid object name 'Database1..@.Table' and
"Invalid object name 'Database2..@.Table'.
I've tried without the bracket signs around [@.Table] as well, but then get
an error "Incorrect Syntax near '@.Table'.
Any ideas? Is there an easier way to achieve this? Thanks in advance,
JasonThe problem is that you cannot substitute variables for object names (like
databases or tables). However, you can execute dynamic sql. Here is a good
article. Also, useful is the undocumented sp_msforeachtable procedure.
http://www.databasejournal.com/feat...cle.php/1438931
"Jason" <jason@.nospam.com> wrote in message
news:OTGTuNX5FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Here's my dilema. I'm trying to create a SQL script that will report back
> to me every table that has a different row count from one database to
> another. I believe the best way to accomplish this is using a CURSOR to
> fetch through the tables in the DB and print those where the record count
> differs. Here is what I'm trying to do:
> DECLARE @.Table nvarchar(40)
> DECLARE Table_CURSOR CURSOR FOR
> select sysobjects.name
> from sysobjects, syscolumns, systypes
> where syscolumns.id = sysobjects.id and syscolumns.xtype
> = systypes.xtype
> and sysobjects.xtype = 'U'
> group by sysobjects.name
> order by sysobjects.name
> OPEN Table_CURSOR
> FETCH NEXT FROM Table_CURSOR
> INTO @.Table
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(*) FROM Database1..[@.Table] WHERE ID NOT IN (SELECT ID
> FROM Database2..[@.Table])) > 1
> BEGIN
> Print @.Table
> END
> FETCH NEXT FROM Table_CURSOR
> INTO @.Table
> END
> CLOSE Table_CURSOR
> DEALLOCATE Table_CURSOR
> However, I get errors on "Invalid object name 'Database1..@.Table' and
> "Invalid object name 'Database2..@.Table'.
> I've tried without the bracket signs around [@.Table] as well, but then get
> an error "Incorrect Syntax near '@.Table'.
> Any ideas? Is there an easier way to achieve this? Thanks in advance,
> Jason
>

Help with "like" or "wildcard" during a parameter entry.

Disclaimer - I'm a novice when it comes to Crystal Reports as well as VB, and I am using Crystal Reports XI.

My trouble - I have a report that uses a parameter field to show a history of a whatever number is entered into the parameter when the report is accessed. I would like to add a "like" or "wildcard" to this parameter filed.

Example:

Parameter entered = ED35A100

The results are correct I get a history of the number ED35A100

However, I would like to enter the parameter = ED35*

and get results like ED35A100, ED35C100, ED35A200, etc.

Any help on how to accomplish this would be greatly appreciated.maybe this will work...lookslike({Fieldname},"*{?Parameter}*") then go to Report...Select Expert then choose the Fieldname then in the drop down key choose Is True

Help w/aggregate function in Matrix

On a report we have a matrix. The data cell has the following expression:
=iif(Fields!Score.Value=0, "", Fields!Score.Value)
When we run the report, we get the following warning:
The value expression for the textbox 'Score' references a field
outside an aggregate function. Value expressions in matrix cells should be
aggregates, to allow for subtotaling.
What does this mean and how can I resolve it?
The goal it to suppress the display of zero (0). We have tried setting the
format of the data cell to be "#", but the zero is still displayed. So we
have been using expressions like the above to achieve this.
The user creating this report is using the stand-alone C# IDE with Reporting
Services. This warning prevents them from previewing the report. Another
user using VS.NET 2003 is able to preview the report despite the warning.
The report renders on our test reporting server. If we need to just ignore
the warning, how can we get the user using C# to be able to preview the
report?
Thanks,
ChrisMatrix cells are always in the scope of two groupings and you could have
multiple data rows which match the group instance values. Therefore, you
should always use aggregate functions when referencing fields in a matrix
cell (hence, a processing warning gets generated).
If you don't use an explicit aggregate function in the matrix cell, we would
implicitly use the first row's field value. I believe you actually don't
want just the first value, but rather the sum - so you should change the
expression to:
=iif(Sum(Fields!Score.Value)=0, "", Sum(Fields!Score.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
In your case, I believe you want
"Chris Walls" <chwalls@.community.nospam> wrote in message
news:OVqzX42OFHA.1500@.TK2MSFTNGP09.phx.gbl...
> On a report we have a matrix. The data cell has the following expression:
>
> =iif(Fields!Score.Value=0, "", Fields!Score.Value)
>
> When we run the report, we get the following warning:
>
> The value expression for the textbox 'Score' references a field
> outside an aggregate function. Value expressions in matrix cells should
> be aggregates, to allow for subtotaling.
>
>
> What does this mean and how can I resolve it?
>
>
> The goal it to suppress the display of zero (0). We have tried setting
> the format of the data cell to be "#", but the zero is still displayed.
> So we have been using expressions like the above to achieve this.
>
>
> The user creating this report is using the stand-alone C# IDE with
> Reporting Services. This warning prevents them from previewing the
> report. Another user using VS.NET 2003 is able to preview the report
> despite the warning. The report renders on our test reporting server. If
> we need to just ignore the warning, how can we get the user using C# to be
> able to preview the report?
>
>
> Thanks,
> Chris
>
>

Help using the formulas in report builder

I am really new to this. If anyone knows a place to go to learn about the syntax of the formulas contained in the report builder and filters I would appreciate it.

Also I am studying from a book from apress and they have some junk to download. I am doing this on my home computer and was wondering if MS had a 30 day trial version of pro sql server 2005 that I could use while at home. My work has it instalkled and I am just remoting in but do not want to dl apresses examples on their server. Thanks Travis

The primary documentation for Report Builder is (currently) only available from within the application, using the Help item on the main menu.

Trial versions of SQL Server can be obtained here: http://www.microsoft.com/sql/downloads/trial-software.mspx.

Hope this helps!

Help using a value from one dataset to lookup a value from a second dataset

Hi All,

I'm quite new to SSRS (and .net development as well) so any help would be useful. My problem is this: I'm running a report from a Sybase ASE datasource to pull data into a table control. One of the fields I'm pulling in is called assignment group. Now, in the same report output I also need to display who the manager of that group is... unfortunately this information is not stored in the same database. We store the manager information in a completely seperate Oracle database. So, I need some method of 'linking' the data from my Sybase dataset to a table in Oracle and I can't quite figure out how to go about it. Every record would have a manager and there could be 10k records returned so I need to be carefull about performance issues.

I was starting to go down the path of creating a .net dataset in a custom assembly which would be filled via a query to the Oracle database. What I was thinking is to create and fill a dataset with my lookup data from Oracle then, in the report, call a function from the table control which would search through the dataset in memory and return the actual manager name.

Am I on the right track or am I overcomplicating the solution? Will this be too inefficient from a performance perspective? Would a subreport be a more efficient solution?

There are a couple of solutions for you:

1) you can write a custom data extension that does the magic merge under the covers. A custom assembly won't help here because it operates on one row at a time - if you have a reasonable nuumber of rows, it might work, but with lots of rows it will probably be inefficient.

2) you can use the Linked server feature of SQL Server. This allows you to build a table/view that uses ODBC connections to other data bases to retrieve data. It has pretty reasonable performance and you don't have to create and maintain custom code. Downside, is you need to add a SQL database in addition to your existing Oracle and Sybase ones :-).

Hope that helps,

-Lukasz

Help Using a field value in a url jump statement

I have a report that uses the table control, the last column has id's for
dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
any of the other Columns in a row I would like to be able to grab the id from
the last column in the row and insert it into the expression for the Jump to
url feature.
this url works fine
="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
"535d58cc-a1b3-da11-9864-001320020c86" & "');"
but this one does not allow for the pointer hand to show up when cusror is
over the row
="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
Fields!res_dealCompanyid.Value & "');"
Do I need to convert Fields!res_dealCompanyid.Value to a string?
Can I assign it to a variable and then use it in the Jump to URL?
I do not know what else to try
MikeI got this to work, my problem was that the dealCompanyid value was not a
string so I had to do a Fields!res_dealcompanyid.Value.tostring() then it
worked fine.
Parameters!CRMServer.Value just holds a Server info
="javascript:void(window.open('http://" & Parameters!CRMServer.Value &
"/EE2/DealCompany.aspx?id=" & Fields!res_dealcompanyid.Value.tostring() & "',
'_blank'))"
"Hotwheels" wrote:
> I have a report that uses the table control, the last column has id's for
> dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
> any of the other Columns in a row I would like to be able to grab the id from
> the last column in the row and insert it into the expression for the Jump to
> url feature.
> this url works fine
> ="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
> "535d58cc-a1b3-da11-9864-001320020c86" & "');"
> but this one does not allow for the pointer hand to show up when cusror is
> over the row
> ="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
> Fields!res_dealCompanyid.Value & "');"
> Do I need to convert Fields!res_dealCompanyid.Value to a string?
> Can I assign it to a variable and then use it in the Jump to URL?
> I do not know what else to try
> Mike

Help understanding generated MDX WHERE clause

When your report datasource is a cube, MDX is generated when you use the design view. In the MDX editor the generated MDX can be viewed. Using parameters I always get a where clause with code like the following:

IIF( STRTOSET(@.OrgLevelname, CONSTRAINED).Count = 1, STRTOSET(@.OrgLevelname, CONSTRAINED), [Organisation].[Level 2 name].currentmember )

I like to understand what is generated. Is there something I can read on the generated WHERE clause (I do understand the generated SELECT and FROM clauses)? Or can someone shed a light on it?

Why does the MDX need to branch on 'Count = 1' In what way does the result slice my data when Count = 1 or when Count <> 1?

Thanks,
Henk

Really no MDX guru around who can explain this to me?|||

Henk,

I would suggest posting your MDX question in the SQL Server Analysis Services forum at this url:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

|||Thanks, I'll do that.

Help setting up datasets

I have a report that needs to show three different counts in the first 3
columns. This will be on a group header line. The detail for the group
needs to show the data that is used to get the third count. My report footer
needs show a sum of the three counts.
I have two different ideas on how do accomplish this but run into problems
with each. If I use two datasets I can only reference the counts in an
aggregate when my table dataset is the details. I can not (or donâ't know
how) to set an expression to get the value from a dataset where the keys are
equal. My other solution is a single dataset with the three counts included
in each row. In the group heading I can do =First(Fields!count1,value) but I
can not sum that way because I can not perform an aggregate on an aggregate.
Anyone have any ideas on this?The "easiest" (or should I say "only") way I've found to pull off
rather complex aggregation for RS is to use a stored procedure to come
up with summaries in a temp table(s), and then run my queries against
those temp table (or tables) to give me a tabular output that is as
close as possible to what my report needs to look like.
On Mar 28, 4:53 pm, simmonsj_98 <simmons...@.discussions.microsoft.com>
wrote:
> I have a report that needs to show three different counts in the first 3
> columns. This will be on a group header line. The detail for the group
> needs to show the data that is used to get the third count. My report footer
> needs show a sum of the three counts.
> I have two different ideas on how do accomplish this but run into problems
> with each. If I use two datasets I can only reference the counts in an
> aggregate when my table dataset is the details. I can not (or don't know
> how) to set an expression to get the value from a dataset where the keys are
> equal. My other solution is a single dataset with the three counts included
> in each row. In the group heading I can do =First(Fields!count1,value) but I
> can not sum that way because I can not perform an aggregate on an aggregate.
> Anyone have any ideas on this?

Help required fro Reporting service webparts

Hi,
Since this is related to sharepoint reporting service web parts, I am
posting in both.
In place of report explorer I need to create my custom explorer. I have
figured out that report explorer implements ICellProvider.
I tried using it in my custom Report explorer, but unfortunately the URL
I am trying to pass is creating problems.
I can see the "report manager" in report viewer web part if my custom
explorer provides the cell data in following form:
"http://server/ReportS?/Users+Folders/domain+user/My
Reports/ChangeManagement/Matrix&rs:Command=Renderrc:Area=Report"
I would appreciate if any one can point out what data (and its format)
Report viewer web part expect as connection parameter.
Thanks
AmitAmit,
Are you using the MS webparts which just has the Viewer and the
explorer?
If so I have some third party web parts that do the parameters etc
which may be an easier solution to your problem.
Kind Regards
Dan Orchard
dan.orchard@.idow.co.uk
Amit Saxena wrote:
> Hi,
> Since this is related to sharepoint reporting service web parts, I am
> posting in both.
> In place of report explorer I need to create my custom explorer. I
> have figured out that report explorer implements ICellProvider. I
> tried using it in my custom Report explorer, but unfortunately the
> URL I am trying to pass is creating problems.
> I can see the "report manager" in report viewer web part if my custom
> explorer provides the cell data in following form:
> "http://server/ReportS?/Users+Folders/domain+user/My
> Reports/ChangeManagement/Matrix&rs:Command=Renderrc:Area=Report"
>
> I would appreciate if any one can point out what data (and its
> format) Report viewer web part expect as connection parameter.
>
> Thanks
> Amit|||Hi Dan,
I am using MS webparts. I will appreciate if you can tell me about other
webparts too.
Amit
Dan Orchard wrote:
> Amit,
> Are you using the MS webparts which just has the Viewer and the
> explorer?
> If so I have some third party web parts that do the parameters etc
> which may be an easier solution to your problem.
> Kind Regards
> Dan Orchard
> dan.orchard@.idow.co.uk
>
> Amit Saxena wrote:
>
>>Hi,
>>Since this is related to sharepoint reporting service web parts, I am
>>posting in both.
>>In place of report explorer I need to create my custom explorer. I
>>have figured out that report explorer implements ICellProvider. I
>>tried using it in my custom Report explorer, but unfortunately the
>>URL I am trying to pass is creating problems.
>>I can see the "report manager" in report viewer web part if my custom
>>explorer provides the cell data in following form:
>>"http://server/ReportS?/Users+Folders/domain+user/My
>>Reports/ChangeManagement/Matrix&rs:Command=Renderrc:Area=Report"
>>
>>I would appreciate if any one can point out what data (and its
>>format) Report viewer web part expect as connection parameter.
>>
>>Thanks
>>Amit
>