Complex Dataset Filtering

I have an Item Report that lists over 50,000 items, we need to restrict using
one of several different methods.
Some times they will run the report with all items (no Where clause in
the SQL Statement).
Some times they will want to see only the items they call "common"
which are stored in a table (normally I would just do a join to that table).
Lastly, they want to run the report for a specific item (where Item = @.ItemNumber)
I would prefer not to create three different reports.
My first hope was to create all three datasets and then dynamically assigne
the dataset depending on a paramater, I have not been able to accomplish this.
Any suggestion would sure be great.
TerryPiece of cake...well, not at all. I don't have the time to write a specific
answer right now, but I promise that next week I will do it.
By now, I'll copy-paste an answer that I gave in another forum. I hope it
helps you:
My scenario was the following.
I had a parameterized report. There's only one parameter (called @.id_acm)
and the values are taken from a query (in the report>report parameters...
menu I choose "From Query" instead of "non-queried" and so a drop down list
is shown to the user) The source query for the parameter values looked like
this:
SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm
And the query used to get the information for the report (the one I wrote in
the DataSet) looked like this:
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
WHERE acm.id_acm = @.id_acm
I also did NOT specified any default value for the parameter.
When my boss executed the report he noticed that it showed anything until he
selected a value from the drop down list. My boss told me that he wanted to
see all the information by default, and if he needed information about a
specific person he would select the person from the drop down list. Here
comes the tricky part.
I noticed that all the current (and future) values for the id_acm field in
the tbl_acm table were greater than 0.
So I modified the source query for the parameter like this:
SELECT -5 AS param_value, 'Everyone' AS param_label
UNION
SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm
And I specified a default value of -5 for the @.id_acm parameter.
Also, I modified the principal query like this:
IF @.id_acm = -5
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
END ELSE
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
WHERE acm.id_acm = @.id_acm
END
Let me explain this: when the user selects "Everyone" the principal query
returns the information of all the persons (because there's no WHERE clause)
If the user selects a specific name, then the principal query returns only
that person's information (because there's a WHERE clause filtering the data)
By default the paramter value is -5 and so the report shows everyone's
information.
Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In
fact, if you have n parameters you'll have to nest n IF statemens and you'll
get 2^n posibilities for the WHERE clauses (that's the big pain)
I don't know if this helps in your scenario, but I hope it does.|||U Rock! That toally solved my problem.
I tried something simular prior but I really used more of a SQL Notation.
Thanks, I had all but givin up!
"F. Dwarf" wrote:
> Piece of cake...well, not at all. I don't have the time to write a specific
> answer right now, but I promise that next week I will do it.
> By now, I'll copy-paste an answer that I gave in another forum. I hope it
> helps you:
> My scenario was the following.
> I had a parameterized report. There's only one parameter (called @.id_acm)
> and the values are taken from a query (in the report>report parameters...
> menu I choose "From Query" instead of "non-queried" and so a drop down list
> is shown to the user) The source query for the parameter values looked like
> this:
> SELECT id_acm AS param_value, nombre AS param_label
> FROM tbl_acm
> And the query used to get the information for the report (the one I wrote in
> the DataSet) looked like this:
> SELECT *
> FROM tbl_sales S
> INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
> WHERE acm.id_acm = @.id_acm
> I also did NOT specified any default value for the parameter.
> When my boss executed the report he noticed that it showed anything until he
> selected a value from the drop down list. My boss told me that he wanted to
> see all the information by default, and if he needed information about a
> specific person he would select the person from the drop down list. Here
> comes the tricky part.
> I noticed that all the current (and future) values for the id_acm field in
> the tbl_acm table were greater than 0.
> So I modified the source query for the parameter like this:
> SELECT -5 AS param_value, 'Everyone' AS param_label
> UNION
> SELECT id_acm AS param_value, nombre AS param_label
> FROM tbl_acm
> And I specified a default value of -5 for the @.id_acm parameter.
> Also, I modified the principal query like this:
> IF @.id_acm = -5
> BEGIN
> SELECT *
> FROM tbl_sales S
> INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> END ELSE
> BEGIN
> SELECT *
> FROM tbl_sales S
> INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> WHERE acm.id_acm = @.id_acm
> END
> Let me explain this: when the user selects "Everyone" the principal query
> returns the information of all the persons (because there's no WHERE clause)
> If the user selects a specific name, then the principal query returns only
> that person's information (because there's a WHERE clause filtering the data)
> By default the paramter value is -5 and so the report shows everyone's
> information.
> Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In
> fact, if you have n parameters you'll have to nest n IF statemens and you'll
> get 2^n posibilities for the WHERE clauses (that's the big pain)
> I don't know if this helps in your scenario, but I hope it does.|||What I do in my reports is something like
SELECT * FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
WHERE (acm.id_acm = @.id_acm or @.id_acm = -5)
"Terry B" wrote:
> U Rock! That toally solved my problem.
> I tried something simular prior but I really used more of a SQL Notation.
> Thanks, I had all but givin up!
>
> "F. Dwarf" wrote:
> > Piece of cake...well, not at all. I don't have the time to write a specific
> > answer right now, but I promise that next week I will do it.
> > By now, I'll copy-paste an answer that I gave in another forum. I hope it
> > helps you:
> >
> > My scenario was the following.
> > I had a parameterized report. There's only one parameter (called @.id_acm)
> > and the values are taken from a query (in the report>report parameters...
> > menu I choose "From Query" instead of "non-queried" and so a drop down list
> > is shown to the user) The source query for the parameter values looked like
> > this:
> >
> > SELECT id_acm AS param_value, nombre AS param_label
> > FROM tbl_acm
> >
> > And the query used to get the information for the report (the one I wrote in
> > the DataSet) looked like this:
> > SELECT *
> > FROM tbl_sales S
> > INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
> > WHERE acm.id_acm = @.id_acm
> >
> > I also did NOT specified any default value for the parameter.
> >
> > When my boss executed the report he noticed that it showed anything until he
> > selected a value from the drop down list. My boss told me that he wanted to
> > see all the information by default, and if he needed information about a
> > specific person he would select the person from the drop down list. Here
> > comes the tricky part.
> > I noticed that all the current (and future) values for the id_acm field in
> > the tbl_acm table were greater than 0.
> > So I modified the source query for the parameter like this:
> >
> > SELECT -5 AS param_value, 'Everyone' AS param_label
> > UNION
> > SELECT id_acm AS param_value, nombre AS param_label
> > FROM tbl_acm
> >
> > And I specified a default value of -5 for the @.id_acm parameter.
> > Also, I modified the principal query like this:
> >
> > IF @.id_acm = -5
> > BEGIN
> > SELECT *
> > FROM tbl_sales S
> > INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> > END ELSE
> > BEGIN
> > SELECT *
> > FROM tbl_sales S
> > INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> > WHERE acm.id_acm = @.id_acm
> > END
> >
> > Let me explain this: when the user selects "Everyone" the principal query
> > returns the information of all the persons (because there's no WHERE clause)
> > If the user selects a specific name, then the principal query returns only
> > that person's information (because there's a WHERE clause filtering the data)
> > By default the paramter value is -5 and so the report shows everyone's
> > information.
> > Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In
> > fact, if you have n parameters you'll have to nest n IF statemens and you'll
> > get 2^n posibilities for the WHERE clauses (that's the big pain)
> > I don't know if this helps in your scenario, but I hope it does.

Compatibility Level

Clicking "Details" from the drop-down menu that lists all the databases in
the Query Analyzer (in SQL Server 2000) pops out a dialog box where all the
databases are listed along with their Compatibility Level. What is this
Compatibility Level? Does this mean backward compatibility i.e. SQL Server
2000's compatibility with SQL Server 7.0? Also why is the Compatibility
Level for all databases 70 - what does it signify? Why not some number other
than 70?
Installing SQL Server 7.0 along with SQL Server 2000 on the same machine -
is that a sane approach or should such a situation be avoided? What problems
could arise if both versions of SQL Server are installed on the same
machine?
Thanks,
Arpan>> What is this Compatibility Level? <<
It is a DBMS specific internal value, that keep certain database behaviors
to be compatible with the specified DBMS version. The values are 80, 70, 65,
or 60 for SQL 2000, SQL 7.0, SQL 6.5 and SQL 6.0 respectively.
For example when you upgrade SQL Server from version 6.5 to version 7.0, the
compatibility will not get updated. You will have to use the procedure
sp_dbcmptlevel to get it to 70. Without this compatibility level, certain
features specific to SQL 7.0 like TOP clause will not work properly. See
details about sp_dbcmptlevel in SQL Server Books Online.
>> Also why is the Compatibility Level for all databases 70 - what does it
signify? Why not some number other than 70? <<
It simply means, this level is specific to SQL Server version 7.0.
>> Installing SQL Server 7.0 along with SQL Server 2000 on the same
machine - is that a sane approach or should such ... <<
You can keep an installation of SQL Server version 7.0 intact on your
computer and also install a named instance of SQL Server 2000 on the same
computer. Details on how to proceed can be found in SQL Server Books Online.
--
- Anith
( Please reply to newsgroups only )|||Thanks, Anith, for your input. What do you mean by "named instance" of SQL
Server 2000?
Regards,
Arpan
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OuiU2ODlDHA.2244@.TK2MSFTNGP12.phx.gbl...
> >> What is this Compatibility Level? <<
> It is a DBMS specific internal value, that keep certain database behaviors
> to be compatible with the specified DBMS version. The values are 80, 70,
65,
> or 60 for SQL 2000, SQL 7.0, SQL 6.5 and SQL 6.0 respectively.
> For example when you upgrade SQL Server from version 6.5 to version 7.0,
the
> compatibility will not get updated. You will have to use the procedure
> sp_dbcmptlevel to get it to 70. Without this compatibility level, certain
> features specific to SQL 7.0 like TOP clause will not work properly. See
> details about sp_dbcmptlevel in SQL Server Books Online.
> >> Also why is the Compatibility Level for all databases 70 - what does it
> signify? Why not some number other than 70? <<
> It simply means, this level is specific to SQL Server version 7.0.
> >> Installing SQL Server 7.0 along with SQL Server 2000 on the same
> machine - is that a sane approach or should such ... <<
> You can keep an installation of SQL Server version 7.0 intact on your
> computer and also install a named instance of SQL Server 2000 on the same
> computer. Details on how to proceed can be found in SQL Server Books
Online.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||Refer to :
http://msdn.microsoft.com/library/en-us/architec/8_ar_cs_9i5u.asp
--
- Anith
( Please reply to newsgroups only )