Showing posts with label define. Show all posts
Showing posts with label define. Show all posts

Thursday, March 22, 2012

Dynamic Page size

hi,
I am facing a problem of dynimically changing he size of a crystal report.
Let me define it like this : I have designed a report for a A3 page and now they want me design the same report to be printed in A3 and A4 . Is there any way that we can pass aurguments and change the size of data dynimicallyCant you use Printer collection property of Printers?
You need to do this in the Front End Applicationsql

Sunday, March 11, 2012

Dynamic Filter, Inline Table & HOST_NAME() - Does it work or MS BUG?

Hi All,
Can someone give me a definitive answer on the following:
Can I define a UDF that is used by my Dynamic Filter, receives the HOST_NAME() as a
parameter and passes back a table that I can perform a SELECT IN on ? An example is
shown below...
SELECT <published_columns> FROM [dbo].[Stores] WHERE StoreID
IN (SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME()))
This Dynamic Filter gives me an error
Error 170, line 1: Incorrect Syntax near '('.
I've had conflicting views on this, and I'd really like to get it sorted, any
input is welcome.
Cube,
I have only been able to use UDFs before when they are scalar, eg
SELECT <published_columns> FROM [dbo].[Region] WHERE region.regionid = dbo.fn_TaxRate(host_name())
works fine. In your case can you restructure your query to return a scalar value and then use this value combined with another table in a join + where clause perhaps?
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the reply. Not a bad idea. In relation to my question about passing in
HOST_NAME() into my inline-table UDF is it the case that this isn't possible ?
BTW - I'll give you all an update when I've tried the other suggestion...now :o)
Cheers
|||Cube,
I can't say definitively that it's not possible - I have always had the same
error message you've seen, and probably like yourself I've tried every
syntax combination for use of the table-leveled UDF I could think of. I've
never heard of anyone else using a table valued UDF in a dynamic filter, but
hopefully if someone else has they'll post it up.
Regards,
Paul Ibison

Wednesday, March 7, 2012

Dynamic datasource name

Hi
Is there a way we can define the datasource name/connection string for a
report in a config file... and read it from there so that it would make the
job of deploying across various servers easy?Various approaches for dynamic database connections in RS 2000 are
available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server - you should some
threads about it in the archives of this newsgroup.
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
In addition, native support (expression-based connection strings) is
available in RS 2005: Finish the design of the datasets with a constant
connection string and make sure everything works. Then, go back to the data
tab and open the dataset/data source dialog and change the connection string
to be an expression. Use string concatenation to plug in the parameter
value. Here is an example of how the RDL would look for a parameter-based
connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" & Parameters!ServerName.Value
& ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>
You can also check this blog posting:
http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PV" <PV@.discussions.microsoft.com> wrote in message
news:25C803F0-8944-4712-9A72-51F26DED394D@.microsoft.com...
> Hi
> Is there a way we can define the datasource name/connection string for a
> report in a config file... and read it from there so that it would make
> the
> job of deploying across various servers easy?

Dynamic dataset

Hi all,
I would like to create a report in which I can dynamically define the
dataset (i.e: the name of the database). How can I do it?
Regards,
Tamas SzaniszloTamas,
Not sure of your big picture here but try using Data processing extensions.
These leave you in control of what data you want presented to RS
Report Designer will need to be "bound" to a datasource / set of course -
you can "fake" this using an XSD or possibly expose a dataset via a web
service.
regards,
- peteZ
"Tamas Szaniszlo" <t.szaniszlo@.scania.hu> wrote in message
news:ci50nt$huj$1@.namru.matavnet.hu...
> Hi all,
> I would like to create a report in which I can dynamically define the
> dataset (i.e: the name of the database). How can I do it?
> Regards,
> Tamas Szaniszlo
>|||Tamas , you could try http://workspaces.gotdotnet.com/appworld this
is a DPE that allows you to change the connection string on the fly.
In deployed scenario please be aware that if you are using URL access or
exporting to HMTL / XML etc the connection string will be exposed. There are
couple ways to tackle this either encrypt the connection string and decrypt
at the server or in my scenario I use a client id or database id to set the
connection string in the DPE, this works well and hides any connection
string information about the server.
Regards
Toby
"Tamas Szaniszlo" <t.szaniszlo@.scania.hu> wrote in message
news:ci50nt$huj$1@.namru.matavnet.hu...
> Hi all,
> I would like to create a report in which I can dynamically define the
> dataset (i.e: the name of the database). How can I do it?
> Regards,
> Tamas Szaniszlo
>|||Tamas , you could try http://workspaces.gotdotnet.com/appworld this
is a DPE that allows you to change the connection string on the fly.
In deployed scenario please be aware that if you are using URL access or
exporting to HMTL / XML etc the connection string will be exposed. There are
couple ways to tackle this either encrypt the connection string and decrypt
at the server or in my scenario I use a client id or database id to set the
connection string in the DPE, this works well and hides any connection
string information about the server.
Regards
Toby
"Tamas Szaniszlo" <t.szaniszlo@.scania.hu> wrote in message
news:ci50nt$huj$1@.namru.matavnet.hu...
> Hi all,
> I would like to create a report in which I can dynamically define the
> dataset (i.e: the name of the database). How can I do it?
> Regards,
> Tamas Szaniszlo
>

Sunday, February 26, 2012

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
peleg
On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
.....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com
|||thnaks alot
"amish" wrote:

> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>
|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
>
>
>
>
>
> - Show quoted text -
:-)

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
pelegOn Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com|||thnaks alot
"amish" wrote:

> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
:-)

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
pelegOn Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
.....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com|||thnaks alot
"amish" wrote:
> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> > i have this code in which i define a @.TempTableCursor
> > but is there a way that the TargetTable will be
> > TargetTable1,TargetTable2,..TargetTable(i)
> > so when i define set the @.TempTableCursor it will have in the
> > defenition the TargetTable with a dynamic changing number?
> > [code]
> > Declare @.TempTableCursor cursor
> > Set @.TempTableCursor = Cursor Local FAST_FORWARD
> > For Select * From TargetTable
> > [/code]
> >
> > thnaks in advance
> > peleg
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
> > On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> > > i have this code in which i define a @.TempTableCursor
> > > but is there a way that the TargetTable will be
> > > TargetTable1,TargetTable2,..TargetTable(i)
> > > so when i define set the @.TempTableCursor it will have in the
> > > defenition the TargetTable with a dynamic changing number?
> > > [code]
> > > Declare @.TempTableCursor cursor
> > > Set @.TempTableCursor = Cursor Local FAST_FORWARD
> > > For Select * From TargetTable
> > > [/code]
> > > thnaks in advance
> > > peleg
> > declare @.sql nvarchar(4000)
> > declare @.table varchar(100)
> > set @.table = 't'
> > set @.sql = N'
> > set @.cur = cursor for
> > select
> > * from ' + @.table + '; open @.cur'
> > exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> > output
> > if cursor_status('variable', '@.cur') = 1
> > begin
> > .....................
> > .....................
> > end
> > if cursor_status('variable', '@.cur') >= 0
> > close @.cur
> > deallocate @.cur
> > Regards
> > Amish Shah
> >http://shahamish.tripod.com- Hide quoted text -
> - Show quoted text -
:-)