Hiding extra rows in report.

Question

Hi,

Is anyone help me to hide the extra row displayed in the below screenshot in SQL Server 2008 R2 reporting services.

Item     Barcode

1    123

      456

2    789

      abc

Thanks,

Prasooth

Answer 1

Hi,

What do you call extra rows? 

could you please giveus an example of what you want.

Regards

http://simplesqlserver.wordpress.com

---

Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

Answer 2

You can define an expression that results in a Boolean to hide rows based on certain criteria. Right-click the row selector > Row Visibility >Show or hide based on an expression. Click the fx (expression builder) button and craft your expression.

Sorry this is not more specific but I do not have any details. I don't know the criteria you need to hide the row. I don't know what you consider and "extra" row, whether you might have more than one "extra" rows, or if you have dynamic rows (from row groups) or if they are all static.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 3

Hi Dj,

Many thanks for your response, I have two tables inner joined and running a report. Tables are from Navision.

Table 1 called Items and Table 2 Barcodes.

One Item number having multiple barcodes in Barcode table. I only need to display the first barcode.

Sorry I am not eligible to add image or link in this question.

Thanks,

Prasooth

Answer 4

Hi,

Select your Dataset and apply a filter on it : 

Filter on Item <> Null

Or 

Not IsNothing(Item)

please folow : http://www.mssqltips.com/sqlservertip/2597/dataset-and-tablix-filtering-in-sql-server-reporting-services/

Regards

http://simplesqlserver.wordpress.com/

---

Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

Answer 5

Hi Dj,

Sorry it doesn't work in my scenario as my Item No is not blank bu the cells are merged.

Item No |   Barcode

---------------------------------------------------

34356  |  5433334656

            |  3463346646

--------------------------------------------------

34333  | 3433446466

            |4545454545

--------------------------------------------------

Answer 6

Okey !

You can hide it in an other way :

Select your barcode cell and on hide property set :

=Iif(Fields!Barcode.Value= Previous(Fields!Barcode.Value) , TRUE, FALSE )

http://simplesqlserver.wordpress.com/2013/02/25/ssrs-grouping-at-the-right-of-tablix/

Regards

http://simplesqlserver.wordpress.com/

---

Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

Answer 7

Hi Dj,

But barcode values are not unique in my case.

Answer 8

Hi, Add with your formula itemid

---

Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

Answer 9

Hi Dj,

At last I got verified. Below is the screenshot. I only want to display one row for each item No.

---

Prasooth

Answer 10

Hi Tim,

At last I got verified. Below is the screenshot. I only want to display one row for each item No.

---

Prasooth

Answer 11

Hi, It is best if you can modifiy the query to get only one row of data for each item number. If you can do that - it is straight forward report.

---

Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Answer 12

Hi Sachi,

Could you please help me in detail, please see my query below.

----------------------------------------------------------------------------------

SELECT        [Aventus Global Trading LLC$Item Ledger Entry].[Item No_], SUM([Aventus Global Trading LLC$Item Ledger Entry].[Invoiced Quantity]) AS Qty,
                         [Aventus Global Trading LLC$Barcodes].[Barcode No_], [Aventus Global Trading LLC$Item Ledger Entry].[Location Code],
                         [Aventus Global Trading LLC$Item].Description, [Aventus Global Trading LLC$Item].[Unit Price], [Aventus Global Trading LLC$Item].Author,
                         [Aventus Global Trading LLC$Item].[Item Category Code], [Aventus Global Trading LLC$Location].[Name 2], [Aventus Global Trading LLC$Item].[Publisher Name],
                         [Aventus Global Trading LLC$Item Ledger Entry].[Entry Type]
FROM            [Aventus Global Trading LLC$Item Ledger Entry] INNER JOIN
                         [Aventus Global Trading LLC$Barcodes] ON
                         [Aventus Global Trading LLC$Item Ledger Entry].[Item No_] = [Aventus Global Trading LLC$Barcodes].[Item No_] INNER JOIN
                         [Aventus Global Trading LLC$Item] ON [Aventus Global Trading LLC$Item Ledger Entry].[Item No_] = [Aventus Global Trading LLC$Item].No_ INNER JOIN
                         [Aventus Global Trading LLC$Location] ON [Aventus Global Trading LLC$Item Ledger Entry].[Location Code] = [Aventus Global Trading LLC$Location].Code
WHERE        ([Aventus Global Trading LLC$Item].Author LIKE '%' + @Author + '%') AND ([Aventus Global Trading LLC$Item Ledger Entry].[Item No_] LIKE '%' + @Item + '%') AND
                         ([Aventus Global Trading LLC$Barcodes].[Barcode No_] LIKE '%' + @Barcode + '%') AND
                         ([Aventus Global Trading LLC$Item].[Publisher Name] LIKE '%' + @Publisher + '%') AND ([Aventus Global Trading LLC$Item].Description LIKE '%' + @Desc + '%')
GROUP BY [Aventus Global Trading LLC$Item Ledger Entry].[Item No_], [Aventus Global Trading LLC$Barcodes].[Barcode No_],
                         [Aventus Global Trading LLC$Item Ledger Entry].[Location Code], [Aventus Global Trading LLC$Item].Description, [Aventus Global Trading LLC$Item].[Unit Price],
                         [Aventus Global Trading LLC$Item].Author, [Aventus Global Trading LLC$Item].[Item Category Code], [Aventus Global Trading LLC$Location].[Name 2],
                         [Aventus Global Trading LLC$Item].[Publisher Name], [Aventus Global Trading LLC$Item Ledger Entry].[Entry Type]
HAVING        ([Aventus Global Trading LLC$Item].[Item Category Code] IN (@Cat))

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---

Prasooth

Answer 13

 Here is how you can do -- with below steps.

   -> Create a table #Final as the final table with required column -- with relevent data type. And make sure all the column is by default NULL.

    

create table #Final 
(
[Item No_], 
Qty,
[Barcode No_], 
[Location Code],
Description, 
[Unit Price], 
Author, 
[Item Category Code], 
[Name 2], 
[Publisher Name], 
[Entry Type]
) 

   --> Insert Items number from table 1 into #Final table

   --> Update #Final table with left join ...below is a sample.

  

 update F
 set <column1> = t2.<column1>,......
 from #Final F
 left join
 (
   select top 1 <column1>,...... from Table2 
 ) T2 on F.<itemnumber> = t2.<itemnumber>

And finally return #Final as dataset - which your report will use. So that way it is going to display without any all this hiding rows etc.

    

---

Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Answer 14

Prasooth, from your screenshot, you are retrieving multiple unique barcodes for a given item number in some cases. You must decide the criteria you will use to pick which of these to return. It can be as simple as removing [Aventus Global Trading LLC$Barcodes].[Barcode No_] from the Group By statement and changing it to MAX([Aventus Global Trading LLC$Barcodes].[Barcode No_]) in the select statement.

There is another option if you want both barcode values. You can display both barcode numbers in the same row of your table in the report by creating a row group on Item Number and using some special code:

    Private Num As String = ""
    Private tempstr As String = ""

    Function AggregateString(Group as String, Val as String)
      If Group = Num Then
        tempstr = tempstr + ", " + Val
      Else
        Num = Group
        tempstr = Val
      End If
      Return tempStr
    End Function

You can consume this function in the Barcode Number table cell by entering an expression similar to:

=IIf(Fields!Barcode.Value=-1,"",RunningValue(Code.AggregateString(Fields!ItemNo.Value+Fields!Name.Value,CStr(Fields!Barcode.Value)),Max,"ItemNo"))

Where Fields!ItemNo.Value is the the field or expression used in you row group, Fields!Barcode.Value is the name of your barcode field, and "ItemNo" is the name of your row group. This will concatenate all barcodes for a given Item Number into a single comma-separated string that will display together in the same row.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.