Partially Bold text and Justify?

I have put following expression in a textbox.
="My name is " + First(Fields!Brok_Name.Value, "myDataset")
I just want to Bold the Field value and not the word "My name is ".
I also want to justify the text Alignment, how can I ?
I am using Sql Server Reporting Service 2005.
Any idea please ?
Best Regards,
LuqmanLuqman,
I don't think multiple formatting within a single text box is supported. You
might be able to embed an html character in the string like this ...>="My
name is <B>" + First(Fields!Brok_Name.Value, "myDataset") + "</B>" ...never
tried it but maybe it will work?
I have always used 2 text boxes. One for the label "My name is " and another
for the field value. Then I right align the label and left align the field
value. As far as justifying the complete string "My name is " + Field ...not
sure if it is possible with different bold values?
luqman wrote:
>I have put following expression in a textbox.
>="My name is " + First(Fields!Brok_Name.Value, "myDataset")
>I just want to Bold the Field value and not the word "My name is ".
>I also want to justify the text Alignment, how can I ?
>I am using Sql Server Reporting Service 2005.
>Any idea please ?
>Best Regards,
>Luqman
--
Message posted via http://www.sqlmonster.com|||Hi,
The Html character <B> within the text is showing as text.
My expression is :
="My name is <B>" + First(Fields!Brok_Name.Value, "myDataset") + "</B>"
Output is :
My name is <B> luqman </B>
Am I missing something ?
Best Regards,
Luqman
"wnichols via SQLMonster.com" <u3357@.uwe> wrote in message
news:7829016629ac6@.uwe...
> Luqman,
> I don't think multiple formatting within a single text box is supported.
You
> might be able to embed an html character in the string like this ...>="My
> name is <B>" + First(Fields!Brok_Name.Value, "myDataset") + "</B>"
...never
> tried it but maybe it will work?
> I have always used 2 text boxes. One for the label "My name is " and
another
> for the field value. Then I right align the label and left align the
field
> value. As far as justifying the complete string "My name is " + Field
...not
> sure if it is possible with different bold values?
>
> luqman wrote:
> >I have put following expression in a textbox.
> >
> >="My name is " + First(Fields!Brok_Name.Value, "myDataset")
> >
> >I just want to Bold the Field value and not the word "My name is ".
> >
> >I also want to justify the text Alignment, how can I ?
> >
> >I am using Sql Server Reporting Service 2005.
> >
> >Any idea please ?
> >
> >Best Regards,
> >
> >Luqman
> --
> Message posted via http://www.sqlmonster.com
>|||Luqman,
It was just a shot in the dark...guess it did not work. I don't think it is
possible to have multiple formats within the same textbox.
luqman wrote:
>Hi,
>The Html character <B> within the text is showing as text.
>My expression is :
>="My name is <B>" + First(Fields!Brok_Name.Value, "myDataset") + "</B>"
>Output is :
>My name is <B> luqman </B>
>Am I missing something ?
>Best Regards,
>Luqman
>> Luqman,
>[quoted text clipped - 26 lines]
>> --
>> Message posted via http://www.sqlmonster.com
--
Message posted via http://www.sqlmonster.com|||Hi,
In case, the textbox does not support multiple formats, can we display RTF
Text inside a textbox, saved in a database.
Best Regards,
Luqman
"wnichols via SQLMonster.com" <u3357@.uwe> wrote in message
news:7834e5fdfd74f@.uwe...
> Luqman,
> It was just a shot in the dark...guess it did not work. I don't think it
is
> possible to have multiple formats within the same textbox.
>
> luqman wrote:
> >Hi,
> >
> >The Html character <B> within the text is showing as text.
> >
> >My expression is :
> >="My name is <B>" + First(Fields!Brok_Name.Value, "myDataset") + "</B>"
> >
> >Output is :
> >My name is <B> luqman </B>
> >
> >Am I missing something ?
> >
> >Best Regards,
> >
> >Luqman
> >
> >> Luqman,
> >>
> >[quoted text clipped - 26 lines]
> >> --
> >> Message posted via http://www.sqlmonster.com
> --
> Message posted via http://www.sqlmonster.com
>|||Luqman,
That's my extent of the knowledge on the subject...Sorry.
luqman wrote:
>Hi,
>In case, the textbox does not support multiple formats, can we display RTF
>Text inside a textbox, saved in a database.
>Best Regards,
>Luqman
>> Luqman,
>[quoted text clipped - 25 lines]
>> --
>> Message posted via http://www.sqlmonster.com
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200709/1|||On Sep 17, 10:55 am, "wnichols via SQLMonster.com" <u3357@.uwe> wrote:
> Luqman,
> That's my extent of the knowledge on the subject...Sorry.
> luqman wrote:
> >Hi,
> >In case, the textbox does not support multiple formats, can we display RTF
> >Text inside a textbox, saved in a database.
> >Best Regards,
> >Luqman
> >> Luqman,
> >[quoted text clipped - 25 lines]
> >> --
> >> Message posted viahttp://www.sqlmonster.com
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200709/1
You can't do it in 2005. I saw a video on reporting services 2008,
and it's a new feature microsoft is adding. Below is some links on
it:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2122612&SiteID=1
http://sqljunkies.com/WebLog/sqlbi/archive/2007/06/07/35631.aspx

Partial formatting of textbox

Is it possible to format part of an expression in a textbox? I have this
expression which displays the parameter value(s) that are selected by the
user:
="Selected Region(s): " & Join(Parameters!paramRegion.Label,", ")
I wish to put "Selected Region(s): " in bold. I can use a second textbox in
bold "Selected Region(s): " and leave just the expression in the other one,
but I'm trying to do everything in a single textbox.
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comYou cannot do this in RS 2005 (rich text in a single textbox)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:ON$sP4yQIHA.748@.TK2MSFTNGP04.phx.gbl...
> Is it possible to format part of an expression in a textbox? I have this
> expression which displays the parameter value(s) that are selected by the
> user:
> ="Selected Region(s): " & Join(Parameters!paramRegion.Label,", ")
> I wish to put "Selected Region(s): " in bold. I can use a second textbox
> in bold "Selected Region(s): " and leave just the expression in the other
> one, but I'm trying to do everything in a single textbox.
> --
> Thank you,
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>

Parsing Text String Field - comma delimited

Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes? What I need is for each new value within the string to create a new record.

Example:

ID STRING

12345 1,2,3

67891 2,4

Becomes

12345 1

12345 2

12345 3

67891 2

67891 4

Thanks for any help!!

Check this page. It has great examples (if you are using 2005 there are two really great ways of handling this)

http://www.aspfaq.com/show.asp?id=2529

Parse Return value of SYSTEM_USER

DECLARE @.UserName nvarchar(100)
SELECT @.UserName = SYSTEM_USER

value returned is "Domain\NTSignonName"
What I want is only "NTSignonName"

Is there a function to do this or an easy parse for this in SQL2000?

lqI know I can do:

DECLARE @.UserNameWithDomain nvarchar(100)
SELECT @.UserNameWithDomain = SYSTEM_USER

DECLARE @.UserNameNoDomain nvarchar(100)
SELECT @.UserNameNoDomain =
SUBSTRING(@.UserNameWithDomain,CHARINDEX('\',@.UserN ameWithDomain)+1,100)

I'm hoping for something more aesthetic.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I know I can do:
> DECLARE @.UserNameWithDomain nvarchar(100)
> SELECT @.UserNameWithDomain = SYSTEM_USER
> DECLARE @.UserNameNoDomain nvarchar(100)
> SELECT @.UserNameNoDomain =
> SUBSTRING(@.UserNameWithDomain,CHARINDEX('\',@.UserN ameWithDomain)+1,100)
> I'm hoping for something more aesthetic.

I don't know if it's more esthetic, but parsename() is an alternative.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx

Parse

I have one database backup file that is created daily with directory
structure listed
below what is the easy way to parse or extract that numeric value from the
command listed below?
Thank You,
EXEC xp_cmdshell 'dir \\DALL3\T$\dump\DALLAS\*.BAK'Joe,
Assuming the command is stored in a column (Col1) in a table (TEST1) and is
a single value, the following should work:
SELECT SUBSTRING(Col1,(CHARINDEX('',Col1,CHARI
NDEX('',Col1,1)+ 2)-1),1)
FROM TEST1
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7771B914-DFFF-473C-881A-82552D7FEF45@.microsoft.com...
> I have one database backup file that is created daily with directory
> structure listed
> below what is the easy way to parse or extract that numeric value from the
> command listed below?
> Thank You,
> EXEC xp_cmdshell 'dir \\DALL3\T$\dump\DALLAS\*.BAK'|||Please, be more specific. At least give a few typical samples, and specify
what exactly you'd like to parse - is it the whole command, the parameter,..
.
what?
ML

Parenthesis in field value

Hello all

I have a table called PRODUCT and the unique field in the table is called PARTNUMBER like so:

PARTNUMBER DESCRIPTION
12345(6) Widget 1
12347(5) Widget 2

my select SQL looks like this
SELECT * FROM PRODUCT WHERE PARTNUMBER = '12345(6)'

That statement fails. what can I do to get around this error?

Regards
The CaptainThe only obvious thing I can think of is that you should use the double quotes and not the single quotes.

This works in SQL View in Access:

SELECT PRODUCT.*, PRODUCT.PARTNUMBER
FROM PRODUCT
WHERE (((PRODUCT.PARTNUMBER)="12345(6)"));|||How does the statement fail? Does it produce an error message?

-PatP

Partitioning error

Hi,

I am trying to implement partitioning on a table
depending upon the fiscal_month value...

The current values are from 1-6...

Create partition function LoadDataPartitionFunction ( smallint)
as
Range for values (1,2,3,4,5,6)

-- drop partition scheme LoadDataPartitionScheme
create partition scheme LoadDataPartitionScheme
as
Partition LoadDataPartitionFunction ALL to ([PRIMARY])

CREATE TABLE Load_Data_Partition (
[RowID] [int] NOT NULL,
[Fiscal_Month] [smallint] NOT NULL,
[Fiscal_Year] [smallint] NOT NULL,
...

[Service] [nvarchar](100) COLLATE
) ON LoadDataPartitionScheme (Fiscal_Month)

truncate table Load_Data_old -- same schema as load_data_partition
Alter table load_data_partition switch partition 1 to Load_Data_old

-- which month's data to be moved out
alter partition function LoadDataPartitionFunction () merge range (1)

Alter partition scheme LoadDataPartitionScheme next used [primary]

-- which months data to be moved in
alter partition function LoadDataPartitionFunction () split range(7)

Select * from sys.partition_range_values

function_id boundary_id parameter_id value
---- ---- ---- --
65545 1 1 2
65545 2 1 3
65545 3 1 4
65545 4 1 5
65545 5 1 6
65545 6 1 7

Alter table [Load_Data_new] switch to [Load_Data_partition] partition 6

ALTER TABLE SWITCH statement failed. Check constraints of source table Load_Data_new' allow values that are not allowed by range defined by partition 6 on target table 'Load_Data_partition'.

Values in Load_Data_new for fiscal_month is 7

But when i try

Insert into [Load_Data_partition]
Select * from [Load_Data_new]
where fiscal_month = 7

it works fine...

reference used : http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htmI got the answer..

Alter table Load_Data_new add constraint load_data_new_month check ( fiscal_month =7)

even though the Load_Data_new table has only month = 7 data...
a constraint is mandatory....

parent and child reports

Please help me, is there a way to get the field.value from another report?

example:
report1.rdl:
id: 01 < field!id.value
name | age | sex
nemo 11 m
id: 02
...
I want the field!id.value to be used by report2.rdl, so i can make a parent -child way:

id: 01 < report1.rdl
name | age | sex
nemo 11 m

friend | school | teacher <--report2.rdl,a subreport with id:01 from report1.rdl
sha ppch yanimoto

As you have already indicated, you can use a subreport and just pass the current id-value as parameter to the subreport. In the subreport make sure the report parameter value is used in the query to just retrieve the data with the according parameter value.

-- Robert

Parameters: How to set null value

Hi, I have a problem.
I have two parameters: Group and Selection

In the first I have 2 choices: Total and Day
And the value of the second parameter is dynamic and is sets to allow null value
The behaviour I want is this: If a user select Total in the first parameter, the second must be set to NULL, if the selection is Day, the second must be set with a data set.

How I can set the value of the second parameter with null, I tryed

Have anyone an idea?

One way I have done this in the past is using '-1' as the Total value, and then letting the stored procedure pass in a null value by setting all '-1' parameters to null.

if @.param1 = '-1' set @.param1=null

System.DbNull.Value may work too.

Yet another option that may be possible is to use an expression to convert the parameter to a null value, and pass in the expression instead of the parameter to the report.

cheers,

Andrew

|||thanks,
I tryed System.DbNull.Value but give me this error : DbNull is a type in system and cannot be used as an expression

Perhaps I wrong something:
I wrote this in the datasets I use for the second parameter

=iif(Parameters!parm1.Value = 2,
"select a11.DATE_ID from Day" ,System.DBNull )

Another question, where I have to place this expression, in the store procedure?

if @.param1 = '-1' set @.param1=null

but I use a dataset.

Thanks in advance

|||

Not sure exactly how you are passing in a dataset as a parameter but looks interesting.

Instead of null perhaps you could use "SELECT null as DateID" instead? Or maybe an empty string?

The syntax for getting dbnull is to use System.DBNull.Value

=iif(Parameters!parm1.Value = 2,
"select a11.DATE_ID from Day" ,System.DBNull.Value )

cheers,

Andrew

|||If I use this expression "System.DBNull.Value" something don't work but I don't understand what.
but following your example and this link forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766574&SiteID=1
it works

thanks

Parameters...Null as an available value?!?!?

I have a hierarchy of organizations that I need to be able to filter by
in the reports...
For example, an Agency can have many Applications associated with it.
I have three stored procedures, one that gets a list of Agencies, the
other that gets a list of Applications based on the Agency that was
selected, and the third takes a bunch of other parameters and gets all
the matching orders (or whatever) associated with the Agency and
Application (Agency and Application are both input params to the third
stored proc).
What I really want is for the user to be able to not select an Agency,
basically setting it to Null, and letting the stored procedure that
does the query for the report ignore that parameter and get results for
all agencies.
If I set the parameter in the report to null, then I can't have a drop
down list with the Agency names if the user doesn't want to set the
Agency to null. But I can't have both!!!
What is the best procedure for doing this? Returning a -1 record in
the list of Agencies and using that to indicate null within the stored
proc? Any other ideas? I can't return a null record in the stored
proc because the report throws an exception. Does this make sense?
Any help would be appreciated!!! Thanks, BrianI had a similiar problem, I solved it by using 'All' as below:
Select * from MainTable
Where (Agency_Name = @.AgencyName OR @.AgencyName = 'All') AND .....
@.AgencyName is a parameter, you can set the parameter's default value
to All( you can make one dataset for this parameter as below:
Select ID, Name from AgencyTable UNION Select 0,'All'. Then In the
Report Parameters dialogue,Choose available value from query,make All
as default value), then the first part of where clause will always
automatically satisfied unless you choose a different value.
This works great for me, Hope this helps.
Good luck.
Henry
Brian wrote:
> I have a hierarchy of organizations that I need to be able to filter
by
> in the reports...
> For example, an Agency can have many Applications associated with it.
> I have three stored procedures, one that gets a list of Agencies, the
> other that gets a list of Applications based on the Agency that was
> selected, and the third takes a bunch of other parameters and gets
all
> the matching orders (or whatever) associated with the Agency and
> Application (Agency and Application are both input params to the
third
> stored proc).
> What I really want is for the user to be able to not select an
Agency,
> basically setting it to Null, and letting the stored procedure that
> does the query for the report ignore that parameter and get results
for
> all agencies.
> If I set the parameter in the report to null, then I can't have a
drop
> down list with the Agency names if the user doesn't want to set the
> Agency to null. But I can't have both!!!
> What is the best procedure for doing this? Returning a -1 record in
> the list of Agencies and using that to indicate null within the
stored
> proc? Any other ideas? I can't return a null record in the stored
> proc because the report throws an exception. Does this make sense?
> Any help would be appreciated!!! Thanks, Brian|||That's exactly what I ended up doing this morning. Works great so far!
Thanks!
Brian
fanh@.tycoelectronics.com wrote:
> I had a similiar problem, I solved it by using 'All' as below:
> Select * from MainTable
> Where (Agency_Name = @.AgencyName OR @.AgencyName = 'All') AND .....
> @.AgencyName is a parameter, you can set the parameter's default value
> to All( you can make one dataset for this parameter as below:
> Select ID, Name from AgencyTable UNION Select 0,'All'. Then In the
> Report Parameters dialogue,Choose available value from query,make All
> as default value), then the first part of where clause will always
> automatically satisfied unless you choose a different value.
> This works great for me, Hope this helps.
> Good luck.
> Henry
>
> Brian wrote:
> > I have a hierarchy of organizations that I need to be able to
filter
> by
> > in the reports...
> >
> > For example, an Agency can have many Applications associated with
it.
> >
> > I have three stored procedures, one that gets a list of Agencies,
the
> > other that gets a list of Applications based on the Agency that was
> > selected, and the third takes a bunch of other parameters and gets
> all
> > the matching orders (or whatever) associated with the Agency and
> > Application (Agency and Application are both input params to the
> third
> > stored proc).
> >
> > What I really want is for the user to be able to not select an
> Agency,
> > basically setting it to Null, and letting the stored procedure that
> > does the query for the report ignore that parameter and get results
> for
> > all agencies.
> >
> > If I set the parameter in the report to null, then I can't have a
> drop
> > down list with the Agency names if the user doesn't want to set the
> > Agency to null. But I can't have both!!!
> >
> > What is the best procedure for doing this? Returning a -1 record
in
> > the list of Agencies and using that to indicate null within the
> stored
> > proc? Any other ideas? I can't return a null record in the stored
> > proc because the report throws an exception. Does this make sense?
> > Any help would be appreciated!!! Thanks, Brian

Parameters!FiledName in Custom Code

Hi All,
How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
within in Custom Code.
Public Shared Function GetParameterText() As String
Return Parameters!vcReportVersion.Value
End Function
This function gives the following error message :
"There is an error on line 37 of custom code: [BC30469] Reference to a
non-shared member requires an object reference."
What is the Object name that contain Parameter collection?
Thanks,
SamYou'll need to pass it as a parameter to the custom code.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:uCAAkJBZEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
> within in Custom Code.
> Public Shared Function GetParameterText() As String
> Return Parameters!vcReportVersion.Value
> End Function
> This function gives the following error message :
> "There is an error on line 37 of custom code: [BC30469] Reference to a
> non-shared member requires an object reference."
> What is the Object name that contain Parameter collection?
> Thanks,
> Sam
>

parameters with multi value and navigation

Hello.

I have a report with parameters include multiple value.

I configure "Jamp To Report" under navigation and I would like to forward to a new reports the same parameters user select in the source report undel multiple value parameters.

in the text box I use =join(Parameters!Ds.Label ,", ") but how can I forward the selected parameters to the new report

Thanks

Idan

Please read this related posting: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163803&SiteID=1

-- Robert

parameters with multi value and navigation

Hello.
I have a report with parameters include multiple value.
I configure "Jamp To Report" under navigation and I would like to
forward to a new reports the same parameters user select in the source
report undel multiple value parameters.
in the text box I use =join(Parameters!Ds.Label ,", ") but how can I
forward the selected parameters to the new report
Thanks
IdanHi,
The parameter used in both the query should be same.
Amarnath
"Idan" wrote:
> Hello.
> I have a report with parameters include multiple value.
> I configure "Jamp To Report" under navigation and I would like to
> forward to a new reports the same parameters user select in the source
> report undel multiple value parameters.
>
> in the text box I use =join(Parameters!Ds.Label ,", ") but how can I
> forward the selected parameters to the new report
> Thanks
> Idan
>

parameters show previous value on second execution of report

I'm running a crystal report from c#.net application, taking parameters from textboxes on the screen, using the parameters to do the data selection, and then printing the parameters in the heading of the report.

The first time I run the report, it displays properly, with the correct info in the heading. If I change the parameters and execute it again, the data on the report changes, but the heading still shows the original parameters that I entered.

Has anyone else encountered a problem like this? Or can you suggest a solution?

Thanks,
ChrisPost the code you are using for displaying the report.|||I solved this already by using a trick that fixes another crystal problem with the navigation end page button: I commented the code in the navigate method, and called my PopulateReport method from the page load event.

I don't know why this works, but it solved the problem.

Parameters Question! Help Please.

So I have created 6 or 7 drop down lists using seperate datasets for each
drop down and the value field is an integer value and the label field is a
text value. The dropdowns all are set to have a datatype of integer and
allow null value is checked. I also have default value on all of the drop
downs set to 0 which is the ALL value. Now the issue, how can I add them to
the where clause in the data view screen? As soon as I add them to the where
clause and ALL (0) is selected there is no way for me to select all values
back. I don't want all the fields to be manditory, that's why I added the
ALL(0) option. I know I could probably write a stored procedure to do this,
but I was wondering if anyone else has found a way around the issue. Thanks,
all help is greatly appreciatedI'm not sure I understand completely. But here's what I do.
(I use oracle, hope you can translate)
I always add a "All" to my parameters list (suppose the parameter name is
parameter1):
Select value, label from parameters
union
Select -1, 'All' from dual
In the data-queries I than write:
Select ...
from table1
where
(table1.xxx = :parameter1 or :parameter1 = -1)
(oracle uses ":" to denote a parameter, in SQLServer that is a "?" I think)
"Eddie J" wrote:
> So I have created 6 or 7 drop down lists using seperate datasets for each
> drop down and the value field is an integer value and the label field is a
> text value. The dropdowns all are set to have a datatype of integer and
> allow null value is checked. I also have default value on all of the drop
> downs set to 0 which is the ALL value. Now the issue, how can I add them to
> the where clause in the data view screen? As soon as I add them to the where
> clause and ALL (0) is selected there is no way for me to select all values
> back. I don't want all the fields to be manditory, that's why I added the
> ALL(0) option. I know I could probably write a stored procedure to do this,
> but I was wondering if anyone else has found a way around the issue. Thanks,
> all help is greatly appreciated|||Thanks for your response, but in oracle does -1 return everything for that
column? I need All to actually return all? It looks like to me that -1
would just return what ever text is associated to the value -1. Thanks
again, I appreciate your help.
"Antoon" wrote:
> I'm not sure I understand completely. But here's what I do.
> (I use oracle, hope you can translate)
> I always add a "All" to my parameters list (suppose the parameter name is
> parameter1):
> Select value, label from parameters
> union
> Select -1, 'All' from dual
> In the data-queries I than write:
> Select ...
> from table1
> where
> (table1.xxx = :parameter1 or :parameter1 = -1)
> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I think)
> "Eddie J" wrote:
> > So I have created 6 or 7 drop down lists using seperate datasets for each
> > drop down and the value field is an integer value and the label field is a
> > text value. The dropdowns all are set to have a datatype of integer and
> > allow null value is checked. I also have default value on all of the drop
> > downs set to 0 which is the ALL value. Now the issue, how can I add them to
> > the where clause in the data view screen? As soon as I add them to the where
> > clause and ALL (0) is selected there is no way for me to select all values
> > back. I don't want all the fields to be manditory, that's why I added the
> > ALL(0) option. I know I could probably write a stored procedure to do this,
> > but I was wondering if anyone else has found a way around the issue. Thanks,
> > all help is greatly appreciated|||I too use this technique. It does not have to be -1, what it should be to
prevent confusion is a value that does not exist in your data. If it is a
text field I use the word All instead of -1. Here is the key point, you
have an or. The first part of the or statement is looking for a specific
value, the next is seeing if the parameter = this particular value
(table1.xxx = :parameter1 or :parameter1 = -1)
Note the different position the parameter is put (whether it is to the left
or right of the equal size).
Give it a try, it works great.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> Thanks for your response, but in oracle does -1 return everything for that
> column? I need All to actually return all? It looks like to me that -1
> would just return what ever text is associated to the value -1. Thanks
> again, I appreciate your help.
> "Antoon" wrote:
>> I'm not sure I understand completely. But here's what I do.
>> (I use oracle, hope you can translate)
>> I always add a "All" to my parameters list (suppose the parameter name is
>> parameter1):
>> Select value, label from parameters
>> union
>> Select -1, 'All' from dual
>> In the data-queries I than write:
>> Select ...
>> from table1
>> where
>> (table1.xxx = :parameter1 or :parameter1 = -1)
>> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
>> think)
>> "Eddie J" wrote:
>> > So I have created 6 or 7 drop down lists using seperate datasets for
>> > each
>> > drop down and the value field is an integer value and the label field
>> > is a
>> > text value. The dropdowns all are set to have a datatype of integer
>> > and
>> > allow null value is checked. I also have default value on all of the
>> > drop
>> > downs set to 0 which is the ALL value. Now the issue, how can I add
>> > them to
>> > the where clause in the data view screen? As soon as I add them to the
>> > where
>> > clause and ALL (0) is selected there is no way for me to select all
>> > values
>> > back. I don't want all the fields to be manditory, that's why I added
>> > the
>> > ALL(0) option. I know I could probably write a stored procedure to do
>> > this,
>> > but I was wondering if anyone else has found a way around the issue.
>> > Thanks,
>> > all help is greatly appreciated|||Bruce, Antoon,
Thank you so much for you help, this seems to be a great start, but I'm
still running into some issues. Here is the way it looks with 3 parameters
w/ 1 dropdown:
WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=@.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
but as soon as I add another dropdown to the where clause like:
And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
the dataview automatically changes the where clause to look like this:
WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=@.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
(pj.BrandManagerID = @.ABM)
OR
(pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
Which would never give me the results I'm look for. Any ideas? Thanks
again, I'm in learning mode with reporting services.
"Bruce L-C [MVP]" wrote:
> I too use this technique. It does not have to be -1, what it should be to
> prevent confusion is a value that does not exist in your data. If it is a
> text field I use the word All instead of -1. Here is the key point, you
> have an or. The first part of the or statement is looking for a specific
> value, the next is seeing if the parameter = this particular value
> (table1.xxx = :parameter1 or :parameter1 = -1)
> Note the different position the parameter is put (whether it is to the left
> or right of the equal size).
> Give it a try, it works great.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > Thanks for your response, but in oracle does -1 return everything for that
> > column? I need All to actually return all? It looks like to me that -1
> > would just return what ever text is associated to the value -1. Thanks
> > again, I appreciate your help.
> >
> > "Antoon" wrote:
> >
> >> I'm not sure I understand completely. But here's what I do.
> >> (I use oracle, hope you can translate)
> >>
> >> I always add a "All" to my parameters list (suppose the parameter name is
> >> parameter1):
> >> Select value, label from parameters
> >> union
> >> Select -1, 'All' from dual
> >>
> >> In the data-queries I than write:
> >> Select ...
> >> from table1
> >> where
> >> (table1.xxx = :parameter1 or :parameter1 = -1)
> >>
> >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> >> think)
> >>
> >> "Eddie J" wrote:
> >>
> >> > So I have created 6 or 7 drop down lists using seperate datasets for
> >> > each
> >> > drop down and the value field is an integer value and the label field
> >> > is a
> >> > text value. The dropdowns all are set to have a datatype of integer
> >> > and
> >> > allow null value is checked. I also have default value on all of the
> >> > drop
> >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> >> > them to
> >> > the where clause in the data view screen? As soon as I add them to the
> >> > where
> >> > clause and ALL (0) is selected there is no way for me to select all
> >> > values
> >> > back. I don't want all the fields to be manditory, that's why I added
> >> > the
> >> > ALL(0) option. I know I could probably write a stored procedure to do
> >> > this,
> >> > but I was wondering if anyone else has found a way around the issue.
> >> > Thanks,
> >> > all help is greatly appreciated
>
>|||try using "(pj.InitiationDate between @.StartDate AND (@.EndDate + 1))" instead
of the => and <= expression
--
"Everyone knows something you don't know"
"Eddie J" wrote:
> Bruce, Antoon,
> Thank you so much for you help, this seems to be a great start, but I'm
> still running into some issues. Here is the way it looks with 3 parameters
> w/ 1 dropdown:
> WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
> but as soon as I add another dropdown to the where clause like:
> And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
> the dataview automatically changes the where clause to look like this:
> WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
> (pj.BrandManagerID = @.ABM)
> OR
> (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
> AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
> Which would never give me the results I'm look for. Any ideas? Thanks
> again, I'm in learning mode with reporting services.
>
> "Bruce L-C [MVP]" wrote:
> > I too use this technique. It does not have to be -1, what it should be to
> > prevent confusion is a value that does not exist in your data. If it is a
> > text field I use the word All instead of -1. Here is the key point, you
> > have an or. The first part of the or statement is looking for a specific
> > value, the next is seeing if the parameter = this particular value
> >
> > (table1.xxx = :parameter1 or :parameter1 = -1)
> >
> > Note the different position the parameter is put (whether it is to the left
> > or right of the equal size).
> >
> > Give it a try, it works great.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> > news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > > Thanks for your response, but in oracle does -1 return everything for that
> > > column? I need All to actually return all? It looks like to me that -1
> > > would just return what ever text is associated to the value -1. Thanks
> > > again, I appreciate your help.
> > >
> > > "Antoon" wrote:
> > >
> > >> I'm not sure I understand completely. But here's what I do.
> > >> (I use oracle, hope you can translate)
> > >>
> > >> I always add a "All" to my parameters list (suppose the parameter name is
> > >> parameter1):
> > >> Select value, label from parameters
> > >> union
> > >> Select -1, 'All' from dual
> > >>
> > >> In the data-queries I than write:
> > >> Select ...
> > >> from table1
> > >> where
> > >> (table1.xxx = :parameter1 or :parameter1 = -1)
> > >>
> > >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> > >> think)
> > >>
> > >> "Eddie J" wrote:
> > >>
> > >> > So I have created 6 or 7 drop down lists using seperate datasets for
> > >> > each
> > >> > drop down and the value field is an integer value and the label field
> > >> > is a
> > >> > text value. The dropdowns all are set to have a datatype of integer
> > >> > and
> > >> > allow null value is checked. I also have default value on all of the
> > >> > drop
> > >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> > >> > them to
> > >> > the where clause in the data view screen? As soon as I add them to the
> > >> > where
> > >> > clause and ALL (0) is selected there is no way for me to select all
> > >> > values
> > >> > back. I don't want all the fields to be manditory, that's why I added
> > >> > the
> > >> > ALL(0) option. I know I could probably write a stored procedure to do
> > >> > this,
> > >> > but I was wondering if anyone else has found a way around the issue.
> > >> > Thanks,
> > >> > all help is greatly appreciated
> >
> >
> >|||David thanks for you help, but unfortunately it doesn't help? I believe the
engine in the background of the Dataview is re-working my sql query for some
reason. So this works fine (perfect):
(pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division =@.division OR @.Division = 0)
but as soon as I add this to the where clause
and (pj.BrandManagerID = @.ABM or @.ABM = 0) then engine reworks my query to
something that doesn't work:) Like this:
(pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division =@.division OR @.Division = 0) AND (pj.BrandManagerID = @.ABM) OR
(pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division =@.division OR @.Division = 0) AND (@.ABM = 0)
Any help is greatly appreciated! I am working on just creating a stored
procedure to do all this work, but it sure would be good to know if there is
a way to do it with out a stored procedure. Thanks in advance.
Eddie
"David Bienstock" wrote:
> try using "(pj.InitiationDate between @.StartDate AND (@.EndDate + 1))" instead
> of the => and <= expression
> --
> "Everyone knows something you don't know"
>
> "Eddie J" wrote:
> > Bruce, Antoon,
> > Thank you so much for you help, this seems to be a great start, but I'm
> > still running into some issues. Here is the way it looks with 3 parameters
> > w/ 1 dropdown:
> > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
> > but as soon as I add another dropdown to the where clause like:
> > And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
> > the dataview automatically changes the where clause to look like this:
> > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
> > (pj.BrandManagerID = @.ABM)
> > OR
> > (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
> > AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
> > Which would never give me the results I'm look for. Any ideas? Thanks
> > again, I'm in learning mode with reporting services.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I too use this technique. It does not have to be -1, what it should be to
> > > prevent confusion is a value that does not exist in your data. If it is a
> > > text field I use the word All instead of -1. Here is the key point, you
> > > have an or. The first part of the or statement is looking for a specific
> > > value, the next is seeing if the parameter = this particular value
> > >
> > > (table1.xxx = :parameter1 or :parameter1 = -1)
> > >
> > > Note the different position the parameter is put (whether it is to the left
> > > or right of the equal size).
> > >
> > > Give it a try, it works great.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> > > news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > > > Thanks for your response, but in oracle does -1 return everything for that
> > > > column? I need All to actually return all? It looks like to me that -1
> > > > would just return what ever text is associated to the value -1. Thanks
> > > > again, I appreciate your help.
> > > >
> > > > "Antoon" wrote:
> > > >
> > > >> I'm not sure I understand completely. But here's what I do.
> > > >> (I use oracle, hope you can translate)
> > > >>
> > > >> I always add a "All" to my parameters list (suppose the parameter name is
> > > >> parameter1):
> > > >> Select value, label from parameters
> > > >> union
> > > >> Select -1, 'All' from dual
> > > >>
> > > >> In the data-queries I than write:
> > > >> Select ...
> > > >> from table1
> > > >> where
> > > >> (table1.xxx = :parameter1 or :parameter1 = -1)
> > > >>
> > > >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> > > >> think)
> > > >>
> > > >> "Eddie J" wrote:
> > > >>
> > > >> > So I have created 6 or 7 drop down lists using seperate datasets for
> > > >> > each
> > > >> > drop down and the value field is an integer value and the label field
> > > >> > is a
> > > >> > text value. The dropdowns all are set to have a datatype of integer
> > > >> > and
> > > >> > allow null value is checked. I also have default value on all of the
> > > >> > drop
> > > >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> > > >> > them to
> > > >> > the where clause in the data view screen? As soon as I add them to the
> > > >> > where
> > > >> > clause and ALL (0) is selected there is no way for me to select all
> > > >> > values
> > > >> > back. I don't want all the fields to be manditory, that's why I added
> > > >> > the
> > > >> > ALL(0) option. I know I could probably write a stored procedure to do
> > > >> > this,
> > > >> > but I was wondering if anyone else has found a way around the issue.
> > > >> > Thanks,
> > > >> > all help is greatly appreciated
> > >
> > >
> > >|||I know what you're saying but I think if you try
(pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division =@.division OR @.Division = 0) it may not re-work it
"Eddie J" wrote:
> David thanks for you help, but unfortunately it doesn't help? I believe the
> engine in the background of the Dataview is re-working my sql query for some
> reason. So this works fine (perfect):
> (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => @.division OR @.Division = 0)
> but as soon as I add this to the where clause
> and (pj.BrandManagerID = @.ABM or @.ABM = 0) then engine reworks my query to
> something that doesn't work:) Like this:
> (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => @.division OR @.Division = 0) AND (pj.BrandManagerID = @.ABM) OR
> (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => @.division OR @.Division = 0) AND (@.ABM = 0)
> Any help is greatly appreciated! I am working on just creating a stored
> procedure to do all this work, but it sure would be good to know if there is
> a way to do it with out a stored procedure. Thanks in advance.
> Eddie
>
> "David Bienstock" wrote:
> > try using "(pj.InitiationDate between @.StartDate AND (@.EndDate + 1))" instead
> > of the => and <= expression
> > --
> > "Everyone knows something you don't know"
> >
> >
> > "Eddie J" wrote:
> >
> > > Bruce, Antoon,
> > > Thank you so much for you help, this seems to be a great start, but I'm
> > > still running into some issues. Here is the way it looks with 3 parameters
> > > w/ 1 dropdown:
> > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
> > > but as soon as I add another dropdown to the where clause like:
> > > And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
> > > the dataview automatically changes the where clause to look like this:
> > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
> > > (pj.BrandManagerID = @.ABM)
> > > OR
> > > (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
> > > AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
> > > Which would never give me the results I'm look for. Any ideas? Thanks
> > > again, I'm in learning mode with reporting services.
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > I too use this technique. It does not have to be -1, what it should be to
> > > > prevent confusion is a value that does not exist in your data. If it is a
> > > > text field I use the word All instead of -1. Here is the key point, you
> > > > have an or. The first part of the or statement is looking for a specific
> > > > value, the next is seeing if the parameter = this particular value
> > > >
> > > > (table1.xxx = :parameter1 or :parameter1 = -1)
> > > >
> > > > Note the different position the parameter is put (whether it is to the left
> > > > or right of the equal size).
> > > >
> > > > Give it a try, it works great.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> > > > news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > > > > Thanks for your response, but in oracle does -1 return everything for that
> > > > > column? I need All to actually return all? It looks like to me that -1
> > > > > would just return what ever text is associated to the value -1. Thanks
> > > > > again, I appreciate your help.
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > >> I'm not sure I understand completely. But here's what I do.
> > > > >> (I use oracle, hope you can translate)
> > > > >>
> > > > >> I always add a "All" to my parameters list (suppose the parameter name is
> > > > >> parameter1):
> > > > >> Select value, label from parameters
> > > > >> union
> > > > >> Select -1, 'All' from dual
> > > > >>
> > > > >> In the data-queries I than write:
> > > > >> Select ...
> > > > >> from table1
> > > > >> where
> > > > >> (table1.xxx = :parameter1 or :parameter1 = -1)
> > > > >>
> > > > >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> > > > >> think)
> > > > >>
> > > > >> "Eddie J" wrote:
> > > > >>
> > > > >> > So I have created 6 or 7 drop down lists using seperate datasets for
> > > > >> > each
> > > > >> > drop down and the value field is an integer value and the label field
> > > > >> > is a
> > > > >> > text value. The dropdowns all are set to have a datatype of integer
> > > > >> > and
> > > > >> > allow null value is checked. I also have default value on all of the
> > > > >> > drop
> > > > >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> > > > >> > them to
> > > > >> > the where clause in the data view screen? As soon as I add them to the
> > > > >> > where
> > > > >> > clause and ALL (0) is selected there is no way for me to select all
> > > > >> > values
> > > > >> > back. I don't want all the fields to be manditory, that's why I added
> > > > >> > the
> > > > >> > ALL(0) option. I know I could probably write a stored procedure to do
> > > > >> > this,
> > > > >> > but I was wondering if anyone else has found a way around the issue.
> > > > >> > Thanks,
> > > > >> > all help is greatly appreciated
> > > >
> > > >
> > > >|||David, I did try it but received the same results. Thanks for you help!
"David Bienstock" wrote:
> I know what you're saying but I think if you try
> (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => @.division OR @.Division = 0) it may not re-work it
> "Eddie J" wrote:
> > David thanks for you help, but unfortunately it doesn't help? I believe the
> > engine in the background of the Dataview is re-working my sql query for some
> > reason. So this works fine (perfect):
> > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > @.division OR @.Division = 0)
> > but as soon as I add this to the where clause
> > and (pj.BrandManagerID = @.ABM or @.ABM = 0) then engine reworks my query to
> > something that doesn't work:) Like this:
> >
> > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > @.division OR @.Division = 0) AND (pj.BrandManagerID = @.ABM) OR
> > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > @.division OR @.Division = 0) AND (@.ABM = 0)
> >
> > Any help is greatly appreciated! I am working on just creating a stored
> > procedure to do all this work, but it sure would be good to know if there is
> > a way to do it with out a stored procedure. Thanks in advance.
> > Eddie
> >
> >
> > "David Bienstock" wrote:
> >
> > > try using "(pj.InitiationDate between @.StartDate AND (@.EndDate + 1))" instead
> > > of the => and <= expression
> > > --
> > > "Everyone knows something you don't know"
> > >
> > >
> > > "Eddie J" wrote:
> > >
> > > > Bruce, Antoon,
> > > > Thank you so much for you help, this seems to be a great start, but I'm
> > > > still running into some issues. Here is the way it looks with 3 parameters
> > > > w/ 1 dropdown:
> > > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
> > > > but as soon as I add another dropdown to the where clause like:
> > > > And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
> > > > the dataview automatically changes the where clause to look like this:
> > > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
> > > > (pj.BrandManagerID = @.ABM)
> > > > OR
> > > > (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
> > > > AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
> > > > Which would never give me the results I'm look for. Any ideas? Thanks
> > > > again, I'm in learning mode with reporting services.
> > > >
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > I too use this technique. It does not have to be -1, what it should be to
> > > > > prevent confusion is a value that does not exist in your data. If it is a
> > > > > text field I use the word All instead of -1. Here is the key point, you
> > > > > have an or. The first part of the or statement is looking for a specific
> > > > > value, the next is seeing if the parameter = this particular value
> > > > >
> > > > > (table1.xxx = :parameter1 or :parameter1 = -1)
> > > > >
> > > > > Note the different position the parameter is put (whether it is to the left
> > > > > or right of the equal size).
> > > > >
> > > > > Give it a try, it works great.
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> > > > > news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > > > > > Thanks for your response, but in oracle does -1 return everything for that
> > > > > > column? I need All to actually return all? It looks like to me that -1
> > > > > > would just return what ever text is associated to the value -1. Thanks
> > > > > > again, I appreciate your help.
> > > > > >
> > > > > > "Antoon" wrote:
> > > > > >
> > > > > >> I'm not sure I understand completely. But here's what I do.
> > > > > >> (I use oracle, hope you can translate)
> > > > > >>
> > > > > >> I always add a "All" to my parameters list (suppose the parameter name is
> > > > > >> parameter1):
> > > > > >> Select value, label from parameters
> > > > > >> union
> > > > > >> Select -1, 'All' from dual
> > > > > >>
> > > > > >> In the data-queries I than write:
> > > > > >> Select ...
> > > > > >> from table1
> > > > > >> where
> > > > > >> (table1.xxx = :parameter1 or :parameter1 = -1)
> > > > > >>
> > > > > >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> > > > > >> think)
> > > > > >>
> > > > > >> "Eddie J" wrote:
> > > > > >>
> > > > > >> > So I have created 6 or 7 drop down lists using seperate datasets for
> > > > > >> > each
> > > > > >> > drop down and the value field is an integer value and the label field
> > > > > >> > is a
> > > > > >> > text value. The dropdowns all are set to have a datatype of integer
> > > > > >> > and
> > > > > >> > allow null value is checked. I also have default value on all of the
> > > > > >> > drop
> > > > > >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> > > > > >> > them to
> > > > > >> > the where clause in the data view screen? As soon as I add them to the
> > > > > >> > where
> > > > > >> > clause and ALL (0) is selected there is no way for me to select all
> > > > > >> > values
> > > > > >> > back. I don't want all the fields to be manditory, that's why I added
> > > > > >> > the
> > > > > >> > ALL(0) option. I know I could probably write a stored procedure to do
> > > > > >> > this,
> > > > > >> > but I was wondering if anyone else has found a way around the issue.
> > > > > >> > Thanks,
> > > > > >> > all help is greatly appreciated
> > > > >
> > > > >
> > > > >|||In the querie pannel there is an icon (two windows and a pencil), select it.
It will give you a plain text window to write your query and VS will not
interfere
"Eddie J" wrote:
> David, I did try it but received the same results. Thanks for you help!
> "David Bienstock" wrote:
> > I know what you're saying but I think if you try
> > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > @.division OR @.Division = 0) it may not re-work it
> >
> > "Eddie J" wrote:
> >
> > > David thanks for you help, but unfortunately it doesn't help? I believe the
> > > engine in the background of the Dataview is re-working my sql query for some
> > > reason. So this works fine (perfect):
> > > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > > @.division OR @.Division = 0)
> > > but as soon as I add this to the where clause
> > > and (pj.BrandManagerID = @.ABM or @.ABM = 0) then engine reworks my query to
> > > something that doesn't work:) Like this:
> > >
> > > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > > @.division OR @.Division = 0) AND (pj.BrandManagerID = @.ABM) OR
> > > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > > @.division OR @.Division = 0) AND (@.ABM = 0)
> > >
> > > Any help is greatly appreciated! I am working on just creating a stored
> > > procedure to do all this work, but it sure would be good to know if there is
> > > a way to do it with out a stored procedure. Thanks in advance.
> > > Eddie
> > >
> > >
> > > "David Bienstock" wrote:
> > >
> > > > try using "(pj.InitiationDate between @.StartDate AND (@.EndDate + 1))" instead
> > > > of the => and <= expression
> > > > --
> > > > "Everyone knows something you don't know"
> > > >
> > > >
> > > > "Eddie J" wrote:
> > > >
> > > > > Bruce, Antoon,
> > > > > Thank you so much for you help, this seems to be a great start, but I'm
> > > > > still running into some issues. Here is the way it looks with 3 parameters
> > > > > w/ 1 dropdown:
> > > > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
> > > > > but as soon as I add another dropdown to the where clause like:
> > > > > And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
> > > > > the dataview automatically changes the where clause to look like this:
> > > > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
> > > > > (pj.BrandManagerID = @.ABM)
> > > > > OR
> > > > > (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
> > > > > AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
> > > > > Which would never give me the results I'm look for. Any ideas? Thanks
> > > > > again, I'm in learning mode with reporting services.
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > I too use this technique. It does not have to be -1, what it should be to
> > > > > > prevent confusion is a value that does not exist in your data. If it is a
> > > > > > text field I use the word All instead of -1. Here is the key point, you
> > > > > > have an or. The first part of the or statement is looking for a specific
> > > > > > value, the next is seeing if the parameter = this particular value
> > > > > >
> > > > > > (table1.xxx = :parameter1 or :parameter1 = -1)
> > > > > >
> > > > > > Note the different position the parameter is put (whether it is to the left
> > > > > > or right of the equal size).
> > > > > >
> > > > > > Give it a try, it works great.
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> > > > > > news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > > > > > > Thanks for your response, but in oracle does -1 return everything for that
> > > > > > > column? I need All to actually return all? It looks like to me that -1
> > > > > > > would just return what ever text is associated to the value -1. Thanks
> > > > > > > again, I appreciate your help.
> > > > > > >
> > > > > > > "Antoon" wrote:
> > > > > > >
> > > > > > >> I'm not sure I understand completely. But here's what I do.
> > > > > > >> (I use oracle, hope you can translate)
> > > > > > >>
> > > > > > >> I always add a "All" to my parameters list (suppose the parameter name is
> > > > > > >> parameter1):
> > > > > > >> Select value, label from parameters
> > > > > > >> union
> > > > > > >> Select -1, 'All' from dual
> > > > > > >>
> > > > > > >> In the data-queries I than write:
> > > > > > >> Select ...
> > > > > > >> from table1
> > > > > > >> where
> > > > > > >> (table1.xxx = :parameter1 or :parameter1 = -1)
> > > > > > >>
> > > > > > >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> > > > > > >> think)
> > > > > > >>
> > > > > > >> "Eddie J" wrote:
> > > > > > >>
> > > > > > >> > So I have created 6 or 7 drop down lists using seperate datasets for
> > > > > > >> > each
> > > > > > >> > drop down and the value field is an integer value and the label field
> > > > > > >> > is a
> > > > > > >> > text value. The dropdowns all are set to have a datatype of integer
> > > > > > >> > and
> > > > > > >> > allow null value is checked. I also have default value on all of the
> > > > > > >> > drop
> > > > > > >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> > > > > > >> > them to
> > > > > > >> > the where clause in the data view screen? As soon as I add them to the
> > > > > > >> > where
> > > > > > >> > clause and ALL (0) is selected there is no way for me to select all
> > > > > > >> > values
> > > > > > >> > back. I don't want all the fields to be manditory, that's why I added
> > > > > > >> > the
> > > > > > >> > ALL(0) option. I know I could probably write a stored procedure to do
> > > > > > >> > this,
> > > > > > >> > but I was wondering if anyone else has found a way around the issue.
> > > > > > >> > Thanks,
> > > > > > >> > all help is greatly appreciated
> > > > > >
> > > > > >
> > > > > >

Parameters multi values

I need to select more of a value in the parameters
How'
--
TomasLook here:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/search?q=multi+value+parameters
Also:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=2E882C0A-8D2B-4EAD-81BE-8E66C0941A18
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"tomas ch" <tomasch@.discussions.microsoft.com> wrote in message
news:D6D73EE8-83A4-45D4-B802-836945C0C543@.microsoft.com...
>I need to select more of a value in the parameters
> How'
>
> --
> Tomas

Parameters in Reporting Services...

Is it possible to control a parameter type as being single vs. multi-valued
based on the value selected in the previous parameter?
Thank you
Ramdaskeep it multi - but only show one value to select if previous parameter
selection so indicates.
"Ram" wrote:
> Is it possible to control a parameter type as being single vs. multi-valued
> based on the value selected in the previous parameter?
> Thank you
> Ramdas|||Hi,
Thanks for the tip. How would i show only one value based on the previous
parameter selection.
Thank you
Ramdas
"Jimbo" wrote:
> keep it multi - but only show one value to select if previous parameter
> selection so indicates.
>
>
> "Ram" wrote:
> > Is it possible to control a parameter type as being single vs. multi-valued
> > based on the value selected in the previous parameter?
> >
> > Thank you
> > Ramdas|||use a stored procedure to populate your select list - one of the parameters
for this stored procedure would indicate whether the return list will be
multiple records or a single record
this parameter would be set by user selection before being passed to the
stored procedure
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:60367FB5-4886-40A2-85F8-A08AF9A938E2@.microsoft.com...
> Hi,
> Thanks for the tip. How would i show only one value based on the previous
> parameter selection.
> Thank you
> Ramdas
> "Jimbo" wrote:
>> keep it multi - but only show one value to select if previous parameter
>> selection so indicates.
>>
>>
>> "Ram" wrote:
>> > Is it possible to control a parameter type as being single vs.
>> > multi-valued
>> > based on the value selected in the previous parameter?
>> >
>> > Thank you
>> > Ramdas|||Is it possible to modify the XML code at runtime? I want to control the
report parameter properties multi-value setting of True/False during runtime
in the XML behind the RDL file. Set it to True if I want the parameters to be
multi-value or False for single-value. This is determined based on the value
selected in parameter one. Parameter one and two are City,State.
If City is selected in parameter one then I want Parameter two to be a
single-valued list, if State is chosen in Parameter One then I want the list
in Parameter two to be a multi-valued select list.
Any ideas or guidance would be appreciated.
"Jim" wrote:
> use a stored procedure to populate your select list - one of the parameters
> for this stored procedure would indicate whether the return list will be
> multiple records or a single record
> this parameter would be set by user selection before being passed to the
> stored procedure
>
>
>
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:60367FB5-4886-40A2-85F8-A08AF9A938E2@.microsoft.com...
> > Hi,
> > Thanks for the tip. How would i show only one value based on the previous
> > parameter selection.
> >
> > Thank you
> > Ramdas
> >
> > "Jimbo" wrote:
> >
> >> keep it multi - but only show one value to select if previous parameter
> >> selection so indicates.
> >>
> >>
> >>
> >>
> >> "Ram" wrote:
> >>
> >> > Is it possible to control a parameter type as being single vs.
> >> > multi-valued
> >> > based on the value selected in the previous parameter?
> >> >
> >> > Thank you
> >> > Ramdas
>
>

Parameters in Page Header

I am trying to put my parameters in my page header.It do works fine.I am using a textbox to hold the value of my parameters.I use

= Join(Parameters!resourceParams.Label, ", ")

in my textbox expression.But my problem is,when there are too many values specified(because i am using a dropdown with multi select) the page header,overlaps the body of my report. The page header's text overlap the contents of my report body.What could be the reason or work around for this? Thanks guys

Make your text box the maximum height and width you want it to have and than select can grow = false..

If you keep the text left align.. No one will know that its very big.

Parameters in named queries

Hi,

I try to build a named query that would either take a parameter or run a procedure to get a value.

The idea is to have a named query used by the cube partition. The named query must limit the lower boud time item according to a complex logic build in a stored procedure.

First I am not sure if it will work even if I can get the named query to behave this way, however I would rather avoid to alter the view behind the cube.

If possible, how can I create a parameter in a named query, something like this in pseudo-code

? = (EXEC up_FirstCrawl_Qtr)
SELECT *
FROM dbo.V_ST_Crawl
WHERE (RSD_QRTR = ?)

Or

SELECT *
FROM dbo.V_ST_Crawl
WHERE (RSD_QRTR = (EXEC up_FirstCrawl_Qtr) )

Thanks,

Philippe

Not to answer your question directly, but I think you would find this very useful.

Project REAL just released entire set of scripts and packages and whole lot of other stuff you can use to create your data warehouse and Analysis Services cubes.

I think this is great material and you should be able to find answers to many of your questions there:

Here is the link: http://www.microsoft.com/downloads/thankyou.aspx?familyId=b61a37b6-5852-4018-bba9-795a34123ed0&displayLang=en&oRef=

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Woowh, Great.
I always wanted to get more from RealProject than abstracts.
Looks like the real meat is there now, 240MB or so of it.

I am sure going to spend a week-end my wife will remember :-)

In the meantime and to cope with management deadline, I will create another view. I am under the gun.

I always wanted to avoid providing things that work now but require more maintenance, however it is not always possible. problem is that management sees value only in brand new stuff never done before.
No budget nor time is allowed to re-create "working" existing legacy stuff in a much better way, but, they complain when the stuff is down for maintenance... The Chicken or the Egg? No both please.

Have a nice week-end

Philippe

parameters default value remains "Query based" in spite of setting

Hello,
In my report manager I am not able to set a default value for a report
parameter, because the default value is indicated to be "query based".
I do not seem to be able to change that whatever I do. Surely I tried
setting the report parameters to "non-queried" and "none".
The default values I set in report designer also do not show in the
deployed report. (Even though other changes do.)
Anybody some ideas? Thanks.In Solution Explorer, right-click on the Project and choose Properties. Change OverwriteDataSources
to True and redeploy.
Optionally, you can use Report Manager (http://yourserver/reports) to do this by navigating to the
report you want to modify, clicking the Properties tab and selecting Data Sources on the left. You
can them make your changes on the server.
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul vO" <info@.vanoordt.nl> wrote in message
news:200e5c1b.0408230349.39c8c5d0@.posting.google.com...
> Hello,
> In my report manager I am not able to set a default value for a report
> parameter, because the default value is indicated to be "query based".
> I do not seem to be able to change that whatever I do. Surely I tried
> setting the report parameters to "non-queried" and "none".
> The default values I set in report designer also do not show in the
> deployed report. (Even though other changes do.)
> Anybody some ideas? Thanks.