Report design where number of columns is unknown

Question

Report design where number of columns is unknown

Duncan Lawrence

SSCrazy

Points: 2047
More actions
May 21, 2018 at 9:52 am

#350094

Hi, I have a query based on pivot and dynamic SQL that results in the number of columns returned being unknown.
How can I use this within SSRS to produce a report with an unknown number of columns.
Not only is the number of columns unknown until the query has executed, the columns generated will have unique column headers.
Anyone got any ideas?
All the best
Duncan
All the best,
Duncan

Viewing 10 posts - 1 through 9 (of 9 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

Simply put, don't; SSRS doesn't support dynamic dataset. It does support dynamic SQL, but the dataset returned still has to be predefined.

Instead use a fixed dataset (you might no even need dynamic SQL for that), and use a Matrix instead of a Tablix.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Duncan Lawrence SSCrazy Points: 2047 More actions · Answer 2

Hi Thom, thanks for getting back to me so quickly.
I have tested the idea you suggest however it will not work in this particular situation and I will try to explain why.
The end-game here is to allow the user to export the data as a csv. Each row being a single instance with n number of columns. Exporting data from a matrix will not present the data in this way.
I think I may have to make the data available to the user in another fashion if it is impossible to dynamically create columns in SSRS
Thanks,
Duncan

All the best,

Duncan

David Burrows SSC Guru Points: 65142 More actions · Answer 3

I cannot see why a matrix will not do what you want.
Make the dataset have row number, column number (must be sequential starting at 1) and the value for the cell (if mixed datatypes convert them all to varchar)
The matrix will then output values where present, giving you rows with variable number of columns
Also if you want different headers per row (or group of rows) then add an extra column to identify header/data and sort appropriately.

Far away is close at hand in the images of elsewhere.
Anon.

Thom A SSC Guru Points: 99130 More actions · Answer 4

I agree, I don't know why this wouldn't work using a Matrix; unfortunately we have no data to base off here though. The only thing I can think of is that you've not set up the Matrix correctly for your needs. David gives a pretty good description of what you need to do. based on the "idea" of some data.

If it doesn't help, provide some sample data, pre pivoting/dynamic SQL and then show what you want your dataset to look like; hopefully one of us will then be able to talk you through more thoroughly.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 5

Just went through this at my company, but luckily, PowerBI supports this feature with dynamic pivots.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

I just hit it with a dynamic CROSSTAB (and not PIVOT, which is usually twice as slow) and have SSRS call the stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Duncan Lawrence SSCrazy Points: 2047 More actions · Answer 7

Thanks guys, your responses have given me food-for-thought.
One other idea I had been playing about with in my head is to stuff each complete row into a single column, separating each column value with commas. This would then provide a fixed data-set.
Once again thank you for your help.
All the best,
Duncan

All the best,

Duncan

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Duncan Lawrence - Tuesday, May 22, 2018 9:57 AM
Thanks guys, your responses have given me food-for-thought.
One other idea I had been playing about with in my head is to stuff each complete row into a single column, separating each column value with commas. This would then provide a fixed data-set.
Once again thank you for your help.
All the best,
Duncan

That'll work if you want it to be slower. 😉 Concatenation costs.

Have a look at the following article.
http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Duncan Lawrence SSCrazy Points: 2047 More actions · Answer 9

Hi Jeff, fortunately I am only processing a few thousand rows. Big data it ain't.
Thanks

All the best,

Duncan