Cascading Parameters, Drop down lists & the use of "ALL"

I have a report I'm working on (data dictionary) where the team wants to be
able to do the following:
1) Choose "All Tables" and get a report listing all tables with each tables
column names & properties underneath the proper table (ie, the columnName
param would be greyed out if this is chosen).
OR
2) Choose a single table by name, then choose "All Columns" and get a report
with ONLY that table and only the columns belonging to that table underneath
it with their properties.
OR
3) Choose a single table by name, then choose a single column by name and
only get that single column's properites.
Obviously, 3 is the easiest and what I was able to get done first. I have a
seperate report (my first report) where I've got a list of all tables with
drilldown enabled that shows each table's columns (all of them) when
expanded. Of course, once I got that report done, I was asked to add this
additional functionality.
Can this be done in one report or will I need to resort to multiple,
seperate reports to accomplish this?
I've got the code written for "All Tables" and "All Columns", but when I try
to pick a single table, I still get a ColumnList that lists all columns in
the entire DB, not the columns specific for that table.
Can someone give me advice on how to accomplish what I need? Thanks!!!
CatadminWe do something similar (although no cascading parameters). For "All", we
pass in the value zero, and the query is written like this:
Select * From OurTable Where (@.ID = 0 Or ID = @.ID)
If you're doing actual database objects and are using the sysobjects and
syscolumns tables, I would imagine your syscolumns query would include a
similar clause:
Where (@.TableID = 0 or id = @.TableID) And (@.ColumnID = 0 or colid =@.ColumnID)
I don't use cascading parameters, so I may be completely missing the point,
and if so, I apologize...
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:D0E39CA8-BB08-47BA-8D98-4DCF30A23C43@.microsoft.com...
>I have a report I'm working on (data dictionary) where the team wants to be
> able to do the following:
> 1) Choose "All Tables" and get a report listing all tables with each
> tables
> column names & properties underneath the proper table (ie, the columnName
> param would be greyed out if this is chosen).
> OR
> 2) Choose a single table by name, then choose "All Columns" and get a
> report
> with ONLY that table and only the columns belonging to that table
> underneath
> it with their properties.
> OR
> 3) Choose a single table by name, then choose a single column by name and
> only get that single column's properites.
> Obviously, 3 is the easiest and what I was able to get done first. I have
> a
> seperate report (my first report) where I've got a list of all tables with
> drilldown enabled that shows each table's columns (all of them) when
> expanded. Of course, once I got that report done, I was asked to add this
> additional functionality.
> Can this be done in one report or will I need to resort to multiple,
> seperate reports to accomplish this?
> I've got the code written for "All Tables" and "All Columns", but when I
> try
> to pick a single table, I still get a ColumnList that lists all columns in
> the entire DB, not the columns specific for that table.
> Can someone give me advice on how to accomplish what I need? Thanks!!!
> Catadmin
>|||I appreciate the suggestion, but I'm not sure it will work.
I want to be able to grey out the column parameter if "All Tables" is
chosen. Make it not accessible. Then, if a single table is chosen, not
print the other table on the report. My first problem is, that even when I
choose a single table, it prints out ALL the tables with the single column I
chose & all associated properties underneath all tables, even the tables that
have no such column. My second problem is creating the second parameter's
drop down list based only the columns associated with a single table picked
in the first parameter. My third problem is forcing all columns to
automatically be chosen, blocking out the second parameter, if "All Tables"
is chosen, so that all tables print, in order, with all of the columns
associated with that table and only that table.
Thank you for your time, though.
Catadmin
"DJM" wrote:
> We do something similar (although no cascading parameters). For "All", we
> pass in the value zero, and the query is written like this:
> Select * From OurTable Where (@.ID = 0 Or ID = @.ID)
> If you're doing actual database objects and are using the sysobjects and
> syscolumns tables, I would imagine your syscolumns query would include a
> similar clause:
> Where (@.TableID = 0 or id = @.TableID) And (@.ColumnID = 0 or colid => @.ColumnID)
> I don't use cascading parameters, so I may be completely missing the point,
> and if so, I apologize...
> "Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
> news:D0E39CA8-BB08-47BA-8D98-4DCF30A23C43@.microsoft.com...
> >I have a report I'm working on (data dictionary) where the team wants to be
> > able to do the following:
> >
> > 1) Choose "All Tables" and get a report listing all tables with each
> > tables
> > column names & properties underneath the proper table (ie, the columnName
> > param would be greyed out if this is chosen).
> >
> > OR
> >
> > 2) Choose a single table by name, then choose "All Columns" and get a
> > report
> > with ONLY that table and only the columns belonging to that table
> > underneath
> > it with their properties.
> >
> > OR
> >
> > 3) Choose a single table by name, then choose a single column by name and
> > only get that single column's properites.
> >
> > Obviously, 3 is the easiest and what I was able to get done first. I have
> > a
> > seperate report (my first report) where I've got a list of all tables with
> > drilldown enabled that shows each table's columns (all of them) when
> > expanded. Of course, once I got that report done, I was asked to add this
> > additional functionality.
> >
> > Can this be done in one report or will I need to resort to multiple,
> > seperate reports to accomplish this?
> >
> > I've got the code written for "All Tables" and "All Columns", but when I
> > try
> > to pick a single table, I still get a ColumnList that lists all columns in
> > the entire DB, not the columns specific for that table.
> >
> > Can someone give me advice on how to accomplish what I need? Thanks!!!
> >
> > Catadmin
> >
>
>