I have many fields but only a few rows to display. How do I invert the table with Report Services Designer so that Field (Columns) go down the page and the row items go across the page?
The best way to do this is to use Matrix instead of table for more help and examples check those links
http://msdn2.microsoft.com/en-us/library/ms251712(VS.80).aspx
http://msdn2.microsoft.com/en-us/library/ms251709(VS.80).aspx
if I drop t1.col2, the view v1 is not working anymore. I want to know
that information.
In Oracle (8.1.7), i can query the all_objects, user_object table,
where status = 'INVALID'. So i can recompile invalid objects (or
correct it).
In sql Server, the table sysobjects give me some status info, but
they are not documented enough.
Do you know if i can user one of those fields : status, userstat,
sysstat ?
Same question for function , procedure.
TKS.SQL Server doesn't expose this information. One method to identify invalid
objects is to reference them with SET FMPONLY ON:
SET FMTONLY ON
SELECT * FROM MyView
SET FMTONLY OFF
GO
SET FMTONLY ON
EXEC MyProcedure NULL
SET FMTONLY OFF
GO
However, this is not as thorough as actually exercising the objects. For
example, it won't detect invalid dynamic SQL or triggers. Below is a proc
that will generate and execute such a script for all views, functions and
procedures in the database. Note that it is still under development and
hasn't been tested thoroughly.
CREATE PROC #ValidateObjects
AS
SET NOCOUNT ON
--procedures and functions
SELECT
CASE r.ROUTINE_TYPE
WHEN 'PROCEDURE' THEN 'Procedure'
WHEN 'FUNCTION' THEN
CASE
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsTableFunction') = 1
THEN 'TableFunction'
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsScalarFunction') = 1
THEN 'ScalarFunction'
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsInlineFunction') = 1
THEN 'InlineFunction'
END
END AS ObjectType,
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS ObjectName,
REPLICATE(N'NULL,',
ISNULL((SELECT COUNT(*) AS Parameters
FROM INFORMATION_SCHEMA.PARAMETERS p
WHERE
p.IS_RESULT = 'NO' AND
p.SPECIFIC_SCHEMA = r.ROUTINE_SCHEMA AND
p.SPECIFIC_NAME = r.ROUTINE_NAME), 0)) AS Parameters
INTO #Objects
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE
ROUTINE_TYPE IN ('PROCEDURE', 'FUNCTION') AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
UNION ALL
--views
SELECT
'View' AS ObjectType,
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS ObjectName,
'' AS Parameters
FROM INFORMATION_SCHEMA.TABLES t
WHERE
TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
--remove trailing comma from parameter list
UPDATE #Objects
SET Parameters = LEFT(Parameters, LEN(Parameters) - 1)
WHERE RIGHT(Parameters, 1) = N','
--generate invocation scripts
SELECT
CASE ObjectType
WHEN 'View' THEN 'SELECT * FROM '
WHEN 'Procedure' THEN 'EXEC '
WHEN 'ScalarFunction' THEN 'SELECT '
WHEN 'InlineFunction' THEN 'SELECT * FROM '
WHEN 'TableFunction' THEN 'SELECT * FROM '
END +
RTRIM(ObjectName) +
CASE ObjectType
WHEN 'View' THEN ''
WHEN 'Procedure' THEN ' '
WHEN 'ScalarFunction' THEN '('
WHEN 'InlineFunction' THEN '('
WHEN 'TableFunction' THEN '('
END +
Parameters +
CASE ObjectType
WHEN 'View' THEN ''
WHEN 'Procedure' THEN ''
WHEN 'ScalarFunction' THEN ')'
WHEN 'InlineFunction' THEN ')'
WHEN 'TableFunction' THEN ')'
END
AS InvocationScript
INTO #InvocationScripts
FROM #Objects
ORDER BY ObjectName
DECLARE InvocationScripts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT InvocationScript
FROM #InvocationScripts
DECLARE @.InvocationScript nvarchar(4000)
OPEN InvocationScripts
WHILE 1 = 1
BEGIN
FETCH NEXT FROM InvocationScripts INTO @.InvocationScript
IF @.@.FETCH_STATUS = -1 BREAK
SET @.InvocationScript = 'PRINT ''' +
@.InvocationScript +
''' SET FMTONLY ON ' + @.InvocationScript + ' SET FMTONLY OFF'
EXEC sp_executesql @.InvocationScript
END
CLOSE InvocationScripts
DEALLOCATE InvocationScripts
DROP TABLE #Objects, #InvocationScripts
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Fran?ois Bourdages" <francois.bourdages@.harfan.com> wrote in message
news:92e05f1e.0410010722.7a57f90a@.posting.google.c om...
> Hi is there a way to know if object (view, function, etc) are invalid
> ?
> let say a have a table t1 (field col1, col2)
> and a view v1 (field t1.col1, t1.col2)
> if I drop t1.col2, the view v1 is not working anymore. I want to know
> that information.
> In Oracle (8.1.7), i can query the all_objects, user_object table,
> where status = 'INVALID'. So i can recompile invalid objects (or
> correct it).
> In sql Server, the table sysobjects give me some status info, but
> they are not documented enough.
> Do you know if i can user one of those fields : status, userstat,
> sysstat ?
> Same question for function , procedure.
> TKS.sql
This is driving me absolutley crazy. This function returns ProductIDs stripped out from a memo field based on a check on Product Type (which is also stripped for the checking) and returns a list of ProductIDs based on the type I'm checking for, in other words the @.FileFormat
The error: Invalid length parameter passed to the substring function.
I am passing 2 types of ProductDescriptions to my function below. This function returns ProductIDs by Type, stripped out of a large varchar field. The problem I'm having is that I have 2 checks that check whether the ProductID is MP3 or WAV. The second check is failing.
The 2 types of possible incoming ProductDescriptions for example are:
'These fully-orchestrated royalty free music tracks invoke the spirit of some of the great themes from 1970"s and 1980"s television and film productions and offer majestic brass, string and guitar melodies that will make a memorable addition to projects as background music and production music.<br><br><span class="product-name-no-link">You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a><br>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a><br>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a><br>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a><br>05. Final Choice <a href="ProductInfo.aspx?ProductID=105242">MP3</a> | <a href="ProductInfo.aspx?ProductID=105243">WAV</a><br>06. Fun and Free <a href="ProductInfo.aspx?ProductID=105244">MP3</a> | <a href="ProductInfo.aspx?ProductID=105245">WAV</a><br>07. Wayward Strangers <a href="ProductInfo.aspx?ProductID=105246">MP3</a> | <a href="ProductInfo.aspx?ProductID=105247">WAV</a><br>08. Savored Moments <a href="ProductInfo.aspx?ProductID=105248">MP3</a> | <a href="ProductInfo.aspx?ProductID=105249">WAV</a><br>09. Endless Searcher <a href="ProductInfo.aspx?ProductID=105250">MP3</a> | <a href="ProductInfo.aspx?ProductID=105251">WAV</a><br>10. Bach Piano <a href="ProductInfo.aspx?ProductID=105252">MP3</a> | <a href="ProductInfo.aspx?ProductID=105253">WAV</a><br>11. Fog Bound Mornings <a href="ProductInfo.aspx?ProductID=105254">MP3</a> | <a href="ProductInfo.aspx?ProductID=105255">WAV</a><br>'
OR
'Clapping percussion effects characterize this Hip Hop/Urban piece with train sound effects and strings.<br><br><b>Styles:</b> Dramatic,Reflective,Somber/dark<br><br><b>If you like this track, you can save more than <span style='color=#ff0000'>70%</span> by purchasing it on the following albums:</b><br><a href="ProductInfo.aspx?ProductID=106758">Hip-Hop / Urban</a><br><a href="ProductInfo.aspx?ProductID=106763">Documentary, Film, Television, General Production - Volume 2</a><br><br>Click <a href="ProductInfo.aspx?ProductID=105747">here</a> for the WAV version of this track.'
The current function I'm trying to fix:
ALTER FUNCTION [dbo].[GetProductChildIDs]
(
@.ProductDescription varchar(5500),
@.FileFormatvarchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
Declare @.Keyword varchar(30),
@.KeywordLen tinyint,
@.ProductID int,
@.Pos smallint,
@.StartPos smallint,
@.EndPos smallint,
@.Result varchar(1000),
@.valid int
SET @.valid = 0
SET @.Keyword = 'ProductInfo.aspx?ProductID='
SET @.KeywordLen = LEN(@.Keyword)
SET @.Result = ''
SET @.Pos = 1
WHILE @.Pos > 0
BEGIN
SET @.Pos = CHARINDEX(@.Keyword, @.ProductDescription, @.Pos)
If @.Pos > 0
BEGIN
SET @.StartPos = @.Pos + @.KeywordLen
SET @.EndPos = CHARINDEX('"', @.ProductDescription, @.StartPos + 1)
IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat
SET @.valid = 1
IF (len(@.ProductDescription) - @.StartPos) > 19
IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat
SET @.valid = 1
IF @.valid = 1
BEGIN
SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription, @.StartPos, @.EndPos - @.StartPos) + ','
END
SET @.Pos = @.EndPos + 1
END
END
RETURN SUBSTRING(@.Result,1,len(@.Result)-1)
END
The following checks to see whether the ProductID is WAV or MP3 by checking certain chars after each sequence 'ProductInfo.aspx?ProductID='
IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat - checks if it's a 'WAV' or 'MP3' based on the first type of incoming ProductDescription passed to this function at any time
IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat - checks to see if it's a 'WAV' or 'MP3' ProductID based on the second type of possible incoming ProductDescription.
I found out that the second check fails if there isn't 19 or more chars to check so I tried putting this in:
So I'm not sure how to handle the Nulls in the second IF statement if there are not enough chars to check in the loop for each SUBSTRING(@.ProductDescription, @.EndPos + 19, 3)
ALTER FUNCTION [dbo].[GetProductChildIDs]
(
@.ProductDescription varchar(5500),
@.FileFormatvarchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
Declare @.Keyword varchar(30),
@.KeywordLen tinyint,
@.ProductID int,
@.Pos smallint,
@.StartPos smallint,
@.EndPos smallint,
@.Result varchar(1000),
@.valid int
SET @.valid = 0
SET @.Keyword = 'ProductInfo.aspx?ProductID='
SET @.KeywordLen = LEN(@.Keyword)
SET @.Result = ''
SET @.Pos = 1
WHILE @.Pos > 0
BEGIN
SET @.Pos = CHARINDEX(@.Keyword, @.ProductDescription, @.Pos)
If @.Pos > 0
BEGIN
SET @.StartPos = @.Pos + @.KeywordLen
SET @.EndPos = CHARINDEX('"', @.ProductDescription, @.StartPos + 1)
IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat
SET @.valid = 1
IF (len(@.ProductDescription) - @.StartPos) > 19
IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat
SET @.valid = 1
--IF @.valid = 1
BEGIN
SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription, @.StartPos, @.EndPos - @.StartPos) + ','
END
SET @.Pos = @.EndPos + 1
END
END
RETURN SUBSTRING(@.Result,1,len(@.Result)-1)
END
|||I since then have figured it out by extensive testing and added some stuff so that it will behave...but let me take a look at what you had to suggest also. Thanks for the suggestion though!
ALTER FUNCTION [dbo].[GetProductChildIDs]
(
@.ProductDescription varchar(5500),
@.FileFormat varchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
Declare @.Keyword varchar(30),
@.KeywordLen tinyint,
@.ProductID int,
@.Pos smallint,
@.StartPos smallint,
@.EndPos smallint,
@.Result varchar(1000),
@.ResultToReturn varchar(1000),
@.valid int,
@.MP3Pos int,
@.WavPos int
SET @.valid = 0
SET @.Keyword = 'ProductInfo.aspx?ProductID='
SET @.KeywordLen = LEN(@.Keyword)
SET @.Result = ''
SET @.ResultToReturn = ''
SET @.Pos = 1
WHILE @.Pos > 0
BEGIN
SET @.Pos = CHARINDEX(@.Keyword, @.ProductDescription, @.Pos)
If @.Pos > 0
BEGIN
SET @.StartPos = @.Pos + @.KeywordLen
SET @.EndPos = CHARINDEX('"', @.ProductDescription, @.StartPos + 1)
IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat
BEGIN
SET @.valid = 1
END
IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat
BEGIN
SET @.valid = 1
END
IF @.valid = 1
BEGIN
SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription, @.StartPos, @.EndPos - @.StartPos) + ','
END
SET @.Pos = @.EndPos + 1
END
END
IF len(@.Result) > 6
SET @.ResultToReturn = @.ResultToReturn + SUBSTRING(@.Result,1,len(@.Result)-1)
RETURN @.ResultToReturn
END
|||why did you comment out the If @.valid = 1?
here's my mdx code for my parameter (CrashVehicleVehicleType)
WITH MEMBER [Measures].[ParameterCaption] AS '[Crash Vehicle].[Vehicle Type].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Crash Vehicle].[Vehicle Type].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Crash Vehicle].[Vehicle Type].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Crash Vehicle].[Vehicle Type].ALLMEMBERS ON ROWS FROM [SDMTest]
why do i keep getting this error if i have a parameter value and caption
[rsInvalidDataSetReferenceField] The report parameter ‘CrashVehicleVehicleType’ uses the field ‘ParameterValue’ in a data set reference, but the data set ‘CrashVehicleVehicleType’ does not contain that field.
Check the XML Code behind page of each report.http://jhermiz.googlepages.com|||
this is the xml behind the report im working on.....It says the value and caption is in these datasets?! i just dont get it.
</DataSet>
<DataSet Name="FromDateCalendarDate">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</CommandText>
<DataSourceName>NewCrashTest</DataSourceName>
<rd:AutoGenerated>true</rd:AutoGenerated>
<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items /></Select><From>SDMTest</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>
<rd:Hidden>true</rd:Hidden>
</Query>
</DataSet>
<DataSet Name="ToDateCalendarDate">
<Query>
<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</CommandText>
<DataSourceName>NewCrashTest</DataSourceName>
<rd:AutoGenerated>true</rd:AutoGenerated>
<rd:MdxQuery><QueryDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items /></Select><From>SDMTest</From></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</Statement></Query></QueryDefinition></rd:MdxQuery>
<rd:Hidden>true</rd:Hidden>
</Query>
<Fields>
If I run each of the subreports by themselves, entering in value for the parameter, they run fine.
When I try to run the master report, it runs, but the subreport sections contain the following text instead of the correct output: "Error: Subreport could not be shown." The Output window shows 2 warning, one for each subreport:
The expression use in subreport 'subreport1' returned a data type that is not valid.I don't know for a fact that it is the parameter causing the problem, because the error message has a blank for the expression that is having the problem.
Each subreport is a simple table with one column containing string fields from a simple query.
Anyone have any ideas?It sounds like you are trying to pass GUIDs as parameters to the subreport.
On the subreport parameters dialog, use the CStr(...) function to convert the Guids to strings. E.g. =CStr(Fields!GuidColumn.Value)
-- Robert|||Guys:
I am having the same problem. I tried the CStr() function on the parameters to the SubReport, but had the same result. Any more ideas?
Thanks,
Mike|||Whoops! I am getting a different error when using the CStr() function:
Cast from type 'Guid' to type 'String' is not valid.
|||I think I originally tried Robert's suggestion and was still giving an error like you found.
What I ended up doing was going into the queries for both the master and sub report and using "(CAST ScriptID AS varchar(255))" in the queries themselves. Then, these string fields linked up without a problem.
Gregg
|||Sorry, I meant using =Fields!GuidColum.Value.ToString() to convert it into a string. Anyway, the CAST in the query is also fine.-- Robert
If I run each of the subreports by themselves, entering in value for the parameter, they run fine.
When I try to run the master report, it runs, but the subreport sections contain the following text instead of the correct output: "Error: Subreport could not be shown." The Output window shows 2 warning, one for each subreport:
The expression use in subreport 'subreport1' returned a data type that is not valid.I don't know for a fact that it is the parameter causing the problem, because the error message has a blank for the expression that is having the problem.
Each subreport is a simple table with one column containing string fields from a simple query.
Anyone have any ideas?It sounds like you are trying to pass GUIDs as parameters to the subreport.
On the subreport parameters dialog, use the CStr(...) function to convert the Guids to strings. E.g. =CStr(Fields!GuidColumn.Value)
-- Robert|||Guys:
I am having the same problem. I tried the CStr() function on the parameters to the SubReport, but had the same result. Any more ideas?
Thanks,
Mike|||Whoops! I am getting a different error when using the CStr() function:
Cast from type 'Guid' to type 'String' is not valid.
|||I think I originally tried Robert's suggestion and was still giving an error like you found.
What I ended up doing was going into the queries for both the master and sub report and using "(CAST ScriptID AS varchar(255))" in the queries themselves. Then, these string fields linked up without a problem.
Gregg
|||Sorry, I meant using =Fields!GuidColum.Value.ToString() to convert it into a string. Anyway, the CAST in the query is also fine.-- Robert
If I run each of the subreports by themselves, entering in value for the parameter, they run fine.
When I try to run the master report, it runs, but the subreport sections contain the following text instead of the correct output: "Error: Subreport could not be shown." The Output window shows 2 warning, one for each subreport:
The expression use in subreport 'subreport1' returned a data type that is not valid.I don't know for a fact that it is the parameter causing the problem, because the error message has a blank for the expression that is having the problem.
Each subreport is a simple table with one column containing string fields from a simple query.
Anyone have any ideas?It sounds like you are trying to pass GUIDs as parameters to the subreport.
On the subreport parameters dialog, use the CStr(...) function to convert the Guids to strings. E.g. =CStr(Fields!GuidColumn.Value)
-- Robert|||Guys:
I am having the same problem. I tried the CStr() function on the parameters to the SubReport, but had the same result. Any more ideas?
Thanks,
Mike|||Whoops! I am getting a different error when using the CStr() function:
Cast from type 'Guid' to type 'String' is not valid.
|||I think I originally tried Robert's suggestion and was still giving an error like you found.
What I ended up doing was going into the queries for both the master and sub report and using "(CAST ScriptID AS varchar(255))" in the queries themselves. Then, these string fields linked up without a problem.
Gregg
|||Sorry, I meant using =Fields!GuidColum.Value.ToString() to convert it into a string. Anyway, the CAST in the query is also fine.-- Robert
Hi!
Trying to generate a report (using WebForm ReportViewer, from dynamically created RDL report and SQL Server 2005 OLAP cube), if a database field contains control characters (code < 0x20), Reporting Services generate following message:
' ', hexadecimal value 0x02, is an invalid character. Line 1, position 2376.
Is it possible to ignore that? I don't care if browser shows an octopus, the report must work.
Thanks, Andrei.
Could you publish RDL (or e-mail it to me)?
thanks!
|||Lev,
I've just emailed RDL and other details to you.
I could reproduce the problem using sample AdventureWorksDW and OLAP (standard edition).
Create an OLAP table report, put e.g. Model Name in one of columns. The report works fine. Now change e.g. ModelName for one of products, to include control character(s), e.g.:
update DimProduct
set ModelName = 'Mountain-100 ' + char(31) + char(2) + ' AB'
where ProductAlternateKey = 'BK-M82S-38'
Reprocess Product dimension.
Refresh the report. Once Montain-100 model is about to be displayed on the page you should get following message:
hexadecimal value 0x1F, is an invalid character. Line 1, position 2385.
I've just noticed that after the change applied even OLAP browser of SQL Server Management Studio generates the same error if only the ModelName is about to be displayed.
So it may be Analysis Service's problem indeed.
I've tried with non-OLAP reports in Reporting Services, and they work fine, displaying square placeholders.
|||That is known AS issue. Certain control characters cannot be transmitted from server to client.Hi!
Trying to generate a report (using WebForm ReportViewer, from dynamically created RDL report and SQL Server 2005 OLAP cube), if a database field contains control characters (code < 0x20), Reporting Services generate following message:
' ', hexadecimal value 0x02, is an invalid character. Line 1, position 2376.
Is it possible to ignore that? I don't care if browser shows an octopus, the report must work.
Thanks, Andrei.
Could you publish RDL (or e-mail it to me)?
thanks!
|||Lev,
I've just emailed RDL and other details to you.
I could reproduce the problem using sample AdventureWorksDW and OLAP (standard edition).
Create an OLAP table report, put e.g. Model Name in one of columns. The report works fine. Now change e.g. ModelName for one of products, to include control character(s), e.g.:
update DimProduct
set ModelName = 'Mountain-100 ' + char(31) + char(2) + ' AB'
where ProductAlternateKey = 'BK-M82S-38'
Reprocess Product dimension.
Refresh the report. Once Montain-100 model is about to be displayed on the page you should get following message:
hexadecimal value 0x1F, is an invalid character. Line 1, position 2385.
I've just noticed that after the change applied even OLAP browser of SQL Server Management Studio generates the same error if only the ModelName is about to be displayed.
So it may be Analysis Service's problem indeed.
I've tried with non-OLAP reports in Reporting Services, and they work fine, displaying square placeholders.
|||That is known AS issue. Certain control characters cannot be transmitted from server to client.