Reporting Services :: Calculating Grand Totals From Group Totals

May 9, 2015

I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.

Example:
...
....
Group1              Value
                           10
                            20
Sub Total 1:         30

Group2                 Value
                              15
                              25
Sub Total 2:           40

Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?

View 5 Replies


ADVERTISEMENT

Reporting Services :: Group Totals From Report Items

May 18, 2015

I have a table with a row group "Sales Area" that lists customers per sales area. There is one column with the sales per customer and another column with the planned sales per customer.A third column "Under Plan" is a simple calculation that compares the two Report Items of the sales to the plan and puts a 1 there if plan is higher. My issue is how to get the total of the group "Sales Area", to display the group total of all customers that are under plan. SSRS doesn't let me use aggregate functions on group totals;Unfortunately I cannot pre-calculate the "Under Plan" figure in the query, since this example is a simplified overview (the customers is a distinct count for example...)

View 5 Replies View Related

Reporting Services :: Adding Group Totals In SSRS

Oct 27, 2015

I have a field on my report that uses the following expression to determine the commission amount for each order line.  It works correctly to get the commission amount for each line, however, I need to get a total of the commission amount for each Salesperson. 

My report is grouped in the following manner:

Salesperson, Type of Sale, Invoice Number, then the detail invoice line items (where the formula below reside).  How can I get the totals for the Salesperson and the Type of Sale?

=IIF(Fields!PartIsSerialized.Value=True, (Sum(Fields!OrderLineSubtotal.Value)/Fields!Quantity.Value),Sum(Fields!OrderLineSubtotal.Value))
*
IIF(
Fields!TransactionType.Value Like "*USED*", (Parameters!CommissionRateUsed.Value*.01),

[Code] ....

View 2 Replies View Related

Reporting Services :: Row Group Totals On A Drill Down Report Builder Report

Oct 28, 2015

I have a report builder drill down report. I have row groups with totals.    It looks like the attached.   The problem is when the report is not expanded the Grand Totals column is not accurate... it is displaying the totals of one of the rows when expanded.The expression in the Total Show text box is

= Switch (                                                                      
MID(Fields!protocol_id.Value,1,7)="THERAPY",                    
                                         Sum(IIF(Fields!status.Value = "CO", CDbl(Fields!TX_CO.Value),           Nothing)),
  MID(Fields!protocol_id.Value,1,7) = "GENERAL" and                                                  MID(Fields!program_id.Value,1,6)
= "INTAKE",                                                        Sum(IIF(Fields!status.Value = "CO",

[code]...

Is there any way to not display the expression in the Total columns unless the report is expanded?

View 2 Replies View Related

Subtotals & Grand Totals

Feb 28, 2007

I am new to SQL and have been given the task of adding Subtotals and a Grand Total to a report. Below is my code...can someone point me in the right direction?

SELECT
POP30300.POPRCTNM,
receiptdate,
VENDORID,
VENDNAME,
POP30310.ITEMNMBR,
SERLTNUM LOTNUMBR,
LandedCost = ISNULL(CONVERT(money,LOTATRB1),0),
STNDCOST = ISNULL(CONVERT(money,STNDCOST),0),
LandedCostStatus = CASE WHEN CONVERT(money,ISNULL(LOTATRB1,0)) = 0 THEN 'Missing' ELSE
CASE WHEN (CONVERT(money,ISNULL(LOTATRB1,0))-STNDCOST)/STNDCOST*100 NOT BETWEEN -30 AND 30 THEN 'OutOfRange' ELSE 'Okay' END END,
PONUMBER,
ISNULL(POP30310.UNITCOST,0) / ISNULL(POP30310.UMQTYINB,1) POPRICE,
ISNULL(QTYAVAIL,0) QTYAVAIL
FROM ODB.dbo.POP30300 POP30300 (NOLOCK)
INNER JOIN ODB.dbo.POP30310 POP30310 (NOLOCK) ON POP30300.POPRCTNM = POP30310.POPRCTNM
INNER JOIN ODB.dbo.POP30330 POP30330 (NOLOCK) ON POP30310.POPRCTNM = POP30330.POPRCTNM AND POP30310.RCPTLNNM = POP30330.RCPTLNNM
INNER JOIN ODB.dbo.IV00301 IV00301 (NOLOCK) ON POP30330.ITEMNMBR = IV00301.ITEMNMBR AND POP30330.SERLTNUM = IV00301.LOTNUMBR
LEFT JOIN ODB.dbo.IV00101 IV00101 (NOLOCK) on POP30330.ITEMNMBR = IV00101.ITEMNMBR
LEFT JOIN (SELECT ITEMNMBR, LOTNUMBR, SUM(QTYRECVD-QTYSOLD-ATYALLOC) QTYAVAIL
FROM ODB.dbo.IV00300 IV00300 (NOLOCK)
GROUP BY ITEMNMBR, LOTNUMBR) QTYAVAIL
ON POP30330.ITEMNMBR = QTYAVAIL.ITEMNMBR AND POP30330.SERLTNUM = QTYAVAIL.LOTNUMBR
WHERE POP30300.POPTYPE IN (1,3)
AND POP30300.VOIDSTTS = 0
-- AND POP30300.receiptdate > DATEADD(dd,-35,GETDATE())
AND ISNUMERIC(LOTATRB1) = 1
AND ISNULL(QTYAVAIL,0) <> 0

View 20 Replies View Related

Howe To Get Grand Totals?

Sep 24, 2007

Hi,

Can you get grand totals row in sql?

I want to add val column to a grant total, Time1 to a grand total and time2 to a grand total in table below. How to do this please?

PVal TIME1TIME2

p101900-01-01 00:49:59.0001900-01-01 00:00:00.000
p2221941900-01-01 06:33:13.0001900-01-01 00:26:55.000
p3229741900-01-01 06:33:13.0001900-01-01 00:52:39.000

Thank you!

View 1 Replies View Related

Grand Totals Page

Sep 7, 2007

We have a report that includes a list that expands down to 17 inches to accomodate two pages for a report and this list is grouped on a particular location. Each report is grouped by State. So in each state there can be many locations that report various items that comprimse two pages.

Now, what we need is a "state grand total" page and I am wondering about the best way to go about doing this. We do not want to create a new report with a separate stored proc that returns grand totals. What we would like is to do a running total or something like that. I could then include this on a subreport, but I need it to be the last page. For some reason, I thought there was a Report Footer section, but I only see Page Footer.

I hope I made myself clear enough and not being too confusing and I many thanks for any helpful information.

View 1 Replies View Related

Analysis :: How To Hide Grand Totals For Particular Dimension

Aug 3, 2015

I have a calculated measure. On a particular dimension I want to hide the grand total. for remaining dimension grad totals to be visible. Like the below screen shot ....

View 4 Replies View Related

Error With Subtotal And Grand Totals With Iff Checking.

Jun 8, 2007



I have a table with amount columns and I want the amount column to either insert the value from the database or a zero based on a condition.



For the table rows I use the following to find the amount:



=iif( Fields!TYPE.value="Material" or Fields!TYPE.value="Other", FIELDS.Amount.Value,0)



which works fine. However, when I try to Sum in the group foot I get #Error when I use



=sum(iif( Fields!TYPE.value="Material" or Fields!TYPE.value="Other", FIELDS.Amount.Value,0) )



for the groupings that have a type other than Material and Other. For some reason, it doesn't total the amounts of Material and Other with the Zeroes that were placed in the table rows based on the Condition.



For example, the subtotal errors out when trying to total Material with Labor but if it was just Material and Other, it works.



Example of what the Columns are:



job, year, month, type, amount



s57, 2007, 2, labor, 0

s57, 2007, 2, material, 500



month total errors out

year total errors out

job total errors out



Any help would be appreciated.

View 12 Replies View Related

Pb With Calculated Measure And Totals (grand Total Or Anysubtotals)

May 20, 2008



Hi,

I've created a calculated measure which is a division between 2 other measures which i also have displayed in the cube.
of course as soon as there is some level of aggregation, the shown result is an averaged division and therefore is wrong.


here is some example:
A 16874
B 956
C 1354
D 264
E 103
F 81
G 6
H 3
X 23
Total 5198.36...

here the wanted result would be a simple some of all the other values (=19664)

How could i treat totals/aggregation differently or have any good way of solving this issue?
I know there is the aggregatefunction propertie for normal dimension, but i'm not familiar with calculated measures and it seems properties that can be accessed through BIStudio's interface is rather limited.


thanks a lot in advance for your help. I've been looking for tweaks on the data to trick the cube, but couldn't find any way of getting the result i'm looking for.

View 1 Replies View Related

Analysis :: Grand Totals Not Adding Up In Power View On Molap Cubes?

Nov 12, 2015

My Power view reports do not show the grand totals correctly.  I think my issue relates perfectly to [URL]. However we have updated service pack 2 on both the sql data warehouse and the ssas box.  They are 2012.  However the power view is still incorrect.  Pivot tables seem to work just fine.  In AS I have a 2 separate measure groups that has a total count.  I then use mdx to bring those two together.  Something I have done many times in past with as 2008.  My mdx looks like this below.  I then hide Total Count EC (from measure group 1) and Total Count SF (from measure group 2). This way I have 1 total count value and they can use another dimension to see the two separate if needed. 

CREATE
MEMBERCURRENTCUBE.MEASURES.[Total
Count New] AS
//
([Measures].[Total Count] + [Measures].[Total Count SF]),
DISPLAY_FOLDER
=
'Totals', 
ASSOCIATED_MEASURE_GROUP=
'Report',
FORMAT_STRING
=
"#,#0",
VISIBLE=
1;

The measure Total Count EC works just fine but the Total Count (MDX) does not .  All in Power Pivot works fine but in Power View the grand totals are not correct.

View 3 Replies View Related

Report Services 2000 Totals On Group Are Incorrect. Summing Duplicate Values

Dec 19, 2007

Hello Everyone
I've created a report with a simple dataset that is similar to this

City , RequestID, Request Amount, ClaimID, ClaimAmount
El Monte 791 52,982.00 2157 41,143.75
El Monte 3691 11,838.00 3140 8,231.14
El Monte 3691 11,838.00 3141 990.00
El Monte 3691 11,838.00 3142 2,615.00


So I group by City, RequestID. On the first group I specified the expression to be City and in the header I list the city and in the footer I list the sum of Request amount. On the second group I specified the group by Request so in the header I placed requestID and on the footer I placed Request Amount. I set request information to hide the duplicates and I even add =Sum(Fields!RequestApprovedGrandTotal.Value,"GroupByRequestID") the scope of the group. But this is what I get:


For requestID = 3691 for Request Amount is 35,514.00 not 11,838.00. All the claim sums are correct and they are located on the detail row.



I've read that a work around is to create multiple dataset but I honestly believe that something as simple as this should work on the reporting server 2000. So I've come to the conclusion that I must be doing something wrong. Can someone give me a hand on this. Thanks.

View 6 Replies View Related

Reporting Services :: Sort Report By Totals Column

Jan 4, 2012

I've created an SSRS report in Report builder and I'm displaying it in SharePoint 2010. What I would need to do is to sort according to my totals -column. My report structure is as follows:

The first and column second columns are the ID and the name of a customer. There are over 35.000 consolidated customers in the database.

The third column is the column which I would like my report to be sorted upon. It gives the sum (Amount_EUR) of all different productlines sold to the customer.

The fourth column in this report design model is the product groups sold to that customer. There over twenty of product groups visible when Running the report.

This is what my report looks like when finished (you'd have to scroll a lot to the right if I took the whole of the report so I cut only a part of it to be visible):

I tried to add a sorting option from the reports Tablix properties and adding this code as the sorting function:

=SUM(Fields!Amount_EUR.Value)

But I'm getting an error:  "A sort expression for the tablix 'Tablix1' includes an aggregate function.  Aggregate functions cannot be used in data row sort expressions."

Ok, so no aggregate functions aren't allowed to the sort.

If I just put:

=Fields!Amount_EUR.Value and sort by that from Z to A(biggest to largest) it doesn't have any effect. The report is still sorted alphabetically by the ParentID.

I've also tried some other code bits, but they all seem to be saying that I couldn't put an aggregate function into the sort expression....but my Total -column is already calculated with a Sum -function....

Is it possible sort my report based on that Total -column? If yes, how? I'm using SSRS 2008

View 4 Replies View Related

Calculating % Totals In Proclarity

May 27, 2008



Hello,

Is it possible to have Proclarity calculate the totals desired below? I know ProClarity is summing the totals but is it possible to calculate the desired result below?







PYFMTD
FMTD Chg
FMTD Chg %


163325
-2817
-1.72%


1048020
19921
1.90%


351134
25698
7.32%


731017
-36191
-4.95%


509297
13356
2.62%


226993
6771
2.98%


194018
30535
15.74%


3223804
57273
23.89%
Incorrect



1.78%
Correct


Thanks,

Tony Spaulding
tony.spaulding@hotmail.com

View 1 Replies View Related

Calculating Weekly Totals

Feb 9, 2008

I have a table with columns: Date (SmallDateTime), Sales (Decimal)

Is there a SELECT way to compute weekly totals, similar to the GROUP BY clause?

Or is using a WHILE loop a practical way?


Thank you.

View 4 Replies View Related

Calculating Totals Based On User ID

Sep 14, 2013

I have created an SQL report that returns order data from a shop DB.

How can you get the output to total the number of order instances and the total value of those orders, both per user_id? Is this possible?

View 3 Replies View Related

Trying To Get Daily Totals From Cumulative Totals In A Pivot

Oct 2, 2006

I have been providing sales data for a few months now from a table that is set up like this:

Date WorkDay GasSales EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 50.00 45.00

etc.

As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.

Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:

Date_WorkDay_GasSales_EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 150.00 245.00

etc.

To make things more complicated, the powers that be wanted this data presented in this fashion:

Total Sales:

1_2_etc.

300.00 95.00 etc.

 So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:

with SalesCTE (Month, WorkDay, [Total Sales])

as

(

SELECT

datename(month, cag.date),

cag.WorkDay AS [Work Day],

sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]

FROM CAG INNER JOIN

Branch ON CAG.[Oracle Branch] = Branch.OracleBranch

group by cag.date, cag.WorkDay

)

select * from SalesCTE

pivot

(

sum([Total Sales])

for WorkDay

in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

) as p

So, my question is:

How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like

select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.

but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.

Any advice/answers? Thanks in advance!!!

 

P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...

View 12 Replies View Related

T-SQL (SS2K8) :: Calculating Running Totals For Partitions Of Data

Sep 23, 2015

I have table named #t1 and three columns. CODE, Column1, and Column2.

create table #t1 (
CODE NVARCHAR(20),
COLUMN1 NUMERIC(18,2),
COLUMN2 NUMERIC(18,2)
)

And i have some data:

INSERT INTO #t1 (CODE,COLUMN1,COLUMN2)
VALUES ('432', 0,100),
('TOTAL FOR 432',0,100),
('4320001',0,250),
('4320001',50,0),
('4320001',0,140),
('4320001',300,0),
('TOTAL FOR 4320001',350,390),
('432002',200,0),
('432002',0,100),
('TOTAL FOR 432002',200,100)
drop table #t1

I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.

View 9 Replies View Related

Power Pivot :: Calculating Monthly Totals From Date Ranges?

Jul 28, 2015

I am looking to calculate the monthly holidays by staff member using DAX. I am able to calculate it  if all entries (Start Date & End Date)  are confined to a unique month. The issue is where a person's holidays go across several months

I have 2 tables

Table 1 - Holidays
Name                        StartDate                                                 EndDate
Joe Bloggs                 27July 2015                                             7th Aug 2015
Jenny Jones               22nd July 2015                                        23rd July 2015
Jenny Jones               27th  July 2015                                        28th July 2015

Table 2 -Months&Years
Month/Year
June 2015
July 2015
August 2015
September 2015

I am looking for two types of Output  (high level and detailed)

Detailed
Month/Year                        Name                         Total  Days OOO
July 2015                          Joe Bloggs                            5
July 2015                          Jenny Jones                          4
August 2015                      Joe Bloggs                          5

High level Month/Year                       Days OOO
June 2015                                0
July 2015                                 9
August 2015                             5

View 4 Replies View Related

Is There A Way To Group Sub Totals?

Nov 14, 2007



Hi,
Is there a way to display the sum of a group of a field?
I've created a group, but when I put the expression of SUM(Field) in the group footer, it gives me the total of Field for the whole dataset.
Is there a way I can display the just the Totals of the Groups?


so if my data looks like :

a | 1
a | 2
a | 3
b | 4
b | 5

b | 6

I want to display :



a | 1
a | 2
a | 3
Total a | 6
b | 4
b | 5

b | 6

Total b | 15


but instead, when i add the SUM expression into the footer group, I get :

a | 1
a | 2
a | 3
Total a | 21
b | 4
b | 5

b | 6

Total b | 21

View 8 Replies View Related

Group Totals On Last Page - How?

Mar 27, 2007

I have a report that groups by dept #, job code and earnings code.



9999 Administration

033 Secretary

200 Regular Pay 44.00 1000.00

300 Sick Pay 8.00 25.00

400 Overtime 3.00 75.00



8888 Janoitorial

055 Janitor

200 Regular Pay 24.00 800.00

300 Sick Pay 4.00 15.00

400 Overtime 1.00 45.00





On the last page of my report I want to sum the earnings totals by earnings number. For Example:





Totals

200 Regular Pay 68.00 1800.00

300 Sick Pay 12.00 40.00

400 Overtime 4.00 120.00





Can this be done?

View 5 Replies View Related

Group Footer With Totals

Nov 30, 2007



I have a tabular report with grouping on the following fields:

Grouped rows: MDC, DrgDesc, ChronicOther
Detail row: Cases


The desired output follows:


MDC1
DrgDesc1
Chronic 50
Other 25
Total cases for DrgDesc1= 75

DrgDesc2
Chronic 20
Other 33
Total cases for DrgDesc2 = 53

etc....

I have everything working up to the Total cases for each DrgDesc. I tried adding a group footer to the Cases row, however this sub-totals for Chronic and Other rather than summing them together. Example is below.

MDC1
DrgDesc1
Chronic 50
50
Other 25
25
DrgDesc2
Chronic 20
20
Other 33
33

etc....


How can I achieve the desired result?

View 3 Replies View Related

Problem With Group Totals And Counts

Sep 21, 2007

I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.

Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).

View 8 Replies View Related

T-SQL (SS2K8) :: GROUP BY CUBE Aggregation - Pivoting On 2 Totals

Aug 1, 2014

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...

View 1 Replies View Related

Inscope Evaluates To False On Second Row Group (totals Column)

Sep 11, 2007

Hi, I have a matrix with 2 row groups and 1 column group.






CGroup1 Val1

CGroup1 Val2

Total


- RGroup1 Val1

RGroup2 Val1

In

In

Out




RGroup2 Val2

In

In

Out


- RGroup1 Val2

RGroup2 Val3

In

In

Out




RGroup2 Val4

In

In

Out


Total

Out

Out

Out


I want to change the row totals at the RGroup2 level. I have put an expression in the measure cell as:

=iif(InScope("matrix1_RowGroup2"), "In", "Out"). Shouldn't the values in the Totals Column on the far right evaluate to "In"? If not, how can I isolate the totals at the RGroup2 level?

Also, I found that when I put =fields!RGroup2.value in the expression for the cell, the Totals Column on the far right is blank but when I put =fields!RGroup1.value the correct value is properly displayed in the Total Column. Why does =fields!RGroup2.value not work?

View 7 Replies View Related

Fiscal Year Totals - Calculating Sales By Month And Current Year

Sep 18, 2013

I have the following script that calculates Sales by month and current year.

We run a Fiscal year from April 1st thru March 31st.

So April 2012 sales are considered Fiscal Year 2013.

Is there a way I can alter this script to get Fiscal Year Totals?

select ClassificationId, YEAR(inv_dt) as Year, cus_no,
isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr',
isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May',
isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun',
isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',

[Code] ....

Data returned looks like the following.

ClassificationID Year Cus_no Apr May June ....
100 2012 100 $23 $30 $400
100 2013 100 $40 $45 $600

What I would need is anything greater than or equal to April to show in the next years row.

View 2 Replies View Related

Reporting Services :: Calculating Difference Between Columns Within Column Group

Jun 15, 2015

Given the attached report, is there an easy way of calculating the difference between the Today and QTR Start column? Because of the Account Group, the report looks like the sample shown on the second image.

Sample report:

View 4 Replies View Related

Calculating Grand Total And Percentage

Oct 19, 2007

Hi All,

I have to create a table like this using SSRS.

LoanStatus NoOfLoans Amouunt Percentage









Started
1,000
1,000,000.00
100%









Sent to Partner
350
350,000.00
35%

Denied by Partner
50
50,000.00
5%

Accepted by Partner
300
30,000.00
30%

Loan Apps Pnd Disb
10
100,000.00
1%

Loan Apps Disbursed
290
290,000.00
29%




Table is grouped by loan status.
Percentage is calculate as NoOfLoans/TotalNoOfLoans.


Please can anyone tell me how to calculate the grand total and Percentages?

View 5 Replies View Related

Reporting Services :: Report Builder 3.0 - Separate Grand Total Section To A New Page?

May 5, 2015

is it possible to separate Grand Total of a sub group to a new page?

I tried using advanced mode, find the first line the Grand Total section and set its "RepeatOnNewPage" to true, but, when I execute the report, it will throw an error something like the value of RepeatOnNewPage of the TablixMemeber must be the same as its previous object.

I need my report pages to separate by subGroup(SubGroupA, SubGroupB, A+BTotal).

View 2 Replies View Related

Getting Totals Help

Feb 22, 2004

I am using a web page to enter records into a table that tracks production on machine parts.

I get two recordsets and loop through them using vbscript to get the totals.

I use two because I first get all production data greater than the last date an event occurred. The second I get the totals for the day of because I have to back out shifts of production prior to the event, the day of the event (ie 3 shift per day, 3rd shift begins the day so a day looks like this 3, 1, 2. We do an event on shift 1. I have to back out shift 3 as it happened prior to 1. Once I have the two seperate totals I then write a record in the historical data table with the event, shift, production totals, etc.

My question is can I get the production totals from within SQL or is looping through the rs's in vb better?

the vb for the shift is similar to this
If last_event_shift = 1 then
DayProd = shift1prod + shift2prod
elseif last_event_shift = 2 then
DayProd = shift2prod
elseif last_event_shift = 3 then
DayProd = shift1prod + shift2prod + shift3prod
else
end if
TotProd = DayProd + GreaterProd


Here is the SQL for each rs
Data Greater than last date
Select Sum(Production)AS TotProd, dbo.Production.LineNum, EventType

From dbo.Production INNER JOIN dbo.EventDate ON dbo.Production.LineNum = dbo.EventDate.LineNum AND dbo.Production.EntryDate > dbo.EventDate.EntryDate

Where dbo.EventDate.LineNum = @Line
Group By dbo.Production.LineNum, dbo.EventDate.EventType

Data for the last date
Select Max(dbo.EventDate.ShiftRemoved) AS RemovedShift, dbo.EventDate.Set_Pos, Max(dbo.EventDate.CutOff)AS CutOff, dbo.EventDate.EntryDate, Sum(Production)AS TotProd, dbo.Production.LineNum, EventType, Shift

From dbo.Production INNER JOIN dbo.EventDate ON dbo.Production.LineNum = dbo.EventDate.LineNum AND dbo.Production.EntryDate = dbo.EventDate.EntryDate

Where dbo.EventDate.LineNum = @Line

Group By dbo.Production.LineNum, dbo.EventDate.EventType, Shift, dbo.EventDate.EntryDate, dbo.EventDate.Set_Pos

Thanks in advance,
Lee

View 2 Replies View Related

Totals

Mar 16, 2007

Hello All,



I have done a report that shows all the subtotals however, I want to show the grand total (the sum of each subtotal) in a row. I am using SQL server BI studio-2005. I know I can add a row but all the rows are detailed rows and subsequenlty reflect the subtotals for last record in the report. All I need to know what type of row do I need to add to show my totals.

Thanks,

Rashi

View 3 Replies View Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

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, ...

View 4 Replies View Related

How To Include Row Totals?

Feb 4, 2002

This is probably a simple task but I just don't know how to do it. I need to return a recordset of details with a row of totals for selected columns. Something with a result like this:

Location Attendance Showings
======== ========== ========
JOHNS 210 3
SEREN 116 2
total 326 5

I know I could do this with a stored procedure, but I'd prefer to do it with just one sql statement. I tried compute but because I have a total on more than one column, the returned recordset is actually 3 rows.
Thanks in advance for any suggestions.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved