Possible to access no. rows in groups outside the table?

Hi there,

I'm currently grouping data on some criteria, the way the data works basically means that there are between 2-3 groups guaranteed (no more). In the Group Header I have CountRows(table1_group) thus giving me the total number of rows in each group at the top of each grouping. However, I need to do a calculation in a textbox above the table using these row counts. The method of grouping is not too difficult when using the VB syntax in the grouping expression but more difficult to get the same effect from the SQL side and hence I don't want to have separate datasets calling different queries to get the information that way. Is there anyway to get access to these row counts on the groups? Reporting Services can't know how many groups there will be before processing so I'm not sure that this is possible? Ideally I suppose if there was a Group CountRows collection of some kind that could be accessed in an expression in the textbox or custom code then this might be possible. I could also add an invisible column and set the values to something specific depending on the group if there was a way to count the number of values in the table (unique values repeated in columns in a group, but unique to that group).

Any help is much appreciated,
Thanks.

Sorry, but it would also be feasible to place the textbox within the table by moving the headings and other table data downward leaving a gap at the top. However, the textbox although in the table is presumably still outside the scope of the group. Just thought I'd mention it incase it sparked an idea by anyone.

Thanks again.

|||

Have an invisible list above your table and add group the list by the same fields as your table and use Count or any other aggregate functions in textboxes inside that list.

Shyam

|||

Thanks for your response, but I'm not sure how to implement that. If I have say 3 groupings in my table. The RowCount at the top of the group headers gives the following at the top of each grouping in the table:

Group1 Total: 4

Group2 Total: 7

Group3 Total: 22

Then a single textbox at the top of the page would say

Group1 + Group2 = 11

Group1 + Group2 = 50% of Group3.

How would I accomplish this using the invisible list?

Thanks again.

|||

Have a hidden table at the top of your report and have the same 3 groupings and then delete all the rows except the group header rows. Write 3 functions in report code which will increment a public variable (3 ublic variables declared at the top of the report) whenever it is called. Say, the function names are CountGroup1, CountGroup2, CountGroup3.

In each of your 3 group headers, call each of the corresponding functions in the code to count the group records. Have the expression in the group headers like this:

=Code.CountGroup1(CountDistinct(Fields!Field1.Value, "table1_Group1"))

=Code.CountGroup2(CountDistinct(Fields!Field1.Value, "table1_Group2"))

=Code.CountGroup3(CountDistinct(Fields!Field1.Value, "table1_Group3"))

And you can access the counts by referring to the public variables at the top of your main table using an expression something like this Code.VariableName

Shyam

Possible Bug Using TOP and Paging via a Temp Table

This is not a "bug" per se, as I know that the order of rows in SQL
Server isn't guaranteed to be consistent unless ORDER BY is specified.
Still, this is somewhat odd behavior.
This involves paging logic via parameterized queries. Since I can't
post my client's DDL, I have used northwind to duplicate the issue.
Code:
--vars to simulate paging
DECLARE
@.start int,
@.end int
--create temp table
CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
NULL, [Orderdate] [DateTime] NOT NULL)
SELECT @.start = 0 /*****CHANGE ME*****/
SELECT @.end = 12 /*****CHANGE ME*****/
--insert rows
INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
ShipAddress,Orderdate)
SELECT DISTINCT
TOP 12 /*****CHANGE ME*****/
CustomerID,
ShipVia,
ShipName,
ShipAddress,
Orderdate
FROM
ORDERS
WHERE
CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
ORDER BY
CustomerId
--select the page of data
SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
--select to see the whole temp table
--SELECT * FROM #TempTable
DROP TABLE #TempTable
1. Run the query as is. Notice that there are 3 rows returned where
ShipVia = 2.
2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
24". Notice that although the __rowcnt selection correctly returns
13-24, the rows are the same.
3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
36". The page of data now changes (I believe because the customerID
changes on this page).
4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
of data returned are now "correct"--the data returned is different for
each page.
You can uncomment the final select statement to see what is actually
going into the temp table.
It seems that the TOP statement is somehow causing the rows to be added
to the temp table in reverse order. Apparently, the "correct" rows are
returned using an unpatched version of SQL Server.
Anyone know what might cause this to happen?
Thanks for any insight,
PhilHi
Add a few 100 thousand rows to this, plus a machine with 4 processors and a
lot of RAM and the query performs different again. Even a different OS.
As the row count in your example increases, there is probably a different
query plan/spool to disk occurring.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>|||There are some better approaches to paging that don't exhibit these
symptoms.
http://www.aspfaq.com/2120
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>