We have may reports that need to customize the where clause based on
parameters entered by the end user. Is there a way to modify the RDL on the
fly in order to achieve this? I am using VS2005 and SQLServer 2005.
Example:
if user selects run report by week ending date the following line needs to
be used
"and we_dt between ? and ?
if the user selects the run the report by export date then the line changes
to:
"and export_dt between ? and ?You can handle this via expressions in the command text of the dataset.
It is much easier than modifying the RDL on the fly.
For example, you can use VB expressions in the command text to
something like this:
="SELECT field1, field2 FROM tblName WHERE " &
iif(Parameter!Param1.Value = something, "do between stuff", "don't do
between stuff")
Andy Potter|||We'll look into this, but what if the entire WHERE cluase needs to be modified?
Are there any good examples of people doing this? Either through changing
the RDL or passing the entire where clause into the report?
Thanks
"Potter" wrote:
> You can handle this via expressions in the command text of the dataset.
> It is much easier than modifying the RDL on the fly.
> For example, you can use VB expressions in the command text to
> something like this:
> ="SELECT field1, field2 FROM tblName WHERE " &
> iif(Parameter!Param1.Value = something, "do between stuff", "don't do
> between stuff")
> Andy Potter
>|||Using expressions, your entire command text is available for
manipulation. Just do something like this:
="SELECT field1, field2 FROM tblName " & iif(Parameter!Param1.Value ="all", "", "WHERE field=" & Parameter!Param1.Value )
Personally, I prefer this kind of logic in a stored procedure. I find
large expressions to handle string manipulation to be a bit unwieldy.
The other option to handle large string manipulation is the custom code
section of the report, which give you a little more flexibility as far
writing your string manipulation code.
Andy Potter
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Customer Sales based on YTM dimension
Hello,
We have a cube that has customer sales data for last 5 years. Time Dimension displays the YTM hierarchy.
=> Selecting "month" is a parameter for the user on a Reporting Service report. Once he selects a month from YTM hierarchy - how to get list of "only those customers" whose sales have been continuously below say 80,000 dollars, beginning the month he selects as a "start month" until "next 6 months".
Multiple selection - not allowed. Only one month can be selected by user at any time.
Any help highly appreciated.
Thanks,
RajShri
Here's an Adventure Works example, which lists all customers with < $1000 in sales for each of 6 months, starting with the selected month (here, Jan. 2004). Note that this includes customers with no sales as well:
>>
With
Member [Measures].[Max6MonthSales] as
Max(LastPeriods(-6,
OpeningPeriod([Date].[Calendar].[Month])),
[Measures].[Internet Sales Amount])
Set [LowSalesCustomers] as
Filter([Customer].[Customer Geography].[Full Name].Members,
[Measures].[Max6MonthSales] < 1000)
select {[Measures].[Internet Sales Amount],
[Measures].[Max6MonthSales]} on 0,
[LowSalesCustomers] on 1
from [Adventure Works]
where [Date].[Calendar].[Month].&[2004]&[1]
>>
sqlThursday, March 22, 2012
Custom unique ID.
Hello all,
I baddly need to create my own datatype, that will be able to generate
itself as unique id.
1. my user defined data type is based on char(14).
2. structure of this type is
YYYYMMDDSCIDCONT
YYYY = year of record creation
MM = month of record creation
DD = day of record creation
SCID = value of SCID column in the table
CONT = counter (like identity seed) next unique value in the table
I can to do it by my .NET application, or i can make unique keys with all
values in the table, but I would like to create my own SQL type, that will
be able to do what i described. Is it possible?
Thanks.Maybe I should better explain this or ask for some questions.
My task Im going to do:
1) create my own User Defined Data Type based on char(16) named for example
MyIDType
2) create rule that will check if the value is in right format.
3) create function, that will able to generate new id with parameters
datetime, scid. This fuction will parse the datetime, scid and newly created
counter into char(16) and into MyIDType.
Questions:
1) can I create rule that contain more complex check than one rule row ?
2) How to ask for source table in T-SQL function. I mean for the table which
initiated running of my function. Im going to put this function onto
defaultValue of the column that will be type of MyIDType. The function will
suppose, that in this table will exist columns CreationDateTime and SCID.
I know, how to do it in C#. The workflow of this function should be (it is
an example, dont check any syntax):
FUNCTION GetNewMyID (@.scid char(4))
DECLARE @.datetimenow char(8)
DECLARE @.lastid char(4)
DECLARE @.partOfMyID char(12)
@.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
for(int i = 1; i < 10000; i++)
{
@.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
if(there is no row)
return @.partOfMyID;
}
I hope, the good programer will understand this weird construction. :)))
Thanks.
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I baddly need to create my own datatype, that will be able to generate
> itself as unique id.
> 1. my user defined data type is based on char(16).
> 2. structure of this type is
> YYYYMMDDSCIDCONT
> YYYY = year of record creation
> MM = month of record creation
> DD = day of record creation
> SCID = value of SCID column in the table
> CONT = counter (like identity seed) next unique value in the table
> I can to do it by my .NET application, or i can make unique keys with all
> values in the table, but I would like to create my own SQL type, that will
> be able to do what i described. Is it possible?
> Thanks.
>|||hi Mirek,
Sure.
I understand that function but what does 'scid' mean? Let me know.
Current location: Alicante (ES)
"Mirek Endys" wrote:
> Maybe I should better explain this or ask for some questions.
> My task Im going to do:
> 1) create my own User Defined Data Type based on char(16) named for exampl
e
> MyIDType
> 2) create rule that will check if the value is in right format.
> 3) create function, that will able to generate new id with parameters
> datetime, scid. This fuction will parse the datetime, scid and newly creat
ed
> counter into char(16) and into MyIDType.
> Questions:
> 1) can I create rule that contain more complex check than one rule row ?
> 2) How to ask for source table in T-SQL function. I mean for the table whi
ch
> initiated running of my function. Im going to put this function onto
> defaultValue of the column that will be type of MyIDType. The function wil
l
> suppose, that in this table will exist columns CreationDateTime and SCID.
> I know, how to do it in C#. The workflow of this function should be (it is
> an example, dont check any syntax):
> FUNCTION GetNewMyID (@.scid char(4))
> DECLARE @.datetimenow char(8)
> DECLARE @.lastid char(4)
> DECLARE @.partOfMyID char(12)
> @.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
>
> for(int i = 1; i < 10000; i++)
> {
> @.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
> SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
> if(there is no row)
> return @.partOfMyID;
> }
> I hope, the good programer will understand this weird construction. :)))
> Thanks.
>
> "Mirek Endys" <MirekE@.community.nospam> wrote in message
> news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Enric
the SCID is variable - value that is contained in inserted record... or
passed by function parameter.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:6DE5E6D0-9B7A-421C-A6BF-14E33D967B06@.microsoft.com...
> hi Mirek,
> Sure.
> I understand that function but what does 'scid' mean? Let me know.
> Current location: Alicante (ES)
>
> "Mirek Endys" wrote:
>|||Hi Mirek,
As for the SQL 2005 clr UDT, it is still quiet limited, e.g, if the data
type used in the UDT is not in the following basic types:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double,
SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble,
SqlMoney, SqlBoolean
we have to implenent our userdefined serialization. For type validation, we
can define the validation rules in a certain method and mark it with the
"ValidationMethodName" attribute.
And for the constructing a new UDT instance based on some parameters or
other info in the current database, this is hard to be done through the UDT
itself since UDT just provide a "parse" function which help use create UDT
instance from a string representation(also a "ToString" method help return
string representation from a UDT instance). For your scenario, maybe we
have to define an additional user defined funciton(CLR based) to the work.
#User-Defined Type Requirements
http://msdn2.microsoft.com/en-us/library/f33b06h1.aspx
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||You can create static method(s) in your UDT to create. Kinda like a static
constructor. Something like:
public static MyID Create(SqlDateTime dt, SqlInt id, SqlInt counter)
{
// ...
return new MyID(dt, id, counter);
}
Use the sql types that make sense for your needs.
Then you can create an instance something like:
declare @.id MyID
set @.id = MyID.Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
| Hello all,
|
| I baddly need to create my own datatype, that will be able to generate
| itself as unique id.
|
| 1. my user defined data type is based on char(14).
| 2. structure of this type is
| YYYYMMDDSCIDCONT
|
| YYYY = year of record creation
| MM = month of record creation
| DD = day of record creation
| SCID = value of SCID column in the table
| CONT = counter (like identity seed) next unique value in the table
|
| I can to do it by my .NET application, or i can make unique keys with all
| values in the table, but I would like to create my own SQL type, that will
| be able to do what i described. Is it possible?
|
| Thanks.
|
||||Thank for Williams inputs.
This is a good suggestion that directly use a static method on the type.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks guys,
i thought so, that this will be solution.
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
> Thank for Williams inputs.
> This is a good suggestion that directly use a static method on the type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Just a note after looking at it again. You need the double "::" to refer to
the static method on a type from tsql. Such as:
set @.id = MyID::Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:uYBv3FmTGHA.4792@.TK2MSFTNGP14.phx.gbl...
| Thanks guys,
|
| i thought so, that this will be solution.
|
|
| "Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
| news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
| > Thank for Williams inputs.
| >
| > This is a good suggestion that directly use a static method on the type.
| >
| > Regards,
| >
| > Steven Cheng
| > Microsoft Online Community Support
| >
| >
| > ========================================
==========
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may
| >
| > learn and benefit from your issue.
| >
| > ========================================
==========
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
|
|
I baddly need to create my own datatype, that will be able to generate
itself as unique id.
1. my user defined data type is based on char(14).
2. structure of this type is
YYYYMMDDSCIDCONT
YYYY = year of record creation
MM = month of record creation
DD = day of record creation
SCID = value of SCID column in the table
CONT = counter (like identity seed) next unique value in the table
I can to do it by my .NET application, or i can make unique keys with all
values in the table, but I would like to create my own SQL type, that will
be able to do what i described. Is it possible?
Thanks.Maybe I should better explain this or ask for some questions.
My task Im going to do:
1) create my own User Defined Data Type based on char(16) named for example
MyIDType
2) create rule that will check if the value is in right format.
3) create function, that will able to generate new id with parameters
datetime, scid. This fuction will parse the datetime, scid and newly created
counter into char(16) and into MyIDType.
Questions:
1) can I create rule that contain more complex check than one rule row ?
2) How to ask for source table in T-SQL function. I mean for the table which
initiated running of my function. Im going to put this function onto
defaultValue of the column that will be type of MyIDType. The function will
suppose, that in this table will exist columns CreationDateTime and SCID.
I know, how to do it in C#. The workflow of this function should be (it is
an example, dont check any syntax):
FUNCTION GetNewMyID (@.scid char(4))
DECLARE @.datetimenow char(8)
DECLARE @.lastid char(4)
DECLARE @.partOfMyID char(12)
@.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
for(int i = 1; i < 10000; i++)
{
@.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
if(there is no row)
return @.partOfMyID;
}
I hope, the good programer will understand this weird construction. :)))
Thanks.
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I baddly need to create my own datatype, that will be able to generate
> itself as unique id.
> 1. my user defined data type is based on char(16).
> 2. structure of this type is
> YYYYMMDDSCIDCONT
> YYYY = year of record creation
> MM = month of record creation
> DD = day of record creation
> SCID = value of SCID column in the table
> CONT = counter (like identity seed) next unique value in the table
> I can to do it by my .NET application, or i can make unique keys with all
> values in the table, but I would like to create my own SQL type, that will
> be able to do what i described. Is it possible?
> Thanks.
>|||hi Mirek,
Sure.
I understand that function but what does 'scid' mean? Let me know.
Current location: Alicante (ES)
"Mirek Endys" wrote:
> Maybe I should better explain this or ask for some questions.
> My task Im going to do:
> 1) create my own User Defined Data Type based on char(16) named for exampl
e
> MyIDType
> 2) create rule that will check if the value is in right format.
> 3) create function, that will able to generate new id with parameters
> datetime, scid. This fuction will parse the datetime, scid and newly creat
ed
> counter into char(16) and into MyIDType.
> Questions:
> 1) can I create rule that contain more complex check than one rule row ?
> 2) How to ask for source table in T-SQL function. I mean for the table whi
ch
> initiated running of my function. Im going to put this function onto
> defaultValue of the column that will be type of MyIDType. The function wil
l
> suppose, that in this table will exist columns CreationDateTime and SCID.
> I know, how to do it in C#. The workflow of this function should be (it is
> an example, dont check any syntax):
> FUNCTION GetNewMyID (@.scid char(4))
> DECLARE @.datetimenow char(8)
> DECLARE @.lastid char(4)
> DECLARE @.partOfMyID char(12)
> @.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
>
> for(int i = 1; i < 10000; i++)
> {
> @.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
> SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
> if(there is no row)
> return @.partOfMyID;
> }
> I hope, the good programer will understand this weird construction. :)))
> Thanks.
>
> "Mirek Endys" <MirekE@.community.nospam> wrote in message
> news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Enric
the SCID is variable - value that is contained in inserted record... or
passed by function parameter.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:6DE5E6D0-9B7A-421C-A6BF-14E33D967B06@.microsoft.com...
> hi Mirek,
> Sure.
> I understand that function but what does 'scid' mean? Let me know.
> Current location: Alicante (ES)
>
> "Mirek Endys" wrote:
>|||Hi Mirek,
As for the SQL 2005 clr UDT, it is still quiet limited, e.g, if the data
type used in the UDT is not in the following basic types:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double,
SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble,
SqlMoney, SqlBoolean
we have to implenent our userdefined serialization. For type validation, we
can define the validation rules in a certain method and mark it with the
"ValidationMethodName" attribute.
And for the constructing a new UDT instance based on some parameters or
other info in the current database, this is hard to be done through the UDT
itself since UDT just provide a "parse" function which help use create UDT
instance from a string representation(also a "ToString" method help return
string representation from a UDT instance). For your scenario, maybe we
have to define an additional user defined funciton(CLR based) to the work.
#User-Defined Type Requirements
http://msdn2.microsoft.com/en-us/library/f33b06h1.aspx
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||You can create static method(s) in your UDT to create. Kinda like a static
constructor. Something like:
public static MyID Create(SqlDateTime dt, SqlInt id, SqlInt counter)
{
// ...
return new MyID(dt, id, counter);
}
Use the sql types that make sense for your needs.
Then you can create an instance something like:
declare @.id MyID
set @.id = MyID.Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
| Hello all,
|
| I baddly need to create my own datatype, that will be able to generate
| itself as unique id.
|
| 1. my user defined data type is based on char(14).
| 2. structure of this type is
| YYYYMMDDSCIDCONT
|
| YYYY = year of record creation
| MM = month of record creation
| DD = day of record creation
| SCID = value of SCID column in the table
| CONT = counter (like identity seed) next unique value in the table
|
| I can to do it by my .NET application, or i can make unique keys with all
| values in the table, but I would like to create my own SQL type, that will
| be able to do what i described. Is it possible?
|
| Thanks.
|
||||Thank for Williams inputs.
This is a good suggestion that directly use a static method on the type.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks guys,
i thought so, that this will be solution.
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
> Thank for Williams inputs.
> This is a good suggestion that directly use a static method on the type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Just a note after looking at it again. You need the double "::" to refer to
the static method on a type from tsql. Such as:
set @.id = MyID::Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:uYBv3FmTGHA.4792@.TK2MSFTNGP14.phx.gbl...
| Thanks guys,
|
| i thought so, that this will be solution.
|
|
| "Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
| news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
| > Thank for Williams inputs.
| >
| > This is a good suggestion that directly use a static method on the type.
| >
| > Regards,
| >
| > Steven Cheng
| > Microsoft Online Community Support
| >
| >
| > ========================================
==========
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may
| >
| > learn and benefit from your issue.
| >
| > ========================================
==========
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
|
|
Tuesday, March 20, 2012
Custom Security w/ Standard Edition/
Hello...
I am trying to verify that the ability to use the security extension to use Forms based authentication is available (or not) with SQL 2005 Standard. I have read a few books and articles that state that only the Enterprise edition will allow us to use the security extensions to customize authentication and authorization. But a few recently have told me otherwise.
Does anyone know for sure? We use Standard edition...and would like to customize our security.
thanks
- will
Hello again,
Does anyone know the answer here?
Thanks for any help,
- will
|||SQL Server 2005 Reporting Services Standard edition allows custom security :-)
http://msdn2.microsoft.com/en-us/library/ms143761.aspx
Support for remote and nonrelational data sources
Yes
Yes
No
No
No
DHTML, Excel, PDF, and Image rendering extensions
Yes
Yes
Yes
No
Yes
MHTML, CSV, XML, and Null rendering extensions
Yes
Yes
No
No
No
E-mail and file share delivery extensions
Yes
Yes
No
No
No
Custom data processing, delivery, and rendering extensions
Yes
Yes
No
No
No
Custom authentication extensions
Yes
Yes
Yes
No
No
Custom security extension permission problem
I've created a Reporting Service security extension based on the sample. It works great on my development 2003 servers but when moved into integration environment I am getting the following error in Report Manager when clicking a report:
Could not load file or assembly 'Microsoft.ReportingServices.ProcessingObjectModel, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Access is denied.
Navigating folders and creating folders all seems to work in Report Manager. We only get this error when selecting a report. If I uninstall the custom security extension then everything works.
This seems like a difference in security settings between the two server but I'm not sure how to troubleshoot it.
Thanks
William
William
I had a similar problem and it was due to the fact that the user account of the SQL Server Reporting Services (MSSQLSERVER) service did not have access to the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files directory. It needs Modify access. Once I changed that, it worked fine.
Good luck
|||Thanks.
I had to add modify permissions on C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files to the Execution Account I specified in Reporting Services Configuration Manager. This is because I ran a report with a local datasource (with dynamic connectionstring) with setting "Credentials are not required".
Pierre
Custom security extension permission problem
I've created a Reporting Service security extension based on the sample. It works great on my development 2003 servers but when moved into integration environment I am getting the following error in Report Manager when clicking a report:
Could not load file or assembly 'Microsoft.ReportingServices.ProcessingObjectModel, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Access is denied.
Navigating folders and creating folders all seems to work in Report Manager. We only get this error when selecting a report. If I uninstall the custom security extension then everything works.
This seems like a difference in security settings between the two server but I'm not sure how to troubleshoot it.
Thanks
William
William
I had a similar problem and it was due to the fact that the user account of the SQL Server Reporting Services (MSSQLSERVER) service did not have access to the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files directory. It needs Modify access. Once I changed that, it worked fine.
Good luck
|||Thanks.
I had to add modify permissions on C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files to the Execution Account I specified in Reporting Services Configuration Manager. This is because I ran a report with a local datasource (with dynamic connectionstring) with setting "Credentials are not required".
Pierre
Custom security extension permission problem
I've created a Reporting Service security extension based on the sample. It works great on my development 2003 servers but when moved into integration environment I am getting the following error in Report Manager when clicking a report:
Could not load file or assembly 'Microsoft.ReportingServices.ProcessingObjectModel, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Access is denied.
Navigating folders and creating folders all seems to work in Report Manager. We only get this error when selecting a report. If I uninstall the custom security extension then everything works.
This seems like a difference in security settings between the two server but I'm not sure how to troubleshoot it.
Thanks
William
William
I had a similar problem and it was due to the fact that the user account of the SQL Server Reporting Services (MSSQLSERVER) service did not have access to the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files directory. It needs Modify access. Once I changed that, it worked fine.
Good luck
|||Thanks.
I had to add modify permissions on C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files to the Execution Account I specified in Reporting Services Configuration Manager. This is because I ran a report with a local datasource (with dynamic connectionstring) with setting "Credentials are not required".
Pierre
Monday, March 19, 2012
Custom Resolver in SQL Server 2005
I'm trying to implement .NET based custom resolver for SQL Server 2005. MS
example shows how to work with publisher / subscriber record data only. To
resolve a conflict I need more info about the business object represented by
record data. I need to run a query / procedure for this purpose. To make a
connection from within my resolver user / password are required. No such
thing is available in 'Microsoft.SqlServer.Replication.BusinessLogicSupp ort'
class.
Note: In SQL Server 2000 VB based COM custom resolver I have
'IReplRowChange' object as an INPUT param for 'IVBCustomResolver_Reconcile'
method which has 'GetSourceConnectionInfo' / 'GetDestinationConnectionInfo'
methods to get to GetLogin / GetPassword and than use ADODB to create a
connection string to run query or sp.
Appreciate any help on this subject.
Is there any workaround to use Custom Resolver COM created for SQL Server
2000 implementing IVBCustomResolver for SQL Server 2005?
|||I've found an example in
C:\Program Files\Microsoft SQL
Server\90\Samples\Replication\Merge\BusinessLogic\ CS
Thanks All
example shows how to work with publisher / subscriber record data only. To
resolve a conflict I need more info about the business object represented by
record data. I need to run a query / procedure for this purpose. To make a
connection from within my resolver user / password are required. No such
thing is available in 'Microsoft.SqlServer.Replication.BusinessLogicSupp ort'
class.
Note: In SQL Server 2000 VB based COM custom resolver I have
'IReplRowChange' object as an INPUT param for 'IVBCustomResolver_Reconcile'
method which has 'GetSourceConnectionInfo' / 'GetDestinationConnectionInfo'
methods to get to GetLogin / GetPassword and than use ADODB to create a
connection string to run query or sp.
Appreciate any help on this subject.
Is there any workaround to use Custom Resolver COM created for SQL Server
2000 implementing IVBCustomResolver for SQL Server 2005?
|||I've found an example in
C:\Program Files\Microsoft SQL
Server\90\Samples\Replication\Merge\BusinessLogic\ CS
Thanks All
Thursday, March 8, 2012
Custom Grouping
My reqmt is to group records based on items purchased. I hv 2 groups cat1 ,cat2 with different set of items in them. My third group cat3 will contain customers who have purchased any items from cat1 AND cat2.
My formula is something like
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'
Am not able to get the cat3(3rd Group) on my report. Any thots on wat I cud b missing?
Am using Crystal Reports XII'd guess that your condition checks are in the wrong order.
You'll never get to the final if statement 'cos one of the previous two will always be true if the 3rd is true.|||Sorry if i have confused u. My reqmt is to get a count of all customers who buy items. Cat1 will have a set of items. Cat2 will have another set of items. My third group will contain items frm both cat1 and cat2. I sud basically get a count of customers in each cat.
I have grouped my report on items using the formula
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'
My report sud luk something like this :
Count(customer)
Cat1 xxxx
Cat2 xxxxx
Cat3 xxx(sud be the count of customers who hv bought frm cat1 and cat2)|||Assuming that your cat1 items and cat2 item lists are mutually exclusive then the 3rd if statement can never be true.
If there is an overlap (which I suspect there isn't, otherwise an item would be both a cat1 and cat2 item) then my previous comment is still true.
i.e. you will never get a cat3 with that formula.
Your main problem, however, is that you want to catagorise a customer, and your formula categorises an item.
My formula is something like
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'
Am not able to get the cat3(3rd Group) on my report. Any thots on wat I cud b missing?
Am using Crystal Reports XII'd guess that your condition checks are in the wrong order.
You'll never get to the final if statement 'cos one of the previous two will always be true if the 3rd is true.|||Sorry if i have confused u. My reqmt is to get a count of all customers who buy items. Cat1 will have a set of items. Cat2 will have another set of items. My third group will contain items frm both cat1 and cat2. I sud basically get a count of customers in each cat.
I have grouped my report on items using the formula
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'
My report sud luk something like this :
Count(customer)
Cat1 xxxx
Cat2 xxxxx
Cat3 xxx(sud be the count of customers who hv bought frm cat1 and cat2)|||Assuming that your cat1 items and cat2 item lists are mutually exclusive then the 3rd if statement can never be true.
If there is an overlap (which I suspect there isn't, otherwise an item would be both a cat1 and cat2 item) then my previous comment is still true.
i.e. you will never get a cat3 with that formula.
Your main problem, however, is that you want to catagorise a customer, and your formula categorises an item.
Wednesday, March 7, 2012
Custom Data Mining Functions
I would like to write a custom mining function, which takes a string, queries the database, and returns an answer based upon those queries. So the basic function is then:
[MiningFunction("Performs Foo")]
public string Foo(string param)
{
// process parameters
// query database
// calculate answer from query results
// return query results
}
And is executed from the client using:
SELECT Foo("X Y Z") FROM FooModel
This arrangement is so that resource-intensive calculations are performed server-side.
My question is: what is the preferrable method for executing the database query from within the custom mining function?
Custom mining functions are not actually designed for this kind of operations. They are intended for predictive features that are related to the mining model and typically this kind of operations do not need external access (such as a database query). I assume that your function's calculation part will use some information from the mining model and apply it to the database query results.
I think you should use a stored procedure. Inside the stored procedure, you should use the server side object model (add a reference to Microsoft.AnalysisServices.AdomdServer). With the server side object model, you can perform the following operations:
- use AdomdCommand to execute calls such as CALL SystemOpenQuery(DataSource, Query), which is the recommended way of querying a relational database from analysis services
- also use AdomdCommand to execute calls such as SELECT .... FROM YourModel PREDICTION JOIN OPENQUERY(DataSource, Query)
This would allow you to get the information from the data base together with scoring for each, scoring computed as a prediction from your model ). Your code could use the results and perform aggregations or more complex computations on the result.
If, in the code of your stored procedure, you need to get extra information from your model, you can traverse the content of the mining model using the object model.
The article at http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/4264.aspx contains such a stored procedure, which requires both data and model content information, so I think it may be a good example. The data is coming directly from the model, with a drillthrough query. You can replace that query with a CALL SystemOpenQuery or prediction against an OPENQUERY statement.
Hope this helps
|||Yes, this is helpful. I'm looking at the material you referenced to see if it completely answers my question. Unfortunately, I don't seem able to progress pass the logon screen at sqlserverdatamining at the moment, so I can't get the .cs example...|||Do you have an account on sqlserverdatamining.com? Do you have problems logging in with your account? Or creating a new account
Sunday, February 19, 2012
Custom authorization on a particular report
Hi,
I have created a report that displays order summaries based on a parameter
CustomerName. I have many customers and want to give them all access to this
report. I've installed the Forms Authentication sample and am able to
authenticate, so each customer has a login.
The problem is that I want each customer to be able to view only his own
order summary (i.e. the CustomerName parameter to the report must be set to
the customer's login id and he cannot change it). Is there a way to pass the
username given in Forms Authentication to the report?
Passing it in the URL is no good because users can modify the URL. The
authorization pieces in the Forms Authentication sample seem only to grant or
deny access on to a particular report, but not on parameters to the report.
Any tips would be greatly appreciated.
Thanks,
DonYou can use the global user!userid, don't pass this as a parameter.
Bruce L-C
"Don" <Don@.discussions.microsoft.com> wrote in message
news:37361218-AD5E-4B95-BF84-3C863FFD1738@.microsoft.com...
> Hi,
> I have created a report that displays order summaries based on a parameter
> CustomerName. I have many customers and want to give them all access to
this
> report. I've installed the Forms Authentication sample and am able to
> authenticate, so each customer has a login.
> The problem is that I want each customer to be able to view only his own
> order summary (i.e. the CustomerName parameter to the report must be set
to
> the customer's login id and he cannot change it). Is there a way to pass
the
> username given in Forms Authentication to the report?
> Passing it in the URL is no good because users can modify the URL. The
> authorization pieces in the Forms Authentication sample seem only to grant
or
> deny access on to a particular report, but not on parameters to the
report.
> Any tips would be greatly appreciated.
> Thanks,
> Don
I have created a report that displays order summaries based on a parameter
CustomerName. I have many customers and want to give them all access to this
report. I've installed the Forms Authentication sample and am able to
authenticate, so each customer has a login.
The problem is that I want each customer to be able to view only his own
order summary (i.e. the CustomerName parameter to the report must be set to
the customer's login id and he cannot change it). Is there a way to pass the
username given in Forms Authentication to the report?
Passing it in the URL is no good because users can modify the URL. The
authorization pieces in the Forms Authentication sample seem only to grant or
deny access on to a particular report, but not on parameters to the report.
Any tips would be greatly appreciated.
Thanks,
DonYou can use the global user!userid, don't pass this as a parameter.
Bruce L-C
"Don" <Don@.discussions.microsoft.com> wrote in message
news:37361218-AD5E-4B95-BF84-3C863FFD1738@.microsoft.com...
> Hi,
> I have created a report that displays order summaries based on a parameter
> CustomerName. I have many customers and want to give them all access to
this
> report. I've installed the Forms Authentication sample and am able to
> authenticate, so each customer has a login.
> The problem is that I want each customer to be able to view only his own
> order summary (i.e. the CustomerName parameter to the report must be set
to
> the customer's login id and he cannot change it). Is there a way to pass
the
> username given in Forms Authentication to the report?
> Passing it in the URL is no good because users can modify the URL. The
> authorization pieces in the Forms Authentication sample seem only to grant
or
> deny access on to a particular report, but not on parameters to the
report.
> Any tips would be greatly appreciated.
> Thanks,
> Don
Friday, February 17, 2012
Custom Assembly
We have written a custom assembly which makes a call to database and
executes one stored procedure and based on stored procedure returned value a
method called IsAuthorized() returns true or false. In the development
environment assembly and report behaves properly. But when we deploy it to
production it gives us error. Any ideas what we are doing wrong ? We are
guessing its some kind of permission issues. Any help greatly appreciated.There is a very useful article on permissions with custom assemblies here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_16g2.asp
"new.microsoft.com" <gsinthoju@.mail.educo-int.com> wrote in message
news:O0ka2itaFHA.3620@.TK2MSFTNGP09.phx.gbl...
> We have written a custom assembly which makes a call to database and
> executes one stored procedure and based on stored procedure returned value
> a
> method called IsAuthorized() returns true or false. In the development
> environment assembly and report behaves properly. But when we deploy it to
> production it gives us error. Any ideas what we are doing wrong ? We are
> guessing its some kind of permission issues. Any help greatly appreciated.
>|||two books specifically talk about this. microsoft reporting services in
action and hitchhikers guide to sql server 2000.
they solved my problems.
"new.microsoft.com" <gsinthoju@.mail.educo-int.com> wrote in message
news:O0ka2itaFHA.3620@.TK2MSFTNGP09.phx.gbl...
> We have written a custom assembly which makes a call to database and
> executes one stored procedure and based on stored procedure returned value
> a
> method called IsAuthorized() returns true or false. In the development
> environment assembly and report behaves properly. But when we deploy it to
> production it gives us error. Any ideas what we are doing wrong ? We are
> guessing its some kind of permission issues. Any help greatly appreciated.
>
executes one stored procedure and based on stored procedure returned value a
method called IsAuthorized() returns true or false. In the development
environment assembly and report behaves properly. But when we deploy it to
production it gives us error. Any ideas what we are doing wrong ? We are
guessing its some kind of permission issues. Any help greatly appreciated.There is a very useful article on permissions with custom assemblies here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_16g2.asp
"new.microsoft.com" <gsinthoju@.mail.educo-int.com> wrote in message
news:O0ka2itaFHA.3620@.TK2MSFTNGP09.phx.gbl...
> We have written a custom assembly which makes a call to database and
> executes one stored procedure and based on stored procedure returned value
> a
> method called IsAuthorized() returns true or false. In the development
> environment assembly and report behaves properly. But when we deploy it to
> production it gives us error. Any ideas what we are doing wrong ? We are
> guessing its some kind of permission issues. Any help greatly appreciated.
>|||two books specifically talk about this. microsoft reporting services in
action and hitchhikers guide to sql server 2000.
they solved my problems.
"new.microsoft.com" <gsinthoju@.mail.educo-int.com> wrote in message
news:O0ka2itaFHA.3620@.TK2MSFTNGP09.phx.gbl...
> We have written a custom assembly which makes a call to database and
> executes one stored procedure and based on stored procedure returned value
> a
> method called IsAuthorized() returns true or false. In the development
> environment assembly and report behaves properly. But when we deploy it to
> production it gives us error. Any ideas what we are doing wrong ? We are
> guessing its some kind of permission issues. Any help greatly appreciated.
>
Tuesday, February 14, 2012
Custom Aggrigate Functions
I was trying to calculate the Meadian of the group of values (grouped based on some columns). I could not find any standard aggrigate funtion for doing so.
It would be great if I can be able to create my own custom aggrigate functions and use the same in the RunningValues funtion.
It would be even interesting if the user can share code written across reports and report projects, with out the need to copy and paste the same custom function in all the reports.
Rich and very interesting feature would be enabling the developer to use the traditional VS2005 UI to develop his code instead of the mundane text box.
Take a look at the following blog article:
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx
Another example (a moving average aggregate implementation for a chart - can be applied similarly for a table or matrix) is shown in one of the samples of the following whitepaper - search for the section about "Moving Average Calculations": http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp
-- Robert
|||
Robert, Thanks for the reply. I have done some thing similar to the one in the first link.
I am facing a problem with this kind of logic. I get the #Error in the first group footer, in second group footer I get the value, but it is the median value of the first group. It continues like this and last footer has the median value of the previous group footer (missing its own value).
To confrim how the values string is built I did reset the visibility of the hidden column and saw the values building correctly. It accumalates each value row by row and last row in the group (before the footer) has all the values to calculate median. And this holds good for the First Group also.
Am I doing some thing wrong?
The code is below:
Public AucBaseAmtString As String
'Append all the amounts to a string seperated by comma. ex: ,12,322,23,232
'Call this funtion in the hidden column of the table for each row
Public Function AccumulateAucBase(Amt As String) As String
AucBaseAmtString = AucBaseAmtString & "," & Amt
Return AucBaseAmtString
End Function
'Call the Median function in the Group Footer.
Public Function Median() As String
Dim Count As Integer
Dim AucBaseAmts() As String
'Truncate the first comma
If AucBaseAmtString <> "" Then
AucBaseAmtString = Mid(AucBaseAmtString, 2)
End If
'Get the amounts to array
AucBaseAmts = AucBaseAmtString.Split(",")
'Reset the string for the next group
AucBaseAmtString = ""
'Calculate median and return
Count = AucBaseAmts.GetLength(0)
If Count = 0 Then Return "0"
Array.Sort(AucBaseAmts)
If Count Mod 2 = 0 Then
Return CStr((CInt(AucBaseAmts((Count / 2) - 1)) + CInt(AucBaseAmts(Count / 2))) / 2)
Else
Return CStr(AucBaseAmts(Count / 2))
End If
End Function
Custom aggregation value for all hierarchies'' levels above a specific level name?
I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.
The behavior that I'd want is:
If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.
I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.
Is this definitely possible and does it have minimal impact on query times and performance?
There might be a better design - if so, please comment and set me straight. Thanks!I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)
But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.
How would one go about overriding the cube behavior?
If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.
|||Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?
I've included syntax of 'currenthierarchy', but i don't know if that's really possible.
SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])
this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)
END SCOPE
Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?
Custom aggregation value for all hierarchies'' levels above a specific level name?
I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.
The behavior that I'd want is:
If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.
I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.
Is this definitely possible and does it have minimal impact on query times and performance?
There might be a better design - if so, please comment and set me straight. Thanks!I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)
But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.
How would one go about overriding the cube behavior?
If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.
|||Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?
I've included syntax of 'currenthierarchy', but i don't know if that's really possible.
SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])
this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)
END SCOPE
Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?
Custom aggregation value for all hierarchies'' levels above a specific level name?
I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.
The behavior that I'd want is:
If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.
I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.
Is this definitely possible and does it have minimal impact on query times and performance?
There might be a better design - if so, please comment and set me straight. Thanks!I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)
But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.
How would one go about overriding the cube behavior?
If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.
|||Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?
I've included syntax of 'currenthierarchy', but i don't know if that's really possible.
SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])
this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)
END SCOPE
Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?
Subscribe to:
Posts (Atom)