Can I define the True/False text labels for a Boolean report param

SSRS 2005
Boolean report parameters are convenient to use.
However, the rendering of the "True"/"False" radio button labels is not
always appropriate. For example sometimes I would prefer the labels to be
"Enabled"/"Disbaled", "Yes"/"No", etc.
Is there anyway to define the text labels differently for each boolean
report parameter?
--
Chris, SSSIHi Chris,
As for this question, I think it is just like the "boolean parameter
represented with checkbox" thread you posted, so far the ReportViewer
control can not support such small granularity level customization.
BTW, if you do want to change the text, one way is use client-script as
I've mentioned in a former thread, but that's not quite flexible when
dealing with multiple different reports.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Can I create dynamic report parameters?

Hi all,

Is it possible to create a report with dynamic parameters or parameters dependant on other ones? I have the following scenario -

Parameter 1 - 'Select an Application' - List of applications that are available

Parameter 2 - 'Select a module' - This is a list of modules that needs to depend on Parameter 1. As a user selects different applications, the appropriate module list will be loaded.

Is this possible to do in SSRS?

Thanks,

Brian

Hi Brian,

Yes you can do that. Its called Cascaded parameters you need to write 2 store procedure. 1st one will be for "select an application" and 2nd one will be for "select a module" for the 2nd sp you need to pass the application id as a parameter and depending on the application Id modules will be fetched.

Once these SPs are done you need to set the report parameters make sure order of the parameters in the report parameter is correct.

Take a look at this example

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

I hope this helps

Thanks

Ashwini

|||

Hello Brian,

Yes, you can do this. Take a look at 'Cascading Parameters' in Books Online.

Hope this helps.

Jarret

sql

Can I add parameters to a report in the Microsoft Report Builder.

Hi,
I'm using SQL Server 2005 Reporting Services and Visual Studio 2005.
I'm just wondering if I can add parameters to a report with the Report
Builder or is this facility only available in the VS2005 report editor? If
it's possible what are the steps I need to follow?
Alternatively, assuming that I can't add parameters in the report
builder directly, I was wondering if parameters could be defined in a report
model and then after it's published the parameters could be included in the
Report Builder report using the model?
Thanks in advance,
Mark.Hi Mark
You can add parameters by adding a Filter.
When building the filter expression click on the operator in the expression
and choose "Prompt" this will generate a parameter. I guess this operator is
not available for all filtering expressions and depends on attribute
ValueSelection property.
Hope this helps,
Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning Iberoamericana
"Mark Hayes" <MarkHayes@.discussions.microsoft.com> escribió en el mensaje
news:75FEDA12-7594-4EB4-A035-F93C9A3E6979@.microsoft.com...
> Hi,
> I'm using SQL Server 2005 Reporting Services and Visual Studio 2005.
> I'm just wondering if I can add parameters to a report with the Report
> Builder or is this facility only available in the VS2005 report editor? If
> it's possible what are the steps I need to follow?
> Alternatively, assuming that I can't add parameters in the report
> builder directly, I was wondering if parameters could be defined in a
> report
> model and then after it's published the parameters could be included in
> the
> Report Builder report using the model?
> Thanks in advance,
> Mark.|||I read your response, I am trying to Add a filter or Parameter to a report. I
don't see any expression Builder, just a list of values? Which in my case has
18,000 rows. Where does one build an expression for a filter?
"Jordi Rambla" wrote:
> Hi Mark
> You can add parameters by adding a Filter.
> When building the filter expression click on the operator in the expression
> and choose "Prompt" this will generate a parameter. I guess this operator is
> not available for all filtering expressions and depends on attribute
> ValueSelection property.
> Hope this helps,
> Jordi Rambla
> SQL Server MVP (Reporting Services)
> SolidQualityLearning Iberoamericana
> "Mark Hayes" <MarkHayes@.discussions.microsoft.com> escribió en el mensaje
> news:75FEDA12-7594-4EB4-A035-F93C9A3E6979@.microsoft.com...
> > Hi,
> > I'm using SQL Server 2005 Reporting Services and Visual Studio 2005.
> > I'm just wondering if I can add parameters to a report with the Report
> > Builder or is this facility only available in the VS2005 report editor? If
> > it's possible what are the steps I need to follow?
> > Alternatively, assuming that I can't add parameters in the report
> > builder directly, I was wondering if parameters could be defined in a
> > report
> > model and then after it's published the parameters could be included in
> > the
> > Report Builder report using the model?
> >
> > Thanks in advance,
> > Mark.
>
>

Can drill through reports display parameters

I have a top level report in which I am select a particular run. The id for
that run is passed to a child report which uses the id from that run to
generate a list of possible values to generate the drill through report. My
problem is that I seem to have to set both values from the top level report
and then the second report does not show the parameters. If I browse
directly to the second report then I have the option of entering parameters.
Should I be able to see parameters when drill into a reportI forgot to mention that when I am in the designer the parameters are always
visible, but when I deploy to website and try to browse the report, that is
when they ar not visible.
"slumley" wrote:
> I have a top level report in which I am select a particular run. The id for
> that run is passed to a child report which uses the id from that run to
> generate a list of possible values to generate the drill through report. My
> problem is that I seem to have to set both values from the top level report
> and then the second report does not show the parameters. If I browse
> directly to the second report then I have the option of entering parameters.
> Should I be able to see parameters when drill into a report|||I have the same problem but only with the latest CTP (Sept), it worked fine
in the June version.
"slumley" wrote:
> I forgot to mention that when I am in the designer the parameters are always
> visible, but when I deploy to website and try to browse the report, that is
> when they ar not visible.
> "slumley" wrote:
> > I have a top level report in which I am select a particular run. The id for
> > that run is passed to a child report which uses the id from that run to
> > generate a list of possible values to generate the drill through report. My
> > problem is that I seem to have to set both values from the top level report
> > and then the second report does not show the parameters. If I browse
> > directly to the second report then I have the option of entering parameters.
> >
> > Should I be able to see parameters when drill into a report

Can groups be driven by parameters

I have a simple report grouped by Quarter (with quarterly totals) and
then by Month (with monthly totals).
I would like to give the user the option to select the grouping they
would like to see. If they select a 'Monthly' parameter, is there a way
to ignore the quarterly group?
Currently:
Month,year col1_total col2_total
Month,year col1_total col2_total
Month,year col1_total col2_total
Quarter col1_total col2_total
Month,year col1_total col2_total
Month,year col1_total col2_total
Quarter col1_total col2_total
Report total col1_total col2_total
I would like to not show the "Quarter" line if they select Monthly.
Can this be done with parameters?Michael wrote:
> I have a simple report grouped by Quarter (with quarterly totals) and
> then by Month (with monthly totals).
> I would like to give the user the option to select the grouping they
> would like to see. If they select a 'Monthly' parameter, is there a
> way to ignore the quarterly group?
> Currently:
> Month,year col1_total col2_total
> Month,year col1_total col2_total
> Month,year col1_total col2_total
> Quarter col1_total col2_total
> Month,year col1_total col2_total
> Month,year col1_total col2_total
> Quarter col1_total col2_total
> Report total col1_total col2_total
> I would like to not show the "Quarter" line if they select Monthly.
> Can this be done with parameters?
You can use an expression on the visibility of the group and reference
your parameter. If the parameter equals 'Monthly', set the visibility
to false, else set the visibility to true.

Can Code be used to define parameters in a subscription?

I am trying to create an email subscription that runs every Monday for the previous week. To do this I was trying to set the start date parameter to dateadd("d", -7, today()) and the end date parameter to dateadd("d", -1, today()). However, every time I change it to anything but a static date value, the screen refreshes and changes the code back to the default date.

I know I can set the defaults to the previous week in the report itself but I already have another subscription that runs the same report as MTD so the parameter defaults are set to that. The only other way I can think to accomplish this is to create a copy of the report with the only difference being parameter defaults (which I don't think is a good solution).

Any suggestions are welcome.

You said that you have the other report run for MTD. What do you mean by that?|||Sorry, Month To Date (MTD)|||Does anyone know if this is possible? I still have not found a solution.

Can a view have parameters?

Can a view have parameters?A view can not have parameters.
Maybe you can achieve whatever it is you try to achieve through a
table-valued user-defined function?
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Can a view have parameters?

For years I would have said, no it can't. But in the course of studying for
my cert exam, I checked out the following syntax:
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
What is (column [ ,...n ] ) if not parameters, and how are they to be used?
I've never seen an example, and there wasn't one in the BOL article. I tried
creating a vew with a parameter, but got syntax errors.Doh! It one to many columns for the view.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:E4F3733F-720F-47C6-8C19-C99DAA45FA8A@.microsoft.com...
> For years I would have said, no it can't. But in the course of studying
> for
> my cert exam, I checked out the following syntax:
> CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
> [ WITH <view_attribute> [ ,...n ] ]
> AS select_statement [ ; ]
> [ WITH CHECK OPTION ]
> What is (column [ ,...n ] ) if not parameters, and how are they to be
> used?
> I've never seen an example, and there wasn't one in the BOL article. I
> tried
> creating a vew with a parameter, but got syntax errors.
>|||Never mind, I just figured it out. The column list is just another way to
set up names for the columns in the view.
CREATE VIEW Test (Vendor, Company)
AS SELECT VendID, VendName FROM Vendor
is the same as
CREATE VIEW Test
AS SELECT VendID AS Vendor, VendName AS Company FROM Vendor
which is information that I will probably never need in the real world.
"Bev Kaufman" wrote:
> For years I would have said, no it can't. But in the course of studying for
> my cert exam, I checked out the following syntax:
> CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
> [ WITH <view_attribute> [ ,...n ] ]
> AS select_statement [ ; ]
> [ WITH CHECK OPTION ]
> What is (column [ ,...n ] ) if not parameters, and how are they to be used?
> I've never seen an example, and there wasn't one in the BOL article. I tried
> creating a vew with a parameter, but got syntax errors.
>|||A parameterized view is called a "user defined inline table function".
Insert / update / delete over UDFs requires coding relevant triggers.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:E4F3733F-720F-47C6-8C19-C99DAA45FA8A@.microsoft.com...
> For years I would have said, no it can't. But in the course of studying
> for
> my cert exam, I checked out the following syntax:
> CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
> [ WITH <view_attribute> [ ,...n ] ]
> AS select_statement [ ; ]
> [ WITH CHECK OPTION ]
> What is (column [ ,...n ] ) if not parameters, and how are they to be
> used?
> I've never seen an example, and there wasn't one in the BOL article. I
> tried
> creating a vew with a parameter, but got syntax errors.
>

Can a Snapshot report be created programmatically?

Can I create a Snapshot report with parameters programmatically in RS 2000 or RS 2005? If this can be done what is the command for it? Thanks.

Yes. The report has to be set to Snapshot Execution and has to have default values. The SOAP method to call is UpdateSnapshot.

If you want to create a history snapshot then you can call CreateReportHistorySnapshot.

You can not set the parameters when calling these parameters. If you want the parameters different then you must first set the report parameters then call one of the above methods.

-Daniel

|||

Hi Daniel,

Thanks for the tip... you put me in the right direction. Now, how do I change the parameters before getting a Snapshot? Can I save the result in my own table instead of the History table?

|||

You need to call SetReportParameters to set the parameters. The history snapshot will be saved to the History table. Can you explain why you would want to save it elsewhere?

-Daniel

|||I was planning to use my own database to save the snapshot for ease of maintenance and backup, besides, using the SnapshotDate and converting it to another format before using it is extar work! I guess I can use the RS History Table for now. One thing though, when I get a snapshot, the date in History table doesn't match with the snapshot date! Any idea why? Thanks.|||

I assume you mean the snapshot datetime stamp used as URL access parameter to render history snapshot. The snapshot datetime value is always based on UTC time which is independent of your local timezone, while the date in the internal catalog tables depends on your server timezone settings.

-- Robert

can a report read parameters from POST as opposed to GET?

My scenarios is that I have to have an ADHOC report page, which will
allow the user to select various attributes, and I have to then create
the SQL clause on the fly, based off these selected attributes. I then
have to have the report in reporting services run off of that generated
SQL clause.
I have two ideas how I can do this, but I am looking for the most
efficient.
My first idea, and the one I assume I will have to go with is this.
When the user clicks the run button, the SQL clause will be built based
on their selections. That SQL clause will then get passed into a
stored proc which will act like a "middle man", this "middle man"
procedure will store the clause into a "holding" table. The Reporting
Services report will then have to run, use a procedure that reads from
this "holding" table and uses the SQL clause, executes it, and if
successful, deletes it from the holding table, ready for the next run
of the page.
The other approach I am thinking of, but am not sure will work is
basically passing the generated SQL query to the report as a POST
variable. I am not sure if this will work or not since I haven't tried
it before.
does anyone know if you can pass in POST variables as opposed to GET
variables when calling a report as a url?answered my own question by searching through the help (which I should
have done in the first place). So far it looks like I can POST the SQL
query that I want....but the help file mentions something about a 2000
char limit, for even POST...anyone have any experience with a similar
situation?

Can a report list available reports? - revisited

We all know its possible to get all our reports w/ .listchildren("/", true)
but how can get properties for that report?
parameters, security info, ect...
?
is it .Role or .Policy?
thanksStandard report properties are listed through ListChildren(). Extended
properties can be retrieved via GetProperties().
Parameter information is available through GetReportParameters()
Security info via GetPolicies().
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"mgonzales3" <mgonzales3@.discussions.microsoft.com> wrote in message
news:D8BFCEEF-ECAB-4BB6-995A-F020BCE2C9A4@.microsoft.com...
> We all know its possible to get all our reports w/ .listchildren("/",
true)
> but how can get properties for that report?
> parameters, security info, ect...
> ?
> is it .Role or .Policy?
> thanks
>

can 2 people view the same report using different parameters at the same time?

Hi, I am using sql server 2005 entreprise edition. I am just wondering when I have a report with parameters and the report uses stored procedure to retrieve the result set for use in the report, if two people from different places are trying to view the report at the same time, and each of them use different set of parameters.
Will they be able to view their report correctly?
Yes, each user will have different session and in each session report will be executes with user's parameters and users will see different report outputs.|||Hi Alexandre,
Sorry to bother you, this might sound stupid but if different users generate the report that will pass the parameters to a stored proc in master, then run different actions stored in the stored proc to return the result set tot he report, will it affect the report output?
Thanks
|||

When two different users run report on report server or stored proc on sql server, those runs will be in two different contexts, so unless you have some shared global variables in your report or stored procs, the executions will be independant.

By shared global variables I mean something like global variables in VB.NEt code embedded or called from report expressions. In the case of SQL stored procs this could be a global temp table used to store intermediate computation results. If you have nothing like this, then you should be fine, two executions will not collide.

|||

Is there a way to store temp data in user's session variable in report server or is there a way to store user specific data in report server?

For example, I have 3 textboxes (1, 2, 3). Textbox 1 allow user to search for country (say type in A), then the country search result will be display into textbox 2. Now, user can choose which country they want from textbox 2 by ticking the country multi select dropdown and the selected country will be display in textbox 3.

Now, if user go back to textbox 1 and search for another country (say type in B), textbox 2 will be refresh and shows list of country starts with B with previous selected country still in textbox 3. How can I achieve that?

Can I access to the request object or the server object in report server so I can save/store the selected country into user session/request object?

can 2 people view the same report using different parameters at the same time?

Hi, I am using sql server 2005 entreprise edition. I am just wondering when I have a report with parameters and the report uses stored procedure to retrieve the result set for use in the report, if two people from different places are trying to view the report at the same time, and each of them use different set of parameters.
Will they be able to view their report correctly?
Yes, each user will have different session and in each session report will be executes with user's parameters and users will see different report outputs.|||Hi Alexandre,
Sorry to bother you, this might sound stupid but if different users generate the report that will pass the parameters to a stored proc in master, then run different actions stored in the stored proc to return the result set tot he report, will it affect the report output?
Thanks
|||

When two different users run report on report server or stored proc on sql server, those runs will be in two different contexts, so unless you have some shared global variables in your report or stored procs, the executions will be independant.

By shared global variables I mean something like global variables in VB.NEt code embedded or called from report expressions. In the case of SQL stored procs this could be a global temp table used to store intermediate computation results. If you have nothing like this, then you should be fine, two executions will not collide.

|||

Is there a way to store temp data in user's session variable in report server or is there a way to store user specific data in report server?

For example, I have 3 textboxes (1, 2, 3). Textbox 1 allow user to search for country (say type in A), then the country search result will be display into textbox 2. Now, user can choose which country they want from textbox 2 by ticking the country multi select dropdown and the selected country will be display in textbox 3.

Now, if user go back to textbox 1 and search for another country (say type in B), textbox 2 will be refresh and shows list of country starts with B with previous selected country still in textbox 3. How can I achieve that?

Can I access to the request object or the server object in report server so I can save/store the selected country into user session/request object?

Calling the same report in a batch (rs2005) from aspx page

Hi,
what would be the best way to call the same report with diffrent
parameters.
right now I am calling the RenderInvoice which is called in a loop
(view code below)
is there a better way to do this, like a batch process?
this works, and it does 50 reports in about one minute, I wonder if I
can make it go faster.
or it's just an issue of computer hardware.
Thanks.
protected Byte[] RenderInvoice(ReportExecutionService rs,string
shipmentId)
{
ParameterValue[] parameters = new ParameterValue[2];
parameters[0] = new ReportExecutionWS.ParameterValue();
parameters[0].Name = "ShipmentId";
parameters[0].Value = shipmentId;
parameters[1] = new ReportExecutionWS.ParameterValue();
parameters[1].Name = "ShowLogo";
parameters[1].Value = "True";
// set report parameters
rs.SetExecutionParameters(parameters, "en-us");
// set rendering info
Byte[] result = null;
String format = "PDF";
String devInfo = "<DeviceInfo><Toolbar>False</Toolbar></
DeviceInfo>";
String extension = "";
String encoding = "";
String mimeType = "";
Warning[] warnings = null;
string[] streamIDs = null;
result = rs.Render(format, devInfo, out extension, out
encoding, out mimeType, out warnings, out streamIDs);
return result;
}You are running the reports one after another. A minute for 50 reports
sounds pretty good to me. RS could do these in parallel but your app would
need to do request the rendering asynchronously. My suggestion is to leave
it alone. I don't think a whole lot of complexity to speed it up more is
worth the effort.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<rperetz@.gmail.com> wrote in message
news:1182807919.166551.199320@.g4g2000hsf.googlegroups.com...
> Hi,
> what would be the best way to call the same report with diffrent
> parameters.
> right now I am calling the RenderInvoice which is called in a loop
> (view code below)
> is there a better way to do this, like a batch process?
> this works, and it does 50 reports in about one minute, I wonder if I
> can make it go faster.
> or it's just an issue of computer hardware.
> Thanks.
>
> protected Byte[] RenderInvoice(ReportExecutionService rs,string
> shipmentId)
> {
> ParameterValue[] parameters = new ParameterValue[2];
> parameters[0] = new ReportExecutionWS.ParameterValue();
> parameters[0].Name = "ShipmentId";
> parameters[0].Value = shipmentId;
> parameters[1] = new ReportExecutionWS.ParameterValue();
> parameters[1].Name = "ShowLogo";
> parameters[1].Value = "True";
> // set report parameters
> rs.SetExecutionParameters(parameters, "en-us");
> // set rendering info
> Byte[] result = null;
> String format = "PDF";
> String devInfo = "<DeviceInfo><Toolbar>False</Toolbar></
> DeviceInfo>";
> String extension = "";
> String encoding = "";
> String mimeType = "";
> Warning[] warnings = null;
> string[] streamIDs = null;
>
> result = rs.Render(format, devInfo, out extension, out
> encoding, out mimeType, out warnings, out streamIDs);
> return result;
> }
>

Calling stored procedures from C#

Hi All... I'm calling a stored procedure from C#. I'm sending it input parameters as follows:

SqlCommand c =newSqlCommand("AddAuthor", myConnection);
c.CommandType =CommandType.StoredProcedure;

c.Parameters.Add(newSqlParameter("@.LastName",SqlDbType.VarChar, 100));
c.Parameters["@.LastName"].Value ="Last";

c.Parameters.Add(newSqlParameter("@.FirstName",SqlDbType.VarChar, 100));
c.Parameters["@.FirstName"].Value ="First";

In my opinion, that works nice - and it's easy. The reference I'm using says I can also specify "output" parameters - instead of supplying a value, one needs to change a property called direction.

But my stored procedure doesnt return any output parameters per se, but it does return a value. That is the last statement in the stored procedure is "RETURN@.@.IDENTITY" - it returns the identity field after an INSERT... So how do I get that value back in my C# code?

Thanks for the help in advance. Happy 4th to all! : ) -- Curt

If you do

SELECT @.@.IDENTITY in your stored procedure, you can get the value with

c.ExecuteScalar();

I do not know if this will work with the RETURN statement,though

|||

To use an output parameter, you need to add an output parameter to your SqlCommand prior to execution, like:

SqlParameter output =new SqlParameter("@.Return_Value", SqlDbType.VarChar, 100);output.Direction = ParameterDirection.Output;c.Parameters.Add(output);

When you execute your SP, you need to set the value. Then you can get the new value from the parameter.

This article is based around using a SqlDataSource, but it has details on the SP updateshttp://aspnet.4guysfromrolla.com/articles/050207-1.aspx.

I'd also take a look herehttp://msdn2.microsoft.com/en-gb/library/ms190315.aspx at the difference between @.@.IDENTITY and SCOPE_IDENTITY (my guess is SCOPE_IDENTITY is what you really want).

Hope that helps.

Aaron

|||

dt...

Yeah, when I added the "SELECT" to my SPROC, it worked fine - thanks for the tip. And yeah, as you suspected it didn't work for the "RETURN" - I had to add the "SELECT" to my SPROC. No problem though...

Athens, eh? Well, that goes back a couple of thousand years, doesn't it? ; ) Thanks for the help!

Curt

|||

however, if you want to get multiple values from your database you should use output parameters, just like agolden described. Moreover, if you use SQL Server 2005, you should use SCOPE_IDENTITY, as agolden also stated.