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
> >
>
>

Cascading Parameters Unreliable

I find that cascading parameters work intermittently. In addition, a
report with a drop down list will sometimes attempt to execute before
the user has made a selection.make sure you have the parameters in the proper order for cascading to work
properly. i.e. country parm first in your parameter list, state parameter
second, city parameter third... all the cascading parameters have to be
ahead of your other non-cascading parameters. to ensure the report will not
execute before a user selects parms... just leave 1 parm without default
value.
"mjhillman@.msn.com" wrote:
> I find that cascading parameters work intermittently. In addition, a
> report with a drop down list will sometimes attempt to execute before
> the user has made a selection.
>|||I am calling two stored procedures that take the same parameter. I
only have one parameter in the parameter list. Does this suggestion
apply to this scenario as well? Do I have to modify the SP to use a
different parameter name? (I did not write the SPs I am just executing
reports against an existing WMS.). Thanks.

Cascading Parameters in Dev environment Convulsing

I'm usring RS SP2 and have a cascading parameter. Both parameters are drop
down list. When you change the first drop down, the dev environment goes
into convulsions and starts flashing. I've done some research and found this
issue in a few places on the net but have not found a solution. All
indications ar ethat it will work fine once deployed. Anyone know of the
solution to this issue or a work around?I don't have this happen with all cascading parameter reports, just some of
them. I don't have a pattern for when it happens and when it doesn't. For
those reports where this happens I test by deploying to a test server rather
than testing from the dev environment. As you say, they work when deployed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephen Farmer" <Stephen.Farmer@.LucrumInc.Com> wrote in message
news:eADozujWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> I'm usring RS SP2 and have a cascading parameter. Both parameters are drop
> down list. When you change the first drop down, the dev environment goes
> into convulsions and starts flashing. I've done some research and found
> this issue in a few places on the net but have not found a solution. All
> indications ar ethat it will work fine once deployed. Anyone know of the
> solution to this issue or a work around?
>
>