Partial searches over a lot of fields

Hi All,
I have the following scenario. I have a table called Invoice. This
has around 30 columns of which i have to do a retrieval based on
filter conditions on 10 columns. These filters need to be partial
searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
'Aaron', now i should be able to search the customer as 'ar' and it
should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
on which this like '%x%' search has to be done and there will
practically be hudreds of thousands of rows. can anybody suggest me to
improve the performance of such a query. Currently what i am thinkin
of is
select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
'%' and etc.
P.S. am using ASP.Net as the front end.ArunPrakash (arunprakashb@.yahoo.com) writes:
> I have the following scenario. I have a table called Invoice. This
> has around 30 columns of which i have to do a retrieval based on
> filter conditions on 10 columns. These filters need to be partial
> searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
> 'Aaron', now i should be able to search the customer as 'ar' and it
> should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
> on which this like '%x%' search has to be done and there will
> practically be hudreds of thousands of rows. can anybody suggest me to
> improve the performance of such a query. Currently what i am thinkin
> of is
> select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
> '%' and etc.

You can use SELECT TOP or SET ROWCOUNT to restrict the number of rows
returned. A good value is probably 2000. If you get 2000 rows, you tell
the user to refine his conditions.

Also keep in mind, that there could be a great difference in performance
when searching for names that start with 'Ar', or have 'ar' anywhere in
the name. If the search column is indexed, that index can be used
for the case "starts with", but not "contains".

There are also a couple of considerations of how to compose the query
to make the search effective. You may be interested in the article
http://www.sommarskog.se/dyn-search.html on my web site.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Parameters with all values as default

Hi all.
Have a report with 2 parameters StartDate and SessionName. Both are non-query
based. So, the user runs the report and has to enter values in both fields in
order to run the report. I would like to have by default to have the report
run all values like as in (select * from etc) and then if user decides to
hone in on any StartDate/Session then they can used the parameter fields on
the report and then click view report.
Thanks
James
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1You'll need to allow Null values for each parameter. There's an example on
www.msbicentral.com. If you login and go to Downloads, Reporting Services,
RDL files, the example is Matrix.Param.RDL. There are other useful examples
there, also.
"James Woo via SQLMonster.com" wrote:
> Hi all.
> Have a report with 2 parameters StartDate and SessionName. Both are non-query
> based. So, the user runs the report and has to enter values in both fields in
> order to run the report. I would like to have by default to have the report
> run all values like as in (select * from etc) and then if user decides to
> hone in on any StartDate/Session then they can used the parameter fields on
> the report and then click view report.
> Thanks
> James
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
>|||COOL SITE.. Thanks. I will have to spend hours there.
daw wrote:
>You'll need to allow Null values for each parameter. There's an example on
>www.msbicentral.com. If you login and go to Downloads, Reporting Services,
>RDL files, the example is Matrix.Param.RDL. There are other useful examples
>there, also.
>> Hi all.
>[quoted text clipped - 7 lines]
>> Thanks
>> James
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Easiest way to do this...
Create a new dataset for your Parameter:
e.g
SELECT Product,NAME
FROM Products
UNION
SELECT '***' AS Product,'All Managers' AS Name
This creates a *** as the ALL for products...
Then in your main report dataset...
Make sure you are using the generic query designer (button above the query)
Then as your statement:
WHERE ( Product = @.ProductParam or @.ProductParam='***')
Good luck
"James Woo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:52B3D2CE3710C@.SQLMonster.com...
> COOL SITE.. Thanks. I will have to spend hours there.
> daw wrote:
> >You'll need to allow Null values for each parameter. There's an example
on
> >www.msbicentral.com. If you login and go to Downloads, Reporting
Services,
> >RDL files, the example is Matrix.Param.RDL. There are other useful
examples
> >there, also.
> >
> >> Hi all.
> >>
> >[quoted text clipped - 7 lines]
> >> Thanks
> >> James
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

Parameters question

I am trying to create a set of parameters that will alow me to select data
either based on criteria from dropdown boxes or from a text box. In my
DataSet I have something like this (simpified)
if @.ItemDesc is null
select citemno, cdescript, 0.0 as price from icitem
where ....
else
select citemno, cdescript, 0.0 as price from icitem
where cdescript like @.ItemDesc
@.ItemDesc is a value from a text box. When I run the report it keeps
prompting me to enter a value into the first dropdown.
Thanks for help.On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am trying to create a set of parameters that will alow me to select data
> either based on criteria from dropdown boxes or from a text box. In my
> DataSet I have something like this (simpified)
> if @.ItemDesc is null
> select citemno, cdescript, 0.0 as price from icitem
> where ....
> else
> select citemno, cdescript, 0.0 as price from icitem
> where cdescript like @.ItemDesc
> @.ItemDesc is a value from a text box. When I run the report it keeps
> prompting me to enter a value into the first dropdown.
> Thanks for help.
You could have a default value in the drop-down box of an empty
string. That should avoid the prompt, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||And to do that I need to select default values non-queried?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
> On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am trying to create a set of parameters that will alow me to select
>> data
>> either based on criteria from dropdown boxes or from a text box. In my
>> DataSet I have something like this (simpified)
>> if @.ItemDesc is null
>> select citemno, cdescript, 0.0 as price from icitem
>> where ....
>> else
>> select citemno, cdescript, 0.0 as price from icitem
>> where cdescript like @.ItemDesc
>> @.ItemDesc is a value from a text box. When I run the report it keeps
>> prompting me to enter a value into the first dropdown.
>> Thanks for help.
>
> You could have a default value in the drop-down box of an empty
> string. That should avoid the prompt, etc. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 27, 6:37 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> And to do that I need to select default values non-queried?
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
> > On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> >> I am trying to create a set of parameters that will alow me to select
> >> data
> >> either based on criteria from dropdown boxes or from a text box. In my
> >> DataSet I have something like this (simpified)
> >> if @.ItemDesc is null
> >> select citemno, cdescript, 0.0 as price from icitem
> >> where ....
> >> else
> >> select citemno, cdescript, 0.0 as price from icitem
> >> where cdescript like @.ItemDesc
> >> @.ItemDesc is a value from a text box. When I run the report it keeps
> >> prompting me to enter a value into the first dropdown.
> >> Thanks for help.
> > You could have a default value in the drop-down box of an empty
> > string. That should avoid the prompt, etc. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
If you want to set the default value to an empty string in the stored
procedure/query that sources the parameter (and order it in the
dataset to show the empty string first) then you can set it as 'from
query.' If you want to just use null and check for the null in the
stored procedure/query then you can set it to non-queried. It depends
on where you want to set the default value. Hope this clarifies it
better.
Regards,
Enrique Martinez
Sr. Software Consultant|||I am setting it up to a non-queried value and I am entering ="" into a
field.
Still asks me to enter parameter when I run it.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182954533.677728.17440@.u2g2000hsc.googlegroups.com...
> On Jun 27, 6:37 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> And to do that I need to select default values non-queried?
>> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
>> > On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> >> I am trying to create a set of parameters that will alow me to select
>> >> data
>> >> either based on criteria from dropdown boxes or from a text box. In my
>> >> DataSet I have something like this (simpified)
>> >> if @.ItemDesc is null
>> >> select citemno, cdescript, 0.0 as price from icitem
>> >> where ....
>> >> else
>> >> select citemno, cdescript, 0.0 as price from icitem
>> >> where cdescript like @.ItemDesc
>> >> @.ItemDesc is a value from a text box. When I run the report it keeps
>> >> prompting me to enter a value into the first dropdown.
>> >> Thanks for help.
>> > You could have a default value in the drop-down box of an empty
>> > string. That should avoid the prompt, etc. Hope this helps.
>> > Regards,
>> > Enrique Martinez
>> > Sr. Software Consultant
>
> If you want to set the default value to an empty string in the stored
> procedure/query that sources the parameter (and order it in the
> dataset to show the empty string first) then you can set it as 'from
> query.' If you want to just use null and check for the null in the
> stored procedure/query then you can set it to non-queried. It depends
> on where you want to set the default value. Hope this clarifies it
> better.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Parameters not working

Hello,
I have created a report based on a stored procedure which uses 13
parameters. The 13 parameter values are mapped to the report parameter
selections (for example: Parameter @.CATEGORY, Value
=Parameters!Category.Value).
When the stored procedure is executed from the 'Data' tab of RS and the
parameters are typed in, the result set is correct.
When the report is executed in 'Preview' tab of RS only the first parameter
is evaluated. The rest of the parameters are not evaluated and the result
set brings everything back regardless of the selections.
Is this a bug or is there a way to enforce report parameter selections to be
passed into the stored procedure?
Thanks in Advance,
JohnParameters are case sensitive. If this is happening to you then something is
wrong with your mapping. I always use the expression builder and select the
report parameter just for this reason.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> Hello,
> I have created a report based on a stored procedure which uses 13
> parameters. The 13 parameter values are mapped to the report parameter
> selections (for example: Parameter @.CATEGORY, Value
> =Parameters!Category.Value).
> When the stored procedure is executed from the 'Data' tab of RS and the
> parameters are typed in, the result set is correct.
> When the report is executed in 'Preview' tab of RS only the first
parameter
> is evaluated. The rest of the parameters are not evaluated and the result
> set brings everything back regardless of the selections.
> Is this a bug or is there a way to enforce report parameter selections to
be
> passed into the stored procedure?
> Thanks in Advance,
> John|||Thanks Bruce,
Actually parameters were selected with expression builder and are correct.
The values for some of these parameters are coming from other stored
procedures. I am wondering if all these values are pulled once prior to
report execution and anything that is changed afterwards is not used.
John K.
===
"Bruce L-C [MVP]" wrote:
> Parameters are case sensitive. If this is happening to you then something is
> wrong with your mapping. I always use the expression builder and select the
> report parameter just for this reason.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John K" <JohnK@.discussions.microsoft.com> wrote in message
> news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > Hello,
> >
> > I have created a report based on a stored procedure which uses 13
> > parameters. The 13 parameter values are mapped to the report parameter
> > selections (for example: Parameter @.CATEGORY, Value
> > =Parameters!Category.Value).
> >
> > When the stored procedure is executed from the 'Data' tab of RS and the
> > parameters are typed in, the result set is correct.
> >
> > When the report is executed in 'Preview' tab of RS only the first
> parameter
> > is evaluated. The rest of the parameters are not evaluated and the result
> > set brings everything back regardless of the selections.
> >
> > Is this a bug or is there a way to enforce report parameter selections to
> be
> > passed into the stored procedure?
> >
> > Thanks in Advance,
> > John
>
>|||The parameters that you see in the toolbar prior to clicking on view report
are what should be getting sent to your stored procedure. One thing to keep
in mind, if the parameters are not changed then the data is cached (in the
development environment) and then next time you preview it uses the cached
data (look where you have your rdl files and you will see
reportname.rdl.data, that is what the .data files are.
I am not really sure what you are seeing but it could be a side affect of
that.
If the parameters are supposed to be done in a certain execution order then
you need to make them cascading parameters. Search books on line for
cascading parameter
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:35EA39B3-D88E-4EE4-973B-D162686C48EA@.microsoft.com...
> Thanks Bruce,
> Actually parameters were selected with expression builder and are correct.
> The values for some of these parameters are coming from other stored
> procedures. I am wondering if all these values are pulled once prior to
> report execution and anything that is changed afterwards is not used.
> John K.
> ===> "Bruce L-C [MVP]" wrote:
> > Parameters are case sensitive. If this is happening to you then
something is
> > wrong with your mapping. I always use the expression builder and select
the
> > report parameter just for this reason.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "John K" <JohnK@.discussions.microsoft.com> wrote in message
> > news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > > Hello,
> > >
> > > I have created a report based on a stored procedure which uses 13
> > > parameters. The 13 parameter values are mapped to the report
parameter
> > > selections (for example: Parameter @.CATEGORY, Value
> > > =Parameters!Category.Value).
> > >
> > > When the stored procedure is executed from the 'Data' tab of RS and
the
> > > parameters are typed in, the result set is correct.
> > >
> > > When the report is executed in 'Preview' tab of RS only the first
> > parameter
> > > is evaluated. The rest of the parameters are not evaluated and the
result
> > > set brings everything back regardless of the selections.
> > >
> > > Is this a bug or is there a way to enforce report parameter selections
to
> > be
> > > passed into the stored procedure?
> > >
> > > Thanks in Advance,
> > > John
> >
> >
> >|||Yes, some of the parameters are cascading.
I tried to change the type from Stored Procedure to Text and typed the
parameters out instead of using the paramter tab (like so: exec usp_x
@.Status=1, @.Project='HELLO') which works fine.
I just need the hardcoded 'HELLO' to be the value selected on the report
parameter which has the same name as the expected stored procedure parameter
(@.Project).
I tried exec usp_x @.Status=1, @.Project=Parameters!Project.Value!
which returns incorrect syntax near '!'
Thanks again.
"Bruce L-C [MVP]" wrote:
> The parameters that you see in the toolbar prior to clicking on view report
> are what should be getting sent to your stored procedure. One thing to keep
> in mind, if the parameters are not changed then the data is cached (in the
> development environment) and then next time you preview it uses the cached
> data (look where you have your rdl files and you will see
> reportname.rdl.data, that is what the .data files are.
> I am not really sure what you are seeing but it could be a side affect of
> that.
> If the parameters are supposed to be done in a certain execution order then
> you need to make them cascading parameters. Search books on line for
> cascading parameter
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John K" <JohnK@.discussions.microsoft.com> wrote in message
> news:35EA39B3-D88E-4EE4-973B-D162686C48EA@.microsoft.com...
> > Thanks Bruce,
> >
> > Actually parameters were selected with expression builder and are correct.
> > The values for some of these parameters are coming from other stored
> > procedures. I am wondering if all these values are pulled once prior to
> > report execution and anything that is changed afterwards is not used.
> >
> > John K.
> >
> > ===> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Parameters are case sensitive. If this is happening to you then
> something is
> > > wrong with your mapping. I always use the expression builder and select
> the
> > > report parameter just for this reason.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "John K" <JohnK@.discussions.microsoft.com> wrote in message
> > > news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > > > Hello,
> > > >
> > > > I have created a report based on a stored procedure which uses 13
> > > > parameters. The 13 parameter values are mapped to the report
> parameter
> > > > selections (for example: Parameter @.CATEGORY, Value
> > > > =Parameters!Category.Value).
> > > >
> > > > When the stored procedure is executed from the 'Data' tab of RS and
> the
> > > > parameters are typed in, the result set is correct.
> > > >
> > > > When the report is executed in 'Preview' tab of RS only the first
> > > parameter
> > > > is evaluated. The rest of the parameters are not evaluated and the
> result
> > > > set brings everything back regardless of the selections.
> > > >
> > > > Is this a bug or is there a way to enforce report parameter selections
> to
> > > be
> > > > passed into the stored procedure?
> > > >
> > > > Thanks in Advance,
> > > > John
> > >
> > >
> > >
>
>|||Corrected the syntax to:
exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
but it still not used in the reporting window.
Also tried: exec usp_X @.Status, @.Project to reference the parameters
directly. Still having problems.
If there are any examples out there please point me that direction.
Thanks.|||When you do a stored procedure do the following steps. I tend to use command
type as text but that is because I tend to go against Sybase a lot (I am
currently creating a datamart and will be doing most of my reporting against
SQL Server). First thing at work tomorrow I'll give you a stored procedure
example with SQL Server. The format
exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
will not work. I am surprised it didn't error out for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:9A94F3C6-54BE-487D-9C43-359F806FE7B9@.microsoft.com...
> Corrected the syntax to:
> exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
> but it still not used in the reporting window.
> Also tried: exec usp_X @.Status, @.Project to reference the parameters
> directly. Still having problems.
> If there are any examples out there please point me that direction.
> Thanks.|||Bruce thanks again,
Unfortunately the attachment did not make it, can you please e-mail it to
yiannino@.hotmail.com?
John K.|||In Outlook express the attachment shows as a paperclip. I know some people
use web based readers and seem to not see attachments. I prefer to do all my
responses here but I will email it to you. If you have additional questions
about the report please just respond here so others can benefit as well.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:60B099C4-5A3F-41FD-B130-24226143E8C5@.microsoft.com...
> Bruce thanks again,
> Unfortunately the attachment did not make it, can you please e-mail it to
> yiannino@.hotmail.com?
> John K.|||Hi Bruce,
Sorry it took a while. I was able to recreate the issue and it seems that
the problem lies with the way stored procedures are evaluated by Reporting
Services in terms of what parameters are used in the preview.
Specifically, the original stored procedure declared a cursor (which
required a select from another table) and then called another stored
procedure within the cursor. Seems that Reporting Services evalutated this
first select statement to decide what parameters would be applicable for the
report.
I changed the stored procedure to have as the first statement the select
(dynamically build) with all the parameters that are being passed in, and
that seems to work.
So is this some type of RS limitation where parameters to be evaluated at
execution time are defined by the first sql statement found/returned?
Is there anything coming in SQL Server 2005 that would allow a Cursor based
on another table to be used?
Any other ideas that would allow me to use the stored procedures as they
exist today instead of having to rewrite them?
Thanks again for all your help,
John

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 an expression

I am trying to write an expression based on a parameter for a text box.
If I write it without the parameter it works fine, but when I add the
parameter, the expression evaluates as just text.
My dataset contains a field called "PeakSessions"
I have a parameter called "chrt", type string, containing the value
"PeakSessions".
=Fields!PeakSessions.Value <= Works fine. Displays the PeakSession
data.
="Fields!" & Parameters!chrt.Value & ".Value"
This parameterized version evaluates to: Fields!PeakSessions.Value.
So it seems the parameter resolved correctly, but my expression is
being evaluated as text. What am I doing wrong?
SQL 2005 Reports Services, SP1, Standard EditionYou are just creating a string. Let's say you were trying to create a string
to display. This is how you would do it. RS will not evaluate the string
which is what you are trying to do. You should be able to do something like
this:
=Fields(Parameters!chrt.Value).Value
Just a guess.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"randscott" <rscott@.plato.com> wrote in message
news:1152724801.223473.94690@.h48g2000cwc.googlegroups.com...
>I am trying to write an expression based on a parameter for a text box.
> If I write it without the parameter it works fine, but when I add the
> parameter, the expression evaluates as just text.
> My dataset contains a field called "PeakSessions"
> I have a parameter called "chrt", type string, containing the value
> "PeakSessions".
> =Fields!PeakSessions.Value <= Works fine. Displays the PeakSession
> data.
> ="Fields!" & Parameters!chrt.Value & ".Value"
> This parameterized version evaluates to: Fields!PeakSessions.Value.
> So it seems the parameter resolved correctly, but my expression is
> being evaluated as text. What am I doing wrong?
> SQL 2005 Reports Services, SP1, Standard Edition
>|||You are right. I was just creating text, but I couldn't figure out the
right way. I had tried something similar to your suggestion
=Fields!(Parameters!chrt.Value).Value but the extra exclamation point
caused me problems. Your way worked. Thanks!
Bruce L-C [MVP] wrote:
> You are just creating a string. Let's say you were trying to create a string
> to display. This is how you would do it. RS will not evaluate the string
> which is what you are trying to do. You should be able to do something like
> this:
> =Fields(Parameters!chrt.Value).Value|||Good. Glad it worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"randscott" <rscott@.plato.com> wrote in message
news:1152729191.900859.300580@.m79g2000cwm.googlegroups.com...
> You are right. I was just creating text, but I couldn't figure out the
> right way. I had tried something similar to your suggestion
> =Fields!(Parameters!chrt.Value).Value but the extra exclamation point
> caused me problems. Your way worked. Thanks!
>
> Bruce L-C [MVP] wrote:
>> You are just creating a string. Let's say you were trying to create a
>> string
>> to display. This is how you would do it. RS will not evaluate the string
>> which is what you are trying to do. You should be able to do something
>> like
>> this:
>> =Fields(Parameters!chrt.Value).Value
>

Parameters for report based on MDX

Hi
Can you pl. help me with something that is supposed to be really simple
however it is frustrating us no end. A client needs a reporting services
report based on an SSAS 2005 cube. He wants to have only two date filters
for the report that are shown in the form of calendars (default of RS report
parameters that are datetime type.) One is a 'From Date' and second is 'To
date'. The report data will be filtered on these two dates. Now to try this
out, I created a very simple report based on a cube. The MDX for the report
is as follows.
/<
SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Stat Can Sales]
Where (
{filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= datevalue('2006/01/01')
and datevalue(Dates.Currentmember.Name) <= datevalue('2007/01/01'))}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>/
AS you notice, I harded coded two dates in this MDX just to get the report
working with dataset and layout. Then I added two report parameters,
FromDate and ToDate of type datetime and ran the report again. Now of course
two calendar controls are shown when the report is previewed though the
parameters are not yet tied with the MDX query. Then I got to the dataset of
this MDX query, selected properties by clicking on the '.' on the toolbar
and in the parameters tab for the dataset, I created two parameters as
follows.
Name Value
pFromDate =Parameters!FromDate.Value
pToDate =Parameters!ToDate.Value
Finally I tried to modify the MDX by replacing the hardcoded date fields
with these parameters as
/<
SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Stat Can Sales]
Where (
{filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= @.
pFromDate
and datevalue(Dates.Currentmember.Name) <= @. pToDate)}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>/
Looks like straightforward thing to do. Right? However I cannot save this
parameterized MDX and get an error. So my question is, how to create and
pass parameters like this to MDX?
I also tried something I found on the net where it was mentioned that one
can start the MDX or a SQL query for that matter with "=" and then have the
rest of it is in double quotes. That way, the processing engine treats it as
an expression and then evaluates it at runtime. So the updated query would
be
/<
= "SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Stat Can Sales]
Where (
{filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= " + Parameters!FromDate.Value +
"and datevalue(Dates.Currentmember.Name) <= " + Paramters!ToDate.Value
+")}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
>/
This does not work either. Basically query editor does not like the starting
"=" itself.
Am I missing something?
ThanksHi Anant
You're second approach is the right one.
First, bin the parameters you defined on the paramter tab of the Dataset
properties dialog.
Then click the query tab. The value of you Querystring text area should be
something along the lines of:
"=Select
Non Empty { [Measures].[Number of copies sold] } ON COLUMNS,
Non Empty { ([Sales].[All Products].[Product Number].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
From
[Stat Can Sales]
Where
(
{
Filter(Dates.[Sales Date].Members, DateValue(Dates.Currentmember.Name) >=" + Parameters!FromDate.Value + " And DateValue(Dates.Currentmember.Name) <=" + Paramters!ToDate.Value +")
}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Bear in mind, you'll always need to run a hard coded version of your query
first to prepare your query.
Joe
"Anant Rege" wrote:
> Hi
>
> Can you pl. help me with something that is supposed to be really simple
> however it is frustrating us no end. A client needs a reporting services
> report based on an SSAS 2005 cube. He wants to have only two date filters
> for the report that are shown in the form of calendars (default of RS report
> parameters that are datetime type.) One is a 'From Date' and second is 'To
> date'. The report data will be filtered on these two dates. Now to try this
> out, I created a very simple report based on a cube. The MDX for the report
> is as follows.
>
> /<
>
> SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
> EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
> PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
>
> FROM [Stat Can Sales]
>
> Where (
> {filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >=> datevalue('2006/01/01')
> and datevalue(Dates.Currentmember.Name) <= datevalue('2007/01/01'))}
> )
> CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>
> >/
>
> AS you notice, I harded coded two dates in this MDX just to get the report
> working with dataset and layout. Then I added two report parameters,
> FromDate and ToDate of type datetime and ran the report again. Now of course
> two calendar controls are shown when the report is previewed though the
> parameters are not yet tied with the MDX query. Then I got to the dataset of
> this MDX query, selected properties by clicking on the '.' on the toolbar
> and in the parameters tab for the dataset, I created two parameters as
> follows.
>
> Name Value
> pFromDate =Parameters!FromDate.Value
> pToDate =Parameters!ToDate.Value
>
> Finally I tried to modify the MDX by replacing the hardcoded date fields
> with these parameters as
>
> /<
>
> SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
> EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
> PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
>
> FROM [Stat Can Sales]
>
> Where (
> {filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= @.
> pFromDate
> and datevalue(Dates.Currentmember.Name) <= @. pToDate)}
> )
> CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>
> >/
>
> Looks like straightforward thing to do. Right? However I cannot save this
> parameterized MDX and get an error. So my question is, how to create and
> pass parameters like this to MDX?
>
> I also tried something I found on the net where it was mentioned that one
> can start the MDX or a SQL query for that matter with "=" and then have the
> rest of it is in double quotes. That way, the processing engine treats it as
> an expression and then evaluates it at runtime. So the updated query would
> be
>
> /<
>
> = "SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
> EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
> PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
>
> FROM [Stat Can Sales]
>
> Where (
> {filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >=> " + Parameters!FromDate.Value +
> "and datevalue(Dates.Currentmember.Name) <= " + Paramters!ToDate.Value
> +")}
> )
> CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
>
>
> >/
>
> This does not work either. Basically query editor does not like the starting
> "=" itself.
>
> Am I missing something?
>
> Thanks
>
>
>
>|||Anant,
If you found a working solution, would you please email me? I have the same problem in trying to pass the parameter to MDX. I am using SSRS on SSAS 2005.
I tried filter and "=" method and have been getting an error on both methods whenever I insert the parameter.
Thanks
John
EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

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.

Parameters and role

Hi all,

I have a report based on a cube with some parameters. So far, everything works fine, but there is one thing I want to improve for user-friendliness. I have a parameter linked to country selection and the roles are also based on countries.

My issue is that even if a user can only access the data of one country, in the drop-down list of the parameters, all the countries are available (even if no data is linked to these countries).

I'm wondering if there is a way to "filter" the parameters selection, to display only the countries useful for a specific user.

Thanks.

Guillaume

I'm not sure whether you can do this using custom code, but...

I use Visual Studio 2005's dropdownlist on my front-end. Basically, just have a SQL table that let's the dropdownlist know which counties the user should be associated with.

I can see how this would be challenging if you only have BIS.

|||

Thanks for your quick reply.

I have only BIDS... There is not an other way to do that? Any other ideas?

Guillaume

Parameters - get last minus one

I have a list of parameters, and they are months. This list is dynamic, and
is based on a query to an OLAP cube.
I want my default parameter to be the last month minus one, so that when the
last month is December, I want to have November as default.
How can I do this?
All help appreciated!
Kaisa M. Lindahljust use other query to get max(month),then use the result to parameter's
default value
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> дÈëÏûÏ¢
news:#sYiAa61EHA.4004@.tk2msftngp13.phx.gbl...
> I have a list of parameters, and they are months. This list is dynamic,
and
> is based on a query to an OLAP cube.
> I want my default parameter to be the last month minus one, so that when
the
> last month is December, I want to have November as default.
> How can I do this?
> All help appreciated!
> Kaisa M. Lindahl
>

Parameterizing page break in a group

We would like to have the Page Break for a group be based on a Yes/No
Parameter that would be a the parameter section of the report. In the
Grouping and sorting options where the "Page Break at End" check box is,
there is no way to enter an expression. Is there any way to do this without
writing code, and/or what would be the code that would make the Page Break
conditional?I think this will work though I haven't tried it. Put a Rectangle in the
bottom of your group footer with PageBreakAtEnd set to true. Then set the
Rectangle.Hidden expression (under visiblility) to =not
cbool(Parameters!PageBreaks.Value) assuming you have a parameter called
PageBreaks.
"Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
news:E730E694-6B20-4A4A-86A8-2BF05EF0DB84@.microsoft.com...
> We would like to have the Page Break for a group be based on a Yes/No
> Parameter that would be a the parameter section of the report. In the
> Grouping and sorting options where the "Page Break at End" check box is,
> there is no way to enter an expression. Is there any way to do this
> without
> writing code, and/or what would be the code that would make the Page Break
> conditional?|||Thanks Bob, we're going to look into it.
"Bob Fisher" wrote:
> I think this will work though I haven't tried it. Put a Rectangle in the
> bottom of your group footer with PageBreakAtEnd set to true. Then set the
> Rectangle.Hidden expression (under visiblility) to =not
> cbool(Parameters!PageBreaks.Value) assuming you have a parameter called
> PageBreaks.
> "Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
> news:E730E694-6B20-4A4A-86A8-2BF05EF0DB84@.microsoft.com...
> > We would like to have the Page Break for a group be based on a Yes/No
> > Parameter that would be a the parameter section of the report. In the
> > Grouping and sorting options where the "Page Break at End" check box is,
> > there is no way to enter an expression. Is there any way to do this
> > without
> > writing code, and/or what would be the code that would make the Page Break
> > conditional?
>
>|||Can anyone verify if this worked?
I tried entering and expression that would evaluate to true or false in the
element, but it did not work:
<PageBreakAtEnd>=CBool(iif(Parameters!GroupBreak1.Value = "true", true,
false))<PageBreakAtEnd>
Thanks!!!!
-Brian
"Alec Hardy" wrote:
> Thanks Bob, we're going to look into it.
> "Bob Fisher" wrote:
> > I think this will work though I haven't tried it. Put a Rectangle in the
> > bottom of your group footer with PageBreakAtEnd set to true. Then set the
> > Rectangle.Hidden expression (under visiblility) to =not
> > cbool(Parameters!PageBreaks.Value) assuming you have a parameter called
> > PageBreaks.
> >
> > "Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
> > news:E730E694-6B20-4A4A-86A8-2BF05EF0DB84@.microsoft.com...
> > > We would like to have the Page Break for a group be based on a Yes/No
> > > Parameter that would be a the parameter section of the report. In the
> > > Grouping and sorting options where the "Page Break at End" check box is,
> > > there is no way to enter an expression. Is there any way to do this
> > > without
> > > writing code, and/or what would be the code that would make the Page Break
> > > conditional?
> >
> >
> >

Parameterized Expression

I am trying to write an expression based on a parameter for a text box.
If I write it without the parameter it works fine, but when I add the
parameter, the expression evaluates as just text.

My dataset contains a field called "PeakSessions"
I have a parameter called "chrt", type string, containing the value
"PeakSessions".

=Fields!PeakSessions.Value <= Works fine. Displays the PeakSession data.

="Fields!" & Parameters!chrt.Value & ".Value"

This parameterized version evaluates to: Fields!PeakSessions.Value.
So it seems the parameter resolved correctly, but my expression is
being evaluated as text. What am I doing wrong?

SQL 2005 Reports Services, SP1, Standard Edition

I don′t think taht this will work, perhaps you might be able to get the data through the object model in custom code. I never tried that for myself, so I ccan not help you with that.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

A user in another forum gave me the correct syntax. I had tried every combination similar to this, but never got it right.

=Fields(Parameters!chrt.Value).Value

|||Cool, yes this is what I ment by object model, but I wasn′t aware that it was that wasy :-D|||

Cool, yes this is what I ment by object model, but I wasn′t aware that it was that easy :-D

Parameterized Connect String

Is it possible to define a datasource which has a dynamic connection string based on a report parameter?

Specifically I'm interested in using the XML data source, but I would like to use one data source for an unlimited number of files (instead of having a separate data source for each xml file). I was thinking that maybe I could just pass a parameter into the Render() web service method and have the connection string substitute the correct filename into the connection string.

Is this possible?

Yes, you can make your connection string an expression (including using parameters) in RS2005. There were some bugs in this area in the June CTP but it should be OK in the final build.

|||How would you write a xml connection string using report parameters?|||

This topic in RS BOL talks about connection strings for xml datasets: http://msdn2.microsoft.com/en-us/library/ms159741.aspx

-- Robert

Parameterized Connect String

Is it possible to define a datasource which has a dynamic connection string based on a report parameter?

Specifically I'm interested in using the XML data source, but I would like to use one data source for an unlimited number of files (instead of having a separate data source for each xml file). I was thinking that maybe I could just pass a parameter into the Render() web service method and have the connection string substitute the correct filename into the connection string.

Is this possible?

Yes, you can make your connection string an expression (including using parameters) in RS2005. There were some bugs in this area in the June CTP but it should be OK in the final build.

|||How would you write a xml connection string using report parameters?|||

This topic in RS BOL talks about connection strings for xml datasets: http://msdn2.microsoft.com/en-us/library/ms159741.aspx

-- Robert