Hi I Have a matrix table with month on the rows and Year on the Columns

The expression I am using for the data is

=Count(Fields!data.Value,"Year")

This shows like this

	          2011_H1
Month 1	    256
Month 2	    256
Month 3 	    256
Month 4	    256
Month 5	    256
Month 6	    256

I only want to show the data value once so it looks like this

	           2011_H1
Month 1	       256
Month 2	
Month 3 	
Month 4	
Month 5	
Month 6	

I have tried using in the text box visibilty

=Iif(Previous(ReportItems!data.Value)=ReportItems!data.Value, true, false)

But I get an error message about aggregate functions can only be used on report items

How can achieve this?

Regards

HI,

It would be more helpful if you can provide how the data-set result set will look like. That will help in providing the appropriate solution to your scenario.

According to the given data example - you are seeing value '256' is repeating for all months. But, as you are using Matrix, the output will completely depends upon data set you are using for this.

Thanks,

Satish Chandra.

HI,

It would be more helpful if you can provide how the data-set result set will look like. That will help in providing the appropriate solution to your scenario.

According to the given data example - you are seeing value '256' is repeating for all months. But, as you are using Matrix, the output will completely depends upon data set you are using for this.

Thanks,

Satish Chandra.

Hi Satish the underlying dataset is at day level 2011-01-01 etc, in the the underlying set I have used some case expression to categorise some information

all the ssrs is doing is counting of the rows that fall under the month and year categories.

The only thing I want the column to do is not to duplicate the value

In that case try using Lookup function. It might help.

Try writing an Lookup expression on the data cell.

• Edited by Satish SQL BI 23 hours 27 minutes ago
• Proposed as answer by Satish SQL BI 23 hours 13 minutes ago

Does your backend query has any unique valued column ie ID value or date based one to pick unique instance out of ev

Ok. then isnt this enough?

make your backend query as

SELECT ROW_NUMBER() OVER (PARTITION BY DATEDIFF(yy,0,DateField),data ORDER BY ID ) AS Rn,*
FROM table

Now in your report make expression as

=Count(IIF(Fields!Rn.Value=1,Fields!data.Value,Nothing),"Year")

Ok. then isnt this enough?

make your backend query as

SELECT ROW_NUMBER() OVER (PARTITION BY DATEDIFF(yy,0,DateField),data ORDER BY ID ) AS Rn,*
FROM table

Now in your report make expression as

=Count(IIF(Fields!Rn.Value=1,Fields!data.Value,Nothing),"Year")		

Hi aivoryuk,

Just as you said, the previous aggregate function couldnt be used in a tablix cell in Reporting Services. After testing the issue in my local environment, we can refer to the following methods to work around the issue.

Method1: Use RowNumber function to control the textbox visibility to only show the last value in the Year column.

1. Right-click the cell which contains the expression =Count(Fields!data.Value,"Year") to open the Text Box Properties dialog box.
2. Select Visibility tab, use the expression below to control the visibility:
   =iif(RowNumber("Year")=reportitems!Year.Value,false,true)

The following screenshot is for your reference:
 
Method2: Use custom code to get the previous value.

1. Copy the custom code below and paste it to your report. (Right-click report>Report Properties>Code)
   Public Shared previous as string
   Public Shared current as string
   Public Shared Function GetCurrent(Item as string) as string
   previous=current
   current=Item
   return current
   End Function
   Public Shared Function GetPrevious()
   return previous
   End Function
2. Replace the original expression
   =Count(Fields!data.Value,"Year")
   with
   =Code.GetCurrent(count(fields!data.Value,"Year"))
3. Use the following expression to control the visibility of the textbox:
   =iif(Code.GetCurrent(count(fields!data.Value,"Year"))=Code.GetPrevious(),true,false)

The following screenshot is for your reference:

If you have any other questions, please feel free to ask.

Thanks,
Katherine Xiong