How can I setup Reporting Service so the report wizard uses a default
template we have created?Hi,
Basically you can save at this folder to access the template.
C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
But for the wizard, you need to change the .vsz file with some scripts, it
is basically a text file. I would suggest you go through MSDN for changing
the scripts and search for "Creating a .vsz File" in MSDN.
Amarnath
"Chris Townsend" wrote:
> How can I setup Reporting Service so the report wizard uses a default
> template we have created?
>
>
Showing posts with label wizard. Show all posts
Showing posts with label wizard. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
Reporting Services on AS2005 cube
Hi all,
I'm trying to create a RS report that will use an AS2005 cube as a datasource. I did try to use the query wizard from reporting services to build the mdx statement but I'm having a hard time with it... How could I use the following MDX statement in a reporting services report? Do you have to use the query builder?
Code Snippet
WITH SET [Last 5 Months] AS
{TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1),
TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(2),
TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(3),
TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(4),
TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(5)}
MEMBER [Date].[Calendar].[AVG 5 months] AS
AVG([Last 5 Months])
MEMBER [Date].[Calendar].[Last Mth Var.] AS
(TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1) - AVG([Last 5 Months]))
MEMBER [Date].[Calendar].[YTD] AS
AGGREGATE(YTD(TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1)))
SELECT
{[Last 5 Months],[AVG 5 Months],[Last Mth Var.],[YTD]} ON COLUMNS,
NONEMPTY([Product].[Category].Members) ON ROWS
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount]
It's probably very simple to acheive but for some reason I can't get this to work properly...
I did a lot of RS against relational DB but reporting out of a cube is new to me.
TIA,
Eric
In the query builder, there's an option at the top for design mode, clicking this will bring you to the mdx.
If you've already been there, sorry for the explanation. Are you receiving any errors?
cheers,
Andrew
|||The SSAS 2005 provider, which the MDX Query Designer uses, requires measures on columns while you request an MDX set. The long story short -- you cannot use this query as it is with the MDX Query Designer. Instead, consider using the Microsoft OLE DB Provider for Analysis Services 9.0. It allows you to send whatever query you want but it doesn't support parameterized queries.|||Thanks Teo,
I looks like RS is a good reporting tool against Relational DB but it's not so good with complex MDX that you need to parameterized...
|||Please post this to the wish list on connect.microsoft.com.Friday, March 9, 2012
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
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
Wednesday, March 7, 2012
Reporting Services Error After Formatting Report
I created a drill-down report from a cube in Analysis Services using the report wizard. When I preview the report, the columns are not wide enough to view the data without the data getting wrapped around to the next line (the number 51,520,384.92 does not display on a single line). When I try to change the column width and then preview the report, I get a fatal error and Visual Studio closes. I had the same problem when I tried to change the number format to display 2 decimal places and preview the report. However, the number format worked if I saved my changes before previewing the report. Unfortunately this does not work for column width. Is there a work around for this? If I can't format my reports I'll have to use another application.
I can format reports without any problems when I create them manually, but it would be nice to use the wizard for the easier reports.|||I believe this issue has been fixed in the upcoming SP1 release of SQL Server 2005.Reporting Services Error After Formatting Report
I created a drill-down report from a cube in Analysis Services using the report wizard. When I preview the report, the columns are not wide enough to view the data without the data getting wrapped around to the next line (the number 51,520,384.92 does not display on a single line). When I try to change the column width and then preview the report, I get a fatal error and Visual Studio closes. I had the same problem when I tried to change the number format to display 2 decimal places and preview the report. However, the number format worked if I saved my changes before previewing the report. Unfortunately this does not work for column width. Is there a work around for this? If I can't format my reports I'll have to use another application.
I can format reports without any problems when I create them manually, but it would be nice to use the wizard for the easier reports.|||I believe this issue has been fixed in the upcoming SP1 release of SQL Server 2005.
Subscribe to:
Posts (Atom)