Adding parameters to report model data source view

[bwalkertn]

I'm trying to add a dynamic parameter to an entity in a report model data source view, for example the entity is built on the following

select docid, facilityid
from userfacjoin
where docid = @docid

The query will run when creating but when you try and save it says that the @docid variable has to be declared. Tried to declare the variable but it says the Declare construct is not recognized.

How to you set a dynamic variable in a report model data source view

 

[Catadmin]

bwalkertn,

Not sure what you mean by "report model" data source "view". Are you talking about a data source or a data set?

If you're working with a data set, you need to declare the variable in the data set as you would in a normal SQL statement. Usually, though, I create my dataset as a stored procedure then do "Exec MyProc @MyVariable" as the actual code in the data set. Then I set up a parameter (under Report -> Report Parameters) and create it as Available Values - From Query and choose the data set in question as well as the data set variable in question. I do not use a default in that screen.

Does this help you out?

Catadmin


Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[bwalkertn]

I guess I wasn't to clear - I'm creating a Report Model Project that is used for the Ad Hoc Reporting functionality of Report Services 2005. When you create a Report Model Project you create a data source (.ds) and then create a data source view (.dsv). The datasource view is a diagram of the tables and relationships you want contained within the model.

Unlike creating a report is does not have an area for defining parameters and I can't find how to add a parameter to a column in a table in the data source view.

 

[Catadmin]

Ah. Unfortunately, I haven't graduated to RS 2005 yet, so I don't know the answer to your question

However I just got the book "Pro SQL Server 2005" by Thomas Rizzo (Apress is the publisher) that has a whole chapter on RS (#9). It says:

Once the metadata model is generated using the default rules, you can view it through the management tool. By browsing the auto-generated model, you can see what fields, aggregates and relations the wizard has created.

...

Clicking the Report Builder button launches the Report Builder Client ... You can build complex criteria in your reports through the Report Builder client by dragging and dropping information in the Criteria window.

This is probably all stuff you know, but I posted it in case you didn't. It's not clear to me whether this Report Builder allows you the option of adding parameters.

Can you get into this Data Model using the Report Designer? Does the new Books Online say anything about Report Model parameters?



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[bwalkertn]

You can export the data model to a text file (xml). I'm trying now to add the parameter there and resave the file back to a report model, which you can also do and then upload into the report manager.

Not sure where to go from here but I'll keep you posted. Thanks for the input.