MS has been nice enough to add the Cube and Rollup operators so I can
have totals with my results.
But out of shear meanness they won't let me use them unless I use a
Group By clause in my select. I have a proc with 25 fields, and have
no desire to Group By anything- when a certain field changes, I just
want a row with the total of that field.
Is this possible?
Thanks,
Burt
Hi,
Can anyone explain me that WHAT IS CUBE AND ROLLUP Statments for?? Why to use, Where to use and When to use???
Are they usefull in any point of view from ADO.NET???
Hai all !
What is purpose of Cube and Rollup?
Can u explain with small example?
Regards
Umapathy
Umapathy
I have been working on this 'Select Count' problem for the past week. Somehow, the Counts were multipled.2 requests showed 4 counts, 3 requests showed 9 counts, 4 requests showed 16 counts. The main question,to the best of my knowledge, is the coding for DBadapter.Fill method. After 'Left Join' 2 tables, how do I code the DataTable names? I have tried using TransDS.Tables("Table name") to solve the problem. Butthe counts still showed mulpile #.
Another question: why there are 1 '0' line and 2 'rollup' lines?
Examiner
Requested
Scheduled
Finished
noShow
Cancelled
Deleted
0
0
0
0
0
0
312
249
97
60
39
45
255
210
90
60
15
30
aaaa
4
4
0
0
0
0
bbbb
9
9
3
0
6
0
16
16
0
0
16
0
16
4
2
0
2
12
4
4
2
0
0
0
strApptsSQL = "SELECT c.LName + ', ' + c.FName AS Examiner, " & _
"COUNT(Case TransCode WHEN 'R' THEN 1 ELSE NULL END) AS Requested, " & _"COUNT(Case TransCode WHEN 'B' THEN 1 ELSE NULL END) AS Scheduled, " & _
"COUNT(Case TransCode WHEN 'F' THEN 1 ELSE NULL END) AS Finished, " & _"COUNT(Case TransCode WHEN 'W' THEN 1 ELSE NULL END) AS DSnoShow, " & _
"COUNT(Case TransCode WHEN 'D' THEN 1 ELSE NULL END) AS Deleted, " & _"COUNT(Case TransCode WHEN 'E' THEN 1 ELSE NULL END) AS Cancelled " & _
"FROM Sssss.dbo.TransHistory AS T " & _"LEFT JOIN Sssss.dbo.Requests AS r ON T.TransUserID = r.RequestStaffIDFK " & " " & _
"LEFT JOIN Employee.dbo.Contacts AS c ON r.RequestStaffIDFK = c.ContactPK " & " " & _"GROUP BY c.LName + ', ' + c.FName WITH ROLLUP " & _
"ORDER BY c.LName + ', ' + c.FName"
'declare and create the data adaptersDim oDAAppts As New OleDbDataAdapter(strApptsSQL, oConnect)
Try
' fill the Dataset using the data adaptersoDAAppts.Fill(TransDS, "Requests")
Catch oErr As Exception
' display error message in page
lblErr.Text &= oErr.Message & "<br />"
Finally
' be sure to close connection if error occurs
If oConnect.State <> ConnectionState.Closed Then
oConnect.Close()
End If
End Try
' bind DataGrid to tablegvExaminerAppts.DataSource = TransDS.Tables("Requests")
gvExaminerAppts.DataBind()
I'm trying using the GROUP BY CUBE aggregation. Currently I have this working as such:
SELECT
ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date
,ISNULL([1 Attempt],0) [1 Attempt]
,ISNULL([2 Attempts],0) AS [2 Attempts]
,ISNULL([3 Attempts],0) AS [3 Attempts]
,ISNULL([4 Or More],0) AS [4 Or More]
[Code] .....
Basically this is used to work similar to a Pivot table in excel. My data will look as follows:
Date 1 Attempt2 Attempts3 Attempts4 Or MoreTotal
2012-09-04 239 68 2 8 317
The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8
If I change the FROM select clause to use SUM instead of COUNT
SELECT
CONVERT(DATE,[Date]) Date
,ISNULL(AttemptsFlag,'Total') as Attempt
,SUM(NoOfTimes) AS Totals
FROM
XXXXX
GROUP BY
CUBE([Date],AttemptsFlag)
It will return the correct Total amount but not the right numbers for the Attempt groupings...
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
When I make a call to GetSchemaDataset with a restriction of a cube name with a space in the name of the cube the call fails. Following is a sample of the code: adoRestriction = new AdomdRestriction("CATALOG_NAME", "Contoso Telecom_Contoso"); adoRestrictions.Add(adoRestriction); dataSet = conn.GetSchemaDataSet("MDSCHEMA_CUBES", adoRestrictions); I am running SQL Server 2005 Analysis Services SP2. Is there some way to qualify the cube name in the restriction or is this just a bug? Thanks.
View 1 Replies View RelatedI am trying to do a Total Row with Rollup.
I am encountering an error:
Server: Msg 8120, Level 16, State 1, Line 25
Column 'CALLSTARTTIME' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Here's the query:
SELECT
CASE WHEN (GROUPING(DATEPART(yy, CALLSTARTTIME))=1) THEN 'Total'
ELSE
DATEPART(yy, CALLSTARTTIME)
END AS 'Year',
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
GROUP BY DATEPART(yy, CALLSTARTTIME) with ROLLUP
ORDER BY DATEPART(yy, CALLSTARTTIME)
Idea outcome:
Year Total Calls
2005 100
2006 200
Total 300
Hi,I am attempting to achieve some form of report that needs to make use of sql rollup and display it as follows:Category Subject Matter1 Subject Matter2 Subject Matter3 No of CasesClubs Facilities Sport Facilities Swimming Pool 3 SubTotal 3Events SBR/AHM NULL NULL 1 SubTotal 1 GrandTotal 4However, with my sql query, using roll up, it will look like the following which is not correct.Category Subject Matter1 Subject Matter2 Subject Matter3 No of CasesClubs Facilities Sport Facilities Swimming Pool 3Clubs Facilities Sport Facilities NULL 3Clubs Facilities NULL NULL 3Clubs Sub Total NULL NULL 3Events SBR/AHM NULL NULL 1Events SBR/AHM NULL NULL 1Events SBR/AHM NULL NULL 1Events Sub Total NULL NULL 1This is the query I am using:<code>select casewhen (grouping(Cat.Description)=1) then 'Grand Total'else Cat.Descriptionend as Category,casewhen (grouping(sub.description)=1) then 'Sub Total'else Sub.descriptionend as SM1,SubSub.Description as SM2, SM.Description as SM3, count(sub.description)from tb_feedbackcase FB left join tb_category Cat on FB.Category_ID = Cat.Category_ID left join tb_subcategory Sub on FB.SubCategory_ID = Sub.SubCategory_IDleft join tb_subsubcategory SubSub on FB.SubSubCategory_ID = SubSub.SubSubCategory_IDleft join tb_SubjectMatterLV3 SM on FB.SM3_ID = SM.SM3_IDwhere fb.commenttype_id in (select commenttypes_id from tb_comment_types where description = @feedback_type)and convert(char(10),feedback_datetime,102) >= convert(char(10),@date_from, 102)and convert(char(10), feedback_datetime, 102) <= convert(char(10),@date_to, 102)group by Cat.Description, Sub.Description, SubSub.Description, SM.Description with rollup</code>How can I change it to reflect more accurately? Please help. Thanks.
View 1 Replies View Relatedl would like to get totals at the bottom of the following columns
SELECT sum(capital_Amount) AS Capital_Amount,
sum(interest_Amount) AS Interest_Amount,
sum(total_Amount) AS Total_Amount,
sum(admin_Fee) AS Admin_Fee
to sum up all totals by column. i tried using the rollup,cube like in oracle no luck. Whats the best way of achieving this? if l have to write it as a function how would l do that?
Alter View Test2
As
SELECT loan_No AS Loan_No,
date_Issued AS Date_Issued,
store AS Store,
product AS Product,
capital_Amount AS Capital_Amount,
interest_Amount AS Interest_Amount,
total_Amount AS Total_Amount,
admin_Fee AS Admin_Fee,
user_Issued AS User_Issued,
LoanBook AS Company,
status
FROM Loan
I have two tables - the first contains machine info, and the second contains monthly readings.
Table1
Serial, Model, Location, etc...
Table2
Serial, Year, Month, Reading
I would like to write a query that gives me one row for each machine that has the serial, model, and the reading for the previous 12 months. The date will be passed into the query
Query (passing 6/1/2004)
Serial, Model, ReadingFor200307, ReadingFor200308, ...., ReadingFor200406
Can anyone help me with this or tell me what topic I should be scanning the help files for?
Thanks,
Rob
Ok, I want to know if it is possible to do an order by while using the rollup in the group by. Look at the below script:
create table tmpTable
(
prod_name varchar(50) null,
prod_color varchar(50) null,
quantity int null
)
insert into tmpTable values ('table', 'blue', 12)
insert into tmpTable values ('table', 'red', 100)
insert into tmpTable values ('table', 'white', 50)
insert into tmpTable values ('chair', 'blue', 12)
insert into tmpTable values ('chair', 'red', 1)
insert into tmpTable values ('chair', 'white', 123)
insert into tmpTable values ('chair', 'yellow', 50)
SELECT CASE WHEN (GROUPING(prod_name) = 1) THEN 'Grand Total'
ELSE ISNULL(prod_name, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(prod_color) = 1) and grouping(prod_name) != 1 THEN 'Sub Total'
when grouping(prod_color) = 1 and grouping(prod_name) = 1 then ''
ELSE ISNULL(prod_color, 'UNKNOWN')
END AS Color,
SUM(quantity) AS QtySum
FROM tmpTable
GROUP BY prod_name, prod_color WITH ROLLUP
--drop table tmpTable
I want to be able to do an order by for each section of the rollup so that the quantity can be asc for both the chair part of the query and the table part.
Thanks ahead of time.
DMW
I found this great rollup query example that uses grouping and subtotals. Would it be possible to expand on this and include a groupwithin a group and subtotal each group? For example, parent product thenchild product? Help appreciated. Thanks.FrankSQL:SELECTCASEWHEN (Grouping(CategoryName)=1) THEN 'MainTotal'ELSE CategoryNameEND AS CategoryName,CASEWHEN (Grouping(ProductName)=1) THEN 'SubTotal'ELSE ProductnameEND AS ProductName,Sum(UnitPrice) as UnitPrice,Sum(UnitsinStock) as UnitsInStockFROM ProductsINNER JOIN Categories OnProducts.CategoryID = Categories.CategoryIDGROUP BY CategoryName, ProductName WITH ROLLUP*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI would like to only have 1 column rollup but I still need the other columns in the output. Here is my T-SQL code:
This is the current output:
0 2007-02-14 00:00:00 test test 03-Barnes Healthcare Services 246.00
0 2007-02-14 00:00:00 test test 03-Barnes Healthcare Services 246.00
0 2007-02-14 00:00:00 test test NULL 246.00
0 2007-02-14 00:00:00 test NULL NULL 246.00
0 2007-02-14 00:00:00 NULL NULL NULL 246.00
...
...
0 NULL NULL NULL NULL 3992230.50
I would like it to look like this:
0 2007-02-14 00:00:00 test test 03-Barnes Healthcare Services 246.00
..
...
0 NULL NULL NULL NULL 3992230.50
I only want the total on the 1st column, but it rollups all of the columns.
Code Snippet
SELECT ServiceTypeCode, ServiceDate, IFirst, ILast, VendorName, Sum(ISNULL(AfterDiscountAmt,0)) AS Amount
FROM Holding_Billable
WHERE DocumentDate BETWEEN '1/1/2007' AND '12/31/2007'
AND SiteCode = 7001
GROUP BY ServiceTypeCode, ServiceDate, IFirst, ILast, VendorName, AfterDiscountAmt
WITH ROLLUP
So I'm making some kind of vain attempt to follow this tutorial to get summary rows, and I've followed it as best I can to the letter using my own data. But when I try to run the query, it tells me there's a syntax error near "WITHROLLUP". Am I missing something?
SELECT CASE GROUPING(Employee) WHEN 0 THEN Employee ELSE 'Total' END AS TheEmployee, CASE GROUPING(ID)
WHEN 0 THEN ID ELSE - 1 END AS TheID, SUM(WorkDays) AS TotDays
FROM EmpScheduleExceptions
WHERE (YEAR(Start) = YEAR(GETDATE()))
GROUP BY Employee, ID WITH ROLLUP
ORDER BY Employee, TotDays
I'm struggling to fin a way to use DISTINCT keyword with ROLLUP (or Cube).For example,SELECT employee_city, employee_country, COUNT(DISTINCT employee_name)FROM employeeGROUP BY employee_city, employee_country WITH ROLLUPthat query does not work.Is there a workaround?Thx.
View 2 Replies View RelatedIs it possible to roll up all child records into one comma delimited field? For example, if the parent table is league info, and the child table is team info, I would be looking to return results like this:
Field 1 Field 2
NFL Bears, Browns, Saints, etc
NBA Bulls, Celtics, Lakers, etc
TIA,
Rob
How do I remove repetitive value in my query with rollup result
a b c 10
a b c 10
a b null 20
x y z 10
x y z 10
x y null 20
null null null 40
I have a two tables - a machine, and a volume
Machine has id, and serial number fields
Volume has machine_id, date, and volume
I need to roll up each volume reading into quarters for the year, and return each machine and all its quarters.
So the output should look like this (only one year will be run):
MachineID Qtr1SUM(Volume) Qtr2SUM(Volume) Qtr3SUM(Volume) Qtr4SUM(Volume)
Any ideas? I was going to join the volume table four times, with each one making sure it had the appropiate dates, but that isn't working well.
Thanks,
Rob
I am using the SQL GROUP BY WITH ROLLUP PER MS's
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html/data01102002.asp
To create subtotals and totals.
The problem is, I want items on every line, like transaction dates, that are neither grouped by or summed. They are just for information only.
ANSI-92 SQL does not allow any column to be selected that is either not grouped on or has an aggregate function performed.
The MS example in the link above works fine, but does not present much data.
If I wanted to sum total orders and each order's subtotal for a given customer, I would also likely want the order date, who the order was placed by, etc. in each line of the report without grouping or summing that extraneous info.
Here is an example of the SQL syntax:
SELECT
CASE GROUPING (companyId) WHEN 0
THEN (SELECT CFP..Company.companyName FROM Company WHERE Company.companyId = Activity.companyId) ELSE 'TOTAL' END AS [Company name],
CASE GROUPING (dateOrder) WHEN 0
THEN (SELECT CAST(dateOrderAS VARCHAR)) ELSE 'SUB TOTAL' END AS [dateOrder],
SUM(transactionAmount) AS [Request amount]
FROM Activity
WHERE DATEPART(year, dateOrder) = DATEPART(year, '2004')
GROUP BY companyId, dateOrderWITH ROLLUP
...
But I want more details.
after I've created a new column in a table with the "Create a new column" with custom members. How do I actually remove this newly created column without recreating the dimension ?
Thank you
Tom
i want this keep query output result but don't want the "compute " this command , because this query cannot run in this dos command directc.exe , Thus, it is have any writing skill , it is possible using of rollup ?
select d.shopcode
,d.memono
,d.txdate
,d.sku
,h.depositamt
[Code] ....
How to writing this query using rollup or grouping sets in two way?
data
shop time date sku discount% sales qty amount
A 13:00pm, 2014-feb-11 apple 30% 5 #20
A 13:00pm, 2014-feb-11 apple 30% 5 #20
A Shop Qty :10 Amt:40
B 23:00pm, 2014-feb-11 apple 30% 5 #20
B 23:00pm, 2014-feb-11 apple 30% 5 #20
B shop Qty :10 Amt $40
Grand total qty:20 , Amt $80
I'm trying to create a report where multiple currencies are rolled up into seperate subtotals. The currency type and amount are in seperate columns..
I'd like for my report to look like this
Account currency_type ammount
I'd like for the report to look like this
001 USD 100
001 USD 150
001 EUR 100
001 EUR 100
001 AUD 100
Total USD 250
EUR 200
AUD 100
Is there a way to do this?
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I installed the SQL Server 2005 SP2 update 2 rollup on my 64-bitserver and the performance has tanked!I installed rollup 3 on some of them, but that did not seem to help.I thought it was just a linked server performance issue, but myoptimization started running today on one of the "update 2" instancesand so far it's been running about 10 hours longer than it normallydoes.The rollup 3 fixed our stack dumping issues, but we NEED to have thisperformance thing fixed!I saw that MS has come out with update 4 last week - doesn't sayanything about fixing this, though.Has anyone else experienced this?I'm not necessarily expecting anyone to have a fix for this, justwantto know I'm looking in the right place before I call MS.
View 3 Replies View RelatedHello all, I´m a beginner on AS2005 (but I know pretty well AS2000), I migrated a cube from AS2000 to AS2005 but in this cube I have a Custom Rollup Formula on two levels of my Time dimension (month and week), an example of the week level formula is the next:
iif( [Time].CurrentMember.Name = [Time].CurrentMember.NextMember.Name,
null,
iif( [Time].CurrentMember.Name = [Time].CurrentMember.PrevMember.Name,
Sum({[Time].CurrentMember.Children,[Time].CurrentMember.PrevMember.Children}),
Sum([Time].CurrentMember.Children)
)
)
<<this formula overrides the week aggregations>>
and I really need this but I cannot find on AS2005 where to do the same thing, someone can tell me where can I define Custom Rollup Formulas and how?.
Thanks in advance!
Daily report generating Monthly rollup stats
I have a daily report which each morning generates monthly information for the current month which was implemented in December. Everything was working correctly untill January 1st. On the 1st the report generated blank since it was suppose to generate 1-31 Dec but but the currently month was Jan, so it failed. How do I program it so if it is the 1st of a month generates the previous month but still would generate current month but while in the current month? Any help is appreciated.
SELECT GETDATE() - 1 AS rptdate, Errors.WTG_ID, lookup.Phase, Errors.STATUS_TYPE, Errors.STATUS_CODE, STATUS_CODES.STATUS_DEF, Errors.TIME_STAMP,
Errors.ANSI_TIME, lookup.WTG_TYPE, Errors.POSITION
FROM Errors INNER JOIN lookup ON Errors.WTG_ID = lookup.WTG_id RIGHT OUTER JOIN STATUS_CODES ON Errors.STATUS_CODE = STATUS_CODES.STATUS_CODE AND lookup.WTG_TYPE = STATUS_CODES.WTG_TYPE
WHERE (STATUS_CODES.STATUS_DEF IS NOT NULL) AND (Errors.TIME_STAMP BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
ORDER BY Errors.WTG_ID, Errors.TIME_STAMP, position
Any working example of what a ragged hierarchy should look like for the unary and custom rollup calcs to work?
I have a parent-child hierarchy that works as expected but can't manage to get the same with a flattened ragged hierarchy. Parents disappear if any of the children aren't in the fact table (even though the children are set to '~' and the parent has its own custom rollup calc).
Do I need to replicate the unary and measure formula all the way to the end in the same way i do the member name/value (so i can set ignore if same as parent)? Setting unary to null seems to work when the key doesnt exist in the fact table but the default behaviour for null is '+' so i end up getting wrong results if the key does exist!
Repro:
In words
I have an EAV style fact table. The measure name is defined in a 'dim measure' table. Some measures exist in the fact, some are manufactured using Unary ops or custom rollup formulas.
Here's a diagram of the hierarchy
I want to recreate the functionality as a flattened hierarchy (the number of levels is fixed), but can't seem to do it... Here's what I've gotten for the same hierarchy created using the two different approaches
Below are queries to recreate the tables from adventureworksdw2014. The parent child hierarchy is set up the standard way... measureparentkey is parent. set name, unary and formula as expected. Set NonLeafDataHidden for the parent attribute. I've tried various combinations for the ragged hierarchy but none work 100% of the time. I want to have a user defined hierarchy for the performance benefits.
--Fact view
CREATE View dbo.FactSales
AS
select fis.ProductKey
, fis.OrderDateKey
, 1 AS MeasureKey -- salesAmount
, fis.SalesAmount AS MeasureValue
[Code] .....
I have a question regarding ROLLUP and how to use the operator.
I've been asked to retrieve a list showing the total repair cost per year displaying Transport Type, Manufacturer and Transport. The query I used is:
SELECT ty.Description AS TransportDescription,
t.SerialNumber AS Transport,
ty.Manufacturer,
SUM (r.RepairCost) AS RepairCost,
YEAR(r.EndWorkDate) AS [Year]
[Code] .....
How could I use the rollup function to display subtotals per Transport Type, Manufacturer and Transport?
Hi!
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
Thanks!
Karen
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.Â
View 4 Replies View Related