Friday, March 30, 2012
Report Error when 2 pages for multiple records
tool. The problem we are having is when we have a report that goes to
numerous people and the report is 2 pages long. When we make it 1 page,
legal size, it works fine (avg records are 500-900). When this report is
generated it is created in a pdf format. I can run it on report services
manager and in the viewer it takes approx 10 secs or less to run and render,
but when we create the pdf it spikes the processor up to 90% and if it's a 2
page report it generates an error.PDF's take significantly longer than HTML but it surprises me that a 2 page
report you see much of a difference. Do you have any service packs
installed. If not, my advice is to install SP2.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
news:BBA2388F-E062-4368-987A-62CD9298BD14@.microsoft.com...
> We have a web app, built in .Net, with reporting services as our reporting
> tool. The problem we are having is when we have a report that goes to
> numerous people and the report is 2 pages long. When we make it 1 page,
> legal size, it works fine (avg records are 500-900). When this report is
> generated it is created in a pdf format. I can run it on report services
> manager and in the viewer it takes approx 10 secs or less to run and
> render,
> but when we create the pdf it spikes the processor up to 90% and if it's a
> 2
> page report it generates an error.|||I have all of the service packs installed. This is a 2 page report that
generates approx 950 pages. I have similar reports, but set to legal single
page, that generate over 1000 pages in pdf. The error message I get is:
"Server Error in '/' Application.
The underlying connection was closed: An unexpected error occurred on a
receive.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details: System.Net.WebException: The underlying connection was
closed: An unexpected error occurred on a receive.
Source Error:
An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.
Stack Trace:
[WebException: The underlying connection was closed: An unexpected error
occurred on a receive.]
System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebReques
t request) +58
System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRe
quest request) +5
System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
methodName, Object[] parameters) +183
LAMS.SQLReportServer.ReportingService.Render(String Report, String
Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters,
DataSourceCredentials[] Credentials, String ShowHideToggle, String& Encoding,
String& MimeType, ParameterValue[]& ParametersUsed, Warning[]& Warnings,
String[]& StreamIds) +141
LAMS.ReportViewer.RenderPDFReport() +618
LAMS.ReportViewer.Page_Load(Object sender, EventArgs e) +88
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750"
"Bruce L-C [MVP]" wrote:
> PDF's take significantly longer than HTML but it surprises me that a 2 page
> report you see much of a difference. Do you have any service packs
> installed. If not, my advice is to install SP2.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> news:BBA2388F-E062-4368-987A-62CD9298BD14@.microsoft.com...
> > We have a web app, built in .Net, with reporting services as our reporting
> > tool. The problem we are having is when we have a report that goes to
> > numerous people and the report is 2 pages long. When we make it 1 page,
> > legal size, it works fine (avg records are 500-900). When this report is
> > generated it is created in a pdf format. I can run it on report services
> > manager and in the viewer it takes approx 10 secs or less to run and
> > render,
> > but when we create the pdf it spikes the processor up to 90% and if it's a
> > 2
> > page report it generates an error.
>
>|||OK, we had a failure to communicate. You said a 2 page report and I thought
it was 2 pages long. You mean 2 pages per record.
RS renders everything in RAM. Formats make a big difference in CPU
utilizations. CSV, HTML are very fast. PDF and Excel are much slower and
much much more intensive in resources required. My personal feeling is that
when you are talking about that many pages it is an inappropriate use of RS.
It is just not designed to be outputting 1000 pages of PDF. Users cannot
look at that many pages anyway. You should consider drill through technique.
Show the users what they need to see. I do know that sometimes users need to
pull data into another program but that is not what PDF format is used for.
Excel is another matter (although Excel can't handle 100,000 rows either).
For Excel you could use CSV to get around this. For PDF there is no work
around. More RAM helps but you really are overloading the server when you
are doing this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
news:790D7C48-1AB7-4F2A-A1C3-A1CF679B9B52@.microsoft.com...
>I have all of the service packs installed. This is a 2 page report that
> generates approx 950 pages. I have similar reports, but set to legal
> single
> page, that generate over 1000 pages in pdf. The error message I get is:
> "Server Error in '/' Application.
> The underlying connection was closed: An unexpected error occurred on a
> receive.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.Net.WebException: The underlying connection was
> closed: An unexpected error occurred on a receive.
> Source Error:
> An unhandled exception was generated during the execution of the current
> web
> request. Information regarding the origin and location of the exception
> can
> be identified using the exception stack trace below.
> Stack Trace:
> [WebException: The underlying connection was closed: An unexpected error
> occurred on a receive.]
> System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebReques
> t request) +58
> System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRe
> quest request) +5
> System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
> methodName, Object[] parameters) +183
> LAMS.SQLReportServer.ReportingService.Render(String Report, String
> Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters,
> DataSourceCredentials[] Credentials, String ShowHideToggle, String&
> Encoding,
> String& MimeType, ParameterValue[]& ParametersUsed, Warning[]& Warnings,
> String[]& StreamIds) +141
> LAMS.ReportViewer.RenderPDFReport() +618
> LAMS.ReportViewer.Page_Load(Object sender, EventArgs e) +88
> System.Web.UI.Control.OnLoad(EventArgs e) +67
> System.Web.UI.Control.LoadRecursive() +35
> System.Web.UI.Page.ProcessRequestMain() +750"
> "Bruce L-C [MVP]" wrote:
>> PDF's take significantly longer than HTML but it surprises me that a 2
>> page
>> report you see much of a difference. Do you have any service packs
>> installed. If not, my advice is to install SP2.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
>> news:BBA2388F-E062-4368-987A-62CD9298BD14@.microsoft.com...
>> > We have a web app, built in .Net, with reporting services as our
>> > reporting
>> > tool. The problem we are having is when we have a report that goes to
>> > numerous people and the report is 2 pages long. When we make it 1
>> > page,
>> > legal size, it works fine (avg records are 500-900). When this report
>> > is
>> > generated it is created in a pdf format. I can run it on report
>> > services
>> > manager and in the viewer it takes approx 10 secs or less to run and
>> > render,
>> > but when we create the pdf it spikes the processor up to 90% and if
>> > it's a
>> > 2
>> > page report it generates an error.
>>|||Thanks for the input. The only problem is this is our leasing application
and we have to print mass batch of renewals once a year. I am thinking of
setting it on a schedule but that would remove a data range filter that is in
place and is changable by the user. Any suggestions?
"Bruce L-C [MVP]" wrote:
> OK, we had a failure to communicate. You said a 2 page report and I thought
> it was 2 pages long. You mean 2 pages per record.
> RS renders everything in RAM. Formats make a big difference in CPU
> utilizations. CSV, HTML are very fast. PDF and Excel are much slower and
> much much more intensive in resources required. My personal feeling is that
> when you are talking about that many pages it is an inappropriate use of RS.
> It is just not designed to be outputting 1000 pages of PDF. Users cannot
> look at that many pages anyway. You should consider drill through technique.
> Show the users what they need to see. I do know that sometimes users need to
> pull data into another program but that is not what PDF format is used for.
> Excel is another matter (although Excel can't handle 100,000 rows either).
> For Excel you could use CSV to get around this. For PDF there is no work
> around. More RAM helps but you really are overloading the server when you
> are doing this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> news:790D7C48-1AB7-4F2A-A1C3-A1CF679B9B52@.microsoft.com...
> >I have all of the service packs installed. This is a 2 page report that
> > generates approx 950 pages. I have similar reports, but set to legal
> > single
> > page, that generate over 1000 pages in pdf. The error message I get is:
> >
> > "Server Error in '/' Application.
> > The underlying connection was closed: An unexpected error occurred on a
> > receive.
> > Description: An unhandled exception occurred during the execution of the
> > current web request. Please review the stack trace for more information
> > about
> > the error and where it originated in the code.
> > Exception Details: System.Net.WebException: The underlying connection was
> > closed: An unexpected error occurred on a receive.
> > Source Error:
> > An unhandled exception was generated during the execution of the current
> > web
> > request. Information regarding the origin and location of the exception
> > can
> > be identified using the exception stack trace below.
> >
> > Stack Trace:
> > [WebException: The underlying connection was closed: An unexpected error
> > occurred on a receive.]
> > System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebReques
> > t request) +58
> > System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRe
> > quest request) +5
> > System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
> > methodName, Object[] parameters) +183
> > LAMS.SQLReportServer.ReportingService.Render(String Report, String
> > Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters,
> > DataSourceCredentials[] Credentials, String ShowHideToggle, String&
> > Encoding,
> > String& MimeType, ParameterValue[]& ParametersUsed, Warning[]& Warnings,
> > String[]& StreamIds) +141
> > LAMS.ReportViewer.RenderPDFReport() +618
> > LAMS.ReportViewer.Page_Load(Object sender, EventArgs e) +88
> > System.Web.UI.Control.OnLoad(EventArgs e) +67
> > System.Web.UI.Control.LoadRecursive() +35
> > System.Web.UI.Page.ProcessRequestMain() +750"
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> PDF's take significantly longer than HTML but it surprises me that a 2
> >> page
> >> report you see much of a difference. Do you have any service packs
> >> installed. If not, my advice is to install SP2.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> >> news:BBA2388F-E062-4368-987A-62CD9298BD14@.microsoft.com...
> >> > We have a web app, built in .Net, with reporting services as our
> >> > reporting
> >> > tool. The problem we are having is when we have a report that goes to
> >> > numerous people and the report is 2 pages long. When we make it 1
> >> > page,
> >> > legal size, it works fine (avg records are 500-900). When this report
> >> > is
> >> > generated it is created in a pdf format. I can run it on report
> >> > services
> >> > manager and in the viewer it takes approx 10 secs or less to run and
> >> > render,
> >> > but when we create the pdf it spikes the processor up to 90% and if
> >> > it's a
> >> > 2
> >> > page report it generates an error.
> >>
> >>
> >>
>
>|||If you can have parameters that allows you to do a subset then schedule the
report multiple times just varying the parameter and print say 100 pages at
a time.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
news:6B4C566B-C41C-49FD-BF58-2FCD7B9656E3@.microsoft.com...
> Thanks for the input. The only problem is this is our leasing application
> and we have to print mass batch of renewals once a year. I am thinking of
> setting it on a schedule but that would remove a data range filter that is
> in
> place and is changable by the user. Any suggestions?
> "Bruce L-C [MVP]" wrote:
>> OK, we had a failure to communicate. You said a 2 page report and I
>> thought
>> it was 2 pages long. You mean 2 pages per record.
>> RS renders everything in RAM. Formats make a big difference in CPU
>> utilizations. CSV, HTML are very fast. PDF and Excel are much slower and
>> much much more intensive in resources required. My personal feeling is
>> that
>> when you are talking about that many pages it is an inappropriate use of
>> RS.
>> It is just not designed to be outputting 1000 pages of PDF. Users cannot
>> look at that many pages anyway. You should consider drill through
>> technique.
>> Show the users what they need to see. I do know that sometimes users need
>> to
>> pull data into another program but that is not what PDF format is used
>> for.
>> Excel is another matter (although Excel can't handle 100,000 rows
>> either).
>> For Excel you could use CSV to get around this. For PDF there is no work
>> around. More RAM helps but you really are overloading the server when you
>> are doing this.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
>> news:790D7C48-1AB7-4F2A-A1C3-A1CF679B9B52@.microsoft.com...
>> >I have all of the service packs installed. This is a 2 page report that
>> > generates approx 950 pages. I have similar reports, but set to legal
>> > single
>> > page, that generate over 1000 pages in pdf. The error message I get
>> > is:
>> >
>> > "Server Error in '/' Application.
>> > The underlying connection was closed: An unexpected error occurred on a
>> > receive.
>> > Description: An unhandled exception occurred during the execution of
>> > the
>> > current web request. Please review the stack trace for more information
>> > about
>> > the error and where it originated in the code.
>> > Exception Details: System.Net.WebException: The underlying connection
>> > was
>> > closed: An unexpected error occurred on a receive.
>> > Source Error:
>> > An unhandled exception was generated during the execution of the
>> > current
>> > web
>> > request. Information regarding the origin and location of the exception
>> > can
>> > be identified using the exception stack trace below.
>> >
>> > Stack Trace:
>> > [WebException: The underlying connection was closed: An unexpected
>> > error
>> > occurred on a receive.]
>> > System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebReques
>> > t request) +58
>> > System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRe
>> > quest request) +5
>> > System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
>> > methodName, Object[] parameters) +183
>> > LAMS.SQLReportServer.ReportingService.Render(String Report, String
>> > Format, String HistoryID, String DeviceInfo, ParameterValue[]
>> > Parameters,
>> > DataSourceCredentials[] Credentials, String ShowHideToggle, String&
>> > Encoding,
>> > String& MimeType, ParameterValue[]& ParametersUsed, Warning[]&
>> > Warnings,
>> > String[]& StreamIds) +141
>> > LAMS.ReportViewer.RenderPDFReport() +618
>> > LAMS.ReportViewer.Page_Load(Object sender, EventArgs e) +88
>> > System.Web.UI.Control.OnLoad(EventArgs e) +67
>> > System.Web.UI.Control.LoadRecursive() +35
>> > System.Web.UI.Page.ProcessRequestMain() +750"
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> PDF's take significantly longer than HTML but it surprises me that a 2
>> >> page
>> >> report you see much of a difference. Do you have any service packs
>> >> installed. If not, my advice is to install SP2.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
>> >> news:BBA2388F-E062-4368-987A-62CD9298BD14@.microsoft.com...
>> >> > We have a web app, built in .Net, with reporting services as our
>> >> > reporting
>> >> > tool. The problem we are having is when we have a report that goes
>> >> > to
>> >> > numerous people and the report is 2 pages long. When we make it 1
>> >> > page,
>> >> > legal size, it works fine (avg records are 500-900). When this
>> >> > report
>> >> > is
>> >> > generated it is created in a pdf format. I can run it on report
>> >> > services
>> >> > manager and in the viewer it takes approx 10 secs or less to run and
>> >> > render,
>> >> > but when we create the pdf it spikes the processor up to 90% and if
>> >> > it's a
>> >> > 2
>> >> > page report it generates an error.
>> >>
>> >>
>> >>
>>|||Thanks a bunch for the info Bruce.
"Bruce L-C [MVP]" wrote:
> If you can have parameters that allows you to do a subset then schedule the
> report multiple times just varying the parameter and print say 100 pages at
> a time.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> news:6B4C566B-C41C-49FD-BF58-2FCD7B9656E3@.microsoft.com...
> > Thanks for the input. The only problem is this is our leasing application
> > and we have to print mass batch of renewals once a year. I am thinking of
> > setting it on a schedule but that would remove a data range filter that is
> > in
> > place and is changable by the user. Any suggestions?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> OK, we had a failure to communicate. You said a 2 page report and I
> >> thought
> >> it was 2 pages long. You mean 2 pages per record.
> >>
> >> RS renders everything in RAM. Formats make a big difference in CPU
> >> utilizations. CSV, HTML are very fast. PDF and Excel are much slower and
> >> much much more intensive in resources required. My personal feeling is
> >> that
> >> when you are talking about that many pages it is an inappropriate use of
> >> RS.
> >> It is just not designed to be outputting 1000 pages of PDF. Users cannot
> >> look at that many pages anyway. You should consider drill through
> >> technique.
> >> Show the users what they need to see. I do know that sometimes users need
> >> to
> >> pull data into another program but that is not what PDF format is used
> >> for.
> >> Excel is another matter (although Excel can't handle 100,000 rows
> >> either).
> >> For Excel you could use CSV to get around this. For PDF there is no work
> >> around. More RAM helps but you really are overloading the server when you
> >> are doing this.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> >> news:790D7C48-1AB7-4F2A-A1C3-A1CF679B9B52@.microsoft.com...
> >> >I have all of the service packs installed. This is a 2 page report that
> >> > generates approx 950 pages. I have similar reports, but set to legal
> >> > single
> >> > page, that generate over 1000 pages in pdf. The error message I get
> >> > is:
> >> >
> >> > "Server Error in '/' Application.
> >> > The underlying connection was closed: An unexpected error occurred on a
> >> > receive.
> >> > Description: An unhandled exception occurred during the execution of
> >> > the
> >> > current web request. Please review the stack trace for more information
> >> > about
> >> > the error and where it originated in the code.
> >> > Exception Details: System.Net.WebException: The underlying connection
> >> > was
> >> > closed: An unexpected error occurred on a receive.
> >> > Source Error:
> >> > An unhandled exception was generated during the execution of the
> >> > current
> >> > web
> >> > request. Information regarding the origin and location of the exception
> >> > can
> >> > be identified using the exception stack trace below.
> >> >
> >> > Stack Trace:
> >> > [WebException: The underlying connection was closed: An unexpected
> >> > error
> >> > occurred on a receive.]
> >> > System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebReques
> >> > t request) +58
> >> > System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRe
> >> > quest request) +5
> >> > System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
> >> > methodName, Object[] parameters) +183
> >> > LAMS.SQLReportServer.ReportingService.Render(String Report, String
> >> > Format, String HistoryID, String DeviceInfo, ParameterValue[]
> >> > Parameters,
> >> > DataSourceCredentials[] Credentials, String ShowHideToggle, String&
> >> > Encoding,
> >> > String& MimeType, ParameterValue[]& ParametersUsed, Warning[]&
> >> > Warnings,
> >> > String[]& StreamIds) +141
> >> > LAMS.ReportViewer.RenderPDFReport() +618
> >> > LAMS.ReportViewer.Page_Load(Object sender, EventArgs e) +88
> >> > System.Web.UI.Control.OnLoad(EventArgs e) +67
> >> > System.Web.UI.Control.LoadRecursive() +35
> >> > System.Web.UI.Page.ProcessRequestMain() +750"
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> PDF's take significantly longer than HTML but it surprises me that a 2
> >> >> page
> >> >> report you see much of a difference. Do you have any service packs
> >> >> installed. If not, my advice is to install SP2.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> >> >> news:BBA2388F-E062-4368-987A-62CD9298BD14@.microsoft.com...
> >> >> > We have a web app, built in .Net, with reporting services as our
> >> >> > reporting
> >> >> > tool. The problem we are having is when we have a report that goes
> >> >> > to
> >> >> > numerous people and the report is 2 pages long. When we make it 1
> >> >> > page,
> >> >> > legal size, it works fine (avg records are 500-900). When this
> >> >> > report
> >> >> > is
> >> >> > generated it is created in a pdf format. I can run it on report
> >> >> > services
> >> >> > manager and in the viewer it takes approx 10 secs or less to run and
> >> >> > render,
> >> >> > but when we create the pdf it spikes the processor up to 90% and if
> >> >> > it's a
> >> >> > 2
> >> >> > page report it generates an error.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>sql
Report Distribution based on SQL Query in RDL
I need to create multiple reports with each report being sent to the specified user. For example, 3 Managers should each get their own report. A Stored Procedure receives a ManagerID parameter, so I need 3 instances of the report. I also need each report to be sent to its associated Manager:
I set up a rss file to run the report and can run it multiple times, passing in a different ID value each time, but it will not be data driven because I must hard-code the parameters in the ReportParmaters object of the report for each execution.
Is there a way, either by designing the report in VS2005 or on the Reporting Services server, to accomplish what I'm intending to do?
Did you already have a look on the datadriven subscription option in Reporting Services ? That would exactly fit your needs.Jens K. Suessmeyer
http://www.sqlserver2005.de
Monday, March 26, 2012
Report deploment to multiple servers SQL Reporting 2005
Hi
Another issue I would like your help:
We are writing reports for different clients. We need to deploy the same reports to different clients (thus also different DataSources). What is the best way to handle this? The folder names would probably be the same.
Andre
Sounds like you need a custom installer that will accept the required input (database name, credentials, folder path) and deploy the reports by calling down to the Report Server APIs. Or, you can script the objects and use the rs.exe utility to deploy. You may also find the RSScripter utility useful.sqlFriday, March 23, 2012
report by multiple parameter with OR relationship
1.
{Command.LICENSE_NO} = {?License Number} or
{Command.LICENSE_ID} = {?Application Number}
2.
if isnull({?License Number}) = false then
{Command.LICENSE_NO} = {?License Number};
if isnull({?Application Number})=false then
{Command.LICENSE_ID}={?Application Number};
** license_id is the application number(number). license number(string)
but both of them do not work. somehow I can not leave Application number empty.
Please Help!!!As Application Number is a number, Crystal wants you to specify one.
If you default it to 0 you could have a selection formula like
({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})
which means that if you supply a non-zero application number it will be used in preference to any licence id you provide.|||As Application Number is a number, Crystal wants you to specify one.
If you default it to 0 you could have a selection formula like
({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})
which means that if you supply a non-zero application number it will be used in preference to any licence id you provide.
I have tried using 0 as default, but for some reason. I still can not leave application number empty. I used exactly same as your code
({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})
but search will not start if I leave application parameter empty.
any thought?
Thanks for your help|||As Application Number is a number, Crystal wants you to specify one.
If you default it to 0 you could have a selection formula like
({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})
which means that if you supply a non-zero application number it will be used in preference to any licence id you provide.
Got it working with your code!!!
Thanks your so much!!
Wednesday, March 21, 2012
Report Builder TOP1
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Monday, March 12, 2012
Report builder and report models
Hi,
the database my app is using contains information from multiple user organisations, a sort of multi organisation database. I intend using the reprot builder to provide access to a user ine ach organisation. However the issue of who sees what info has popped up and i'm trying to think of a way to restict the user of report builder to only be able to see their own organisations information. This is a social services type application which is used by mutlitple service providers so organisations get twitchy about other organisations being able to see detailed client info. on the application the security stops this but at am a t a loss as to how to best restirct access to an organisation won client data.
any thoughts? is this somehting that can be configured through the reprt model and the report builder user account?
few ideas or advise would eb v helpful,
trhanks
Are you using SQL Server as the back end?
R
|||test. sorry to intrude.|||Repot Models can have security rights assigned to different piece within the model. You can set up Perspectives and assigned users or roles to those perspectives. Or you can assign rights to individual folders. This can be done not through the report model (or the report server), but by opening the Report Model's Properties in SQL Server Management Studio.
Hope this helps.
|||hi,
prev q answer - yes this is sql server 2005.
havn't come across perspectives but sounds v interesting. is that in SSRS or is it in SSAS?
i will check it out
many thanks
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
>
>
report builder - need help reporting from multiple tables
relationship). When I go into report builder, I see both entities
available for me. When I click one of the entities, I am able to select
that entities data items to drag onto my report. However, the other
entity disapears. If I delete all the fields from the report, both
entities are available again. So it appears as if I can have as many
entities defined as I want, but can only use one at a time to report
on. I know I am doing something wrong because I have seen samples that
do what I am looking for - I just can't figure out how to do it.its because you don't have relationship between your 2 tables.
if you have some relation, in this case you can go from table A to table B
"directly"
else you have to go through other shared tables (with unexpected results)
"steve" <stevensherman@.comcast.net> wrote in message
news:1165065154.712904.183760@.f1g2000cwa.googlegroups.com...
>I created a data view and model that has two tables joined (one to many
> relationship). When I go into report builder, I see both entities
> available for me. When I click one of the entities, I am able to select
> that entities data items to drag onto my report. However, the other
> entity disapears. If I delete all the fields from the report, both
> entities are available again. So it appears as if I can have as many
> entities defined as I want, but can only use one at a time to report
> on. I know I am doing something wrong because I have seen samples that
> do what I am looking for - I just can't figure out how to do it.
>|||Thanks for your reply. I do have the relationship defined in the
reporting services model. Do I need to depict it somewhere else or am I
missing setting a property or something?|||Once the ralationships are defined in the datasource view, that is all
it takes. Report builder recognizes the relationship and will let you
select from both tables. If the tables are not joined in the datasource
view, then you will only be able to select attributes from one table or
the other - but not both.
Saturday, February 25, 2012
Report Authoring Using Report Builder
Report against oracle with multiple statements
hello developers!!!!!
How I have to do a Report against oracle with multiple statements?
I need execute multiple SQL statements in the generic query designer with an oracle connection.
thanks
Maybe I'm missing something, but couldn't you just define multiple datasets in the report with one query per dataset?
If the queries have interdependencies, you could look into moving them into a stored procedure and calling the stored procedure from SSRS. You should find several threads on the regular SSRS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1) discussing how to call Oracle stored procedures from SSRS.
-- Robert
|||
Robert -- slightly off topic but do you have any idea why the Query Designer would be really slow loading tables from Oracle?
There are two threads on this subject:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019689&SiteID=1
...maybe this deserved looking at for a fix in Katmai if it's not already done... is there an issue with multiple connections for each table that is being added or something?
Thanks in advance,
>L<
|||The query designer is actually a VS component, not an RS component. It might use the OLE DB provider but I'm not 100% certain.|||
Thanks Brian. I might have known that the query designer was a shared component, but I haven't heard of anybody complaining about its speed except in this (report design) context.
As far as whether the query designer uses OLE DB or not... it can, and does, and when it does it is apparently fast -- against the same data.
It's just when these guys are accessing Oracle and their specified connection to this data source is *not* an OLE DB connection that they see the problem. (10-15 minutes to add a table in the designer ?!? Yuck...) I was thinking maybe the designer was trying to make a separate connection every time they pressed that "Add" button.
>L<
|||
Well, I know this won't really resolve the issue, but the shared VS query designer component is actually only supported for a particular set of OLEDB providers, as described in the following KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811241
? Microsoft OLE DB Provider for SQL Server
? Microsoft Jet 4.0 OLE DB Provider
? Microsoft OLE DB Provider for Oracle
It is not supported for any other providers.
-- Robert
|||Thanks for going the extra mile, Robert <s>. That totally makes sense. I will refer them to this post and stop worrying that the query designer has a bug <g>.
>L<
Report against oracle with multiple statements
hello developers!!!!!
How I have to do a Report against oracle with multiple statements?
I need execute multiple SQL statements in the generic query designer with an oracle connection.
thanks
Maybe I'm missing something, but couldn't you just define multiple datasets in the report with one query per dataset?
If the queries have interdependencies, you could look into moving them into a stored procedure and calling the stored procedure from SSRS. You should find several threads on the regular SSRS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1) discussing how to call Oracle stored procedures from SSRS.
-- Robert
|||Robert -- slightly off topic but do you have any idea why the Query Designer would be really slow loading tables from Oracle?
There are two threads on this subject:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019689&SiteID=1
...maybe this deserved looking at for a fix in Katmai if it's not already done... is there an issue with multiple connections for each table that is being added or something?
Thanks in advance,
>L<
|||The query designer is actually a VS component, not an RS component. It might use the OLE DB provider but I'm not 100% certain.|||
Thanks Brian. I might have known that the query designer was a shared component, but I haven't heard of anybody complaining about its speed except in this (report design) context.
As far as whether the query designer uses OLE DB or not... it can, and does, and when it does it is apparently fast -- against the same data.
It's just when these guys are accessing Oracle and their specified connection to this data source is *not* an OLE DB connection that they see the problem. (10-15 minutes to add a table in the designer ?!? Yuck...) I was thinking maybe the designer was trying to make a separate connection every time they pressed that "Add" button.
>L<
|||Well, I know this won't really resolve the issue, but the shared VS query designer component is actually only supported for a particular set of OLEDB providers, as described in the following KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811241
? Microsoft OLE DB Provider for SQL Server
? Microsoft Jet 4.0 OLE DB Provider
? Microsoft OLE DB Provider for Oracle
It is not supported for any other providers.
-- Robert
|||Thanks for going the extra mile, Robert <s>. That totally makes sense. I will refer them to this post and stop worrying that the query designer has a bug <g>.
>L<
Report against oracle with multiple statements
hello developers!!!!!
How I have to do a Report against oracle with multiple statements?
I need execute multiple SQL statements in the generic query designer with an oracle connection.
thanks
Maybe I'm missing something, but couldn't you just define multiple datasets in the report with one query per dataset?
If the queries have interdependencies, you could look into moving them into a stored procedure and calling the stored procedure from SSRS. You should find several threads on the regular SSRS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1) discussing how to call Oracle stored procedures from SSRS.
-- Robert
|||Robert -- slightly off topic but do you have any idea why the Query Designer would be really slow loading tables from Oracle?
There are two threads on this subject:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019689&SiteID=1
...maybe this deserved looking at for a fix in Katmai if it's not already done... is there an issue with multiple connections for each table that is being added or something?
Thanks in advance,
>L<
|||The query designer is actually a VS component, not an RS component. It might use the OLE DB provider but I'm not 100% certain.|||
Thanks Brian. I might have known that the query designer was a shared component, but I haven't heard of anybody complaining about its speed except in this (report design) context.
As far as whether the query designer uses OLE DB or not... it can, and does, and when it does it is apparently fast -- against the same data.
It's just when these guys are accessing Oracle and their specified connection to this data source is *not* an OLE DB connection that they see the problem. (10-15 minutes to add a table in the designer ?!? Yuck...) I was thinking maybe the designer was trying to make a separate connection every time they pressed that "Add" button.
>L<
|||Well, I know this won't really resolve the issue, but the shared VS query designer component is actually only supported for a particular set of OLEDB providers, as described in the following KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811241
? Microsoft OLE DB Provider for SQL Server
? Microsoft Jet 4.0 OLE DB Provider
? Microsoft OLE DB Provider for Oracle
It is not supported for any other providers.
-- Robert
|||Thanks for going the extra mile, Robert <s>. That totally makes sense. I will refer them to this post and stop worrying that the query designer has a bug <g>.
>L<
Report against oracle with multiple statements
hello developers!!!!!
How I have to do a Report against oracle with multiple statements?
I need execute multiple SQL statements in the generic query designer with an oracle connection.
thanks
Maybe I'm missing something, but couldn't you just define multiple datasets in the report with one query per dataset?
If the queries have interdependencies, you could look into moving them into a stored procedure and calling the stored procedure from SSRS. You should find several threads on the regular SSRS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1) discussing how to call Oracle stored procedures from SSRS.
-- Robert
|||
Robert -- slightly off topic but do you have any idea why the Query Designer would be really slow loading tables from Oracle?
There are two threads on this subject:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019689&SiteID=1
...maybe this deserved looking at for a fix in Katmai if it's not already done... is there an issue with multiple connections for each table that is being added or something?
Thanks in advance,
>L<
|||The query designer is actually a VS component, not an RS component. It might use the OLE DB provider but I'm not 100% certain.|||
Thanks Brian. I might have known that the query designer was a shared component, but I haven't heard of anybody complaining about its speed except in this (report design) context.
As far as whether the query designer uses OLE DB or not... it can, and does, and when it does it is apparently fast -- against the same data.
It's just when these guys are accessing Oracle and their specified connection to this data source is *not* an OLE DB connection that they see the problem. (10-15 minutes to add a table in the designer ?!? Yuck...) I was thinking maybe the designer was trying to make a separate connection every time they pressed that "Add" button.
>L<
|||
Well, I know this won't really resolve the issue, but the shared VS query designer component is actually only supported for a particular set of OLEDB providers, as described in the following KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811241
? Microsoft OLE DB Provider for SQL Server
? Microsoft Jet 4.0 OLE DB Provider
? Microsoft OLE DB Provider for Oracle
It is not supported for any other providers.
-- Robert
|||Thanks for going the extra mile, Robert <s>. That totally makes sense. I will refer them to this post and stop worrying that the query designer has a bug <g>.
>L<
Report across multiple db
For example - I have a customer DB that has social security #'s on Server1
DB-abc and I want to link to another server Server2 DB-efg - and create a
report with data elements from both DB's.yes, create a Linked Server to the second DB Server. After that you have the
possibility to create a view getting the data from both DB's. build the
report on this view and you are done.
hope this helps.
Regards, Rene
"Joe" schrieb:
> Can I use BIDS to create a report that will cross other DB's and servers?
> For example - I have a customer DB that has social security #'s on Server1
> DB-abc and I want to link to another server Server2 DB-efg - and create a
> report with data elements from both DB's.
>
>|||Just a heads up. Be very very careful with linked tables. If you do a
heterogenous join you will be pulling a massive amount of data locally. With
SQL 2000 it would do this sometimes even when the query was all on a single
remote database as I found out when I went against a large table. SQL 2005
is better at this but you will still see issues with heterogenous joins.
Linked tables are easy to use but dangerous.
One suggestion, do not use four part naming. Use openquery instead. Limit
the data as much as possible bringing it into a temp table and then do your
join there.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rene Fehr" <ReneFehr@.discussions.microsoft.com> wrote in message
news:7242A398-BC81-4DB3-A946-0A4DA7567C2E@.microsoft.com...
> yes, create a Linked Server to the second DB Server. After that you have
> the
> possibility to create a view getting the data from both DB's. build the
> report on this view and you are done.
> hope this helps.
> Regards, Rene
> "Joe" schrieb:
>> Can I use BIDS to create a report that will cross other DB's and servers?
>> For example - I have a customer DB that has social security #'s on
>> Server1
>> DB-abc and I want to link to another server Server2 DB-efg - and create a
>> report with data elements from both DB's.
>>