Change the Parameter in an query

I have a report "Clearance Summary Report:
Each page summarized the total sales for one agency.
Everything works great except for one total "Adjustments:"
An agency can have one order but have 50 adjustments to that order.
That is why the Adjustments can not be included in the "dsReport" query.
For that reason the Adjustments number is calculate with its own query
(dsAdjustments). but gets two parameters ClearanceID and MagazineID.
NOW the problem.
The help file says to create two parameters. ClearanceID and MagazineID.
Declear them internal.
Link them to the fields: ClearanceID and MagazineID.
Then the dsAdjustments query can refreance the paramters ClearanceID and
MagazineID.
When the report is run the dsAjustments query gets it value from the
parameters ClearanceID and MagazineID and then returns a number. That is how
I get the adjusments.
This works if I lookup only one Agency. But if there are two or more
agencies in the report the parameters ClearanceID and MagazineID are only
fill in at the begining of the report. All the other Agencies reports ty to
use the first agency adjustmets numbers.
Question: How do I get the parameters ClearanceID and MagzineID to update
for every new Agency''
Thank you
Scott BurkeRead up on subreports. Subreports are exactly for this sort of this.
Master-detail type reports pretty much must be done using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
>I have a report "Clearance Summary Report:
> Each page summarized the total sales for one agency.
> Everything works great except for one total "Adjustments:"
> An agency can have one order but have 50 adjustments to that order.
> That is why the Adjustments can not be included in the "dsReport" query.
> For that reason the Adjustments number is calculate with its own query
> (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
> NOW the problem.
> The help file says to create two parameters. ClearanceID and MagazineID.
> Declear them internal.
> Link them to the fields: ClearanceID and MagazineID.
> Then the dsAdjustments query can refreance the paramters ClearanceID and
> MagazineID.
> When the report is run the dsAjustments query gets it value from the
> parameters ClearanceID and MagazineID and then returns a number. That is
> how
> I get the adjusments.
> This works if I lookup only one Agency. But if there are two or more
> agencies in the report the parameters ClearanceID and MagazineID are only
> fill in at the begining of the report. All the other Agencies reports ty
> to
> use the first agency adjustmets numbers.
> Question: How do I get the parameters ClearanceID and MagzineID to update
> for every new Agency''
> Thank you
> Scott Burke
>|||Hi Bruce, This report org used a subreport to produce the totals. HOWEVER;
For some reason unknow to everyone I talked to the Report server tends to
lose/ can't find the subreport!!!!!
The last time it happed I had to redeploy the main report. That did not
work this time.
We only had the report server for four months! I dont look forward to
redeploying the CommisionSummaryReport every two months!
my solution is to replace the subreport. Besides running faster the only
problem I am having is getting the right values to the dsAdjustment query.
My only problem is getting the right ClearanceID and MagazineID value to the
query.
Is it possible to load a value into the parameters?
I am trying this in the textbox:
=Parameters!MagazineID.Value = Fields!MagazineID.Value
=(Fields!Adjustment.Value, "dsAdjustment")
The problem here is that "=Parameters!MagazineID.Value =Fields!MagazineID.Value" is being treaded like a comparason. The textbox
displays "True" or "False".
Any Ideals?
Scott Burke
"Bruce L-C [MVP]" wrote:
> Read up on subreports. Subreports are exactly for this sort of this.
> Master-detail type reports pretty much must be done using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
> news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
> >I have a report "Clearance Summary Report:
> > Each page summarized the total sales for one agency.
> >
> > Everything works great except for one total "Adjustments:"
> >
> > An agency can have one order but have 50 adjustments to that order.
> > That is why the Adjustments can not be included in the "dsReport" query.
> >
> > For that reason the Adjustments number is calculate with its own query
> > (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
> >
> > NOW the problem.
> > The help file says to create two parameters. ClearanceID and MagazineID.
> > Declear them internal.
> > Link them to the fields: ClearanceID and MagazineID.
> > Then the dsAdjustments query can refreance the paramters ClearanceID and
> > MagazineID.
> >
> > When the report is run the dsAjustments query gets it value from the
> > parameters ClearanceID and MagazineID and then returns a number. That is
> > how
> > I get the adjusments.
> >
> > This works if I lookup only one Agency. But if there are two or more
> > agencies in the report the parameters ClearanceID and MagazineID are only
> > fill in at the begining of the report. All the other Agencies reports ty
> > to
> > use the first agency adjustmets numbers.
> >
> > Question: How do I get the parameters ClearanceID and MagzineID to update
> > for every new Agency''
> >
> > Thank you
> > Scott Burke
> >
>
>|||Don't know why you are losing subreports. I have lots and lots and lots of
subreports. Not only have I never seen this but I have not seen any posting
about losing subreports. Makes me wonder what is unusual about your
environment.
Read up on drill-down (not drill through). Basically you have a single query
that contains all your information. The master information is repeated for
every row. Then you design your report to drill-down (click on a + sign to
see more) OR if you don't want to use drill down UI then you just hide the
master information when it is in the above row.
What you are trying to do will not work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:D302FB3A-8351-4C5E-AD1E-0011FCFC6634@.microsoft.com...
> Hi Bruce, This report org used a subreport to produce the totals.
> HOWEVER;
> For some reason unknow to everyone I talked to the Report server tends to
> lose/ can't find the subreport!!!!!
> The last time it happed I had to redeploy the main report. That did not
> work this time.
> We only had the report server for four months! I dont look forward to
> redeploying the CommisionSummaryReport every two months!
> my solution is to replace the subreport. Besides running faster the only
> problem I am having is getting the right values to the dsAdjustment query.
> My only problem is getting the right ClearanceID and MagazineID value to
> the
> query.
> Is it possible to load a value into the parameters?
> I am trying this in the textbox:
> =Parameters!MagazineID.Value = Fields!MagazineID.Value
> =(Fields!Adjustment.Value, "dsAdjustment")
>
> The problem here is that "=Parameters!MagazineID.Value => Fields!MagazineID.Value" is being treaded like a comparason. The textbox
> displays "True" or "False".
> Any Ideals?
> Scott Burke
>
>
> "Bruce L-C [MVP]" wrote:
>> Read up on subreports. Subreports are exactly for this sort of this.
>> Master-detail type reports pretty much must be done using subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
>> news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
>> >I have a report "Clearance Summary Report:
>> > Each page summarized the total sales for one agency.
>> >
>> > Everything works great except for one total "Adjustments:"
>> >
>> > An agency can have one order but have 50 adjustments to that order.
>> > That is why the Adjustments can not be included in the "dsReport"
>> > query.
>> >
>> > For that reason the Adjustments number is calculate with its own query
>> > (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
>> >
>> > NOW the problem.
>> > The help file says to create two parameters. ClearanceID and
>> > MagazineID.
>> > Declear them internal.
>> > Link them to the fields: ClearanceID and MagazineID.
>> > Then the dsAdjustments query can refreance the paramters ClearanceID
>> > and
>> > MagazineID.
>> >
>> > When the report is run the dsAjustments query gets it value from the
>> > parameters ClearanceID and MagazineID and then returns a number. That
>> > is
>> > how
>> > I get the adjusments.
>> >
>> > This works if I lookup only one Agency. But if there are two or more
>> > agencies in the report the parameters ClearanceID and MagazineID are
>> > only
>> > fill in at the begining of the report. All the other Agencies reports
>> > ty
>> > to
>> > use the first agency adjustmets numbers.
>> >
>> > Question: How do I get the parameters ClearanceID and MagzineID to
>> > update
>> > for every new Agency''
>> >
>> > Thank you
>> > Scott Burke
>> >
>>