dynamic row formatting based on dataset field values

HI y'all.
PROBLEM. Would like to change row color based on a calculated field.
I.E. need the row to highlight when an expiration date is within 14 days of
expiring.
I can use the row expression panel to alternate row color, but cant quite
find the magic to make it happen with data as the driver. I can also use the
report code panel which can be accessed with an =Code.bgcolor in the row
background settings.
(sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
But yet the report code requires objects references and I can find no
information how this is done in reporting services.
While I am here: What is the process sequence at run time? Does the
report, Table and row formatting have a view of the fields in a defined
dataset?
Thanks in advance.From Properties/BackgroundColor, select Expression.
If you are using a field as the driver, cope and paste the following code
and make sure to change YourFieldName with the actual name.
=IIf(Fields!YourFieldName.Value < 14, "Red", "White")
If you are using a parameter field as the driver, use this instead.
=IIf(Parameters!YourParameterName.Value < 14, "Red", "White")
"MKTapps" wrote:
> HI y'all.
> PROBLEM. Would like to change row color based on a calculated field.
> I.E. need the row to highlight when an expiration date is within 14 days of
> expiring.
> I can use the row expression panel to alternate row color, but cant quite
> find the magic to make it happen with data as the driver. I can also use the
> report code panel which can be accessed with an =Code.bgcolor in the row
> background settings.
> (sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
> But yet the report code requires objects references and I can find no
> information how this is done in reporting services.
> While I am here: What is the process sequence at run time? Does the
> report, Table and row formatting have a view of the fields in a defined
> dataset?
> Thanks in advance.

dynamic query

hi,

i'm using dynamic query in the dataset because i wanted the result to group by the particular parameter selected.

however, another parameter requires the data to be taken from another table.

is the following syntax acceptable?

=if (Parameters!rev_data = 'A')

begin

"select a,b,c.... from tableA"

end

else

begin

"select d,e,f.... from tableB"

end

i encounter this error : 'Expression expected'...

is the syntax wrong or this way is not possible?

thanks!

I use Stored Procedures to generate dynamic SQL used for reports. The conditional logic is handled in T-SQL to generate and execute the appropriate SQL based on the input parameters. For example, when users want to select the sort fields and sort order for the data in a report.|||Can you give us an example of how you made this work in the stored procedure?

dynamic query

hi,

i'm using dynamic query in the dataset because i wanted the result to group by the particular parameter selected.

however, another parameter requires the data to be taken from another table.

is the following syntax acceptable?

=if (Parameters!rev_data = 'A')

begin

"select a,b,c.... from tableA"

end

else

begin

"select d,e,f.... from tableB"

end

i encounter this error : 'Expression expected'...

is the syntax wrong or this way is not possible?

thanks!

I use Stored Procedures to generate dynamic SQL used for reports. The conditional logic is handled in T-SQL to generate and execute the appropriate SQL based on the input parameters. For example, when users want to select the sort fields and sort order for the data in a report.|||Can you give us an example of how you made this work in the stored procedure?

Dynamic Parameters

I need to build a report in which every field of the dataset acts as a
Parameter to filter data in the report, so that the user is not binded
only to a fixed set of filters.
Based on the datatype of the filter; the condition should be specified
and the user will only supply the value for which he is looking for.
For Example:
In the report i have about 10 fields
EmpSSN No varchar(20)
Emp FirstName varchar(20)
Emp LastName varchar(20)
Emp Location varchar(20)
Emp MartialStatus char(1)
Emp DOB datetime
Emp Salary numeric(18,2)
Emp Designation varchar(20)
Emp Department varchar(20)
Emp Status varchar(20)
When i choose the filter to be as DOB the condition needs to be
generated as >,>=,<,<=,!= and user can define a specific data for the
filter.
Pls advise.Hi,
I am about to start building the exact same report as you outlined
below. did you get any references or whether or not this can be achieved
using reporting services
--
Ciaran
Software developer
"brinda.shree@.gmail.com" wrote:
> I need to build a report in which every field of the dataset acts as a
> Parameter to filter data in the report, so that the user is not binded
> only to a fixed set of filters.
> Based on the datatype of the filter; the condition should be specified
> and the user will only supply the value for which he is looking for.
> For Example:
> In the report i have about 10 fields
> EmpSSN No varchar(20)
> Emp FirstName varchar(20)
> Emp LastName varchar(20)
> Emp Location varchar(20)
> Emp MartialStatus char(1)
> Emp DOB datetime
> Emp Salary numeric(18,2)
> Emp Designation varchar(20)
> Emp Department varchar(20)
> Emp Status varchar(20)
> When i choose the filter to be as DOB the condition needs to be
> generated as >,>=,<,<=,!= and user can define a specific data for the
> filter.
> Pls advise.
>

Dynamic formatting question

Hello,

I have a dataset that returns one column of data from which some fields are supposed to appear horizontally in the report, while the rest appear line by line vertically. Is it possible to take, for example, three consecutive fields from the dataset and merge the corresponding table rows that hold these fields in the report? Perhaps using an expression or custom code?

Thanks in advance for any help. I'd be happy to provide examples of what I need if it can help clarify the question.

RLGow

Not exactly sure what you are trying to do, but you can write expressions that concatenate the value of multiple fields, such as:

=Fields!ColumnA.Value & Fields!ColumnB.Value & Fields!ColumnC.Value

-- Robert

Dynamic footer visibility

I have two DataSet and two tables in a report. Almost identical reports (top 5). Both DataSet have a Boolean indicator and I am to append an asterisk to a column in the detail rows if indicator is true. If an asterisk appears, I am to display the footer, that has a comment in it explaining the asterisk.

Public Class AsteriskFootNote

'

' To be used on the Visibility.Hidden attribute

'

Dim _FooterVisiblity As Boolean = True

Public ReadOnly Property FooterVisibility() As Boolean

Get

Return _FooterVisibility

End Get

End Property

'

Function AsteriskIndicator(ByVal strInd As String) As String

If (strInd.ToLower = "true") Then

_FooterVisibility = False

Return "*"

End If

Return ""

End Function

'

End Class

I have instantiated two classes Dollars and Hours. In the detail, call the

=Fields!Descr.Value & Code.Dollar.AsteriskIndicator( Fields!MultiInd.Value )

In the footer visibility, I assign hidden:

=Code.Dollar.FooterVisibility

But it does not work. The tables act as if they assign the Hidden value, then processes the detail rows.

I belieive the problem is that RS processes the outer bands/section of the table prior to the inner bands. This means that the footer of a table is actually processed prior to the detail section. Two possibilties are

Use an invisible table at the beginning of a report to call the asterikindicator function.

or

Instead of embedded code you should be able to construct an expression like =iif(sum(iif(fields!boolfield=True,1,0)),False,True) and put it on the visibility property of the footer row.

|||

phuhn wrote:

But it does not work. The tables act as if they assign the Hidden value, then processes the detail rows.

I'm not sure I understand this last bit. What actually happens?

Have you tested that the function calls actually work?

Try placing the expressions inside a textbox and see if the value returned is as expected.

It's probably just a typo but you say that you instatiated a class calld Dollars, yet your expression referes to Dollar.

|||

Yes, I created a testing report and played around with TextBox's and it does work.

My temporary solution was to remove the Footer and replace the footer with a TextBox and it does work.

Phil

Dynamic footer visibility

I have two DataSet and two tables in a report. Almost identical reports (top 5). Both DataSet have a Boolean indicator and I am to append an asterisk to a column in the detail rows if indicator is true. If an asterisk appears, I am to display the footer, that has a comment in it explaining the asterisk.

Public Class AsteriskFootNote

'

' To be used on the Visibility.Hidden attribute

'

Dim _FooterVisiblity As Boolean = True

Public ReadOnly Property FooterVisibility() As Boolean

Get

Return _FooterVisibility

End Get

End Property

'

Function AsteriskIndicator(ByVal strInd As String) As String

If (strInd.ToLower = "true") Then

_FooterVisibility = False

Return "*"

End If

Return ""

End Function

'

End Class

I have instantiated two classes Dollars and Hours. In the detail, call the

=Fields!Descr.Value & Code.Dollar.AsteriskIndicator( Fields!MultiInd.Value )

In the footer visibility, I assign hidden:

=Code.Dollar.FooterVisibility

But it does not work. The tables act as if they assign the Hidden value, then processes the detail rows.

I belieive the problem is that RS processes the outer bands/section of the table prior to the inner bands. This means that the footer of a table is actually processed prior to the detail section. Two possibilties are

Use an invisible table at the beginning of a report to call the asterikindicator function.

or

Instead of embedded code you should be able to construct an expression like =iif(sum(iif(fields!boolfield=True,1,0)),False,True) and put it on the visibility property of the footer row.

|||

phuhn wrote:

But it does not work. The tables act as if they assign the Hidden value, then processes the detail rows.

I'm not sure I understand this last bit. What actually happens?

Have you tested that the function calls actually work?

Try placing the expressions inside a textbox and see if the value returned is as expected.

It's probably just a typo but you say that you instatiated a class calld Dollars, yet your expression referes to Dollar.

|||

Yes, I created a testing report and played around with TextBox's and it does work.

My temporary solution was to remove the Footer and replace the footer with a TextBox and it does work.

Phil

Dynamic field list

Hi,
The underlying query in the dataset of my report has a set number of static
fields (which I bind to report elements) but also can return additional
variable number of fields, depending on passed parameters. Is there a way to
access those fields at report runtime?
Thanks.How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10=A0pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset =A0of my report has a set number of st=atic
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way =to
> access those fields at report runtime?
> Thanks.|||That's the thing - I don't know in advance *how many* dynamic columns I am
going to return. Let's say I pass no parameters - the query will return
columns:
A B C
If I pass parameter "1" the query will return columns
A B C D
If I pass parameter "2" the the query will return columns
A B C D E
field list in dataset in report definition can contain only static number of
fields and if I bind report to A B C then D and E become unaccessable even
if query returns them.
The only way I can think of is, since I am launching the report from a .NET
application anyway is download report definition and modify it on the fly by
adding new columns to dataset field list. But I'd like to avoid it if
possible.
<prabhupr@.gmail.com> wrote:
How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10 pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset of my report has a set number of
> static
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way
> to
> access those fields at report runtime?
> Thanks.|||Not tested , Just an idea - Does the use of
=IIF(Fields!Column_1.IsMissing, true, false)in the hidden property of the
coloumn solve your problem ?
P.I.
"Yuriy Galanter" <yuri@.galanter.net> a écrit dans le message de news:
eXm6bCJrIHA.4848@.TK2MSFTNGP05.phx.gbl...
> Hi,
> The underlying query in the dataset of my report has a set number of
> static fields (which I bind to report elements) but also can return
> additional variable number of fields, depending on passed parameters. Is
> there a way to access those fields at report runtime?
> Thanks.
>

dynamic field for backgroundcolor

How can i change the background color of a textbox in RS where the
background color is stored in a dataset?
I have been trying this on the background color properties:
=Fields.LineColor.Value
Values on that dataset include standard colors like:
-DimGray
-Gray
-White
so on
I also tried using Hex or integers but to no avail.
I need this because I have set of rows where color is attached. The
list is dynamic and cannot be hard-coded in the report.Shouldn't that be:
=Fields!LineColor.Value
vice
=Fields.LineColor.Value
John Grant
=""" & Fields!
"rGenius" wrote:
> How can i change the background color of a textbox in RS where the
> background color is stored in a dataset?
> I have been trying this on the background color properties:
> =Fields.LineColor.Value
> Values on that dataset include standard colors like:
> -DimGray
> -Gray
> -White
> so on
> I also tried using Hex or integers but to no avail.
> I need this because I have set of rows where color is attached. The
> list is dynamic and cannot be hard-coded in the report.
>|||typo error
yes it is =Fields!LineColor.Value

Dynamic Dataset to Reports

Hi,
I have some selection criteria in my .aspx web page and based on the
selections, by using an stored procedure, I am generating the dataset. As of
now, I am displaying this in HTML table format.
Now I have designed the report using Reporting Services and have deployed it
onto the Report Server. I would like to know how do I pass this dataset from
my .aspx webpage onto the report and generate the same.
Could any one please let me know the links to materials explaining this
technique or any help would be highly appreciated.
Regards,
Sudhakara.T.P.Hi,
Try to do this.
1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
2. Configure your ReportViewer to a LocalReport mode. This will allow you to
link your dataset to a report at runtime by using ReportViewer.LocalReport
methods or at disign level too.
Hopefully I help you.
Arturo Carrión
at Teimpo Hard Development team
"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> escribió en el
mensaje news:3188FD41-0A7F-448F-AE2C-7AD2423F343F@.microsoft.com...
> Hi,
> I have some selection criteria in my .aspx web page and based on the
> selections, by using an stored procedure, I am generating the dataset. As
> of
> now, I am displaying this in HTML table format.
> Now I have designed the report using Reporting Services and have deployed
> it
> onto the Report Server. I would like to know how do I pass this dataset
> from
> my .aspx webpage onto the report and generate the same.
> Could any one please let me know the links to materials explaining this
> technique or any help would be highly appreciated.
> Regards,
> Sudhakara.T.P.|||Hi,
Thank you very much for your reply.
Infact, I tried doing this earlier, but the problem with this is that, in
the preview mode, I am not getting the print button to print the button, even
tough I have set the showprintoption to true.
Any alternative solution for this?
Regards,
Sudhakara.T.P.
"tiempotecnologia@.newsgroup.nospam" wrote:
> Hi,
> Try to do this.
> 1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
> 2. Configure your ReportViewer to a LocalReport mode. This will allow you to
> link your dataset to a report at runtime by using ReportViewer.LocalReport
> methods or at disign level too.
> Hopefully I help you.
> Arturo Carrión
> at Teimpo Hard Development team
> "Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> escribió en el
> mensaje news:3188FD41-0A7F-448F-AE2C-7AD2423F343F@.microsoft.com...
> > Hi,
> > I have some selection criteria in my .aspx web page and based on the
> > selections, by using an stored procedure, I am generating the dataset. As
> > of
> > now, I am displaying this in HTML table format.
> > Now I have designed the report using Reporting Services and have deployed
> > it
> > onto the Report Server. I would like to know how do I pass this dataset
> > from
> > my .aspx webpage onto the report and generate the same.
> > Could any one please let me know the links to materials explaining this
> > technique or any help would be highly appreciated.
> > Regards,
> > Sudhakara.T.P.
>
>

Dynamic Dataset for Chart in Report

Is it possible to dynamically set the dataset for a chart on a report?

What I'm trying to do is generate a single chart on a report. Depending on what items a user selects as parameters would determine the dataset used. Unfortunately, one dataset comes from Oracle the other from SQLServer.

Thanks!

You could have two charts - one per dataset. Set the chart.Visibility property so that only one chart is visible depending on the selected parameter value.

-- Robert

Dynamic dataset column names and number of columns

I have a dataset that is based on a dynamic pivot stored procedure. There is
at least one column name that I know will be in the dataset, the key column,
but the rest of the columns are dependent on the data in the underlying table.
Once I have the dataset, how can I put it in the layout given that the
column names are dynamic and the number of columns are dynamic?
StephanieOn Aug 20, 4:36 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset that is based on a dynamic pivot stored procedure. There is
> at least one column name that I know will be in the dataset, the key column,
> but the rest of the columns are dependent on the data in the underlying table.
> Once I have the dataset, how can I put it in the layout given that the
> column names are dynamic and the number of columns are dynamic?
> Stephanie
You will want to look into Matrix Reports, where the pivoted column's
unique values are actually the number of columns in the report. This
provides the dynamic functionality you need.
Regards,
Enrique Martinez
Sr. Software Consultant

Dynamic dataset

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

Dynamic Data sets in reports

Hi,

I'm using a matrix report where in i want to use two datasets in the same report. How can i make the dataset dynamic for a single report.

Regards

Can the two datasets be accessed from the same datasource/connection?

Can they be made to "express themselves" using exactly the same column names ?

>L<

Dynamic connection string in dataset?

Hi,

Since rds (dataset file) is simple XML, is it possible to change the datasource value from it and use it without deploying it on the RS server, similar as we do with the web.config?

TIA,

Tanmaya

Just changing the xml contents of a rds file in the file system won't have any effects on the report server. Shared data source settings can only be changed through the RS SOAP API methods.

-- Robert

Dynamic conditional report parameters error. SQL Server 2005 SSRS

Given the following dataset:
="Select * " &
"From pat " &
"WHERE (pattype IN (@.pattype)) " &
"AND (facility IN (@.facility)) " &
IIf(Parameters!fromdate.Value <> "","", "AND fromdate = " &
CDate(Parameters!fromdate.Value)) &
"Order By pattype,facility"
I am trying to dynamically generate a report based on pattype,
facility, and fromdate. The problem I am running into is that it keeps
choking on the fromdate parameter and I don't know why. I keep getting
'Cannot set the command text for data set (above data set)'.
Is it a problem with syntax? Am I not typecasting correctly? The data
type for 'fromdate' in the database is DateTime.
I'm wondering if this might not be possible to do, given the first two
lines in the WHERE statement use @. parameters and the IIF statement
doesn't...
Any advice would be greatly appreciated. Thank you...Correction on the SELECT statement:
="Select * " &
"From pat " &
"WHERE (pattype IN (@.pattype)) " &
"AND (facility IN (@.facility)) " &
IIf(Parameters!fromdate.Value = "","", "AND fromdate = " & <--
CDate(Parameters!fromdate.Value)) &
"Order By pattype,facility"|||Two things, first, you are assembling a string. When trying to debug this
what I suggest is to have a report with the report parameters and a single
textbox that you assign this expression to so you can see the resulting
string. This helps you figure out what is going on.
One of the things you are doing wrong is with your @.pattype and @.facility.
You have that embedded in your string, RS will not be replacing these for
you. What you need to do is this:
"WHERE (pattype IN (" & Join(Parameters!pattype.Value, ", ") & ")) " &
Here is a summary from Robert a MS employee on a variety of expressions for
multi-value parameters.
To access individual values of a multi value parameter you can use
expressions like this:
=Parameters!MVP1.IsMultiValue boolean flag - tells if a parameter is
defined as multi value
=Parameters!MVP1.Count returns the number of values in the array
=Parameters!MVP1.Value(0) returns the first selected value
=Join(Parameters!MVP1.Value) creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ") creates a comma separated list of
values
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178558124.887441.311130@.y5g2000hsa.googlegroups.com...
> Given the following dataset:
> ="Select * " &
> "From pat " &
> "WHERE (pattype IN (@.pattype)) " &
> "AND (facility IN (@.facility)) " &
> IIf(Parameters!fromdate.Value <> "","", "AND fromdate = " &
> CDate(Parameters!fromdate.Value)) &
> "Order By pattype,facility"
> I am trying to dynamically generate a report based on pattype,
> facility, and fromdate. The problem I am running into is that it keeps
> choking on the fromdate parameter and I don't know why. I keep getting
> 'Cannot set the command text for data set (above data set)'.
> Is it a problem with syntax? Am I not typecasting correctly? The data
> type for 'fromdate' in the database is DateTime.
> I'm wondering if this might not be possible to do, given the first two
> lines in the WHERE statement use @. parameters and the IIF statement
> doesn't...
> Any advice would be greatly appreciated. Thank you...
>|||First of all, thank you. I appreciate your reply.
Now that you mention it, that makes obvious sense. I overlooked that
while I was going through the Books Online tutorials and I feel they
just 'jumped' too quickly without describing it the way you did. So
thanks for that.
The problem I'm running into now is that I am at this point:
="Select *" &
" From pat" &
" WHERE (pattype IN (" & Join(Parameters!pattype.Value, ", ") &
")) " &
" AND (facility IN (" & Join(Parameters!facility.Value, ",
") & "))" &
Iif(Parameters!fromdate.Value = ""," Order By pattype,facility", " AND
(fromdate IN (" & Parameters!fromdate.Value & ")) Order By
pattype,facility,fromdate")
I'm still getting command text errors though. Can you offer any other
thoughts? Perhaps elaborate on how you use a textbox for a resulting
string (or is this useful after I get past my current problem?)? Or
can you point me to other tutorials that get more involved with what I
am trying to do? Books online don't really go deep enough.
Thanks again for your help. I assure you I am rigorously working on
this. Any help is once again greatly appreciated.
Jay|||The best way to do this is to assign this expression to a textbox so you can
see it (copy the report and delete everything except the parameters and the
textbox).
What I bet is happening is that your data types are strings. That means what
you really want is not a comma separated string. You want single quotes
around it. You could write some code behind reports that you bind the
parameter to that takes the parameter and returns a string all properly
formatted.
I have a suggestion, use the user sortable columns instead of sorting it
yourself this way. Then you would not have to use an expression for your
dataset definition in the first place. Using expressions for this is
generally a pain. For one thing, it will not give you a list of fields. You
have to use a regular sql string to get your field list and then change it
to an expression after than.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178567450.999831.232960@.q75g2000hsh.googlegroups.com...
> First of all, thank you. I appreciate your reply.
> Now that you mention it, that makes obvious sense. I overlooked that
> while I was going through the Books Online tutorials and I feel they
> just 'jumped' too quickly without describing it the way you did. So
> thanks for that.
> The problem I'm running into now is that I am at this point:
> ="Select *" &
> " From pat" &
> " WHERE (pattype IN (" & Join(Parameters!pattype.Value, ", ") &
> ")) " &
> " AND (facility IN (" & Join(Parameters!facility.Value, ",
> ") & "))" &
> Iif(Parameters!fromdate.Value = ""," Order By pattype,facility", " AND
> (fromdate IN (" & Parameters!fromdate.Value & ")) Order By
> pattype,facility,fromdate")
> I'm still getting command text errors though. Can you offer any other
> thoughts? Perhaps elaborate on how you use a textbox for a resulting
> string (or is this useful after I get past my current problem?)? Or
> can you point me to other tutorials that get more involved with what I
> am trying to do? Books online don't really go deep enough.
> Thanks again for your help. I assure you I am rigorously working on
> this. Any help is once again greatly appreciated.
> Jay
>
>|||OK I think we're getting somewhere now...I hooked up the expression to
a textbox like you advised.
This works:
="Select facility, account, fromdate, thrudate, mednum, last_name,
pattype" & " From pat" &
" WHERE (pattype IN (" & Parameters!pattype.Value & ")) "
This does not work (#error is returned in the textbox):
="Select facility, account, fromdate, thrudate, mednum, last_name,
pattype" & " From pat" &
" WHERE (pattype IN (" & Join(Parameters!pattype.Value, ",") &
")) "
It appears to be choking on the join statement. I doublechecked the
syntax and I'm pretty sure I'm using it correctly...Do you see
anything wrong by any chance?
I'm going to shop around for an advanced manual I think. Books online
and the Osbourne SSRS manual doesn't go over this area very well at
all...
Thanks again for your help.|||Does the first syntax work or only works if you check a single value?
Also, just in case. I have in the past had problems with carriage returns.
Try putting it all on one line.
I use the Join(Parameters ...) syntax when I am showing the selected
parameters at the top of the report.
You got me curious. I did the following. I created a new report. I put a
single textbox on the report. I copied and pasted your expression which uses
the Join() below. Next I added a parameter called pattype, multi-select,
string and put a few values in.
It ran and it did as I suspected it would do. You end up with this:
Select facility, account, fromdate, thrudate, mednum, last_name, pattype
From pat WHERE (pattype IN (T1,T2,T3))
Note that this would work if the value type was integer but with a value
type of string this is invalid SQL. It needs single quotes around each
parameter.
I tried the one you say works below and it does not work of me. I get a
#Error.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178632816.621829.101750@.e51g2000hsg.googlegroups.com...
> OK I think we're getting somewhere now...I hooked up the expression to
> a textbox like you advised.
> This works:
> ="Select facility, account, fromdate, thrudate, mednum, last_name,
> pattype" & " From pat" &
> " WHERE (pattype IN (" & Parameters!pattype.Value & ")) "
> This does not work (#error is returned in the textbox):
> ="Select facility, account, fromdate, thrudate, mednum, last_name,
> pattype" & " From pat" &
> " WHERE (pattype IN (" & Join(Parameters!pattype.Value, ",") &
> ")) "
> It appears to be choking on the join statement. I doublechecked the
> syntax and I'm pretty sure I'm using it correctly...Do you see
> anything wrong by any chance?
> I'm going to shop around for an advanced manual I think. Books online
> and the Osbourne SSRS manual doesn't go over this area very well at
> all...
> Thanks again for your help.
>|||Trying again. I replied but looks like it didn't go through...
I think that's it (the single quotes around the string values). Now I
get a convert to int data type error when the column in the database
is of type char/string.
Is there an example of a formed statement that uses the Join method
with multi selects for strings? I tried [ Join("'" & value & "'",
",") ] but that didn't work. Perhaps I need to create a loop or load
an array?
Bruce, thank you...|||From user Jeje: sure in case of an array of string the code is different
something like:
"'" & Join(Parameters!deployment_id.Value, "', '") & "'"
single quote ' added in the join clause + single quote ' added before and
after the Join clause produce:'toto', 'tata', 'tutu'
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178802164.886275.281550@.y5g2000hsa.googlegroups.com...
> Trying again. I replied but looks like it didn't go through...
> I think that's it (the single quotes around the string values). Now I
> get a convert to int data type error when the column in the database
> is of type char/string.
> Is there an example of a formed statement that uses the Join method
> with multi selects for strings? I tried [ Join("'" & value & "'",
> ",") ] but that didn't work. Perhaps I need to create a loop or load
> an array?
> Bruce, thank you...
>|||Holy painful lesson...lol.
I got it:
="Select facility, account, fromdate, thrudate, mednum, last_name,
pattype" &
" From pat" &
" WHERE (pattype IN (" & "'" & Join(Parameters!
pattype.Value,"','") & "'" & ")) " &
" AND (facility IN (" & "'" & Join(Parameters!
facility.Value,"','") & "'" & ")) "
Man that made me feel so stupid. My mental block was the fact that I
needed to think from a perspective of VBScript writing SQL/T-SQL...
Well the good news is that now I can throw a few 'Iif' statements on
each 'WHERE/AND' and, along with SELECT DISTINCT hookups on the other
dropdowns, I can create ONE dynamic statement/report that the user can
do with as they please and report on whatever they want.
Bruce. Thank you for being there and seeing me through this. I
appreciate that.|||Glad you got it to work. Dynamic queries (and getting the single quotes
right) is a pain. If you ever use openquery it gets worse because you need
to double the amount of single quotes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1179330253.302418.191850@.n59g2000hsh.googlegroups.com...
> Holy painful lesson...lol.
> I got it:
> ="Select facility, account, fromdate, thrudate, mednum, last_name,
> pattype" &
> " From pat" &
> " WHERE (pattype IN (" & "'" & Join(Parameters!
> pattype.Value,"','") & "'" & ")) " &
> " AND (facility IN (" & "'" & Join(Parameters!
> facility.Value,"','") & "'" & ")) "
> Man that made me feel so stupid. My mental block was the fact that I
> needed to think from a perspective of VBScript writing SQL/T-SQL...
> Well the good news is that now I can throw a few 'Iif' statements on
> each 'WHERE/AND' and, along with SELECT DISTINCT hookups on the other
> dropdowns, I can create ONE dynamic statement/report that the user can
> do with as they please and report on whatever they want.
> Bruce. Thank you for being there and seeing me through this. I
> appreciate that.
>|||On May 16, 11:53 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Glad you got it to work. Dynamic queries (and getting the single quotes
> right) is a pain. If you ever use openquery it gets worse because you need
> to double the amount of single quotes.
Hey one last quick question if you don't mind and I'll stop bugging
you.
I found it once in SQL Server 2005 Books Online/Tutorials, but I put
it down and can't find it anymore. There was a section on how to call
a webservice from SSRS and get the XML back. Have you, by any chance,
come across this?
I even remember what it looked like too. You had to put the webservice
URL in the data source connection string and the XML namespace/wsdl
for the data set (I think) and some other stuff like that.
Thanks Bruce.|||Found it...
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/d23408e5-e65b-4f49-
a98f-234454d5d267.htm

dynamic columns in matrix

hi all,

i m using ssrs 2005. i want to generate a report which displays data according to the dataset returned.Now this dataset can return any number of columns.

does matrix use helps out in this case..

if anyone can really explain me on it or can even point to certain articles in this regard,that wudd be wonderful

thanks a ton...

hi all
m quite new to reporting services so may b it sounds easy for u champs but still ur replies wudd b appreciated..

thanks a ton ...

|||Hi,hilander:
Try to do this.
1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
2. Configure your ReportViewer to a LocalReport mode. This will allow you to
link yourdataset to a report at runtime by using ReportViewer.LocalReport methods or at disign level too.

Hopefully I help you.

|||

Hi,hilander:
We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

Dynamic Columns

Can I create columns dynamically? In other words, progammatically determine the columns to display based on what is returned by the dataset. I know that it is easy to just drag/drop items from my dataset but is there a way to do this programmatically.

You can define a table with all the possible columns, then decide whether to show the column based on the the value of the IsMissing property on Field. http://msdn2.microsoft.com/en-us/library/ms157274(SQL.90).aspx

The other alternative is to generate rdl dynamically based on the dataset.

Dynamic change of the column size and location of a matrix

I have a report that has a matrix. That matrix can have from 2 to 16 columns dependinging on the dataset result. Right now I am forced to place this matrix on the left side of the report and make a column layout pretty narrow. When dataset has more than 13 or so columns it looks OK, but when dataset has only two or three columns it looks weird with a matrix sitting in the left corner with two or three narrow columns and a lot of empty space to the right.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

Thank you,

Simon.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

No

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

No

Curently, the RS object model doesn't support manupaluting the report definition at runtime. Please take a moment to put these on the wish list for a future release on connect.microsoft.com

|||Is the feature available yet or is it still a no go. I really need to be able to change the width of columns and the height of textboxes dynamically. Has anyone been able to acomplish this?
|||

No go, as far as I know.

:-(

|||

The only way to do this that I know of is to re-write the RDL dynamically at run time with the specifications that you need for this run. Does this help at all? There are probably only a couple of changes that you would need to make in your case.

>L<

|||Yeah that's what I have done. When the user selects the report from my web app, I load the rdl file, make the necessary changes to the xml, re-deploy the report and then generate it. A bit of a pain but unfortunately the only work around at the moment.
|||

Hi Mark,

One can imagine that something similar would be necessary if dynamic width were built into the product.

Unfortunately it would pretty much take the same work *and* -- this is the kicker that may have taken it off the Katmai table for all we know -- it might be very difficult to get it right for every type of scenario that people might want to use it in.

I think it would be a great idea for Katmai or some future version to provide a hook that allowed a "swap event" during the report rendering process (there's a reason I call it that, although I know it sounds odd <s>).

The basic idea here is that the kinds of work we need to do at this point in the process would still be up to us, the product wouldn't do the work of adjusting the RDL, but it would offer a safe and consistent point at which to do that work. It would maybe hand the RDL as a stream or a loaded DOM object, we could make whatever adjustments we wanted, and then it would continue on its way.

Would you agree that this would provide some benefit, or do you see it as not sufficiently "automagical"?

>L<

|||I think that any improvement in this area would be beneficial however I would prefer for the process to be a bit more "automagical" as you put it.

Dynamic change of the column size and location of a matrix

I have a report that has a matrix. That matrix can have from 2 to 16 columns dependinging on the dataset result. Right now I am forced to place this matrix on the left side of the report and make a column layout pretty narrow. When dataset has more than 13 or so columns it looks OK, but when dataset has only two or three columns it looks weird with a matrix sitting in the left corner with two or three narrow columns and a lot of empty space to the right.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

Thank you,

Simon.

Is it possible programmatically change the width of the columns depending on their number in the dataset?

No

Is it possible to move the location of the matrix (horizontally) depending on the number of columns in the dataset?

No

Curently, the RS object model doesn't support manupaluting the report definition at runtime. Please take a moment to put these on the wish list for a future release on connect.microsoft.com

|||Is the feature available yet or is it still a no go. I really need to be able to change the width of columns and the height of textboxes dynamically. Has anyone been able to acomplish this?
|||

No go, as far as I know.

:-(

|||

The only way to do this that I know of is to re-write the RDL dynamically at run time with the specifications that you need for this run. Does this help at all? There are probably only a couple of changes that you would need to make in your case.

>L<

|||Yeah that's what I have done. When the user selects the report from my web app, I load the rdl file, make the necessary changes to the xml, re-deploy the report and then generate it. A bit of a pain but unfortunately the only work around at the moment.
|||

Hi Mark,

One can imagine that something similar would be necessary if dynamic width were built into the product.

Unfortunately it would pretty much take the same work *and* -- this is the kicker that may have taken it off the Katmai table for all we know -- it might be very difficult to get it right for every type of scenario that people might want to use it in.

I think it would be a great idea for Katmai or some future version to provide a hook that allowed a "swap event" during the report rendering process (there's a reason I call it that, although I know it sounds odd <s>).

The basic idea here is that the kinds of work we need to do at this point in the process would still be up to us, the product wouldn't do the work of adjusting the RDL, but it would offer a safe and consistent point at which to do that work. It would maybe hand the RDL as a stream or a loaded DOM object, we could make whatever adjustments we wanted, and then it would continue on its way.

Would you agree that this would provide some benefit, or do you see it as not sufficiently "automagical"?

>L<

|||I think that any improvement in this area would be beneficial however I would prefer for the process to be a bit more "automagical" as you put it.