2

I would like to create a report that will display a variable amount of images i.e. 2 or 3 or 4 and so on. I have a dataset that returns the path to each of these images,see example.

SELECT Path FROM Photos
returns:
'C:\photos\photo1.jpg
'C:\photos\photo2.jpg
'C:\photos\photo3.jpg
'C:\photos\photo4.jpg

another dataset could return:
'C:\photos\photo7.jpg
'C:\photos\photo8.jpg
'C:\photos\photo9.jpg

What I want the report to look like:

For example 1 I want 3 pictures across the report and then another on on the second line

For example 2 I just want 3 pictures across the report.

I have tried using a table and tablix and can not get it to work. Any Ideas?

DarrynC
  • 67
  • 1
  • 7

2 Answers2

2

Get your paths from the database in three fields. Then put the fields in three columns of a table.

The idea for the query is to get the 1st, 4th, 7th... path in column1, the 2nd, 5th, 8th ... path in column 2, and the 3rd, 6th, 9th... path in column 3.

Number the paths using How to select the nth row in a SQL database table?, and use a modulo 3 function with a self join to create three columns. 

create table #photo
(
    Path varchar(100)
)
go

insert into #photo values ('Path1')
insert into #photo values ('Path2')
insert into #photo values ('Path3')
insert into #photo values ('Path4')
insert into #photo values ('Path5')
insert into #photo values ('Path6')
insert into #photo values ('Path7')
insert into #photo values ('Path8')

go

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY Path)-1 AS RowNumber, Path
FROM #photo)
SELECT Ord1.Path as Col1Path, Ord2.Path as Col2Path, ord3.Path as Col3Path
FROM Ordered Ord1
left outer join
Ordered Ord2
on Ord1.RowNumber = Ord2.RowNumber - 1
left outer join 
Ordered Ord3
on Ord3.RowNumber = Ord2.RowNumber + 1
where Ord1.RowNumber % 3 = 0


drop table #photo

This is the report

Community
  • 1
  • 1
Fillet
  • 1,426
  • 12
  • 26
0

I used this to have 4 rows of varied amounts of images in columns using a Matrix report

SELECT ImageLink, ImageRowNumber, ROW_NUMBER() OVER( PARTITION BY ImageRowNumber ORDER BY ImageLink) ImageColumns

FROM ( SELECT ImageLink, NTILE(4) OVER (PARTITION BY ImageLink ORDER BY ImageLink) AS ImageRowNumber FROM {Table} WITH(NOLOCK) )x

Jubblies
  • 1