Hide rows such as 4, 8, 14, 18, 24, 28 and so on

Question

Post reply

Hide rows such as 4, 8, 14, 18, 24, 28 and so on

vigneshlagoons 51204

Old Hand

Points: 354
More actions
October 20, 2016 at 9:13 am

#331483

Hi,
I need to hide rows like 4, 8, 14, 18, 24, 28 in my report and how can I write an expression.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 1

You could prevent that data from coming back to the report in the first place by doing something like:

WITH numbers AS

(

SELECT

ROW_NUMBER() OVER ( ORDER BY id ) AS num

FROM

syscolumns

)

SELECT

*

FROM

numbers

WHERE

RIGHT(CONVERT(VARCHAR(10), numbers.num), 1) <> 4

AND RIGHT(CONVERT(VARCHAR(10), numbers.num), 1) <> 8 ;

Thomas Rushton
blog: https://thelonedba.wordpress.com

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

That's a weird requirement. What's the story behind this?

Here's a shorter alternative which I expect that might be a bit faster (not much).

WITH numbers AS

(

SELECT ROW_NUMBER() OVER ( ORDER BY id ) AS num

FROM syscolumns

)

SELECT *

FROM numbers

WHERE numbers.num % 10 NOT IN (4, 8) ;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

Luis Cazares (10/20/2016)
That's a weird requirement. What's the story behind this?

Agreed - it's a very weird requirement. There's got to be more to the story and I'm hoping for something that's really interesting. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

Taking what Luis put together, you can hide these rows by adding an isVisable column to your dataset. Then, for the Row Viability setting in SSRS use an expression that makes the row hidden when isVisable = 0.

-- sample data that represents your data

DECLARE @yourTable TABLE (someid int identity, col1 varchar(100));

INSERT @yourTable(col1) SELECT TOP (20) newid() FROM sys.all_columns;

-- adding an isVisable column

WITH yourdata AS

(

SELECT col1, ROW_NUMBER() OVER ( ORDER BY someid ) AS rn

FROM @yourTable

)

SELECT

rn,

col1,

isVisable = CASE WHEN yourdata.rn % 10 NOT IN (4, 8) THEN 1 ELSE 0 END

FROM yourdata;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 5

Luis Cazares (10/20/2016)
That's a weird requirement. What's the story behind this?
Here's a shorter alternative which I expect that might be a bit faster (not much).
WITH numbers AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY id ) AS num
FROM syscolumns
)
SELECT *
FROM numbers
WHERE numbers.num % 10 NOT IN (4, 8) ;

Probably would be quicker - integer mathematics got to be quicker than converting to string and slicing... surely...

...but the bigger question is, why didn't I think of that? 😀

Thomas Rushton
blog: https://thelonedba.wordpress.com