Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.

Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.sql

grouping on date without time

Hello,

I have the following problem.

I a making reports based on a database that i do not control.

In that database i have a table with statistical data including a field with datetime informtion.

The format of the data I receive is "5/04/2007 7:43:27".

In my report i want to create a group which groups my event by date : "05/04/2007"

In my output i always get subgroups by date & time so "5/04/2007 7:43:27", "5/04/2007 7:43:28", ....

How can i group only on the date.

Vincent

Hello Vincent,

Right click on your group row for the dates, and select 'Edit Group...' In the 'Group on:' section, change your expression from =Fields!DateField.Value to =Format(Fields!DateField.Value, "MM/dd/yyyy")

Jarret

|||

Use this expression for grouping:

DateValue(Fields!DateField.Value)

This will set the time part of the datetime field to 00:00:00 and use only the date part. This gives better performance than formatting or any other solution.

Shyam

|||

Vincent,

I forgot to mention this in my last post...

You will probably want to show the date without the time as well, so just put the format statement as your textbox's expression in the group row. You could use Shyam's suggestion for the DateValue function (I didn't know it is more efficient, but I haven't noticed any performance degredation from using format), I just use the format for simplicity; both the group on expression and the textbox expression being shown will be the same.

Jarret

|||

Jarrett,

As you know, any operation (be it grouping or sorting or whatever) based on string is going to be more costlier (if not much more) than other datatypes and unfortunately Format function returns a string though we can still convert it using CDate and use that expression for grouping (which again becomes a 2 level conversion). Maybe there wont be a significant difference in performance unless there are millions of records.

Shyam

|||this is helpful
|||hi Lifesavers

I am getting this default format
3/6/2007 12:00:00 AM|||somehow I am using this following format and it seems to work.

=FormatDateTime(Fields!LOGINDTTIME.Value, 3)

now i am not able to sort it properly ...when i do sort it give following output

03/01/07 01:51:09 pm 03/01/07 11:46:35 am 03/01/07 04:42:53 pm 03/02/07 12:40:08 pm 03/01/07 03:56:04 pm

Please notice 03/01/07 11:46:35 am on second line, it is sorting it on numeric value not in am /pm...any ideas?
|||

Hello Anand,

If you want to display as 03/06/07 12:00:00 AM, you could use this: =Format(Fields!LOGINDTTIME.Value, "MM/dd/yy hh:mms tt"). The FormatDateTime uses your computer's regional settings to display the date/time.

As for the sorting on the table, you need to go to the Properties of the table, then click on the Sorting tab. Instead of using the Format in here, you should sort by the value in the field. You should be sorting by Fields!LOGINDTTIME.Value.

Jarret

|||

The best way to do it is to format the text box where you keep your date. (If it is a table it still will be a text box whithin a table)

So :

-> Right click -> Properties -> Format (tab) -> Format code: -> Ellipsis button [...] ->

... and here you have all kind of Standard formating e.g. date,time, currency ... Good Luck

|||

Hello Bernardo,

One of Anand's questions was how to format it like this 03/06/07 12:00:00 AM. Since there is no standard format that matches this, a custom format had to be used.

I'm not so sure that either way is a 'best' way (putting in a format code through the properties or using the format function), aren't they both doing the same thing? At least with the Format function, you can see the format directly in the textbox without navigating through the dialog box to find it or looking in the properties window.

Jarret

|||thanks Jarret,

now formating and sorting on date works as I wanted

thanks,
anand

Grouping in List Control

Hi,

I am new with RS2000. I am working on a Reporting application. I have some Application names on X-axis of a bar chart say they are 90 in number. I want to group this chart in a list control in such a way that in each portion the chart must contain 15 application names if suppose there are 90 application names.

Add a (detail) group to the list with the following grouping expression:

=Int((RowNumber(Nothing)-1)/15)

This should result in the desired grouping of 15 items per (repeating) list instance. Then just put the chart inside the list.

-- Robert

|||But i need to add a list fir that.are there any other work arounds?

Grouping in List Control

Hi,

I am new with RS2000. I am working on a Reporting application. I have some Application names on X-axis of a bar chart say they are 90 in number. I want to group this chart in a list control in such a way that in each portion the chart must contain 15 application names if suppose there are 90 application names.

Add a (detail) group to the list with the following grouping expression:

=Int((RowNumber(Nothing)-1)/15)

This should result in the desired grouping of 15 items per (repeating) list instance. Then just put the chart inside the list.

-- Robert

|||But i need to add a list fir that.are there any other work arounds?sql

Grouping in columns rather than rows using table control?

Is there a way to transform the table object to display group data in columns
instead of rows? Here is my example:
Report services table can do this when grouping on YEAR
[1 GROUP Header (YEAR)
[Header
[ BODY Parameter1 Parameter 2 Parameter 3
[FOOTER
[1 GROUP Footer (SUM)
Example
Year 2000
Mike John Mary
5 1 4
5 2 2
SUM 10 3 6
Year 2001
Mike John Mary
1 6 5
2 2 2
SUM 3 8 7
What I want is this:
[ Group Header ] [Table Header] [DATA] [Table Footer] [Group
Footer]
YEAR Parameter 1
SUM
Parameter 2
Parameter 3
2000 SUM 2001 SUM
Mike 5 5 10 1 2 3
John 1 2 3 6 2 8
Mary 4 2 6 5 2 7
So the idea is to group by Year but display the SUMs in a column not in a
row. I just can't figure out how to use the Matrix control, I want to use the
table control functionality but with column output.
Thanksyou can use a matrix to do just that
"Ramez" wrote:
> Is there a way to transform the table object to display group data in columns
> instead of rows? Here is my example:
> Report services table can do this when grouping on YEAR
> [1 GROUP Header (YEAR)
> [Header
> [ BODY Parameter1 Parameter 2 Parameter 3
> [FOOTER
> [1 GROUP Footer (SUM)
> Example
> Year 2000
> Mike John Mary
> 5 1 4
> 5 2 2
> SUM 10 3 6
> Year 2001
> Mike John Mary
> 1 6 5
> 2 2 2
> SUM 3 8 7
> What I want is this:
> [ Group Header ] [Table Header] [DATA] [Table Footer] [Group
> Footer]
> YEAR Parameter 1
> SUM
> Parameter 2
> Parameter 3
> 2000 SUM 2001 SUM
> Mike 5 5 10 1 2 3
> John 1 2 3 6 2 8
> Mary 4 2 6 5 2 7
> So the idea is to group by Year but display the SUMs in a column not in a
> row. I just can't figure out how to use the Matrix control, I want to use the
> table control functionality but with column output.
> Thanks

Grouping Data in Matrix like Table control

All,

Is it possible to Group data in a Matrix exactly similar to Table Control. For example, my table control would group data as follows:

Region Country City $ales

North America

USA

Chicago 4 MM

LA 10 MM

NYK 6 MM

Canada

Toronto 4 MM

while the matrix would display as:

North America USA Chicago 4MM

LA

NYK

Canada Toronto ...

Do you see the problem? The matrix is starting the subgroup at the same level as the parent group. How do I make a sub group start at the row below the parent group row in matrix just like in the table above?

Any one?

On a related note, How can I add the "heading" to the dynamic rows in the matrix similar to table:

Region Country City Sales

North America 10 MM

USA 8 MM

Chicago 2 MM

LA 2 MM

NYK 4 MM

Canada 2 MM

Toronto 2 MM

Can a matrix be formatted as above, it can be in a table but I am not sure if this can be achieved with Matrix?

|||This issue is now resolved. The SubTotal property of each Row/Column grouping in a Matrix could be set to "Before/After" the details cells to achieve the desired format. Matrix is an amazing tool created by the SSRS team but it does require you to fiddle with it a bit before being used effectively. It saved me from having to write a Cross Tab query in dynamic SQL.|||

Were you able to get the headings on the columns? That's what I've been fighting with, but I haven't been able to figure it out.

Thanks,

Marianne

|||

DotNet_Guy wrote:

Matrix is an amazing tool created by the SSRS team but it does require you to fiddle with it a bit before being used effectively.

SSRS 2008 will include a Tablix (cross between a table and a matrix) due to grouping issues and the like that couldn't previously be accomplished easily.

sql

Grouping Data in Matrix like Table control

All,

Is it possible to Group data in a Matrix exactly similar to Table Control. For example, my table control would group data as follows:

Region Country City $ales

North America

USA

Chicago 4 MM

LA 10 MM

NYK 6 MM

Canada

Toronto 4 MM

while the matrix would display as:

North America USA Chicago 4MM

LA

NYK

Canada Toronto ...

Do you see the problem? The matrix is starting the subgroup at the same level as the parent group. How do I make a sub group start at the row below the parent group row in matrix just like in the table above?

Any one?

On a related note, How can I add the "heading" to the dynamic rows in the matrix similar to table:

Region Country City Sales

North America 10 MM

USA 8 MM

Chicago 2 MM

LA 2 MM

NYK 4 MM

Canada 2 MM

Toronto 2 MM

Can a matrix be formatted as above, it can be in a table but I am not sure if this can be achieved with Matrix?

|||This issue is now resolved. The SubTotal property of each Row/Column grouping in a Matrix could be set to "Before/After" the details cells to achieve the desired format. Matrix is an amazing tool created by the SSRS team but it does require you to fiddle with it a bit before being used effectively. It saved me from having to write a Cross Tab query in dynamic SQL.|||

Were you able to get the headings on the columns? That's what I've been fighting with, but I haven't been able to figure it out.

Thanks,

Marianne

|||

DotNet_Guy wrote:

Matrix is an amazing tool created by the SSRS team but it does require you to fiddle with it a bit before being used effectively.

SSRS 2008 will include a Tablix (cross between a table and a matrix) due to grouping issues and the like that couldn't previously be accomplished easily.

Grouping Data in Matrix like Table control

All,

Is it possible to Group data in a Matrix exactly similar to Table Control. For example, my table control would group data as follows:

Region Country City $ales

North America

USA

Chicago 4 MM

LA 10 MM

NYK 6 MM

Canada

Toronto 4 MM

while the matrix would display as:

North America USA Chicago 4MM

LA

NYK

Canada Toronto ...

Do you see the problem? The matrix is starting the subgroup at the same level as the parent group. How do I make a sub group start at the row below the parent group row in matrix just like in the table above?

Any one?

On a related note, How can I add the "heading" to the dynamic rows in the matrix similar to table:

Region Country City Sales

North America 10 MM

USA 8 MM

Chicago 2 MM

LA 2 MM

NYK 4 MM

Canada 2 MM

Toronto 2 MM

Can a matrix be formatted as above, it can be in a table but I am not sure if this can be achieved with Matrix?

|||This issue is now resolved. The SubTotal property of each Row/Column grouping in a Matrix could be set to "Before/After" the details cells to achieve the desired format. Matrix is an amazing tool created by the SSRS team but it does require you to fiddle with it a bit before being used effectively. It saved me from having to write a Cross Tab query in dynamic SQL.|||

Were you able to get the headings on the columns? That's what I've been fighting with, but I haven't been able to figure it out.

Thanks,

Marianne

|||

DotNet_Guy wrote:

Matrix is an amazing tool created by the SSRS team but it does require you to fiddle with it a bit before being used effectively.

SSRS 2008 will include a Tablix (cross between a table and a matrix) due to grouping issues and the like that couldn't previously be accomplished easily.

grouping and sorting in matrix control

I have a dataset returned from sql server that can be represented for the
purpose of this discussion with 2 columns. From the server all the data is
sorted first by column 1 and then by column 2 so that the resultset looks
like the following:
column1, column2, column3
a, 1/1/2007, 10
a, 2/1/2007, 30
a, 3/1/2007, 15
b, 10/1/2006, 5
b, 11/1/2006, 1
b, 12/1/2006, 100
b, 1/1/2007, 10
b, 2/1/2007, 9
c, 11/1/2006, 22
c, 12/1/2006, 33
c, 1/1/2007, 44
When I put this data into a matrix with the dates making the columns and
column1 values for each row I get the following
1/1/2007 2/1/2007 3/1/2007 10/1/2006 11/1/2006 12/1/2006
a 10 30 15
b 10 9 5 1
100
c 44 22
33
what I want is the following:
10/1/2006 11/1/2006 12/1/2006 1/1/2007 2/1/2007 3/1/2007
a 10
30 15
b 5 1 100 10 9
c 22 33 44
With the dates sorted. I know I can do it by changing the stored proc but
that opens up all sorts of issues with other things. Is there any way to get
the data looking like I want using reporting services and not modifying the
stored proc?
thanksOn Feb 28, 2:11 pm, Brian <B...@.discussions.microsoft.com> wrote:
> I have a dataset returned from sql server that can be represented for the
> purpose of this discussion with 2 columns. From the server all the data is
> sorted first by column 1 and then by column 2 so that the resultset looks
> like the following:
> column1, column2, column3
> a, 1/1/2007, 10
> a, 2/1/2007, 30
> a, 3/1/2007, 15
> b, 10/1/2006, 5
> b, 11/1/2006, 1
> b, 12/1/2006, 100
> b, 1/1/2007, 10
> b, 2/1/2007, 9
> c, 11/1/2006, 22
> c, 12/1/2006, 33
> c, 1/1/2007, 44
> When I put this data into a matrix with the dates making the columns and
> column1 values for each row I get the following
> 1/1/2007 2/1/2007 3/1/2007 10/1/2006 11/1/2006 12/1/2006
> a 10 30 15
> b 10 9 5 1
> 100
> c 44 22
> 33
> what I want is the following:
> 10/1/2006 11/1/2006 12/1/2006 1/1/2007 2/1/2007 3/1/2007
> a 10
> 30 15
> b 5 1 100 10 9
> c 22 33 44
> With the dates sorted. I know I can do it by changing the stored proc but
> that opens up all sorts of issues with other things. Is there any way to get
> the data looking like I want using reporting services and not modifying the
> stored proc?
> thanks
>From your results, it looks like column2 is sorting aphabetically (I'm
assuming that column2 is not defined as a datetime field in the report
or dataset). You should be able to use the conversion function CDate()
in your sort expression. Something like this should work: CDate(Fields!
column2.Value) and the direction should be ascending. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer