How to use two aggregate functions in RS 2005

I am not able to use two aggregate functions to display a value in a
table.
Basically it is (Sum of (Sum of X)).
How do we get around this?
Please let me know.
ThanksWhat might work for you is to add a calculated field. On the field list
where you drag and drop from just do a right mouse click, add a field (or
something like that). Then you can do a sum of that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anup" <anupkm@.gmail.com> wrote in message
news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
>I am not able to use two aggregate functions to display a value in a
> table.
> Basically it is (Sum of (Sum of X)).
> How do we get around this?
> Please let me know.
> Thanks
>|||Hi Bruce,
Thank you very much for your reply.
So you mean to say that I need to add that field as a calculated field
instead of a normal field?
Thanks
Anup
Bruce L-C [MVP] wrote:
> What might work for you is to add a calculated field. On the field list
> where you drag and drop from just do a right mouse click, add a field (or
> something like that). Then you can do a sum of that.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Anup" <anupkm@.gmail.com> wrote in message
> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
> >I am not able to use two aggregate functions to display a value in a
> > table.
> > Basically it is (Sum of (Sum of X)).
> >
> > How do we get around this?
> > Please let me know.
> >
> > Thanks
> >|||If you add a calculated field to your dataset (this is from the list of
field, I am not talking about a sql statement here) you can have the
calculated field be an aggregate. Then you can aggregate the calculated
field, getting around your problem. I do this to make things simplier too.
Try it as I mentioned and see if it makes sense in your situation. Adding a
field manually to the field list returned by the query is not very
discoverable.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anup" <anupkm@.gmail.com> wrote in message
news:1165432174.432662.162930@.f1g2000cwa.googlegroups.com...
> Hi Bruce,
> Thank you very much for your reply.
> So you mean to say that I need to add that field as a calculated field
> instead of a normal field?
> Thanks
> Anup
> Bruce L-C [MVP] wrote:
>> What might work for you is to add a calculated field. On the field list
>> where you drag and drop from just do a right mouse click, add a field (or
>> something like that). Then you can do a sum of that.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Anup" <anupkm@.gmail.com> wrote in message
>> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
>> >I am not able to use two aggregate functions to display a value in a
>> > table.
>> > Basically it is (Sum of (Sum of X)).
>> >
>> > How do we get around this?
>> > Please let me know.
>> >
>> > Thanks
>> >
>|||Thanks Bruce. Here is my situation:
I need to display the "Double Sum" Value in the footer of a table.The
details of this table is in a different level of grouping and footer is
at a different level of grouping.
Thanks
Anup
Bruce L-C [MVP] wrote:
> If you add a calculated field to your dataset (this is from the list of
> field, I am not talking about a sql statement here) you can have the
> calculated field be an aggregate. Then you can aggregate the calculated
> field, getting around your problem. I do this to make things simplier too.
> Try it as I mentioned and see if it makes sense in your situation. Adding a
> field manually to the field list returned by the query is not very
> discoverable.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Anup" <anupkm@.gmail.com> wrote in message
> news:1165432174.432662.162930@.f1g2000cwa.googlegroups.com...
> > Hi Bruce,
> >
> > Thank you very much for your reply.
> >
> > So you mean to say that I need to add that field as a calculated field
> > instead of a normal field?
> >
> > Thanks
> > Anup
> >
> > Bruce L-C [MVP] wrote:
> >> What might work for you is to add a calculated field. On the field list
> >> where you drag and drop from just do a right mouse click, add a field (or
> >> something like that). Then you can do a sum of that.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Anup" <anupkm@.gmail.com> wrote in message
> >> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
> >> >I am not able to use two aggregate functions to display a value in a
> >> > table.
> >> > Basically it is (Sum of (Sum of X)).
> >> >
> >> > How do we get around this?
> >> > Please let me know.
> >> >
> >> > Thanks
> >> >
> >

How to use the same stored procedure to display two different char

I need to display two charts on a report based on the same stored procedure
that accepts a single parameter. I do not wish to be promted for the
parameter. Instead, I would like to pass two different values to each chart
within the report.
Please advise.
Thanks,
KonstantinHow do you know what value to pass it?
Anyway, something to get you going in the right direction. First thing to
realize is that query parameters and report parameters are two different
things. RS automatically creates a report parameter for you for each stored
procedure query parameter it sees. But, you don't have to use it. You can
map it to an expression instead. Click on the ..., parameters tab. On the
right pick change it from mapping to a report parameter and map it to an
expression instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Konstantin Shaumyan" <KonstantinShaumyan@.discussions.microsoft.com> wrote
in message news:3A7E2C6B-FF43-4993-9B21-04EA79316410@.microsoft.com...
>I need to display two charts on a report based on the same stored procedure
> that accepts a single parameter. I do not wish to be promted for the
> parameter. Instead, I would like to pass two different values to each
> chart
> within the report.
> Please advise.
> Thanks,
> Konstantin|||Thanks for the tip.
I created two datasets based on the same stored procedure for each graph and
set my parameter for each dataset to literals.
Everything worked as expected.
Regards,
Konstantin
"Bruce L-C [MVP]" wrote:
> How do you know what value to pass it?
> Anyway, something to get you going in the right direction. First thing to
> realize is that query parameters and report parameters are two different
> things. RS automatically creates a report parameter for you for each stored
> procedure query parameter it sees. But, you don't have to use it. You can
> map it to an expression instead. Click on the ..., parameters tab. On the
> right pick change it from mapping to a report parameter and map it to an
> expression instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Konstantin Shaumyan" <KonstantinShaumyan@.discussions.microsoft.com> wrote
> in message news:3A7E2C6B-FF43-4993-9B21-04EA79316410@.microsoft.com...
> >I need to display two charts on a report based on the same stored procedure
> > that accepts a single parameter. I do not wish to be promted for the
> > parameter. Instead, I would like to pass two different values to each
> > chart
> > within the report.
> >
> > Please advise.
> >
> > Thanks,
> > Konstantin
>
>sql

How to Use RownNumber in a Matrix?

Hello Guys,

I am trying to count the number of rows that are displayed on my matrix report and display it in a textbox. What I use for the table reports doesn't really work:

="Number of records displayed: " + cstr(RowNumber("Data_Set_Name"))

This will return a number of rows returned by this data set. However, the count of rows that are actually being displayed in the matrix is very different. If anyone dealt with this before please let me know how to solve it. Thank you very much!

Try =CountRows(Fields!FieldName.Value) or =CountRows("DataSetName").

|||

I actually brought in another field, and did CountDistinct on it - now it works like a charm. Thank you so much for pointing me in the right direction.

How to use query to get sql database properties

Hi,

I'm creating a User Interface to display Sql database Properties, but I cannot find the right query to retrieved the info on database properties.

The status that I get is "ONLINE", but it should be "NORMAL".

Cannot find the right query to get the date of last database backup, last transaction log backup, and maintenance plan, etc..

Please help me [:'(]

All the backup info is stored in msdb db. Check out BOL under "Viewing Backup Information".|||

Thanks 4 d reply, but I cannot find an example code, what I found was this,

Syntax

RESTORE FILELISTONLY
FROM < backup_device >
[ WITH
[ FILE=file_number]
[ [, ] PASSWORD= {password |@.password_variable } ]
[ [, ] MEDIAPASSWORD= {mediapassword |@.mediapassword_variable } ]
[ [,] { NOUNLOAD | UNLOAD } ]
]

< backup_device > ::=
{
{'logical_backup_device_name' |@.logical_backup_device_name_var}
| { DISK | TAPE }=
{'physical_backup_device_name' |@.physical_backup_device_name_var}
}

I need an example code

how to use group by (group tasks based on projects)

Hi folks,

I have a Projects , each project have many tasks now i want to display tasks replated to each project:

for example:

Project1-->task1

task2

task3

task4

Project2-->task4

task5

task6

.............................................projectN.....................

how to write query for this

i have 2 tables:

Project .......>columns are projectid

Task->columns are projectid, taskid

|

Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid

In this case you would not have to use a group by.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||its giving taskid 's null|||

Consider this example

Project

1

2

3

Task

1,A

1,B

2,C

I you want all tasks and their projects, you could write

SELECT ProjectID, TaskID

FROM Task

GROUP BY ProjectID, TaskID

ORDER BY ProjectID, TaskID

This would return

1,A

1,B

2,C

but would provide nothing about Project 3 because it has no tasks.

Given the structure of the table (no repeated tasks), you might even get away with

SELECT ProjectID, TaskID

FROM Task

ORDER BY ProjectID, TaskID

However, if you wanted all projects and to include their tasks, if available, then Jens is correct

Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid

This would return

1, A

1, B

2, C

3, NULL

The NULL on the last row exists because Project 3 is shown (from the Project table) but there is not equivalent task in the Task table.

The word LEFT from LEFT JOIN tells SQL to return all rows from the Project table and, if available, the row data from Tasks too. If there is no row in Task for a Project in Project, then NULL is placed in the task specific columns.

By dropping the word LEFT, it would limit the result to only those projects that had tasks (but the earlier examples are simpler).

If you want to replace NULL with something more useful, using ISNULL, you could write

SELECT p.projectid, ISNULL(t.Taskid, 'No associated tasks')
FROM Project p
LEFT JOIN Task t ON p.projectid = t.taskid
ORDER BY p.projectid, t.taskid

This would now return

1, A

1, B

2, C

3, No associated tasks

regards,

Niall.

how to use DtsLocalizableAttribute

I Have to set the display name and Description of DtsPipelineAttribute using DtsLocalizableAttribute.

Can anybody point to some example. MSDN Help is not much of use, it does not contain any example.

Dhamrbir

Can you please explain us more about what you are trying to do here?

|||

Hi Deniz

I have a custom component, whose name should be localized. For this I have to read the resource file and set the Display Name and Description of the DtsPipelineComponent attribute. As per the MSDN Documentation http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.localization.dtslocalizableattribute.aspx

We need to set the LocalizationType property of the DtsPipelineComponent attribute to the type of the resource class that contains the resources for the component. When ever I set this attribute to the Resource Type I get this Error

"Error 1 'ETI.HPC.Oracle.Dest.OracleDestinationResources' is a 'type', which is not valid in the given context. "

Do I have to inherit this resource class for some class. What I am doing wrong.

After that how do I use DtsLocalizableAttribute to set the Name and Description.

Thanks

Dharmbir

How to use a variable in a SQL statement…

Hi,

background

From a 'members' table, I get the first letter of the last name of the members and display in a dataList as buttons.

In the dataList control, the

<asp:button>

has the following:

CommandArgument='<%# DataBinder.Eval(...) %>' OnCommand="doSomething"

In the code behind page I have the following code:

Public Sub doSomething(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs)
 Dim getLastName as string = e.CommandArgument.ToString()
 lblBtn.Text = "clicked " + e.CommandArgument.ToString()
 …

This code will do the job – in the label 'lblBtn' the text will show the 'value' that the button represents – i.e. if you click the button for 'B' the lblBtn will read 'clicked B'

The problem

Now that I know which button was clicked, I need to use the information in a SQL query to retrieve the relevant data.

Const strSQL2 As String = "select * from members where letter = 'getLastName'"

This query returns no records at all – no errors, just nothing. What I am doing wrong?

Kindly help.

Thanks,

Sami.a couple of questions:
1> Is the first letter of the last name stored in Members under field letter?
2> Is the following statement used in doSomething?

Const strSQL2 As String = "select * from members where letter = 'getLastName'"

If the answer to both questions are YES. Here is your correct strSQL2:

Const strSQL2 As String = "select * from members where letter = '" & getLastName & "'"

|||Thanks for your response – yes, you are right on both assumptions. I did what you suggested but got an error.

In VS the expression 'getLastName' got a blue underline and when I mouse over it I get a balloon message that says 'Constant expression is required'

When I 'view in browser' and click 'yes' for continuing with the preview and then click on one of the letter buttons I get an error page with the following message:

Incorrect syntax near '='

Any suggestions?

Thanks for your patience - I am very new at this

Sami.|||Sorry. My fault. Overlooked your statement. Please remove "Const " from your statement below.

Const strSQL2 As String = "select * from members where letter = 'getLastName'"

Change to this:

strSQL2 As String = "select * from members where letter = 'getLastName'"

Please let me know if there is still error.|||Wow - thank you so much...

It works great.

Sami.

How to use a value from one recordset in another..?

Im doing a select that should retrieve a name from one table and display the
number of correct bets done in the betDB (using the gameDB that has info on
how a game ended)

I want the "MyVAR" value to be used in the inner select statement without
too much hassle. As you can see im trying to get the "MyVAR" to insert in
the bottom line of the code.

Whats the quick fix to this one..?

Thanks in advance :-)

---- code begin ----
select memberDB.memberID as MyVAR, (select count(GamesDB.GameID)
from GamesDB
inner join GameBetDB
on GameBetDB.betHome = GamesDB.homeGoal and GameBetDB.betAway =
GamesDB.awaygoal
inner join memberDB
on memberDB.memberID = GameBetDB.memberID
where GamesDB.gameID=GameBetDB.gameID
and GameBetDB.memberID= MyVAR ) as wins from memberDB
---- code end ----Bane (bane@.noname.net) writes:
> Im doing a select that should retrieve a name from one table and display
> the number of correct bets done in the betDB (using the gameDB that has
> info on how a game ended)
> I want the "MyVAR" value to be used in the inner select statement without
> too much hassle. As you can see im trying to get the "MyVAR" to insert in
> the bottom line of the code.
> Whats the quick fix to this one..?
> Thanks in advance :-)
> ---- code begin ----
> select memberDB.memberID as MyVAR, (select count(GamesDB.GameID)
> from GamesDB
> inner join GameBetDB
> on GameBetDB.betHome = GamesDB.homeGoal and GameBetDB.betAway =
> GamesDB.awaygoal
> inner join memberDB
> on memberDB.memberID = GameBetDB.memberID
> where GamesDB.gameID=GameBetDB.gameID
> and GameBetDB.memberID= MyVAR ) as wins from memberDB
> ---- code end ----

Well, the answer to the question as posted is: use aliases, like this:

select m.memberID as MyVAR,
(select count(g.GameID)
from GamesDB g
join GameBetDB gb on gb.betHome = g.homeGoal
and gb.betAway = g.awaygoal
join memberDB m2 on m2.memberID = gb.memberID
where g.gameID=gb.gameID
and gb.memberID = m.memberID) as wins
from memberDB m

But that inner memberDB does not make any sense to me. I think you
are better off with:

select m.memberID as MyVAR,
(select count(g.GameID)
from GamesDB g
join GameBetDB gb on gb.betHome = g.homeGoal
and gb.betAway = g.awaygoal
where g.gameID=gb.gameID
and gb.memberID = m.memberID) as wins
from memberDB m

Then of course the join conditions between GamesDB and GameBetDB looks
funny. Surely g.gameID = gb.gameID is the join condition? The other two
looks more like filter to me. (This is a theoretical issue only, though,
and does not affect the result.)

And finally I don't see the need for the nested subquery. Maybe it is
as simple as?

select m.memberID, count(g.GameID)
from GamesDB g
join GameBetDB gb on g.gameID=gb.gameID
and gb.betHome = g.homeGoal
and gb.betAway = g.awaygoal
join memberDB m on m.memberID = gb.memberID
group by m.memberID

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

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