Filter by a concatenated column?

I am using a query in a stored procedure, where the user can dictate which
field they want to sort by. That I can do. What I also want to do is
filter out the null values for the selected field, and one of the fields is
a concatenated result. This is the query:
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
so the stored procedure would be something like this:
CREATE PROCEDURE [dbo].ClientSearch]
@.SearchField as varchar(50)='' AS
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
where @.SearchField is not null order by @.SearchField
If the user is selecting FullName, I get an error saying the @.SearchField is
an invalid column name -- however this works if other field names are
selected, and it also works if I only want to order by FullName, but not
filter.
Thanks for your help.I hope you are using exec since its a dyamic query.
fullname is a concatenationo of two fields LName + ', ' + FName
therefore LName + ', ' + FName is not null does not work.
make sure that LName is not null and FNAME is not NULL
HTH
Rajesh Peddireddy.
"news.microsoft.com" wrote:

> I am using a query in a stored procedure, where the user can dictate which
> field they want to sort by. That I can do. What I also want to do is
> filter out the null values for the selected field, and one of the fields i
s
> a concatenated result. This is the query:
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> so the stored procedure would be something like this:
> CREATE PROCEDURE [dbo].ClientSearch]
> @.SearchField as varchar(50)='' AS
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> where @.SearchField is not null order by @.SearchField
> If the user is selecting FullName, I get an error saying the @.SearchField
is
> an invalid column name -- however this works if other field names are
> selected, and it also works if I only want to order by FullName, but not
> filter.
> Thanks for your help.
>
>

Filter and Sort Priority

I have a report with a category that filters for "top N" categories, but it is preventing the entire data set from being evaluated so that the series subtotals are incorrect. Is there a way to change the precedence, so that the subtotals are computed across the entire data set, and the "top N" is evaluated afterwards?

I attempted to solve this problem, or work around it, by having all relevant computation performed in the data source (which required dynamic sql with nested selects and window functions). Even with the data perfectly arranged and sorted in advance, the "TopN" feature of reporting services STILL managed to screw up the results. I've concluded that "TopN" is broken.

However, I found a useable work around. I added still another 'select' layer on my datasource with a dense_rank() function, and then used its result in the filter expressions of the relevant 'category.' Problem solved.

|||

I ran into the same problem and I found that this could be resolved though Reporting Services by adding the following to the group's visibility expression:

=IIF(RUNNINGVALUE(Fields!User.Value,COUNTDISTINCT,"table1_Domain")<11,False,True)

This report is counting top users of a web site that stores hits in a database table which is group by domain, then user.

In the report, I created a field: COUNT(Fields!User.Value). The inner group is then is sorted by this field (descending).

So, the logic in this expression is: Every time the user name changes it keeps that in the running total. The running total is reset when the group above it (the user's domain) changes. IIF the running total is less than 11, then Hidden = False.

So, while all values may still be processed, it only shows the top 10.

Hope this helps others

BTW, there is one drawback - since I'm using an expression for the visibility, I can't make this a drill-down field (since drill-down is also a function of visibility). If I select the "Visibility can be toggled by another report item", the report still displays as expected when first rendered, but if it's collapsed then expanded, ALL values for the group will appear, not just the top 10. I guess the visibility expression is only process at initial report rendering time, and not each time the group is collapsed/expanded.

|||

Okay, I'm a but dense today - there is another way to do this. The above will guarantee that ONLY 10 values are returned, but if you remove the sort, and use only the filter for TopN, you'll get the top 10 VALUES (some may duplicate). So, the above solution may return

DOMAIN Logon Count

~~~~~~~~~~~~~~~~~~~~~~~~

Domain1

UserA 10

UserB 10

UserC 9

........etc...upto

UserJ 2

Domain2

etc

However, using only TopN (no sort), may return

Domain1

UserA 10

....

UserJ 2

UserK 2

UserL 2

Domain2

etc

So, TopN by itself (without Sort) can return more than 10 values because there are multiples of the last entry with the same value. My solution in the previous posting depends on Sort, and simply hides everything after the 10th entry.

If someone else finds a way to list just the first 10 entries in a sorted list, please post. I don't like my solution too much because I can't further drill-down into the entries.

sql

Filter and Sort Priority

I have a report with a category that filters for "top N" categories, but it is preventing the entire data set from being evaluated so that the series subtotals are incorrect. Is there a way to change the precedence, so that the subtotals are computed across the entire data set, and the "top N" is evaluated afterwards?

I attempted to solve this problem, or work around it, by having all relevant computation performed in the data source (which required dynamic sql with nested selects and window functions). Even with the data perfectly arranged and sorted in advance, the "TopN" feature of reporting services STILL managed to screw up the results. I've concluded that "TopN" is broken.

However, I found a useable work around. I added still another 'select' layer on my datasource with a dense_rank() function, and then used its result in the filter expressions of the relevant 'category.' Problem solved.

|||

I ran into the same problem and I found that this could be resolved though Reporting Services by adding the following to the group's visibility expression:

=IIF(RUNNINGVALUE(Fields!User.Value,COUNTDISTINCT,"table1_Domain")<11,False,True)

This report is counting top users of a web site that stores hits in a database table which is group by domain, then user.

In the report, I created a field: COUNT(Fields!User.Value). The inner group is then is sorted by this field (descending).

So, the logic in this expression is: Every time the user name changes it keeps that in the running total. The running total is reset when the group above it (the user's domain) changes. IIF the running total is less than 11, then Hidden = False.

So, while all values may still be processed, it only shows the top 10.

Hope this helps others

BTW, there is one drawback - since I'm using an expression for the visibility, I can't make this a drill-down field (since drill-down is also a function of visibility). If I select the "Visibility can be toggled by another report item", the report still displays as expected when first rendered, but if it's collapsed then expanded, ALL values for the group will appear, not just the top 10. I guess the visibility expression is only process at initial report rendering time, and not each time the group is collapsed/expanded.

|||

Okay, I'm a but dense today - there is another way to do this. The above will guarantee that ONLY 10 values are returned, but if you remove the sort, and use only the filter for TopN, you'll get the top 10 VALUES (some may duplicate). So, the above solution may return

DOMAIN Logon Count

~~~~~~~~~~~~~~~~~~~~~~~~

Domain1

UserA 10

UserB 10

UserC 9

........etc...upto

UserJ 2

Domain2

etc

However, using only TopN (no sort), may return

Domain1

UserA 10

....

UserJ 2

UserK 2

UserL 2

Domain2

etc

So, TopN by itself (without Sort) can return more than 10 values because there are multiples of the last entry with the same value. My solution in the previous posting depends on Sort, and simply hides everything after the 10th entry.

If someone else finds a way to list just the first 10 entries in a sorted list, please post. I don't like my solution too much because I can't further drill-down into the entries.

file import to SQL Server 2000

Hi all,

I have a file with an extension of .sdf. I "believe" it is a text file
of some sort but I am uncertain. The source agency hasn't returned any
of my calls so I'm wondering if anyone is familiar with this extension?
I'd like to import the file into my database - when I use DTS and chose
a text format, regardless of what delimiter I choose, the format is
still really ugly. when I pull it up in a huge text editor, it is hard
for me to tell what it is there.

I saw in one of my searches that it could be a comma delimited (it's
not) .. could be a unisys file? I know it's not much information to go
on - but where should I start in trying to get this into my database
without knowing the format? Any suggestions would be greatly
appreciated.

Thanks!
Bethany

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!A google search turned up

SDF = Windows SQL CE-version internal databases
SDF = MDL Isis SDF chemical modeller input file
SDF = Mime: application/e-score
SDF = Mime: chemical/x-mdl-sdf
SDF = Source Definition File (Sourcer)
SDF = System Data Format file (fixed lenght ascii text)

> know it's not much information to go on -
> but where should I start in trying to get this into my database
> without knowing the format?

All you can do is make assumptions and try to identify the file format via
trial-and-error. You might get help with identifying/eliminating SQL CE
from the microsoft.public.sqlserver.ce.

Once you've positively identified the file format, you can take steps to
either import directly or at least transform data into an intermediate
format suitable for import.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Bethany Holliday" <bhollida@.iupui.edu> wrote in message
news:418a7210$0$14507$c397aba@.news.newsgroups.ws.. .
> Hi all,
> I have a file with an extension of .sdf. I "believe" it is a text file
> of some sort but I am uncertain. The source agency hasn't returned any
> of my calls so I'm wondering if anyone is familiar with this extension?
> I'd like to import the file into my database - when I use DTS and chose
> a text format, regardless of what delimiter I choose, the format is
> still really ugly. when I pull it up in a huge text editor, it is hard
> for me to tell what it is there.
> I saw in one of my searches that it could be a comma delimited (it's
> not) .. could be a unisys file? I know it's not much information to go
> on - but where should I start in trying to get this into my database
> without knowing the format? Any suggestions would be greatly
> appreciated.
> Thanks!
> Bethany
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!