dynamic report based on a dynamic record set

Question

dynamic report based on a dynamic record set

l.poerio

SSC Veteran

Points: 293
More actions
July 17, 2008 at 7:51 am

#122411

sorry,
maybe I didn't searched enough, but I can't find an answer for this question ...
I'd like to build a dynamic report based on a dynamic record set.
How can I do it?
Just to make me understand, here you find an hypothetical origin of data I'd like to output:
create procedure [dbo].[SP_Dynamic_report]
@param int =1
as
BEGIN
if @param=1
select top 10 * from SYSOBJECTS
else
select top 10 * from syscolumns
end
then the user can chose between param 1 or 2 and get
in the same report different data ...
exec [SP_Dynamic_report] 1
exec [SP_Dynamic_report] 2
Is it possible?
Can you provide me an example? or a link?
Thank you very much for any help!
Lorenzo

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I would handle it by specifying the column list in the procedure and aliasing the column names so I had the same list. If there are a different number of columns being returned I would pass out empty string for the unused columns. Then the objects on the report remain the same.

Another option, which I am not sure would work but might be worth a try, is to create 2 tables (assuming you are using a table) and hide one based on the the parameter. You may still have to have the same column names in your dataset for this to work.

If you come up with a solution please post it so we can learn from your experience as well.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

l.poerio SSC Veteran Points: 293 More actions · Answer 2

thank you for prompt reply.

I'm afraid of your answer! This mean that I have to work hard on t-sql side

or hiding many report and just show the one the user wants.

In 2 word RS is not intelligent enough to create on the fly a

report for a generic select * from [any_table]

showing the correct column name for each field ...

My wish would be just to show a generic table inside RS,

The original idea was to give a web access to our DB to our internal user ...

It's so sad to see that a thing that seem simple require so many work ...

Imagin that my wish was to show all this report

select name as report_title , 'select top 10 * from ' + name as report_sql

from SYSOBJECTS where xtype in ('U', 'V')

thank you anyway

Lorenzo