Dynamincally setting the minimum y-axis scale on charts

Is there anyway to dynamically set the minimum, or maximum for that matter,
y-axis. I'd like to do something like 'iif ( Fields!ReportType =1, 50, 80)
to set the minimum y-axis.
Thanks in advance for any and all help!You can only set it to a particular value but not to an
expression.
>--Original Message--
>Is there anyway to dynamically set the minimum, or maximum
for that matter,
>y-axis. I'd like to do something like 'iif (
Fields!ReportType =1, 50, 80)
>to set the minimum y-axis.
> Thanks in advance for any and all help!
>
>.
>|||Ravi is correct. RS 2000 does not allow you to use expression-based min/max
settings at this point. Expression-based min/max/etc. is on the list for the
next version.
Would auto-scaling work for you (i.e. remove all the contents in the Minimum
field and leave it blank), so the minimum is automatically determined based
on the actual data point values?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ravi" <ravikantkv@.rediffmail.com> wrote in message
news:013b01c491e3$71076410$a401280a@.phx.gbl...
> You can only set it to a particular value but not to an
> expression.
> >--Original Message--
> >Is there anyway to dynamically set the minimum, or maximum
> for that matter,
> >y-axis. I'd like to do something like 'iif (
> Fields!ReportType =1, 50, 80)
> >to set the minimum y-axis.
> > Thanks in advance for any and all help!
> >
> >
> >.
> >sql

Dynamically Writing & Rendering Report

I have a report which will change almost everytime it is requested. I am
building the query and writing the RDL on the fly. I don't want to save the
RDL to the report server as it will be of no future use. Is there a way to
pass the RDL as perhaps a string or something similar and ask the
ReportServer to render that instead of a saved report.
Many thanks in advance for any help.
SimonThis functionality is not supported in the current release but is on wish
list for a future release. For now, you'll need to publish the report in
order to render it, i.e., call CreateReport() and then call Render(). You
can always delete it once you're done.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote in message
news:%23VX8qckbEHA.2544@.TK2MSFTNGP10.phx.gbl...
> I have a report which will change almost everytime it is requested. I am
> building the query and writing the RDL on the fly. I don't want to save
the
> RDL to the report server as it will be of no future use. Is there a way to
> pass the RDL as perhaps a string or something similar and ask the
> ReportServer to render that instead of a saved report.
> Many thanks in advance for any help.
> Simon
>|||Thanks Ravi,
Thats a bit of pain as I could have 100's if not 1000's of stray reports on
the server that would only ever be used once. I now need to write a cleanup
routine to peridoically remove the unused reports from the server. A majorly
inefficient process but looks like the only option.
Do you have any idea when the next iteration of SQL Reporting is planned for
that will include this functionality?
Simon
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote
> This functionality is not supported in the current release but is on wish
> list for a future release. For now, you'll need to publish the report in
> order to render it, i.e., call CreateReport() and then call Render(). You
> can always delete it once you're done.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services|||The next release of reporting services is in SQL Server 2005. This feature
is on wish list for that release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote in message
news:ee0IXHxbEHA.796@.TK2MSFTNGP09.phx.gbl...
> Thanks Ravi,
> Thats a bit of pain as I could have 100's if not 1000's of stray reports
on
> the server that would only ever be used once. I now need to write a
cleanup
> routine to peridoically remove the unused reports from the server. A
majorly
> inefficient process but looks like the only option.
> Do you have any idea when the next iteration of SQL Reporting is planned
for
> that will include this functionality?
> Simon
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote
> > This functionality is not supported in the current release but is on
wish
> > list for a future release. For now, you'll need to publish the report in
> > order to render it, i.e., call CreateReport() and then call Render().
You
> > can always delete it once you're done.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
>

Dynamically Writing & Rendering Report

Is there a way to Dynamically Writing & Rendering Report for SQL
Server Reporting Services? If so an example would be good.Until there is a render control that does not require the server this
difficult to do. When you publish the report it is there for everyone, so
they can easily step on each other. MS has a document that totally specifies
the xml syntax for RDL. Also, you can open up the report.rdl into an editor
to see what it looks like.
Bruce L-C
"Bila" <bakpan@.teckit.com> wrote in message
news:d26cf8a6.0407291113.6103048@.posting.google.com...
> Is there a way to Dynamically Writing & Rendering Report for SQL
> Server Reporting Services? If so an example would be good.|||A method may be do infer an XSD document from the document MSFT provides and
this will create an object model which you can access just like any other
object model. You can then create your report / content using the object
model, serialize back into XML, and then deploy the report to a server and
invoke the render command.
I'm pretty sure you can pull the RDL specification into a XSD document
(which will create a .vb or .c# file for you), but I haven't tried this
befoe.
-Joel
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OqsarIadEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Until there is a render control that does not require the server this
> difficult to do. When you publish the report it is there for everyone, so
> they can easily step on each other. MS has a document that totally
specifies
> the xml syntax for RDL. Also, you can open up the report.rdl into an
editor
> to see what it looks like.
> Bruce L-C
> "Bila" <bakpan@.teckit.com> wrote in message
> news:d26cf8a6.0407291113.6103048@.posting.google.com...
> > Is there a way to Dynamically Writing & Rendering Report for SQL
> > Server Reporting Services? If so an example would be good.
>|||Still, when you deploy that report is there for everyone. I guess you could
deploy with a unique name just for that particular user but still, not easy,
not fast.
Bruce L-C
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:u15I6lcdEHA.720@.TK2MSFTNGP11.phx.gbl...
> A method may be do infer an XSD document from the document MSFT provides
and
> this will create an object model which you can access just like any other
> object model. You can then create your report / content using the object
> model, serialize back into XML, and then deploy the report to a server and
> invoke the render command.
> I'm pretty sure you can pull the RDL specification into a XSD document
> (which will create a .vb or .c# file for you), but I haven't tried this
> befoe.
> -Joel
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OqsarIadEHA.3420@.TK2MSFTNGP12.phx.gbl...
> > Until there is a render control that does not require the server this
> > difficult to do. When you publish the report it is there for everyone,
so
> > they can easily step on each other. MS has a document that totally
> specifies
> > the xml syntax for RDL. Also, you can open up the report.rdl into an
> editor
> > to see what it looks like.
> >
> > Bruce L-C
> >
> > "Bila" <bakpan@.teckit.com> wrote in message
> > news:d26cf8a6.0407291113.6103048@.posting.google.com...
> > > Is there a way to Dynamically Writing & Rendering Report for SQL
> > > Server Reporting Services? If so an example would be good.
> >
> >
>

Dynamically using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie

sql

Dynamically using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie

Dynamically use variables in SQL in EXECUTE

Hi,
What I want to do is:
DECLARE @.sqlName varchar(255)
DECLARE @.temp NVARCHAR(100)
SET @.sqlName =(select name from master.dbo.sysdatabases where name like
'Job_%')
SET @.temp = 'USE ' + RTRIM(@.sqlName)
PRINT @.sqlName
EXEC (@.temp)
GO
--rest of my SQL code
--
Now basically I am going to have this script to run on multiple
databases where the database could be something different.
ex.
Computer1 - DB: Job_1234
Computer2 - DB: Job_5678
Before I run my code I want to make sure it runs under the correct
database. It finds the right database using select name from
master.dbo.sysdatabases where name like 'Job_%'
but how do I execute the USE @.temp statement. It says it executes, but
it still displays the master database in Query Analyzer. Any ideas on
how to do this? I just basically need to get this dynamic USE
statement to work. Thanks in advance.stuart.k...@.gmail.com wrote:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
Your code should work but the USE is scoped to the EXEC statement. Once
the EXEC is done you are returned to where you started. You need to put
some other code into the EXEC string as well if you want it to execute
in the context of another database.
EXEC is a pretty useless tool for this kind of thing. It's much easier
to parameterize the database in a connection string or at the OSQL
command prompt.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks a lot.
This worked if I ran something like
EXEC (@.temp + ' ' + @.code)
where @.code is the rest of my code that I wanted to run. I would use
OSQL if I could but unfortunately I can't.
Thanks again for your quick response.
-Stu
David Portas wrote:
> stuart.k...@.gmail.com wrote:
> Your code should work but the USE is scoped to the EXEC statement. Once
> the EXEC is done you are returned to where you started. You need to put
> some other code into the EXEC string as well if you want it to execute
> in the context of another database.
> EXEC is a pretty useless tool for this kind of thing. It's much easier
> to parameterize the database in a connection string or at the OSQL
> command prompt.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||You want to change context switching.
You can search sp_executeresultset on SQL Server 2000 SP3 later.
Not S2K5.
You can use below sample query.
DECLARE @.PROC NVARCHAR(4000)
SET @.PROC ='job_1234' + '.DBO.SP_EXECRESULTSET'
EXEC @.PROC @.SQLSTMT
"stuart.karp@.gmail.com"?? ??? ??:

> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
>

Dynamically switching report data connection at run time

We have been presented with the problem of using Reporting Services to run
the same report against one of two databases.
We are currently using SQL Server 2000, and while 2005 has support for
dynamically building connection strings with parameters, 2000 apparently does
not.
Thus far, we have found the following solutions:
- A custom data processing extension, which wraps up a SqlConnection, and
switches database context at run-time based on an expected query parameter
- Reporting against a front-end query on the database, which in turn calls
the query from the desired database
- Installing two versions of the same report on the report server, and
having the application choose which to execute at run-time.
Each of these options has various drawbacks, the first brings with it a mess
of support and deployment issues, the second leans on the database harder
than it needs to, and the third is basically redundant.
Although the solution we need now is to switch between one of two databases,
the ideal solution would be able to manage 1-n databases.
We would appreciate your input as to which solution is the best, or if there
is functionality which would better suit our needs that we havenâ't discovered
yet.On Nov 16, 4:07 pm, breedReed <br...@.community.nospam> wrote:
> We have been presented with the problem of using Reporting Services to run
> the same report against one of two databases.
> We are currently using SQL Server 2000, and while 2005 has support for
> dynamically building connection strings with parameters, 2000 apparently does
> not.
> Thus far, we have found the following solutions:
> - A custom data processing extension, which wraps up a SqlConnection, and
> switches database context at run-time based on an expected query parameter
> - Reporting against a front-end query on the database, which in turn calls
> the query from the desired database
> - Installing two versions of the same report on the report server, and
> having the application choose which to execute at run-time.
> Each of these options has various drawbacks, the first brings with it a mess
> of support and deployment issues, the second leans on the database harder
> than it needs to, and the third is basically redundant.
> Although the solution we need now is to switch between one of two databases,
> the ideal solution would be able to manage 1-n databases.
> We would appreciate your input as to which solution is the best, or if there
> is functionality which would better suit our needs that we haven't discovered
> yet.
I would personally create a SQL Server instance that has Linked
Servers to your two other databases, then in reporting services pass
the SELECT * FROM OPENQUERY( @.ServerName, 'SELECT real SQL here' )
-- Scott

dynamically switching databases in a script

I've got a situation where I need to execute portions of a script against every database on a given instance. I don't know the name of all the databases beforehand so I need to scroll through them all and call the "use" command appropriately.

I need the correct syntax, the following won't work:

DECLARE DBS CURSOR FOR
SELECT dbname
FROM #helpdb
ORDER BY dbname

OPEN DBS

FETCH NEXT
FROM DBS
INTO
@.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN

USE @.dbname

The last line - the "USE" statement - is invalid. The following for example works:

USE master

But when supplied a declared variable a syntax error results for the use command because it expects an identifier.

So .. what is the correct syntax to pass a declared parameter to "USE", or is there another way to meet this requirement?

Thanks for your time.

This is not possible right now since you cannot use variables in lot of statements in place of options or identifiers. You can use dynamic SQL though and below is the easiest way to do it:

declare @.sp nvarchar(500)

...

while .....

begin

-- use dbo.sp_executesql for SQL Server 2000

set @.sp = quotename(@.dbname) + N'sys.sp_executesql'

exec @.sp N'your sql string that needs to execute against db'

...

end

|||although this allowed the "use .." statement to run, it didn't have the effect I need. The remainder of the script was still running in the context of the original database.|||

So here's what I really need:

I need a way to switch the context of a script from one database to another, where I do not know the name of the databases beforehand (so they can't be hard-coded).

|||What I suggested will work provided the code that you want to run within context of the database is executed dynamically. Another approach is to pre-process the script file based on the database name and then run it. With SQL Server 2005, you can do this using SQLCMD pre-processing features.

Dynamically supress drill-down enabled groups ?

Hi,
We are trying to find if it is possible to customize grouping with
drill-down in a way as explained below. Any help is appreciated. Thanks.
The structure of the data is such that a group can contain subgroups which
in turn contain detail rows. However, in some cases
a group does not have subgroups (actually has one subgroup which represents
group itself). In our table-based reports this is defined as :
Col 1 Col2 Col3 ...
---
Group | | | (group by
Fields!group)
---
Subgroup | | | (group by
Fields!subgroup)
---
Detail | | |
---
Oupt of this report is as follows:
Figure 1 :
--
+ Group 1
+ SubGroup 1
Detail 1
Detail 2
Detail 3
+ Subgroup 2
Detail 1
Detail 2
+ Group 2
+ SubGroup 1
Detail 1
+ Subgroup 2
Detail 1
Detail 2
...
Both group and subgroup feature toggle item that hides or shows their
children rows. However, as explained above sometimes a group may not have
subgroups. In that case query returns one subgroup which has the same name as
the main group.
In example below Group1 only has one subgroup. That subgroup represents
actually the main group itself. Thus it is redundant.
Figure 2:
--
+ Group 1
+ Group 1
Detail 1
Detail 2
Detail 3
+ Group 2
+ SubGroup 1
Detail 1
Detail 2
Detail 3
+ Subgroup 2
Detail 1
Detail 2
In the case as shown above we would like to have something like:
Figure 3
--
+ Group 1
Detail 1
Detail 2
Detail 3
+ Group 2
+ SubGroup 1
Detail 1
Detail 2
Detail 3
+ Subgroup 2
Detail 1
Detail 2
Note that subgroup of Group1 has been supressed in the example above. The
details that were in subgroup of Group 1 were moved up the hierarchy and now
are shown as part of the main group. We can find out if the subgroups need
to be supressed by using CountDistinct for example. However we do not know of
way to supress the subgroups like in example above once we have the
information that it should be supressed. Again, please note that we would
like to use drilldown feature and toggle-item functionality with this.The current feature set of the matrix region doesn't include the
functionality you are asking for. Instead, I will try to massage the data at
the data store to give me the hierarchy I need.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Vedad" <Vedad@.discussions.microsoft.com> wrote in message
news:8E2B91AE-854E-40C0-9964-41127BBF08A4@.microsoft.com...
> Hi,
> We are trying to find if it is possible to customize grouping with
> drill-down in a way as explained below. Any help is appreciated. Thanks.
> The structure of the data is such that a group can contain subgroups which
> in turn contain detail rows. However, in some cases
> a group does not have subgroups (actually has one subgroup which
represents
> group itself). In our table-based reports this is defined as :
> Col 1 Col2 Col3 ...
> ---
> Group | | | (group by
> Fields!group)
> ---
> Subgroup | | | (group by
> Fields!subgroup)
> ---
> Detail | | |
> ---
>
> Oupt of this report is as follows:
>
> Figure 1 :
> --
> + Group 1
> + SubGroup 1
> Detail 1
> Detail 2
> Detail 3
> + Subgroup 2
> Detail 1
> Detail 2
> + Group 2
> + SubGroup 1
> Detail 1
>
> + Subgroup 2
> Detail 1
> Detail 2
> ...
> Both group and subgroup feature toggle item that hides or shows their
> children rows. However, as explained above sometimes a group may not have
> subgroups. In that case query returns one subgroup which has the same name
as
> the main group.
> In example below Group1 only has one subgroup. That subgroup represents
> actually the main group itself. Thus it is redundant.
>
> Figure 2:
> --
> + Group 1
> + Group 1
> Detail 1
> Detail 2
> Detail 3
> + Group 2
> + SubGroup 1
> Detail 1
> Detail 2
> Detail 3
> + Subgroup 2
> Detail 1
> Detail 2
> In the case as shown above we would like to have something like:
> Figure 3
> --
> + Group 1
> Detail 1
> Detail 2
> Detail 3
> + Group 2
> + SubGroup 1
> Detail 1
> Detail 2
> Detail 3
> + Subgroup 2
> Detail 1
> Detail 2
> Note that subgroup of Group1 has been supressed in the example above. The
> details that were in subgroup of Group 1 were moved up the hierarchy and
now
> are shown as part of the main group. We can find out if the subgroups
need
> to be supressed by using CountDistinct for example. However we do not know
of
> way to supress the subgroups like in example above once we have the
> information that it should be supressed. Again, please note that we would
> like to use drilldown feature and toggle-item functionality with this.sql

Dynamically substitute table name in Cursor Defn.

hi,
I am looking for a soln,where in I can substiute the name of the table
dynamically with a paramter passed through the procedure in the Cursor
Defn
as shown below.

PROCEDURE FLAT_TCN
(p_table_name IN varchar2(50),
p_error_msg IN OUT varchar2)

IS

error_msg VARCHAR2(300) := SQLERRM;

TCN_record VARCHAR2(1000);

CURSOR TCN_cur IS

SELECT
py.SRV_CAT,
py.NET_FT,
py.JUR_CODE,
py.LATA_CODE,
py.CALL_TYPE,
py.CUST_SEG,
py.HIST_CTR,
py.EFF_DATE,
py.EXP_DATE,
py.TRAN_IND,
FROM
p_table_name py;

here note the p_table_name used for cursor declaration(is passed as a
parameter to the proc)...
Any soln or any other way of implemention this is appreciated.

Rgds
VivianYou need to use dynamic SQL:

PROCEDURE FLAT_TCN
(p_table_name IN varchar2(50),
p_error_msg IN OUT varchar2)

IS

error_msg VARCHAR2(300) := SQLERRM;

TCN_record VARCHAR2(1000);

TYPE refcur IS REF CURSOR;
tcn_cur refcur;

BEGIN

OPEN tcn_cur FOR
'SELECT
py.SRV_CAT,
py.NET_FT,
py.JUR_CODE,
py.LATA_CODE,
py.CALL_TYPE,
py.CUST_SEG,
py.HIST_CTR,
py.EFF_DATE,
py.EXP_DATE,
py.TRAN_IND,
FROM '||p_table_name||' py';
...

By the way, I'm not sure this does what you intend:

error_msg VARCHAR2(300) := SQLERRM;

The value of SQLERRM will be assigned at the time the variable is declared - but there hasn't been any error yet, so the value will always be "ORA-0000: normal, successful completion". Or maybe that's the default value you want if no error occurs?|||Hi,
Thks for ur reply.

I want this cursor fo looping record by record.How do I do that. eg

CREATE OR REPLACE procedure test (tab_id in integer)

TYPE refcur IS REF CURSOR;
c_Data refcur;


begin

select table_name into stg_table from table_source where table_type='ST' and table_id=tab_id and src_id=2;

open c_Data for 'Select * from '||stg_table||'order by record_id;';
loop
fetch c__data into rec_id ;
EXIT WHEN c_gib_data%NOTFOUND;
----
---- /* Some actions
end Loop;

When I complie,I get a error at the cursor where the sql statment is created

BEGIN test_dym(1); END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "USR.TEST", line 46
ORA-06512: at line 1|||1) You need a space before 'order'
2) You should not put a semi-colon in the SQL string

open c_Data for 'Select * from '||stg_table||' order by record_id';|||Thnks Andrew.I t worked.|||Hi,
Suppose if I have to do the follwoing,how do I implment using REF cursor.Actually I need to pass a IN paramter to the cursor. Can this be implemeted in REF cursor

CURSOR c_Data( P_Serial_Number IN VARCHAR2 ) IS
SELECT
record_id,record_history_status ,updation_ico_count
FROM
prod_data
WHERE
record_id = ( SELECT max(record_id)
FROM
prod_data
WHERE
Serial_number LIKE P_Serial_Number);|||You don't pass a parameter to the ref cursor, instead you use a BIND VARIABLE and the USING clause. Here is a simple example:

DECLARE
TYPE refcur IS REF CURSOR;
c refcur;
r dept%ROWTYPE;
BEGIN
OPEN c FOR 'SELECT * FROM dept WHERE deptno > :mindeptno' USING 10;
LOOP
FETCH c INTO r;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(r.deptno);
END LOOP;
CLOSE c;
END;
/

In this example, the value 10 is used for the bind variable :mindeptno in the query. In your example this would be used like this:

OPEN c_data FOR 'SELECT ... WHERE Serial_number LIKE :x' USING P_Serial_Number;|||Hi,
I have defined the cursor as ref cursor as follows

TYPE c_Data_refcur IS REF CURSOR; --Cursor with dynamic table defn
c_Data c_data_refcur;

TYPE c_View_Data_refcur IS REF CURSOR; --Cursor with Dynamic
table defn and IN Para
c_View_Data c_View_Data_refcur;

Begin

--The value for stg_table is there with me --
open c_Data for'Select * from '||stg_table||' order by record_id';
loop
fetch c_Data into rec_id ; (Q1 : rec id should be deifned as what %rowtype as the table name is generated dynamically)

EXIT WHEN c_data%NOTFOUND;

IF v_transformation_boolean = FALSE
THEN
SELECT Transfer_ID_Seq.NEXTVAL INTO v_TransferSeq FROM dual;
v_start_record_id := stg_table.record_id; (Q2: How do I specify the fetch cursor value here?)
v_transformation_boolean := TRUE;
END IF;

v_end_record_id := stg_table.record_id; (Q3 How do I specify the fetch cursor value here?)

Q4 : Here I have to inialize the field values as null for the second cursor which has a In paramter.Here again the table name is dynamically populated for the cursor defn.)
c_GibView_Data.updation_ico_count := NULL;
c_GibView_Data.record_history_status := NULL;
c_GibView_Data.ico_status:= NULL;

--2 cursor having IN Parameter--
OPEN c_View_Data for 'SELECT record_id, record_history_status ,updation_count, status FROM'||prd_table||'WHERE record_id = ( SELECT max(record_id) FROM '||prd_table||'WHERE Serial_number = :P_Serial_Number)' using c_Data.serial_number;(This defn is not allowed)
FETCH c_View_Data INTO {c_GibView_Data_Rec};(Q5: how do i defin this as %row type as the table is dynamically populated)
CLOSE c_View_Data;|||Q1) You cannot declare a variable dynamically using NDS, you would need to know the structure of the record in advance. You need to use DBMS_SQL to handle this situation.
Q2) If you had a record declared like v_rec mytable%ROWTYPE into which you had fetched a row, then you would say v_rec.record_id
Q3) Same as Q2
Q4) Don't understand the question.

Dynamically specify server and database in Stored Procedure

I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this?
EXAMPLE:
SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable

OBJECTIVE:

Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')?)

Thanks for any help

Hi,

that is not (yet) parameterizable. You would have to use dynamic sql here.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

dynamically sort columns?

Hi,
Can you set up a report so that you can dynamically sort columns.
ie.
When viewing a report you click on a header of a column and sort by
this column.
If so can you point me to an example
Thanks in AdvanceHi Paul,
You can rightclick on the columnheaders textbox to display the
properties of this, select the tab "Interactive Sort", and select "Add
an interactive sort action to this textbox".
Below you can specify how you want the data to be sorted, ie.
selecting a field from your dataset.
Regards,
Rune Brahe Bjerregaard
On 30 Jul., 06:03, paulhux...@.hotmail.com wrote:
> Hi,
> Can you set up a report so that you can dynamically sort columns.
> ie.
> When viewing a report you click on a header of a column and sort by
> this column.
> If so can you point me to an example
> Thanks in Advance|||On Jul 30, 12:03 am, paulhux...@.hotmail.com wrote:
> Hi,
> Can you set up a report so that you can dynamically sort columns.
> ie.
> When viewing a report you click on a header of a column and sort by
> this column.
> If so can you point me to an example
> Thanks in Advance
Right Click on the title of the column which you wish to use an
Interactive sort with. On the drop down menu select properties. When
the properties widow pops up go to the tab labeled "Interactive
Sort". Click the check box at the top and under the drop down
selection box pick the column that you are trying to sort by. For
example, you selected the column with the datafield "Order Number" so
select that in your interactive sort drop down... otherwise it will
sort according to another column when you click on it. Preview the
report to make sure it works.|||Thanks

dynamically showing subreport

Does anyone know of a way to dynamically show / hide a subreport based on the
record count. I need to be able to hide the subreport if the dataset used for
the subreport doesn't contain any records.
Also, I'd like to dynamically change the order of subreports depending on
data chosen for the main report. For example, depending on a value the user
choses for the main report, I want to set the order that the subreports
appear. Does anyone know if / how to dynamically arrange the subreports
within one report?
Any help is greatly appreciated,
PaulPaul Delcogliano wrote:
> Does anyone know of a way to dynamically show / hide a subreport
> based on the record count. I need to be able to hide the subreport if
> the dataset used for the subreport doesn't contain any records.
Maybe you could write a piece of custom code wich counts the records of the
table by the rowcount-property and the bind the visibility of the subreport
to an expression wich will filled by the custom code.
regards
frank

Dynamically Showing Information

Is there a way of dynamically showing information from a sql server database in a web page without doing a postback.
My goal is to display information from a sql server database everytime the underlying table has received a change. I will also need to have the ability to format the cells based on the information being populated.
If anyone knows of any technology on this topic, or has attempted to do something similar please respond. Even if it's not quite what I'm looking for, the advice given may spin me in the right direction.
It is my belief that this may require creating this web page through a sql server wizard that offers the ability to watch the table for underlying changes. However I am not so sure about how I would then format the information displayed. Has anyone used
this technology before?
Thanks,
Pink
SQL Server has a web assistant. Start with the BOL topic "Using the Web
Assistant Wizard". Or you could write a trigger to push data modifications
to the web page.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamically setting maximum server date dimension

Hello everyone. I have what should hopefully be a simple problem. I have a fairly complicated cube created in SSAS 2005. One of my dimensions is a Server Time dimension. I have this linked to my fact table with a Regular relationship. Now, I would ideally like to be able to set the CalendarEndDate property in the Source property group of my dimension dynamically, based on the most recent date in the fact table. I will never need to see information for a future date using this dimension. Any ideas would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Why would you want to do that? Which goals of yours would it allow to reach?|||

I have a cube which is being used by analysts who are using Excel pivottables as a frontend. When sorting by time, they do not wish to see any options which will return no data. New data is only added about once a month, so reprocessing the cube at that time is not an issue. Does this make sense? Is this the appropriate way of handling this situation?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Usually the tools are capable to show only cells with data. For instance, Browser page in Visual Studio does not show the cells with no data by default. You can control it with "Show Empty Cells" button on the toolbar. I think Excel has the same functionality.

Suppose you have 2 measure groups. For one measure group the fact data gives you "last known date1", for another - "last known date2". What would you do?

The time span for the time dimension should cover all the facts with which the dimension will be used. Otherwise the processing of the cube will fail.|||

you can disable cells in excel where you do't have any data. in the Pivot table you have a button called "Always display items"

-- Good Luck

|||

Here is another way to skin the cat.

I have mutiple measure groups using the time dimension. The way I find the min and max dates is store them in a single record table DAY_DIM_RANGE using a stored procedure. This goes through the multiple fact tables and retains the min and max dates and writes them to the single row table.

The date dimension is then built using a view with a where clause such as

WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

When you add additional measure groups, just adjust the stored procedure to handle the new fact table min and max dates. The view does not change. The stored procedure needs to run just before the cube is processed or at end of ETL.

Dynamically setting maximum server date dimension

Hello everyone. I have what should hopefully be a simple problem. I have a fairly complicated cube created in SSAS 2005. One of my dimensions is a Server Time dimension. I have this linked to my fact table with a Regular relationship. Now, I would ideally like to be able to set the CalendarEndDate property in the Source property group of my dimension dynamically, based on the most recent date in the fact table. I will never need to see information for a future date using this dimension. Any ideas would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Why would you want to do that? Which goals of yours would it allow to reach?|||

I have a cube which is being used by analysts who are using Excel pivottables as a frontend. When sorting by time, they do not wish to see any options which will return no data. New data is only added about once a month, so reprocessing the cube at that time is not an issue. Does this make sense? Is this the appropriate way of handling this situation?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Usually the tools are capable to show only cells with data. For instance, Browser page in Visual Studio does not show the cells with no data by default. You can control it with "Show Empty Cells" button on the toolbar. I think Excel has the same functionality.

Suppose you have 2 measure groups. For one measure group the fact data gives you "last known date1", for another - "last known date2". What would you do?

The time span for the time dimension should cover all the facts with which the dimension will be used. Otherwise the processing of the cube will fail.|||

you can disable cells in excel where you do't have any data. in the Pivot table you have a button called "Always display items"

-- Good Luck

|||

Here is another way to skin the cat.

I have mutiple measure groups using the time dimension. The way I find the min and max dates is store them in a single record table DAY_DIM_RANGE using a stored procedure. This goes through the multiple fact tables and retains the min and max dates and writes them to the single row table.

The date dimension is then built using a view with a where clause such as

WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

When you add additional measure groups, just adjust the stored procedure to handle the new fact table min and max dates. The view does not change. The stored procedure needs to run just before the cube is processed or at end of ETL.

Dynamically set logging levels?

Hello All,

I suspect I know the answer but I'll ask away. We currently have our SSIS packages set up to log to SQL Server. Currently they log OnError, OnInformation and OnTaskFailed. If I'd like to have it log OnPipeLineRowsSent, is there anyway I can get that done without opening up the package and editing it? I know the change is trivial from the IDE but the deployment process at my current engagement is quite lengthy. If something breaks in production, I'd like to know if it'll be possible to turn up the chattiness of logging without going through a full deploy scenario.

I was looking at the parameters for dtexec/dtexecui and I see that you can configure where something logs but nothing about the verbosity of the logs generated. Is it something I'm missing with that or is that all you can set there?

The only other option that jumps out at me is to develop a custom script or component that sets the logging level based on a parameter. Anyone have a thought as to how much effort that would besomething easily tackled or probably more trouble that it's worth?

Thanks for the help

I don't think you can send parameters to the logging provider because logging occurs before pretty much any thing else. See this post for more details:
http://weblogs.sqlteam.com/dmauri/archive/2006/04/02/9489.aspx

Dynamically set different font weight for each text in the textbox

Hi friends,

I have a text box with n number of text.

I want to set the font weight of each text in the textbox dynamically..

For eg.. suppose the text of the textbox is "Hello Friends", then i need "Hello Friends" as output.

Is there any way to accomplish this in SQL Reporting Service.

Any help will be appreciated. Its critical.

Please help me out ASAP.

No, the font settings are per textbox, so everything in the textbox will have the same font weight. You can use different textboxes, but that ends up pretty messy.

|||

Yes it is possible. However, it is not easy and you will need Visual Studio 2005 to do it.

It sounds like you are basically wanting to change the layout of the report at runtime.

Here is how to do it:

http://msdn2.microsoft.com/en-us/library/ms170667.aspx

After you are able to change the layout of the report at runtime, you will want to deploy the report from your application using SetReportDefinition.

|||

Greg, are you thinking that he would create a separate textbox for each wordin the original textbox to handle this requirement, so that each one would have its own formatting? How would that work, for different instances of the same textbox in the report (for example, the first one reads "this is my text" and "is" should be bold, but the second instance reads "this is not really my text" and the word "my" should be bold?

Also I think the positioning/kerning would be a nightmare...

>L<

|||

The only way I know how to do this successfully in Reporting Services (although Greg may have another approach, I'm not seeing it!), is to do some custom rendering. IOW, if you render the information yourself, you are free to set the formatting for each word in the text. The result becomes a small graphical "piece" in the report, though, not really text. So it wouldn't be searchable text.

If this solution appeals to you at all, I can give you more details.

>L<

|||

Lisa Nicholls wrote:

The only way I know how to do this successfully in Reporting Services (although Greg may have another approach, I'm not seeing it!), is to do some custom rendering. IOW, if you render the information yourself, you are free to set the formatting for each word in the text. The result becomes a small graphical "piece" in the report, though, not really text. So it wouldn't be searchable text.

If this solution appeals to you at all, I can give you more details.

>L<

This sounds like a better approach. I suppose I was thinking that you could change the font weight per word.

|||

Thanks Lisa and Greg for the answers .. I am sorry to reply late.. I was trying out lisa's solution.

But my scenario is different. I have a text bos and the expression of my textbox is as follows:

="Dear"+" "+Ucase("Robert")+space(2)+vbcrlf+"How are you"

where "vbclrf" is for new line and "space(2)" is for leaving 2 spaces between the text and "Ucase" stands for upper case.

Similarly I need a way to display the text "Robert" in bold letters.

Do you have any suggestion for this.

|||

Lisa Nicholls wrote:

...do some custom rendering. IOW, if you render the information yourself, you are free to set the formatting for each word in the text. The result becomes a small graphical "piece" in the report, though, not really text. So it wouldn't be searchable text.

If this solution appeals to you at all, I can give you more details.

>L<

It still sounds like this is what you need.

|||

>>But my scenario is different.

No, really, it's exactly what I thought. If you tried what I suggested... what exactly did you try?

In the current version of RS, what you need to do is build a function that parses your markup in a

CustomReportItem, and renders the content appropriately for your markup.

In the next version of RS (Katmai -- there is a thread post about rich text) you might have a

better choice, from the point of view of rendering. IOW, you would not have to use a

CustomReportItem for this. However, given your custom markup, you would still have to create a

code function to parse the markup and put it in a more standard form, such as HTML markup or RTF

or whatever Katmai supports, so that the standard rendering could deal with it.

For this reason, I strongly suggest you think about switching to a standard markup format that

standard renderers, whether in RS or elsewhere, could read! Your users and designers will also

thank you for it.

>L<

Dynamically set chart type?

How can I dynamically set the chart type based on a report parameter?
-mdbTo my knowledge, chart type is not available at run time, only design
time.
Andy Potter|||Howdy,
Being a complete newbie to reporting services I can think of 2 Q & D
ways to do this.
1. Add all your charts of various type to the report and set visibility
hidden=true on all but the one chart indicated by the parameter, or
2. Add and configure a chart with designer, then copy the XML in the
code view. Repeat for all chart types. Then paste all chart snippets
into the rdl with a case/switch statement for the parameter.
1. is probably quicker and dirtier, 2. is perhaps a little less
inelegant.
HTH,
Sean G.|||FYI,
YMMV, but I couldn't do much with suggestion 2 neither with the chart
<Type> nor the entire <Chart> element.
But no trouble at all with suggestion 1. Add all desired charts in
designer and set visibility based on your parameter.
Sean G.|||SeanGerman@.gmail.com wrote in news:1136925323.993091.53580
@.f14g2000cwb.googlegroups.com:
> But no trouble at all with suggestion 1. Add all desired charts in
> designer and set visibility based on your parameter.
Problem with that is that the data probably gets queried once for each
chart, no? In other words, the charts are being generated, you just can't
see them.
Its ok I found another way to do what I needed to do. Thanks!
-mdb

Dynamically send query to report

hi all,
can we use our own SQL queries to display records in the report?
if report contains the Group field.
Can we do this in Crystal Report 8.5
and SQL server 2000
Thanks in Advance
Regards
Henry JonesYes u can
First of all tell me the Client side application techn... u r using

whether classic VB6 / VS2003

and u r requirement is bit unclear can u illustrate more on it

FaFa|||Make use of SQLQuery feauture