Customised totals

Hi,

I am creating a report using matrix. The reporting services subtotal, sums up the data on all rows. The data in rows contains numeric & percentage values. I need the total for numeric value as sum and for percentage values as weighted average. Does anyone know how to do this or is this limitation of RS. Thanks.

You mean you want to have different calculation done in the subtotal as oppose to the one used in detail cells? If so, you can use the InScope function to do it. http://msdn2.microsoft.com/en-us/library/ms156490.aspxsql

Custom Rollup Dimension for AVG

I am trying to build a dimension and a cube that contains a measure of Avg Family Size and a dimension that group different types of families into different categories.

The dimension is a parent-child type but the parent should be the AVG of all children instead of SUM.

I tried to use Custom Rollup feature in dimension (there are 2 places, one during the dimension setup in pop-up window, there is check box then to define with MDX or use a column with unitary operator.Another place is in dimension Properties after dimension is setup).I cannot get it to work no matter what I do in dimension with MDX.The cube display #ERR for each cell.

Also, on cube setup, measure setup has a property of Aggregation.It has SUM, MAX, MIN, COUNT, DISTINCT COUNT but just does not have AVG.

So far, the only way is to define a calc measure with such MDX:

IIF(IsLeaf([Test1].CurrentMemeber), [Measure].size, AVG([Test1].CurrentMember.Children, [Measuer].size) )

This is sort of awkward because it renders the original Measure useless.

Any ideas to do this directly in Dimension or in Mesaure? This is for AS 2000.

One of the standard way of doing an average in AS is to set up 2 measures, a SUM and a COUNT and then create a calculated measure for the average and set it up as the SUM / COUNT. If you don't need the original SUM and COUNT measures you can set their visible properties to false. This would create an average for a single measure.

If you had a lot of measures and they all have to be averaged you could try something like one the following which I think should work across all measures (but I have not tested it)

AVG([Test1].CurrentMember,1))

OR

SUM(descendants([Test1].CurrentMember,1))/COUNT(descendants([Test1].CurrentMember,1))

The descendants(<member>,1) will go down 1 level if there is one, otherwise it will return the member itself, which helps avoid the IIF(IsLeaf(...)) test. And not explicitly stating a measure *should* mean that the query will use the current measure context.

Custom Rendering Extension for HTML Fields

I am using RS 2005, a "comments" field in a database contains HTML content. When rendering the field on the report it displays raw HTML.

Does anybody knows about a custom rendering extension that will render HTML content?

Thanks

Fernando

Has anyone resolved this? I am facing a similar issue. My website has form that takes comments via FreeTextBox. It is probably similar to what is used on this FORUM! The FreeTextBox control returns text with HTML tags throughout. I need to display the same data via Reporting Services. Currently using SQL Server 2000, Migrating to 2005 soon.

This has to be a common scenario. How about a third party control?

Thanks.

|||

This is currently not supported. See http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx. We are working on this for an upcoming release.

Custom Rendering Extension for HTML Fields

I am using RS 2005, a "comments" field in a database contains HTML content. When rendering the field on the report it displays raw HTML.

Does anybody knows about a custom rendering extension that will render HTML content?

Thanks

Fernando

Has anyone resolved this? I am facing a similar issue. My website has form that takes comments via FreeTextBox. It is probably similar to what is used on this FORUM! The FreeTextBox control returns text with HTML tags throughout. I need to display the same data via Reporting Services. Currently using SQL Server 2000, Migrating to 2005 soon.

This has to be a common scenario. How about a third party control?

Thanks.

|||

This is currently not supported. See http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx. We are working on this for an upcoming release.

Custom Rendering Extension for HTML Fields

I am using RS 2005, a "comments" field in a database contains HTML content. When rendering the field on the report it displays raw HTML.

Does anybody knows about a custom rendering extension that will render HTML content?

Thanks

Fernando

Has anyone resolved this? I am facing a similar issue. My website has form that takes comments via FreeTextBox. It is probably similar to what is used on this FORUM! The FreeTextBox control returns text with HTML tags throughout. I need to display the same data via Reporting Services. Currently using SQL Server 2000, Migrating to 2005 soon.

This has to be a common scenario. How about a third party control?

Thanks.

|||

This is currently not supported. See http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx. We are working on this for an upcoming release.

Custom error messages

Some of our reports use a stored proc as the source for base data. The proc contains RAISEERROR statements to handle error scenarios.

Is there a way in which I could display the message that is output from the RAISEERROR statements in the report?

ThanksThis is not natively supported. You would need to return it as a data column.

Custom Delivery Extension

Hi,
I'm writing a custom delivery extension that saves the rendered report
to the disk.
My problem is that the output file only contains spaces (the file size
is excatly what it should be but no text only black spaces).
Does anyone know why spaces appear instead of chars ?
this is the code for excel but the same happens with mhtml:
Dim deviceInfo, format As String
format = "EXCEL"
deviceInfo = String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
format)
' Render report
m_files = notification.Report.Render(format, deviceInfo)
Dim fileName As String
fileName = "C:\" & m_files(0).FileName & ".xls"
Dim results(CInt(m_files(0).Data.Length)) As Byte
m_files(0).Data.Read(results, 0, CInt(m_files(0).Data.Length))
Dim _filestream As System.IO.FileStream = System.IO.File.OpenWrite(fileName)
_filestream.Write(results, 0, CInt(m_files(0).Data.Length))
_filestream.Flush()
_filestream.Close()You need to take the encoding of the stream into account. Use a stream
reader to read the contents passing in the encoding of the stream, and then
use a stream writer to write to the filestream, also passing in the
encoding.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"kman" <kamara1@.yahoo.com> wrote in message
news:1106326857.837797.301490@.z14g2000cwz.googlegroups.com...
> Hi,
> I'm writing a custom delivery extension that saves the rendered report
> to the disk.
> My problem is that the output file only contains spaces (the file size
> is excatly what it should be but no text only black spaces).
> Does anyone know why spaces appear instead of chars ?
> this is the code for excel but the same happens with mhtml:
> Dim deviceInfo, format As String
> format = "EXCEL"
> deviceInfo => String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
> format)
> ' Render report
> m_files = notification.Report.Render(format, deviceInfo)
> Dim fileName As String
> fileName = "C:\" & m_files(0).FileName & ".xls"
> Dim results(CInt(m_files(0).Data.Length)) As Byte
> m_files(0).Data.Read(results, 0, CInt(m_files(0).Data.Length))
> Dim _filestream As System.IO.FileStream => System.IO.File.OpenWrite(fileName)
> _filestream.Write(results, 0, CInt(m_files(0).Data.Length))
> _filestream.Flush()
> _filestream.Close()
>|||Thanks,
I tried what you've suggested but i think i'm doing something wrong
because now i'm getting a zero byte file.
can you provide the code for that?
here's the code i added:
Dim encode As Encoding = m_files(0).Encoding
Dim _readStream As New StreamReader(m_files(0).Data, encode)
Dim _filestream As New System.IO.FileStream(fileName,
FileMode.OpenOrCreate)
Dim _writeStream As New StreamWriter(_filestream, encode)
_writeStream.Write(_readStream.ReadToEnd)
_writeStream.Close()
_readStream.Close()
_FileStream.Close()|||Daniel, somebody,
Please help ...|||You're correct - for excel there's no encoding. I tried the mhtml
format which has ASCIIEncoding.
I'm only sending the format in the device info:
String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",format)
I'm not sure what you mean to pass via url, i've tried to export the
report from the report manager and it works fine.
Can you post how to read it like you do ?|||Try not passing in this device info and instead pass the format in on the
render call only.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"kman" <kamara1@.yahoo.com> wrote in message
news:1106853884.827838.300370@.z14g2000cwz.googlegroups.com...
> You're correct - for excel there's no encoding. I tried the mhtml
> format which has ASCIIEncoding.
> I'm only sending the format in the device info:
> String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",format)
> I'm not sure what you mean to pass via url, i've tried to export the
> report from the report manager and it works fine.
> Can you post how to read it like you do ?
>|||i tried - notification.Report.Render(format, Nothing), but still
there's a black file.
i checked the findrendersave sample and it works fine there but i can't
use the render method directly from the web service because i need the
subscription info (parameters ...)
any ideas ?|||I'm sorry to say, I don't see what the problem is. I can't see anything
wrong with what you are doing. It should work.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"kman" <kamara1@.yahoo.com> wrote in message
news:1106909217.659089.277880@.z14g2000cwz.googlegroups.com...
>i tried - notification.Report.Render(format, Nothing), but still
> there's a black file.
> i checked the findrendersave sample and it works fine there but i can't
> use the render method directly from the web service because i need the
> subscription info (parameters ...)
> any ideas ?
>|||Hi,
Solved it. It wasn't the encoding after all but the position on the
data stream.
I added this line and it solved it:
_renderedOutputFile.Data.Seek(CType(0, Long),
System.IO.SeekOrigin.Begin)
Thanks for your help.
This is the complete code for saving the file (works for excel, mhtml
and pdf):
m_files = notification.Report.Render(format, Nothing)
Dim _fileStream As System.IO.FileStream = Nothing
Dim _renderedOutputFile As RenderedOutputFile
_renderedOutputFile = m_files(0)
Dim fileName As String = "C:\" & _renderedOutputFile.FileName.Trim &
"." & _renderedOutputFile.Extension.Trim
_fileStream = New System.IO.FileStream(fileName, FileMode.OpenOrCreate,
FileAccess.Write)
_renderedOutputFile.Data.Seek(CType(0, Long),
System.IO.SeekOrigin.Begin)
Dim arr(CInt(_renderedOutputFile.Data.Length)) As Byte
_renderedOutputFile.Data.Read(arr, 0,
CInt(_renderedOutputFile.Data.Length))
_fileStream.Write(arr, 0, CInt(_renderedOutputFile.Data.Length))
_fileStream.Close()

Custom code using case statement

My report contains a field which shows a '# of days on Hand' column. This
column is the result of a datediff calc. I am not familar with vb.net but I
need to add custom code logic so that if the value in '# of days on hand" is
null that it shows a hardcoded text like "inv" or "onhand". The rest of the
value are >= 0 and I would just want to show there values as-is. Can anyone
show me what the a sample of code would look like to do this and how I call
this in my report?
ANY HELP IS MUCH APPRECIATEDAn expression would work well in this situation
=iif(Fields!column1.Value < 0,"inv",Fields!column1.Value)
I haven't tested this but it should work. If the expression evaluates to
true, i.e. If the value of the field is less than zero, then the string
'inv' is returned, if false, the field's value is returned as-is.
Put this in your column and replace Fields!column1 with whatever your field
is called.
HTH
"stacey" wrote:
> My report contains a field which shows a '# of days on Hand' column. This
> column is the result of a datediff calc. I am not familar with vb.net but I
> need to add custom code logic so that if the value in '# of days on hand" is
> null that it shows a hardcoded text like "inv" or "onhand". The rest of the
> value are >= 0 and I would just want to show there values as-is. Can anyone
> show me what the a sample of code would look like to do this and how I call
> this in my report?
> ANY HELP IS MUCH APPRECIATED

Custom Assembly Security Error

I have created a custom assembly that contains a function that uses
System.DirectoryServices to determine the user groups a user belongs to. It
works fine in development (preview) but returns the following error on the
report server:
"Request for the permission of type
System.Security.Permissions.SecurityPermission, mscorlib, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 failed."
- I have another function in the same assembly that uses
System.Data.SQLClient to access a database and return values - this works
fine.
- I have included a codegroup for the assembly in rssrvpolicy.config with
the FullTrust permissionset.
- I have tried combinations of the following in the assemblyInfo.cs file:
[assembly: DirectoryServicesPermission(SecurityAction.RequestMinimum,
PermissionAccess=DirectoryServicesPermissionAccess.Browse)]
[assembly:
System.Security.Permissions.SecurityPermission(SecurityAction.RequestMinimum,
Unrestricted = true)]
[assembly: AllowPartiallyTrustedCallers]
- I have tried the following at the top of the function:
System.DirectoryServices.DirectoryServicesPermission pDir = new
DirectoryServicesPermission(System.Security.Permissions.PermissionState.Unrestricted);
System.DirectoryServices.DirectoryServicesPermissionEntry pDirEnt = new
DirectoryServicesPermissionEntry(System.DirectoryServices.DirectoryServicesPermissionAccess.Browse,"");
pDir.PermissionEntries.Add(pDirEnt);
pDir.Assert();
Please help!
I have been trying to solve this for ages and just cannot find a solution.
Regards,
Flon MackenzieHey, are you using 1.1 of the framework on the web server? If you do have it
installed, it doesn't look like it is installed for that virtual web.
I would like to suggest to first refresh the framwork installation on the
web server.
in %systemroot%\microsoft.net\framework\v1.1.4322\
installutil /i
The only reason I say that is because this number bugs me: "...mscorlib,
Version=1.0.5000.0, "
=-Chris
"Flon" <Flon@.discussions.microsoft.com> wrote in message
news:0725157A-2C8D-4CCE-B382-A6658FE98449@.microsoft.com...
>I have created a custom assembly that contains a function that uses
> System.DirectoryServices to determine the user groups a user belongs to.
> It
> works fine in development (preview) but returns the following error on the
> report server:
> "Request for the permission of type
> System.Security.Permissions.SecurityPermission, mscorlib,
> Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed."
> - I have another function in the same assembly that uses
> System.Data.SQLClient to access a database and return values - this works
> fine.
> - I have included a codegroup for the assembly in rssrvpolicy.config with
> the FullTrust permissionset.
> - I have tried combinations of the following in the assemblyInfo.cs file:
> [assembly: DirectoryServicesPermission(SecurityAction.RequestMinimum,
> PermissionAccess=DirectoryServicesPermissionAccess.Browse)]
> [assembly:
> System.Security.Permissions.SecurityPermission(SecurityAction.RequestMinimum,
> Unrestricted = true)]
> [assembly: AllowPartiallyTrustedCallers]
> - I have tried the following at the top of the function:
> System.DirectoryServices.DirectoryServicesPermission pDir = new
> DirectoryServicesPermission(System.Security.Permissions.PermissionState.Unrestricted);
> System.DirectoryServices.DirectoryServicesPermissionEntry pDirEnt = new
> DirectoryServicesPermissionEntry(System.DirectoryServices.DirectoryServicesPermissionAccess.Browse,"");
> pDir.PermissionEntries.Add(pDirEnt);
> pDir.Assert();
> Please help!
> I have been trying to solve this for ages and just cannot find a solution.
> Regards,
> Flon Mackenzie
>|||I just verified the version on my server with the 1.1 version of the
framework, and my version says:
1.1.4322.2251 for MSCORLIB.DLL.
=-Chris
"Christopher Conner" <someone@.someplace.com> wrote in message
news:On91kx3EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Hey, are you using 1.1 of the framework on the web server? If you do have
> it installed, it doesn't look like it is installed for that virtual web.
> I would like to suggest to first refresh the framwork installation on the
> web server.
> in %systemroot%\microsoft.net\framework\v1.1.4322\
> installutil /i
> The only reason I say that is because this number bugs me: "...mscorlib,
> Version=1.0.5000.0, "
> =-Chris
>
> "Flon" <Flon@.discussions.microsoft.com> wrote in message
> news:0725157A-2C8D-4CCE-B382-A6658FE98449@.microsoft.com...
>>I have created a custom assembly that contains a function that uses
>> System.DirectoryServices to determine the user groups a user belongs to.
>> It
>> works fine in development (preview) but returns the following error on
>> the
>> report server:
>> "Request for the permission of type
>> System.Security.Permissions.SecurityPermission, mscorlib,
>> Version=1.0.5000.0,
>> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed."
>> - I have another function in the same assembly that uses
>> System.Data.SQLClient to access a database and return values - this works
>> fine.
>> - I have included a codegroup for the assembly in rssrvpolicy.config with
>> the FullTrust permissionset.
>> - I have tried combinations of the following in the assemblyInfo.cs file:
>> [assembly: DirectoryServicesPermission(SecurityAction.RequestMinimum,
>> PermissionAccess=DirectoryServicesPermissionAccess.Browse)]
>> [assembly:
>> System.Security.Permissions.SecurityPermission(SecurityAction.RequestMinimum,
>> Unrestricted = true)]
>> [assembly: AllowPartiallyTrustedCallers]
>> - I have tried the following at the top of the function:
>> System.DirectoryServices.DirectoryServicesPermission pDir = new
>> DirectoryServicesPermission(System.Security.Permissions.PermissionState.Unrestricted);
>> System.DirectoryServices.DirectoryServicesPermissionEntry pDirEnt = new
>> DirectoryServicesPermissionEntry(System.DirectoryServices.DirectoryServicesPermissionAccess.Browse,"");
>> pDir.PermissionEntries.Add(pDirEnt);
>> pDir.Assert();
>> Please help!
>> I have been trying to solve this for ages and just cannot find a
>> solution.
>> Regards,
>> Flon Mackenzie
>|||I meant aspnet_regiis /i not installutil /i DOH!
Sorry!
=-Chris
"Christopher Conner" <someone@.someplace.com> wrote in message
news:On91kx3EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Hey, are you using 1.1 of the framework on the web server? If you do have
> it installed, it doesn't look like it is installed for that virtual web.
> I would like to suggest to first refresh the framwork installation on the
> web server.
> in %systemroot%\microsoft.net\framework\v1.1.4322\
> installutil /i
> The only reason I say that is because this number bugs me: "...mscorlib,
> Version=1.0.5000.0, "
> =-Chris
>
> "Flon" <Flon@.discussions.microsoft.com> wrote in message
> news:0725157A-2C8D-4CCE-B382-A6658FE98449@.microsoft.com...
>>I have created a custom assembly that contains a function that uses
>> System.DirectoryServices to determine the user groups a user belongs to.
>> It
>> works fine in development (preview) but returns the following error on
>> the
>> report server:
>> "Request for the permission of type
>> System.Security.Permissions.SecurityPermission, mscorlib,
>> Version=1.0.5000.0,
>> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed."
>> - I have another function in the same assembly that uses
>> System.Data.SQLClient to access a database and return values - this works
>> fine.
>> - I have included a codegroup for the assembly in rssrvpolicy.config with
>> the FullTrust permissionset.
>> - I have tried combinations of the following in the assemblyInfo.cs file:
>> [assembly: DirectoryServicesPermission(SecurityAction.RequestMinimum,
>> PermissionAccess=DirectoryServicesPermissionAccess.Browse)]
>> [assembly:
>> System.Security.Permissions.SecurityPermission(SecurityAction.RequestMinimum,
>> Unrestricted = true)]
>> [assembly: AllowPartiallyTrustedCallers]
>> - I have tried the following at the top of the function:
>> System.DirectoryServices.DirectoryServicesPermission pDir = new
>> DirectoryServicesPermission(System.Security.Permissions.PermissionState.Unrestricted);
>> System.DirectoryServices.DirectoryServicesPermissionEntry pDirEnt = new
>> DirectoryServicesPermissionEntry(System.DirectoryServices.DirectoryServicesPermissionAccess.Browse,"");
>> pDir.PermissionEntries.Add(pDirEnt);
>> pDir.Assert();
>> Please help!
>> I have been trying to solve this for ages and just cannot find a
>> solution.
>> Regards,
>> Flon Mackenzie
>|||Thanks for your suggestion.
I have no idea what the 1.0.5000.0 is for.
I have reinstalled .net framework 1.1 and my version of mscorlib.dll
is 1.1.4322.573.
Still no change.
I have also tried the following in my function:
System.Security.Permissions.SecurityPermission pSec = new
SecurityPermission(System.Security.Permissions.PermissionState.Unrestricted);
pSec.Assert();
System.DirectoryServices.DirectoryServicesPermission pDir = new
DirectoryServicesPermission(System.Security.Permissions.PermissionState.Unrestricted);
pDir.Assert();
which results in this error:
"Stack walk modifier must be reverted before another modification of the
same type can be performed."
"Christopher Conner" wrote:
> I meant aspnet_regiis /i not installutil /i DOH!
> Sorry!
> =-Chris
> "Christopher Conner" <someone@.someplace.com> wrote in message
> news:On91kx3EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> > Hey, are you using 1.1 of the framework on the web server? If you do have
> > it installed, it doesn't look like it is installed for that virtual web.
> >
> > I would like to suggest to first refresh the framwork installation on the
> > web server.
> >
> > in %systemroot%\microsoft.net\framework\v1.1.4322\
> >
> > installutil /i
> >
> > The only reason I say that is because this number bugs me: "...mscorlib,
> > Version=1.0.5000.0, "
> >
> > =-Chris
> >
> >
> > "Flon" <Flon@.discussions.microsoft.com> wrote in message
> > news:0725157A-2C8D-4CCE-B382-A6658FE98449@.microsoft.com...
> >>I have created a custom assembly that contains a function that uses
> >> System.DirectoryServices to determine the user groups a user belongs to.
> >> It
> >> works fine in development (preview) but returns the following error on
> >> the
> >> report server:
> >>
> >> "Request for the permission of type
> >> System.Security.Permissions.SecurityPermission, mscorlib,
> >> Version=1.0.5000.0,
> >> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed."
> >>
> >> - I have another function in the same assembly that uses
> >> System.Data.SQLClient to access a database and return values - this works
> >> fine.
> >>
> >> - I have included a codegroup for the assembly in rssrvpolicy.config with
> >> the FullTrust permissionset.
> >>
> >> - I have tried combinations of the following in the assemblyInfo.cs file:
> >> [assembly: DirectoryServicesPermission(SecurityAction.RequestMinimum,
> >> PermissionAccess=DirectoryServicesPermissionAccess.Browse)]
> >> [assembly:
> >> System.Security.Permissions.SecurityPermission(SecurityAction.RequestMinimum,
> >> Unrestricted = true)]
> >> [assembly: AllowPartiallyTrustedCallers]
> >>
> >> - I have tried the following at the top of the function:
> >> System.DirectoryServices.DirectoryServicesPermission pDir = new
> >> DirectoryServicesPermission(System.Security.Permissions.PermissionState.Unrestricted);
> >> System.DirectoryServices.DirectoryServicesPermissionEntry pDirEnt = new
> >> DirectoryServicesPermissionEntry(System.DirectoryServices.DirectoryServicesPermissionAccess.Browse,"");
> >> pDir.PermissionEntries.Add(pDirEnt);
> >> pDir.Assert();
> >>
> >> Please help!
> >> I have been trying to solve this for ages and just cannot find a
> >> solution.
> >>
> >> Regards,
> >> Flon Mackenzie
> >>
> >
> >
>
>