Friday, March 30, 2012
Report Designer question
I have the following challenge:
Let's imagine you have a report containing a table/list of salespeople from
one table, their sales per year from another table, and thirdly for instance
the salesperson's zip code.
If a given salesperson have sales in 2003, 2004, 2005 and 2006, he will have
3 rows returned in the list, and the same zip code listed 4 times.
What you do not want for the report is that the total for the zip coded
column is summed up, so it shows 4 times the zip code. You just want the
single zip code returned on the summary line.
Is there an easy way to achieve this?
Kind Regards,
Torkild HagenYou can use First(Fields!fieldname.value) instead of Sum.
daw
"thagen" wrote:
> Hi gurus.
> I have the following challenge:
> Let's imagine you have a report containing a table/list of salespeople from
> one table, their sales per year from another table, and thirdly for instance
> the salesperson's zip code.
> If a given salesperson have sales in 2003, 2004, 2005 and 2006, he will have
> 3 rows returned in the list, and the same zip code listed 4 times.
> What you do not want for the report is that the total for the zip coded
> column is summed up, so it shows 4 times the zip code. You just want the
> single zip code returned on the summary line.
> Is there an easy way to achieve this?
> Kind Regards,
> Torkild Hagen
Wednesday, March 28, 2012
Report design scenario
Hey guys,
I want to create a report called "Report X", which has four reports under it. Assume the following scenario.
1. Report X
1.1. Bar chart report
1.2. Pie chart report
1.3. Matrix report
1.4. Pie chart report
In addition to this, there are about four parameters that should be available for all reports. So my question is: what approach should I follow to implement/design a report for the above requirement.
Thank you for your cooperation in advance.
Sincerely,
Amde
The question is discussed/answered in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=374405&SiteID=1
Report Design & Layout
Hi Everybody, i have been having problems in design & layout of the following report .
i need the report layout as follows a
-
HP- FIELDS IN HEADER OF PAGE -REPEATED IN EVERY PAGE.
HR- FIELDS IN HEADER OF REPORT-ONLY ONCE ON 1st PAGE OF REPORT.
i have created a table report in the body of the report as follows.
HP - In Page Header, i have placed some text and page no
HR - In Table Header, i have placed some text & fields.
HP - In Group Header 1, i have some text & fields. & RepeatOnNewPage = true
HR - IN GROUP HEADER 1, text & fields, which i want to repeat on only 1st page.
(this is were i am having problems to set the group header for the table to
to repeat only on 1st page)
HP - In Group Header 1, some text & fields
DETAIL- some fields.
I would really appreciate your help, or any changes in design & layout of the report.
Thanks A lot IN ADVANCE
Sud
As I understand your issue, you have three rows in Group Header 1, the first row you want to repeat on every page, the second row you only want it to show on the 1st page, is this correct?
You can try creating two groups with the same group expressions. Put the first row in the first group and mark it RepeatOnNewPage. Put the second row in the second group and don't repeat it.
Monday, March 26, 2012
Report Deployment Error:
Server did not recognize the value of HTTP Header SOAPAction:
http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportingservices/ListSecureMethods.
Could anyone help me to overcome this?
Please mention if any configuration changes are to be made.
Thanks.Looks like a version mismatch between your client (VS.NET) and your
server... open http://<servername>/reportserver and in VS.NET open Help |
About Microsoft Development Environment and select "Microsoft SQL Server
Report Designer"... compare the version numbers
--
Douglas McDowell douglas@.nospam.solidqualitylearning.com
"Thyagu" <Thyagu@.discussions.microsoft.com> wrote in message
news:A10C3285-CAB6-4A19-93A3-6DD7365F49B7@.microsoft.com...
> Hi while deploying the reports to the server I get the following error.
> Server did not recognize the value of HTTP Header SOAPAction:
> http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportingservices/ListSecureMethods.
> Could anyone help me to overcome this?
> Please mention if any configuration changes are to be made.
> Thanks.|||Douglas,
Thanks very much. The problem got solved once I upgraded the package to a
newer version.
Regards,
Thyagu
"Douglas McDowell" wrote:
> Looks like a version mismatch between your client (VS.NET) and your
> server... open http://<servername>/reportserver and in VS.NET open Help |
> About Microsoft Development Environment and select "Microsoft SQL Server
> Report Designer"... compare the version numbers
> --
> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>
> "Thyagu" <Thyagu@.discussions.microsoft.com> wrote in message
> news:A10C3285-CAB6-4A19-93A3-6DD7365F49B7@.microsoft.com...
> > Hi while deploying the reports to the server I get the following error.
> >
> > Server did not recognize the value of HTTP Header SOAPAction:
> > http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportingservices/ListSecureMethods.
> >
> > Could anyone help me to overcome this?
> > Please mention if any configuration changes are to be made.
> >
> > Thanks.
>
>
Report Deployment Error on --http://localhost/reportserver
-- Deploy started: Project: Report Project1, Configuration: DebugLocal --
Deploying to http://localhost/reportserver/?%2fReport+Project1
Error rsReportServerDatabaseUnavailable : The report server cannot opena connection to the report server database. A connection to thedatabase is required for all requests and processing.
Deploy complete -- 1 errors, 0 warnings
=====================================================
When I try to go tohttp://<server>/reportserver a PROMT window pop up "Enter username and password for "" at http://localhost. Atthis point I'm pretty desperate to get it to work., I have been tryingfor couple of days. I reinstalled reporting services couple of timeswith no success. Any help would be greatly appreciated. Thanks!
-lalon
Report deployment error in SharePoint Integration mode
Hi,
I get the following error when I am trying to deploy Report from Visual Studio Report Designer.
SRSS is configured in SharePoint integration mode. Configuration is mentioned below.
TITLE: Microsoft Report Designer
A connection could not be made to the report server http://rbddspsdev2:44887/.
ADDITIONAL INFORMATION:
Server was unable to process request. > The request failed with HTTP status 401: Unauthorized. (System.Web.Services)
BUTTONS:
OK
Configuration is as follows.
Web front end server.
MOSS 2007 Standard edition 64 bit.
SQL Reporting Service Add in 64 bit.
SQL Server
MS SQL server 2005 32 bit with sp2.
Reporting Services configured.
WSS 3.0 32 bit required for configuration, of SRSS in integrated mode.
Windows service and web service are both running under a domain account. The account is a SharePoint Admininstrator. The account is also in Administrators, Reporting Server user and Web service group and also has a dbo rights on database.
Hi
To publish a report to a SharePoint site configured for SharePoint integration, you must verify that the report project properties in Report Designer point to the SharePoint site. All references to servers, reports, shared data sources, and resources must be fully qualified URLs. The TargetServerURL property must be a URL to a SharePoint site. The TargetDataSourceFolder and TargetReportFolder properties must be URLs to a SharePoint library or library folder. You must have Member or Owner permission on the SharePoint site to set the properties on the project.
To publish a report to a SharePoint site
1. In SQL Server Development Studio, open an existing or new Report Server project.
2. From the Project menu, click Properties. The <project> Property Pages dialog box opens.
3. Choose the Configuration you use to publish to a SharePoint site. For example, Active(Debug) or Production.
4. In the Deployment section, for the optional TargetDataSourceFolder, type a URL to a library or library folder. For example, http://TestServer/TestSite/Documents/DataSources.
5. For TargetReportFolder, type a fully qualified URL to a library or library folder. For example, http://TestServer/TestSite/Documents.
6. For TargetServerURL, type a URL to a SharePoint top-level site or sub-site. If you do not specify a site, the default top-level site is used. For example, http://servername, http://servername/site or http://servername/site/subsite..
7. Click OK.
8. In Solution Explorer, right-click on the report you want to publish, and click Deploy. The report is published to the location specified in TargetReportFolder.
|||Please post back if you have resolved this issue, as I have the same issue.
I have SQL 2005 sp2 as a seperate server from my sharepoint server. I was running reports in native mode and converted to integrated. I am using domain accounts for windows and web services. I have all green checks in reports configuration. If I browse to http://sqlserver/reportserver/ I get the following error:
The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) Get Online Help
I don't see anything in the log - but would be happy to post entries if someone can spot my error in them.
From VS 2005 when I attempt to upload a report, I get "a connection could not be made to the report server Get Online Help
I don't see anything in the log - but would be happy to post entries if someone can spot my error in them.
From VS 2005 when I attempt to upload a report, I get "a connection could not be made to the report server https://sharepointserver/
In Sharepoint configuration, when I attempt to set server defaults I get: "
Troubleshoot issues with Windows SharePoint Services. "
I would really love to get this working.
Please check rs_log# file in ...:\Documents and Settings\[USER]\Local Settings\Temp
It should contain more details about your problem.
Are you using "Windows Integrated" or "Trusted Accounts" Authentication Mode?
|||Hi all,
My issue i guess was because of the double hop between my Reporting Server and SharePoint server. Anyways now i have Reporting Server and MOSS both on same machine and the problem seem to go away on its own.
Hope this helps other too.
Thanks,
|||Anyone an answer to this? I've seen many blogs w/ this issue but no answers.Wednesday, March 21, 2012
Report Builder Processing Error
Am having a problem processing reports using Report Builder. Have created the Data Source, DSV and Report Model and deployed them (following Report Builder walkthrough instructions in BOL). Building the report through the Report Builder app is also no problem. However when I try and process the report I always get the same error:
Report Execution Error Keyword not supported: 'provider'.
The Report might not be valid or the server could not process the data
-
Cannot create a connection to data source 'dataSource'. (rsErrorOpeningConnection)
-
An error has occurred during report processing. (rsProcessingAborted)
Have tried building reports using several different models off several different DBs with the same results. Has anyone else seen the same?
I know that in the April CTP Report Builder was not fully functional. Just want to know if this is just a CTP glitch or something I am missing. Either way I am very impressed with what RB is going to offer the ad-hoc reporting world. Really happy that MS will be including it with Standard as well as Enterprise.
Cheers,Please ignore - my fault! Error was being thrown because I used '.' as the server name (lazy way of saying localhost) in my data source.
Everything worked as soon as I typed the full name of the server and redeployed all objects.
Apologies for time wasting.
Cheers,sql
Report Builder not working - Us Too
Hi, Running RS2005 (was running RS2000 on same system)
When trying to run Report Builder I am getting the following error;
Report Execution Error
The report might not be valid or the server could not process the data.
An attempt has been made to use a rendering extension that is not registered for this report server.
Anyone come across this error before?
Thanks in advance
Scott
anywhere I can go to get an answer to this. Excuse the pun but this renders Report Builder useless
Advaned thanks to anyone who has any idea
Scott
|||We are getting same error on our shop. Have you found resolution yet?
|||nothing from anyone....if you find anything I would appreciate anything you find.
I have searched high and low.
One person on another forum said the following;
'Did you upgrade from RS2000 to RS2005? If so, prior to the upgrade, did you have any custom controls or third party controls you may have bought installed? That error message to me seems to say that the report is trying to show a control for which a dll (control source) does not exist. This would lead me to believe - based on you saying RS2000 worked before - that when you did an upgrade, you did not setup these custom controls. '
Maybe it might help
Scott
|||I spent at lot of time on Friday getting the SelfSSL working on the system. It was set up with the name of the computer, I managed to get it working with ComputerName.domain.com/local etc.
I have just come back to Report builder and it now works!! I am writing this just incase it gives you a clue to how I managed to get it working
Thanks
Scott
Report Builder not working - Us Too
Hi, Running RS2005 (was running RS2000 on same system)
When trying to run Report Builder I am getting the following error;
Report Execution Error
The report might not be valid or the server could not process the data.
An attempt has been made to use a rendering extension that is not registered for this report server.
Anyone come across this error before?
Thanks in advance
Scott
anywhere I can go to get an answer to this. Excuse the pun but this renders Report Builder useless
Advaned thanks to anyone who has any idea
Scott
|||We are getting same error on our shop. Have you found resolution yet?
|||nothing from anyone....if you find anything I would appreciate anything you find.
I have searched high and low.
One person on another forum said the following;
'Did you upgrade from RS2000 to RS2005? If so, prior to the upgrade, did you have any custom controls or third party controls you may have bought installed? That error message to me seems to say that the report is trying to show a control for which a dll (control source) does not exist. This would lead me to believe - based on you saying RS2000 worked before - that when you did an upgrade, you did not setup these custom controls. '
Maybe it might help
Scott
|||I spent at lot of time on Friday getting the SelfSSL working on the system. It was set up with the name of the computer, I managed to get it working with ComputerName.domain.com/local etc.
I have just come back to Report builder and it now works!! I am writing this just incase it gives you a clue to how I managed to get it working
Thanks
Scott
Report Builder not working
Hi, Running RS2005 (was running RS2000 on same system)
When trying to run Report Builder I am getting the following error;
Report Execution Error
The report might not be valid or the server could not process the data.
An attempt has been made to use a rendering extension that is not registered for this report server.
Anyone come across this error before?
Thanks in advance
Scott
anywhere I can go to get an answer to this. Excuse the pun but this renders Report Builder useless
Advaned thanks to anyone who has any idea
Scott
|||We are getting same error on our shop. Have you found resolution yet?
|||nothing from anyone....if you find anything I would appreciate anything you find.
I have searched high and low.
One person on another forum said the following;
'Did you upgrade from RS2000 to RS2005? If so, prior to the upgrade, did you have any custom controls or third party controls you may have bought installed? That error message to me seems to say that the report is trying to show a control for which a dll (control source) does not exist. This would lead me to believe - based on you saying RS2000 worked before - that when you did an upgrade, you did not setup these custom controls. '
Maybe it might help
Scott
|||I spent at lot of time on Friday getting the SelfSSL working on the system. It was set up with the name of the computer, I managed to get it working with ComputerName.domain.com/local etc.
I have just come back to Report builder and it now works!! I am writing this just incase it gives you a clue to how I managed to get it working
Thanks
Scott
Report Builder not working
Hi, Running RS2005 (was running RS2000 on same system)
When trying to run Report Builder I am getting the following error;
Report Execution Error
The report might not be valid or the server could not process the data.
An attempt has been made to use a rendering extension that is not registered for this report server.
Anyone come across this error before?
Thanks in advance
Scott
anywhere I can go to get an answer to this. Excuse the pun but this renders Report Builder useless
Advaned thanks to anyone who has any idea
Scott
|||We are getting same error on our shop. Have you found resolution yet?
|||nothing from anyone....if you find anything I would appreciate anything you find.
I have searched high and low.
One person on another forum said the following;
'Did you upgrade from RS2000 to RS2005? If so, prior to the upgrade, did you have any custom controls or third party controls you may have bought installed? That error message to me seems to say that the report is trying to show a control for which a dll (control source) does not exist. This would lead me to believe - based on you saying RS2000 worked before - that when you did an upgrade, you did not setup these custom controls. '
Maybe it might help
Scott
|||I spent at lot of time on Friday getting the SelfSSL working on the system. It was set up with the name of the computer, I managed to get it working with ComputerName.domain.com/local etc.
I have just come back to Report builder and it now works!! I am writing this just incase it gives you a clue to how I managed to get it working
Thanks
Scott
sqlTuesday, March 20, 2012
report builder how to
Col1: All Customers
Col2: Count of Orders in Last 12 months
my customers records get filtered when there is no order is last 12
months
but I'd like to show all customers and count of 0 where there is no
order in last 12 months
any idea?
KamelIf your SQL statement returns the two columns:
SELECT CustomerName, count(OrderID) as CountOfOrders
FROM ORDERTable
GROUP BY CustomerName
Then put those values into a table or list data region.
Is this what you are asking for?
"kamel" <kwiciak@.gmail.com> wrote in message
news:1148415235.959944.42070@.i39g2000cwa.googlegroups.com...
> Is it possible to create following table report:
> Col1: All Customers
> Col2: Count of Orders in Last 12 months
> my customers records get filtered when there is no order is last 12
> months
> but I'd like to show all customers and count of 0 where there is no
> order in last 12 months
> any idea?
> Kamel
>|||I talk about Report Builder with Report Model;
notice: Last 12 Months|||SQL which I would like to obtain in Report Builder:
select
CustomerName,
(select count(OrderID) from Orders where Orders.CustId =Customers.CustId and OrderDate > '2005-05-01')
from Customers
group by CustomerName
is it possible?|||Have you checked the cardinality of the relationship of the Customers entity
to the relationship of the Orders entity? It sounds like it should be
OptionalMany, meaning that there may be 0 or more Orders for each Customer.
If it is Many, then you are telling the model that each Customer has 1 or
more Orders.
Setting the cardinality from Many to OptionalMany should change the query
from something analogous to:
select
c.CustomerName,
count(o.OrderID)
from
Customers c
inner join
Orders o
on
c.CustId = o.CustId
where
o.OrderDate > '2005-05-01'
group by
c.CustomerName
to:
select
c.CustomerName,
count(o.OrderID)
from
Customers c
left outer join
Orders o
on
c.CustId = o.CustId
where
o.OrderDate > '2005-05-01'
group by
c.CustomerName
Having said this, my own experiments with Report Builder and Models show
that there may be a bug preventing the outer join from being used, even if
the cardinality is OptionalMany. However, the cardinality will have to be
set properly for it to work.|||Thank you!
It doesn't work with 3 entities as in my exaple (1 entity - "time" is
in a filter)
and relations are as:
Customer - Order - Time
Any ideas,
Kamel
larthallor wrote:
> Have you checked the cardinality of the relationship of the Customers entity
> to the relationship of the Orders entity? It sounds like it should be
> OptionalMany, meaning that there may be 0 or more Orders for each Customer.
> If it is Many, then you are telling the model that each Customer has 1 or
> more Orders.
> Setting the cardinality from Many to OptionalMany should change the query
> from something analogous to:
> select
> c.CustomerName,
> count(o.OrderID)
> from
> Customers c
> inner join
> Orders o
> on
> c.CustId = o.CustId
> where
> o.OrderDate > '2005-05-01'
> group by
> c.CustomerName
> to:
> select
> c.CustomerName,
> count(o.OrderID)
> from
> Customers c
> left outer join
> Orders o
> on
> c.CustId = o.CustId
> where
> o.OrderDate > '2005-05-01'
> group by
> c.CustomerName
> Having said this, my own experiments with Report Builder and Models show
> that there may be a bug preventing the outer join from being used, even if
> the cardinality is OptionalMany. However, the cardinality will have to be
> set properly for it to work.|||Refrash of topic.
I would like to create following query:
select
Customer.CustomerID,
Time.DayID,
count(Order.OrderID)
from
Customer LEFT OUTER JOIN
Time LEFT OUTER JOIN
Order
with ReportBuilder I can not obtain such a query.
RB puts table from whitch value is on matrix's data area on top, eg.
select
Customer.CustomerID,
Time.DayID,
count(Order.OrderID)
from
Order LEFT OUTER JOIN
Customer LEFT OUTER JOIN
Time
in this example count(Order.OrderID) is on data area of matrix type
report
Any ideas,
Kamel
kamel wrote:
> Thank you!
> It doesn't work with 3 entities as in my exaple (1 entity - "time" is
> in a filter)
> and relations are as:
> Customer - Order - Time
> Any ideas,
> Kamel
> larthallor wrote:
> > Have you checked the cardinality of the relationship of the Customers entity
> > to the relationship of the Orders entity? It sounds like it should be
> > OptionalMany, meaning that there may be 0 or more Orders for each Customer.
> > If it is Many, then you are telling the model that each Customer has 1 or
> > more Orders.
> >
> > Setting the cardinality from Many to OptionalMany should change the query
> > from something analogous to:
> >
> > select
> > c.CustomerName,
> > count(o.OrderID)
> >
> > from
> > Customers c
> > inner join
> > Orders o
> > on
> > c.CustId = o.CustId
> >
> > where
> > o.OrderDate > '2005-05-01'
> >
> > group by
> > c.CustomerName
> >
> > to:
> >
> > select
> > c.CustomerName,
> > count(o.OrderID)
> >
> > from
> > Customers c
> > left outer join
> > Orders o
> > on
> > c.CustId = o.CustId
> >
> > where
> > o.OrderDate > '2005-05-01'
> >
> > group by
> > c.CustomerName
> >
> > Having said this, my own experiments with Report Builder and Models show
> > that there may be a bug preventing the outer join from being used, even if
> > the cardinality is OptionalMany. However, the cardinality will have to be
> > set properly for it to work.
Report Builder error.
Hi,
I am facing a peculiar situation where my ad-hoc report is inter-mittently giving me the following error based on the parameter values I am selecting from the dropdown. What could be the possible reasons for this ? Report execution error
The report might not be valid or the server could not process the data.
An existing connection was forcibly closed by the remote host.
-
Unable to read data from the transport connection:An existing connection was forcibly closed by the remote host.
-
The underlying connection was closed: An unexpected error occured on a receive.
In the same report if I try to add another runtime filter, the application crashes. This filter field has about 50k available values.Is that the reason for such behaviour? What can be the possible solution here ?
Is this error displayed during execution (preview) of the report or during design-time?
If it is being displayed during design-time, try opening the model in Model Designer and change the element's "Value Selection" property to "None". This will tell Report Builder to use a textbox instead of a dropdown menu for this element.
If this error is during execution (preview) of the report, does it occur when the report is published and executed directly from the server?
HTH, Jason Tremper
|||Hi !
Sorry for the late response !The error is shown during execution of the ad-hoc report . As I said the above error comes only if I select some particular set of values from the parameter dropdowns.
The other question is that of the filter condition.The application crashes during the design time when I try to apply that filter.This filter probably tries to load all the available values which is about 50k!
I cannot make the 'Value Selection' property to 'None'. The user won't have a clue as to what to enter in the text box.
Report Builder error.
Hi,
I am facing a peculiar situation where my ad-hoc report is inter-mittently giving me the following error based on the parameter values I am selecting from the dropdown. What could be the possible reasons for this ?
Report execution error
The report might not be valid or the server could not process the data.
An existing connection was forcibly closed by the remote host.
-
Unable to read data from the transport connection:An existing connection was forcibly closed by the remote host.
-
The underlying connection was closed: An unexpected error occured on a receive.
In the same report if I try to add another runtime filter, the application crashes. This filter field has about 50k available values.Is that the reason for such behaviour? What can be the possible solution here ?
Is this error displayed during execution (preview) of the report or during design-time?
If it is being displayed during design-time, try opening the model in Model Designer and change the element's "Value Selection" property to "None". This will tell Report Builder to use a textbox instead of a dropdown menu for this element.
If this error is during execution (preview) of the report, does it occur when the report is published and executed directly from the server?
HTH, Jason Tremper
|||Hi !
Sorry for the late response !The error is shown during execution of the ad-hoc report . As I said the above error comes only if I select some particular set of values from the parameter dropdowns.
The other question is that of the filter condition.The application crashes during the design time when I try to apply that filter.This filter probably tries to load all the available values which is about 50k!
I cannot make the 'Value Selection' property to 'None'. The user won't have a clue as to what to enter in the text box.
Report Builder error with Cubes
I am receiving the following error when I run the report builder query. I am
able to successfully select the dimensions and measures that I am interested
in but when I run the report I get the error message.
The 'PerspectiveID' custom property for the 'query' perspective is either
not set or is not set to the string data type.
--
Semantic query execution failed.
--
Query execution failed for data set 'dataSet'.
--
An error has occurred during report processing.
I am testing this against both Adventureworks standard and enterprise cubes.
Additionally I saw this post in another forum
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=368962&SiteID=1
that seemed related but I am using reporting services in sharepoint
integrated mode so I am having a hard time trying to troubleshoot this.
Thanks for your help!On Dec 14, 2:14 am, Kodi <K...@.discussions.microsoft.com> wrote:
Hi
Whats your query ? Seems like you are using parameters which have not
been set.
Cheers
Shai
> Hi,
> I am receiving the following error when I run the report builder query. I am
> able to successfully select the dimensions and measures that I am interested
> in but when I run the report I get the error message.
> The 'PerspectiveID' custom property for the 'query' perspective is either
> not set or is not set to the string data type.
> --
> Semantic query execution failed.
> --
> Query execution failed for data set 'dataSet'.
> --
> An error has occurred during report processing.
> I am testing this against both Adventureworks standard and enterprise cubes.
> Additionally I saw this post in another forumhttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=368962&SiteID=1
> that seemed related but I am using reporting services in sharepoint
> integrated mode so I am having a hard time trying to troubleshoot this.
> Thanks for your help!|||Hi,
I work with kodi and he posted this for me while my subscription was in
progress.
When using Cubes with report builder the report model is generated
automatically by clicking generate a model from the data source (RDS) working
in Sharepoint in my case, Then you load that report model which opens up the
report builder and it is drag and drop from there. There are no parameter in
report builder that I know of, it is all add-hoc and you would use filters to
act like parameters.
Larry
"shaikat.das@.gmail.com" wrote:
> On Dec 14, 2:14 am, Kodi <K...@.discussions.microsoft.com> wrote:
> Hi
> Whats your query ? Seems like you are using parameters which have not
> been set.
> Cheers
> Shai
> > Hi,
> >
> > I am receiving the following error when I run the report builder query. I am
> > able to successfully select the dimensions and measures that I am interested
> > in but when I run the report I get the error message.
> >
> > The 'PerspectiveID' custom property for the 'query' perspective is either
> > not set or is not set to the string data type.
> > --
> > Semantic query execution failed.
> > --
> > Query execution failed for data set 'dataSet'.
> > --
> > An error has occurred during report processing.
> >
> > I am testing this against both Adventureworks standard and enterprise cubes.
> > Additionally I saw this post in another forumhttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=368962&SiteID=1
> > that seemed related but I am using reporting services in sharepoint
> > integrated mode so I am having a hard time trying to troubleshoot this.
> >
> > Thanks for your help!
>
Report Builder Error
When I create a report using Report Builder I receive the following error when I click Run Report. Any help would be appreciated. (I do not have a data source named datasource1!!!?)
w3wp!webserver!c!07/10/2006-10:30:39:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'dataSource1'. > System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
dataSource1 is the default datasource for the model.
I am assuming you are trying to run IIS without authentication. Do you have to run it that way or can you turn it on? From everything I read (and opinions will differ here) it seems that RS really wants to have authentication. Without it, how will you control who can deploy a model to your server?
Once you have authentication fixed, you can setup an impersonated user to access the database. Letting 'NT AUTHORITY\ANONYMOUS LOGON' access your SQL Server is a really bad idea.
R
|||Anonymous Access is disabled in IIS on the Report Server. (Integrated Windows Authentication is checked). That is why I cannot figure out why the Anonymous Logon account is attemtping to access the database?|||My problem was related to the Report Builder Model's Data Source. I changed the authentication method to SQL Server authentication and voila! It worked.|||Has anyone gotten the Report Builder to work with Integrated Security (given this is Microsoft's preferred authentication method)?Report Builder Error
When I create a report using Report Builder I receive the following error when I click Run Report. Any help would be appreciated. (I do not have a data source named datasource1!!!?)
w3wp!webserver!c!07/10/2006-10:30:39:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'dataSource1'. > System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
dataSource1 is the default datasource for the model.
I am assuming you are trying to run IIS without authentication. Do you have to run it that way or can you turn it on? From everything I read (and opinions will differ here) it seems that RS really wants to have authentication. Without it, how will you control who can deploy a model to your server?
Once you have authentication fixed, you can setup an impersonated user to access the database. Letting 'NT AUTHORITY\ANONYMOUS LOGON' access your SQL Server is a really bad idea.
R
|||Anonymous Access is disabled in IIS on the Report Server. (Integrated Windows Authentication is checked). That is why I cannot figure out why the Anonymous Logon account is attemtping to access the database?|||My problem was related to the Report Builder Model's Data Source. I changed the authentication method to SQL Server authentication and voila! It worked.|||Has anyone gotten the Report Builder to work with Integrated Security (given this is Microsoft's preferred authentication method)?Friday, March 9, 2012
Report Builder : select multiple tables in one report
I'm trying to create a report on Microsoft CRM on the following tables
dbo.FilteredAccount
dbo.FilteredNew_internatsamenwerking
dbo.FilteredNew_ImpExpCountry
I have created a data source view in with the following relations :
dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
I can create a report with fields from dbo.FilteredAccount and
dbo.FilteredNew_internatsamenwerking and another report with fields from
dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
Is this relationship 1 to N a restriction in Report Builder?
Do you have a solution?
Thanks in advanceIs all your tables connected with a key , then it should not be a problem
Amarnath
"carolineb" wrote:
> Hello,
> I'm trying to create a report on Microsoft CRM on the following tables
> dbo.FilteredAccount
> dbo.FilteredNew_internatsamenwerking
> dbo.FilteredNew_ImpExpCountry
> I have created a data source view in with the following relations :
> dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
>
> I can create a report with fields from dbo.FilteredAccount and
> dbo.FilteredNew_internatsamenwerking and another report with fields from
> dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> Is this relationship 1 to N a restriction in Report Builder?
> Do you have a solution?
> Thanks in advance
>
>|||Hi,
Table dbo.FilteredAccount has a primary key accountid
Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
dbo.FilteredAccount 1 to many relation on
dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
the field accountid in both tables)
I'm I missing something?
Thanks
"Amarnath" wrote:
> Is all your tables connected with a key , then it should not be a problem
> Amarnath
>
> "carolineb" wrote:
> > Hello,
> >
> > I'm trying to create a report on Microsoft CRM on the following tables
> > dbo.FilteredAccount
> > dbo.FilteredNew_internatsamenwerking
> > dbo.FilteredNew_ImpExpCountry
> >
> > I have created a data source view in with the following relations :
> >
> > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> >
> >
> > I can create a report with fields from dbo.FilteredAccount and
> > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> >
> > Is this relationship 1 to N a restriction in Report Builder?
> > Do you have a solution?
> >
> > Thanks in advance
> >
> >
> >|||Report Model can be designed in such a way that one table will act as storing
all the PK and FK's of the table.
ie taking your example.
dbo.FilteredAccount 1
account ID as PK
dbo.FilteredNew_internatsamenwerking
intsamenwerkingid PK
accountid as FK
dbo.FilteredNew_ImpExpCountry
impexpid as PK
accountid as FK
If you can design in such a way that both the other tables PK should be in
FilteredAccount Table then it should work. Since you are refereing just
account ID for all the tables it assumes 1 to 1 relation.
You can try this and see.
Amarnath
"carolineb" wrote:
> Hi,
> Table dbo.FilteredAccount has a primary key accountid
> Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> dbo.FilteredAccount 1 to many relation on
> dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> the field accountid in both tables)
> I'm I missing something?
> Thanks
> "Amarnath" wrote:
> > Is all your tables connected with a key , then it should not be a problem
> >
> > Amarnath
> >
> >
> > "carolineb" wrote:
> >
> > > Hello,
> > >
> > > I'm trying to create a report on Microsoft CRM on the following tables
> > > dbo.FilteredAccount
> > > dbo.FilteredNew_internatsamenwerking
> > > dbo.FilteredNew_ImpExpCountry
> > >
> > > I have created a data source view in with the following relations :
> > >
> > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > >
> > >
> > > I can create a report with fields from dbo.FilteredAccount and
> > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > >
> > > Is this relationship 1 to N a restriction in Report Builder?
> > > Do you have a solution?
> > >
> > > Thanks in advance
> > >
> > >
> > >|||Do you mean that i have to add the PK's as a field in the table
dbo.FilteredAccount 1
intsamenwerkingid PK
impexpid as PK
This is not possible since the relation is 1 to n in both tables?
Thanks
"Amarnath" wrote:
> Report Model can be designed in such a way that one table will act as storing
> all the PK and FK's of the table.
> ie taking your example.
> dbo.FilteredAccount 1
> account ID as PK
> dbo.FilteredNew_internatsamenwerking
> intsamenwerkingid PK
> accountid as FK
> dbo.FilteredNew_ImpExpCountry
> impexpid as PK
> accountid as FK
> If you can design in such a way that both the other tables PK should be in
> FilteredAccount Table then it should work. Since you are refereing just
> account ID for all the tables it assumes 1 to 1 relation.
> You can try this and see.
> Amarnath
> "carolineb" wrote:
> > Hi,
> >
> > Table dbo.FilteredAccount has a primary key accountid
> > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> >
> > dbo.FilteredAccount 1 to many relation on
> > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > the field accountid in both tables)
> >
> > I'm I missing something?
> >
> > Thanks
> >
> > "Amarnath" wrote:
> >
> > > Is all your tables connected with a key , then it should not be a problem
> > >
> > > Amarnath
> > >
> > >
> > > "carolineb" wrote:
> > >
> > > > Hello,
> > > >
> > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > dbo.FilteredAccount
> > > > dbo.FilteredNew_internatsamenwerking
> > > > dbo.FilteredNew_ImpExpCountry
> > > >
> > > > I have created a data source view in with the following relations :
> > > >
> > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > >
> > > >
> > > > I can create a report with fields from dbo.FilteredAccount and
> > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > >
> > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > Do you have a solution?
> > > >
> > > > Thanks in advance
> > > >
> > > >
> > > >|||Sorry, I mean that two id's should be there in your account table not as PK
as FK.
Amarnath
"carolineb" wrote:
> Do you mean that i have to add the PK's as a field in the table
> dbo.FilteredAccount 1
> intsamenwerkingid PK
> impexpid as PK
> This is not possible since the relation is 1 to n in both tables?
> Thanks
>
> "Amarnath" wrote:
> > Report Model can be designed in such a way that one table will act as storing
> > all the PK and FK's of the table.
> > ie taking your example.
> > dbo.FilteredAccount 1
> > account ID as PK
> > dbo.FilteredNew_internatsamenwerking
> > intsamenwerkingid PK
> > accountid as FK
> > dbo.FilteredNew_ImpExpCountry
> > impexpid as PK
> > accountid as FK
> >
> > If you can design in such a way that both the other tables PK should be in
> > FilteredAccount Table then it should work. Since you are refereing just
> > account ID for all the tables it assumes 1 to 1 relation.
> > You can try this and see.
> >
> > Amarnath
> >
> > "carolineb" wrote:
> >
> > > Hi,
> > >
> > > Table dbo.FilteredAccount has a primary key accountid
> > > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> > >
> > > dbo.FilteredAccount 1 to many relation on
> > > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > > the field accountid in both tables)
> > >
> > > I'm I missing something?
> > >
> > > Thanks
> > >
> > > "Amarnath" wrote:
> > >
> > > > Is all your tables connected with a key , then it should not be a problem
> > > >
> > > > Amarnath
> > > >
> > > >
> > > > "carolineb" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > > dbo.FilteredAccount
> > > > > dbo.FilteredNew_internatsamenwerking
> > > > > dbo.FilteredNew_ImpExpCountry
> > > > >
> > > > > I have created a data source view in with the following relations :
> > > > >
> > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > > >
> > > > >
> > > > > I can create a report with fields from dbo.FilteredAccount and
> > > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > > >
> > > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > > Do you have a solution?
> > > > >
> > > > > Thanks in advance
> > > > >
> > > > >
> > > > >|||This is not possible because one account can have many records
dbo.FilteredNew_ImpExpCountry and many records in
dbo.FilteredNew_internatsamenwerking.
"Amarnath" wrote:
> Sorry, I mean that two id's should be there in your account table not as PK
> as FK.
> Amarnath
> "carolineb" wrote:
> > Do you mean that i have to add the PK's as a field in the table
> > dbo.FilteredAccount 1
> > intsamenwerkingid PK
> > impexpid as PK
> > This is not possible since the relation is 1 to n in both tables?
> >
> > Thanks
> >
> >
> > "Amarnath" wrote:
> >
> > > Report Model can be designed in such a way that one table will act as storing
> > > all the PK and FK's of the table.
> > > ie taking your example.
> > > dbo.FilteredAccount 1
> > > account ID as PK
> > > dbo.FilteredNew_internatsamenwerking
> > > intsamenwerkingid PK
> > > accountid as FK
> > > dbo.FilteredNew_ImpExpCountry
> > > impexpid as PK
> > > accountid as FK
> > >
> > > If you can design in such a way that both the other tables PK should be in
> > > FilteredAccount Table then it should work. Since you are refereing just
> > > account ID for all the tables it assumes 1 to 1 relation.
> > > You can try this and see.
> > >
> > > Amarnath
> > >
> > > "carolineb" wrote:
> > >
> > > > Hi,
> > > >
> > > > Table dbo.FilteredAccount has a primary key accountid
> > > > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > > > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> > > >
> > > > dbo.FilteredAccount 1 to many relation on
> > > > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > > > the field accountid in both tables)
> > > >
> > > > I'm I missing something?
> > > >
> > > > Thanks
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Is all your tables connected with a key , then it should not be a problem
> > > > >
> > > > > Amarnath
> > > > >
> > > > >
> > > > > "carolineb" wrote:
> > > > >
> > > > > > Hello,
> > > > > >
> > > > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > > > dbo.FilteredAccount
> > > > > > dbo.FilteredNew_internatsamenwerking
> > > > > > dbo.FilteredNew_ImpExpCountry
> > > > > >
> > > > > > I have created a data source view in with the following relations :
> > > > > >
> > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > > > >
> > > > > >
> > > > > > I can create a report with fields from dbo.FilteredAccount and
> > > > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > > > >
> > > > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > > > Do you have a solution?
> > > > > >
> > > > > > Thanks in advance
> > > > > >
> > > > > >
> > > > > >|||You should connect a PK to PK and not a non PK key. in your case it is
account id in the other two tables.
Amarnath
"carolineb" wrote:
> This is not possible because one account can have many records
> dbo.FilteredNew_ImpExpCountry and many records in
> dbo.FilteredNew_internatsamenwerking.
> "Amarnath" wrote:
> > Sorry, I mean that two id's should be there in your account table not as PK
> > as FK.
> >
> > Amarnath
> >
> > "carolineb" wrote:
> >
> > > Do you mean that i have to add the PK's as a field in the table
> > > dbo.FilteredAccount 1
> > > intsamenwerkingid PK
> > > impexpid as PK
> > > This is not possible since the relation is 1 to n in both tables?
> > >
> > > Thanks
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > Report Model can be designed in such a way that one table will act as storing
> > > > all the PK and FK's of the table.
> > > > ie taking your example.
> > > > dbo.FilteredAccount 1
> > > > account ID as PK
> > > > dbo.FilteredNew_internatsamenwerking
> > > > intsamenwerkingid PK
> > > > accountid as FK
> > > > dbo.FilteredNew_ImpExpCountry
> > > > impexpid as PK
> > > > accountid as FK
> > > >
> > > > If you can design in such a way that both the other tables PK should be in
> > > > FilteredAccount Table then it should work. Since you are refereing just
> > > > account ID for all the tables it assumes 1 to 1 relation.
> > > > You can try this and see.
> > > >
> > > > Amarnath
> > > >
> > > > "carolineb" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Table dbo.FilteredAccount has a primary key accountid
> > > > > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > > > > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> > > > >
> > > > > dbo.FilteredAccount 1 to many relation on
> > > > > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > > > > the field accountid in both tables)
> > > > >
> > > > > I'm I missing something?
> > > > >
> > > > > Thanks
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > Is all your tables connected with a key , then it should not be a problem
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > >
> > > > > > "carolineb" wrote:
> > > > > >
> > > > > > > Hello,
> > > > > > >
> > > > > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > > > > dbo.FilteredAccount
> > > > > > > dbo.FilteredNew_internatsamenwerking
> > > > > > > dbo.FilteredNew_ImpExpCountry
> > > > > > >
> > > > > > > I have created a data source view in with the following relations :
> > > > > > >
> > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > > > > >
> > > > > > >
> > > > > > > I can create a report with fields from dbo.FilteredAccount and
> > > > > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > > > > >
> > > > > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > > > > Do you have a solution?
> > > > > > >
> > > > > > > Thanks in advance
> > > > > > >
> > > > > > >
> > > > > > >|||Sorry, i cannot follow anymore.
Can you please say which key's in these tables has to be a PK and FK and
what relationship i have to design?
Thanks
"Amarnath" wrote:
> You should connect a PK to PK and not a non PK key. in your case it is
> account id in the other two tables.
> Amarnath
> "carolineb" wrote:
> > This is not possible because one account can have many records
> > dbo.FilteredNew_ImpExpCountry and many records in
> > dbo.FilteredNew_internatsamenwerking.
> >
> > "Amarnath" wrote:
> >
> > > Sorry, I mean that two id's should be there in your account table not as PK
> > > as FK.
> > >
> > > Amarnath
> > >
> > > "carolineb" wrote:
> > >
> > > > Do you mean that i have to add the PK's as a field in the table
> > > > dbo.FilteredAccount 1
> > > > intsamenwerkingid PK
> > > > impexpid as PK
> > > > This is not possible since the relation is 1 to n in both tables?
> > > >
> > > > Thanks
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Report Model can be designed in such a way that one table will act as storing
> > > > > all the PK and FK's of the table.
> > > > > ie taking your example.
> > > > > dbo.FilteredAccount 1
> > > > > account ID as PK
> > > > > dbo.FilteredNew_internatsamenwerking
> > > > > intsamenwerkingid PK
> > > > > accountid as FK
> > > > > dbo.FilteredNew_ImpExpCountry
> > > > > impexpid as PK
> > > > > accountid as FK
> > > > >
> > > > > If you can design in such a way that both the other tables PK should be in
> > > > > FilteredAccount Table then it should work. Since you are refereing just
> > > > > account ID for all the tables it assumes 1 to 1 relation.
> > > > > You can try this and see.
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "carolineb" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > Table dbo.FilteredAccount has a primary key accountid
> > > > > > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > > > > > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> > > > > >
> > > > > > dbo.FilteredAccount 1 to many relation on
> > > > > > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > > > > > the field accountid in both tables)
> > > > > >
> > > > > > I'm I missing something?
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > "Amarnath" wrote:
> > > > > >
> > > > > > > Is all your tables connected with a key , then it should not be a problem
> > > > > > >
> > > > > > > Amarnath
> > > > > > >
> > > > > > >
> > > > > > > "carolineb" wrote:
> > > > > > >
> > > > > > > > Hello,
> > > > > > > >
> > > > > > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > > > > > dbo.FilteredAccount
> > > > > > > > dbo.FilteredNew_internatsamenwerking
> > > > > > > > dbo.FilteredNew_ImpExpCountry
> > > > > > > >
> > > > > > > > I have created a data source view in with the following relations :
> > > > > > > >
> > > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > > > > > >
> > > > > > > >
> > > > > > > > I can create a report with fields from dbo.FilteredAccount and
> > > > > > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > > > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > > > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > > > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > > > > > >
> > > > > > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > > > > > Do you have a solution?
> > > > > > > >
> > > > > > > > Thanks in advance
> > > > > > > >
> > > > > > > >
> > > > > > > >|||Ok, What I did is I created 3 dummy tables, same as yours,
ie
t1 aid PK
t2 tid2 PK ,aid FK
t3 tid3 PK, aid Fk
When I created these tables and created the model I got to see all the 3
tables in the report builder, So what I think you are missing is that you
have not defined the FK to other two tables
ie
dbo.FilteredNew_internatsamenwerking
dbo.FilteredNew_ImpExpCountry
Have you created Fk in the above tables which referes the FilteredAccount
table "accountid" ? then it should work, so basically it all works with
proper key and relations.
Amarnath
"carolineb" wrote:
> Sorry, i cannot follow anymore.
> Can you please say which key's in these tables has to be a PK and FK and
> what relationship i have to design?
> Thanks
> "Amarnath" wrote:
> > You should connect a PK to PK and not a non PK key. in your case it is
> > account id in the other two tables.
> >
> > Amarnath
> >
> > "carolineb" wrote:
> >
> > > This is not possible because one account can have many records
> > > dbo.FilteredNew_ImpExpCountry and many records in
> > > dbo.FilteredNew_internatsamenwerking.
> > >
> > > "Amarnath" wrote:
> > >
> > > > Sorry, I mean that two id's should be there in your account table not as PK
> > > > as FK.
> > > >
> > > > Amarnath
> > > >
> > > > "carolineb" wrote:
> > > >
> > > > > Do you mean that i have to add the PK's as a field in the table
> > > > > dbo.FilteredAccount 1
> > > > > intsamenwerkingid PK
> > > > > impexpid as PK
> > > > > This is not possible since the relation is 1 to n in both tables?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > Report Model can be designed in such a way that one table will act as storing
> > > > > > all the PK and FK's of the table.
> > > > > > ie taking your example.
> > > > > > dbo.FilteredAccount 1
> > > > > > account ID as PK
> > > > > > dbo.FilteredNew_internatsamenwerking
> > > > > > intsamenwerkingid PK
> > > > > > accountid as FK
> > > > > > dbo.FilteredNew_ImpExpCountry
> > > > > > impexpid as PK
> > > > > > accountid as FK
> > > > > >
> > > > > > If you can design in such a way that both the other tables PK should be in
> > > > > > FilteredAccount Table then it should work. Since you are refereing just
> > > > > > account ID for all the tables it assumes 1 to 1 relation.
> > > > > > You can try this and see.
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "carolineb" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > Table dbo.FilteredAccount has a primary key accountid
> > > > > > > Table dbo.FilteredNew_internatsamenwerking has a primary key intsamenwerkingid
> > > > > > > Table dbo.FilteredNew_ImpExpCountry has a primary key impexpid
> > > > > > >
> > > > > > > dbo.FilteredAccount 1 to many relation on
> > > > > > > dbo.FilteredNew_internatsamenwerking (on the field accountid in both tables)
> > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry (on
> > > > > > > the field accountid in both tables)
> > > > > > >
> > > > > > > I'm I missing something?
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > > "Amarnath" wrote:
> > > > > > >
> > > > > > > > Is all your tables connected with a key , then it should not be a problem
> > > > > > > >
> > > > > > > > Amarnath
> > > > > > > >
> > > > > > > >
> > > > > > > > "carolineb" wrote:
> > > > > > > >
> > > > > > > > > Hello,
> > > > > > > > >
> > > > > > > > > I'm trying to create a report on Microsoft CRM on the following tables
> > > > > > > > > dbo.FilteredAccount
> > > > > > > > > dbo.FilteredNew_internatsamenwerking
> > > > > > > > > dbo.FilteredNew_ImpExpCountry
> > > > > > > > >
> > > > > > > > > I have created a data source view in with the following relations :
> > > > > > > > >
> > > > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> > > > > > > > > dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > I can create a report with fields from dbo.FilteredAccount and
> > > > > > > > > dbo.FilteredNew_internatsamenwerking and another report with fields from
> > > > > > > > > dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> > > > > > > > > in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> > > > > > > > > disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> > > > > > > > >
> > > > > > > > > Is this relationship 1 to N a restriction in Report Builder?
> > > > > > > > > Do you have a solution?
> > > > > > > > >
> > > > > > > > > Thanks in advance
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >|||Hi CarolineB,
did you ever resolve your issues?
I have the same problem:
I have three tables:
Table A: PK A_ID
Table B: PK is A_ID, B_ID, FK is A_ID into Table A
Table C: PK is A_ID, C_ID, FK as A_ID into Table A.
I can see all three tables in Report Builder.
I can add fields from Table A and Table B at the same time to a single report.
I can add fields from Table A and Table C at the same time to a single report.
I cannot add fields from Table A and Table B and Table C at the same time,
yet this is a simple requirement.
Think of it as Customers, Orders & Payments.
A Customer may have 0, 1 or more Orders. Each Order has one and one only
Customer. A Customer may have 0, 1 or more Payments. Each Payment is for
one and one only Customer.
Payments and Orders are not directly related.
I'd like to list all customers with outstanding orders, and they payments
they've made. I can't do this using report builder.
"carolineb" wrote:
> Hello,
> I'm trying to create a report on Microsoft CRM on the following tables
> dbo.FilteredAccount
> dbo.FilteredNew_internatsamenwerking
> dbo.FilteredNew_ImpExpCountry
> I have created a data source view in with the following relations :
> dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_internatsamenwerking
> dbo.FilteredAccount 1 to many relation on dbo.FilteredNew_ImpExpCountry
>
> I can create a report with fields from dbo.FilteredAccount and
> dbo.FilteredNew_internatsamenwerking and another report with fields from
> dbo.FilteredAccount and dbo.FilteredNew_ImpExpCountry. But i can't get fields
> in one report from the 3 tables, dbo.FilteredNew_internatsamenwerking is
> disabeling dbo.FilteredNew_ImpExpCountry and visa versa.
> Is this relationship 1 to N a restriction in Report Builder?
> Do you have a solution?
> Thanks in advance
>
>
Saturday, February 25, 2012
Report Authenication
I have the following scenario:
1) A database server (machine A, Windows 2000 Server Standard) within a
domain
2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
same domain; the Reporting-Services-Database is also on the Database-server
3) My Client (windows XP) within the same domain.
My Problem: When I publisch a Report to the Reporting Server and I use
"Windows integrated Authentication", I get an error "NT-Authority/Anonymous
User" cant access the database. That's right, but why wouldn't be used my
Username/password instead of Anonymous? What have I to do, to pass my
Logonuser form XP over the Reporting-Services to the Database ? A always
thought "windows integrated authentication" in the shared Connection object
would be enough?
Thanks
HansIt sounds to me like you have IIS setup for anonymous access. When you do
this all users are the same user (the anonymous user as you see below). You
should only be doing this if you are implementing custom authorization.
Otherwise this is a really bad idea.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hans" <sqlserver@.strengberg.net> wrote in message
news:O0FIBcazEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the
Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error
"NT-Authority/Anonymous
> User" cant access the database. That's right, but why wouldn't be used my
> Username/password instead of Anonymous? What have I to do, to pass my
> Logonuser form XP over the Reporting-Services to the Database ? A always
> thought "windows integrated authentication" in the shared Connection
object
> would be enough?
> Thanks
> Hans
>|||If you are trying to authenticate in a scenerio which involves more than two
endpoints eg: client connects to remote RS web service which connects to a
remote database (3 endpoints) and tou're using Integrated security, NTLM
authentication will not work, you will have to setup a delegation scenerio
using kerberos authentication which supports more than one network hop. Look
here for help about setting up a asp.net app for delegation.
http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
To test that this is your problem first access the report from the reporting
services console, this seems to cache your credential locally, now try from
your remote client try the same report, it should work until you log off the
rs server.
HTH,
Dan
"Hans" wrote:
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error "NT-Authority/Anonymous
> User" cant access the database. That's right, but why wouldn't be used my
> Username/password instead of Anonymous? What have I to do, to pass my
> Logonuser form XP over the Reporting-Services to the Database ? A always
> thought "windows integrated authentication" in the shared Connection object
> would be enough?
> Thanks
> Hans
>
>|||Thanks to all for the answers. I have disabled all anonymous and I think,
its the problem, Dan reports, because I have 3 Endpoints. I will test this.
Thanks again.
Hans
"Hans" <sqlserver@.strengberg.net> schrieb im Newsbeitrag
news:O0FIBcazEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the
> Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error
> "NT-Authority/Anonymous User" cant access the database. That's right, but
> why wouldn't be used my Username/password instead of Anonymous? What have
> I to do, to pass my Logonuser form XP over the Reporting-Services to the
> Database ? A always thought "windows integrated authentication" in the
> shared Connection object would be enough?
> Thanks
> Hans
>
Reporing Service and deploy permission
Dear all,
I use Window Vista+ Sql server 2005 with SP 2
When I deploy the report in Report designer, the following error message display
The permissions granted to user 'winVista\admin' are insufficient for performing this operation.
The user "admin" is a admininstor and I assign the following group to it
(1) administors
(2)WINVISTA\SQLServer2005ReportingServicesWebServiceUser$WINVISTA$MSSQLSERVER
(3)WINVISTA\SQLServer2005ReportServerUser$WINVISTA$MSSQLSERVER
Which and Where I must assign the permissions to this account?
thanks you
If you connect to Reporting Services in Sql Server Management Studio, right click on the servername and select the properties of the server. Then select the permissions page. What's in there?