Reporting Services SP1 Designer Cache Problem

Reporting Service SP1 provides an option to cache the data during
design time for preview, which seems to be making the preview to take
lot longer time than pre SP1.
This is what Reporting Services SP1 is doing:
During preview it is making a call to SQL and in between it is writing
into report data file, which is taking longer time just because it is
reading and writing into data file before even rendering.
Why can't it cache the data after it gets the entire data instead of
holding the sql connection or after rendering?
One of my reports is used to take 10 secs prior to SP1 now it is
taking 6 mins.
After turning of the configuration to false we started getting the
report in 10 secs.
Is there any resolution for this?
Thanks
RI have a couple of questions. Does this type of performance difference
occur for all reports or just this one? What is the structure of the data:
number of rows returned, etc. I would expect caching to take longer but not
in the magnitude you are describing.
The report is cached and rendered as we get the data (it is not buffered).
On the surface it seems quite in efficient to render the report and then
potentially round trip to the server again. Buffering is an issue since we
do not know the size of result set in advance.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"am_i_dotnet" <am_i_dotnet@.hotmail.com> wrote in message
news:ac7b2754.0407151240.2374c78b@.posting.google.com...
> Reporting Service SP1 provides an option to cache the data during
> design time for preview, which seems to be making the preview to take
> lot longer time than pre SP1.
> This is what Reporting Services SP1 is doing:
> During preview it is making a call to SQL and in between it is writing
> into report data file, which is taking longer time just because it is
> reading and writing into data file before even rendering.
> Why can't it cache the data after it gets the entire data instead of
> holding the sql connection or after rendering?
> One of my reports is used to take 10 secs prior to SP1 now it is
> taking 6 mins.
> After turning of the configuration to false we started getting the
> report in 10 secs.
> Is there any resolution for this?
> Thanks
> R|||There are around 20000+ records in this report and the report is in
tabular format.
I may not agree with you on "The report is cached and rendered as we
get the data". Because I observed the data file keep growing when I
tried to preview the report and report showed up only after the entire
data file created.
I know it is inefficient to make second round trip to server just to
cache the data but do we have any thoughts on using the data in the
report. I mean first render the report and cache the data
asynchronously from report data.
Thanks
R
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message news:<#dyED4qaEHA.3684@.TK2MSFTNGP09.phx.gbl>...
> I have a couple of questions. Does this type of performance difference
> occur for all reports or just this one? What is the structure of the data:
> number of rows returned, etc. I would expect caching to take longer but not
> in the magnitude you are describing.
> The report is cached and rendered as we get the data (it is not buffered).
> On the surface it seems quite in efficient to render the report and then
> potentially round trip to the server again. Buffering is an issue since we
> do not know the size of result set in advance.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "am_i_dotnet" <am_i_dotnet@.hotmail.com> wrote in message
> news:ac7b2754.0407151240.2374c78b@.posting.google.com...
> > Reporting Service SP1 provides an option to cache the data during
> > design time for preview, which seems to be making the preview to take
> > lot longer time than pre SP1.
> >
> > This is what Reporting Services SP1 is doing:
> > During preview it is making a call to SQL and in between it is writing
> > into report data file, which is taking longer time just because it is
> > reading and writing into data file before even rendering.
> >
> > Why can't it cache the data after it gets the entire data instead of
> > holding the sql connection or after rendering?
> >
> > One of my reports is used to take 10 secs prior to SP1 now it is
> > taking 6 mins.
> >
> > After turning of the configuration to false we started getting the
> > report in 10 secs.
> >
> > Is there any resolution for this?
> >
> > Thanks
> > R

Reporting Services Render issues in IE

Hi

We are having an issue with reporting services when it is viewed in IE.

We can design the report in visual studio and it looks fine, however when we upload the report to reporting services it displays the report incorrectly (shown below)

http://static.flickr.com/103/298669551_7395fc946d.jpg

When it is print previewed it looks like this (which is the correct layout)

http://static.flickr.com/104/298669558_cfd56ffd0e.jpg

Any advice would be most appreciated!

Thanks Ross

Ross,

You're running into the differences between the renderers. Designer preview and print preview use a GDI-based renderer, while Report Manager uses an HTML-based renderer. There are some pretty significant differences in the way borders and other elements are handled.

Best advice I can give you is to decide which renderer is most important for each report, and make sure you get what you want for that renderer.

It may be possible to get a consistent result from both types of renderers with the use of rectangles to "freeze" report items.

|||

Mike

I posted this yesterday...

"We have a SQL Server 2005 database which holds all the companies scanned documents (in image fields). I have developed a report which allows the user to search for a contract and all relevant scanned documents are returned.

When I preview the report with Visual Studio Report Designer...SOME of the images are not displayed, I get the little red cross icon in the top left corner.

When viewed using Report Manager with a browser... the report displays ALL images... however, when I click on the print button and then the print preview button in Report Manager, some of the images do not appear (the same images that don't appear in the Report Designer preview).

Can anyone tell me why I can see all the images through Report Manager and my Browser (IE7), but only some of the images when in Report Designer preview or Print Preview? "

After reading your post above, I started thinking it could be a rendering issue. Have you got any more information?

Reporting Services Render issues in IE

Hi

We are having an issue with reporting services when it is viewed in IE.

We can design the report in visual studio and it looks fine, however when we upload the report to reporting services it displays the report incorrectly (shown below)

http://static.flickr.com/103/298669551_7395fc946d.jpg

When it is print previewed it looks like this (which is the correct layout)

http://static.flickr.com/104/298669558_cfd56ffd0e.jpg

Any advice would be most appreciated!

Thanks Ross

Ross,

You're running into the differences between the renderers. Designer preview and print preview use a GDI-based renderer, while Report Manager uses an HTML-based renderer. There are some pretty significant differences in the way borders and other elements are handled.

Best advice I can give you is to decide which renderer is most important for each report, and make sure you get what you want for that renderer.

It may be possible to get a consistent result from both types of renderers with the use of rectangles to "freeze" report items.

|||

Mike

I posted this yesterday...

"We have a SQL Server 2005 database which holds all the companies scanned documents (in image fields). I have developed a report which allows the user to search for a contract and all relevant scanned documents are returned.

When I preview the report with Visual Studio Report Designer...SOME of the images are not displayed, I get the little red cross icon in the top left corner.

When viewed using Report Manager with a browser... the report displays ALL images... however, when I click on the print button and then the print preview button in Report Manager, some of the images do not appear (the same images that don't appear in the Report Designer preview).

Can anyone tell me why I can see all the images through Report Manager and my Browser (IE7), but only some of the images when in Report Designer preview or Print Preview? "

After reading your post above, I started thinking it could be a rendering issue. Have you got any more information?

sql

Reporting Services Render issues in IE

Hi
We are having an issue with reporting services when it is viewed in IE.
We can design the report in visual studio and it looks fine, however
when we upload the report to reporting services it displays the report
incorrectly (shown below)
http://static.flickr.com/103/298669551_7395fc946d.jpg
When it is print previewed it looks like this (which is the correct
layout)
http://static.flickr.com/104/298669558_cfd56ffd0e.jpg
Any advice would be most appreciated!
Thanks RossI'd like to know more about this too.
I had a report with a chart and an image on it. The image was to the
right of the chart. When viewing the report it looked fine. However,
when it was print previewed, the image showed up right on top of the
chart. Made no sense.
However, i fixed this by making sure my report elements didn't overlap.
Is this happening in your case? Reporting services doesn't like reports
where the elements on it are either touching or overlapping. Try
spacing out some stuff.
-Kevin
ross.dargan@.gmail.com wrote:
> Hi
> We are having an issue with reporting services when it is viewed in IE.
> We can design the report in visual studio and it looks fine, however
> when we upload the report to reporting services it displays the report
> incorrectly (shown below)
> http://static.flickr.com/103/298669551_7395fc946d.jpg
> When it is print previewed it looks like this (which is the correct
> layout)
> http://static.flickr.com/104/298669558_cfd56ffd0e.jpg
> Any advice would be most appreciated!
> Thanks Ross

Reporting Services Query designer toolbar disabled

Hi to all , i'm trying to modify an mdx generated by query designer in "Design" mode.
I would like to switch to query designer in "Query" mode but the query designer toolbar and its button "Change Type" appears to be disabled.
In this project i use a shared data source and the data source type is Analysis Services.
In another project I followed the first basic Tutorial on Reporting Service ("Sales Order" report) so data source it's SQL Server and query designer it is locked to Query Mode and i cannot change it to Design Mode.
Thanks for help.
Alberto De MarcoI'm not sure which buttons you are referring to. Could you attach a picture of what you are seeing?

-Albert

Reporting Services Query designer toolbar disabled

Hi to all , i'm trying to modify an mdx generated by query designer in "Design" mode.
I would like to switch to query designer in "Query" mode but the query designer toolbar and its button "Change Type" appears to be disabled.
In this project i use a shared data source and the data source type is Analysis Services.
In another project I followed the first basic Tutorial on Reporting Service ("Sales Order" report) so data source it's SQL Server and query designer it is locked to Query Mode and i cannot change it to Design Mode.
Thanks for help.
Alberto De MarcoI'm not sure which buttons you are referring to. Could you attach a picture of what you are seeing?

-Albert

Reporting Services initial application design question

I am new to Reporting Services, and I would like to serve up SQL 2005 Reporting Services reports via an ASP.NET web app (possibly using Report Viewer controls in Server mode).The web app will be running on an IIS server outside the firewall.

I would prefer to have the Reporting Services server be inaccessible outside the firewall, but it seems that the client browsers need direct URL access to the Reporting Services server.I guess this means either locating the Reporting Services server outside the firewall, or opening a hole in the firewall for access to the Reporting Services server.

Is there a Microsoft best-practice methodology/white-paper that addresses this scenario (especially as regards security concerns)?

Alternately, would it be possible, and would the same report presentation functionality be available (reports with expanding/collapsing sections, choice of export formats, etc), by calling the Reporting Services web service directly from the ASP.NET app, and manually populating the Response object?

Thanks.Any help would be appreciated.

Here is a posting that may help your scenario.

http://blogs.msdn.com/tudortr/archive/2005/11/03/488731.aspx

cheers,

Andrew

|||

Thanks for your response. I had found that link previously, but I've since found another idea that sounds promising:

Extending SQL Server Reporting Services with SQL CLR Table-Valued FunctionsExtending SQL Server Reporting Services with SQL CLR Table-Valued Functions http://msdn2.microsoft.com/en-us/library/bb293147.aspx

Reporting Services Has a problem with my Dynamic SQL

I have a stored proc that I can execute from Visual Studio, Query Analyzer,
and from within the "Design the Query" window in the Report Wizard. It
returns data in all cases. However, when I continue on with the wizard and
get to the "Design the Table" page, there are no fields in the "Available
Fields" list box. If I continue on and Finish, there an ugly error message
about "MS Development Environment is unable to load this document.
Deserialization failed: The element 'http://schemas..../reportdefinition:
TableCells' has incomplete content... An error occurred at , (33, 16)."
What is unusual is that it seems to bomb anytime I use dynamic SQL, but if I
use a local variable (casting a date as a string) it will work. If I use the
same local variable, and parse the incoming date parameter to the identical
date string, it will not work.
Here's the code... any help is appreciated.
ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
(
@.Shop varchar(15),
@.DateBgn datetime,
@.DateEnd datetime,
@.IncTypes varchar(4096)
)
AS
SET NOCOUNT ON
DECLARE
@.lv_shop varchar(15),
@.lv_types varchar(4096),
@.lv_SQL varchar(8000)
BEGIN
If IsNumeric(@.Shop) = 1
SET @.lv_shop = @.Shop
Else
SET @.lv_shop = '%'
SET @.lv_types = @.IncTypes
SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
SET @.lv_types = ('''' + @.lv_types + '''')
SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
'ggptrk07.incdnt_typ_d, ' +
'ggptrk08.incdnt_strt_t, ' +
'ggptrk08.incdnt_end_t, ' +
'ggptrk08.incdnt_n, ' +
'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
getdate())), 2) Duration, ' +
'ggptrk08.mold_n, ' +
'ggptrk08.mount_sect_i, ' +
'ggptrk08.mount_pstn_i, ' +
'ggptrk06.incdnt_rsn_d, ' +
'ggptrk08.incdnt_cmnt_e, ' +
'ggptrk27.incdnt_actn_t, ' +
'ggptrk27.incdnt_solution_c, ' +
'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON GGPTRK08.INCDNT_RSN_C
= GGPTRK06.INCDNT_RSN_C ' +
'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON GGPTRK08.INCDNT_TYP_C
= GGPTRK07.INCDNT_TYP_C ' +
'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
' +
'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
' +
'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
'ORDER BY ' +
'ggptrk08.prod_ln_i, ' +
'ggptrk07.incdnt_typ_d, ' +
'ggptrk08.incdnt_strt_t'
Exec (@.lv_SQL)
ENDIt seems to me that Report Wizard cannot get field list because it is a
dynamic query.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JKramer" <JKramer@.discussions.microsoft.com> wrote in message
news:E9FF54EA-478F-4CE4-BC17-219BAA942D4D@.microsoft.com...
>I have a stored proc that I can execute from Visual Studio, Query Analyzer,
> and from within the "Design the Query" window in the Report Wizard. It
> returns data in all cases. However, when I continue on with the wizard and
> get to the "Design the Table" page, there are no fields in the "Available
> Fields" list box. If I continue on and Finish, there an ugly error message
> about "MS Development Environment is unable to load this document.
> Deserialization failed: The element 'http://schemas..../reportdefinition:
> TableCells' has incomplete content... An error occurred at , (33, 16)."
> What is unusual is that it seems to bomb anytime I use dynamic SQL, but if
> I
> use a local variable (casting a date as a string) it will work. If I use
> the
> same local variable, and parse the incoming date parameter to the
> identical
> date string, it will not work.
> Here's the code... any help is appreciated.
> ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
> (
> @.Shop varchar(15),
> @.DateBgn datetime,
> @.DateEnd datetime,
> @.IncTypes varchar(4096)
> )
> AS
> SET NOCOUNT ON
> DECLARE
> @.lv_shop varchar(15),
> @.lv_types varchar(4096),
> @.lv_SQL varchar(8000)
> BEGIN
> If IsNumeric(@.Shop) = 1
> SET @.lv_shop = @.Shop
> Else
> SET @.lv_shop = '%'
> SET @.lv_types = @.IncTypes
> SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
> SET @.lv_types = ('''' + @.lv_types + '''')
> SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t, ' +
> 'ggptrk08.incdnt_end_t, ' +
> 'ggptrk08.incdnt_n, ' +
> 'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
> getdate())), 2) Duration, ' +
> 'ggptrk08.mold_n, ' +
> 'ggptrk08.mount_sect_i, ' +
> 'ggptrk08.mount_pstn_i, ' +
> 'ggptrk06.incdnt_rsn_d, ' +
> 'ggptrk08.incdnt_cmnt_e, ' +
> 'ggptrk27.incdnt_actn_t, ' +
> 'ggptrk27.incdnt_solution_c, ' +
> 'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
> 'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
> 'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
> 'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
> 'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
> GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
> 'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON GGPTRK08.INCDNT_RSN_C
> = GGPTRK06.INCDNT_RSN_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON GGPTRK08.INCDNT_TYP_C
> = GGPTRK07.INCDNT_TYP_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
> 'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
> GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
> 'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
> 'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
> 'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
> ' +
> 'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
> ' +
> 'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
> 'ORDER BY ' +
> 'ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t'
> Exec (@.lv_SQL)
> END|||Hi JKramer,
yes, when you link a stored procedure to a dataset in report services
it queries the report and builds the list of columns returned from the
query.....this is very, very handy as you can name your fields
consistently and then copy reports and just change the stored procedure
the report uses and it will 'auto-magically' put the data into the
right fields in the datasets linked to tables and charts.....very very
handy...if you use strings like this I gather than you can not do
this...
I don't see why you would want to generate strings like this.....we
tried this and found performance around 50% down on just using straight
sql with variables in it....also with strings you are limited to 8000
characters which you may over-run without knowing it....we have
queries over 8000 characters....
We are yet to find a situation where we cannot make do with using sql
and not strings like this......
Best Regards
Peter
www.peternolan.com|||Peter,
The reason I use dynamic SQL is because one of the where conditions is a
LIKE condition, which I'm taking in as one string parameter. So the input
might be "21,22,23" or it might be "31". It is also dynamic, so I'm never
exactly sure what might be coming in (though I'm sure I won't hit the 8000
character limit). I don't know how to build that using straight SQL. If it
can be done, I agree, that's the way to go.
Can you tell me how I'd accomplish what I'm trying to do with straight SQL?
"Peter Nolan" wrote:
> Hi JKramer,
> yes, when you link a stored procedure to a dataset in report services
> it queries the report and builds the list of columns returned from the
> query.....this is very, very handy as you can name your fields
> consistently and then copy reports and just change the stored procedure
> the report uses and it will 'auto-magically' put the data into the
> right fields in the datasets linked to tables and charts.....very very
> handy...if you use strings like this I gather than you can not do
> this...
> I don't see why you would want to generate strings like this.....we
> tried this and found performance around 50% down on just using straight
> sql with variables in it....also with strings you are limited to 8000
> characters which you may over-run without knowing it....we have
> queries over 8000 characters....
> We are yet to find a situation where we cannot make do with using sql
> and not strings like this......
> Best Regards
> Peter
> www.peternolan.com
>|||In case anyone is interested, I think the problem is actually with the wizard
and not in the handling of the dynamic SQL. After re-reading the error, I got
to thinking the problem was in the *.rdl. So as I attempted to "fix" it (and
I'm not familiar enough with it to do so), I didn't help the issue at all. So
I copied the text of a different *.rdl inot the one I couldn't get to work
and saved it. When I opened it up, it opened just fine (of course the data
source was wrong, since it was copied from another). So I went to the data
tab, pointed it to the correct Stored Proc (still using dynamic SQL) and it
worked fine from there. So the problem was not the data source, but the
wizard.
Hopefully that helps anyone else who runs into this one.
Where does one post a bug for reporting services?
"JKramer" wrote:
> I have a stored proc that I can execute from Visual Studio, Query Analyzer,
> and from within the "Design the Query" window in the Report Wizard. It
> returns data in all cases. However, when I continue on with the wizard and
> get to the "Design the Table" page, there are no fields in the "Available
> Fields" list box. If I continue on and Finish, there an ugly error message
> about "MS Development Environment is unable to load this document.
> Deserialization failed: The element 'http://schemas..../reportdefinition:
> TableCells' has incomplete content... An error occurred at , (33, 16)."
> What is unusual is that it seems to bomb anytime I use dynamic SQL, but if I
> use a local variable (casting a date as a string) it will work. If I use the
> same local variable, and parse the incoming date parameter to the identical
> date string, it will not work.
> Here's the code... any help is appreciated.
> ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
> (
> @.Shop varchar(15),
> @.DateBgn datetime,
> @.DateEnd datetime,
> @.IncTypes varchar(4096)
> )
> AS
> SET NOCOUNT ON
> DECLARE
> @.lv_shop varchar(15),
> @.lv_types varchar(4096),
> @.lv_SQL varchar(8000)
> BEGIN
> If IsNumeric(@.Shop) = 1
> SET @.lv_shop = @.Shop
> Else
> SET @.lv_shop = '%'
> SET @.lv_types = @.IncTypes
> SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
> SET @.lv_types = ('''' + @.lv_types + '''')
> SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t, ' +
> 'ggptrk08.incdnt_end_t, ' +
> 'ggptrk08.incdnt_n, ' +
> 'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
> getdate())), 2) Duration, ' +
> 'ggptrk08.mold_n, ' +
> 'ggptrk08.mount_sect_i, ' +
> 'ggptrk08.mount_pstn_i, ' +
> 'ggptrk06.incdnt_rsn_d, ' +
> 'ggptrk08.incdnt_cmnt_e, ' +
> 'ggptrk27.incdnt_actn_t, ' +
> 'ggptrk27.incdnt_solution_c, ' +
> 'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
> 'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
> 'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
> 'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
> 'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
> GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
> 'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON GGPTRK08.INCDNT_RSN_C
> = GGPTRK06.INCDNT_RSN_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON GGPTRK08.INCDNT_TYP_C
> = GGPTRK07.INCDNT_TYP_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
> 'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
> GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
> 'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
> 'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
> 'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
> ' +
> 'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
> ' +
> 'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
> 'ORDER BY ' +
> 'ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t'
> Exec (@.lv_SQL)
> END|||In general with Stored Procedures I very seldom use the Report Wizard. I
create a new report, go to the data tab and set it up, then to layout and
drag and drop the table. Right mouse click on a column to add additional
columns. Or, start off with a query that has the appropriate fields, use the
wizard and then change to a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JKramer" <JKramer@.discussions.microsoft.com> wrote in message
news:75EEA5FA-0373-420B-B90E-0CF660220EF2@.microsoft.com...
> In case anyone is interested, I think the problem is actually with the
> wizard
> and not in the handling of the dynamic SQL. After re-reading the error, I
> got
> to thinking the problem was in the *.rdl. So as I attempted to "fix" it
> (and
> I'm not familiar enough with it to do so), I didn't help the issue at all.
> So
> I copied the text of a different *.rdl inot the one I couldn't get to work
> and saved it. When I opened it up, it opened just fine (of course the data
> source was wrong, since it was copied from another). So I went to the data
> tab, pointed it to the correct Stored Proc (still using dynamic SQL) and
> it
> worked fine from there. So the problem was not the data source, but the
> wizard.
> Hopefully that helps anyone else who runs into this one.
> Where does one post a bug for reporting services?
> "JKramer" wrote:
>> I have a stored proc that I can execute from Visual Studio, Query
>> Analyzer,
>> and from within the "Design the Query" window in the Report Wizard. It
>> returns data in all cases. However, when I continue on with the wizard
>> and
>> get to the "Design the Table" page, there are no fields in the "Available
>> Fields" list box. If I continue on and Finish, there an ugly error
>> message
>> about "MS Development Environment is unable to load this document.
>> Deserialization failed: The element
>> 'http://schemas..../reportdefinition:
>> TableCells' has incomplete content... An error occurred at , (33, 16)."
>> What is unusual is that it seems to bomb anytime I use dynamic SQL, but
>> if I
>> use a local variable (casting a date as a string) it will work. If I use
>> the
>> same local variable, and parse the incoming date parameter to the
>> identical
>> date string, it will not work.
>> Here's the code... any help is appreciated.
>> ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
>> (
>> @.Shop varchar(15),
>> @.DateBgn datetime,
>> @.DateEnd datetime,
>> @.IncTypes varchar(4096)
>> )
>> AS
>> SET NOCOUNT ON
>> DECLARE
>> @.lv_shop varchar(15),
>> @.lv_types varchar(4096),
>> @.lv_SQL varchar(8000)
>> BEGIN
>> If IsNumeric(@.Shop) = 1
>> SET @.lv_shop = @.Shop
>> Else
>> SET @.lv_shop = '%'
>> SET @.lv_types = @.IncTypes
>> SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
>> SET @.lv_types = ('''' + @.lv_types + '''')
>> SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
>> 'ggptrk07.incdnt_typ_d, ' +
>> 'ggptrk08.incdnt_strt_t, ' +
>> 'ggptrk08.incdnt_end_t, ' +
>> 'ggptrk08.incdnt_n, ' +
>> 'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
>> getdate())), 2) Duration, ' +
>> 'ggptrk08.mold_n, ' +
>> 'ggptrk08.mount_sect_i, ' +
>> 'ggptrk08.mount_pstn_i, ' +
>> 'ggptrk06.incdnt_rsn_d, ' +
>> 'ggptrk08.incdnt_cmnt_e, ' +
>> 'ggptrk27.incdnt_actn_t, ' +
>> 'ggptrk27.incdnt_solution_c, ' +
>> 'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
>> 'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
>> 'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
>> 'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
>> 'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
>> GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
>> 'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON
>> GGPTRK08.INCDNT_RSN_C
>> = GGPTRK06.INCDNT_RSN_C ' +
>> 'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON
>> GGPTRK08.INCDNT_TYP_C
>> = GGPTRK07.INCDNT_TYP_C ' +
>> 'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
>> 'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
>> GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
>> 'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
>> 'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
>> 'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
>> ' +
>> 'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
>> ' +
>> 'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
>> 'ORDER BY ' +
>> 'ggptrk08.prod_ln_i, ' +
>> 'ggptrk07.incdnt_typ_d, ' +
>> 'ggptrk08.incdnt_strt_t'
>> Exec (@.lv_SQL)
>> END|||Hi JKramer,
does it recognise the fieds being returned or do you have to type them
into the dataset? When you add a dataset to a report it automatically
adds the parameters and the fields returned from the SP...this is what
I was saying was useful...I gave up on wizards almost
immediately...they are just WAY too limited....we set up templates and
put the templates into a vs directory and they appear as templates when
we create a new report..although most of the time we just copy reports
anyway...
Peter|||It appears that a lot of folks have raised this issue also, but there was
never a concreate solution from the MFST experts?
Here is an example of a stored procedure that creates a dynamic query. I am
using a simple example to demonstrate the problem that can be recreated
easily, so please do not reply and said "why dont you use a straight select
statement instead?"
Create PROCEDURE dbo.QuarterlyReportingTest
@.CustomerPk int
AS
declare @.SQL varchar(2000)
select @.SQL = 'select * from [DataWarehouse].dbo.CustomerDim where
CustomerPk = ' + convert(varchar(10), @.CustomerPk)
exec (@.SQL)
This query will return the particular rows of information depending on the
report parameter @.CustomerPk
In RS, the dataset was setup as command type of 'Stored Procedure'. The
query string field has 'QuarterlyReportingTest'. When I clicked the '!', it
will prompt me for the CustomerPk. I would enter the CustomerPk, and it will
return the appropriate data.
The problem is, the DataSet fields remain blank. I can click the "Refresh
field" button, but the DataSet fields are still blank. Of course, without
the DataSet field, you cannot go and create the report, and report rendering
would also have error because there are no fields associated with the dataset.
Can anyone help in this so that the Dataset fields are populated / available
for these dynamic queries stored procedures?
Thanks.
LBJOHN99@.yahoo.com|||Hi JKramer,
can you use 'in' and just pass this set of values?
Also, I just got my copy of hitchhikers guide to SQL Server 2000 and
there is a specific example of this on pages 535 and 536. Since I'm a
believer in guys getting paid for their books I won't write the example
here...but they provide an example of picking multiple values from a
parameter in 2000 and then passing it through to a query making it safe
from SQL Injection along the way....the example is selecting a set of
employee ids from a list and printing the employee details...
Not sure if this would be applicable to your specific problem...but if
you know someone near you with a copy of the book you can look and see.
Best Regards
Peter Nolan
www.peternolan.com