SSRS: Sum Of A Column With Subtotals Based On A An ID Group

 
Hello,
I'm just starting to analyze with SQL 2005 reporting services and trying to build a test report using the report wizard.
 
This is how i've got it in SQL reports now:
 
Opportunity ID             Product Name    Cost
---------------------            ---------------------  ----------------
1                                 Orange               $1
1                                  Apple                $2
1                                 Grapes               $4
2                                 Orange               $1
2                                 Apple                 $2
3                                 Orange               $1
 
 
Now, what if I wanted to group them to provide subtotals like:
 

Order                          Product Name    Cost
---------------------            ---------------------  ----------------
1                                 Orange               $1
                                   Apple                $2
                                   Grapes               $4
                                  -------------------------------
    SubTotal                                             $7
 
2                                 Orange               $1
                                  Apple                 $2
                                   -----------------------------
    SubTotal                                           $3
 
3                                 Orange               $1
                                   ------------------------------
    SubTotal                                           $1
 
The problem that i'm having right now is on the layout designer somehow programming the subtotals based on the IDs.
Am i thinking in the right direction that I should be using the layout designer to do this? Or should this be done programmatically?
 
Thanks.
Mike
 

View Replies


ADVERTISEMENT

Mutiple Column Group Subtotals, Custom Subtotals Expressions/Calculations

 


































Some DB Field ID
X
                                                                     2007
                                                                                2008

Jan
Feb 
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
A
X-A
Jan
Feb 
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
B
X- A -B

1
500
10
20
0
0
0
0
0
0
0
20
50
0
100
400
25
10
10
0
0
5
0
25
15
10
10
20
130
270

2
750
10
10
10
20
20
10
10
20
10
10
10
10
150
600
20
20
20
20
30
30
10
10
10
30
30
30
260
340

3
600












 
 












 
 

 

 

All,

 

I am trying to achieve something as above. Basically, the Months subtotals are represented by A and B. Then (X-A) and (X-A-B) are also the subtotals at the same group level as A and B but don't simply display the total for respective years 2007 and 2008, instead those are remaning totals from X. In order to calculate the remaining totals however, one need to consider the subtotal in previous group. For example, for 2007 its X-A, but for 2008 its X-A-B. I would like to know if this can be achieved using Matrix control. If so, what would be the steps?

 

Thanks.

 

 

View Replies View Related

Matrix Sub Total Column Width And Multiple Subtotals At Same Column Group Level

All,
 
I have two questions. First how can I make the width of the subtotal column in a matrix bigger than the column group cells. Secondly, how can I display two subtotals at the same group level, say one that adds all the columns and one that provides the product of subtotal and some value X?
 
Thanks.

View Replies View Related

Hiding Column Group Based On Row Group In Matrix...

I have a matrix that has a row group with page break after each and a column group.  I want to show only certain columns when row group is equal to (something).  Is this possible?

 

View Replies View Related

How Can I Hide A Table Column Based On A Group ?

I am using Reporting Services 2000. If you find out that Reporting Services 2005 would resolve this issue, please lemme know also. But I want to mention that I would prefer a way to fix this without changing Reporting Services versions.

I have a table that has a group on ProductTypes. This group is set to PageBreak at end.
What I need to do is to conditionally hide an entire column based on the current group's ProductType.

Examples
Page 1 = Product 1 = Column 1 is visible
Page 1 = Product 1 = Column 2 is visible
Page 2 = Product 2 = Column 1 is hidden
Page 2 = Product 2 = Column 1 is visible

Can you help me figure this one out ? I've tried everything I found on the net, especially everything on this page : http://blogs.msdn.com/chrishays/rss.xml

Thank you in advance.

View Replies View Related

Expressions Based On Subtotals

How do I create calculated fields based on subtotals?

View Replies View Related

Help With Group Subtotals

I have contracts grouped by project then grouped by division. On the  division group header I want to show the subtotal of all active projects only.  CountDistinct(Fields!ProjectId.Value) gives me count of all projects. 

I can't figure out what I need to get only a subset of projects counted. I need something like:

CountDistinct(Fields!ProjectId.Value & Fields!ProjectStatusId.Value = 1)

but that isn't allowed.

Any help appreciated.

View Replies View Related

SSRS 2005 - How To Have Multiple Subtotals In Matrix

Is there any way to get multiple subtotals in a matrix?  For example, one that does a count and the 2nd that does an averages as per the desired result below ...





Code Block

       A   B   C
A      1   2   3
B      2   3   4
C      3   4   5
Total  6   9  12
Avg    2   3   4  

View Replies View Related

Subtotals In Table (group Footer) Using Report Items 2005

How can I calculate a subtotal for a Report Item? I have a textbox(lets call it "PlusMinus") in the detail section of my table, which is a calculated textbox of two others (lets call them "Budget" and "Spent"). So, PlusMinus = (Budget - Spent). What I would like to do is get a subtotal for PlusMinus. I have tried several ways, using Sum() or RunningValue, even tried to write code, but I can't seem to get it right. Any ideas??
 
Thanks in advance!

View Replies View Related

Need Script For Column Visibility Based On Group's Visibility Toggle

 

Hello,
 
I'm using SSRS 2005
 
I'm trying to write an expression for the "Hidden"  property of a column in a table. The column is only populated with data if the group on my table is open. If the group is collapsed, then the column is empty. I'd like to make the column hidden if the group is collapsed. So I'm thinking it would be something like this:
 
=Iif(Table_1_Group.Hidden = "True", "True", "False")
 
But I can't find anyone who has written this anywhere.
 
I'm basically trying to make this report do the same thing a matrix does, but the matrix doesn't let me label the columns. I put textboxes above the matrix in line with the columns, but when I deploy the report to ReportManager, the textboxes get thrown all over the place and don't line up with the columns anymore.
 
So I guess if you can't answer the first question, an alternate question I have is how are you supposed to label the columns (row groups) in your matrix? They don't have headers, if I try to line textboxes up with them they get all messed up during deployment. They have that textbox up there in the top left corner that runs across the top of all of the row group columns, but if I put labels separated spaces in that textbox, the spaces get removed in rendering so the text is pushed all to the left and doesn't line up anymore.
 
Thanks,
Andy

View Replies View Related

How To Have 2 Subtotals Under Column Side For Reports?

Hi to all,

 

I am a newbie to SQL Report 2005 and currently i am required to a report with a format something like this.

 

                                                Month         Total              Total (%)

Employee A

Employee B

Employee C

 

Currently, i am able to generate a table with [Month], subtotal[Total], Employee rows...

 

I do not know how to display another subtotal of [Total(%)] which calculates the percentage of the Total (i.e, Total/GrandTotal x 100%)

 

Any advice is appreciated.

Thanks

 

Rgds

Eric

View Replies View Related

How To Use Group By (group Tasks Based On Projects)

Hi folks,

               I have a Projects , each project have many tasks now i want to display tasks replated to each project:

for example:

            

 

 

    Project1-------------------->task1

                                              task2

                                               task3

                                              task4

 

Project2----------------------->task4

                                              task5

                                              task6

 

 

.............................................projectN.....................

 

 

how to write query for this

 

i have 2 tables:

Project .......>columns are projectid

Task------------->columns are projectid, taskid

|


 

View Replies View Related

Problem With Subtotals Of A Matrix (Blank, Pdf, Empty Column)

Because i get no answers i will try it again:
 
Hi,
 
i have the following problem:
I have a matrix with a right subtotal column and this matrix was in a list (because in the end i will have more than one matrix). The list fits perfectly the matrix in design mode. But if i render the report in the viewer or to pdf, an additional blank area (like a blank copy of the subtotal column) was inserted after the right subtotal column of the matrix and increases the list too. You can see this easy by set the backgroundcolor of a list to a color. Without the subtotal column the list fits perfect after rendering. The problem is that this additional blank "column" creates empty pages in .pdf rendering, if the width of the matrix is near the page width. The same behaviour happens if i put the matrix with subtotal in a rectangle.
I must use a list in the end because the the final report contains some matrixes and a subreport. 
So is this a bug?
Someone must have this problem too?
 
Thanks for any help.

View Replies View Related

Column Based On Other Column (short Name Based On Name), When To Do The Transformation?

Hi!
 
I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:


Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName

Create a derived column in the SSIS flow which does the same thing

Create the ProductShortName column as a computed column which uses substring on ProductName

Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated

Create a named calculation in the table in the Analysis Services project's data source view

Create a named query in the Analysis Services project's data source view

I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?
 
Thanks!
 
 

View Replies View Related

Subtotals In Report Give &&"Infinity&&" Value In Case Of Empty Cells In Column.

The title, I think, accurately illustrates the situation in my report.
Naturally, this result is unwanted.
 
How do I get the avg-function to ignore empty cells in the column?
 
Thanks in advance,
 
Pluggie

View Replies View Related

Sum In A Group Based On Other Group Value

 

Hi all,
I have a Report and I have used Table in the report to define 5 levels of Sub Groupings: Group1, Group2, Group3, Group4, grpResourceBehavior.
 
The Sample data which comes is:
Group1    Group2    Group3    Group4    ResourceBehavior    BgtHrs   BgtAmt
   A1             B1         C1           D1         NML                        12         12
   A1             B1         C1           D1         ODC                         0          12
 
Now, to calculate a field "Cost", I have to use following condition. If the ResourceBehavior is NML, then Cost = BgtAmt/BgtHrs
But, if the ResourceBehavior is ODC, then COst = BgtAmt/(SUm of BgtHrs for the resourcebehavior NML)
 
I am not able to calculate the Sum of the BgtHrs for different ResourceBehavior. Also, I need this field calculation for each and every group as well, and this makes it all the more complicated.
 
Any help is appreciated.
 
Thanks in advance.
Swati

View Replies View Related

Query Based On Group By Clause

Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**

if no employee is absent in the department it has to display Zero

View Replies View Related

SSRS Group Authentication

Added an AD group to SSRS both in the properties of the server with system user access and gave it role "browser" but users are denied access. Why?

View Replies View Related

SSRS Conditional Group?

Hi,
 
I was wondering if there was a way to do a conditional group in Reporting Services or do I need to create another report?  One user wants it grouped by a certain field and another user does not want it grouped at all.  I know I can hide the group header with a parameter but I don't want it sorted by the group either.
 
Thanks for any help.
 
Fred

View Replies View Related

SSRS - Matrix Row Group

I have a matrix with six rows and the column is dynamic depending on if any data exists for any row item in a month/year.  All rows are summations of data for the particular month.  I have a row group that when toggled to visible expands all the rows.  Is there a way to restrict the row group to only expand the data in a specific row?

View Replies View Related

Query To Find A Value In Column B Based On An Aggregate Function On Column A?

Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy

View Replies View Related

Assigning Values Based On InitialToggleState Of A Control In SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

 

 

View Replies View Related

Using Prompt Parameter Into SSRS Report Based On Sql Query

Hi All,

I have a question regarding how to use report prompts in SSRS reports that are based on SQL queries.
When I added the prompt into the query for use as a filter value, it says that there is an error.
It does not recognise the '!' inside the parameter prompt string. Example is parameter!month_prompt.Value, which the ! is not recognised.

Your help is much appreciated.

Thanks & Regards,
Mohd Fadzli

View Replies View Related

Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?

 
Table structure as follows
 
 
 
Employee
 
Empno                  empname                           salary
commission
 
 
 
I want to have an other employee table named  employee_modified
 
Empno                  empname                           salary
commission                        derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx)    and so on derive other
columns based on the earlier derived columns)
 
Is that possible to do it.. or am I doing something wrong.
 
 
 
something like
 
 
 
Select empno , empname , salary , commission,
 
(salary + commission) as derived_colum1 ,
 
(derived_colum1 + xxxxx) as derived_colum2 ,
 
(derived_colum2 + xxxxx) as derived_colum3
 
into employee_modified from employee

View Replies View Related

SSRS: How To Get A Page Break After A Value In A Group

This question is related to SSRS: There are 3 groups in my report– grp1, grp2 and grp3, with grp1 at the top, grp2 in the middle and grp3 at the bottom. I need to insert a page break when the value of grp1 record becomes “abc”. Is this possible? If so, I will really appreciate if someone can let me know how.

Thanks in advance,
Saurav

View Replies View Related

SSRS Adding Group Rows

 

Hello Friends,
                     I have created a report using SSRS and in that report I am using group rows in one of the matrix. When I tried to display the subtotal of that group row by using the SSRS in-built feature the subtotal column is coming at the last of the matrix columns but I want it at the front .
 
so it will be like this,
 
First the total should be displayed then the group members value.
 
Can anyone help me on this issue.
 
Thanks & Regards
Shivanandan Gupta

View Replies View Related

SSRS GROUP PAGE BREAK

I am using three groups in my report on a table. The option "page break at end" is checked in all three groups. It is working fine as for report output is concerned but in print preview it generates extra pages. In print preview page break gets occured at every group regardless of the fact that group break occurs or not. As a result, it is generating extra pages. please help me out
 
thanks.

View Replies View Related

Sorting Parameter Values In SSRS Model Based Report

Hi,
 
I have created a report using Report designer (Visual Studio,  using Data Model as a data source), in the report I had created few datasets (with single filed) to populate the report parameters, lets  say I have created a multi valued Parameter CustomerName and assigned field from a dataset,
 
result are coming correctly and combo box is getting populated but the customers are not in alphabetical order!
 
I want to sort it and need to specify it in report (please note that I am using Report Model as a data source and I can€™t sort the source table in the data base to get the result sorted)
 
 
Please let me know if anybody has done that or forward me if know some link which talks about it.
 
 
Thanks in advance.
 
Regards,
 
Jayant Jape
 

View Replies View Related

Change Property Based On Actual State Of Collapsed/expanded Group

Hi, please I would like to use something like this>

=IIF(table1_group2 is Collapsed,True,False)

I want to generated some event based on actual state of e.g. report group. if user expand group make this event otherwise (group is collapsed) make another event.

Thanks for your advice.

View Replies View Related

Display Data In Reports Based On Active Directory Group Membership...

Hello,
 
I am fairly new to SQL 2005 and Reporting Services.
 
We are trying to create a report that will display sales data based on group membership from Active Directory.
 
For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group.  If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group.
 
Background Information:  We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services.  We are delivering the reports through a Sharepoint site.
 
Please let me know if anyone has a good way to tackle this.
 
Thanks,
 
Justin

View Replies View Related







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