SSRS - Row visibility expression

Question

I am using SSRS 2014.

I want to display only first 50 rows. What will be the expression for this please.

I used top n functionality it doesn't work. So I thought to take another route to achieve the result via expression.

Below image with out adding 'top n'. In the below image I wanted to display only first 50 rows in Supplier column

Below image with adding 'top n'. Then results are changed. Few rows in Concession column are missing.

SQL code is,

    SELECT  s.[CusNo] Supplier, 
RTRIM(CAST(s.[Customer] AS VARCHAR(50)) ) AS Name,
s.[ConcessionNo] Concession, 
RTRIM(CAST(s.[ConcessionName] AS VARCHAR(50)) ) AS ConcessionName,

sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
           then s.SELLINC else 0 end) ActualSales,

    sum(case when s.Date 
        BETWEEN         
             convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
        AND        
             Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5)       
           then s.SELLINC else 0 end) YrToDateActual


FROM [dbo].[CustomerReports] s
WHERE s.BRN = 1 or s.BRN = 2 or s.BRN = 3 or s.BRN = 4 or s.BRN = 5  or s.SELLINC is null or s.SELLINC = '0'
GROUP BY s.[CusNo], s.[Customer], s.ConcessionNo, s.ConcessionName
order by YrToDateActual desc

Any help please?

http://stackoverflow.com/questions/18085716/ssrs-display-top-n-but-have-sum-include-all-values — Snowlockk, Mar 08 '17 at 08:30
Can you please add atleast 10 sample data so that we can give you an accurate solution. — Jacky Montevirgen, Mar 08 '17 at 08:51
one of the simplest way is to use Sub report for detail part that you are filling and for Group you can use main report in that query you can put top 50 so it will get only 50 records for Group section and in each of the Tablix row you can bind sub report and pass ID of Group header as a parameter of Sub report and fire individual Query to get detail result. — Kevin Shah, Mar 08 '17 at 09:13
Please refer, http://stackoverflow.com/questions/42668622/set-expression-for-hidden-ssrs — user2331670, Mar 08 '17 at 16:17
The wording of this question is ambiguous. Do you want to return 50 individual concession rows, or 50 suppliers? — Wesley Marshall, Mar 11 '17 at 05:17

score 0 · Answer 1 · answered Mar 11 '17 at 05:24

If you want the first 50 suppliers, you should be able to use the following expression in the row visibility property:

=RunningValue(Fields!Supplier.Value, CountDistinct,"YourDataset") > 50

The report should perform better if you put this expression as a filter on the group instead of a row visibility expression, but at the moment I can't test to confirm whether this expression would be allowed there.

score -1 · Answer 2 · answered Mar 08 '17 at 16:02

-1

Modify your ORDER BY clause to:
ORDER BY s.[CusNo], YrToDateActual desc

answered Mar 08 '17 at 16:02

William Xu

241
2
12

SSRS - Row visibility expression

2 Answers2