Friday, March 23, 2012
How to allow use to exec xp_sendmail
The last part of the stored proc sends an email if certain conditions are met.
It appears that users do not have permission to execute xp_sendmail. I guess this is because it is executed on the master database. Is there a way I can give them permission to this stored proc?
The users are getting this message:
EXECUTE permission denied on bject 'xp_sendmail', 'database master', owner 'dbo'.(#229)You can grant a user to execute xp_sendmail by going to properties of xp_sendmail in master database and grant the user permission to execute.
Monday, March 12, 2012
How to add parameters to a MDX query in RS 2005?
2005 in RS 2005.
How do I add a new parameter to an MDX query, after having built the query
using the wysiwig query builder? I could probably start all over again to
use the query builder, but I can't figure out how to create semi-dynamic
queries like I did with RS 2000 / AS 2000.
And is there a way to choose if a parameter should be multivalue when adding
it as a parameter?
Any good books focusing on Reporting Services and Analysis Services 2005?
Most of them focus on SQL based reports.
Kaisa M. Lindahl LervikHope you have tried puting "@." to the parameters this is basics and go to
report parameter from menu and can define multi value.
A book from "Brian Larson" of SSRS and SS BI is good book to start with and
your online help and samples are best resources for learning.
Amarnath
"Kaisa M. Lindahl Lervik" wrote:
> I'm currently struggling with parameters when doing reports based on SSAS
> 2005 in RS 2005.
> How do I add a new parameter to an MDX query, after having built the query
> using the wysiwig query builder? I could probably start all over again to
> use the query builder, but I can't figure out how to create semi-dynamic
> queries like I did with RS 2000 / AS 2000.
> And is there a way to choose if a parameter should be multivalue when adding
> it as a parameter?
> Any good books focusing on Reporting Services and Analysis Services 2005?
> Most of them focus on SQL based reports.
> Kaisa M. Lindahl Lervik
>
>|||I had problems with the whole strtomember syntax. Just making a parameter
didn't put this new parameter into the query, probably because I'd gone from
the editor to hand editing the query, and I couldn't figure out the syntax
of a new parameter.
Anyway, I found out how to make dynamic mdx queries the old way*. I'll check
out the book by Brian Larson, there's probably a more modern way of making
mdx based reports than the old school way I'm used to. ;)
*= Make a new data source with OLE DB instead of Analysis Services, and then
connect it to Analysis Services 9.0. Gives you back the good old text only
editor, which I prefer right now.
Kaisa M: Lindahl Lervik
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:FFF7458A-F34C-4CC7-8CA0-55C59B8C0106@.microsoft.com...
> Hope you have tried puting "@." to the parameters this is basics and go to
> report parameter from menu and can define multi value.
> A book from "Brian Larson" of SSRS and SS BI is good book to start with
> and
> your online help and samples are best resources for learning.
> Amarnath
> "Kaisa M. Lindahl Lervik" wrote:
>> I'm currently struggling with parameters when doing reports based on SSAS
>> 2005 in RS 2005.
>> How do I add a new parameter to an MDX query, after having built the
>> query
>> using the wysiwig query builder? I could probably start all over again to
>> use the query builder, but I can't figure out how to create semi-dynamic
>> queries like I did with RS 2000 / AS 2000.
>> And is there a way to choose if a parameter should be multivalue when
>> adding
>> it as a parameter?
>> Any good books focusing on Reporting Services and Analysis Services 2005?
>> Most of them focus on SQL based reports.
>> Kaisa M. Lindahl Lervik
>>
Friday, March 9, 2012
How to add filter based on query?
Hi guys,
I have the following problem:
I have several tables in SQL 2005 ( table GeographicLocations and other related to it ). I created a cube in SSAS in which GeographicLocations is dimension.
I was able to add parameters but I need something more complicated: Having columns longitude and latitude in GeographicLocations I need to be able to return all locations within X (parameter) miles from passed GeographicLocation (parameter - GeographicLocationID )
I found SQL function that calculates distance from passed longitude and latitude of two points and T-SQL would like this:
declare @.GeoId int;
set @.GeoID = 200765;
select * from GeographicLocations as t1
where dbo.geo_distance(t1.longitude, t1.latitude,
(select longitude from GeographicLocations where id=@.GeoID),
(select latitude from GeographicLocations where id=@.GeoID)) < 2000
but how I can make same in SSAS and Reports?
Thanks in advance
I suggest making Longitude and Latitude related attributes of GeoLocation, and then implement SSAS sproc which computes difference between two points (similar to geo_distance).
|||Hi Mosha,
Thank you very much for helping me.
I would like to ask you to be more specific as I am newbee in SSAS. Could you point me at some samples and resources that use such approach?
It is kind of blury to me how to create related attributes in such manner (although I read this article ), how to create SSAS stored procedure and how to call it from the report project
Best regards,
Galin
|||Hi,
Any advise ? I'm dealing with the same problem and I'm also a newbie in SSAS.
I was thinking to make an user defined function in an assembly to compute the distance but I can't figure out if this will be viable.
Daniel.
|||I made .NET function to calculate distance - same as T-SQL onealso I made Longitude and Latitude measures and I could pass them in MDX query
Hope this helps|||
Hi,
Can you provide a sample of your MDX query ?
I was thinking that Lat and Long to be dimensions not measures.
Thnx.
daniel
|||I've changed the fact table in order to have Latitude and Longitude as Measures; so now I have:
Measures(AggregateFunction)
PriceCount (Count),
Latitude (None),
Longitude (None)
Dimension
[Tbl DW Dim Property Type]
but from tests I've found that performing filter operations on Latitude/Longitude measures it is returning wrong results:
Code Snippet
SELECT
NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,
NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, [Measures].[Latitude]>44.42659335504373) ON ROWS
FROM [DW DEV]
so I used an .NET sp to capture the data:
Code Snippet
SELECT
NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,
NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, MDXClassLibrary.DoSomething([Measures].[Latitude])>44.42659335504373) ON ROWS
FROM [DW DEV]
Code Snippet
public static double DoSomething(double x)
{
return x;
}
And during debugging I’ve found that the sp’s parameter is receiving wrong values not the ones that are in [Measures].[Latitude].
I know that I'm missing something but I can't figure.
Any help would be appreciated.
|||I've managed to run a query like this
Code Snippet
SELECT NON EMPTY { [Measures].[Prices Avg], [Measures].[Prices Max], [Measures].[Prices Min] } ON COLUMNS,
NON EMPTY { ([Tbl DW Dim Time].[Calendar Year].[Calendar Year].ALLMEMBERS *
[Tbl DW Dim Time].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
( SELECT ( { FILTER([Tbl DW Dim Gis].[Coord].MEMBERS,
mdxclasslibrary.dist([Tbl DW Dim Gis].[Coord].CurrentMember.Name,
'44.42366640719915000000000','26.07366800308228000000000',1 )>0)}) ON COLUMNS
FROM (
SELECT ({[Tbl DW Dim Property Type].[Name].&[Apartment] } ) ON COLUMNS
FROM [DEV]))
but i noticed a significant performance degradation.
Any suggestions ?
How to add filter based on query?
Hi guys,
I have the following problem:
I have several tables in SQL 2005 ( table GeographicLocations and other related to it ). I created a cube in SSAS in which GeographicLocations is dimension.
I was able to add parameters but I need something more complicated: Having columns longitude and latitude in GeographicLocations I need to be able to return all locations within X (parameter) miles from passed GeographicLocation (parameter - GeographicLocationID )
I found SQL function that calculates distance from passed longitude and latitude of two points and T-SQL would like this:
declare @.GeoId int;
set @.GeoID = 200765;
select * from GeographicLocations as t1
where dbo.geo_distance(t1.longitude, t1.latitude,
(select longitude from GeographicLocations where id=@.GeoID),
(select latitude from GeographicLocations where id=@.GeoID)) < 2000
but how I can make same in SSAS and Reports?
Thanks in advance
I suggest making Longitude and Latitude related attributes of GeoLocation, and then implement SSAS sproc which computes difference between two points (similar to geo_distance).
|||Hi Mosha,
Thank you very much for helping me.
I would like to ask you to be more specific as I am newbee in SSAS. Could you point me at some samples and resources that use such approach?
It is kind of blury to me how to create related attributes in such manner (although I read this article ), how to create SSAS stored procedure and how to call it from the report project
Best regards,
Galin
|||Hi,
Any advise ? I'm dealing with the same problem and I'm also a newbie in SSAS.
I was thinking to make an user defined function in an assembly to compute the distance but I can't figure out if this will be viable.
Daniel.
|||I made .NET function to calculate distance - same as T-SQL onealso I made Longitude and Latitude measures and I could pass them in MDX query
Hope this helps|||
Hi,
Can you provide a sample of your MDX query ?
I was thinking that Lat and Long to be dimensions not measures.
Thnx.
daniel
|||I've changed the fact table in order to have Latitude and Longitude as Measures; so now I have:
Measures(AggregateFunction)
PriceCount (Count),
Latitude (None),
Longitude (None)
Dimension
[Tbl DW Dim Property Type]
but from tests I've found that performing filter operations on Latitude/Longitude measures it is returning wrong results:
Code Snippet
SELECT
NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,
NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, [Measures].[Latitude]>44.42659335504373) ON ROWS
FROM [DW DEV]
so I used an .NET sp to capture the data:
Code Snippet
SELECT
NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,
NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, MDXClassLibrary.DoSomething([Measures].[Latitude])>44.42659335504373) ON ROWS
FROM [DW DEV]
Code Snippet
public static double DoSomething(double x)
{
return x;
}
And during debugging I’ve found that the sp’s parameter is receiving wrong values not the ones that are in [Measures].[Latitude].
I know that I'm missing something but I can't figure.
Any help would be appreciated.
|||I've managed to run a query like this
Code Snippet
SELECT NON EMPTY { [Measures].[Prices Avg], [Measures].[Prices Max], [Measures].[Prices Min] } ON COLUMNS,
NON EMPTY { ([Tbl DW Dim Time].[Calendar Year].[Calendar Year].ALLMEMBERS *
[Tbl DW Dim Time].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
( SELECT ( { FILTER([Tbl DW Dim Gis].[Coord].MEMBERS,
mdxclasslibrary.dist([Tbl DW Dim Gis].[Coord].CurrentMember.Name,
'44.42366640719915000000000','26.07366800308228000000000',1 )>0)}) ON COLUMNS
FROM (
SELECT ({[Tbl DW Dim Property Type].[Name].&[Apartment] } ) ON COLUMNS
FROM [DEV]))
but i noticed a significant performance degradation.
Any suggestions ?
How to add filter based on query?
Hi guys,
I have the following problem:
I have several tables in SQL 2005 ( table GeographicLocations and other related to it ). I created a cube in SSAS in which GeographicLocations is dimension.
I was able to add parameters but I need something more complicated: Having columns longitude and latitude in GeographicLocations I need to be able to return all locations within X (parameter) miles from passed GeographicLocation (parameter - GeographicLocationID )
I found SQL function that calculates distance from passed longitude and latitude of two points and T-SQL would like this:
declare @.GeoId int;
set @.GeoID = 200765;
select * from GeographicLocations as t1
where dbo.geo_distance(t1.longitude, t1.latitude,
(select longitude from GeographicLocations where id=@.GeoID),
(select latitude from GeographicLocations where id=@.GeoID)) < 2000
but how I can make same in SSAS and Reports?
Thanks in advance
I suggest making Longitude and Latitude related attributes of GeoLocation, and then implement SSAS sproc which computes difference between two points (similar to geo_distance).
|||Hi Mosha,
Thank you very much for helping me.
I would like to ask you to be more specific as I am newbee in SSAS. Could you point me at some samples and resources that use such approach?
It is kind of blury to me how to create related attributes in such manner (although I read this article ), how to create SSAS stored procedure and how to call it from the report project
Best regards,
Galin
|||Hi,
Any advise ? I'm dealing with the same problem and I'm also a newbie in SSAS.
I was thinking to make an user defined function in an assembly to compute the distance but I can't figure out if this will be viable.
Daniel.
|||I made .NET function to calculate distance - same as T-SQL onealso I made Longitude and Latitude measures and I could pass them in MDX query
Hope this helps|||
Hi,
Can you provide a sample of your MDX query ?
I was thinking that Lat and Long to be dimensions not measures.
Thnx.
daniel
|||I've changed the fact table in order to have Latitude and Longitude as Measures; so now I have:
Measures(AggregateFunction)
PriceCount (Count),
Latitude (None),
Longitude (None)
Dimension
[Tbl DW Dim Property Type]
but from tests I've found that performing filter operations on Latitude/Longitude measures it is returning wrong results:
Code Snippet
SELECT
NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,
NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, [Measures].[Latitude]>44.42659335504373) ON ROWS
FROM [DW DEV]
so I used an .NET sp to capture the data:
Code Snippet
SELECT
NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,
NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, MDXClassLibrary.DoSomething([Measures].[Latitude])>44.42659335504373) ON ROWS
FROM [DW DEV]
Code Snippet
public static double DoSomething(double x)
{
return x;
}
And during debugging I’ve found that the sp’s parameter is receiving wrong values not the ones that are in [Measures].[Latitude].
I know that I'm missing something but I can't figure.
Any help would be appreciated.
|||I've managed to run a query like this
Code Snippet
SELECT NON EMPTY { [Measures].[Prices Avg], [Measures].[Prices Max], [Measures].[Prices Min] } ON COLUMNS,
NON EMPTY { ([Tbl DW Dim Time].[Calendar Year].[Calendar Year].ALLMEMBERS *
[Tbl DW Dim Time].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
( SELECT ( { FILTER([Tbl DW Dim Gis].[Coord].MEMBERS,
mdxclasslibrary.dist([Tbl DW Dim Gis].[Coord].CurrentMember.Name,
'44.42366640719915000000000','26.07366800308228000000000',1 )>0)}) ON COLUMNS
FROM (
SELECT ({[Tbl DW Dim Property Type].[Name].&[Apartment] } ) ON COLUMNS
FROM [DEV]))
but i noticed a significant performance degradation.
Any suggestions ?
How to add columns dynamically based on number of groups used.
Hi,
I am pretty new to the Microsoft Reporting services. I wanted to know if
this is possible to design in the reporting server.
I have a requirement to take the number of groups in my report as input. So
if there are two groups I will have two columns and if I have three groups I
will have three colums. Is there any way I can dynamically set the number of
columns based on the input.
Currently I am having to design different report for different number of
groups. This causes more maintenance in case of frequent report design
changes.
Thanks in advance,The matrix control add columns dynamically.
"Ragz" wrote:
> How to add columns dynamically based on number of groups used.
>
> Hi,
> I am pretty new to the Microsoft Reporting services. I wanted to know if
> this is possible to design in the reporting server.
> I have a requirement to take the number of groups in my report as input. So
> if there are two groups I will have two columns and if I have three groups I
> will have three colums. Is there any way I can dynamically set the number of
> columns based on the input.
> Currently I am having to design different report for different number of
> groups. This causes more maintenance in case of frequent report design
> changes.
>
> Thanks in advance,
>
>
How to add columns dynamically based on number of groups used.
I am pretty new to the Microsoft Reporting services. I wanted to know if this is possible to design in the reporting server.
I have a requirement to take the number of groups in my report as input. So if there are two groups I will have two columns and if I have three groups I will have three colums. Is there any way I can dynamically set the number of columns based on the input.
Currently I am having to design different report for different number of groups. This causes more maintenance in case of frequent report design changes.
Thanks in advance,
Ragz.To Elaborate further on this question.
If I have a table like
Employee(EmployeeName varchar(100),EmployeeManagerName varchar(100),team varchar(100),experience int)
I take the following inputs:
The number of groups and what to group on.
For example one input might be:
No of groups:1
group-on:EmployeeName.
The resulting report will have
Employee-Name experience
xxxx yyyy
But If I have Number of Groups as 2 and
group-on as
EmployeName and EmployeemanagerName
then i will have three columns Employee-name Employee-manager-name experience
The questions is how can I generate these columns dynamically ?|||Yes, you can do this. The way to do this is:
1) Create a report parameter that specifies the number of groups
2) to add a calculated field to your data set (right click on the Fields window and choose add).
2.1) the expression for the field would select which of the three groups you'd have (e.g. 1, 2, or 3... or what ever you need)
3) Create a matrix report
3.1) group the columns on your constructed parameter
That should probably work.
-Lukasz
Friday, February 24, 2012
How to Add a column by getting value from a lookup table
I have few columns at some level, and now I need to add a column from another database table based on the values from one of the existing columns.
It seems that Lookup Transformation doesnt support parameterized query.
Any clue ?
Thanks
Fahad
Are you sure you need a aprameterized query?
in the the lookup trasnform you can write a query; the join the columns of the lookup table/query with the columns available in the data pipeline.
Keep in mind, that you could have an aditional source component and then combine them using a Merge Join transform.
|||I agree with Rafael... By joining columns to the lookup table/query, you're effectively using "parameters". Unless you need your lookup query to be dynamic for each record coming down through the data flow.|||Yes, I know, but still I need to pass some values to the stored procedure to get a huge list, and then I will select from the list.Am I using the wrong Transform Component ?|||Maybe. Have you looked at using the OLE DB Command transform?|||Yes, and it seems it doesnt return the cardinality return by query to its output pipeline.|||
Fahad,
I still don't see how a the lookup trasnform or the 2 SOurce components plus the Merge join cannot help you. Pherhaps if you provide an example you may be more likely to receive more suggestions.
How to Add a Calculated Column in Report Builder using a Model Based on a SSAS Cube
I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.
Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?
Thanks,
Some functions are not supported for calculated fields when running against an SSAS cube, in particular the aggregate functions like Sum, Count, Average, etc. The error message should provide some details on this.
Hope that helps!
|||The support for calculated fields in an SSAS model is better than Bob lets on. Most functions work. The trick is that you need to install Excel 2003 on the Analysis Services server. (Not sure if other versions like Excel 2007 work, but I know Excel 2003 does the trick.)
Report Builder builds MDX that use Excel functions, and for those functions to succeed, Excel has to be installed on the server.
If you agree with me that this is a terrible architecture, vote for this issue:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124864
|||Are there any online documentation about this? Our IT policy is not to install office or office application on SQL Server boxes.|||Common policy. Vote for that issue at connect.microsoft.com and maybe MS will change this in Katmai.
As for documentation:
http://msdn2.microsoft.com/en-us/library/ms145486.aspx
And documentation of the excel functions that are supported. (Don't know if there's a 2005 version of this page, but they're the same):
http://msdn2.microsoft.com/en-us/library/aa178231(SQL.80).aspx
I suppose one workaround is to code the Excel functions yourself and register them as specified here:
http://geekswithblogs.net/darrengosbell/archive/2006/12/13/100998.aspx
If you have interest in going that route, let me know because there are some gotchas.
How to Add a Calculated Column in Report Builder using a Model Based on a SSAS Cube
I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.
Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?
Thanks,
Some functions are not supported for calculated fields when running against an SSAS cube, in particular the aggregate functions like Sum, Count, Average, etc. The error message should provide some details on this.
Hope that helps!
|||The support for calculated fields in an SSAS model is better than Bob lets on. Most functions work. The trick is that you need to install Excel 2003 on the Analysis Services server. (Not sure if other versions like Excel 2007 work, but I know Excel 2003 does the trick.)
Report Builder builds MDX that use Excel functions, and for those functions to succeed, Excel has to be installed on the server.
If you agree with me that this is a terrible architecture, vote for this issue:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124864
|||Are there any online documentation about this? Our IT policy is not to install office or office application on SQL Server boxes.|||Common policy. Vote for that issue at connect.microsoft.com and maybe MS will change this in Katmai.
As for documentation:
http://msdn2.microsoft.com/en-us/library/ms145486.aspx
And documentation of the excel functions that are supported. (Don't know if there's a 2005 version of this page, but they're the same):
http://msdn2.microsoft.com/en-us/library/aa178231(SQL.80).aspx
I suppose one workaround is to code the Excel functions yourself and register them as specified here:
http://geekswithblogs.net/darrengosbell/archive/2006/12/13/100998.aspx
If you have interest in going that route, let me know because there are some gotchas.
Sunday, February 19, 2012
how to access sum(Some_Column) in SQL
total value to the original one. (So I could pass this new dataset into a bar
chart to plot)
I know the sum(Some_column) is in the report domain, since we can access
this value in designer. But my question is how can I pick it out using SQL
statement to build another dataset. Or if there is better way to construct
the new dataset.
Micosoft gurus, please help. I am doing a consulting project at client side
for MS. Do not want to blow this one :)
--
SincerelyHave you tried using the ReportItems collection to reference the sum
field,and use it as a parameter into the other query?
ie
ReportItems!Textbox1.Value
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:7D89CBF3-D016-459F-B3F3-B1FC58DE3B3A@.microsoft.com...
> I am trying to build a new dataset based on existing set, by appending a
> total value to the original one. (So I could pass this new dataset into a
bar
> chart to plot)
> I know the sum(Some_column) is in the report domain, since we can access
> this value in designer. But my question is how can I pick it out using SQL
> statement to build another dataset. Or if there is better way to construct
> the new dataset.
> Micosoft gurus, please help. I am doing a consulting project at client
side
> for MS. Do not want to blow this one :)
> --
> Sincerely