non-required parameter

I using BI development studio to create some reports and one of the stored procedures that I have takes in 4 parameters.

ManagerID int =0
OfficeID int =0
StartDate datetime
EndDate datetime

In the report designer I use 2 other sql queries to populate ManagerID fields and OfficeID Fields.

The report will work if the customer selects both ManagerID and OfficeID fields but will not work if Manager OR OfficeID fields are select. They both have to be selected or an error message will be displayed stating that the non-selected field must have a value.

The the parameters properties section I have changed the type is integer and select allowed NULLs, I have even set the default value to 0 this didn't work. Then I tried changing the type from integer to string and selected 'allowed blanks'.

I still get the popup message stating that the non-select item must have a value.

Is there a way I can select 1 and not the other?

why not to try to add those drop down list to the web page not in the report and pass the selected parameters to the report file

Sample code

Private Sub SetReportParameters()
Dim userid As New ReportParameter("userid",
Security.GetUserID().ToString())
Dim p() As ReportParameter = {userid}
ReportViewer1.ServerReport.SetParameters(p)
End Sub

http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-1/

|||

Tthank you for the reply.

One thing I should have mentioned is that we are using ColdFusion 7 with SQL 2005.
I am still learning a few things about it.
If will try passing in those parameters but I am not sure how it will work with this structure.

|||

I don't think there is any problem but i cam through great web cast for this hope it helps you

SQL Server Reporting Services for Cold Fusion Developers
https://www119.livemeeting.com/cc/mseventsbmo/view?id=1032256029&role=attend&pw=7933C65D

|||

Thank you for the link, I have been looking for this video for awhile and all I got was a word document of the webcast.

I will be watching it soon.

I will mark this as answered.

Non-queried parameter giving 'Invalid Column Name' error

I have created a non-queried boolean parameter called HideOptionalColumns.
This field doesn't exist in my query result set. I use this value in the
Hidden attribute of the Visiblity property for the column to hide or display
certain columns based on the user's runtime selection. It works fine when I
View Report in VS - the columns are hiddden/displayed appropriately. But
when I run the report, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'myDset'.
Invalid column name 'HideOptionalColumns'.
This seems to imply that it does need that parameter in the result set
itself. But that configuration doesn't work since the rows are then filtered
out based on that parameter. Is there another way to accomplish this hiding
of columns?
Thank you!On Apr 30, 4:04 pm, marian <mar...@.discussions.microsoft.com> wrote:
> I have created a non-queried boolean parameter called HideOptionalColumns.
Instead of using a parameter, you could place another field/textbox
right in your table or matrix. For each column you want to hide, set
the Hide option to trigger from the field or text box you add. I use
this a lot and it works without any problems.
> This field doesn't exist in my query result set. I use this value in the
> Hidden attribute of the Visiblity property for the column to hide or display
> certain columns based on the user's runtime selection. It works fine when I
> View Report in VS - the columns are hiddden/displayed appropriately. But
> when I run the report, I get the following error:
> An error has occurred during report processing.
> Query execution failed for data set 'myDset'.
> Invalid column name 'HideOptionalColumns'.
> This seems to imply that it does need that parameter in the result set
> itself. But that configuration doesn't work since the rows are then filtered
> out based on that parameter. Is there another way to accomplish this hiding
> of columns?
> Thank you!sql

Non-clustered index on a field and a "%" sign

Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.Not necessarily. It does mean that a scan will be done, not a seek. Here's
a case where it will scan the NC index:
1) You have a NC index on ColA.
2) You run: SELECT ColA FROM MyTable WHERE ColA LIKE '%XYZ'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<almurph@.altavista.com> wrote in message
news:a8035a0d-7d2f-4ea4-9394-fc3b9b3c6247@.w34g2000hsg.googlegroups.com...
Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.|||"almurph@.altavista.com" wrote:
> Folks,
> I'm not sure, but if you have a non-clustered index on a field and
> you have "%" character as the first character of the search parameter
> does this bypass the index? In other words, do you need a certain
> amount of characters before the % sign in SQL Server 2000?
> Any comments/knowledge much appreciated,
> Thanks,
> Al.
It disqualifies the seeking or partial scanning of the index. But as
mentioned by Tom, a full nonclustered index scan could still be used.
If you know that you will always have the % sign preceding the text, and
never after the text, then you could consider storing the data in
REVERSE order, or create a computed column on the REVERSE value, index
that, and use that column in your query.
For example: SELECT * FROM my_table WHERE reversed_domain LIKE
REVERSE('%.altavista.com')
--
Gert-Jan

NOLOCK sentence

Hello !!

I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ?? I mean, I don't like to lock any table for selects.

Is It possible ?? How to do It (step by step) ?

Thanks !!You can set transaction isolation level for your connection:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED|||Choosing to not use locking in inherantly dangerous. It means that you can have all kinds of strange problems due to interactions with other spids (users) that can be impossible to diagnose because they are impossible to recreate.

If you want to desend into the madness, all you need to do is:SET TRANSACTION LEVEL READ_UNCOMMITTED (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp) Be sure to read Customizing Locking (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_27cc.asp) AND all of the sections under it before you do this!

-PatP|||I wouldn't exaggerate the dangers of dirty reads. In a busy OLTP database there are hundreds of calls made to static tables and there is NO NEED to allow default READ COMMITTED behavior. Of course, when a DML is relying on a SELECT, then this should be seriously taken into account. Personally, I wouldn't use the SET statement to control transaction isolation level. It takes less than 10 characters to explicitly state how you want the data to be accessed by using table hints.|||From my perspective, I don't exaggerate the dangers of dirty reads. I see them as a serious problem that lots of people overlook because allowing dirty reads is easier than solving the underlying problems in their code.

While there are reasons that dirty reads are necessary, and many cases where they are convenient, I feel very strongly that dirty reads are both dangerous and overused.

Most of the time when I encounter dirty reads, it is because of financial statements that don't balance consistantly in production systems, but almost always balance in test. The test system may only have a simulated load of 50 or 100 users, which may not be enough to cause the report to be unbalanced. It will never cause it to be unbalanced the same way twice. This leads to lots and lots of hair loss if someone doesn't think to check the locking to see that the developer specified that the statements don't need balance!

-PatP|||Pat, financial statements need to be generated when there is no activity going on against the period for which the statement has been requested. Otherwise, dirty or not, you won't be able to balance it anyway.|||Pat, financial statements need to be generated when there is no activity going on against the period for which the statement has been requested. Otherwise, dirty or not, you won't be able to balance it anyway.In the ideal world that is true, but I can't even get a vistor's pass for there anymore! I have to live and code in the real world.

You are correct that final statements need to be done after the period is closed, but working statements are generated from shortly after the period starts until sometime after the period ends. When the bean counters get a statement that doesn't balance, they don't think about why it might not balance, they just scream that it doesn't. You can explain to them, and they understand that "work papers" might not always balance, but those papers have the same format/appearance as final papers and that can make the users crazy.

In some ways, this is a training issue. The users need to realize that a statement for YE 12/31 isn't complete on 06/18, and if they stopped to think about it they'd know that it wasn't complete, but that still doesn't make them willing to excuse a statement that doesn't balance as of the time it was run.

I can control what the developers do (to some extent). I have little or no control over what the users do. I fix the problems where I can.

-PatP|||I wash windows...

And string up my dbas who use (NOLOCK)

EDIT: And if you want, why not pin the code tables?

EDIT2: And if you notice they want to do that for the ENTIRE db...

WOW...holy corruption bartman!|||I think I must continue using NOLOCK clause !|||Well, I think it's more an app design issue rather than users training or NOLOCK. App design will be reconciled with db design which should have the same set of business rules as the foundation, just like the app design must. And all this results from a sound system analysis where each data/info flow is accounted for and projected onto app/db design...But, as you said, - that's an ideal world, and "they" don't pay us enough to dedicate several years of our lives to creating one :(|||The application development finished, and they are having locking problems, and I can't change it !|||Doesn't sound finished to me...

If they have that many problems...you can bet the wheels are gonna fly right off when you change the ISOLATION LEVEL

They may be complaining now...soon they will be blaimng you and the database for screwing up the data

Oh

MOO|||The application development finished, and they are having locking problems, and I can't change it !Have they finished, or are they having locking problems? The two are mutually exclusive, they can't have both.

Changing the locking level would be what we call a "Class 2 CLM (Career Limiting Move)". It might not get you fired, but whether it does or not you'll wish that it had!

I think that life is too short to volunteer to sign up for that kind of problems.

-PatP|||Thanks everybody !