Is it possible to dynamically populate a parameter list with values based on another parameter v

Is it possible to fill a parameter list with values based on another parameter value?

Here's what I have so far (which hasn't worked)...

I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.

I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).

I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.

Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...

The value expression for the query parameter '@.SchoolID' refers to a non-existing report parameter 'SchoolID'.

The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.

...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?

Any help you can give me would be great!! Thank you
-Ethan

I ran into that problem, and it was simply due to the fact that my parameters were in the wrong order. Try this : go into the parameters dialog and make sure the list-of-schools parameter is above the list-of-students parameter.

Hope that helps.|||If you look at the sample reports included with RS, you should see one called 'Product Line Sales'. It demonstrates what you are trying to do.|||

hallo,

i have the same problem, can you give me a example.

thx

Is it possible to dynamically populate a parameter list with values based on another parameter v

Is it possible to fill a parameter list with values based on another parameter value?

Here's what I have so far (which hasn't worked)...

I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.

I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).

I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.

Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...

The value expression for the query parameter '@.SchoolID' refers to a non-existing report parameter 'SchoolID'.

The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.

...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?

Any help you can give me would be great!! Thank you
-Ethan

I ran into that problem, and it was simply due to the fact that my parameters were in the wrong order. Try this : go into the parameters dialog and make sure the list-of-schools parameter is above the list-of-students parameter.

Hope that helps.

|||If you look at the sample reports included with RS, you should see one called 'Product Line Sales'. It demonstrates what you are trying to do.|||

hallo,

i have the same problem, can you give me a example.

thx

Is it possible to dynamically create columns in a table in SSRS

Hi,

I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths of the column in those tables without shrinking the size of the Table...

any help is appreciated..

Regards

Karen

Hi,

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths of the column in those tables without shrinking the size of the Table...

If there's no record in one of your row, you can use =IsNothing(Fields!productname.Value) filter your record. But if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.

Thanks.

|||

Jin,

Thanks for your response.. what do u mean by remove the column from the recordset... cause i am populating the Reports using a stored procedure and sometimes... there may be some data or not..

Can u pls give me code snipet or an example

Regards

Karen

|||

Hi,

if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.

Here's the sample code, suppose you have two fields, Sp and Hd. If there's no data in Hd field of your return set, then only Sp field would been selected.

DECLARE @.NULLCOUNTINTSELECT @.NULLCOUNT =COUNT(*)FROM MatrixCapitalWHERE MatrixCapital.Spisnot nullif @.NULLCOUNT=0BEGIN SELECT MatrixCapital.HdFROM MatrixCapitalEND ELSEBEGIN SELECT MatrixCapital.Hd,MatrixCapital.SpFROM MatrixCapitalEND

Thanks.|||

Jin,

Thanks a lot for your answer so this mean that if i have more 5 - 6 columns NULL... i have check for each column in the NULL count and then prob union for each feild so that my end resultset will be columns that has data in them?

I have another question too.. if i have a table variable like

Declare @.tbl table

(

tblid int indentity(1,1),

Col 1,

Col 2,

..,

Col n

)

Is it possible to have a variable column size depending on the number of entries in my select column like for example.. my if i have 10 columns in my select statement and in that 5 are null... so can just insert 5 rows to the table and then remove the remaining columns out.

Regards

Karen

|||

Hi,

i have check for each column in the NULL count and then prob union for each feild so that my end resultset will be columns that has data in them?

Yes, that's right.

Is it possible to have a variable column size depending on the number of entries in my select column like for example..

Based on my knowledge, another way i can see is to use CASE WHEN clause in your SQL, but it still requires you to give differrent sql statments accoring to the "isNUll" result of a column.

Thanks.

sql

Is it possible to create a view within a stored procedure

Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?

Hi,

A stored procedure cannot be created with the create View command in it .

But we could create a t-sql string with the create View statement and execute the string with sp_executesql

Though, it returns a Warning message("Cannot add rows to sysdepends for the current stored procedure") the View would be created. The warning message occurs because during the First execution of the SP the View will not be created.

SanDoty

|||

As SanDoty indicated, you cannot create a VIEW inside a STORED PROCEDURE.

However, there are 'tricks', such as using dynamic SQL. Because of various issues, such as dependencies, those 'tricks' should not be considered except under certain circumstances.

However inside a Stored Procedure, you can create a table variable, populate it and use it just like you would use a VIEW.

|||

create proc my_proc as

BEGIN

declare @.sql varchar(max)

select @.sql = 'create view my_sysobjects as select top 5 * from sysobjects'

exec (@.sql)

select * from my_sysobjects

END

go

exec my_proc

|||hi,i tried it and it works..but when i try with my coding to execute, it gives an 'Invalid object name 'vw_test' error.in my stored proc thers some query which does some calculations based on date(im passing in date as parameter in the stored proc - i suspect this might be the cause). how do we overcome this?kindly advice.thanx.

Is it possible to create a view within a stored procedure

Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?

Hi,

A stored procedure cannot be created with the create View command in it .

But we could create a t-sql string with the create View statement and execute the string with sp_executesql

Though, it returns a Warning message("Cannot add rows to sysdepends for the current stored procedure") the View would be created. The warning message occurs because during the First execution of the SP the View will not be created.

SanDoty

|||

As SanDoty indicated, you cannot create a VIEW inside a STORED PROCEDURE.

However, there are 'tricks', such as using dynamic SQL. Because of various issues, such as dependencies, those 'tricks' should not be considered except under certain circumstances.

However inside a Stored Procedure, you can create a table variable, populate it and use it just like you would use a VIEW.

|||

create proc my_proc as

BEGIN

declare @.sql varchar(max)

select @.sql = 'create view my_sysobjects as select top 5 * from sysobjects'

exec (@.sql)

select * from my_sysobjects

END

go

exec my_proc

|||hi,i tried it and it works..but when i try with my coding to execute, it gives an 'Invalid object name 'vw_test' error.in my stored proc thers some query which does some calculations based on date(im passing in date as parameter in the stored proc - i suspect this might be the cause). how do we overcome this?kindly advice.thanx.

Is it possible to change the connection dynamically.

Hi guys,
We have 2 database one in US another in UK. I am developing 2 reports one
for US and another for UK..
Is there any way to pass/give the connection dynamically?
Regards,
SriOn Apr 26, 11:22 am, Sriman <Sri...@.discussions.microsoft.com> wrote:
> Hi guys,
> We have 2 database one in US another in UK. I am developing 2 reports one
> for US and another for UK..
> Is there any way to pass/give the connection dynamically?
> Regards,
> Sri
The only options I can think of are:
- Have a report parameter (possibly hidden) passed to the query/stored
procedure that is sourcing the report and based on that parameter,
select the correct database (via opendatasource, etc).
- If an ASP.NET application is an option, dynamically set the desired
datasource as part of a custom RDL building process (dynamically) as
part of the <Datasources><Datasource Name="SomeDatasource"></
Datasource></Datasources> tags.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||So one report connects to US with US database and the other connects to UK
database is it right ? then while creating dont use shared data source
instead create a seperate data source from data tab for each server and each
report.
Sounds simple. Let me know if my understanding is right
Amarnath
"Sriman" wrote:
> Hi guys,
> We have 2 database one in US another in UK. I am developing 2 reports one
> for US and another for UK..
> Is there any way to pass/give the connection dynamically?
> Regards,
> Sri|||wow..both options sounds good to me.
Thanks alot ..
Regards,
Sri
"Amarnath" wrote:
> So one report connects to US with US database and the other connects to UK
> database is it right ? then while creating dont use shared data source
> instead create a seperate data source from data tab for each server and each
> report.
> Sounds simple. Let me know if my understanding is right
> Amarnath
> "Sriman" wrote:
> > Hi guys,
> >
> > We have 2 database one in US another in UK. I am developing 2 reports one
> > for US and another for UK..
> > Is there any way to pass/give the connection dynamically?
> >
> > Regards,
> > Sri|||On Apr 27, 8:06 am, Sriman <Sri...@.discussions.microsoft.com> wrote:
> wow..both options sounds good to me.
> Thanks alot ..
> Regards,
> Sri
> "Amarnath" wrote:
> > So one report connects to US with US database and the other connects to UK
> > database is it right ? then while creating dont use shared data source
> > instead create a seperate data source from data tab for each server and each
> > report.
> > Sounds simple. Let me know if my understanding is right
> > Amarnath
> > "Sriman" wrote:
> > > Hi guys,
> > > We have 2 database one in US another in UK. I am developing 2 reports one
> > > for US and another for UK..
> > > Is there any way to pass/give the connection dynamically?
> > > Regards,
> > > Sri
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant