procedure, report parameters

(sql/rs v2K, VS .Net 2003)
I have a problem with several multi-dataset reports. I've got several
single-dataset reports working just fine, but these multi-datasets are
problematic. For example, I have one procedure which works w/out parameters,
just returns a statistics report. I have modified the proc to accept a
parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the 2nd
chunk, etc., all the way thru to @.ch=5. All of them together comprise the
whole report. The proc works just fine in query analyzer, like this:
exec procname 1
exec procname 2
exec procname 3
exec procname 4
exec procname 5
now, in RS, i have created 5 datasets. each with the fields returned by
each procedure invocation. mind you, these aren't actual fields, it's select
this as value, select this as count, select this as volume, etc.
the datasets each are Text command types, the query strings are simply
'procname 1', 'procname 2', etc.
all of them work just fine in the Data tab in VS.
in the layout tab, i've created several tables and dragged/dropped the
fields into each accordingly
in the preview tab, i've got data returned in the top two chunks. WOW! cuz
i have been trying this for a very long time, yesterday i had no data at all,
only column headings, i don't know what i changed, but, now i have data in
two chunks, none in the rest. only column headings.
please, can anybody provide some direction on the right way to wrap the
report(s) around procedures like this? remember, the procedures all all just
statisitcal analysis data...no user input is required. i just need to
deliver nicely via email on a scheduled basis.
-- LynnAnd, when I refresh the report (in Preview tab, VS), sometimes all 5 chunks
are there, as desired...but then, I refresh again, and two are blank. (just
the column headings, no data) then, refresh again, and a different two are
blank.
(by 'chunks', i just mean different sections of the report. this particular
report has five different sections...or 'chunks'. each chunk has a
corresponding dataset)
please advise. this is terribly urgent.
-- Lynn
"Lynn" wrote:
> (sql/rs v2K, VS .Net 2003)
> I have a problem with several multi-dataset reports. I've got several
> single-dataset reports working just fine, but these multi-datasets are
> problematic. For example, I have one procedure which works w/out parameters,
> just returns a statistics report. I have modified the proc to accept a
> parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the 2nd
> chunk, etc., all the way thru to @.ch=5. All of them together comprise the
> whole report. The proc works just fine in query analyzer, like this:
> exec procname 1
> exec procname 2
> exec procname 3
> exec procname 4
> exec procname 5
> now, in RS, i have created 5 datasets. each with the fields returned by
> each procedure invocation. mind you, these aren't actual fields, it's select
> this as value, select this as count, select this as volume, etc.
> the datasets each are Text command types, the query strings are simply
> 'procname 1', 'procname 2', etc.
> all of them work just fine in the Data tab in VS.
> in the layout tab, i've created several tables and dragged/dropped the
> fields into each accordingly
> in the preview tab, i've got data returned in the top two chunks. WOW! cuz
> i have been trying this for a very long time, yesterday i had no data at all,
> only column headings, i don't know what i changed, but, now i have data in
> two chunks, none in the rest. only column headings.
> please, can anybody provide some direction on the right way to wrap the
> report(s) around procedures like this? remember, the procedures all all just
> statisitcal analysis data...no user input is required. i just need to
> deliver nicely via email on a scheduled basis.
> -- Lynn|||You might just be seeing an artifact of the development environment. Report
designer caches data. Look where your .rdl file is and you will see a
.rdl.data file. Delete it and you will be guaranteed that it will query your
database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:96659649-7A0D-488A-B6AD-C6BDD9A2651F@.microsoft.com...
> And, when I refresh the report (in Preview tab, VS), sometimes all 5
> chunks
> are there, as desired...but then, I refresh again, and two are blank.
> (just
> the column headings, no data) then, refresh again, and a different two
> are
> blank.
> (by 'chunks', i just mean different sections of the report. this
> particular
> report has five different sections...or 'chunks'. each chunk has a
> corresponding dataset)
> please advise. this is terribly urgent.
> -- Lynn
>
> "Lynn" wrote:
>> (sql/rs v2K, VS .Net 2003)
>> I have a problem with several multi-dataset reports. I've got several
>> single-dataset reports working just fine, but these multi-datasets are
>> problematic. For example, I have one procedure which works w/out
>> parameters,
>> just returns a statistics report. I have modified the proc to accept a
>> parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the
>> 2nd
>> chunk, etc., all the way thru to @.ch=5. All of them together comprise
>> the
>> whole report. The proc works just fine in query analyzer, like this:
>> exec procname 1
>> exec procname 2
>> exec procname 3
>> exec procname 4
>> exec procname 5
>> now, in RS, i have created 5 datasets. each with the fields returned by
>> each procedure invocation. mind you, these aren't actual fields, it's
>> select
>> this as value, select this as count, select this as volume, etc.
>> the datasets each are Text command types, the query strings are simply
>> 'procname 1', 'procname 2', etc.
>> all of them work just fine in the Data tab in VS.
>> in the layout tab, i've created several tables and dragged/dropped the
>> fields into each accordingly
>> in the preview tab, i've got data returned in the top two chunks. WOW!
>> cuz
>> i have been trying this for a very long time, yesterday i had no data at
>> all,
>> only column headings, i don't know what i changed, but, now i have data
>> in
>> two chunks, none in the rest. only column headings.
>> please, can anybody provide some direction on the right way to wrap the
>> report(s) around procedures like this? remember, the procedures all all
>> just
>> statisitcal analysis data...no user input is required. i just need to
>> deliver nicely via email on a scheduled basis.
>> -- Lynn|||Hi Bruce. There are no data files there at all. ( I wasn't able to get back
to you sooner, as I had a bit of a family emergency. ) I'm not sure how to
proceed with this. I am looking at the rpt right now through
http://server/reports, it is doing the same thing. First invocation, it had
all chunks of the rpt except for chunk 3. After a refresh, that chunk was
there, too. Two more refreshes, entire resultset is there fine, but then
after another refresh, chunk 4 is now missing/blank.
Another much less urgent question would be subscription scheduling. Can
those things vary at all? Meaning, how can i run it on all production
weekdays, instead of just all weekdays? you know...how do I correctly
account for the holidays?
Please advise.
-- Lynn
"Bruce L-C [MVP]" wrote:
> You might just be seeing an artifact of the development environment. Report
> designer caches data. Look where your .rdl file is and you will see a
> ..rdl.data file. Delete it and you will be guaranteed that it will query your
> database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:96659649-7A0D-488A-B6AD-C6BDD9A2651F@.microsoft.com...
> > And, when I refresh the report (in Preview tab, VS), sometimes all 5
> > chunks
> > are there, as desired...but then, I refresh again, and two are blank.
> > (just
> > the column headings, no data) then, refresh again, and a different two
> > are
> > blank.
> >
> > (by 'chunks', i just mean different sections of the report. this
> > particular
> > report has five different sections...or 'chunks'. each chunk has a
> > corresponding dataset)
> >
> > please advise. this is terribly urgent.
> > -- Lynn
> >
> >
> > "Lynn" wrote:
> >
> >> (sql/rs v2K, VS .Net 2003)
> >> I have a problem with several multi-dataset reports. I've got several
> >> single-dataset reports working just fine, but these multi-datasets are
> >> problematic. For example, I have one procedure which works w/out
> >> parameters,
> >> just returns a statistics report. I have modified the proc to accept a
> >> parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the
> >> 2nd
> >> chunk, etc., all the way thru to @.ch=5. All of them together comprise
> >> the
> >> whole report. The proc works just fine in query analyzer, like this:
> >>
> >> exec procname 1
> >> exec procname 2
> >> exec procname 3
> >> exec procname 4
> >> exec procname 5
> >>
> >> now, in RS, i have created 5 datasets. each with the fields returned by
> >> each procedure invocation. mind you, these aren't actual fields, it's
> >> select
> >> this as value, select this as count, select this as volume, etc.
> >>
> >> the datasets each are Text command types, the query strings are simply
> >> 'procname 1', 'procname 2', etc.
> >>
> >> all of them work just fine in the Data tab in VS.
> >> in the layout tab, i've created several tables and dragged/dropped the
> >> fields into each accordingly
> >>
> >> in the preview tab, i've got data returned in the top two chunks. WOW!
> >> cuz
> >> i have been trying this for a very long time, yesterday i had no data at
> >> all,
> >> only column headings, i don't know what i changed, but, now i have data
> >> in
> >> two chunks, none in the rest. only column headings.
> >>
> >> please, can anybody provide some direction on the right way to wrap the
> >> report(s) around procedures like this? remember, the procedures all all
> >> just
> >> statisitcal analysis data...no user input is required. i just need to
> >> deliver nicely via email on a scheduled basis.
> >>
> >> -- Lynn
>
>