Dynamic Select/Update Statement Possible?

Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?

For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...

ALTER PROCEDURE u_sp_x
@.semester int
AS
Select Semester@.semester
From ThisTable

Just curious.

Thanks,
Steve HanzelmanThis might work..

alter procedure u_sp_x
@.semester int
as
select * from semester
where @.semester = 'semester 1'|||You CAN do just about anything. Dynamic SQL statements would be required here, or a UNION query or complicated WHERE clause. But whether you SHOULD do it is another think entirely. Dynamic SQL statements are a pain in the butt, and should be avoided, and thus are definitely more for masochistic DBAs than lazy DBAs.

Your problem, as is often the case, is that you are having to code around a deficiency in the design of your tables. You should have a table that stores each Semester's value as a separate record. Then your application will also be easily adaptable to situations where three or five semesters are allowed, or half-semesters, or quarters, or whatever.|||Blindman,
I agree re: the design of the tables/database. Unfortunately, it is one that was inherited and belongs to an application that was purchased by my employer. Therein lies the rub...can't modify so I'm try to save a few steps.

Oh well, I'm guessing four procedures.

Thanks for the help.|||OK...

First, I have seen WAY too many slick apps that pretend to be cute..they are MAJOR pain to debug.

The smaller you make your sprocs, the better. And the less dynamic sql the better.

So with that said...the keys to the kingdom

USE Northwind
GO

CREATE PROC mySproc99 @.COLUMN_NAME sysname, @.TABLE_NAME sysname
AS
DECLARE @.sql varchar(8000)

SELECT @.sql = 'SELECT ' + @.COLUMN_NAME + ' FROM ' + @.TABLE_NAME

EXEC(@.sql)
GO

EXEC mySproc99 'ShipName','Orders'
GO

DROP PROC mySproc99
GO|||Brett proposing dynamic SQL?! :eek:

What's the weather forecast in Hell, today? ;)|||I was thinking this, but forgot...

Becareful out there...

And

Abandon all hope for ye who enter here...

Only dynamic sql I use is for admin purposes...never in an application

(Some would say some of my admin procedures amount to a mini mainframe application...but that a story for another margarita...COME ON 5:00!)

Dynamic Scripts

I've found that SSIS does not work well with running small dynamic scripts ...

For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.

The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.

Does anybody else do this, or have a better way of doing it?

I would be inclined to use:

package variables for my input values;|||

I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?

Approach 1:

1, use an Execute SQL task the define an package variable

select last_modified_date as variable1

from table

2, use another Execute SQL task to define second package variable(variable2)

select * from sourceTable

where last_modified_date > ? --variable1

3, use variable2 in OLE db source in a data flow to get source data

Approach 2:

1, 1, use an Execute SQL task with dynimac sql to define an package variable

select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL

from table

2, use the variable (variable_SQL) to get source data in OLE db source in a data flow

|||

Jessie,

Why is this not working for you? Do you get an error message?

-Jamie

|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||

This may work for you.

Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]

Look at the resulting value. The query and the date should now be together.

You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.

cheers,

Andrew

|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.

Dynamic Scripts

I've found that SSIS does not work well with running small dynamic scripts ...

For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.

The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.

Does anybody else do this, or have a better way of doing it?

I would be inclined to use:

package variables for my input values;|||

I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?

Approach 1:

1, use an Execute SQL task the define an package variable

select last_modified_date as variable1

from table

2, use another Execute SQL task to define second package variable(variable2)

select * from sourceTable

where last_modified_date > ? --variable1

3, use variable2 in OLE db source in a data flow to get source data

Approach 2:

1, 1, use an Execute SQL task with dynimac sql to define an package variable

select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL

from table

2, use the variable (variable_SQL) to get source data in OLE db source in a data flow

|||

Jessie,

Why is this not working for you? Do you get an error message?

-Jamie

|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||

This may work for you.

Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]

Look at the resulting value. The query and the date should now be together.

You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.

cheers,

Andrew

|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.

Dynamic Scripts

I've found that SSIS does not work well with running small dynamic scripts ...

For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.

The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.

Does anybody else do this, or have a better way of doing it?

I would be inclined to use:

package variables for my input values;|||

I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?

Approach 1:

1, use an Execute SQL task the define an package variable

select last_modified_date as variable1

from table

2, use another Execute SQL task to define second package variable(variable2)

select * from sourceTable

where last_modified_date > ? --variable1

3, use variable2 in OLE db source in a data flow to get source data

Approach 2:

1, 1, use an Execute SQL task with dynimac sql to define an package variable

select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL

from table

2, use the variable (variable_SQL) to get source data in OLE db source in a data flow

|||

Jessie,

Why is this not working for you? Do you get an error message?

-Jamie

|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||

This may work for you.

Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]

Look at the resulting value. The query and the date should now be together.

You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.

cheers,

Andrew

|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.sql

Dynamic report

Dear all,
I want to create a dynamic report so that I can change some field quickly.
For example: in future, if Field {Report ID} on report header is replaced by {Running User ID}. How can I do once for all reports.
Many thanksWill this work? The idea is to only maintain data in the table.

1. setup a database table that contains user-id and names etc.
2. create a string variable in the report for display.
3. write a routine to detect for the requires condition and pull data off the table.
4. for each report, plug in the same codes created in step-2.

Hope it works for you.

dynamic query

I need to change the query, according to the parameter I get from url to the reporting services.

For example,

if url =http://localhost:reportserver/parmeter=true?param1=A

then, I need to use a "query1" for the report . If param1 = B, then I need to use "query2".

I would like to know whether this is possible..

thanks,

I suggest using a stored procedure as the dataset for your target report.

The stored procedure would accept as its one parameter the report parameter of the target report and run the required query accordingly.

Dynamic Query

I'm a bit of a newbie to SRS / CRM3 and have worked through the Adventure
Works example http://msdn2.microsoft.com/en-us/library/ms171046(SQL.90).aspx
so I can get my head around creating reports.
I am already struggling with Dynamic Queries, my first attempt has failed.
= "SELECT New_LiveDate, New_sale_description, New_Amount FROM New_GIISale"
& Iif(Parameters!Amount.Value = 0.0, "", " WHERE New_Amount = " &
Parameters!Amount.Value)
I get an error
"Cannot set the command text for data set xxxxxxxxx"
"Error during processing of the CommandText expression of dataset xxxxxxx"
Can anyone tell me where I'm going wrong and point me in the direction of
any other tutorials on creating these queries.
Thanks in advance.
StuartFirst off, I only use dynamic queries in very very rare circumstances.
Unless you are doing something like dynamically determining the table to
query in most cases you do not need to do that.
Now, when you do need to do a dynamic query the thing to realize is that you
are creating a string with the query you want. The best way to see if you
are creating the string properly is to have a report with nothing on it but
a text box. Then set the textbox to an expression. The expression would be
what you have below (so your report would still have the report parameters).
Note that with a dynamic query you don't get a field list generated or the
report parameters created for you automatically.
Also, just glancing at it it looks to me the last line is the problem:
Parameters!Amount.Value)
should be
Parameters!Amount.Value & ")"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:AAF6F385-0C34-461B-93EA-8EAC9A379885@.microsoft.com...
> I'm a bit of a newbie to SRS / CRM3 and have worked through the Adventure
> Works example
> http://msdn2.microsoft.com/en-us/library/ms171046(SQL.90).aspx
> so I can get my head around creating reports.
> I am already struggling with Dynamic Queries, my first attempt has failed.
> = "SELECT New_LiveDate, New_sale_description, New_Amount FROM
> New_GIISale"
> & Iif(Parameters!Amount.Value = 0.0, "", " WHERE New_Amount = " &
> Parameters!Amount.Value)
> I get an error
> "Cannot set the command text for data set xxxxxxxxx"
> "Error during processing of the CommandText expression of dataset xxxxxxx"
> Can anyone tell me where I'm going wrong and point me in the direction of
> any other tutorials on creating these queries.
> Thanks in advance.
> Stuart
>|||You should rewrite your query like this and forget about dynamic sql...
SELECT
New_LiveDate,
New_sale_description,
New_Amount
FROM
New_GIISale
WHERE
(@.Amount != 0.0 and
New_Amount = @.Amount) or
@.Amount = 0.0
As a best practice I also would suggest to always package your queries
in stored procedures on the database...
Hope this helps!
--
Ben Sullins
http://bensullins.com

Dynamic query

Hello!
I tryed the example from Reporting Services Books Online but it didn't work.
I copied ="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
in generic query designer and when I ran the report I received the following
error: Cannot use empty object or column names. Use a single space if
necessary.If department ID is a string then you need to embed it in single quotes. I
suggest doing the following, create a textbox and assign expression to it so
you can see whether you have created it correctly.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:FCD2E55C-37F9-43C9-AF60-BAF2E9FF468E@.microsoft.com...
> Hello!
> I tryed the example from Reporting Services Books Online but it didn't
work.
> I copied ="SELECT FirstName, LastName, Title FROM Employee" &
> IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
> Parameters!Department.Value & ")") & " ORDER BY LastName"
> in generic query designer and when I ran the report I received the
following
> error: Cannot use empty object or column names. Use a single space if
> necessary.
>

dynamic queries

Hi,

I have a parameter in the url to the report. According to that parameter, I need to change the query. For example, url = http://localhost/reportserver?param1=A

if Param1=A, then I need to use query1 for the report

if param1 = B, then I need to use query2 for the report.

I would like to know if I can do this. If not, is there any other way to build query dynamically in the report.

thanks

Hi,

you can use a stored procedure for this, using the inside procedural logic for deciding which query should be executed.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Dynamic parameters

Welcome.
I have report with two parameters :From and To.
Is there any posibility to have a subscryption that use "dynamic"
parameters, for example Datetime.Today in order to have actual data ?In Report parameter, specify default parameter with Global field of today,
click the fx / expression button , look into Globals.
"Marcin Zawadzki" wrote:
> Welcome.
> I have report with two parameters :From and To.
> Is there any posibility to have a subscryption that use "dynamic"
> parameters, for example Datetime.Today in order to have actual data ?
>
>|||THQ V Much.
Works
"Marcin Zawadzki" <marcinz@.dcs.pl> wrote in message
news:cn2btd$315$1@.nemesis.news.tpi.pl...
> Welcome.
> I have report with two parameters :From and To.
> Is there any posibility to have a subscryption that use "dynamic"
> parameters, for example Datetime.Today in order to have actual data ?
>

Dynamic Parameters

I need to be able to schedule a report with a dynamic parameter.
For example,
The user may select "Start of Last Month" as the date parameter value. I
then need to turn this into something like :
CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
Date)))
When scheduling this report, this parameter should then always be "the Start
of Last Month"
I don't know how to make the parameter look at the code as an expression and
not as a String.
Any help would be appreciated.Try,
=CDATE(CStr(Month(DateAdd("m", -1, Date))) & "/01/" &
CStr(Year(DateAdd("m", -1,
Date))))
If it doesn't work, you may also try to create a default value using the
expression above at report manager.
Regards,
Cem Demircioglu
"Tarik Peterson" <tarikp@.investigo.net> wrote in message
news:utVz7JbHFHA.2132@.TK2MSFTNGP14.phx.gbl...
>I need to be able to schedule a report with a dynamic parameter.
> For example,
> The user may select "Start of Last Month" as the date parameter value. I
> then need to turn this into something like :
> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
> Date)))
> When scheduling this report, this parameter should then always be "the
> Start of Last Month"
> I don't know how to make the parameter look at the code as an expression
> and not as a String.
> Any help would be appreciated.
>|||Another option if you're using stored procedures is to set the start/end date
within the procedure based on a parameter sent through.
"Tarik Peterson" wrote:
> I need to be able to schedule a report with a dynamic parameter.
> For example,
> The user may select "Start of Last Month" as the date parameter value. I
> then need to turn this into something like :
> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
> Date)))
> When scheduling this report, this parameter should then always be "the Start
> of Last Month"
> I don't know how to make the parameter look at the code as an expression and
> not as a String.
> Any help would be appreciated.
>
>|||Thanks!
I had actually decided to do it this way prior to reading your post, so it
was good to get some kind of confirmation that this was a good way to do it.
Thanks again.
"Dave Klug" <Dave Klug@.discussions.microsoft.com> wrote in message
news:CB4A4573-EB5B-498C-AF2C-11C6FE5CDE00@.microsoft.com...
> Another option if you're using stored procedures is to set the start/end
> date
> within the procedure based on a parameter sent through.
> "Tarik Peterson" wrote:
>> I need to be able to schedule a report with a dynamic parameter.
>> For example,
>> The user may select "Start of Last Month" as the date parameter value. I
>> then need to turn this into something like :
>> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
>> Date)))
>> When scheduling this report, this parameter should then always be "the
>> Start
>> of Last Month"
>> I don't know how to make the parameter look at the code as an expression
>> and
>> not as a String.
>> Any help would be appreciated.
>>

Dynamic MDX Parameter Query Question

Hi:
Thanks first.Did anyone know how to send the query statement for dynamic
MDX Parameter query in sql server reporting service 2005.For example in
2000,we could send the statement like this:
= " select {[Measures].[aaa]} on axis(0),
{" + Parameters@.Parm_time.value + "} on axis(1)
from [Cube] "
,but it failes in 2005.Anyone know why it failes?
--
zhouhuituanI'm pretty sure you need to write the parameters like this:
Parameters!Parm_time.Value in 2005.
Also, make sure everything is on the same line (there will be page breaks,
but don't add them yourself.)
Does your syntax work without the parameters? Are you sure your parameter
value looks like the code you test your query with?
Also, are you using the graphic query builder at all? In my opinion, the
query syntax that appears when using the query builder is very different
from the MDX queries used with AS 2000, and takes a while to get used to.
The filtering in the query works differently than before, especially when
you mess around with the parameters.
What is the error message?
Kaisa M. Lindahl Lervik
"zhouhuituan" <zhouhuituan@.discussions.microsoft.com> wrote in message
news:D231360E-7B36-41B2-A476-15FE606435BB@.microsoft.com...
> Hi:
> Thanks first.Did anyone know how to send the query statement for dynamic
> MDX Parameter query in sql server reporting service 2005.For example in
> 2000,we could send the statement like this:
> = " select {[Measures].[aaa]} on axis(0),
> {" + Parameters@.Parm_time.value + "} on axis(1)
> from [Cube] "
> ,but it failes in 2005.Anyone know why it failes?
> --
> zhouhuituan

Dynamic Flat File Destination Name!

Hi,

I am trying to access from OLE DB source. And based on one of the columns, I need to write the data to a Flat File Destination.

For Example,

CustID, ProductID, Product Name, Product Description

Say I am going to write to a different Flat File for every product. So if there are 10 products in the data. There should be 10 Flat Files. Also the file name should include the Product Name And Product ID.

It is being done in a single Data Flow Task.

Right now the Property Expression for the File Name is which is not working)

Code Snippet

@.DestFolder + [Data Conversion].ProductID + @.TodaysDate + ".txt"

The ProductIDs are in the ascending order. Any help or guidance?

Thanks

-Leo

You can't do that in one data flow unless you want to split it out to a separate destination (and connection manager) for each product. That might be okay for three or four products, but not if you have a bunch.

What you should do instead is set up a ForEach loop based on a the results of a SELECT DISTINCT ProductId query. Then execute your data flow once for each ProductId, customizing both the Source query and the destination connection manager with your ProductId.
|||

Thanks for your thoughts. OK, can we achieve this i.e. first 1000 records from the query to file1 and then next 1000 to file2. Or vice versa i.e. Write all the records to a single file and then read 1000 in first iteration and next 1000 records in the next iteration and so on, without multiple reads of the whole destination file.

Thanks

--Leo

|||You might be able to use the Export Column transform. That transform writes out a file per row based on one column containing the filename and another column containing the data.|||

Correct me if I am wrong, Ted, but the Export Column transform is used when you have a binary field in your data flow that you want to persist to a file. That doesn't seem to be the case for Leo's data.

Leo, you can partition the file by rows, but what Jay is suggesting would be simpler and perform better. If you use a For Each Loop, you would get a list of all product IDs, then execute a data flow inside the loop for each product ID. Using expressions, you can alter both the OLE DB Source query and the destination file connection string for each iteration of the loop. You wouldn't ever read a destination file - you would only be writing rows to them. And you would only process each row once.

Here's a few examples of using ForEach loops:

http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

http://www.sqlis.com/55.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

http://blogs.conchango.com/jamiethomson/archive/2005/06/15/SSIS_3A00_-Getting-a-value-out-of-a-file-to-use-it-in-our-package.aspx

and a search that might have some more:

http://search.live.com/results.aspx?q=foreach+&form=QBRE&q1=macro%3Ajamiet.ssis

|||The Export Column should work with text and ntext in addition to binary -- the column containing the file data just needs to be either a character or binary lob column. This is just another option for Leo with a different approach, requiring the data and file columns to be derived, which he might be able to do since he has already derived the filename column in his original post.|||

New Leo wrote:

Thanks for your thoughts. OK, can we achieve this i.e. first 1000 records from the query to file1 and then next 1000 to file2. Or vice versa i.e. Write all the records to a single file and then read 1000 in first iteration and next 1000 records in the next iteration and so on, without multiple reads of the whole destination file.

Thanks

--Leo

Leo,

Yes you can do this. I've covered this exact problem here:

Splitting a file into multiple files

(http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx)

You won't be able to do it in a single data-flow tho.

-Jamie

Dynamic fields in SQL

I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
any suggessions?
Thnak :)The standard solution for your problem is to model the problem in the database, stroring the user defined attributes in a separate table.

Something like:
create table MY_TABLE (id int, name varchar(32) primary key(id))
create table MY_DYNAMIC_ATTRIBUTES
( id int
, attribute_name varchar(20)
, attribute_type_code char(1)
, attribute_value varchar(255)
primary key (id, attribute_name)
foreign key (id) references MY_TABLE(id)
)

The problem is usually the application reading and writing these tables.
A general report would need crosstabbing.

Specific reports, where you already know which fields are involved is easier, could even be done with a view:

create view MY_DYNAMIC_VIEW as
select
t.id
, t.name
, d1.attribute_value as address
, d2.attribute_value as city
, convert(int, d3.attribute_value) as age
from MY_TABLE t
left join MY_DYNAMIC_ATTRIBUTES d1 on d1.id = t.id and d1.attribute_name = 'address'
left join MY_DYNAMIC_ATTRIBUTES d2 on d2.id = t.id and d2.attribute_name = 'city'
left join MY_DYNAMIC_ATTRIBUTES d3 on d3.id = t.id and d3.attribute_name = 'age'|||Originally posted by plextoR
I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
any suggessions?
Thnak :)

Suggestions?

Yeah, don't do it...

Just think what kind of mess you'll end up with...

Ummmm I want a varchar(8000) column...ummm I want another one...and another one...

Booooooooooooooooooooooom

What's business requirement to support, in non tech terms...

Dynamic Expression

I would like to display calculated field on a group header.
The nature of the calculation must be passed as a parameter to the report.
For example lets assume that my report lists X,Y pairs.
On the break I would like to display the value of
Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
It is not practical to prepare an expression for every possible option and
use a selector as X,Y are also dynamically selected values from the query.
So I need a dynamic way to define the expression based on input parameters.
Is Custom code is the way to go ? How do I access the Parameters collection,
and the Report's data from Custom code ?
Any Ideas ?
Thanks.I think that custom code will be needed. You can pass the values you need
(Parameters, Report Data) in as parameters to a function.
"NL" wrote:
> I would like to display calculated field on a group header.
> The nature of the calculation must be passed as a parameter to the report.
> For example lets assume that my report lists X,Y pairs.
> On the break I would like to display the value of
> Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> It is not practical to prepare an expression for every possible option and
> use a selector as X,Y are also dynamically selected values from the query.
> So I need a dynamic way to define the expression based on input parameters.
> Is Custom code is the way to go ? How do I access the Parameters collection,
> and the Report's data from Custom code ?
> Any Ideas ?
> Thanks.
>
>
>
>
>
>|||How does one create a custom function that manipulates the headers of a
report based on parameter values?
"John W" wrote:
> I think that custom code will be needed. You can pass the values you need
> (Parameters, Report Data) in as parameters to a function.
> "NL" wrote:
> > I would like to display calculated field on a group header.
> > The nature of the calculation must be passed as a parameter to the report.
> >
> > For example lets assume that my report lists X,Y pairs.
> > On the break I would like to display the value of
> > Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> >
> > It is not practical to prepare an expression for every possible option and
> > use a selector as X,Y are also dynamically selected values from the query.
> > So I need a dynamic way to define the expression based on input parameters.
> >
> > Is Custom code is the way to go ? How do I access the Parameters collection,
> > and the Report's data from Custom code ?
> >
> > Any Ideas ?
> >
> > Thanks.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||The previous question was asking about display a calculated field on a group
header. You can do this by adding a field to the group header that references
a function defined in VB.NET code in the Report Properties Code tab.
Code sample:
Public Function GetDoubledValue(ByVal NumToDouble As Int) As String
Return CStr(NumToDouble * 2)
End Function
Field Reference:
=Code.GetDoubledValue(4)
"Leneise44" wrote:
> How does one create a custom function that manipulates the headers of a
> report based on parameter values?
> "John W" wrote:
> > I think that custom code will be needed. You can pass the values you need
> > (Parameters, Report Data) in as parameters to a function.
> >
> > "NL" wrote:
> >
> > > I would like to display calculated field on a group header.
> > > The nature of the calculation must be passed as a parameter to the report.
> > >
> > > For example lets assume that my report lists X,Y pairs.
> > > On the break I would like to display the value of
> > > Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> > >
> > > It is not practical to prepare an expression for every possible option and
> > > use a selector as X,Y are also dynamically selected values from the query.
> > > So I need a dynamic way to define the expression based on input parameters.
> > >
> > > Is Custom code is the way to go ? How do I access the Parameters collection,
> > > and the Report's data from Custom code ?
> > >
> > > Any Ideas ?
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >

Dynamic Decimal Format

I have a text field = 3303.123456.
I want to display decimals based on a dynanic parameter.
For example if 4 decimals = 3303.1234. If 2 = 3303.12.
The dynamic parameter will be a field in the data set.You can dynamically generate a format code string on a textbox. Assuming you
have an integer parameter called Decimals, you would set the Format property
of the textbox to an expression like this: ="N" &
Parameters!Decimals.Value.ToString()
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott2624" <Scott2624@.discussions.microsoft.com> wrote in message
news:55FC071E-E0A0-4AF4-8015-5F2F2DA7D082@.microsoft.com...
>I have a text field = 3303.123456.
> I want to display decimals based on a dynanic parameter.
> For example if 4 decimals = 3303.1234. If 2 = 3303.12.
> The dynamic parameter will be a field in the data set.

Dynamic columns in RS reports

I have to design a report that will have a dynamic number of columns, in a
dynamic order.
For example: items and a selection from their attributes
ITEM Attr 1 Attr 2
Attr3 Attr 4
I1 5 Realy large text
........................................ Short text
12305
etc...
I know the matrix is used to obtain dynamic number of columns, but the
problem is that the columns in matrix have the same width
and i need different column widths (for text attributes and numeric
attributes for example), and the column width can have only numeric
(and fixed) values - i mean you can put there a function call..
Is there any way other than generating the rdl at run time to obtain such
dynamic table ?
Thanks in advance for any advice.This is not really feasible as far as I can tell, in the way you are
asking.
You can't set column width dynamically, which sounds like a big problem
for you.
But you may be able to get some way towards it if you compromise a
little.
In terms of no. of columns, you'd need to set a maximum number, make
sure you always return that number in the dataset even if they contain
nulls you can then allow for the maximum in your report and just hide
or show them as needed. You'd have to do it this way because you will
be referring to the columns with a field name and the report validates
that those fields will be returned when it renders.
You can then hide table columns or text boxes in a list control based
on the number of columns you're expecting that scenario. You could have
several table columns geared to one dataset column, all set differently
according to data type, then hide the columns that you don't need based
on whats returned in the query.
Chris
Razvan Popov wrote:
> I have to design a report that will have a dynamic number of columns,
> in a dynamic order.
> For example: items and a selection from their attributes
> ITEM Attr 1 Attr 2
> Attr3 Attr 4
> I1 5 Realy large text
> ........................................ Short text
> 12305
> etc...
> I know the matrix is used to obtain dynamic number of columns, but
> the problem is that the columns in matrix have the same width
> and i need different column widths (for text attributes and numeric
> attributes for example), and the column width can have only numeric
> (and fixed) values - i mean you can put there a function call..
> Is there any way other than generating the rdl at run time to obtain
> such dynamic table ?
> Thanks in advance for any advice.|||Thank you for your suggestion. But my problem is a little worse than that:
I don't have a maximum number of columns (the user can add attributes and
select them into the report at runtime).
And if i set a maximum number of X columns as constraint. I don't know which
will be the X attributes the user selected to
be displayed into report and for that I have no way of presetting the width
of the columns (which should be dependent on attribute type)..
I have as the last option to generate dinamically the RDL (or parts of it)
but this is prety complex solution and i would like to be sure that there is
no
simpler solution before i start coding such scenario...
Thx
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> This is not really feasible as far as I can tell, in the way you are
> asking.
> You can't set column width dynamically, which sounds like a big problem
> for you.
> But you may be able to get some way towards it if you compromise a
> little.
> In terms of no. of columns, you'd need to set a maximum number, make
> sure you always return that number in the dataset even if they contain
> nulls you can then allow for the maximum in your report and just hide
> or show them as needed. You'd have to do it this way because you will
> be referring to the columns with a field name and the report validates
> that those fields will be returned when it renders.
> You can then hide table columns or text boxes in a list control based
> on the number of columns you're expecting that scenario. You could have
> several table columns geared to one dataset column, all set differently
> according to data type, then hide the columns that you don't need based
> on whats returned in the query.
> Chris|||Razvan,
You've got a pretty complex scenario, I think you're going to need a
complex solution!
It sounds like you're developing a form of end-user report builder?
Have you taken a look at Cizer (www.cizer.com) or Report Builder in
SQL2005?
Chris
Razvan Popov wrote:
> Thank you for your suggestion. But my problem is a little worse than
> that: I don't have a maximum number of columns (the user can add
> attributes and select them into the report at runtime).
> And if i set a maximum number of X columns as constraint. I don't
> know which will be the X attributes the user selected to
> be displayed into report and for that I have no way of presetting the
> width of the columns (which should be dependent on attribute type)..
> I have as the last option to generate dinamically the RDL (or parts
> of it) but this is prety complex solution and i would like to be sure
> that there is no
> simpler solution before i start coding such scenario...
> Thx
>
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> > This is not really feasible as far as I can tell, in the way you are
> > asking.
> > You can't set column width dynamically, which sounds like a big
> > problem for you.
> >
> > But you may be able to get some way towards it if you compromise a
> > little.
> > In terms of no. of columns, you'd need to set a maximum number, make
> > sure you always return that number in the dataset even if they
> > contain nulls you can then allow for the maximum in your report and
> > just hide or show them as needed. You'd have to do it this way
> > because you will be referring to the columns with a field name and
> > the report validates that those fields will be returned when it
> > renders.
> >
> > You can then hide table columns or text boxes in a list control
> > based on the number of columns you're expecting that scenario. You
> > could have several table columns geared to one dataset column, all
> > set differently according to data type, then hide the columns that
> > you don't need based on whats returned in the query.
> >
> > Chris|||Thank you for your help..
If i find any solution simpler than generating rdls dynamically i'll post it
here..
Razvan

Dynamic columns in RS reports

I have to design a report that will have a dynamic number of columns, in a
dynamic order.
For example: items and a selection from their attributes
ITEM Attr 1 Attr 2
Attr3 Attr 4
I1 5 Realy large text
........................................ Short text
12305
etc...
I know the matrix is used to obtain dynamic number of columns, but the
problem is that the columns in matrix have the same width
and i need different column widths (for text attributes and numeric
attributes for example), and the column width can have only numeric
(and fixed) values - i mean you can put there a function call..
Is there any way other than generating the rdl at run time to obtain such
dynamic table ?
Thanks in advance for any advice.This is not really feasible as far as I can tell, in the way you are
asking.
You can't set column width dynamically, which sounds like a big problem
for you.
But you may be able to get some way towards it if you compromise a
little.
In terms of no. of columns, you'd need to set a maximum number, make
sure you always return that number in the dataset even if they contain
nulls you can then allow for the maximum in your report and just hide
or show them as needed. You'd have to do it this way because you will
be referring to the columns with a field name and the report validates
that those fields will be returned when it renders.
You can then hide table columns or text boxes in a list control based
on the number of columns you're expecting that scenario. You could have
several table columns geared to one dataset column, all set differently
according to data type, then hide the columns that you don't need based
on whats returned in the query.
Chris
Razvan Popov wrote:
> I have to design a report that will have a dynamic number of columns,
> in a dynamic order.
> For example: items and a selection from their attributes
> ITEM Attr 1 Attr 2
> Attr3 Attr 4
> I1 5 Realy large text
> ........................................ Short text
> 12305
> etc...
> I know the matrix is used to obtain dynamic number of columns, but
> the problem is that the columns in matrix have the same width
> and i need different column widths (for text attributes and numeric
> attributes for example), and the column width can have only numeric
> (and fixed) values - i mean you can put there a function call..
> Is there any way other than generating the rdl at run time to obtain
> such dynamic table ?
> Thanks in advance for any advice.|||Thank you for your suggestion. But my problem is a little worse than that:
I don't have a maximum number of columns (the user can add attributes and
select them into the report at runtime).
And if i set a maximum number of X columns as constraint. I don't know which
will be the X attributes the user selected to
be displayed into report and for that I have no way of presetting the width
of the columns (which should be dependent on attribute type)..
I have as the last option to generate dinamically the RDL (or parts of it)
but this is prety complex solution and i would like to be sure that there is
no
simpler solution before i start coding such scenario...
Thx
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> This is not really feasible as far as I can tell, in the way you are
> asking.
> You can't set column width dynamically, which sounds like a big problem
> for you.
> But you may be able to get some way towards it if you compromise a
> little.
> In terms of no. of columns, you'd need to set a maximum number, make
> sure you always return that number in the dataset even if they contain
> nulls you can then allow for the maximum in your report and just hide
> or show them as needed. You'd have to do it this way because you will
> be referring to the columns with a field name and the report validates
> that those fields will be returned when it renders.
> You can then hide table columns or text boxes in a list control based
> on the number of columns you're expecting that scenario. You could have
> several table columns geared to one dataset column, all set differently
> according to data type, then hide the columns that you don't need based
> on whats returned in the query.
> Chris|||Razvan,
You've got a pretty complex scenario, I think you're going to need a
complex solution!
It sounds like you're developing a form of end-user report builder?
Have you taken a look at Cizer (www.cizer.com) or Report Builder in
SQL2005?
Chris
Razvan Popov wrote:
> Thank you for your suggestion. But my problem is a little worse than
> that: I don't have a maximum number of columns (the user can add
> attributes and select them into the report at runtime).
> And if i set a maximum number of X columns as constraint. I don't
> know which will be the X attributes the user selected to
> be displayed into report and for that I have no way of presetting the
> width of the columns (which should be dependent on attribute type)..
> I have as the last option to generate dinamically the RDL (or parts
> of it) but this is prety complex solution and i would like to be sure
> that there is no
> simpler solution before i start coding such scenario...
> Thx
>
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> > This is not really feasible as far as I can tell, in the way you are
> > asking.
> > You can't set column width dynamically, which sounds like a big
> > problem for you.
> >
> > But you may be able to get some way towards it if you compromise a
> > little.
> > In terms of no. of columns, you'd need to set a maximum number, make
> > sure you always return that number in the dataset even if they
> > contain nulls you can then allow for the maximum in your report and
> > just hide or show them as needed. You'd have to do it this way
> > because you will be referring to the columns with a field name and
> > the report validates that those fields will be returned when it
> > renders.
> >
> > You can then hide table columns or text boxes in a list control
> > based on the number of columns you're expecting that scenario. You
> > could have several table columns geared to one dataset column, all
> > set differently according to data type, then hide the columns that
> > you don't need based on whats returned in the query.
> >
> > Chris|||Thank you for your help..
If i find any solution simpler than generating rdls dynamically i'll post it
here..
Razvan

Dynamic columns

Hi,
Can a column be created dynamically in a table...
For example the user selects start date and enddate and i want to create
columns from startdate to enddate...
Thanks,
Chandra.Chandra,
The simple answer for tables is "No".
What you should be using is a matrix, the columns are dynamic on this.
Chris
CCP wrote:
> Hi,
> Can a column be created dynamically in a table...
> For example the user selects start date and enddate and i want to
> create columns from startdate to enddate...
> Thanks,
> Chandra.

dynamic aliases/columns

Hi
Is it possible to return a table that its aliases are changing according to
varaiables ?
For example: I would like to use something like (ofcourse it doesn't work).
declare @.p1,@.p2 varchar(30)
set @.p1 ='blhablha'
set @.p2 ='hghfg'
select field1 as @.p1, field2 as @.p2 from table1Only with dynamic SQL (sp_executesql or exec). Most of the time,
however, dynamic SQL is not really a terrific idea. Here is Erland
Sommarskog's page on dynamic SQL (a frequently referenced page):
http://www.sommarskog.se/dynamic_sql.html
I can't imagine why you'd actually want to do this. What are you trying
to achieve?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
romy wrote:

>Hi
>Is it possible to return a table that its aliases are changing according to
>varaiables ?
>For example: I would like to use something like (ofcourse it doesn't work)
.
>
>declare @.p1,@.p2 varchar(30)
>set @.p1 ='blhablha'
>set @.p2 ='hghfg'
>select field1 as @.p1, field2 as @.p2 from table1
>
>|||rommy
if changing alias is only problem
you can use the trick of if condition in sp of CASE in query.Post your
script to suggest you better.
Regards
R.D
"romy" wrote:

> Hi
> Is it possible to return a table that its aliases are changing according t
o
> varaiables ?
> For example: I would like to use something like (ofcourse it doesn't work
).
>
> declare @.p1,@.p2 varchar(30)
> set @.p1 ='blhablha'
> set @.p2 ='hghfg'
> select field1 as @.p1, field2 as @.p2 from table1
>
>|||While I can't think of why you would need to do this on the server,
you could use put the results into a table, rename the columns
with sp_rename (which accepts parameters), then select the contents
of the table.
declare
@.cname1 sysname,
@.cname2 sysname
set @.cname1 = N'lName'
set @.cname2 = N'ID'
select LastName, EmployeeID
into #tmp
from Northwind..Employees
exec tempdb..sp_rename N'#tmp.LastName', @.cname1, 'COLUMN'
exec tempdb..sp_rename N'#tmp.EmployeeID', @.cname2, 'COLUMN'
select * from #tmp
drop table #tmp
Steve Kass
Drew University
romy wrote:

>Hi
>Is it possible to return a table that its aliases are changing according to
>varaiables ?
>For example: I would like to use something like (ofcourse it doesn't work)
.
>
>declare @.p1,@.p2 varchar(30)
>set @.p1 ='blhablha'
>set @.p2 ='hghfg'
>select field1 as @.p1, field2 as @.p2 from table1
>
>