How to display only distinct values/labels in a report parameter drop down?
The values are generated from the main query of the report. Thx. JLYou should have a dataset that is specifically for your report parameter. As
a matter of fact, you have it a little reversed. The report parameters
should be used to limit the query. If you are getting the data and then
using the report parameters to filter the report, you should re-evaluate. In
most cases you should limit the data coming over using query parameters
mapped to report parameters. If you filter the data and the data is of any
significant size you will have performance problems.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CCDA1621-CE1A-4790-8BF7-447456ABB794@.microsoft.com...
> How to display only distinct values/labels in a report parameter drop
> down?
> The values are generated from the main query of the report. Thx. JL|||It's very helpful. That really enlightens me. Now I think I have a lot of
changes to make. Thx. JL
"Bruce L-C [MVP]" wrote:
> You should have a dataset that is specifically for your report parameter. As
> a matter of fact, you have it a little reversed. The report parameters
> should be used to limit the query. If you are getting the data and then
> using the report parameters to filter the report, you should re-evaluate. In
> most cases you should limit the data coming over using query parameters
> mapped to report parameters. If you filter the data and the data is of any
> significant size you will have performance problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "JL" <JL@.discussions.microsoft.com> wrote in message
> news:CCDA1621-CE1A-4790-8BF7-447456ABB794@.microsoft.com...
> > How to display only distinct values/labels in a report parameter drop
> > down?
> > The values are generated from the main query of the report. Thx. JL
>
>
Showing posts with label parameter. Show all posts
Showing posts with label parameter. Show all posts
Sunday, March 25, 2012
distinct mvp
hi all,
i use a query that returns a set of projects and customers for my
report. i am using a multi value parameter to filter the projects in
the data. because the grouping is done in the report itself, when i
try to set the mvp from my query each project apears multiple times on
the dropdown list. is there a way to get the distinct projects from
the query? - to get each project to apear once?.
i dont want to create another dataset to get the distinct projects
becuase the query is quite heavy.
or maybe is there a way to create another dataset to query from my
main dataset?
thanks in advance
offaYou can't do a query on an existing dataset. For my parameter lists I have
dedicated datasets. Perhaps if all the query is doing is getting is the
distinct projects it won't be a compute intensive query. Also, you might
want to see what sort of index you have on the table.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<offa23@.hotmail.com> wrote in message
news:1188265077.769587.273140@.k79g2000hse.googlegroups.com...
> hi all,
> i use a query that returns a set of projects and customers for my
> report. i am using a multi value parameter to filter the projects in
> the data. because the grouping is done in the report itself, when i
> try to set the mvp from my query each project apears multiple times on
> the dropdown list. is there a way to get the distinct projects from
> the query? - to get each project to apear once?.
> i dont want to create another dataset to get the distinct projects
> becuase the query is quite heavy.
> or maybe is there a way to create another dataset to query from my
> main dataset?
> thanks in advance
> offa
>
i use a query that returns a set of projects and customers for my
report. i am using a multi value parameter to filter the projects in
the data. because the grouping is done in the report itself, when i
try to set the mvp from my query each project apears multiple times on
the dropdown list. is there a way to get the distinct projects from
the query? - to get each project to apear once?.
i dont want to create another dataset to get the distinct projects
becuase the query is quite heavy.
or maybe is there a way to create another dataset to query from my
main dataset?
thanks in advance
offaYou can't do a query on an existing dataset. For my parameter lists I have
dedicated datasets. Perhaps if all the query is doing is getting is the
distinct projects it won't be a compute intensive query. Also, you might
want to see what sort of index you have on the table.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<offa23@.hotmail.com> wrote in message
news:1188265077.769587.273140@.k79g2000hse.googlegroups.com...
> hi all,
> i use a query that returns a set of projects and customers for my
> report. i am using a multi value parameter to filter the projects in
> the data. because the grouping is done in the report itself, when i
> try to set the mvp from my query each project apears multiple times on
> the dropdown list. is there a way to get the distinct projects from
> the query? - to get each project to apear once?.
> i dont want to create another dataset to get the distinct projects
> becuase the query is quite heavy.
> or maybe is there a way to create another dataset to query from my
> main dataset?
> thanks in advance
> offa
>
Wednesday, March 21, 2012
Disregard null parameter in WHERE clause
I have a problem optionally using a parameter to query a second key
column in an outer joined table:
DROP TABLE Sub;
DROP TABLE Main;
CREATE TABLE Main (
main_key_col INTEGER NOT NULL PRIMARY KEY,
main_data_col VARCHAR(15) NOT NULL
)'
CREATE TABLE Sub (
main_key_col INTEGER NOT NULL
REFERENCES Main (main_key_col),
sub_key_col INTEGER NOT NULL,
PRIMARY KEY (main_key_col, sub_key_col),
sub_data_col VARCHAR(15) NOT NULL
);
INSERT INTO Main VALUES (1,'Ford Model T');
INSERT INTO Main VALUES (2,'Ferrari GTB');
INSERT INTO Sub VALUES (2,1,'Red');
INSERT INTO Sub VALUES (2,2,'Yellow');
INSERT INTO Sub VALUES (2,3,'Silver');
To return the full 'denormalized' set:
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
Now I want to use two parameters for the respective key columns with
the sub_key_col parameter 'optional', meaning if it's NULL it is not
used in the WHERE clause. In the past I've got away with a trick like:
Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
but, because of the outer join, sub_key_col can be null and NULL = NULL
removes the row, of course.
I have a solution but it isn't very satisfactory:
DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
WHERE Main.main_key_col = @.main_key_col
AND
CASE WHEN @.sub_key_col IS NULL THEN 1
WHEN Sub.sub_key_col = @.sub_key_col THEN 1
ELSE 0 END = 1
Is there a better way?
Thank you.See if this article on Dynamic Search Conditions by Erland helps:
http://www.sommarskog.se/dyn-search.html
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<decland@.petml.com> wrote in message
news:1118313681.656069.190380@.g14g2000cwa.googlegroups.com...
I have a problem optionally using a parameter to query a second key
column in an outer joined table:
DROP TABLE Sub;
DROP TABLE Main;
CREATE TABLE Main (
main_key_col INTEGER NOT NULL PRIMARY KEY,
main_data_col VARCHAR(15) NOT NULL
)'
CREATE TABLE Sub (
main_key_col INTEGER NOT NULL
REFERENCES Main (main_key_col),
sub_key_col INTEGER NOT NULL,
PRIMARY KEY (main_key_col, sub_key_col),
sub_data_col VARCHAR(15) NOT NULL
);
INSERT INTO Main VALUES (1,'Ford Model T');
INSERT INTO Main VALUES (2,'Ferrari GTB');
INSERT INTO Sub VALUES (2,1,'Red');
INSERT INTO Sub VALUES (2,2,'Yellow');
INSERT INTO Sub VALUES (2,3,'Silver');
To return the full 'denormalized' set:
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
Now I want to use two parameters for the respective key columns with
the sub_key_col parameter 'optional', meaning if it's NULL it is not
used in the WHERE clause. In the past I've got away with a trick like:
Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
but, because of the outer join, sub_key_col can be null and NULL = NULL
removes the row, of course.
I have a solution but it isn't very satisfactory:
DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
WHERE Main.main_key_col = @.main_key_col
AND
CASE WHEN @.sub_key_col IS NULL THEN 1
WHEN Sub.sub_key_col = @.sub_key_col THEN 1
ELSE 0 END = 1
Is there a better way?
Thank you.|||DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main
LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
AND (Sub.sub_key_col = @.sub_key_col OR @.sub_key_col IS NULL)
WHERE Main.main_key_col = @.main_key_col
Jacco Schalkwijk
SQL Server MVP
<decland@.petml.com> wrote in message
news:1118313681.656069.190380@.g14g2000cwa.googlegroups.com...
>I have a problem optionally using a parameter to query a second key
> column in an outer joined table:
> DROP TABLE Sub;
> DROP TABLE Main;
> CREATE TABLE Main (
> main_key_col INTEGER NOT NULL PRIMARY KEY,
> main_data_col VARCHAR(15) NOT NULL
> )'
> CREATE TABLE Sub (
> main_key_col INTEGER NOT NULL
> REFERENCES Main (main_key_col),
> sub_key_col INTEGER NOT NULL,
> PRIMARY KEY (main_key_col, sub_key_col),
> sub_data_col VARCHAR(15) NOT NULL
> );
> INSERT INTO Main VALUES (1,'Ford Model T');
> INSERT INTO Main VALUES (2,'Ferrari GTB');
> INSERT INTO Sub VALUES (2,1,'Red');
> INSERT INTO Sub VALUES (2,2,'Yellow');
> INSERT INTO Sub VALUES (2,3,'Silver');
> To return the full 'denormalized' set:
> SELECT Main.main_key_col, Main.main_data_col,
> Sub.sub_key_col, Sub.sub_data_col
> FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
> Now I want to use two parameters for the respective key columns with
> the sub_key_col parameter 'optional', meaning if it's NULL it is not
> used in the WHERE clause. In the past I've got away with a trick like:
> Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
> but, because of the outer join, sub_key_col can be null and NULL = NULL
> removes the row, of course.
> I have a solution but it isn't very satisfactory:
> DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
> SET @.main_key_col = 2
> SET @.sub_key_col = NULL
> SELECT Main.main_key_col, Main.main_data_col,
> Sub.sub_key_col, Sub.sub_data_col
> FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
> WHERE Main.main_key_col = @.main_key_col
> AND
> CASE WHEN @.sub_key_col IS NULL THEN 1
> WHEN Sub.sub_key_col = @.sub_key_col THEN 1
> ELSE 0 END = 1
> Is there a better way?
> Thank you.
>|||COALESCE (Sub.sub_key_col, '?') = COALESCE(@.sub_key_col,
Sub.sub_key_col, '?')|||--CELKO-- wrote:
> COALESCE (Sub.sub_key_col, '?') = COALESCE(@.sub_key_col,
> Sub.sub_key_col, '?')
Now this I like because I get to use COALESCE after all! I'm now off to
order 'SQL Programming Style' <g>
Thanks everyone.
column in an outer joined table:
DROP TABLE Sub;
DROP TABLE Main;
CREATE TABLE Main (
main_key_col INTEGER NOT NULL PRIMARY KEY,
main_data_col VARCHAR(15) NOT NULL
)'
CREATE TABLE Sub (
main_key_col INTEGER NOT NULL
REFERENCES Main (main_key_col),
sub_key_col INTEGER NOT NULL,
PRIMARY KEY (main_key_col, sub_key_col),
sub_data_col VARCHAR(15) NOT NULL
);
INSERT INTO Main VALUES (1,'Ford Model T');
INSERT INTO Main VALUES (2,'Ferrari GTB');
INSERT INTO Sub VALUES (2,1,'Red');
INSERT INTO Sub VALUES (2,2,'Yellow');
INSERT INTO Sub VALUES (2,3,'Silver');
To return the full 'denormalized' set:
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
Now I want to use two parameters for the respective key columns with
the sub_key_col parameter 'optional', meaning if it's NULL it is not
used in the WHERE clause. In the past I've got away with a trick like:
Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
but, because of the outer join, sub_key_col can be null and NULL = NULL
removes the row, of course.
I have a solution but it isn't very satisfactory:
DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
WHERE Main.main_key_col = @.main_key_col
AND
CASE WHEN @.sub_key_col IS NULL THEN 1
WHEN Sub.sub_key_col = @.sub_key_col THEN 1
ELSE 0 END = 1
Is there a better way?
Thank you.See if this article on Dynamic Search Conditions by Erland helps:
http://www.sommarskog.se/dyn-search.html
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<decland@.petml.com> wrote in message
news:1118313681.656069.190380@.g14g2000cwa.googlegroups.com...
I have a problem optionally using a parameter to query a second key
column in an outer joined table:
DROP TABLE Sub;
DROP TABLE Main;
CREATE TABLE Main (
main_key_col INTEGER NOT NULL PRIMARY KEY,
main_data_col VARCHAR(15) NOT NULL
)'
CREATE TABLE Sub (
main_key_col INTEGER NOT NULL
REFERENCES Main (main_key_col),
sub_key_col INTEGER NOT NULL,
PRIMARY KEY (main_key_col, sub_key_col),
sub_data_col VARCHAR(15) NOT NULL
);
INSERT INTO Main VALUES (1,'Ford Model T');
INSERT INTO Main VALUES (2,'Ferrari GTB');
INSERT INTO Sub VALUES (2,1,'Red');
INSERT INTO Sub VALUES (2,2,'Yellow');
INSERT INTO Sub VALUES (2,3,'Silver');
To return the full 'denormalized' set:
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
Now I want to use two parameters for the respective key columns with
the sub_key_col parameter 'optional', meaning if it's NULL it is not
used in the WHERE clause. In the past I've got away with a trick like:
Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
but, because of the outer join, sub_key_col can be null and NULL = NULL
removes the row, of course.
I have a solution but it isn't very satisfactory:
DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
WHERE Main.main_key_col = @.main_key_col
AND
CASE WHEN @.sub_key_col IS NULL THEN 1
WHEN Sub.sub_key_col = @.sub_key_col THEN 1
ELSE 0 END = 1
Is there a better way?
Thank you.|||DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main
LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
AND (Sub.sub_key_col = @.sub_key_col OR @.sub_key_col IS NULL)
WHERE Main.main_key_col = @.main_key_col
Jacco Schalkwijk
SQL Server MVP
<decland@.petml.com> wrote in message
news:1118313681.656069.190380@.g14g2000cwa.googlegroups.com...
>I have a problem optionally using a parameter to query a second key
> column in an outer joined table:
> DROP TABLE Sub;
> DROP TABLE Main;
> CREATE TABLE Main (
> main_key_col INTEGER NOT NULL PRIMARY KEY,
> main_data_col VARCHAR(15) NOT NULL
> )'
> CREATE TABLE Sub (
> main_key_col INTEGER NOT NULL
> REFERENCES Main (main_key_col),
> sub_key_col INTEGER NOT NULL,
> PRIMARY KEY (main_key_col, sub_key_col),
> sub_data_col VARCHAR(15) NOT NULL
> );
> INSERT INTO Main VALUES (1,'Ford Model T');
> INSERT INTO Main VALUES (2,'Ferrari GTB');
> INSERT INTO Sub VALUES (2,1,'Red');
> INSERT INTO Sub VALUES (2,2,'Yellow');
> INSERT INTO Sub VALUES (2,3,'Silver');
> To return the full 'denormalized' set:
> SELECT Main.main_key_col, Main.main_data_col,
> Sub.sub_key_col, Sub.sub_data_col
> FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
> Now I want to use two parameters for the respective key columns with
> the sub_key_col parameter 'optional', meaning if it's NULL it is not
> used in the WHERE clause. In the past I've got away with a trick like:
> Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
> but, because of the outer join, sub_key_col can be null and NULL = NULL
> removes the row, of course.
> I have a solution but it isn't very satisfactory:
> DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
> SET @.main_key_col = 2
> SET @.sub_key_col = NULL
> SELECT Main.main_key_col, Main.main_data_col,
> Sub.sub_key_col, Sub.sub_data_col
> FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
> WHERE Main.main_key_col = @.main_key_col
> AND
> CASE WHEN @.sub_key_col IS NULL THEN 1
> WHEN Sub.sub_key_col = @.sub_key_col THEN 1
> ELSE 0 END = 1
> Is there a better way?
> Thank you.
>|||COALESCE (Sub.sub_key_col, '?') = COALESCE(@.sub_key_col,
Sub.sub_key_col, '?')|||--CELKO-- wrote:
> COALESCE (Sub.sub_key_col, '?') = COALESCE(@.sub_key_col,
> Sub.sub_key_col, '?')
Now this I like because I get to use COALESCE after all! I'm now off to
order 'SQL Programming Style' <g>
Thanks everyone.
Monday, March 19, 2012
Displaying the available values for a parameter from the database
hI,
My report has 4 report parameters: @.manufacturer , @.brand, @.Start Date,
@.EndDate .
I want the @.manufacturer , @.brand to hsow the drop down list with distinct
values from the db. Now when in these 2 report parameters I do , Available
Values as Non Queried and the Value and lable as manufacturer , i get the
following errors:
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'manucodeâ'. Forward dependencies are not
valid."
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'StartDateâ'. Forward dependencies are not
valid."
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'EndDateâ'. Forward dependencies are not
valid."
Please help.
Thanks
--
pmudHi,
I found the solution to that. I created 2 datasets: one for desplaying the
manufacture and one for displaying brand. Then in Report parameters Available
Values" I put these new datasets respectively for brand and manucode and
chose the value and label. :)
--
pmud
"pmud" wrote:
> hI,
>
> My report has 4 report parameters: @.manufacturer , @.brand, @.Start Date,
> @.EndDate .
> I want the @.manufacturer , @.brand to hsow the drop down list with distinct
> values from the db. Now when in these 2 report parameters I do , Available
> Values as Non Queried and the Value and lable as manufacturer , i get the
> following errors:
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'manucodeâ'. Forward dependencies are not
> valid."
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'StartDateâ'. Forward dependencies are not
> valid."
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'EndDateâ'. Forward dependencies are not
> valid."
> Please help.
> Thanks
> --
> pmud
My report has 4 report parameters: @.manufacturer , @.brand, @.Start Date,
@.EndDate .
I want the @.manufacturer , @.brand to hsow the drop down list with distinct
values from the db. Now when in these 2 report parameters I do , Available
Values as Non Queried and the Value and lable as manufacturer , i get the
following errors:
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'manucodeâ'. Forward dependencies are not
valid."
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'StartDateâ'. Forward dependencies are not
valid."
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'EndDateâ'. Forward dependencies are not
valid."
Please help.
Thanks
--
pmudHi,
I found the solution to that. I created 2 datasets: one for desplaying the
manufacture and one for displaying brand. Then in Report parameters Available
Values" I put these new datasets respectively for brand and manucode and
chose the value and label. :)
--
pmud
"pmud" wrote:
> hI,
>
> My report has 4 report parameters: @.manufacturer , @.brand, @.Start Date,
> @.EndDate .
> I want the @.manufacturer , @.brand to hsow the drop down list with distinct
> values from the db. Now when in these 2 report parameters I do , Available
> Values as Non Queried and the Value and lable as manufacturer , i get the
> following errors:
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'manucodeâ'. Forward dependencies are not
> valid."
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'StartDateâ'. Forward dependencies are not
> valid."
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'EndDateâ'. Forward dependencies are not
> valid."
> Please help.
> Thanks
> --
> pmud
Displaying sub report even when it has no data
Hi,
I have a main report with a sub report in it. Sub Report contains 1 table and some labels. I am passing 1 parameter from the main report to sub report on the basis of which sub report is generated with in the main report. In case if there comes some records in the sub report than the table as well as the other labels of the sub report are coming properly. But if no records are there in the sub report than it doesn't display the complete sub report i.e. no table and no labels which are with in the sub report. I want to make sure that labels should come even if there is no data in the sub report.
I had this same problem. The solution I came up with was to modify my stored procedure to always returnsome value.SELECT @.Count =Count(*)FROM TableWHERE parameter1 = @.parameter1AND parameter2 = @.parameter2IF @.Count > 0BEGINSELECT Field1, Field2, Field3,DummyValue = -1FROM TableWHERE parameter1 = @.parameter1AND parameter2 = @.parameter2ORDER BY Field1DESC, Field2DESC, Field3ENDELSEBEGINSELECT DummyValue = @.CountENDI don't know if this is best solution, but it definitely works. Hope this helps.
Displaying Selected Report Parameter on Report
Can you display the selected report parameter on the report after the user
submits? I actually want to display the label property of the report
parameter on the report, not the value behind the scenes.
If I just drag and drop from the tool bar, nothing displays.
If I use =Fields(Parameters!P1.Value).Value, I get an error saying the value
for the textbox refers to an non-existing report parameter.
Can anyone helpTry Parameters!P1.Label
Sanjeev
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:D9904481-F4B3-4585-A832-936F89C80817@.microsoft.com...
> Can you display the selected report parameter on the report after the user
> submits? I actually want to display the label property of the report
> parameter on the report, not the value behind the scenes.
> If I just drag and drop from the tool bar, nothing displays.
> If I use =Fields(Parameters!P1.Value).Value, I get an error saying the
> value
> for the textbox refers to an non-existing report parameter.
> Can anyone help
submits? I actually want to display the label property of the report
parameter on the report, not the value behind the scenes.
If I just drag and drop from the tool bar, nothing displays.
If I use =Fields(Parameters!P1.Value).Value, I get an error saying the value
for the textbox refers to an non-existing report parameter.
Can anyone helpTry Parameters!P1.Label
Sanjeev
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:D9904481-F4B3-4585-A832-936F89C80817@.microsoft.com...
> Can you display the selected report parameter on the report after the user
> submits? I actually want to display the label property of the report
> parameter on the report, not the value behind the scenes.
> If I just drag and drop from the tool bar, nothing displays.
> If I use =Fields(Parameters!P1.Value).Value, I get an error saying the
> value
> for the textbox refers to an non-existing report parameter.
> Can anyone help
displaying report parameter names and values in a textbox
I'm trying to return the parameters used in a report in the report footer. Where it is no problem getting back the values I would also like to display the actual parameter name. However I haven't found a way to do that yet.
My hunch is this needs to be done with embedded code. If anyone has got the answer to this I'd be very glad if you could share it.
thanks a lot
Axe
From http://www.developmentnow.com/g/115_2004_7_0_21_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comUse Parameters!ParamName.Label
"Axel" wrote:
> I'm trying to return the parameters used in a report in the report footer. Where it is no problem getting back the values I would also like to display the actual parameter name. However I haven't found a way to do that yet.
> My hunch is this needs to be done with embedded code. If anyone has got the answer to this I'd be very glad if you could share it.
> thanks a lot
> Axel
> From http://www.developmentnow.com/g/115_2004_7_0_21_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>
My hunch is this needs to be done with embedded code. If anyone has got the answer to this I'd be very glad if you could share it.
thanks a lot
Axe
From http://www.developmentnow.com/g/115_2004_7_0_21_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comUse Parameters!ParamName.Label
"Axel" wrote:
> I'm trying to return the parameters used in a report in the report footer. Where it is no problem getting back the values I would also like to display the actual parameter name. However I haven't found a way to do that yet.
> My hunch is this needs to be done with embedded code. If anyone has got the answer to this I'd be very glad if you could share it.
> thanks a lot
> Axel
> From http://www.developmentnow.com/g/115_2004_7_0_21_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>
Sunday, March 11, 2012
displaying Params in Report
Hi... I am trying to display a parameter in my report.. the parameter can have up to 5 chocies... if all 5 are checked I want to display all 5..
I know how to trick it and use: Parameters!Country.Value(0)&Parameters!Country.Value(1) etc
Is there a way to do this that I dont have to have from (0) to (5)..
another thing... when you only choose 2 of the 5 params the rest show errors.. (#Error)
Thanks for help... and Happy Reporting !!
Hey....I'm trying to do the same thing. So far, the best I've come up with is:
join(Parameters!Country.Value, Chr(13) & Chr(10))
This will create a string with a CRLF between all the parameters. Of course, you can change the delimeter to anything you want, but I'm still working on a way to A) insert tabs and B) stick my multi-value parameter in a table or list, but so far, no luck. So if you've got any idea...
Displaying parameter list in report header
I'm trying to display a parameter list of items to a textbox in the header.
Somehow it never evaluates anything after the first item in the array. 1st
below is the expression... 2nd is the custom code. I don't know what I'm
doing wrong...
1.
=code.ParameterList(Parameters!ProgCatCode.value(0))
2.
Public Function ParameterList(ByVal Parameter as Object) as String
Dim sParamItem as Object
Dim sParamVal as String = " "
For Each sParamItem in Parameter
If sParamItem Is Nothing then Exit For
sParamVal &= sParamItem & ", "
Next
Return sParamVal
End FunctionFigured it out..
"gdjoshua" wrote:
> I'm trying to display a parameter list of items to a textbox in the header.
> Somehow it never evaluates anything after the first item in the array. 1st
> below is the expression... 2nd is the custom code. I don't know what I'm
> doing wrong...
> 1.
> =code.ParameterList(Parameters!ProgCatCode.value(0))
> 2.
> Public Function ParameterList(ByVal Parameter as Object) as String
> Dim sParamItem as Object
> Dim sParamVal as String = " "
> For Each sParamItem in Parameter
> If sParamItem Is Nothing then Exit For
> sParamVal &= sParamItem & ", "
> Next
> Return sParamVal
> End Function
>|||What was it that you figured out. Share it with us.
EROK
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:1D6BB58A-C00C-496C-A675-3FBFBED4E332@.microsoft.com...
> Figured it out..
> "gdjoshua" wrote:
>> I'm trying to display a parameter list of items to a textbox in the
>> header.
>> Somehow it never evaluates anything after the first item in the array.
>> 1st
>> below is the expression... 2nd is the custom code. I don't know what
>> I'm
>> doing wrong...
>> 1.
>> =code.ParameterList(Parameters!ProgCatCode.value(0))
>> 2.
>> Public Function ParameterList(ByVal Parameter as Object) as String
>> Dim sParamItem as Object
>> Dim sParamVal as String = " "
>> For Each sParamItem in Parameter
>> If sParamItem Is Nothing then Exit For
>> sParamVal &= sParamItem & ", "
>> Next
>> Return sParamVal
>> End Function|||I had a similar issue, so I can tell you the things I tried.
1. His primary issue with the custom code was what he was passing into the
function. Parameters!ProgCatCode.value(0) will only give the value at index
0, but Parameters!ProgCatCode.value without the parentethis will pass in the
entire array
2. One way around having to write custom code is to use the Join function.
Join(Parameters!ProgCatCode.value, ", ") gives you all the selected Values.
Join(Parameters!ProgCatCode.label, ", ") gives you all the selected Labels,
which might be better in some cases. Your Value might be numeric data and
meaningless to the user, but the Lable you made to display in your drop down
list might be what the user really wants to see.
"Erok" wrote:
> What was it that you figured out. Share it with us.
> EROK
> "gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
> news:1D6BB58A-C00C-496C-A675-3FBFBED4E332@.microsoft.com...
> > Figured it out..
> >
> > "gdjoshua" wrote:
> >
> >> I'm trying to display a parameter list of items to a textbox in the
> >> header.
> >> Somehow it never evaluates anything after the first item in the array.
> >> 1st
> >> below is the expression... 2nd is the custom code. I don't know what
> >> I'm
> >> doing wrong...
> >>
> >> 1.
> >> =code.ParameterList(Parameters!ProgCatCode.value(0))
> >>
> >> 2.
> >>
> >> Public Function ParameterList(ByVal Parameter as Object) as String
> >> Dim sParamItem as Object
> >> Dim sParamVal as String = " "
> >> For Each sParamItem in Parameter
> >> If sParamItem Is Nothing then Exit For
> >> sParamVal &= sParamItem & ", "
> >> Next
> >> Return sParamVal
> >> End Function
> >>
>
>|||Rob,
sicne the others did not thank you, I'll say it for them.
THANKS!
"Rob 'Spike' Stevens" wrote:
> I had a similar issue, so I can tell you the things I tried.
> 1. His primary issue with the custom code was what he was passing into the
> function. Parameters!ProgCatCode.value(0) will only give the value at index
> 0, but Parameters!ProgCatCode.value without the parentethis will pass in the
> entire array
> 2. One way around having to write custom code is to use the Join function.
> Join(Parameters!ProgCatCode.value, ", ") gives you all the selected Values.
> Join(Parameters!ProgCatCode.label, ", ") gives you all the selected Labels,
> which might be better in some cases. Your Value might be numeric data and
> meaningless to the user, but the Lable you made to display in your drop down
> list might be what the user really wants to see.
Somehow it never evaluates anything after the first item in the array. 1st
below is the expression... 2nd is the custom code. I don't know what I'm
doing wrong...
1.
=code.ParameterList(Parameters!ProgCatCode.value(0))
2.
Public Function ParameterList(ByVal Parameter as Object) as String
Dim sParamItem as Object
Dim sParamVal as String = " "
For Each sParamItem in Parameter
If sParamItem Is Nothing then Exit For
sParamVal &= sParamItem & ", "
Next
Return sParamVal
End FunctionFigured it out..
"gdjoshua" wrote:
> I'm trying to display a parameter list of items to a textbox in the header.
> Somehow it never evaluates anything after the first item in the array. 1st
> below is the expression... 2nd is the custom code. I don't know what I'm
> doing wrong...
> 1.
> =code.ParameterList(Parameters!ProgCatCode.value(0))
> 2.
> Public Function ParameterList(ByVal Parameter as Object) as String
> Dim sParamItem as Object
> Dim sParamVal as String = " "
> For Each sParamItem in Parameter
> If sParamItem Is Nothing then Exit For
> sParamVal &= sParamItem & ", "
> Next
> Return sParamVal
> End Function
>|||What was it that you figured out. Share it with us.
EROK
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:1D6BB58A-C00C-496C-A675-3FBFBED4E332@.microsoft.com...
> Figured it out..
> "gdjoshua" wrote:
>> I'm trying to display a parameter list of items to a textbox in the
>> header.
>> Somehow it never evaluates anything after the first item in the array.
>> 1st
>> below is the expression... 2nd is the custom code. I don't know what
>> I'm
>> doing wrong...
>> 1.
>> =code.ParameterList(Parameters!ProgCatCode.value(0))
>> 2.
>> Public Function ParameterList(ByVal Parameter as Object) as String
>> Dim sParamItem as Object
>> Dim sParamVal as String = " "
>> For Each sParamItem in Parameter
>> If sParamItem Is Nothing then Exit For
>> sParamVal &= sParamItem & ", "
>> Next
>> Return sParamVal
>> End Function|||I had a similar issue, so I can tell you the things I tried.
1. His primary issue with the custom code was what he was passing into the
function. Parameters!ProgCatCode.value(0) will only give the value at index
0, but Parameters!ProgCatCode.value without the parentethis will pass in the
entire array
2. One way around having to write custom code is to use the Join function.
Join(Parameters!ProgCatCode.value, ", ") gives you all the selected Values.
Join(Parameters!ProgCatCode.label, ", ") gives you all the selected Labels,
which might be better in some cases. Your Value might be numeric data and
meaningless to the user, but the Lable you made to display in your drop down
list might be what the user really wants to see.
"Erok" wrote:
> What was it that you figured out. Share it with us.
> EROK
> "gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
> news:1D6BB58A-C00C-496C-A675-3FBFBED4E332@.microsoft.com...
> > Figured it out..
> >
> > "gdjoshua" wrote:
> >
> >> I'm trying to display a parameter list of items to a textbox in the
> >> header.
> >> Somehow it never evaluates anything after the first item in the array.
> >> 1st
> >> below is the expression... 2nd is the custom code. I don't know what
> >> I'm
> >> doing wrong...
> >>
> >> 1.
> >> =code.ParameterList(Parameters!ProgCatCode.value(0))
> >>
> >> 2.
> >>
> >> Public Function ParameterList(ByVal Parameter as Object) as String
> >> Dim sParamItem as Object
> >> Dim sParamVal as String = " "
> >> For Each sParamItem in Parameter
> >> If sParamItem Is Nothing then Exit For
> >> sParamVal &= sParamItem & ", "
> >> Next
> >> Return sParamVal
> >> End Function
> >>
>
>|||Rob,
sicne the others did not thank you, I'll say it for them.
THANKS!
"Rob 'Spike' Stevens" wrote:
> I had a similar issue, so I can tell you the things I tried.
> 1. His primary issue with the custom code was what he was passing into the
> function. Parameters!ProgCatCode.value(0) will only give the value at index
> 0, but Parameters!ProgCatCode.value without the parentethis will pass in the
> entire array
> 2. One way around having to write custom code is to use the Join function.
> Join(Parameters!ProgCatCode.value, ", ") gives you all the selected Values.
> Join(Parameters!ProgCatCode.label, ", ") gives you all the selected Labels,
> which might be better in some cases. Your Value might be numeric data and
> meaningless to the user, but the Lable you made to display in your drop down
> list might be what the user really wants to see.
Displaying Multi-valued parameters
Hi There.
I am struggling with an issue with multi-valued parameters. I have a parameter that is a list of several hundred items and when someone selects all of them, I display the huge list in the report header vias the join command.
This works great for a few parameters, but overwrites my data when the list is large. I want to do something in the expression where I determine if all items are selected and then just display 'All' instead of the whole list. Any ideas would be very helpful!
Thanks, Mike
see this code from msdn, modify it to suit your need, if parameter.Value(i) <> " " then increase a counter value, at the end check if counter = total count in parameter then display all
to call the function in the expression
=Code. ShowParameterValues(Parameter!SomePara)
hope this helps
Public Function ShowParameterValues(ByVal parameter as Parameter)
as String
Dim s as String
If parameter.IsMultiValue then
s = "Multivalue: "
For i as integer = 0 to parameter.Count-1
s = s + CStr(parameter.Value(i)) + " "
Next
Else
s = "Single value: " + CStr(parameter.Value)
End If
Return s
End Function
Thanks for the guidance Yashant!
I have not done custom code in a report. I think the
=Code. ShowParameterValues(Parameter!SomePara)
goes in the expression of the textbox correct?
Where does the function go? Is it under the report properties code tab?
Thanks, Mike
|||yes, the expression goes into the text box and code goes in reports properties code tab. you need to modify the code to work for you.Displaying Multi parameter info : Report builder
Hi All,
Is there is any way to display the User selected parameter information if the parameter is multiselect ?
I was able to display if the parameter is single valued but if it is multivalued (list), i was not able to .
Any help is greatly appreciated.
Regards,
Can you elaborate your question?Regards,
Ramesh KS
Hi!
Join([your parameter, either value or label], [your delimiter]), will do the trick for you.
//H?kan
|||Hi.
Thanks a lot , it worked.
|||HI Bala_SSRS:
Could You Post Your Sources for a code sample ?
TKS
|||
Here is the sample code
=String.Format("Organization Name: {0}",Join(Parameters!OrganizationName.Value,";"))
Thanks,
Bala
Friday, March 9, 2012
Displaying images in a report
Hi all
I have a report and I display specific images based on the parameter selected. i.e. If parameter value = "1" then I display "image1.jpg". The images were added as a part of the project so I was able to display the images by setting the value property on the images to the image file name. So if I want to display image1.jpg then I would set the value property of the image to "image1.jpg".
Now I have decided to create a folder called "images" in which I want to keep all the images. So I have removed the images from the project. In the value property of the image when I put "/images/image1.jpg" it doesnt work. I have also tried "images/image1.jpg" When I run the report in the Preview mode, the image is not shown. How do i fix this?
The directory struncture is so:
Reports folder
--images folder
-image1.jpg
-image2.jpg
--report1.rdl
--report2.rdl
Thanks guys!!
One thing you could do is embed the images so they're available without being published separately. But if you have a very large number of alternative images, I guess this might be cumbersome.
For dynamic images, you can create a parameter with the location/path of these reports, such as file://.... or /. finishing up with the final path slash. Now your values are something like
Code Snippet
IIF(paramvalue = "1",paramlocation & "image1.jpg", paramlocation & "image2.jpg")... or maybe if the "1" represents a part of the image filename, it's really just something like the following (no IIF):
Code Snippet
paramlocation & "image" & paramvalue & ".jpg"... IOW, you can fully-qualify the path, and this is one way I know for sure it works.
I'm not entirely clear on where you put the images and whether, therefore, the relative path you're using is going to work as you think it is. If it's on the Report Server you can try using Globals!ReportFolderand Globals!ReportServer to build up the image path but I haven't personally had much luck doing it that way.
You may also be using ReportViewer and trying to set the folder according to where your EXE is (or your webapp if it's a webform ReportViewer)? I think you still need to fully-qualify that path.
>L<
|||I have several images that I need to change based upon a parameter being passed in. I put the images in my images folder on my website and changed the image names to each parameter being passed. I then placed a web image on the report and changed the value of the image in properties to something like the following. "=http://server/images/" & Fields!parameter.Value & ".jpg" This way it changes the image based upon a parameter. Basically changing the image property to a string. Hope this might help.Jason
displaying different reports based on a parameter
Hi,
I have bunch of reports that take same set of parameters. I am trying parametrize the report type so that depending on the report type selected, body should display that report when user hits "View report" button. How can I do this? Pardon me if there is an obvious solution as I am pretty new to the joys of MS Reporting Services.
Thanks a bunch.
add a subreport control and then set the sub report name using an expression
Wednesday, March 7, 2012
Displaying 'ALL' - Multivalue Parameter
Now that the Select All feature is working, I need to be able to display the
text ALL when the end user checks select all (in a textbox that is designed
to show values chosen for the parameter). Is this possible?
I am able to do this when I add an all value to the drop down selection, but
that gives you a value of ALL and a value of Select All. I am certain that
this will confuse the end user.
We need to be able to turn the Select All off.
Thanks!
--
blesrptdevOn Mar 8, 2:02 pm, blesrptdev <blesrpt...@.discussions.microsoft.com>
wrote:
> Now that the Select All feature is working, I need to be able to display the
> text ALL when the end user checks select all (in a textbox that is designed
> to show values chosen for the parameter). Is this possible?
> I am able to do this when I add an all value to the drop down selection, but
> that gives you a value of ALL and a value of Select All. I am certain that
> this will confuse the end user.
> We need to be able to turn the Select All off.
> Thanks!
> --
> blesrptdev
I don't believe that turning 'Select All' off is possible; however,
you can either take a count of the options selected by the user and
compare it to the total possible options in the query and return a
separate field to the report showing that 'Select All' was selected.
Or if you know that the total options will not exceed a certain
quantity, you could use something like the following:
=iif(Parameters!ParameterName.Count > 20, "Select All",
"SomeDefaultText")
Where 20, in this example, is the maximum number of options to select
in the drop-down list box. Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks Enrique! This creatively worked.
--
blesrptdev
"EMartinez" wrote:
> On Mar 8, 2:02 pm, blesrptdev <blesrpt...@.discussions.microsoft.com>
> wrote:
> > Now that the Select All feature is working, I need to be able to display the
> > text ALL when the end user checks select all (in a textbox that is designed
> > to show values chosen for the parameter). Is this possible?
> >
> > I am able to do this when I add an all value to the drop down selection, but
> > that gives you a value of ALL and a value of Select All. I am certain that
> > this will confuse the end user.
> >
> > We need to be able to turn the Select All off.
> >
> > Thanks!
> > --
> > blesrptdev
> I don't believe that turning 'Select All' off is possible; however,
> you can either take a count of the options selected by the user and
> compare it to the total possible options in the query and return a
> separate field to the report showing that 'Select All' was selected.
> Or if you know that the total options will not exceed a certain
> quantity, you could use something like the following:
> =iif(Parameters!ParameterName.Count > 20, "Select All",
> "SomeDefaultText")
> Where 20, in this example, is the maximum number of options to select
> in the drop-down list box. Sorry I could not be of more assistance.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>
>
text ALL when the end user checks select all (in a textbox that is designed
to show values chosen for the parameter). Is this possible?
I am able to do this when I add an all value to the drop down selection, but
that gives you a value of ALL and a value of Select All. I am certain that
this will confuse the end user.
We need to be able to turn the Select All off.
Thanks!
--
blesrptdevOn Mar 8, 2:02 pm, blesrptdev <blesrpt...@.discussions.microsoft.com>
wrote:
> Now that the Select All feature is working, I need to be able to display the
> text ALL when the end user checks select all (in a textbox that is designed
> to show values chosen for the parameter). Is this possible?
> I am able to do this when I add an all value to the drop down selection, but
> that gives you a value of ALL and a value of Select All. I am certain that
> this will confuse the end user.
> We need to be able to turn the Select All off.
> Thanks!
> --
> blesrptdev
I don't believe that turning 'Select All' off is possible; however,
you can either take a count of the options selected by the user and
compare it to the total possible options in the query and return a
separate field to the report showing that 'Select All' was selected.
Or if you know that the total options will not exceed a certain
quantity, you could use something like the following:
=iif(Parameters!ParameterName.Count > 20, "Select All",
"SomeDefaultText")
Where 20, in this example, is the maximum number of options to select
in the drop-down list box. Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks Enrique! This creatively worked.
--
blesrptdev
"EMartinez" wrote:
> On Mar 8, 2:02 pm, blesrptdev <blesrpt...@.discussions.microsoft.com>
> wrote:
> > Now that the Select All feature is working, I need to be able to display the
> > text ALL when the end user checks select all (in a textbox that is designed
> > to show values chosen for the parameter). Is this possible?
> >
> > I am able to do this when I add an all value to the drop down selection, but
> > that gives you a value of ALL and a value of Select All. I am certain that
> > this will confuse the end user.
> >
> > We need to be able to turn the Select All off.
> >
> > Thanks!
> > --
> > blesrptdev
> I don't believe that turning 'Select All' off is possible; however,
> you can either take a count of the options selected by the user and
> compare it to the total possible options in the query and return a
> separate field to the report showing that 'Select All' was selected.
> Or if you know that the total options will not exceed a certain
> quantity, you could use something like the following:
> =iif(Parameters!ParameterName.Count > 20, "Select All",
> "SomeDefaultText")
> Where 20, in this example, is the maximum number of options to select
> in the drop-down list box. Sorry I could not be of more assistance.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>
>
Saturday, February 25, 2012
display the description of the parameter field in the report
How do i display the description in the report for the parameter field
eg suppose Parameter UWName Value is AA Desc is Andy Arace
I want to display the Desc of the Parameter in the report details instead of the value AA
Pls helpNot sure what you really want, but here goes (VB Code using CRAXDRT COM object):
assumes you already have an instance of the report object open:
Dim objParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim objParamDef As CRAXDRT.ParameterFieldDefinition
.
.
.
.
Set objPrintApp = New CRAXDRT.Application
Set objReport = objPrintApp.OpenReport(strReportSourcePath & strSource)
.
.
.
Set objParamDefs = objReport.ParameterFields
For Each objParamDef In objParamDefs
objParamDef.Print .Prompt
Next
.
.
.
does that help
dave|||Hi, I have the same problem too,
Springsoft, your answer doesnt match with the problem :(
eg suppose Parameter UWName Value is AA Desc is Andy Arace
I want to display the Desc of the Parameter in the report details instead of the value AA
Pls helpNot sure what you really want, but here goes (VB Code using CRAXDRT COM object):
assumes you already have an instance of the report object open:
Dim objParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim objParamDef As CRAXDRT.ParameterFieldDefinition
.
.
.
.
Set objPrintApp = New CRAXDRT.Application
Set objReport = objPrintApp.OpenReport(strReportSourcePath & strSource)
.
.
.
Set objParamDefs = objReport.ParameterFields
For Each objParamDef In objParamDefs
objParamDef.Print .Prompt
Next
.
.
.
does that help
dave|||Hi, I have the same problem too,
Springsoft, your answer doesnt match with the problem :(
Friday, February 24, 2012
display short datetime
Hi
I need to set the default value of an "End Date" parameter to the date of today.
Now I use the expression =Now(), but then the timestamp is also displayed. That I don't want to happen!
I tried formatting the datetime but then it becomes a string, and converting it back to a date leaves also a timestamp but with midnight time.
Is there a way to format a date parameter?
Hi,
Did you try : Format(mydate.Value,"dd/MM/yyyy") or Cdate(Format(mydate.Value,"dd/MM/yyyy").ToString) ?
Regards
Ayzan
|||Both expressions above don't work because my parameter is of the type datetime and the expressions return a string. So I get an error that the parameter has another type than it expected.
Still thanks for pointing me the CDate function. It has alot of functions and that's the function that got me the wanted result.
To display only the date part of a datetime value retrieved by the function Now() you need the following expression:
=CDate(Now()).Today
Display Selected Parameters in Report
Is there a way of showing which parameter values were chosen from a multi-value parameter list in RS 2005?
I want to show in the Report Header the parameters that have been chosen. There are "Count", "Value", "Label", "IsMultiValue" options in the expression builder for the parameter but no way of knowing which ones were selected?
Found the answer....from Robert....
Once you mark a paraeter as "multi-value", the .Value property will return an object[] with all selected values. If only one value is selected, it will be an object array of length = 1. Object arrays cannot be directly compared with Strings.
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
=Split("a b c", " ")
to create a multi value object array from a string (this can be used e.g. for drillthrough parameters, subreports, or query parameters)
See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
-- Robert
Display selected multivalue parameters
I would like to display the selections made for a parameter in a multi select dropdown.
If i do as following for a textbox..doesnt work.
=Parameters!STORELOCATION.Label.ToString
I would like the labels to be displayed as below.
Report is for the following stores: abc, xyz
Pls help!
Use this expression...
=Join(Parameters!Storelocation.label, ", ")
|||Thank you !! This works..Display selected multivalue parameters
I would like to display the selections made for a parameter in a multi select dropdown.
If i do as following for a textbox..doesnt work.
=Parameters!STORELOCATION.Label.ToString
I would like the labels to be displayed as below.
Report is for the following stores: abc, xyz
Pls help!
Use this expression...
=Join(Parameters!Storelocation.label, ", ")
|||Thank you !! This works..Display report parameter in report
How can I display the label field from a query parameter within the report header? I have a parameter that gets it's value from a drop down list. The parameter value is passed to the stored procedure but I want to display the text portion within the report.=Parameters!YourParam.Label() & ": " & Parameters!YourParam.Value()
Subscribe to:
Posts (Atom)