Dynamic graphs in SSRS

Hello,

Im building a horizontal bar graph, but according to the parameters it could have a different number of bars, how can i make the SSRS resize the graph?

Or how can i make ssrs to give the same size as a table right next to it?

Thank you

You may need to create a couple of separate graphs with different sizes and show-hide based on # of rows in the dataset.

There is an article here which may also help, though it is more for working inside the chart rather than sizing the object itself. http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx#maxminvalues

A more flexible approach may be available in Dundas Charts for Reporting Services.

More info here:
http://msdn2.microsoft.com/en-us/library/aa964128.aspx

cheers,
Andrew

Dynamic Filter Operator

Here is what I am trying to do in SSRS 2005.

Setting up filters based on parameters with an expression like this:

=Iif(Parameters!Company.Value = "", "", Fields!company.Value)

In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter?

Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.

Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.

How can I do this?

Thanks in advance for your help.

You indicate you have a parameter named "Company"

first:
Create a parameter named "operator" and give it values "equals" and "like"

label value

equals equals
like like

Set the default value if you choose.

then:
Create a parameter named "filter" and leave it blank

Open the "table properties" and select "filter"

1st expression -
for the filter expression like:
=Iif(Parameters!Operator.Value = "equals", "", Fields!company.Value))
for the operator:
select the "like" operator
for the value:
=Iif(Parameters!Operator.Value = "equals", "", Switch(Parameters!Filter.Value = Parameters!Filter.Value, Parameters!Filter.Value & "*", Parameters!Filter.Value = nothing, "*"))

2nd expression -
for the filter expression equals:
=Iif(Parameters!Operator.Value = "like", "", Fields!company.Value))
for the operator:
select the "=" operator
for the value:
=Iif(Parameters!Operator.Value = "like", "", Parameters!Filter.Value)

This only covers one data field so when you select "like" you can enter the leter "A" in the filter parameter and all fields that start with "A" will be returned.
However if you select "equals" you need to know exactly what to type in other wise a dropdown would be great here.
If you leave the filter parameter blank and select "like" it will return all the data.
The options are endless. . .

|||

That is exactly what I was looking for!

Thanks very much

Dynamic Filter Operator

Here is what I am trying to do in SSRS 2005.

Setting up filters based on parameters with an expression like this:

=Iif(Parameters!Company.Value = "", "", Fields!company.Value)

In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter?

Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.

Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.

How can I do this?

Thanks in advance for your help.

You indicate you have a parameter named "Company"

first:
Create a parameter named "operator" and give it values "equals" and "like"

label value

equals equals
like like

Set the default value if you choose.

then:
Create a parameter named "filter" and leave it blank

Open the "table properties" and select "filter"

1st expression -
for the filter expression like:
=Iif(Parameters!Operator.Value = "equals", "", Fields!company.Value))
for the operator:
select the "like" operator
for the value:
=Iif(Parameters!Operator.Value = "equals", "", Switch(Parameters!Filter.Value = Parameters!Filter.Value, Parameters!Filter.Value & "*", Parameters!Filter.Value = nothing, "*"))

2nd expression -
for the filter expression equals:
=Iif(Parameters!Operator.Value = "like", "", Fields!company.Value))
for the operator:
select the "=" operator
for the value:
=Iif(Parameters!Operator.Value = "like", "", Parameters!Filter.Value)

This only covers one data field so when you select "like" you can enter the leter "A" in the filter parameter and all fields that start with "A" will be returned.
However if you select "equals" you need to know exactly what to type in other wise a dropdown would be great here.
If you leave the filter parameter blank and select "like" it will return all the data.
The options are endless. . .

|||

That is exactly what I was looking for!

Thanks very much

Dynamic default value for parameter in OLAP report

I have a basic SSRS report against an SSAS database with a "start date" parameter. I want to set the default value of that date parameter to Today's date. What is the easiest way to do this? I have no problem doing it against a relational source, just haven't done it against an OLAP source.

Thanks for any ideas.

-Josh R.

At parameter properties page in section "Default values" check radio button "Non-queried" and then at Expression box write:

=Today

Dynamic Data/Groups/Page breaks

I am absolutely new to SSRS (about a day into it). I am trying to figure out
the reporting methodology to use for building custom reports and would really
like some suggestions on the reporting architecture that I should use.
The base functionality is
1) Provide a c# UI to allow end-user to set report options (described below).
2) Use a stored procedure to generate dynamic sql based on user selection in
(1).
2) Show the report in the report viewer control (for now, we might develop
our own custom report viewer control later).
Almost all our report options are highly dynamic in nature. These include
ability to include/exclude some data, dynamic grouping on certain data,
dynamic page breaks as requested by the user and a whole lot more.
The Report Designer seems almost like a non-starter for us, based on our
requirements.
From what I've read so far, the best option seems to be to dynamically
generate the RDL, from our c# application, to be used in our reports. This
means we won't have to publish our reports to the ReportServer.
Is this the best approach based on the above requirements? Are there any
other options available? Also, what might be the best resources for dynamic
RDL generation on the web or books?
Any inputs will be greatly appreciated.
Thanks,
NaveenOn Feb 21, 11:06 am, Naveen <Nav...@.discussions.microsoft.com> wrote:
> I am absolutely new to SSRS (about a day into it). I am trying to figure out
> the reporting methodology to use for building custom reports and would really
> like some suggestions on the reporting architecture that I should use.
> The base functionality is
> 1) Provide a c# UI to allow end-user to set report options (described below).
> 2) Use a stored procedure to generate dynamic sql based on user selection in
> (1).
> 2) Show the report in the report viewer control (for now, we might develop
> our own custom report viewer control later).
> Almost all our report options are highly dynamic in nature. These include
> ability to include/exclude some data, dynamic grouping on certain data,
> dynamic page breaks as requested by the user and a whole lot more.
> The Report Designer seems almost like a non-starter for us, based on our
> requirements.
> From what I've read so far, the best option seems to be to dynamically
> generate the RDL, from our c# application, to be used in our reports. This
> means we won't have to publish our reports to the ReportServer.
> Is this the best approach based on the above requirements? Are there any
> other options available? Also, what might be the best resources for dynamic
> RDL generation on the web or books?
> Any inputs will be greatly appreciated.
> Thanks,
> Naveen
I would have to say that dynamically generated RDLs via C# would
probably be the best route to take. Just an FYI, you can dynamically
control data (i.e., filtering, including, excluding data) w/a
combination of report parameters tied to report expressions. Hope this
helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I just about discovered that using expressions might be worth a try before
attempting to generate RDL dynamically. Your response seems to confirm that.
If that doesn't work, maybe dynamic RDL generation would be the way to go.
Thanks for your reply.
Naveen
"EMartinez" wrote:
> On Feb 21, 11:06 am, Naveen <Nav...@.discussions.microsoft.com> wrote:
> > I am absolutely new to SSRS (about a day into it). I am trying to figure out
> > the reporting methodology to use for building custom reports and would really
> > like some suggestions on the reporting architecture that I should use.
> >
> > The base functionality is
> > 1) Provide a c# UI to allow end-user to set report options (described below).
> > 2) Use a stored procedure to generate dynamic sql based on user selection in
> > (1).
> > 2) Show the report in the report viewer control (for now, we might develop
> > our own custom report viewer control later).
> >
> > Almost all our report options are highly dynamic in nature. These include
> > ability to include/exclude some data, dynamic grouping on certain data,
> > dynamic page breaks as requested by the user and a whole lot more.
> >
> > The Report Designer seems almost like a non-starter for us, based on our
> > requirements.
> >
> > From what I've read so far, the best option seems to be to dynamically
> > generate the RDL, from our c# application, to be used in our reports. This
> > means we won't have to publish our reports to the ReportServer.
> >
> > Is this the best approach based on the above requirements? Are there any
> > other options available? Also, what might be the best resources for dynamic
> > RDL generation on the web or books?
> >
> > Any inputs will be greatly appreciated.
> >
> > Thanks,
> > Naveen
>
> I would have to say that dynamically generated RDLs via C# would
> probably be the best route to take. Just an FYI, you can dynamically
> control data (i.e., filtering, including, excluding data) w/a
> combination of report parameters tied to report expressions. Hope this
> helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>

Dynamic connection string problem in SSRS2005

Hi, I'm a newbie to SSRS, and was wondering if anybody can shed light on a problem I have. I have a report which every client uses, but each client's data is held in its own database. Rather than create many reports, is it possible to create 1 report, which all can use, passing in the different datasource? I was thinking of a hidden parameter, passed by URL. Or maybe using the report viewer control in VS2005. Can anybody please help?

Thanks

Dan

Yes, private data sources can have expression-based connection strings. Please check the following threads:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1008518&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1306007&SiteID=1

|||

Thats great, thanks. I have used a report viewer, pointing to a report on a server, with a dynamic connection string.

One more question though, I am connecting to a secure server, and need to provide a login, eg sql_user, pword. Where / how do I provide this in the code / url?

Thanks

Dan

|||How would you collect the credentials? If the user enters them before running the report you have no other way but passing them as parameters to the report (something you should avoid). If they are known values, you can save them in the Report Server web.config file as the ExpressionBasedConnection report demonstrates.|||

Oh, ok I'll try that tomorrow.

Thanks Teo.

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!