Making groups available

I'd like to get the sum of a row in a reporting services table. Unlike
summing columns which are easy, I'm told I need to create a group.
I create the group with the fields I want to sum. Then I right click on the
table cell I want to hold the sum value, and select expression. My table
column names are there, but not my group (which I need to sum my row).
When I create the group, I give it a name and add the fields I want to sum.
What else do I have to do to make the group available when I edit expressions?
Of course, if there's some aggregate functions that let's me sum a row
without a group, all the better.
Thanks
--
RandyHi Randy,
Welcome to the MSDN newsgroup.
Regarding on the "sum of a row in a reporting services table", do you mean
perform sum on all the columns in a single data row or still sum a certain
column , but restricted in a certain group?
If you're wantting to perform the sum on all the columns in a single row,
I'm afraid, the group or other filter can not help on this. You may need to
manually write the sum code in the expression of the textbox or certain
table cell. Also, since you'll perform sum on the columns, we need to make
sure all the columns are of number type.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Many thanks for the reply.
Yes, I would like to sum all the columns in the row, and they are all type
int.
I have found the code window, and written a VB function to sum all the
columns. When I say Code.MySum(...) in the Edit Expression dialog box, MySum
is underlined in red and "not defined".
I've tried to just sum the columns in the edit expression dialog box. The
sum function provided only takes one parameter. I'd need an example or BOM
reference to see how to do this. At one point, someone suggested the Subtotal
function, but I couldn't find that in the available aggregate functions list.
If you could point me toward an article on writing functions for reporting
services under the Code tab or in the Edit Expression dialog box, that would
help a lot.
Thanks again,
--
Randy
"Steven Cheng[MSFT]" wrote:
> Hi Randy,
> Welcome to the MSDN newsgroup.
> Regarding on the "sum of a row in a reporting services table", do you mean
> perform sum on all the columns in a single data row or still sum a certain
> column , but restricted in a certain group?
> If you're wantting to perform the sum on all the columns in a single row,
> I'm afraid, the group or other filter can not help on this. You may need to
> manually write the sum code in the expression of the textbox or certain
> table cell. Also, since you'll perform sum on the columns, we need to make
> sure all the columns are of number type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
>|||Thanks for your followup Randy,
IMO, if you just want to do sum on all the columns in a single row, you can
just put the sum code in the expression of the textbox( use + operator).
For example:
=Fields("col1").Value + Fields("col2").Value + Fields("col3").Value
For custom code in SSRS, you can have a look at the following web reference:
#Using Custom Code References in Expressions [Reporting Services]
http://msdn2.microsoft.com/en-US/library/ms155798(SQL.90).aspx
#Chapter 5: Using Custom .Net Code with Reports
http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Making a view that shows the results of several different queries.

Hello,

I am trying to create a view that shows the following

Field1: Sum of Amounts from Table A
Field2: Count of Amounts from Table A

Field3: Sum of of Amounts from Table B
Field4: Count of Amounts from Table B
..
..
..
Field3: Sum of of Amounts from Table H
Field4: Count of Amounts from Table H
..
..
..
Things are a bit more complex but this is the gist.

I am using SQL 2000.

I know how to do this pretty easily using a stored procedure. But how
can I do it in a view? A SQL server won't meet my needs in this
situation.

I tried OpenQuery ('myserver', 'exec myprocedure') but get the message
that my server is not configured for data access. I tried the system
stored procedure to set data access to true but nothing seemed to
happen.

I also tried Select * from (

Select Statement1, select statement2

)

but got syntax error at the comma between statement1 and statement2.

Trying to use select Statement1 as ABC to does not seem to work either.

Is there a way to do what I want without making 15 views and then a
final view that shows them all together? I know I could probably do
something by creating a ton of functions, but it really seems this
should not be that hard...

I am definitely open to any easy suggestions!

Thanks,
RyanOn 21 Dec 2005 13:51:38 -0800, Ryan wrote:

>Hello,
>I am trying to create a view that shows the following
>Field1: Sum of Amounts from Table A
>Field2: Count of Amounts from Table A
>Field3: Sum of of Amounts from Table B
>Field4: Count of Amounts from Table B
>.
>.
>.
>Field3: Sum of of Amounts from Table H
>Field4: Count of Amounts from Table H
>.
>.
>.
>Things are a bit more complex but this is the gist.
>I am using SQL 2000.
>I know how to do this pretty easily using a stored procedure. But how
>can I do it in a view? A SQL server won't meet my needs in this
>situation.

Hi Ryan,

This can be done in a single query. You can of course encapsulate that
in a view, stored procedure, or whatever.

To prevent double table-scanning, here's a query that will scan each
table only once:

SELECT Field1, Field2, Field3, ..., Field16
FROM (SELECT SUM(Amount) AS Field1, COUNT(Amount) AS Field2
FROM TableA) AS A
CROSS JOIN (SELECT SUM(Amount) AS Field3, COUNT(Amount) AS Field4
FROM TableB) AS B
....
CROSS JOIN (SELECT SUM(Amount) AS Field15, COUNT(Amount) AS Field16
FROM TableH) AS H

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have tried what you suggested but it seems to snag with Syntax
errors. SQL accepts my Select ... AS A, B, C if I use it with the
Select * construct but it does not accept the CROSS JOIN STATEMENT

Select * FROM
(
SELECT count(Amount) as count1, sum(Amount) as sum1
FROM mytable1
WHERE x="1" and y="2"...
)
AS A

CROSS JOIN

Select * FROM
(
SELECT count(Amount) as count1, sum(Amount) as sum1
FROM mytable1
WHERE x="1" and y="2" and some other stuff...
)
AS B

Any ideas? I am not sure why the CROSS JOIN won't work actually. It
error out as soon as it hits the SELECT statement that follows CROSS
JOIN...|||On 21 Dec 2005 16:58:18 -0800, Ryan wrote:

>I have tried what you suggested but it seems to snag with Syntax
>errors. SQL accepts my Select ... AS A, B, C if I use it with the
>Select * construct but it does not accept the CROSS JOIN STATEMENT

Hi Ryan,

You didn't use the correct syntax. Check my previous replly and compare
it carefully with your query.

>Select * FROM
>(
>SELECT count(Amount) as count1, sum(Amount) as sum1
>FROM mytable1
>WHERE x="1" and y="2"...
>)
>AS A
>CROSS JOIN
Remove the line below!
>Select * FROM
>(
>SELECT count(Amount) as count1, sum(Amount) as sum1
>FROM mytable1
>WHERE x="1" and y="2" and some other stuff...
>)
>AS B
>Any ideas? I am not sure why the CROSS JOIN won't work actually. It
>error out as soon as it hits the SELECT statement that follows CROSS
>JOIN...

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

make 1 record with Union statement

Hi there,
I've made a join query:
SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM QTYORDER',
A.QTYONHND AS 'QTYONHND'
FROM IV00102 A LEFT JOIN
IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR =
'S.NL.543'
GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
A.ATYALLOC, A.QTYSOLD
UNION
SELECT ITEMDESC AS ITEMDESC, ITEMNMBR AS ITEMNMBR, SUM(QTYORDER) AS 'SUM
QTYORDER',
0 AS 'QTYONHND'
FROM POP10110
WHERE POLNESTA IN (2) AND ITEMNMBR = 'S.NL.543'
GROUP BY ITEMDESC, ITEMNMBR
the result looks like this:
ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
Proactiv (ITEM) S.NL.543
0 -477
Proactiv (ITEM) S.NL.543
6000 0
what I want is to produce 1 record which will look like this:
ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
Proactiv (ITEM) S.NL.543
6000 -477
any suggestions on how to do this?
Thanks in advance,
SusannaUse the UNION-ed query as a derived table & use aggregate functions on the
outer query. i.e :
SELECT MAX( itemdesc ) AS "item_desc",
MAX( itemnbr ) AS "item_nbr",
...
FROM ( < your query with UNION > ) D
Anith|||What are you doing here? It could either be the max, or adding. I am
assuming that it doesn't matter because the 0 values actually mean not
applicable in your main query.

> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 0 -477
> Proactiv (ITEM) S.NL.543
> 6000 0
Like anith says, you can just do a group, or possibly something like this,
assuming that you are actually putting out one row per ITEMMBR (if not the
union isn't going to work well either)
SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, A.QTYONHND AS
'QTYONHND',
(SELECT SUM(QTYORDER) AS 'SUM QTYORDER'
FROM POP10110
WHERE POLNESTA IN (2) AND ITEMNMBR =
A.ITEMNMBR) as 'QTYORDER'
FROM IV00102 A
LEFT JOIN IV00101 B
ON A.ITEMNMBR = B.ITEMNMBR
WHERE A.RCRDTYPE IN (2)
AND A.LOCNCODE IN ('SALES')
AND A.ITEMNMBR = 'S.NL.543'
GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
A.ATYALLOC, A.QTYSOLD
Do you have some issues with poor data quality? I notice that ITEMDESC
comes from an outer join (which makes me wonder what that table is for) and
what the uniqueness is on. In your second query it seems to be itemnmber.
If it is, this should work (or something close.)
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:5D04D744-C679-487D-85F9-8AFBC2D3A844@.microsoft.com...
> Hi there,
> I've made a join query:
> SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM
> QTYORDER',
> A.QTYONHND AS 'QTYONHND'
> FROM IV00102 A LEFT JOIN
> IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
> WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR =
> 'S.NL.543'
> GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
> A.ATYALLOC, A.QTYSOLD
> UNION
> SELECT ITEMDESC AS ITEMDESC, ITEMNMBR AS ITEMNMBR, SUM(QTYORDER) AS 'SUM
> QTYORDER',
> 0 AS 'QTYONHND'
> FROM POP10110
> WHERE POLNESTA IN (2) AND ITEMNMBR = 'S.NL.543'
> GROUP BY ITEMDESC, ITEMNMBR
> the result looks like this:
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 0 -477
> Proactiv (ITEM) S.NL.543
> 6000 0
> what I want is to produce 1 record which will look like this:
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 6000 -477
> any suggestions on how to do this?
> --
> Thanks in advance,
> Susanna