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 methods in a DLL, where is the break point?

We have roughly 5000 methods generated into C# that need to get assembled into DLLs and then put out as assmeblies in a SQL Server database. We then intend on creating user-defined functions to call many of these directly.

We can break these 5000 down into bundles and make about 10 DLLs, OR we can make 5000 DLLs. What are the concerns from a run-time perspective ?

If we go with 5000 DLLs, will an end user performing a few ad-hoc queries that use these user-defined functions experience poorer performance than if we had 10 large DLLs ?

Any input is appreciated. We'll be trying to assess the performance ourselves, but I'm looking for any stories about using large numbers of DLLs.

Thanks

Scott

? I'll let someone else comment on the possible performance implications, but I'd personally be more concerned with management. 5000 methods in a single DLL is definitely an issue because if you change one method you'll have to redeploy all of them. Likewise with 5000 DLLs; I think you should try to group related methods in their own DLLs so that you can deploy and update them together. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Scott Flory@.discussions.microsoft.com> wrote in message news:b3ecc08c-4d63-43f2-a09c-3271eb35a8cf@.discussions.microsoft.com... We have roughly 5000 methods generated into C# that need to get assembled into DLLs and then put out as assmeblies in a SQL Server database. We then intend on creating user-defined functions to call many of these directly. We can break these 5000 down into bundles and make about 10 DLLs, OR we can make 5000 DLLs. What are the concerns from a run-time perspective ? If we go with 5000 DLLs, will an end user performing a few ad-hoc queries that use these user-defined functions experience poorer performance than if we had 10 large DLLs ? Any input is appreciated. We'll be trying to assess the performance ourselves, but I'm looking for any stories about using large numbers of DLLs. Thanks Scott

Grouping Data with Weekly break out?

Hi All,
I'm trying to figure out and learn how to do the following:
/*I have the following query which works:*/
SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber]
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
My question here is how can I get a wly breakout of the COUNT(PoNumber)?
I want a column for each w and the number of PONumbers Per Datasource and
NamePrep for that w.
Example Output:
Service Line | PO Created By | Total Of PONumber | 3/5/2005 |
3/12/2005 | ETC...
MNS JOHN 10
MNS ERIC 25
FMS CARL 8
Hope my question makes sense :)
John.If you had a calendar table, this would be easier, but if not, you need to
know the columns you want, or use dynamic SQL...
Select DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber],
Sum(Case When ReqSubmitDate
Between '03/ 01/2005' AND '03/8/2005' Then 1 End) Wk1Count,
Sum(Case When ReqSubmitDate
Between '03/ 09/2005' AND '03/16/2005' Then 1 End) Wk2Count,
Sum(Case When ReqSubmitDate
Between '03/ 17/2005' AND '03/24/2005' Then 1 End) Wk3Count,
Sum(Case When ReqSubmitDate
Between '03/ 25/2005' AND '03/31/2005' Then 1 End) Wk4Count
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
If you want it dynamic, you have to write code to dynamic construct an SQL
statement like the one above, based on the date ranges you pass it, and then
execute that SQL Statement using EXECUTE, or sp_ExecuteSQL() functions
"John Rugo" wrote:

> Hi All,
> I'm trying to figure out and learn how to do the following:
> /*I have the following query which works:*/
> SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
> COUNT(PoNumber) AS [Total Of PONumber]
> FROM OPW
> WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
> GROUP BY DataSource, NamePrep
> My question here is how can I get a wly breakout of the COUNT(PoNumber)
?
> I want a column for each w and the number of PONumbers Per Datasource a
nd
> NamePrep for that w.
> Example Output:
> Service Line | PO Created By | Total Of PONumber | 3/5/2005
|
> 3/12/2005 | ETC...
> MNS JOHN 10
> MNS ERIC 25
> FMS CARL 8
> Hope my question makes sense :)
> John.
>
>sql

Group Page number

I have a report which is group by invoice no. For each new invoice no, page
break is inserted. I want to set page number for each invoice (e.g. Page 1
of 1). But if the invoice has so many charge line that occupy more than 1
page, I want to set page 1 as Page 1 of 10, page 2 as Page 2 of 10 and so on
...
HOW CAN I DO IT ? Also the page number will be reset to 1 for new invoice.
Another question is that, I have sub-total for each new invoice which is
placed in Group footer. In crystal report, I can fix the footer position.
So the position of sub-total will not be altered (move up and down) no matter
how many charge lines under this invoice. Is RS has this feature ?Here is a reference to the answer:
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&query=reset+page+number+&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=030990a7-1731-4e50-9e0b-4ed69ac1bd63&mid=2bdeaef4-35b0-49ed-86c6-5c69a155be38
"May Liu" wrote:
> I have a report which is group by invoice no. For each new invoice no, page
> break is inserted. I want to set page number for each invoice (e.g. Page 1
> of 1). But if the invoice has so many charge line that occupy more than 1
> page, I want to set page 1 as Page 1 of 10, page 2 as Page 2 of 10 and so on
> ...
> HOW CAN I DO IT ? Also the page number will be reset to 1 for new invoice.
> Another question is that, I have sub-total for each new invoice which is
> placed in Group footer. In crystal report, I can fix the footer position.
> So the position of sub-total will not be altered (move up and down) no matter
> how many charge lines under this invoice. Is RS has this feature ?

group page break adding blank pages

I have a report that I am grouping the data and setting the page to break
after each group (check box "Page break at end"). When I View Report in
Preview, the report looks perfect. Four pages, four groups. In this case,
each group fits on one page. When I click on the magnifying glass to see the
print view, it returns 8 pages. There is a completely blank page (except for
page header) on every even page. This is the case when a group goes to two
pages as well. If I run the report and get 5 pages with 4 groups, I still
get 10 pages when viewing for printing.
How can I keep the page break and get rid of the extra page? This is really
bad when the report is over 1000 pages... making it over 2000 pages for
nothing."SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
news:0F2CDB4D-6130-4D9B-A382-028941BCA3CF@.microsoft.com...
>I have a report that I am grouping the data and setting the page to break
> after each group (check box "Page break at end"). When I View Report in
> Preview, the report looks perfect. Four pages, four groups. In this
> case,
> each group fits on one page. When I click on the magnifying glass to see
> the
> print view, it returns 8 pages.
Hi Sharin,
Try to figure out what element is causing this behavior. In general if you
set to invisible only one element and everything will get in order. Find the
wrong element and then look for problems in it.
Regards,
--
Martin Kulov
http://www.codeattest.com/blogs/martin
MCAD Charter Member
MCSD.NET Early Achiever
MCSD|||I tried setting each element to hidden one at a time and nothing changed.
I finally got it to stop happening though. My page width property is 5.5
inches. For an 8 inch page with half inch margins. I'm wasting all that
space. But if I make my page 5.5 inches (and therefore my table has to fit
in that), then it doesn't happen anymore. Any suggestions on why this is
happening?
"Martin Kulov" wrote:
> "SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
> news:0F2CDB4D-6130-4D9B-A382-028941BCA3CF@.microsoft.com...
> >I have a report that I am grouping the data and setting the page to break
> > after each group (check box "Page break at end"). When I View Report in
> > Preview, the report looks perfect. Four pages, four groups. In this
> > case,
> > each group fits on one page. When I click on the magnifying glass to see
> > the
> > print view, it returns 8 pages.
> Hi Sharin,
> Try to figure out what element is causing this behavior. In general if you
> set to invisible only one element and everything will get in order. Find the
> wrong element and then look for problems in it.
> Regards,
> --
> Martin Kulov
> http://www.codeattest.com/blogs/martin
> MCAD Charter Member
> MCSD.NET Early Achiever
> MCSD
>
>|||I'm having the same issue. I have a group that does a page break the
customer. That field is hidden. The only way I can get the blank page to
not print is to remove the page break in the table group. Is there a way to
have a page break and not have that blank page print?
"Martin Kulov" wrote:
> "SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
> news:0F2CDB4D-6130-4D9B-A382-028941BCA3CF@.microsoft.com...
> >I have a report that I am grouping the data and setting the page to break
> > after each group (check box "Page break at end"). When I View Report in
> > Preview, the report looks perfect. Four pages, four groups. In this
> > case,
> > each group fits on one page. When I click on the magnifying glass to see
> > the
> > print view, it returns 8 pages.
> Hi Sharin,
> Try to figure out what element is causing this behavior. In general if you
> set to invisible only one element and everything will get in order. Find the
> wrong element and then look for problems in it.
> Regards,
> --
> Martin Kulov
> http://www.codeattest.com/blogs/martin
> MCAD Charter Member
> MCSD.NET Early Achiever
> MCSD
>
>sql

Group Name in Page Header

When using Grouping, I need to display the value of the grouped field
in the page header (I'm also inserting a page break at the end of each
group). Is this possible?Yes this is possible in your case. Assuming you have a textbox called
GroupName in the grouping header which shows the current value of the
grouping, you can just add another textbox in the page header which
references the value of the GroupName textbox:
=ReportItems!GroupName.Value
Note: only the ReportItems collection is accessible in the page
headers/footers, but not the Fields collection.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"J Chang" <julia.chang@.maritz.com> wrote in message
news:946c3e64.0410221341.3018ec18@.posting.google.com...
> When using Grouping, I need to display the value of the grouped field
> in the page header (I'm also inserting a page break at the end of each
> group). Is this possible?

Group Footer on Next Page

Anyone know if there is a way to keep at least one detail row with the group
footer at page break?
It doesn't make for a very nice report when some pages begin with a group
footer and no details to go with it.
Thanks to anyone that can help with this.I am having the same problem. MS documentation says that "a table group
footer and the last previous detail row before the footer are kept together".
This is not happening for me. I get the group footer on a page by itself
when the last table detail line ends a group.
Any ideas?
"Alison" wrote:
> Anyone know if there is a way to keep at least one detail row with the group
> footer at page break?
> It doesn't make for a very nice report when some pages begin with a group
> footer and no details to go with it.
> Thanks to anyone that can help with this.
>
>|||Sorry forgot to mention: For me, the table group footers page breaks look
great when previewing in rs, but after saving to PDF, then the group footer
is not "kept together" with the last detail row.
Help?
"LesW" wrote:
> I am having the same problem. MS documentation says that "a table group
> footer and the last previous detail row before the footer are kept together".
> This is not happening for me. I get the group footer on a page by itself
> when the last table detail line ends a group.
> Any ideas?
> "Alison" wrote:
> > Anyone know if there is a way to keep at least one detail row with the group
> > footer at page break?
> > It doesn't make for a very nice report when some pages begin with a group
> > footer and no details to go with it.
> >
> > Thanks to anyone that can help with this.
> >
> >
> >

Group Case Sensitive

It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?

I am running SQL Server 2000 and the database that I am querying is not case sensitive.

In report designer, edit the dataset properties, go to the "Data Options" tab and for the Case Sensitivity property select "False" instead of "Auto".

-- Robert

|||Made the suggested change and it didn't affect the report grouping.
After a little experiment, I can say the group break is definitely case sensitive.|||

Note: you can also just change the grouping expression to make it case-insensitive by applying the LCase() function which converts the string to lower case:

=LCase(Fields!Group.Value)

-- Robert

|||Just now saw the LCase response and tested it on the problem report. It did solve the problem!|||

Just a clarification on what I have found in this case.

Since DataRegion/DataSet/Grouing can be used for scoping puproses I thought it would be good to broaden this case sensitivity to include all of these.

What I found was the following on how RS compares names (short form 'I' for Case Insensitive and 'S' for Case Sensitive):

a) Grouping - S ("a" is different then "A")

b) DataSet - I

c) DataRegion - I

d) Grouping to DataSet - S (Grouping "A" can exist even if DataSet "a" exists)

e) Grouping to DataRegion - I

f) DataSet to DataRegion - I

I am perplexed as to why situations a) and especially d) exist. But it appears to be the way things work.

DK

|||Dataset fields are also S. It's probably because the report definition is compiled to a .NET assembly but I am all for (I).|||

Teo is regarding case-sensitiveness. The main reason is how the ReportObjectModel works and that making scope names case-insensitive would have a negative overall performance impact.

-- Robert

Group Case Sensitive

It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?

I am running SQL Server 2000 and the database that I am querying is not case sensitive.

In report designer, edit the dataset properties, go to the "Data Options" tab and for the Case Sensitivity property select "False" instead of "Auto".

-- Robert

|||Made the suggested change and it didn't affect the report grouping.
After a little experiment, I can say the group break is definitely case sensitive.|||

Note: you can also just change the grouping expression to make it case-insensitive by applying the LCase() function which converts the string to lower case:

=LCase(Fields!Group.Value)

-- Robert

|||Just now saw the LCase response and tested it on the problem report. It did solve the problem!|||

Just a clarification on what I have found in this case.

Since DataRegion/DataSet/Grouing can be used for scoping puproses I thought it would be good to broaden this case sensitivity to include all of these.

What I found was the following on how RS compares names (short form 'I' for Case Insensitive and 'S' for Case Sensitive):

a) Grouping - S ("a" is different then "A")

b) DataSet - I

c) DataRegion - I

d) Grouping to DataSet - S (Grouping "A" can exist even if DataSet "a" exists)

e) Grouping to DataRegion - I

f) DataSet to DataRegion - I

I am perplexed as to why situations a) and especially d) exist. But it appears to be the way things work.

DK

|||Dataset fields are also S. It's probably because the report definition is compiled to a .NET assembly but I am all for (I).|||

Teo is regarding case-sensitiveness. The main reason is how the ReportObjectModel works and that making scope names case-insensitive would have a negative overall performance impact.

-- Robert

Group Case Sensitive

It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?

I am running SQL Server 2000 and the database that I am querying is not case sensitive.

In report designer, edit the dataset properties, go to the "Data Options" tab and for the Case Sensitivity property select "False" instead of "Auto".

-- Robert

|||Made the suggested change and it didn't affect the report grouping.
After a little experiment, I can say the group break is definitely case sensitive.
|||

Note: you can also just change the grouping expression to make it case-insensitive by applying the LCase() function which converts the string to lower case:

=LCase(Fields!Group.Value)

-- Robert

|||Just now saw the LCase response and tested it on the problem report. It did solve the problem!|||

Just a clarification on what I have found in this case.

Since DataRegion/DataSet/Grouing can be used for scoping puproses I thought it would be good to broaden this case sensitivity to include all of these.

What I found was the following on how RS compares names (short form 'I' for Case Insensitive and 'S' for Case Sensitive):

a) Grouping - S ("a" is different then "A")

b) DataSet - I

c) DataRegion - I

d) Grouping to DataSet - S (Grouping "A" can exist even if DataSet "a" exists)

e) Grouping to DataRegion - I

f) DataSet to DataRegion - I

I am perplexed as to why situations a) and especially d) exist. But it appears to be the way things work.

DK

|||Dataset fields are also S. It's probably because the report definition is compiled to a .NET assembly but I am all for (I).|||

Teo is regarding case-sensitiveness. The main reason is how the ReportObjectModel works and that making scope names case-insensitive would have a negative overall performance impact.

-- Robert

Group By Row Count And Force Page Break

Hello All!

Is there a "clean" way to group rows from a table by the row count, and then force a page break each time the count hits a maximum number (say 25 rows)?

As an alternative, can you set the maximum count of rows in a table, and then force a page break if it reaches that count? (this may actually be a better solution for my needs).

Thanks!

Create a group, set the expression for grouping to "=Fix((RowNumber("table1")-1) / 25)", and set the page break at end option on. To vary the number of rows per page, change 25 to your desired value.|||This works good, except that your table footer will always be on another page. Any ideas on how to prevent this using the solution above?|||In order to get table header and footer on all pages (and no hanging footer on last page), put the table in a list and do the grouping on the list.

Group By Row Count And Force Page Break

Hello All!

Is there a "clean" way to group rows from a table by the row count, and then force a page break each time the count hits a maximum number (say 25 rows)?

As an alternative, can you set the maximum count of rows in a table, and then force a page break if it reaches that count? (this may actually be a better solution for my needs).

Thanks!

Create a group, set the expression for grouping to "=Fix((RowNumber("table1")-1) / 25)", and set the page break at end option on. To vary the number of rows per page, change 25 to your desired value.|||This works good, except that your table footer will always be on another page. Any ideas on how to prevent this using the solution above?|||In order to get table header and footer on all pages (and no hanging footer on last page), put the table in a list and do the grouping on the list.