|
|
You're in the
sql server reporting services
group:procedure, report parameters
sql server reporting services:
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.
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 [quoted text, click to view] "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. >
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 [quoted text, click to view] "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 [quoted text, click to view] "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 > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |