creating a view in SSRS?

Question

creating a view in SSRS?

Steven.Grzybowski

Hall of Fame

Points: 3214
More actions
May 2, 2016 at 6:40 am

#310325

I am working with XMPIE, but as of right now, the only way to create reports on any given campaign is to generate a view for it using a very complex script. that is designed to take a campaign ID as a parameter. i was wondering if it would be possible to execute a create view statement from SSRS and have some sort of drop-down for the campaign, or if that would be impossible in SSRS. The way I was thinking was to use the campaign table in a parameter with a drop-down set to only use values from the campaign name column, and use that to set the campaign ID parameter, which could be used to generate the required view. If anybody has any better suggestions of how to go about this, I would appreciate any advice that I could get.

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

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

You can execute stored procedures from SSRS. Anything that you can do in a view you could also do in a subquery or temp table inside a stored procedure; a view is just a named query stored in your database. Heck you don't even need a subquery or temp table. You could just have the stored proc return what you need.

The logic to create a view inside a stored procedure would look like this:

CREATE proc x (@id int)

AS

BEGIN

DECLARE @sql varchar(8000) =

'CREATE VIEW xxx AS SELECT x = '+CAST(@id as varchar(10));

EXEC(@sql);

SELECT * FROM xxx;

DROP VIEW xxx;

END

This is, however, wrong for many reasons. You would get the same results by doing this:

CREATE proc x (@id int)

AS

SELECT @id;

(replace my simple SELECT statement with your more complicated query.)

"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

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

There's no need to create a view from SSRS. A view is simply a named query. You should be creating a stored procedure in the server and call the stored procedure instead of creating the view.

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

Steven.Grzybowski Hall of Fame Points: 3214 More actions · Answer 3

That is a good point. I should have thought of that .

Steven.Grzybowski Hall of Fame Points: 3214 More actions · Answer 4

So now I am wondering if anyone could post me in the direction of some good information on usig dynamically generated queries in SSRS. The data can have different column names and different amounts of columns based on what campaign the query is given a parameter. I am not even sure if there is a way to do this .

Thom A SSC Guru Points: 99133 More actions · Answer 5

Steven.Grzybowski (5/3/2016)
So now I am wondering if anyone could post me in the direction of some good information on usig dynamically generated queries in SSRS. The data can have different column names and different amounts of columns based on what campaign the query is given a parameter. I am not even sure if there is a way to do this .

There isn't. SSRS expects a specific number of columns with specific names, which it obtains when you add the dataset. If you add extra columns to the dataset, SSRS will not notice these unless you refresh the dataset. If you remove any columns (this includes renaming, as this would be seen as removing and adding), SSRS will FAIL, as an expected item does not exist.

If you have dynamic columns, my suggestion would be to also return all the columns you need (even if they are full of NULLs), and then only display the columns required on SSRS based on your selection criteria.

You can have a column's visibility dynamically change by right clicking the column and selecting Column Visibility. Select the radio option "Show or hide based on an expression" and then click the f_x button to enter your expression.

So, for example, if you only wanted to display a column if the variable Stock had the value "Television", you would put the following:

=iif(Parameters!Stock.Value = "Television", FALSE, TRUE)

Note that FALSE DISPLAYS the column. The value TRUE equates to "Hide this column".

Hope that helps.

Edit: because I can't spell.

Thom~

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