MS SQL Server :: SQL Server 2008 :: Field As Group Header?

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

SQL Server 2012 :: Group By Column As Sub Header

Jan 28, 2014

I have a table which I would like to format like so:

From this:

CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[header] [varchar](50) NULL,
[citation] [varchar](200) NULL,
CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED

[Code] ....

Just as a single varchar(max) field.

View 9 Replies View Related

Any Way To Show A Group Detail Header Row Once For Each Group In A Table?

Nov 21, 2007

I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.

View 1 Replies View Related

Repeat First Row Group Header For Second Row Group Items

Jun 6, 2007

I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.

Example:

Now:

Code Snippet
Column Column
Section1 -

Mnemonic

Mnemonic

Mnemonic

Mnemonic

Section2 -

Mnemonic

Mnemonic

Mnemonic

Mnemonic

Should be:

Code Snippet

Column Column

Section1 -

Section1 Mnemonic

Section1 Mnemonic

Section1 Mnemonic

Section2 -

Section2 Mnemonic

Section2 Mnemonic

Section2 Mnemonic

View 2 Replies View Related

SQL Server 2008 :: Selecting Field That Contains Data From Another Field

May 28, 2015

We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.

ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY

what I need to do is identify where the Brand is in the Description field ...

I have tried ;

select * from Table
where Description Like Brand

not very successful.

View 3 Replies View Related

SQL Server 2008 :: Bulk Import And Create New Table Based On Header Fields Of Imported File (XLXS)

Sep 11, 2015

I have a record in an Excel format (Excel 2010) and I would like to bulk import that into SQL Server 2008 and also while importing, SQL Server will automatically create a new table based on the header fields or row of the source file.

I am not sure if SQL Server 2008 has this capabilities.

View 0 Replies View Related

SQL Server 2008 :: Group By Total Sum

Oct 31, 2015

Despite the products are different, I want to get the sum of all products according to customer card.(client ref)

select (ORF.PRICE) *( ORF.AMOUNT - ORF.SHIPPEDAMOUNT) from [.dbo.LG_032_01_ORFLINE ORF JOIN [.dbo.LG_32_01_ITEMS ITM ON ORF.STOCKREF = ITM.LOGICALREF where ORF.CLIENTREF = XXX

View 1 Replies View Related

Group Header??

Feb 13, 2008

I need to add a group header to my report. What the heck am I doing wrong, when I add new row above/below my group it adds another group row. How do I make a header within a group?

Please help, very frustrating.

View 4 Replies View Related

SQL Server 2008 :: Getting Row Numbers For Each People Group?

Feb 18, 2015

I have a set of rows in a table like for example

Client ID Client Name Date Score
1 Smith 12/31/2014 25
1 Smith 10/15/2014 45
2 John 08/11/2014 55
2 John 06/18/2014 15
3 Rose 04/15/2014 12
4 Mike 07/23/2014 28
5 Mary 01/5/2014 56
6 Lisa 08/1/2014 54
6 Lisa 05/10/2014 34

Now I want to use Row Number function or any way where I can get the result as below

Client ID Client Name Date Score RowNo
1 Smith 12/31/2014 25 1
1 Smith 10/15/2014 45 2
2 John 08/11/2014 55 1
2 John 06/18/2014 15 2
3 Rose 04/15/2014 12 1
4 Mike 07/23/2014 28 1
5 Mary 01/5/2014 56 1
6 Lisa 08/1/2014 54 1
6 Lisa 05/10/2014 34 2

View 3 Replies View Related

SQL Server 2008 :: Using Expression In GROUP BY Clause

Feb 23, 2015

I am working to move an application from MySQL to SQL Server. The person who developed the MySQL application has little database experience, and took some shortcuts that the lax nature of MySQL allows. One query with which I am struggling looks something like this:

SELECT StartTime + Offset, min(Sensor), max(Sensor)
FROM SensorData
WHERE SensorID = @SensorID AND
StartTime + Offset > @BeginTime AND
StartTime + Offset < @EndTime
GROUP BY (StartTime + Offset) / 100
ORDER BY StartTime + Offset

What we are trying to accomplish is to return minimum and maximum sensor values over a number of periods between the BeginTime and EndTime. When I run this query in MySQL on a sample dataset, it returns a small number of rows, with each one being the min/max values for a portion of the overall period.

Under MS SQL Server 2008, SP3, I get the two following error messages:
Column 'SensorData.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'SensorData.Offset' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

View 9 Replies View Related

SQL Server 2008 :: How To Remove Group By Clause

Mar 5, 2015

SELECT DISTINCT

'Banquets - All Day' as revName,
SUM(t.c_items_total) AS Banquet_Total,
SUM(t.cover_count) as Total_Covers,
-- (t.c_items_total) / (t.cover_count) as AvgPer_Cover--

[Code] ....

The output needs to be grouped by the t.c_items_total...I just need the avg per cover (person) / items_total.

View 4 Replies View Related

SQL Server 2008 :: Get Max Of Group No Matter Currency

Aug 5, 2015

I have this data below :

isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)

DEX | XFFA | DEM | 013 | 2009 | 5 | 8
DEX | XFFA | EUR | 013 | 2014 | 11 | 25

get this data by running the following query :

select b.isin, a.market, a.ccy, a.stock_mkt, max(a.year)
,max(a.month), max(a.day) from market_table a inner join
isin_table b on a.id = b.id where a.isin_closing_date=0
and a.market in ('XFFA', 'XFFA') and
b.isin_type= 'I' and b.isin = 'DEX' group by b.isin,
a.market, a.ccy, a.stock_mkt

what's needed is to get the earliest record all times, no mater the currency :

isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)
DEX | XFFA | EUR | 013 | 2014 | 11 | 25

View 3 Replies View Related

Scrolling Group Header

Jul 25, 2007

I am using table to display data.

I want to have group with group header.Group header needs to remain fix while scrolling down.

View 2 Replies View Related

How Do I Just SUM The Group Header Values?

Jun 21, 2007

Here's my table so far in RS:

Facility Name | Claim | Fee | Value | Payment | Table Header
Wisconsin West | | | | | Group1 Header
| 2356 | $45 | $23 | | Group2 Header
| | | | $21 | Details
| | | | $7 | Details
| | | | $16 | Details
| 2357 | $85 | $47 | | Group2 Header
| | | | $21 | Details
| | | | $9 | Details
| | | | $13 | Details
| 2358 | $105 | $65 | | Group2 Header
| | | | $35 | Details
| | | | $12 | Details
| | | | $20 | Details
Facility Totals | 3 | $705 | $405 | $154 | Group1 Footer

*Notes = Table and Group2 footers are hidden and contains nothing. The last column is detailing what level each line is and not actually part of the table.

I believe my issue is with the SUM function and how or where to place it. In the Payment field, the SUM function is adding correctly, but for the Fee and Value field, the SUM fuction is adding as if every line item in the payment field had the fee and value amount, hence the huge amount.

How can make the Fee field total to $235 (45+85+105) instead of $705 (45+45+45+85+85+85+105+105+105); the same goes for the Value field of $135 (23+47+65) instead of $405 (23+23+23+47+47+47+65+65+65).

Below are the expressions I have in place at the Group1 Footer for the Claim, Fee, Value, and Payment fields respectively:

=CountDistinct(Fields!ClaimNumber.Value

=Sum(Fields!dAmount.Value)

=Sum(Fields!Value.Value)

=Sum(Fields!cAmount.Value)

I've tried playing with the scope, but to no avail. Any ideas or maybe I'm doing it all wrong? It's almost as if I need a SumDistinct if such a thing exist.

In the meantime, I'm doing some serious researching. Thanks!

View 1 Replies View Related

Text Above Group Header

Feb 20, 2008

Dear all,

The report I am trying to build are grouping the data by CATEGORY AND SUB-CATEGORY as shown below.

Product Category
Category1 Total: 5

Sub-Category1 2
Sub-Category2 3

Is it possible to put a static text above the Sub-Category group header row as shown below.

Product Category
Category1 Total: 5

Product Sub-Category (this is the line of text i want to added above the subcategory group header.)

Sub-Category1 2
Sub-Category2 3

Regards,

Yu

View 7 Replies View Related

Hiding Header Row In A Group

Mar 21, 2007

I have a table with a group. I have one row of the group that is the header for the detail section. How can I suppress the row header in the group if there is no data in the detail section for a group value? I was thinking something along the line of setting the visibilty of the row header to an expression based on the existence of data in the detail, but don't know how to go about this.

Thanks.

View 2 Replies View Related

SQL Server 2008 :: Moving Tables To New File Group

Mar 16, 2015

I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.

So, how do I get the free space out of the data file? - I've decided to:

1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables

There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue. Are there any other ways I can get the free space back to the OS?

View 8 Replies View Related

SQL Server 2008 :: Check Constraint On Group Of Records?

May 25, 2015

I have groups of records in a table, and I would like to set a necessary condition on each group. The condition is that EXACTLY ONE of the records in each group has a flag field set to True (bit = 1). I can naturally write triggers for update, insert and delete events that test for such a condition.

Something along the lines of this condition:

(select count(ClovekAutoID)
from TableOfClovekNames tCN
where JeHlavni = 1
group by ClovekAutoID
having COUNT(JeHlavni ) > 1) = 1In fact,

I tried this just on whim, but naturally, the SS engine told me to go roll my hoop, that subqueries are not allowed in constraint expressions.

View 9 Replies View Related

SQL Server 2008 :: Query To Group And Find Latest

Aug 25, 2015

I have a scenario as below for one ID -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 10.00 | 2015-08-25 12:05:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
| 5689 | 130.00 | 2015-08-25 12:07:57.107 | 3 |
+------+--------+-----------------------------+-----+

I want to fetch below 3 records from the above scenario i.e. latest record of each amount (Latest is determined using "descr" column i.e. 4 is greater then 3 -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+

But in case of same amounts I am unable to fetch the latest status as even using partitioning will treat them as one.

CREATE TABLE #TMP
(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)

INSERT INTO #TMP VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')

View 8 Replies View Related

SQL Server 2008 :: Get Total Number Of Attachments Of Group ID

Sep 10, 2015

Copy and paste the code to review sample:

Create table #temp(
ID varchar (20),
ID_BegAttach varchar (20),
ID_EndAttach varchar (20),
ID_AttachLvl varchar (20),
ID_NumofAttach varchar (20)

[Code] ...

I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.

View 9 Replies View Related

Aggregates Displayed In Group Header.

Nov 12, 2007

I have a report that consists of three nested group levels. Each level can be expanded/collapsed. I wanted to have at each level the summed values:

e.g.

+ Group Header 1 Sum1 Sum2 etc..

+ Group Header 2 Sum1 Sum2 etc..

+ Group Header 3 Sum1 Sum2 etc..

Rather I have had to output the aggregated values like so ..

+ Group Header 1

+ Group Header 2

+ Group Header 3
Total Group Footer Sum1 Sum2 etc..
Total Group Footer Sum1 Sum2 etc..
Total Group Footer Sum1 Sum2 etc..

Is there a way to display the aggregated values at the header level of the grouping. I thought this could be possible if I could hide the Group Footer and directly reference the footer sum total values in the header. Any help would be greatly appreciated.

Theo

View 1 Replies View Related

Group Header Alternating Color

Apr 28, 2006

I am trying to get alternating colors on group headers.

The rownumber() doesn't work; that only seems to be the count of rows in the group.

Does anyone have any great ideas for this?

Thanks!

BobP

View 11 Replies View Related

Group Column Header Visibility

Mar 12, 2008

I have a table with 5 levels of grouping and a detail row. When the report first displays it looks like this:

Group1 Hdr Group2 Hdr Group3 Hdr Group4 Hdr Group5 Hdr Detail Headers

+Group1-Item1
+Group1-Item2
+Group1-Item3

How can I alter the visibility of the group headers when they don't have access into the scope of the groups themselves ? At startup, I only want to show group1's header. If I expand an item in group 1, then I only want to show headers for group 1 and 2, etc ...

Thanks

View 1 Replies View Related

How To Add A Group Header And Footer For A Report

Jan 9, 2007

I have a report that is being called via stored proc, and i want to group by contract. when report gets generated i get multiple contracts info. but will be grouped/sorted by contract.

please how can i have a group header and also a group footer to show a summary of each contract information with some calculated fields in it.

i may get 100 records related to 10 contracts , 10 rows for each contract.

as soon as the first contract info is shown on the report it has to show a summary related to the first contract in the group footer, and then continue populating the second contract info and so on.

Please i am totally new to reporting and help would be appreciated. thank you all.

View 2 Replies View Related

Display Group Header With No Items‎

Jul 24, 2007

I have to display group header inspite of not having records in that group.

Group1 [First page orgid1]

values
Group 2valuesgroup1 [Second Page orgid2]

No values group2 no values

View 1 Replies View Related

How Do You Edit A Group With No Header Or Footer?

Jun 5, 2006

I am new to SSRS 2005 and so far the only way I can figure out how to edit groups in a table is to right click on the group header or footer and select Edit Group. But how would you edit the group if you chose to not have a header or footer? And how would you delete the group for that matter? I'm looking for something like the Crystal Reports Group Expert.

View 4 Replies View Related

Group Header Repeating In Exported Csv

Jul 25, 2007

Need to export a report into csv. The report contains a table with one group, the group header contains infomation that needs to be shown once at the start of the csv. Currently the group header repeats for every record in the detail. How can I stop the group header from repeating.

Thanks

View 4 Replies View Related

SQL Server 2008 :: Aggregate Function Or GROUP BY Clause Error

Apr 13, 2015

While running the below query, getting the error: Am I missing any of the columns to include in the SELECT column_list?

Msg 8120, Level 16, State 1, Line 1

Column 'sys.master_files.database_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

<code>
select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate
from
(
SELECT database_id,[Database Name]= DB_NAME(database_id),
[Type]= CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END ,

[code]...

View 3 Replies View Related

SQL Server 2008 :: File Group Is Running Out Of Space Frequently

Sep 1, 2015

We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.

Error:

The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.

tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB
templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited

View 9 Replies View Related

Fixed Group Header Row Height Problem

Dec 12, 2007

Hi all,

I am looking for help with a problem:

I am using the fixed header functionality in reporting services (SS2005) to keep the 3 left-hand-most columns of a table always visible as my report scrolls to the right. To do this I set the "fixed header" value for those columns to true.

My problem is that some of the group headers resize as I scroll to the right - everything looks normal before I start scrolling, but immediately as I move the scrollbar, some of my group headers grow (row height increases), throwing the headers out of line with the data row in the columns that aren't fixed.

It seems it's only certain headers that grow - they alternate between growing and normal size. I can't find any differences in the properties of the textboxes between those whose height increases and those that don't.

I have tried setting the CanGrow property in the relevant textboxes to false, to cater for the possibility that something might be trying to wrap - that didn't make any difference (which is what I expected, seeing as the text is never wider than the textbox width).

Does anyone have any ideas as to what the problem might be?

Thank you,

Regards,

Fritz

View 10 Replies View Related

Dynamic Grouping Group Header Problem

Jan 23, 2007

Hi, I am currently trying to create a report the dynamicaly groups from parameters. The grouping part works fine but I need to show the parameter label name or the field referenced by a parameter.

=iif(Parameters!Param1.Value="P",Parameters!Param2.Label,Fields(Parameters!Param1.Value).Value)

This is the expression I have written to do this. The problem seems to be is with the true part of the iif statement. The false displays fine in the group header. The Parameters!Param2.Label also displays if used on its own.

This is the error that is fires back when I run the report.

The Value expression for the textbox ��textbox4�� contains an error: The expression referenced a non-existing field in the fields collection.

I am quite new to SSRS and I am using VS2005 pro.

Thanks

D

View 1 Replies View Related

Group Header && Footer Hidden By Mistake

May 2, 2008

Hi,

I accidentally unclicked the check-boxes for group-header and group footer - I meant to leave the group header in. Now, I can't get to the group to edit it! Is there any way I can get back to it?

View 1 Replies View Related