Dynamic Report Name at run time?

Hi Friends,

Is it possible to give a name at run time to a report when we try to download it in any format.

Do we have any control over the report name for e.g. the report name can be passed at parameter value.

Thanks,
Novin

Provide an example of the first five report names.

|||Hi,

I have created on single dynamic report name as "Generic Report" which will serve the need of 5 diff reports,

Where we go one by one report from the drill down from report no1 to 2 to 3 and....5 and all these 5 reports are generated using the single dynamic general report.

Problem is when ever at any report level i try to download the report its name remain the same as "Generic Report".

but I want the name to be parameteries based on the report level.

Hope you understood my probelm now.

Thanks

Novin

Dynamic Report Name at run time?

Hi Friends,

Is it possible to give a name at run time to a report when we try to download it in any format.

Do we have any control over the report name for e.g. the report name can be passed at parameter value.

Thanks,
Novin

Provide an example of the first five report names.

|||Hi,

I have created on single dynamic report name as "Generic Report" which will serve the need of 5 diff reports,

Where we go one by one report from the drill down from report no1 to 2 to 3 and....5 and all these 5 reports are generated using the single dynamic general report.

Problem is when ever at any report level i try to download the report its name remain the same as "Generic Report".

but I want the name to be parameteries based on the report level.

Hope you understood my probelm now.

Thanks

Novin

Dynamic Query

Hi friends,
this my query
declare @.i_errorDb varchar(200)
declare @.i_tableName varchar(200)
Declare @.SQL varchar(4000)
Declare @.ParamList varchar(4000)
select @.i_errorDb = 'test'
select @.i_tableName = 'employee'
select @.SQL = 'if exists (select * from @.xi_errorDb.dbo.sysobjects where id
= object_id([dbo].[@.xi_tableName]) and OBJECTPROPERTY(id, IsUserTable) = 1) '
select @.paramlist = '@.xi_errorDb varchar(200),
@.xi_tableName varchar(200)'
sp_executesql @.sql,@.paramlist,@.i_errorDb,@.i_tableName
I am getting syntax error. Please help me to solve this.
thanks
vanithaShould be something like this:
select @.SQL = 'if exists (select * from ' + @.xi_errorDb +
'.dbo.sysobjects where id
= object_id([dbo].[' + @.xi_tableName + ']) and OBJECTPROPERTY(id,
IsUserTable) = 1)'
sp_executesql @.sql
But suggestable to use the INFORMATION_SCHEMA Views:
select @.SQL = 'if exists (select * from ' + @.xi_errorDb +
'.INFORMATION_SCHEMA.TABLES ' +
'Table_Name like ' + CHAR(39) + @.xi_tableName +
CHAR(39)
sp_executesql @.sql
HTH, Jens Suessmeyer.|||still i am getting the same error
"Jens" wrote:

> Should be something like this:
> select @.SQL = 'if exists (select * from ' + @.xi_errorDb +
> '.dbo.sysobjects where id
> = object_id([dbo].[' + @.xi_tableName + ']) and OBJECTPROPERTY(id,
> IsUserTable) = 1)'
> sp_executesql @.sql
> But suggestable to use the INFORMATION_SCHEMA Views:
>
> select @.SQL = 'if exists (select * from ' + @.xi_errorDb +
> '.INFORMATION_SCHEMA.TABLES ' +
> 'Table_Name like ' + CHAR(39) + @.xi_tableName +
> CHAR(39)
> sp_executesql @.sql
>
> HTH, Jens Suessmeyer.
>|||Could you please post the error ? The best thing would be also to Print
out the produced SQL with Print @.Sql before executing it.
Jens Suessmeyer.|||when the query is printed and executed its executing, but with sp_executesql
its throwing "syntax error near sp_executesql"
declare @.i_errorDb varchar(200)
declare @.i_tableName varchar(200)
Declare @.SQL varchar(4000)
Declare @.ParamList varchar(4000)
select @.i_errorDb = 'test'
select @.i_tableName = 'employee'
select @.SQL = 'if exists (select * from ' + @.i_errorDb +
'.INFORMATION_SCHEMA.TABLES where ' +
'Table_Name like ' + CHAR(39) + @.i_tableName +
CHAR(39) + ') drop table ' + @.i_errorDb + '.dbo.'+@.i_tableName
print @.sql
sp_executesql @.sql
"Jens" wrote:

> Could you please post the error ? The best thing would be also to Print
> out the produced SQL with Print @.Sql before executing it.
> Jens Suessmeyer.
>|||You have to EXEC the stored procedure if you are having just once batch
use habe to split the batch via GO to execute a procedure without the
word EXEC or you place an execute (whch shoudl be the prefered method)
in front of the procedurename, or you use another syntax with just the
EXEC word (sp_executesql required an nvarchar, so you want to use this
further you have to change the @.sql variable to nvarchar)
declare @.i_errorDb varchar(200)
declare @.i_tableName varchar(200)
Declare @.SQL varchar(4000)
Declare @.ParamList varchar(4000)
select @.i_errorDb = 'test'
select @.i_tableName = 'employee'
select @.SQL = 'if exists (select * from ' + @.i_errorDb +
'.INFORMATION_SCHEMA.TABLES where ' +
'Table_Name like ' + CHAR(39) + @.i_tableName +
CHAR(39) + ') drop table ' + @.i_errorDb + '.dbo.'+@.i_tableName
print @.sql
EXEC(@.SQL)
--OR
--EXEC sp_executesql @.sql
HTH, jens Suessmeyer.

Dynamic Query

hi friends,
my sp is used to eliminate duplicate data from the table
dynamic query inside the sp is
select @.sql = 'SELECT * into ' + @.i_errorDb + '.dbo.' + @.i_tableName + '
from '
+ @.i_oldDb + '..'+ @.i_tableName + ' as T where ' + @.i_PrimaryKey + '=
(select min('+@.i_PrimaryKey + ') from ' +
@.i_oldDb + '..'+ @.i_tableName + ' where ' + @.i_PrimaryKey + ' = T.' +
@.i_PrimaryKey + ' having count(*) > 1) order by ' + @.i_PrimaryKey
exec sp_executesql @.sql
it works fine if i have a single pk in the table. but in case of composite
pk, it fails.
pls help me to solve this
thanks
vanithait gives me the solution.
is there any alternative method?
select @.sql = 'delete FROM ' + @.i_oldDb + '..'+ @.i_tableName + ' WHERE ' +
@.i_PrimaryKey1 + '=(SELECT MIN('+
@.i_PrimaryKey1 + ') FROM ' + @.i_oldDb + '..'+ @.i_tableName + ' as T WHERE '
+ @.i_oldDb + '..'+ @.i_tableName + '.' + @.i_PrimaryKey1 + '= T.' +
@.i_PrimaryKey1
if @.i_PrimaryKey2 is not null
begin
select @.sql = @.sql + ' and ' + @.i_oldDb + '..'+ @.i_tableName + '.' +
@.i_PrimaryKey2 + ' = T.' + @.i_PrimaryKey2
end
if @.i_PrimaryKey3 is not null
begin
select @.sql = @.sql + ' and ' + @.i_oldDb + '..'+ @.i_tableName + '.' +
@.i_PrimaryKey3 + ' = T.' + @.i_PrimaryKey3
end
select @.sql = @.sql + ' having count(*) > 1)'
exec sp_executesql @.sql
"vanitha" wrote:

> hi friends,
> my sp is used to eliminate duplicate data from the table
> dynamic query inside the sp is
> select @.sql = 'SELECT * into ' + @.i_errorDb + '.dbo.' + @.i_tableName + '
> from '
> + @.i_oldDb + '..'+ @.i_tableName + ' as T where ' + @.i_PrimaryKey + '=
> (select min('+@.i_PrimaryKey + ') from ' +
> @.i_oldDb + '..'+ @.i_tableName + ' where ' + @.i_PrimaryKey + ' = T.' +
> @.i_PrimaryKey + ' having count(*) > 1) order by ' + @.i_PrimaryKey
> exec sp_executesql @.sql
> it works fine if i have a single pk in the table. but in case of composite
> pk, it fails.
> pls help me to solve this
> thanks
> vanitha

dynamic query

Hello friends,

I want to create a dynamic query based on input of the parameter.

If the user passes nothing then all fields should be displayed else use query based on parameter.

I had view sample of MSDN ,but I got error [BC30203].

Is there another way to it ?Please help.

I use:

= iif(Parameters!SQLQuery.Value<>"",Parameters!SQLQuery.Value,"SELECT somecolumn from sometable") as "select statement"

and provide a query in the SQLQuery-Parameter..

You could also use:

= iif(Parameters!SomeID.Value<>"","SELECT somecolumn from sometable where id=" & Parameters!SQLQuery.Value,"SELECT somecolumn from sometable where id=123")

|||

Thanks For Your Reply

But I m still confusing.

I had used the iif (condition) in the generic query designer but i cannot retrive the fields which I want from the query.

The Query is executing but the data set does not contain any fields.

for eg:

="Select Idnummer,.....

iif(parameter is null,nothing,"AND ART IN ( " & parameter.value & ")")

Please reply sooner.

|||

You can't check your query anymore, thats right. When writing the query as ="select .. " & some_condition .. Hitting the "!"-Button has no effect.. This statement is evaluated at runtime.. So, you have to go to Preview-Mode and check if the result looks right..

If you are missing the Fields!.. for report design, the easiest way is to execute a "normal" sql-statement once (this will add the fields) and then transform your sql-string..

|||

Dynamic query should be avoided wherever possible. It is much harder to do (as you have seen).

I do this, I have a parameter that says All and returns a value of All (it could also return a number if a number field, just make it a value that does not exist in the database.

Do this:

selct * from sometable where (somefield = @.MyParam or @.MyParam = 'All') and ...

|||

I dunno if this is better, but should do the same thing:

The query will be:

select * from TableName
where FieldName LIKE (CASE WHEN @.param IS NULL THEN '%' ELSE @.param END)

|||

Hello Sir,

How can I implement "ALL" in my parameter

The table field does not contain 'ALL" .

If the parameter selected is 'ALL',then query should execute with the parameter contaning 'ALL' the values.Then my problem could be solve if the parameter contains 'ALL'.

please give a sample to implement 'ALL' in my parameter and in query.

Please reply soon.

Thanks

|||

u can use the query in this way,

SELECT AreaName, AreaCode
FROM Area
UNION
SELECT ' All' AS Areaname, '' AS AreaCode
ORDER BY AreaName

This will add 'All' n ur drop down box n using case statement in query u can get the desired results.

regards

Satyendra