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.
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?
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
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 ....
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.
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.
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.Â
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.
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).
I'm writing reports that absolutely require page totals for several columns. I don't need a cumulative total for everything in the report, just for the items on the current page. Is this doable in SSRS? If not, is there another reporting package that supports putting data in the header or footer?
I am new to reporting services and had a couple questions regarding the formatting of my reports.
First- I have a matrix report that needs to be sorted in both ascending and descending order by Total Buys for each Title. I have inserted a subtotal for Buys in the report Layout but I cannot get the report to sort by this Total, only by the name of the Title. Is there an advanced sorting option I am missing?
Second- My matrix reports generate on hundreds of pages and I would like them to generate on as few pages as possible to make viewing the reports easier. I have made sure to turn off page breaks in every category but this does not seem to have any effect. How else should I go about eliminating these page breaks?
Any help would be greatly appreciated, thanks in advance.
I currently have an Ungrouped Dataset being displayed in a Table. One of the columns include AMOUNT. This is an example of what my report looks like atm: ================== **Report Name**
No Name Amount
Signed By XXXX ==================
I would like to display a SUBTOTAL of the Amounts for each page and, on the final page, a TOTAL of the Amounts. Yes the Last Page's SubTotal and Total will be the same. Looking like: ===============================
pg1 **Report Name**
No Name Amount Sub Total Amount Signed By XXXX
(lastpage) **Report Name**
No Name Amount
(<-----no huge spaces between Table/Subtotal) Sub Total Amount
Total Amount
Signed By XXXX
=================================
Issues: I've found a way to get a Page's SubTotal, but the way I found requires this to be put in a field on the Page Footer Section. This means that on the final page, the SubTotal will be at the top of the Page Footer and not neccessarily directly beneath the table.
So: 1. Is there a way to move the Page Footer so that its printed DIRECTLY under neath the Page Body instead of at the bottom of the Page? 2. If not, is there a way to have the SubTotal of each Page and a Final Page Total in the Page Body section. Cheers.
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...
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
Hello All, I've created a report that has a count of dates reports. Every company has at least one date report. I've group the report by company and did a count this works find. Now I want to SUM up this field and put it at the end of the report. I've used =sum(reportitems!textbox10.value) but it only works at the page footer. And only give me a grand total for that page. I want a grand total for the whole report. Is there a report footer???
I need to get the Grand Total of the results of this query. The query pulls the total customer quotes for each community in a management company and loads my DataGrid:
This query works fine for individual community totals"SELECT TOP 100 PERCENT vcName, COUNT(DISTINCT vcCustId) AS " & _ " Total FROM dbo.PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' and " & _ " (vcEntryDate >= CONVERT(DATETIME, '" & StartDate & "', 102)) AND " & _ "(vcEntryDate <= CONVERT(DATETIME, '" & EndDate & " 11:59:59 PM" & "' , 102))GROUP BY vcName ORDER by vcName"
At first glance you would think that the following query would return the Grand Total for all communities in the management company:
Current Grand Total Query"SELECT COUNT(DISTINCT vcCustID) As gTotal FROM PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' AND vcEntryDate >= '" & Session ("StartDate") & "' AND vcEntryDate <= '" & Session("EndDate") & " 11:59:59 PM' "
But here's the problem. If there are multiple customer quotes created for different communites, then the customer(vcCustID) is only counted once in the Grand Total Query because I have to use DISTINCT, which of course only picks up one instance of the customer.
I am trying to calculate grand totals in the table footer area on a single table report. The report table has a grouping with a filter. I want the grand total to sum the visible grouping data but instead it sums the entire dataset. I've tried summing the textbox but RS does not allow that from the footer. I've tried specified the group for the scope but apparently RS scope works outward not inward in regards to groups. A simple example of my problem might be a list of bank accounts whose balance is overdrawn. In the report you want to show only the accounts whose balance has gone negative and all the transactions for those accounts. You can create a group on the account field and then a filter on the group to sum the transaction amount and filter out the accounts whose sum is not below zero. The grand total would supposedly sum the accounts whose balance is negative. Unfortunately the sum is on the entire dataset and therefore includes accounts with good balances. I'm sure some will suggest changing the SQL but that is not an option for me. These seems like a very basic operation for a report writer so I'm guessing there is some easy way to do this that has eluded me. Thanks for any help.
Im using Access and have created a query which adds up the grand totals of clients orders. However i want to be able to add up what each client has spent in total over any given time period. Basically in SQL logic terms:
take all order totals from same client and add together order totals to produce a grand client spend total.
how to add group subtotal and grand total in report? i try to add formula Sum(Field!Net_Weight.Value) in group footer and unable repeat footer on each page, it return same total on every pages. I hope to get subtotal on each page by group. the expected result would be like this:
I have a requirement where I need to show the maximum value in grand totals but for the dimension members the same measure has to sum.
For ex: lets say I have a measure called Test and this is a base measure. The aggregation type set  to this is SUM.
For this same measure the grand totals should not show the sum instead it should show the maximum value of the dimension members which is being analyzed across.
My FactEmployeeTable showing 13 rows of data related to employees, but i used a bridge table for connecting project and employee dimensions. In the ProjEmpBridge table i mapped only 6 employees data to the proj's data. When dragging projet name and employee name it is showing data related to 6 employees but grand total showing 13 as the result. I dont know why it is showing 13 instead of 6. Can anyone please solve this issue.
I am having some trouble getting a Grand Total column at the end of my matrix report (not row at the bottom), such as you get by default in an Excel pivot table.
I have managed so far to add another column that sums up all values across the data range for a particular product code. My intention was to hide all but the last (I did something similar on rows). However you cannot use an expression on the width property for columns, so this will not work.
My report is very standard: Year Month (grouped by year) Product code | Description [qty]
I want a column that displays the total qty across all dates for each row. Surely this is possible?
Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.
When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.
I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)
I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.
Any pointers in the right direction would be appreciated. I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦
I have a DB of professors and information related with them. I created the cube, it consist of: Measures: Measure group Professors: Amount of projects (COUNT proj_id) Amount of pulications (COUNT pub_id) Amount of e_books (COUNT book_id) -------------- Measure group Projects: Distinct amount of projects (DISTINCT COUNT proj_id) -------------- Measure group Publications: Distinct amount of publications (DISTINCT COUNT pub_id) -------------- Measure group E_books: Distinct amount of e_books (DISTINCT COUNT book_id) -------------- Calculated measures: Amnt_Projects iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects]) Amnt_Publications (similar to the above one) Amnt_E_books (similar to the above one) --------------------------- Dimensions: dimPROFESSORS - prof_id -surname -name -gender dimPROJECTS - proj_id -type name -name dimPUBLICATIONS - pub_id -type name -name dimE_BOOKS - book_id -name Date_Projects -date_id -years Date_Publications -date_id -years Date_E_books -date_id -years
For example, when I browse the cube: prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects 1032------------------- 30 --------------------------1----------------1 1070-------------------90 --------------------------2----------------2 1111-------------------0 ---------------------------1----------------0 1137-------------------0 ---------------------------1----------------0 1234-------------------1404--------------------------9----------------9 1721-------------------504--------------------------7----------------7 2661-------------------85 --------------------------5----------------5 ...--------------------...---------------------------...----------------... 6999------------------- 20---------------------------1-----------------1 9956-------------------50---------------------------5-----------------5 Unknown----------------(empty)---------------------(empty)-----------0 Grand Total------------ 2421------------------------11-----------------11
Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?
