2nd request: group visibility issue

Hi,
I have a report with a SP datasource. The groups have drilldown
capabilities. I have conditional expressions on groups 1 and group 2 that
when a flag in the proc is one value then something like this is displayed:
region 1
district 1
facility A data data data
facility C data data data
facility F data data data
district 2
facility B
facility D
...etc
Then, when the flag is another value, I want to display just the facility
list without the groups. I almost have this successfully implemented, by
having conditionals on groups 1 and 2. It is something like this:
=iif(Fields!GroupRegDis.Value = 1, Fields!districtName.Value, 0)
The only problem is that when I disable the groups like that, I still get
the drilldown's plus sign at the top of the facility list.
+
+
Facility A data data
Facility B data data
etc
I don't want them there. Hope this is clear.
Thanks
--
Patrick StadlerYou cannot conditionally "remove" groupings.
You may try duplicating the table/matrix and remove the groupings for the
copied data region. Depending on the parameter values you would always just
show one of the data regions.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stads" <Stads@.discussions.microsoft.com> wrote in message
news:F4FCE3A3-1389-4F00-AB4F-BE3BBDB6A519@.microsoft.com...
> Hi,
> I have a report with a SP datasource. The groups have drilldown
> capabilities. I have conditional expressions on groups 1 and group 2 that
> when a flag in the proc is one value then something like this is
displayed:
> region 1
> district 1
> facility A data data data
> facility C data data data
> facility F data data data
> district 2
> facility B
> facility D
> ...etc
> Then, when the flag is another value, I want to display just the facility
> list without the groups. I almost have this successfully implemented, by
> having conditionals on groups 1 and 2. It is something like this:
> =iif(Fields!GroupRegDis.Value = 1, Fields!districtName.Value, 0)
> The only problem is that when I disable the groups like that, I still get
> the drilldown's plus sign at the top of the facility list.
> +
> +
> Facility A data data
> Facility B data data
> etc
> I don't want them there. Hope this is clear.
> Thanks
> --
> Patrick Stadler

2005 - Common Table Expressions Question

I am excited about this new feature in SQL Server 2005. However, it seems to
me there is a rather severe limitation to CTE's. That is the fact that any
queries that call the CTE must call them right after the CTE definition. For
example, Example #1 works, Example #2 fails, Example #3 works. I suppose
there is some logical technical reason why this must be but this seems, IMO,
to significantly limit the usefullness of CTE's especially as a replacement
for temporary tables (which can be referenced anytime after it is declared).
Any chance this limitation will be lifted before release?
-- Example #1
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE -- Select all records from the CTE
-- Example #2
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From Production.Location -- Select all records from a physical
table in the DB
Select * From MyCTE -- Select all records from the CTE
-- Example #3
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE -- Select all records from the CTE
Select * From Production.Location -- Select all records from a physical
table in the DB>I am excited about this new feature in SQL Server 2005.
Please post to the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1|||You should be able to achieve the desired result of #2 by writing
Select * From Production.Location;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE
If this does not meet your requirements, please explain what
the problem is.
Steve Kass
Drew University
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:ffSdnT55Xf7vgG_fRVn-jQ@.buckeye-express.com...
>I am excited about this new feature in SQL Server 2005. However, it seems
>to me there is a rather severe limitation to CTE's. That is the fact that
>any queries that call the CTE must call them right after the CTE
>definition. For example, Example #1 works, Example #2 fails, Example #3
>works. I suppose there is some logical technical reason why this must be
>but this seems, IMO, to significantly limit the usefullness of CTE's
>especially as a replacement for temporary tables (which can be referenced
>anytime after it is declared). Any chance this limitation will be lifted
>before release?
>
> -- Example #1
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From MyCTE -- Select all records from the CTE
> -- Example #2
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From Production.Location -- Select all records from a physical
> table in the DB
> Select * From MyCTE -- Select all records from the CTE
> -- Example #3
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From MyCTE -- Select all records from the CTE
> Select * From Production.Location -- Select all records from a physical
> table in the DB
>|||The point of CTEs is that they are scoped to the query. Non-recursive
CTEs are primarily hints for the optimizer and as a bonus they also
shorten the syntax compared to repeating a subquery / derived table.
If you want to persist the definition of a query then SQL2000 already
gives you views, procs and table-valued functions. I don't see that we
could gain much from yet another similar structure but you can email
sqlwish@.microsoft.com with suggestions for post-Yukon. 2005
functionality is pretty much wrapped up.
David Portas
SQL Server MVP
--