Grouping on multiple datasets - ANY suggestions

I have a report with two datasets, DS1 and DS2, which contain the same data fields, but with different values. Like so:

DS1 = sales
salesperson sale_number amount
John Smith 1 $100
John Smith 2 $105
Mary Jane 3 $98
John Smith 4 $275
Mary Jane 5 $92

DS2 = sales with price overrides
salesperson sale_number amount
John Smith 1 $100
Mary Jane 3 $98
Mary Jane 5 $92

Now what I want to do is see how the salespeople are doing. I can use either dataset and get great results independently:

Sales Results:
Salesperson Number of Sales Total Amount
John Smith 3 $480
Mary Jane 2 $190

or

Sales results with price overrides:
Salesperson NumSales with Over Total Amount
John Smith 1 $480
Mary Jane 2 $190

Now what I really want to do is a combo table like so:
Salesperson NumSales with Over Number of Sales %Overrides
John Smith 1 3 33.3%
Mary Jane 2 2 100%

I can not figure out how to do this. If I create a table that has DS1 as its datasource, I need to access DS2 for a count. So I try this for the NumSales with Over:

= count((Fields!sale_number.Value,"DS2"))

This just repeates the total number of sales in DS2, which is 3, for each line; not separating them out by salesperson.

If I try something fancier such as:

=count((Fields!sale_number.Value,"DS2", (Fields!Salesperson.Value,"DS2") like =(Fields!Salesperson.Value))

The report won't even run.

I want to do something along those lines. Does anyone have any ideas how to do this? I've considered subqueries to use salesperson as a filter, but the datasets are so large that the reports end up taking forever to run. I've tried using iif, but it doesn't seem to like using a field from a second dataset. I even tried to use the embedded VB code box to write a function, but then I couldn't pass the full array from the secondary dataset to the function (I could pass it from DS1, but not DS2).

I know this is incredibly simple, but this noob can't figure it out. If anyone has any suggestions I would deeply appreciate it.

Thank you,

cmk8895Joining datasets at a report level is not currently supported. It is on the wish list for a next release. Why don't you create the combo dataset at the data source level?

Grouping Dimension Values

I have a question about grouping values in a dimension.

I have a dimension that relates to the age of a person. When I run my ETL we bring in the age as it was at the time of the load. When I display that age to my users in a report, I would like to group the ages according to internal usages.

Example: Age 16, 17, 18 would be grouped into a group of 16 to 18, etc....

This provides to us a larger statistic sample set, plus it makes the reports much easier to read.

I am using Reporting Services to generate reports, but I was looking to move the grouping logic away from the client to the Analysis Services server.

I actually have several dimensions like this where I would like to perform grouping.

I was considering using a Calculated Member to solve this, but wasn't sure if there was a cleaner solution.

Thanks in advance.

Bill

One solution is to do this by a named calculation in the data source view and create your groups with TSQL-CASE. In this way you will have full control over the groups.

The other way is to use the discretization method property of this attribute/column in the dimension editor. With this method you let SSAS create the groups with a little less flexibility than using a named calculation.

HTH

Thomas Ivarsson

|||

Thomas,

Thanks for the response.

I should have stated in my initial post that I am using AS 2000 not SSAS 2005.

Sorry for that.

Any ideas as to how to fix this solution into AS 2000?

Thanks in advance

Bill

|||

Use TSQL Case when you update your dimension table. Books On Line have good examples regarding case.

You can also wrap the TSQL case in you key and name columns, for the dimension level, in the dimension editor. This more of a dirty hack than fixing this in the dimension and your data source.

You can use views against the source table as well.

HTH

Thomas Ivarsson

grouping data to a field

Hey
Im doing a report wich is causing me some trouble:
Im doing a dataset selecting all values regarding the company, and then
im mapping the fields to textboxes in my VS designer. This should
result in a list with all my customers and their data.
Now below each customer i would like to place a table holding data from
another dataset, but i only want the data in the table to correspond to
the company just above the table.
I have placed both the customer data and the table in a list item, but
the problem seems to be the data in the table. I either get evrything
each time for all customers, or i only get the first customer data
below each customer.
Can anyone tell me how i can make this work.
Thanks
JimmyHi
I think there are two ways to solve this
first and the easiest :
get all the data in one dataset and use grouping facility in the reporting
services to group the data
second :
use sub report to build the details and pass the company id from the
parent report to the sub report
Joe
"Jimbo" wrote:
> Hey
> Im doing a report wich is causing me some trouble:
> Im doing a dataset selecting all values regarding the company, and then
> im mapping the fields to textboxes in my VS designer. This should
> result in a list with all my customers and their data.
> Now below each customer i would like to place a table holding data from
> another dataset, but i only want the data in the table to correspond to
> the company just above the table.
> I have placed both the customer data and the table in a list item, but
> the problem seems to be the data in the table. I either get evrything
> each time for all customers, or i only get the first customer data
> below each customer.
> Can anyone tell me how i can make this work.
> Thanks
> Jimmy
>|||Hey Joe
Ok, its not optimal, but it got the job done.
Im actually using a combination of the two.
Your posting lead me on the way though :)
Thanks
Jimmy

Grouping by Combinations of Values

Here's a Brain Twister:
I have a list of Customers who buy from a list of Products. I need to
summarize (countdistinct) customers by the combination(s) of products
they buy.
Product Combo Distinct Customer Count
Product A 10
Product B 8
Product C 6
Product A&B 7
Product A&C 5
Product B&C 4
Product A&B&C 1
I'm sure somebody must have had to do this before and it's probably
totally simple, but I'm just not seeing it. It gets even worse in that
there are multiple levels in multiple hierarchies of products, but if I
can get the technique down, I can probably get the rest.
Any ideas will be greatly appreciated..
Thanks!!how many products do you have?|||9 Level 1's with 1-3 Level 2's
12 Level 2's with 1-6 Level 3's
25 Level 3's with 1-15 Level 4's
40 Level 4's with 1-10 Level 5's
61 Level 5's with 1-6 Level 6's
etc...
304 possible 8 level combinations at present, and products can be added
or recategorized pretty regularly.|||I have not done this "cut & paste" in awhile:
=========================
Relational division is one of the eight basic operations in Codd's
relational algebra. The idea is that a divisor table is used to
partition a dividend table and produce a quotient or results table.
The quotient table is made up of those values of one column for which a
second column had all of the values in the divisor.
This is easier to explain with an example. We have a table of pilots
and the planes they can fly (dividend); we have a table of planes in
the hangar (divisor); we want the names of the pilots who can fly every
plane (quotient) in the hangar. To get this result, we divide the
PilotSkills table by the planes in the hangar.
CREATE TABLE PilotSkills
(pilot CHAR(15) NOT NULL,
plane CHAR(15) NOT NULL,
PRIMARY KEY (pilot, plane));
PilotSkills
pilot plane
=========================
'Celko' 'Piper Cub'
'Higgins' 'B-52 Bomber'
'Higgins' 'F-14 Fighter'
'Higgins' 'Piper Cub'
'Jones' 'B-52 Bomber'
'Jones' 'F-14 Fighter'
'Smith' 'B-1 Bomber'
'Smith' 'B-52 Bomber'
'Smith' 'F-14 Fighter'
'Wilson' 'B-1 Bomber'
'Wilson' 'B-52 Bomber'
'Wilson' 'F-14 Fighter'
'Wilson' 'F-17 Fighter'
CREATE TABLE Hangar
(plane CHAR(15) NOT NULL PRIMARY KEY);
Hangar
plane
=============
'B-1 Bomber'
'B-52 Bomber'
'F-14 Fighter'
PilotSkills DIVIDED BY Hangar
pilot
=============================
'Smith'
'Wilson'
In this example, Smith and Wilson are the two pilots who can fly
everything in the hangar. Notice that Higgins and Celko know how to
fly a Piper Cub, but we don't have one right now. In Codd's original
definition of relational division, having more rows than are called for
is not a problem.
The important characteristic of a relational division is that the CROSS
JOIN (Cartesian product) of the divisor and the quotient produces a
valid subset of rows from the dividend. This is where the name comes
from, since the CROSS JOIN acts like a multiplication operator.
Division with a Remainder
There are two kinds of relational division. Division with a remainder
allows the dividend table to have more values than the divisor, which
was Codd's original definition. For example, if a pilot can fly more
planes than just those we have in the hangar, this is fine with us.
The query can be written in SQL-89 as
SELECT DISTINCT pilot
FROM PilotSkills AS PS1
WHERE NOT EXISTS
(SELECT *
FROM Hangar
WHERE NOT EXISTS
(SELECT *
FROM PilotSkills AS PS2
WHERE (PS1.pilot = PS2.pilot)
AND (PS2.plane = Hangar.plane)));
The quickest way to explain what is happening in this query is to
imagine an old World War II movie where a cocky pilot has just walked
into the hangar, looked over the fleet, and announced, "There ain't no
plane in this hangar that I can't fly!" We are finding the pilots for
whom there does not exist a plane in the hangar for which they have no
skills. The use of the NOT EXISTS() predicates is for speed. Most SQL
systems will look up a value in an index rather than scan the whole
table. The SELECT * clause lets the query optimizer choose the column
to use when looking for the index.
This query for relational division was made popular by Chris Date in
his textbooks, but it is not the only method nor always the fastest.
Another version of the division can be written so as to avoid three
levels of nesting. While it is not original with me, I have made it
popular in my books.
SELECT PS1.pilot
FROM PilotSkills AS PS1, Hangar AS H1
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar);
There is a serious difference in the two methods. Burn down the
hangar, so that the divisor is empty. Because of the NOT EXISTS()
predicates in Date's query, all pilots are returned from a division by
an empty set. Because of the COUNT() functions in my query, no pilots
are returned from a division by an empty set.
In the sixth edition of his book, INTRODUCTION TO DATABASE SYSTEMS
(Addison-Wesley; 1995 ;ISBN 0-201-82458-2), Chris Date defined another
operator (DIVIDEBY ... PER) which produces the same results as my
query, but with more complexity.
Exact Division
The second kind of relational division is exact relational division.
The dividend table must match exactly to the values of the divisor
without any extra values.
SELECT PS1.pilot
FROM PilotSkills AS PS1
LEFT OUTER JOIN
Hangar AS H1
ON PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar);
This says that a pilot must have the same number of certificates as
there planes in the hangar and these certificates all match to a plane
in the hangar, not something else. The "something else" is shown by a
created NULL from the LEFT OUTER JOIN.
Please do not make the mistake of trying to reduce the HAVING clause
with a little algebra to:
HAVING COUNT(PS1.plane) = COUNT(H1.plane)
because it does not work; it will tell you that the hangar has (n)
planes in it and the pilot is certified for (n) planes, but not that
those two sets of planes are equal to each other.
Note on Performance
The nested EXISTS() predicates version of relational division was made
popular by Chris Date's textbooks, while the author is associated with
popularizing the COUNT(*) version of relational division. The Winter
1996 edition of DB2 ON-LINE MAGAZINE
(http://www.db2mag.com/96011ar:htm) had an article entitled "Powerful
SQL:Beyond the Basics" by Sheryl Larsen which gave the results of
testing both methods. Her conclusion for DB2 was that the nested
EXISTS() version is better when the quotient has less than 25% of the
dividend table's rows and the COUNT(*) version is better when the
quotient is more than 25% of the dividend table.|||Try this link:
http://www.windowsitpro.com/Article...20169.html?Ad=1
Although its a simple request i dont think its a 2minute job.
Ian
<datagal@.msn.com> wrote in message
news:1126559995.257988.29900@.g44g2000cwa.googlegroups.com...
> Here's a Brain Twister:
> I have a list of Customers who buy from a list of Products. I need to
> summarize (countdistinct) customers by the combination(s) of products
> they buy.
> link
> Product Combo Distinct Customer Count
> Product A 10
> Product B 8
> Product C 6
> Product A&B 7
> Product A&C 5
> Product B&C 4
> Product A&B&C 1
> I'm sure somebody must have had to do this before and it's probably
> totally simple, but I'm just not seeing it. It gets even worse in that
> there are multiple levels in multiple hierarchies of products, but if I
> can get the technique down, I can probably get the rest.
> Any ideas will be greatly appreciated..
> Thanks!!
>|||Still not getting where I need to go....
Maybe I should clarify that my 8 levels of product categories (classes,
whatever...) and all adjacencies in a single table, making the tough
part my problem the gouping by multiple, concatinated values at each
level.
Jody|||Just modify the code and change the Baskets (Hangar) in the example to
include a basket name. Here is the painful details and code:
DROP TABLE Sales;
CREATE TABLE Sales
(customer CHAR(15) NOT NULL,
item_code CHAR(15) NOT NULL,
PRIMARY KEY (customer, item_code));
INSERT INTO Sales VALUES ('Celko', 'Piper Cub');
INSERT INTO Sales VALUES ('Higgins', 'B-52 Bomber');
INSERT INTO Sales VALUES ('Higgins', 'F-14 Fighter');
INSERT INTO Sales VALUES ('Higgins', 'Piper Cub');
INSERT INTO Sales VALUES ('Jones', 'B-52 Bomber');
INSERT INTO Sales VALUES ('Jones', 'F-14 Fighter');
INSERT INTO Sales VALUES ('Smith', 'B-1 Bomber');
INSERT INTO Sales VALUES ('Smith', 'B-52 Bomber');
INSERT INTO Sales VALUES ('Smith', 'F-14 Fighter');
INSERT INTO Sales VALUES ('Motts', 'B-1 Bomber');
INSERT INTO Sales VALUES ('Motts', 'B-52 Bomber');
INSERT INTO Sales VALUES ('Motts', 'F-14 Fighter');
INSERT INTO Sales VALUES ('Wilson', 'B-1 Bomber');
INSERT INTO Sales VALUES ('Wilson', 'B-52 Bomber');
INSERT INTO Sales VALUES ('Wilson', 'F-14 Fighter');
INSERT INTO Sales VALUES ('Wilson', 'F-17 Fighter');
CREATE TABLE Baskets
(item_code CHAR(15) NOT NULL,
basket_name CHAR(15)NOT NULL,
PRIMARY KEY (item_code, basket_name));
INSERT INTO Baskets VALUES ('B-1 Bomber', '3 planes');
INSERT INTO Baskets VALUES ('B-52 Bomber', '3 planes');
INSERT INTO Baskets VALUES ('F-14 Fighter', '3 planes');
INSERT INTO Baskets VALUES ('B-1 Bomber', '2 bombers');
INSERT INTO Baskets VALUES ('B-52 Bomber', '2 bombers');
INSERT INTO Baskets VALUES ('Piper Cub', 'wimp');
CREATE PROCEDURE ReportBasket (@.my_basket CHAR(15))
AS
SELECT @.my_basket, COUNT (DISTINCT cust)
FROM (
SELECT S1.customer
FROM Sales AS S1
LEFT OUTER JOIN
(SELECT item_code FROM Baskets WHERE basket_name =
@.my_basket)AS B1
ON S1.item_code = B1.item_code
GROUP BY S1.customer
HAVING COUNT(S1.item_code) = (SELECT COUNT(item_code)
FROM Baskets AS B2 WHERE basket_name
= @.my_basket)
AND COUNT(B1.item_code) = (SELECT COUNT(item_code)
FROM Baskets AS B2 WHERE basket_name =
@.my_basket)
) AS X(cust);
You can further modify this procedure with a derived table of the
basket names instead of a parameter and get it all at once.|||I know purists will be mad at this solution, as it uses proprietary
tricks, but anyway:
create table sales(customer_id int, product varchar(3))
insert into sales values(1, 'a')
insert into sales values(1, 'b')
insert into sales values(1, 'c')
insert into sales values(2, 'a')
insert into sales values(2, 'b')
insert into sales values(2, 'c')
insert into sales values(3, 'a')
insert into sales values(3, 'b')
insert into sales values(3, 'c')
insert into sales values(4, 'a')
insert into sales values(4, 'b')
insert into sales values(5, 'b')
insert into sales values(5, 'c')
insert into sales values(6, 'b')
insert into sales values(6, 'c')
insert into sales values(7, 'a')
insert into sales values(8, 'b')
insert into sales values(9, 'c')
insert into sales values(10, 'a')
insert into sales values(11, 'b')
go
create function customer_products(@.customer_id int)
returns varchar(100)
as
begin
declare @.ret varchar(100)
set @.ret = ''
select @.ret = @.ret + ',' + product
from sales where customer_id = @.customer_id
order by product
return @.ret
end
go
-- testing the function
select distinct customer_id, dbo.customer_products(customer_id)
product_list
from sales
go
customer_id product_list
----
--
1 ,a,b,c
2 ,a,b,c
3 ,a,b,c
4 ,a,b
5 ,b,c
6 ,b,c
7 ,a
8 ,b
9 ,c
10 ,a
11 ,b
-- what I think you need
select product_list, count(*)
from(select distinct customer_id, dbo.customer_products(customer_id)
product_list
from sales) t
group by product_list
product_list
-- --
,a 2
,a,b 1
,a,b,c 3
,b 2
,b,c 2
,c 1
(6 row(s) affected)
go
drop function customer_products
drop table sales|||You should probably add OPTION(MAXDOP 1) to your select statement.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1126792522.609984.131080@.g14g2000cwa.googlegroups.com...
> I know purists will be mad at this solution, as it uses proprietary
> tricks, but anyway:
> create table sales(customer_id int, product varchar(3))
> insert into sales values(1, 'a')
> insert into sales values(1, 'b')
> insert into sales values(1, 'c')
> insert into sales values(2, 'a')
> insert into sales values(2, 'b')
> insert into sales values(2, 'c')
> insert into sales values(3, 'a')
> insert into sales values(3, 'b')
> insert into sales values(3, 'c')
> insert into sales values(4, 'a')
> insert into sales values(4, 'b')
> insert into sales values(5, 'b')
> insert into sales values(5, 'c')
> insert into sales values(6, 'b')
> insert into sales values(6, 'c')
> insert into sales values(7, 'a')
> insert into sales values(8, 'b')
> insert into sales values(9, 'c')
> insert into sales values(10, 'a')
> insert into sales values(11, 'b')
> go
> create function customer_products(@.customer_id int)
> returns varchar(100)
> as
> begin
> declare @.ret varchar(100)
> set @.ret = ''
> select @.ret = @.ret + ',' + product
> from sales where customer_id = @.customer_id
> order by product
> return @.ret
> end
> go
> -- testing the function
> select distinct customer_id, dbo.customer_products(customer_id)
> product_list
> from sales
> go
> customer_id product_list
> --
> ----
--
> 1 ,a,b,c
> 2 ,a,b,c
> 3 ,a,b,c
> 4 ,a,b
> 5 ,b,c
> 6 ,b,c
> 7 ,a
> 8 ,b
> 9 ,c
> 10 ,a
> 11 ,b
> -- what I think you need
> select product_list, count(*)
> from(select distinct customer_id, dbo.customer_products(customer_id)
> product_list
> from sales) t
> group by product_list
> product_list
> -- --
> ,a 2
> ,a,b 1
> ,a,b,c 3
> ,b 2
> ,b,c 2
> ,c 1
> (6 row(s) affected)
>
> go
> drop function customer_products
> drop table sales
>|||I think this might be it!!! I'm going to play with it and see what
happens.
Thank you everybody!
Jodysql

Grouping and summing

HI,
I have a group on two fields (ie SalesPersonId and CustomerID). I want
to print a total after any of this values change (ie. If CustomerId
changes, a total line for the customerID will be printed but not for
the SalesPersonId). How Can I do it with running values ?
Best regards=runningvalue(sum, Fields!xxx.value, "CustomerID")
where "CustomerID" is the name for your customer group. I would make
sure to name your group something other than "table1_Group1" for
clarification purposes.
Fab wrote:
> HI,
> I have a group on two fields (ie SalesPersonId and CustomerID). I want
> to print a total after any of this values change (ie. If CustomerId
> changes, a total line for the customerID will be printed but not for
> the SalesPersonId). How Can I do it with running values ?
> Best regards

Grouping and Custom Code

Hello everyone,

I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup.

TH

GH1 (hidden)

GH2 (hidden)

Det (hidden)

GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value)

GF1 =Code.ShowAndResetSubTotal()

TF =Code.GrandTotal

I have the following in my Code window.

Dim Public SubTotal as Decimal

Dim Public GrandTotal as Decimal

Function ShowAndResetSubTotal() as Decimal

ShowAndResetSubTotal = SubTotal

SubTotal = 0

End Function

Function AddValue(newValue as decimal) as Decimal

SubTotal += newValue

GrandTotal += newValue

AddValue = newValue

End Function

This gives me incorrect results and I can't figure out why. Here's how it shows on my report:

Part Number Quantity Cost Regular Subtotal Method Using Custom Code Part 1 4,000 1.49 $5,947.20 Customer 1 $11,894.40 $0.00 Part 2 10 1.01 $10.07 Customer 2 $50.34 $5,947.20 Part 3 1 0.44 $0.44 Part 4 6,050 0.25 $1,530.41 Part 5 0 1.25 $0.00 Part 6 0 1.23 $0.00 Customer 3 $42,851.86 $10.07 Part 7 16,250 0.24 $3,922.59 Customer 4 $19,612.94 $1,530.85 Part 8 17,250 0.38 $6,544.82 Part 9 27,225 0.20 $5,380.20 Customer 5 $66,891.69 $3,922.59 Grand Total $141,301.23 $0.00

The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different.

As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this?

Jarret

Hi Jarret,

The reason for seeing 0 (I think) Is that after a group ends, Reporting Services basically creates a new instance of your custom code and therefore any saved values get cleared.

I am not sure how your GF1 shows a value though... I could be wrong, but this is the experience I have had...

Regards,
Neil

|||The way I approached it...

I ordered my duplicate values... or some way of identifying that the value was not needed, and if the previous item = that item then do not add it to the total... Then for each footer call the same code and passing in the same values.

So each footer will be identical ... passing in the value and some other way of identifying if the value is unique...

Hope this helps...

Regards,
Neil

Group with Time Values - Please Help

I have two tables that are joined by a left outer join.
Table A (Hours) just has 24 records that represent each our in the day. For example:
00:00, 01:00, ...23:00. Table B (Data) has the data I need to report off of. I joined
Table A to Table B.

I created a group on Table A because I always want to display all 24 hours even if there
is no data in Table B for that hour. So I now have 24 sections in my group.

My problem is that when I put data into the details section, I'm only getting data where
the hours exactly match. For example, Group section 08:00 is only returning data where
the hour is 08:00. I actually need it to return all data where the hour is between
08:00 and 08:59. I've been working on this for a while and I'm really stuck.

I'm using an access database and the hours field in both table is a date/time field.

Any help would be greatly appreciated. Thanks so much.

- StephanieHi,

I had experienced the same problem. I had to display the data for all the days in a month regardless of the data they have.

Used the same left outer join concept. But it didn't work. Then I had created a temp table and written code to get the result.

If any body knows why the left outer join concept is not working in crystal, please share with us

sample

table1 : contains simply all the dates from 1 to lastday
table2 : contains data for the dates in table1 (not for all the days)|||Hi Stephanie
One possible solution would be to create a report based only on Table A(with hours registered) and subreport based on Table B.Don't make any links between them.In main report insert group for field that holds hours.If you view preview now you would see all records from Table B for each hour.
In main report create a formula and add shared variable and assign only first two characters from group field.Values will be 00,01,02 etc.
Now,in subreport supress all records where first two characters of your hour fiels in Table B are not equal to shared variable.
I tested this in CR 8.0 and it worked fine.|||Thanks Denan, that's a very interesting suggestion! I'm going to try that right now!

Stephanie|||Good idea!

But what about the performance?

For a single day, the report will be called 24 times?|||Hi
Performance is definetly not optimal here.Best solution would be to filter data in subreport but unfortunately CR (at least 8.0) doesn't allow shared variables in record selection.
Biggest problem here is that you can not link those two tables.Left outer join doesn't help since it also requires a match in both tables.
Another solution would be to add another field in Table B that would hold values like 08:00,09:00 etc.That means you would need to add application logic to compute that value.I think that solution is more expensive.

Đenan

Group sum outside group?

I'm trying to get a sum of values from a group with the following expression.
=SUM(Fields!BF_Child.Value, "table1_Details_Group"). When I try to run it I
get the following error.
[rsInvalidAggregateScope] The Value expression for the textbox â'textbox7â'
has a scope parameter that is not valid for an aggregate function. The scope
parameter must be set to a string constant that is equal to either the name
of a containing group, the name of a containing data region, or the name of a
data set.
Build complete -- 1 errors, 0 warnings
Why can't I sum group values?Eric,
Try this:
=RunningValue(Fields!BF_Child.Value,Sum,"table1_Details_Group")
Michael C
"eric" wrote:
> I'm trying to get a sum of values from a group with the following expression.
> =SUM(Fields!BF_Child.Value, "table1_Details_Group"). When I try to run it I
> get the following error.
> [rsInvalidAggregateScope] The Value expression for the textbox â'textbox7â'
> has a scope parameter that is not valid for an aggregate function. The scope
> parameter must be set to a string constant that is equal to either the name
> of a containing group, the name of a containing data region, or the name of a
> data set.
> Build complete -- 1 errors, 0 warnings
>
> Why can't I sum group values?|||Hi Michael,
Thank you for the response. Unfortunately I get the same error. I have 2
groups, one to group by parent and the second group to reduce duplicate
records. The Sum I would like to total the second group values, but only
the visible records in the group. Any other ideas? Thanks
Eric
"Michael C" wrote:
> Eric,
> Try this:
> =RunningValue(Fields!BF_Child.Value,Sum,"table1_Details_Group")
> Michael C
> "eric" wrote:
> > I'm trying to get a sum of values from a group with the following expression.
> > =SUM(Fields!BF_Child.Value, "table1_Details_Group"). When I try to run it I
> > get the following error.
> >
> > [rsInvalidAggregateScope] The Value expression for the textbox â'textbox7â'
> > has a scope parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the name
> > of a containing group, the name of a containing data region, or the name of a
> > data set.
> > Build complete -- 1 errors, 0 warnings
> >
> >
> > Why can't I sum group values?|||Hi Eric,
Did you find the solution for this?
I have the same problem and I would like to know if there´s anyway of doing
it.
Thanks,
Mónica
"eric" <eric@.discussions.microsoft.com> escribió en el mensaje
news:2DD05D4D-4B91-4088-B1A9-EA5BF35592B4@.microsoft.com...
> Hi Michael,
> Thank you for the response. Unfortunately I get the same error. I have 2
> groups, one to group by parent and the second group to reduce duplicate
> records. The Sum I would like to total the second group values, but only
> the visible records in the group. Any other ideas? Thanks
> Eric
> "Michael C" wrote:
>> Eric,
>> Try this:
>> =RunningValue(Fields!BF_Child.Value,Sum,"table1_Details_Group")
>> Michael C
>> "eric" wrote:
>> > I'm trying to get a sum of values from a group with the following
>> > expression.
>> > =SUM(Fields!BF_Child.Value, "table1_Details_Group"). When I try to
>> > run it I
>> > get the following error.
>> >
>> > [rsInvalidAggregateScope] The Value expression for the textbox
>> > 'textbox7'
>> > has a scope parameter that is not valid for an aggregate function. The
>> > scope
>> > parameter must be set to a string constant that is equal to either the
>> > name
>> > of a containing group, the name of a containing data region, or the
>> > name of a
>> > data set.
>> > Build complete -- 1 errors, 0 warnings
>> >
>> >
>> > Why can't I sum group values?sql

Group Query to include 0 values

I have to sum(NoWidgets) produced GROUP'ed By Station. The Widgets Produced
are in a subtable of the Widget Stations, so it works for the most part by
doing this:
Select Sum(NoWidgets) From WidgetData Inner Join StationData GROUP BY Station
Where I run into problems is when I want to sum the number of blue widgets
by Station. Because I want to know even if a station produced 0 blue widgets.
If I say:
Select Sum(NoWidgets) From WidgetData INNER JOIN StationData Where Color =
'Blue' GROUP BY Station
Then the stations that had no blue widgets are not included in the output.
I want ALL stations in the output, even if they have a value of 0. I'm also
wanting to do an average, and I want that based upon all stations, whether or
not any blue widgets were produced.
Seems like it should be easy to do, and I'm sure I'm showing myself to be a
novice for not know how that is done.
Thanks!
On Thu, 9 Mar 2006 13:50:28 -0800, PolarBears wrote:
(snip)
>Where I run into problems is when I want to sum the number of blue widgets
>by Station. Because I want to know even if a station produced 0 blue widgets.
>If I say:
>Select Sum(NoWidgets) From WidgetData INNER JOIN StationData Where Color =
>'Blue' GROUP BY Station
>Then the stations that had no blue widgets are not included in the output.
>I want ALL stations in the output, even if they have a value of 0. I'm also
>wanting to do an average, and I want that based upon all stations, whether or
>not any blue widgets were produced.
Hi PolarBears,
Yes, this is simple. You can use the GROUP BY ALL version of the GROUP
BY clause:
SELECT SUM(NoWidgets)
FROM WidgetDate
INNER JOIN StationData
ON somethin you forgot to include in your post
WHERE Color = 'Blue'
GROUP BY ALL Station
Note that this works in SQL Server 2000 and SQL Server 2005, but the
GROUP BY ALL clause is marked as deprecated in SQL Server 2005 (meaning
it will be removed in a future version).
If you prefer a portable, ANSI-standard version, you can use
SELECT SUM(CASE WHEN Color = 'Blue' THEN NoWidgets ELSE 0 END)
FROM WidgetDate
INNER JOIN StationData
ON somethin you forgot to include in your post
GROUP BY Station
(Note: both queries above are untested. See www.aspfaq.com/5006 ff you
prefer a tested query.)
Hugo Kornelis, SQL Server MVP
|||Thanks a ton!
"Hugo Kornelis" wrote:

> On Thu, 9 Mar 2006 13:50:28 -0800, PolarBears wrote:
> (snip)
> Hi PolarBears,
> Yes, this is simple. You can use the GROUP BY ALL version of the GROUP
> BY clause:
> SELECT SUM(NoWidgets)
> FROM WidgetDate
> INNER JOIN StationData
> ON somethin you forgot to include in your post
> WHERE Color = 'Blue'
> GROUP BY ALL Station
> Note that this works in SQL Server 2000 and SQL Server 2005, but the
> GROUP BY ALL clause is marked as deprecated in SQL Server 2005 (meaning
> it will be removed in a future version).
> If you prefer a portable, ANSI-standard version, you can use
> SELECT SUM(CASE WHEN Color = 'Blue' THEN NoWidgets ELSE 0 END)
> FROM WidgetDate
> INNER JOIN StationData
> ON somethin you forgot to include in your post
> GROUP BY Station
> (Note: both queries above are untested. See www.aspfaq.com/5006 ff you
> prefer a tested query.)
> --
> Hugo Kornelis, SQL Server MVP
>

Group no. of records by text in a text/varchar field

Create table Test
(Text1 varchar(500))
insert Test values('I love SQL')
insert Test values('SQL rocks')
insert Test values('SQL rocks in 2005')
insert Test values('MS rocks too')
insert Test values('MS is short for microsoft')
So i want to run a query where I would like to group by some key text words
..
So i want to get a count of entries in the table that has words 'SQL' and
'MS' in it
Output should be
KeyWord Count
MS 2
SQL 3
What is the query ? I would eventually add more keywords to the query..
Thanksyou'd want to unpack your input string into a table then it's just a matter
of finding the occurrences.
e.g.
declare @.s varchar(100)
set @.s='MS,SQL'
declare @.padded varchar(8000);set @.padded=','+@.s+','
select s,count(*)
from (select
substring(@.padded,digit+1,charindex(',',
@.padded,digit+1)-digit-1)
from racdigits
where digit <= len(@.padded)-1
and substring(@.padded,digit,1)= ',') derived(s)
join Test on Test.Text1 like '%'+derived.s+'%'
group by s
racdigits is just an auxilary table with value from 1-8000 (i.e. select top
8000 digit=identity(int,1,1) into racdigits from sysobjects,syscolumns)
-oj
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5HDQH2jGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Create table Test
> (Text1 varchar(500))
> insert Test values('I love SQL')
> insert Test values('SQL rocks')
> insert Test values('SQL rocks in 2005')
> insert Test values('MS rocks too')
> insert Test values('MS is short for microsoft')
> So i want to run a query where I would like to group by some key text
> words ..
> So i want to get a count of entries in the table that has words 'SQL' and
> 'MS' in it
> Output should be
> KeyWord Count
> MS 2
> SQL 3
> What is the query ? I would eventually add more keywords to the query..
> Thanks
>
>|||Where do you want to show data?
If you use front end application, split data there
Madhivanan
Hassan wrote:
> Create table Test
> (Text1 varchar(500))
> insert Test values('I love SQL')
> insert Test values('SQL rocks')
> insert Test values('SQL rocks in 2005')
> insert Test values('MS rocks too')
> insert Test values('MS is short for microsoft')
> So i want to run a query where I would like to group by some key text word
s
> ..
> So i want to get a count of entries in the table that has words 'SQL' and
> 'MS' in it
> Output should be
> KeyWord Count
> MS 2
> SQL 3
> What is the query ? I would eventually add more keywords to the query..
> Thanks|||On Tue, 13 Jun 2006 20:22:47 -0700, Hassan wrote:

>Create table Test
>(Text1 varchar(500))
>insert Test values('I love SQL')
>insert Test values('SQL rocks')
>insert Test values('SQL rocks in 2005')
>insert Test values('MS rocks too')
>insert Test values('MS is short for microsoft')
>So i want to run a query where I would like to group by some key text words
>..
>So i want to get a count of entries in the table that has words 'SQL' and
>'MS' in it
>Output should be
>KeyWord Count
>MS 2
>SQL 3
>What is the query ? I would eventually add more keywords to the query..
Hi Hassan,
Store the keywords in a seperate table, then use a query such as this:
SELECT k.Keyword, COUNT(t.Text1)
FROM Keywords AS k
LEFT JOIN Test AS t
ON t.Text1 LIKE '%' + k.Keyword + '%'
GROUP BY k.Keyword
Hugo Kornelis, SQL Server MVP

Group by Time interval.

I have a table(work_order) with time as varchar(5).

The values in table looks like this

work_order_id rtim

1 08:15
2 08:45
3 10:13
4 14:56

and so on...

I want to count how many work orders for every half an hour.

The result should look like this

Hours Count
8 10
8:30 15
9 34
9:30 03

and so on...

really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.

Any Help is Appreciated.

Thankyou.
Jaidev ParuchuriBetter to store your times as a DATETIME column:

CREATE TABLE Work_Order (work_order_id INTEGER PRIMARY KEY, rtim DATETIME
NOT NULL)

INSERT INTO Work_Order VALUES (1, '2003-11-11T08:15:00')
INSERT INTO Work_Order VALUES (2, '2003-11-11T08:45:00')
INSERT INTO Work_Order VALUES (3, '2003-11-11T10:13:00')
INSERT INTO Work_Order VALUES (4, '2003-11-11T14:56:00')

SELECT mi,
COUNT(*)
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',rtim)/30.0)*30
,'20000101'),108)
FROM Work_Order) AS W(mi)
GROUP BY mi

If you have to keep the Rtim column as CHAR:

SELECT mi,
COUNT(*)
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',
CONVERT(DATETIME,rtim,108)
)/30.0)*30
,'20000101'),108)
FROM Work_Order) AS W(mi)
GROUP BY mi

--
David Portas
----
Please reply only to the newsgroup
--|||"Jaidev Paruchuri" <jaidev@.criticalresourcetech.com> wrote in message
news:f885ab3.0311110746.7084cc95@.posting.google.co m...
> I have a table(work_order) with time as varchar(5).
> The values in table looks like this
> work_order_id rtim
> 1 08:15
> 2 08:45
> 3 10:13
> 4 14:56
> and so on...
> I want to count how many work orders for every half an hour.
> The result should look like this
> Hours Count
> 8 10
> 8:30 15
> 9 34
> 9:30 03
> and so on...
> really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.
> Any Help is Appreciated.
> Thankyou.
> Jaidev Paruchuri

CREATE TABLE Work_Orders
(
work_order_id INT NOT NULL PRIMARY KEY,
rtim CHAR(5) NOT NULL
)

SELECT Hrs.h + Sep.s + Mins.begin_min AS start_time,
COUNT(rtim) AS order_count
FROM (SELECT '00' AS h UNION ALL SELECT '01' AS h UNION ALL
SELECT '02' AS h UNION ALL SELECT '03' AS h UNION ALL
SELECT '04' AS h UNION ALL SELECT '05' AS h UNION ALL
SELECT '06' AS h UNION ALL SELECT '07' AS h UNION ALL
SELECT '08' AS h UNION ALL SELECT '09' AS h UNION ALL
SELECT '10' AS h UNION ALL SELECT '11' AS h UNION ALL
SELECT '12' AS h UNION ALL SELECT '13' AS h UNION ALL
SELECT '14' AS h UNION ALL SELECT '15' AS h UNION ALL
SELECT '16' AS h UNION ALL SELECT '17' AS h UNION ALL
SELECT '18' AS h UNION ALL SELECT '19' AS h UNION ALL
SELECT '20' AS h UNION ALL SELECT '21' AS h UNION ALL
SELECT '22' AS h UNION ALL SELECT '23' AS h) AS Hrs
CROSS JOIN
(SELECT ':' AS s) AS Sep
CROSS JOIN
(SELECT '00' AS begin_min, '29' AS end_min
UNION ALL
SELECT '30' AS begin_min, '59' AS end_min) AS Mins
LEFT OUTER JOIN
Work_Orders AS WO
ON rtim BETWEEN Hrs.h + Sep.s + Mins.begin_min AND
Hrs.h + Sep.s + Mins.end_min
GROUP BY Hrs.h + Sep.s + Mins.begin_min

Regards,
jag|||John

This query is beyond Excellence !!

This is exactly what i need.

Thank you for valuable your time!

regards,
--Jaidev Paruchuri

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
David,

Your Query worked fine.
I didnt look at it earlier.

Thankyou very much .
Jaidev Paruchuri

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Group By Problem RS2005 using Report-Parameter?

Hi all,
my Report shows not the correct values, if i choose more than one
Products from my Parameter-Object (Products). Does RS2005 is having a
problem with GROUP BY?
The SqlProfiler does not show the filtered query which the Report is
executing?
I just want to have a small Report to show the Totals of each sold
Products and i want to filter on one ore more Products.
my Report based on the following DataSet (DsSales) of AdventureWorks:
SELECT Production.Product.Name,
SUM(Sales.SalesOrderDetail.LineTotal) AS Total,
SUM(Sales.SalesOrderDetail.UnitPriceDiscount) AS Discount,
Production.Product.ProductID
FROM Sales.SalesOrderDetail INNER JOIN
Production.Product ON
Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
GROUP BY Production.Product.Name, Production.Product.ProductID
i have a additional DataSet for my Parameter Products which based on
the Query:
SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
I attached a new Report-Parameter to this DataSet (Multi, Value-Member
to ProductID, Display-Member to name of the Product).
i defined a Filter in DsSales (=Fields!ProductID.Value In
=Parameters!Products.Value(0))
If i choose all from the combo-Box (Parameter is multi) i get no
result. If i choose just one Product i get the correct Result.
Plain sql against the Database gives always the correct values, so
RS2005 not filtering correct with Group by and Having?
Any ideas?
Thanks for your answers in advance...
RonnyThere is nothing wrong in the query or group by etc...
This works very much fine. One small adjustment needs to be done is that
in your filter you need to have something like this.
(=Fields!ProductID.Value In
=Parameters!Products.Value(0)) or (=Fields!ProductID.Value In
=Parameters!Products.Value)
The difference between value(0) and Value is select full oject or all option
or single from the drop down.
Amarnath
"Classgenerator" wrote:
> Hi all,
> my Report shows not the correct values, if i choose more than one
> Products from my Parameter-Object (Products). Does RS2005 is having a
> problem with GROUP BY?
> The SqlProfiler does not show the filtered query which the Report is
> executing?
> I just want to have a small Report to show the Totals of each sold
> Products and i want to filter on one ore more Products.
> my Report based on the following DataSet (DsSales) of AdventureWorks:
> SELECT Production.Product.Name,
> SUM(Sales.SalesOrderDetail.LineTotal) AS Total,
> SUM(Sales.SalesOrderDetail.UnitPriceDiscount) AS Discount,
> Production.Product.ProductID
> FROM Sales.SalesOrderDetail INNER JOIN
> Production.Product ON
> Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
> GROUP BY Production.Product.Name, Production.Product.ProductID
> i have a additional DataSet for my Parameter Products which based on
> the Query:
> SELECT ProductID, Name
> FROM Production.Product
> ORDER BY Name
> I attached a new Report-Parameter to this DataSet (Multi, Value-Member
> to ProductID, Display-Member to name of the Product).
> i defined a Filter in DsSales (=Fields!ProductID.Value In
> =Parameters!Products.Value(0))
> If i choose all from the combo-Box (Parameter is multi) i get no
> result. If i choose just one Product i get the correct Result.
> Plain sql against the Database gives always the correct values, so
> RS2005 not filtering correct with Group by and Having?
> Any ideas?
> Thanks for your answers in advance...
> Ronny
>|||Hi Amarnath,
thanx for the hint.
i also found another solution which is quicker in execution also.
Define the parameter already in Query:
SELECT Production.Product.Name, SUM(Sales.SalesOrderDetail.LineTotal)
AS Total, SUM(Sales.SalesOrderDetail.UnitPriceDiscount) AS Discount,
Production.Product.ProductID
FROM Sales.SalesOrderDetail INNER JOIN Production.Product ON
Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
GROUP BY Production.Product.Name, Production.Product.ProductID
HAVING (Production.Product.ProductID IN (@.ProductID))
Create a DataSet for the Products.
SELECT ProductID, Name FROM Production.Product ORDER BY Name
Add a Report-Parameter:
name: ProductID
type: String
prompt: products
multi: checked
available values: from query (DataSetproducts)
valuemember: ProducID
displaymember: name
Not nessecary to set a Filter in DataSet!
The Parameter is automatically added to the Parameters of the DataSet:
@.ProductID=Parameters!ProductID.Value
Everything is working fine and very quick.
Thanx Ronny

Group by Not in the Group

Hi,
I am trying to work out a way of finding out if certain values do not appear
in the group by e.g.
ID Ref
1 1
1 2
2 3
2 2
3 1
3 1
I would only want to return ID 1 and 3 as these are the only ID's that dont
have a Ref of 3, if that makes any sense, does anyone know how this is done.
Thanks
PD
Try this:
SELECT DISTINCT [ID] FROM [Test] WHERE [ID] NOT IN (SELECT DISTINCT [ID]
FROM [Test] WHERE [Ref] = 3)
Regards,
JayAchTee
"Phil" wrote:

> Hi,
> I am trying to work out a way of finding out if certain values do not appear
> in the group by e.g.
> ID Ref
> 1 1
> 1 2
> 2 3
> 2 2
> 3 1
> 3 1
> I would only want to return ID 1 and 3 as these are the only ID's that dont
> have a Ref of 3, if that makes any sense, does anyone know how this is done.
> Thanks
> PD

Group By Consecutive Values

Hi I've been puzzling over this one for ages, Imagine a dataset like:
ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
1-Jan 07:00 1-Jan 15:00 00001 Fred S
2-Jan 07:00 2-Jan 15:00 00001 Fred S
3-Jan 07:00 3-Jan 15:00 00001 Fred
4-Jan 07:00 4-Jan 15:00 00001 Fred S
5-Jan 07:00 5-Jan 15:00 00001 Fred S
6-Jan 07:00 6-Jan 15:00 00001 Fred
1-Jan 07:00 1-Jan 15:00 00002 Jane
2-Jan 07:00 2-Jan 15:00 00002 Jane S
3-Jan 07:00 3-Jan 15:00 00002 Jane S
4-Jan 07:00 4-Jan 15:00 00002 Jane S
5-Jan 07:00 5-Jan 15:00 00002 Jane S
6-Jan 07:00 6-Jan 15:00 00002 Jane
I would like to group by consecutive absence codes to generate a report
like:
Fred had 2 occurrences of AbsenceCode S
1-Jan 07:00 to 2-Jan 15:00
4-Jan 07:00 to 5-Jan 15:00
Jane had 1 occurrence of AbsenceCode S
2-Jan 07:00 to 5-Jan 15:00
I have many more codes, and many more records, How can I group by each
occurrence like this?
I've tried using RunningValue with Previous(AbsenceCode) but RS won't allow
nested aggregate functions.
Thanks in advance,
N.Create two groups - one on Name and another one on AbsenceCode. See the
attached example that goes against local pubs database.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>state</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>city</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>address</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="state">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>state</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="city">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>city</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="au_fname">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>au_fname</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!au_fname.Value & " " &
Fields!au_lname.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="address">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>address</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!address.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>pubs</DataSetName>
<Width>5.375in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!state.Value & " [" &
RunningValue(Fields!state.Value, Count, "table1_Group1") & "
authors]"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!state.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!city.Value & " [" &
RunningValue(Fields!city.Value, Count, "table1_Group2") & "
authors]"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!city.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!state.Value</Parent>
</Grouping>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>1.625in</Width>
</TableColumn>
<TableColumn>
<Width>1.375in</Width>
</TableColumn>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>1.125in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>4.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="pubs">
<rd:DataSourceID>d743d813-756c-4688-850b-f2e6deda54b0</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=pubs</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7.125in</Width>
<DataSets>
<DataSet Name="pubs">
<Fields>
<Field Name="au_id">
<DataField>au_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="au_lname">
<DataField>au_lname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="au_fname">
<DataField>au_fname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="phone">
<DataField>phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="address">
<DataField>address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="city">
<DataField>city</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="state">
<DataField>state</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="zip">
<DataField>zip</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="contract">
<DataField>contract</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>pubs</DataSourceName>
<CommandText>select * from authors</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>7a57b47c-d471-411b-abea-bc7acc3f1a16</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nytr0x" <nytr0x@.yahoo.com> wrote in message
news:%23iNlXG0aEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Hi I've been puzzling over this one for ages, Imagine a dataset like:
> ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
> 1-Jan 07:00 1-Jan 15:00 00001 Fred S
> 2-Jan 07:00 2-Jan 15:00 00001 Fred S
> 3-Jan 07:00 3-Jan 15:00 00001 Fred
> 4-Jan 07:00 4-Jan 15:00 00001 Fred S
> 5-Jan 07:00 5-Jan 15:00 00001 Fred S
> 6-Jan 07:00 6-Jan 15:00 00001 Fred
> 1-Jan 07:00 1-Jan 15:00 00002 Jane
> 2-Jan 07:00 2-Jan 15:00 00002 Jane S
> 3-Jan 07:00 3-Jan 15:00 00002 Jane S
> 4-Jan 07:00 4-Jan 15:00 00002 Jane S
> 5-Jan 07:00 5-Jan 15:00 00002 Jane S
> 6-Jan 07:00 6-Jan 15:00 00002 Jane
> I would like to group by consecutive absence codes to generate a report
> like:
> Fred had 2 occurrences of AbsenceCode S
> 1-Jan 07:00 to 2-Jan 15:00
> 4-Jan 07:00 to 5-Jan 15:00
> Jane had 1 occurrence of AbsenceCode S
> 2-Jan 07:00 to 5-Jan 15:00
> I have many more codes, and many more records, How can I group by each
> occurrence like this?
> I've tried using RunningValue with Previous(AbsenceCode) but RS won't
allow
> nested aggregate functions.
> Thanks in advance,
> N.
>|||I haven't had chance to test your example yet. But wouldn't this just group
each AbsenceCode for each Name? In the example given I want two groups for
the AbsenceCode S for Fred and 1 group for Jane.
Thanks,
N
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%23VsZSq0aEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Create two groups - one on Name and another one on AbsenceCode. See the
> attached example that goes against local pubs database.
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>1in</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>15</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>state</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>14</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>city</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>13</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>address</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="state">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>state</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="city">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>city</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="au_fname">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>au_fname</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!au_fname.Value & " " &
> Fields!au_lname.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="address">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>address</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!address.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>pubs</DataSetName>
> <Width>5.375in</Width>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!state.Value & " [" &
> RunningValue(Fields!state.Value, Count, "table1_Group1") & "
> authors]"</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!state.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox13">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox13</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox14">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox14</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!city.Value & " [" &
> RunningValue(Fields!city.Value, Count, "table1_Group2") & "
> authors]"</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox15">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox15</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox16">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox16</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group2">
> <GroupExpressions>
> <GroupExpression>=Fields!city.Value</GroupExpression>
> </GroupExpressions>
> <Parent>=Fields!state.Value</Parent>
> </Grouping>
> </TableGroup>
> </TableGroups>
> <TableColumns>
> <TableColumn>
> <Width>1.625in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.375in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.25in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.125in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>4.75in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="pubs">
> <rd:DataSourceID>d743d813-756c-4688-850b-f2e6deda54b0</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>initial catalog=pubs</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>7.125in</Width>
> <DataSets>
> <DataSet Name="pubs">
> <Fields>
> <Field Name="au_id">
> <DataField>au_id</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="au_lname">
> <DataField>au_lname</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="au_fname">
> <DataField>au_fname</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="phone">
> <DataField>phone</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="address">
> <DataField>address</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="city">
> <DataField>city</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="state">
> <DataField>state</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="zip">
> <DataField>zip</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="contract">
> <DataField>contract</DataField>
> <rd:TypeName>System.Boolean</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>pubs</DataSourceName>
> <CommandText>select * from authors</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>7a57b47c-d471-411b-abea-bc7acc3f1a16</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <Language>en-US</Language>
> </Report>
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Nytr0x" <nytr0x@.yahoo.com> wrote in message
> news:%23iNlXG0aEHA.3792@.TK2MSFTNGP09.phx.gbl...
> > Hi I've been puzzling over this one for ages, Imagine a dataset like:
> >
> > ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
> > 1-Jan 07:00 1-Jan 15:00 00001 Fred S
> > 2-Jan 07:00 2-Jan 15:00 00001 Fred S
> > 3-Jan 07:00 3-Jan 15:00 00001 Fred
> > 4-Jan 07:00 4-Jan 15:00 00001 Fred S
> > 5-Jan 07:00 5-Jan 15:00 00001 Fred S
> > 6-Jan 07:00 6-Jan 15:00 00001 Fred
> > 1-Jan 07:00 1-Jan 15:00 00002 Jane
> > 2-Jan 07:00 2-Jan 15:00 00002 Jane S
> > 3-Jan 07:00 3-Jan 15:00 00002 Jane S
> > 4-Jan 07:00 4-Jan 15:00 00002 Jane S
> > 5-Jan 07:00 5-Jan 15:00 00002 Jane S
> > 6-Jan 07:00 6-Jan 15:00 00002 Jane
> >
> > I would like to group by consecutive absence codes to generate a report
> > like:
> >
> > Fred had 2 occurrences of AbsenceCode S
> > 1-Jan 07:00 to 2-Jan 15:00
> > 4-Jan 07:00 to 5-Jan 15:00
> > Jane had 1 occurrence of AbsenceCode S
> > 2-Jan 07:00 to 5-Jan 15:00
> >
> > I have many more codes, and many more records, How can I group by each
> > occurrence like this?
> > I've tried using RunningValue with Previous(AbsenceCode) but RS won't
> allow
> > nested aggregate functions.
> >
> > Thanks in advance,
> > N.
> >
> >
>|||Yes, ths will first group by name and then by absense code. If you want to
do one group per absense code for each name, you'd have to do that in SQL.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nytr0x" <nytr0x@.yahoo.com> wrote in message
news:OCioZO9aEHA.3692@.TK2MSFTNGP09.phx.gbl...
> I haven't had chance to test your example yet. But wouldn't this just
group
> each AbsenceCode for each Name? In the example given I want two groups
for
> the AbsenceCode S for Fred and 1 group for Jane.
> Thanks,
> N
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:%23VsZSq0aEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Create two groups - one on Name and another one on AbsenceCode. See the
> > attached example that goes against local pubs database.
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> >
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> > tion"
> >
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Table Name="table1">
> > <Height>1in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox4">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>15</ZIndex>
> > <rd:DefaultName>textbox4</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>state</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>14</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>city</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>13</ZIndex>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>Name</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>12</ZIndex>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>address</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="state">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>3</ZIndex>
> > <rd:DefaultName>state</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="city">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>city</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="au_fname">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>au_fname</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!au_fname.Value & " " &
> > Fields!au_lname.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="address">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <rd:DefaultName>address</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!address.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>pubs</DataSetName>
> > <Width>5.375in</Width>
> > <TableGroups>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox12">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>11</ZIndex>
> > <rd:DefaultName>textbox12</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!state.Value & " [" &
> > RunningValue(Fields!state.Value, Count, "table1_Group1") & "
> > authors]"</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox11">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>10</ZIndex>
> > <rd:DefaultName>textbox11</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox5">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>9</ZIndex>
> > <rd:DefaultName>textbox5</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox6">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>8</ZIndex>
> > <rd:DefaultName>textbox6</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> > <GroupExpression>=Fields!state.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox13">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>7</ZIndex>
> > <rd:DefaultName>textbox13</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox14">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>6</ZIndex>
> > <rd:DefaultName>textbox14</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!city.Value & " [" &
> > RunningValue(Fields!city.Value, Count, "table1_Group2") & "
> > authors]"</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox15">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>5</ZIndex>
> > <rd:DefaultName>textbox15</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox16">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>4</ZIndex>
> > <rd:DefaultName>textbox16</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group2">
> > <GroupExpressions>
> > <GroupExpression>=Fields!city.Value</GroupExpression>
> > </GroupExpressions>
> > <Parent>=Fields!state.Value</Parent>
> > </Grouping>
> > </TableGroup>
> > </TableGroups>
> > <TableColumns>
> > <TableColumn>
> > <Width>1.625in</Width>
> > </TableColumn>
> > <TableColumn>
> > <Width>1.375in</Width>
> > </TableColumn>
> > <TableColumn>
> > <Width>1.25in</Width>
> > </TableColumn>
> > <TableColumn>
> > <Width>1.125in</Width>
> > </TableColumn>
> > </TableColumns>
> > </Table>
> > </ReportItems>
> > <Style />
> > <Height>4.75in</Height>
> > </Body>
> > <TopMargin>1in</TopMargin>
> > <DataSources>
> > <DataSource Name="pubs">
> >
> > <rd:DataSourceID>d743d813-756c-4688-850b-f2e6deda54b0</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>initial catalog=pubs</ConnectString>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <Width>7.125in</Width>
> > <DataSets>
> > <DataSet Name="pubs">
> > <Fields>
> > <Field Name="au_id">
> > <DataField>au_id</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="au_lname">
> > <DataField>au_lname</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="au_fname">
> > <DataField>au_fname</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="phone">
> > <DataField>phone</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="address">
> > <DataField>address</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="city">
> > <DataField>city</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="state">
> > <DataField>state</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="zip">
> > <DataField>zip</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="contract">
> > <DataField>contract</DataField>
> > <rd:TypeName>System.Boolean</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>pubs</DataSourceName>
> > <CommandText>select * from authors</CommandText>
> > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <LeftMargin>1in</LeftMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>7a57b47c-d471-411b-abea-bc7acc3f1a16</rd:ReportID>
> > <BottomMargin>1in</BottomMargin>
> > <Language>en-US</Language>
> > </Report>
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Nytr0x" <nytr0x@.yahoo.com> wrote in message
> > news:%23iNlXG0aEHA.3792@.TK2MSFTNGP09.phx.gbl...
> > > Hi I've been puzzling over this one for ages, Imagine a dataset like:
> > >
> > > ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
> > > 1-Jan 07:00 1-Jan 15:00 00001 Fred S
> > > 2-Jan 07:00 2-Jan 15:00 00001 Fred S
> > > 3-Jan 07:00 3-Jan 15:00 00001 Fred
> > > 4-Jan 07:00 4-Jan 15:00 00001 Fred S
> > > 5-Jan 07:00 5-Jan 15:00 00001 Fred S
> > > 6-Jan 07:00 6-Jan 15:00 00001 Fred
> > > 1-Jan 07:00 1-Jan 15:00 00002 Jane
> > > 2-Jan 07:00 2-Jan 15:00 00002 Jane S
> > > 3-Jan 07:00 3-Jan 15:00 00002 Jane S
> > > 4-Jan 07:00 4-Jan 15:00 00002 Jane S
> > > 5-Jan 07:00 5-Jan 15:00 00002 Jane S
> > > 6-Jan 07:00 6-Jan 15:00 00002 Jane
> > >
> > > I would like to group by consecutive absence codes to generate a
report
> > > like:
> > >
> > > Fred had 2 occurrences of AbsenceCode S
> > > 1-Jan 07:00 to 2-Jan 15:00
> > > 4-Jan 07:00 to 5-Jan 15:00
> > > Jane had 1 occurrence of AbsenceCode S
> > > 2-Jan 07:00 to 5-Jan 15:00
> > >
> > > I have many more codes, and many more records, How can I group by each
> > > occurrence like this?
> > > I've tried using RunningValue with Previous(AbsenceCode) but RS won't
> > allow
> > > nested aggregate functions.
> > >
> > > Thanks in advance,
> > > N.
> > >
> > >
> >
> >
>