Problem with LoadReport web method

Hi,

I'm working on a reporting client for Reporting Services 2005 using SOAP. The problem I'm having is that for reports with multiple pages or a document map, the LoadReport web method, always says they have 0 pages or no document map.

To simplify the problem, i created a simple console app that just calls LoadReport on the /AdventureWorks Sample Reports/Product Catalog report to see if HasDocumentMap is true (it should be but isn't).

Any suggestions?

Kwan

I figured it out and it makes sense. The information is available only after you call Render.

So call, Render, then GetExecutionInfo to get the correct values for HasDocumentMap and NumPages.

Problem with Grouping

Hi,
I have 2 tables from which I need to get 2 figures:
1. Divide the Total of Table2 by the Total of Table1 and multiple by 10 as
in the top select and this yields the correct results.
2. Display the Amount using the same formula as above per each Id and here
is where I fail....when I sum theAmount returned from this result set I do
not get the number I get from the first result set which
is -2.4129432084474347 and is correct.
-- This select yields the correct result
select
sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
from Table1
full join Table2 on Table1.id = Table2.id
order by 1
-- The Amount grouped per each Id seems incorrect
select
Table1.Id
,sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
from Table1
inner join Table2 on Table1.id = Table2.id
group by Table1.Id
order by 1
/*
create table Table1 (Id int, Amount float)
create table Table2 (Id int, Amount float)
insert Table1 select 0, 2466940.7630629078
insert Table1 select 1, 1619341.4993436863
insert Table1 select 2, 294424.12812010606
insert Table1 select 3, 35224.9308604404
insert Table1 select 4, 5816.581840630568
insert Table1 select 5, 9909.2411025063448
insert Table1 select 6, 552720.34837997227
insert Table1 select 7, 20845.780113921814
insert Table1 select 8, 249466.69869616581
insert Table1 select 9, 223489.19351831
insert Table2 select 0, -26748.78485354947
insert Table2 select 1, -444083.44694001391
insert Table2 select 2, -190871.26271638702
insert Table2 select 3, -62978.717071003601
insert Table2 select 4, -4810.138640776684
insert Table2 select 5, -9915.2079038903303
insert Table2 select 6, -305657.70221188507
insert Table2 select 7, -17519.425886078694
insert Table2 select 8, -189198.31576409994
insert Table2 select 9, -70070.519106139123
--DELETE FROM Table1
--DELETE FROM Table2
--drop table Table1
--drop table Table2
*/The behavior you see is correct. The problem is mathematical.
Lets look at a bit of algebra. Your first query creates two sums and
then performs division on the two resuts. This is the equivelent to
the algebraic equation:
(a + b + c) / (x + y +z)
The second query pairs numbers from each set, performs division, then
sums the results:
(a / x) + (b / y) + (c / z)
These are NOT EQUIVELENT to each other. Plug in some numbers.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 16:50:53 +0200, "yan" <yanive@.rediffmail.com>
wrote:

>Hi,
>I have 2 tables from which I need to get 2 figures:
>1. Divide the Total of Table2 by the Total of Table1 and multiple by 10 as
>in the top select and this yields the correct results.
>2. Display the Amount using the same formula as above per each Id and here
>is where I fail....when I sum theAmount returned from this result set I do
>not get the number I get from the first result set which
>is -2.4129432084474347 and is correct.
>
>-- This select yields the correct result
>select
> sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
>from Table1
>full join Table2 on Table1.id = Table2.id
>order by 1
>-- The Amount grouped per each Id seems incorrect
>select
> Table1.Id
> ,sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
>from Table1
>inner join Table2 on Table1.id = Table2.id
>group by Table1.Id
>order by 1
>
>/*
>create table Table1 (Id int, Amount float)
>create table Table2 (Id int, Amount float)
>insert Table1 select 0, 2466940.7630629078
>insert Table1 select 1, 1619341.4993436863
>insert Table1 select 2, 294424.12812010606
>insert Table1 select 3, 35224.9308604404
>insert Table1 select 4, 5816.581840630568
>insert Table1 select 5, 9909.2411025063448
>insert Table1 select 6, 552720.34837997227
>insert Table1 select 7, 20845.780113921814
>insert Table1 select 8, 249466.69869616581
>insert Table1 select 9, 223489.19351831
>insert Table2 select 0, -26748.78485354947
>insert Table2 select 1, -444083.44694001391
>insert Table2 select 2, -190871.26271638702
>insert Table2 select 3, -62978.717071003601
>insert Table2 select 4, -4810.138640776684
>insert Table2 select 5, -9915.2079038903303
>insert Table2 select 6, -305657.70221188507
>insert Table2 select 7, -17519.425886078694
>insert Table2 select 8, -189198.31576409994
>insert Table2 select 9, -70070.519106139123
>--DELETE FROM Table1
>--DELETE FROM Table2
>--drop table Table1
>--drop table Table2
>*/
>|||Well,
The reason is what Roy said. But if you want that total sum too,
then you can try this query. The column with the ID null will have the total
u wanted.Hope this helps.
select
Table1.Id
,sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
from Table1
inner join Table2 on Table1.id = Table2.id
group by Table1.Id
with rollup
order by 1|||Thanks, I see.
Any way to achieve what I need?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:nhj1521k8ufpo28lgfv3fpugiqja0hmbro@.
4ax.com...
> The behavior you see is correct. The problem is mathematical.
> Lets look at a bit of algebra. Your first query creates two sums and
> then performs division on the two resuts. This is the equivelent to
> the algebraic equation:
> (a + b + c) / (x + y +z)
> The second query pairs numbers from each set, performs division, then
> sums the results:
> (a / x) + (b / y) + (c / z)
> These are NOT EQUIVELENT to each other. Plug in some numbers.
> Roy Harvey
> Beacon Falls, CT
> On Thu, 27 Apr 2006 16:50:53 +0200, "yan" <yanive@.rediffmail.com>
> wrote:
>|||I have no idea what you need, as the only information provided is
contradictory.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 21:34:49 +0200, "yan" <yanive@.rediffmail.com>
wrote:

>Thanks, I see.
>Any way to achieve what I need?
>
>
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:nhj1521k8ufpo28lgfv3fpugiqja0hmbro@.
4ax.com...
>|||What is it that you need?
What do these numbers represent and what is it you are trying to calculate?
You could do several things, each one is going to give you different
numbers:
Post a more complete explanation of what you need, along with DDL, sample
data, and desired results, and we will be able to help you.
For an explanation of what I am talking about :
http://www.aspfaq.com/etiquette.asp?id=5006
"yan" <yanive@.rediffmail.com> wrote in message
news:uzq9ihiaGHA.5004@.TK2MSFTNGP02.phx.gbl...
> Thanks, I see.
> Any way to achieve what I need?
>
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:nhj1521k8ufpo28lgfv3fpugiqja0hmbro@.
4ax.com...
as
here
>|||The 2 tables are actually temp tables calculated as part of a report and
contain Totals.
The Id column represnets groups along with each groups amount n the Amount
column.
I need to show the great total (which is -2.4129432084474347) from these
tables which is the formula given in my first post (Table2/Table1*10) and
which yields a correct figure and also the result per each group using the
same formula.
If I run the following select I get the numbers bellow which when I sum I
get a differnrt number than the great total, this is what I had doubts
about :
select Table1.id, Table2.Amount / Table1.Amount *10 AS Amount
from Table1 inner join Table2 on Table1.id = Table2.id
order by 1
Id, Amount
--
0 -0.10842897103187299
1 -2.7423705692715177
2 -6.4828675535221034
3 -17.879017937756206
4 -8.2696999244065026
5 -10.006021451413144
6 -5.530060601310776
7 -8.4043033123900095
8 -7.5841110959074793
9 -3.1352978639836735
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:gp3252d2pdhahoriu1mlqpt43krt7bpbeg@.
4ax.com...
>I have no idea what you need, as the only information provided is
> contradictory.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 21:34:49 +0200, "yan" <yanive@.rediffmail.com>
> wrote:
>|||As Roy stated in his first post, these are mathematically different
calculations.
10 / 2 = 5
20 / 10 = 2
50 / 5 = 10
(10+20+50) / (2+10+5) does not equal (5+2+10)
80 / 17 = 4.7
You cannot do it both ways and get the same answer. Explain the numbers,
what they mean individually, why you are deviding one by the other, and what
the final numbers are supposed to represent.
"yan" <yanive@.rediffmail.com> wrote in message
news:eJCpuziaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> The 2 tables are actually temp tables calculated as part of a report and
> contain Totals.
> The Id column represnets groups along with each groups amount n the Amount
> column.
> I need to show the great total (which is -2.4129432084474347) from these
> tables which is the formula given in my first post (Table2/Table1*10) and
> which yields a correct figure and also the result per each group using the
> same formula.
> If I run the following select I get the numbers bellow which when I sum I
> get a differnrt number than the great total, this is what I had doubts
> about :
> select Table1.id, Table2.Amount / Table1.Amount *10 AS Amount
> from Table1 inner join Table2 on Table1.id = Table2.id
> order by 1
> Id, Amount
> --
> 0 -0.10842897103187299
> 1 -2.7423705692715177
> 2 -6.4828675535221034
> 3 -17.879017937756206
> 4 -8.2696999244065026
> 5 -10.006021451413144
> 6 -5.530060601310776
> 7 -8.4043033123900095
> 8 -7.5841110959074793
> 9 -3.1352978639836735
>
> --
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:gp3252d2pdhahoriu1mlqpt43krt7bpbeg@.
4ax.com...
10
I
>|||Thank you, you both pointed out my mistake. I knwo what I have to do from
here on.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:er4k94iaGHA.1192@.TK2MSFTNGP04.phx.gbl...
> As Roy stated in his first post, these are mathematically different
> calculations.
> 10 / 2 = 5
> 20 / 10 = 2
> 50 / 5 = 10
> (10+20+50) / (2+10+5) does not equal (5+2+10)
> 80 / 17 = 4.7
> You cannot do it both ways and get the same answer. Explain the numbers,
> what they mean individually, why you are deviding one by the other, and
> what
> the final numbers are supposed to represent.
>
> "yan" <yanive@.rediffmail.com> wrote in message
> news:eJCpuziaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> 10
> I
>|||On Thu, 27 Apr 2006 22:07:22 +0200, "yan" <yanive@.rediffmail.com>
wrote:

>I need to show the great total (which is -2.4129432084474347) from these
>tables which is the formula given in my first post (Table2/Table1*10) and
>which yields a correct figure
You have already demonstrated that you can calculate that number.

> and also the result per each group using the
>same formula.
And you can calculate that number for each group.

>If I run the following select I get the numbers bellow which when I sum I
>get a differnrt number than the great total, this is what I had doubts
>about :
"when I sum...". Adding up all those numbers is meaningless. If you
need the number that results from calculating based on all the rows
together, generate that number in a different SELECT.
Roy

problem with GROUP BY and SUM

I'm having a problem with GROUP BY. There are multiple records for each registration.registrationid returned, and I need it to only return one record for each registration.registrationid and to add up all the TotalDollarsUsed for that registration.registrationid.

What ever I do I can't get it to SUM up the TotalDollarsUsed columns for each registrationid.

Does anyone have any ideas?

DECLARE@.SiteID int,
@.StartDate datetime,
@.EndDate datetime

SET @.SiteID = 2216
SET @.StartDate = '9/1/2003'
SET @.EndDate = '3/31/2004'

SELECT registration.registrationid, Registration.LinkID, Registration.StudentID, Registration.LastName, Registration.FirstName, Registration.MI, [Session].Hours,
DATEDIFF(minute, Attendance.TimeIn, Attendance.TimeOut) AS TimeMinutes,
SUM(CASE [Session].Timebased WHEN 1 THEN DATEDIFF(minute, Attendance.TimeIn, Attendance.TimeOut) * (Rate / 60)
ELSE Hours * Rate
END) AS TotalDollarsUsed
FROM Attendance
INNER JOIN Registration ON Attendance.RegistrationID = Registration.RegistrationID
INNER JOIN [Session] ON Attendance.SessionID = [Session].SessionID
GROUP BY Registration.RegistrationID, Registration.LinkID, Registration.StudentID, Registration.LastName, Registration.FirstName, Registration.MI,
[Session].Timebased, [Session].Hours, [Session].Rate, Attendance.AttendanceDate, Attendance.TimeOut, Attendance.TimeIn, Registration.SiteID
HAVING (NOT (Session.Rate IS NULL))
AND (Attendance.AttendanceDate BETWEEN @.startdate AND @.enddate)
AND LinkID IN (SELECT LinkID FROM Registration WHERE withdrawdate IS NULL AND SiteID = @.SiteID)
AND SiteID = @.SiteID
ORDER BY Registration.LinkID

The results returned from this script are like this:

RegistrationID, TotalDollarsUsed
1001, 200
1001, 100
1001, 50
4005, 200
4005, 200Your SELECT statement is returning more than RegistrationID and TotalDollarsUsed columns. Your indicated result shows that for RegistrationID 1001 there are 3 records with unidentical value for a particular column that is not yet clear from your code. Eliminating such a column from the result may be an option. Post your full result to facilitate better resolution to the problem.

Problem with Group By

Hi there,

I am a novice SQL Server Programmer. I have got a task to select multiple fields from multiple tables...I have successfully used joins to get the result. but I have got 2 questions

1. Is there any way of combining (concatenating) three strings as one in the query using "AS"

2. The final result had to be GROUPED by one particular field I am using in select statement...But, for every field in select statement, it lets me run the query without error, only if the field is included Group By clause.

Is there any way to avoid it?

yes, you can concatenate multiple strings into a single field with as...

select (f1+ f2 + f3) as singleString

and you to include any fields in the query in the group by clause...just put the field you are concerned with first. -- jp

|||

hi jp

Thanks for your reply...

your first answer fetched me correct results but regarding the Group By, I still have the problem :(

|||when you use group by you have to include all the fields from the query statement in the group by clause...|||

thank you jp..............

Problem with GETDATE()

Hello All,

I have a problem as follows

if i execute SELECT GETDATE() statement multiple times in a single run it returns me the same datetime without any difference in even milliseconds.

I am unable to figure out what is wrong. I am assuming that whenever executed in a transaction it will give the same result.

could anybody let me know what is correct. Thanks for your help in advance.

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

even then i get the same date.

What are you trying to achieve? The amount of time it takes to run multiple Select GetDate() is very minor. We would be able to help you better if we knew what your goal was.

|||

Hi, mate

I just executed:

SELECTGETDATE()SELECT *FROM Table1SELECTGETDATE()

and the the two dates was different. (Table1 has 120 000 rows)

This means that the query is executing too fast (in less than a millisecond) and that is why you receive the same results.

|||

yeah... if u execute query select getdate() several times one after another u cant understand the difference of milliseconds. don't worry...

|||

Hi Diamsorn,

Thanks for the reply. but all i am trying to do was i have a history table and i have included modified date as a part of primary key and when i am trying to update my main table i am inserting a record into history table. eventhough i am doing it in different time system says it is a violation of primary key.

For eg. Table1 is having below columns

Column1 Column2 Column3 and Suppose Primary key is composite key of column1 and column2

I have HistoryTable having columns

Column1 Column2 modifieddate and Suppose Primary key is composite key of Column1,Column2 and Modifieddate. but when i am trying to update the table1, and though trigger i am capturing getdate() to fill modifieddate, then as it is not different it is giving error.

how to overcome this problem?

Gneralproblem

|||

Which table is giving the primary key violation error? Table1 or HistoryTable.

What is your purpose of having a composite primary key in your history table of column1, column2, and modified date?

I would move away from using a trigger to insert into your history table, and do your update/insert inside of a transaction in a stored procedure. Triggers are a maintenance nightmare and I avoid them personally at all costs.

|||

Hi Diamsorn,

History table is giving me error. As i have to update the same record in Table1 and track the changes in HistoryTable. As my operation is so fast and as it is caputring same date it is giving primary key violation.

I would appreciate if any way to handle this problem using Triggers.

Thanks,

GeneralProblem