Datasets and iff statements

Hello experts,

I have a report with four different datasets that slice certain record by different days of the week. I can do the individual summing for each dataset by putting a , "datasetname" at in the expression. I also have 4 matrixes in the report that are grouped on certain transactions within each timeslice. My problem is that if I use the dataset name in the fields, the groups get ignored. Do I have to put the dataset name in each group expression? for example, here is my group expression...

Code Snippet

=iif(Left(Fields!TestName.Value,4)="VTAM","VTAM Logon",

iif(Left(Fields!TestName.Value,4)="CICS","VTAM Logon",

iif(Left(Fields!TestName.Value,4)="Shaw","SHAW Main Menu",

iif(Left(Fields!TestName.Value,7)="Inquiry","Inquiry Menu",

iif(Left(Fields!TestName.Value,6)="Search","Search Menu",

iif(Left(Fields!TestName.Value,6)="SEAX A","SEAX Auxiliary Search Menu",

iif(Left(Fields!TestName.Value,8)="SEAX - B","SEAX",

iif(Left(Fields!TestName.Value,4)="STLN","STLN","Other Value"))))))))

Not sure where I put the dataset name in this.

Thanks,

C

Answered myself. Choose the data set in the matrix properties. Leave the expression alone.

Dataset query with alias column and allow searches

I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.
In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)

Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet

If InStr(Trim(searchClientNameText), "%")>0 Then
searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"
Else
searchStatement = "WHERE ClientName = @.searchClientNameText"
End If

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
searchStatement

........
End Function

(1) You would search on each of the columns that comprise the "ClientName".
searchValue = "%" & searchvalue & "%"

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
WHERE
([People].[Given_Name] IS NULL OR [People].[Given_Name] LIKE @.searchvalue)
OR ([People].[Family_Name] IS NULL OR [People].[Family_Name] LIKE @.searchvalue)
OR ( [Bus].[Organisation_Name] IS NULL OR [Bus].[Organisation_Name] LIKE @.searchvalue)
(2) You should be using Parameterized Query instead of hardcoding the values into the SQL Statement to prefent your sever from SQL Injection attack. Google for more info.

|||I don't see any reason why you should not go for a stored procedure for this type of situation. I would highly recommend that.
Thanks

database_name.table_name in create table ?

Hi,
In create table statements we use database_name.table_name.
Eg :
create table my_database.test_table(sno int ....)
What is the meaning of this ? Is that, the table is created in the specified database_name ?
Please clarify.
Thanks,
SamOf course ... But the correct form include owner information as below ...

CREATE TABLE DBName.OwnerName.TableName (ID int)

Bye.|||Can't I give

create table db_name.table_name

( omitting the owner name ) ?|||No. You can use CREATE TABLE db_name..table_name.

The owner will default to whoever is running the CREATE TABLE statement. If the are db_owner, it will default to dbo.

Database Tuning Advisor question (against sql server 2000 trace fi

Hi,
I caught all RPC:Completed, SP:StmtCompleted, and SQL:BatchCompleted events
that occured in the system (based on prepared statements, so that's why I
included statement level of events) during one day, and I ran DTA against
this trace file.
Surprisingly it ran only 5 minutes, regardless of the fact that there is
around 700,000 events (real sql statements) in the trace file and that I did
not limit tuning time. When I reviewed session summary, I noticed that it
reported only 8,553 events in the workload, number of events tuned 8,553, and
number of statements tuned 517!
What the heck happened there? Again, I did not limit tuning time, and after
running it against 3 different trace files, each time it ran and reported
running time of only 5 minutes.
Does anybody have an idea what's going on, and how to resolve it?
Thanks,
Pedja
Do you see anything in the event log? Did you get any recommendation
back? What kind of workload is this?
An event represents a batch,a stored procedure or a single statement.
Statement represents the tuneable query. A batch or SP can potentially
map to multiple statements.However it can also be "non-tuneable"
example select@.@. version
Manoj
On Jan 13, 12:48 pm, Pedja <P...@.discussions.microsoft.com> wrote:
> Hi,
> I caught all RPC:Completed, SP:StmtCompleted, and SQL:BatchCompleted events
> that occured in the system (based on prepared statements, so that's why I
> included statement level of events) during one day, and I ran DTA against
> this trace file.
> Surprisingly it ran only 5 minutes, regardless of the fact that there is
> around 700,000 events (real sql statements) in the trace file and that I did
> not limit tuning time. When I reviewed session summary, I noticed that it
> reported only 8,553 events in the workload, number of events tuned 8,553, and
> number of statements tuned 517!
> What the heck happened there? Again, I did not limit tuning time, and after
> running it against 3 different trace files, each time it ran and reported
> running time of only 5 minutes.
> Does anybody have an idea what's going on, and how to resolve it?
> Thanks,
> Pedja
|||I got recommendation back, but it was based on extreemly small sample of the
trace. Therefore I couldn't accept its results... I did analysis of the
trace, and from around 700,000 events, around 100,000 were tunable (select
statements against database tables). So I still didn't figure out what
happened there.
Workload is whatever happened on the system that day, application is based
on prepared statements...
"_manoj@.yahoo.com" wrote:

> Do you see anything in the event log? Did you get any recommendation
> back? What kind of workload is this?
> An event represents a batch,a stored procedure or a single statement.
> Statement represents the tuneable query. A batch or SP can potentially
> map to multiple statements.However it can also be "non-tuneable"
> example select@.@. version
> Manoj
> On Jan 13, 12:48 pm, Pedja <P...@.discussions.microsoft.com> wrote:
>
|||Scoping down to your actual post
"Surprisingly it ran only 5 minutes, regardless of the fact that there
is
around 700,000 events (real sql statements) in the trace file and that
I did
not limit tuning time. When I reviewed session summary, I noticed that
it
reported only 8,553 events in the workload, number of events tuned
8,553, and
number of statements tuned 517! "
a) Did you get any recommendation with this case (not the sample you
mention in the previous post).If so what is the expected percentage
improvement?
b) Did you get any errors in the tuning log?
c) Are you pointing to the right database for workload analysis (via
the user interface option)?
d) Are you choosing the right databases to tune?
The behavior you mention is not abnormal - DTA relies on the right
database context and if this is incorrect DTA might not do any useful
work. Also if your workload comprises vaild non-tuneable statements
(select @.@.version ; select SERVER_PROPERTY(...)) etc it can scan thru
fast and hence the question about the nature of the workload
Thanks
Manoj