Disable Drill Down Based on Parameter 1

[Auguy]

SQL 2000 RS. Is there anyway to disable the drill down to a detail report?. I have a sales summary report with a drill down to a detail report that has financial details on it. I want to prevent the drill down if the user is not the salesman on the invoice. At this point I would rather not change the detail report.

Auguy
Northwest Ohio

 

[Auguy]

I found a work-around by passing the salesman's code to the report and making the invoice number textbox visible only when this code matches the salesman's code from the database. For supervisory personnel I passed "<ALL>" to the report. An "or" condition in the visible code for the invoice text box makes it visible all of the time. I don't like making the invoice invisible, but it works until I figure out a better method.

Auguy
Northwest Ohio

 

[xlbo]

How is the drill through managed? Is it navigation on the textbox?

If so, use an IIF statement on the navigation like:

=iif(ConditionToTest=Passed,"Report Name",FALSE)

The FALSE will disable the navigation element and the textbox will act like a normal textbox - if the condition is true, the report name is parsed and navigation should be available

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Auguy]

Thanks Geoff. Yes, it is a navigation throught the text box to another URL. I Tried something similar to what you have suggested, but I had a blank instead of FALSE in my IIF and it was giving me an error. I will try it out and post back my results.

Auguy
Northwest Ohio

 

[xlbo]

No worries - important note, it needs to be FALSE rather than "FALSE"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Auguy]

Thanks again Geoff, it worked perfectly!

Auguy
Northwest Ohio

 

[Auguy]

Just a follow up. Thanks to Geoff I now pass a "Grouping String" to the report, which passes it on to the stored proc. This "Grouping String" is concatenation of two to four fields. This lets me set the grouping in the report to the column created by the stored proc for the "Grouping string". Therefore I don't have to do anything on the report to change the grouping, it is always set to this column and I can still drill down to the detail. Right now I have nine different groupings I let the user select.
1. Shipping State/City to Destination State/City
2. Shipping State/City to Destination State
3. Shipping State/City to Destination Zip
4. Shipping State to Destination State/City
5. Shipping State to Destination State
6. Shipping State to Destination Zip
7. Shipping Zip to Destination State/City
8. Shipping Zip to Destination State
9. Shipping Zip to Destination Zip

The actual string I pass looks like the following for # 2

&Group1=ShipState + %2B + ', ' + %2B + ShipCity + %2B + ' To ' + %2B + DestState

On the report it looks like this

OH, CLEVELAND To TX
OH, COLUMBUS To TX
...

The user may also select from one to 5 digits of the zip code for the grouping. This report is being used to analyze costs and profits between various shipping and destination locations.


Auguy
Northwest Ohio

 

[Auguy]

Oops. Sorry this was supposed to be posted in "Error When Report Called Form VB.net"

Auguy
Northwest Ohio