I have a set of data tables with their own datasets from different database
tables. They go across the report page and all need to be linked together
via an ID number. How can this be achieved or is there a better way of doing
it other than in report tables?
The data tables across the report page are:
Details, Actuals and Budgets ie., ID number in Details need to be linked to
the same ID in Actuals and Budgets so that it appears as a single line of
continuous information across the page.
Thanks
IanStep 1. Create one report for each of the Details, Actuals, Budget data.
Give each report an ID parameter.
Step 2. Create a main report and add a list data region. Make the dataset of
the list be based on a query that returns unique id values. Inside the list
data region add subreport controls and map them each to the reports you
created at Step 1. You will need to map the subreport parameters to the ID
field in the main report.
Done.
HTH
Charles Kangai, MCT, MCDBA
"IanP" wrote:
> I have a set of data tables with their own datasets from different database
> tables. They go across the report page and all need to be linked together
> via an ID number. How can this be achieved or is there a better way of doing
> it other than in report tables?
>
> The data tables across the report page are:
>
> Details, Actuals and Budgets ie., ID number in Details need to be linked to
> the same ID in Actuals and Budgets so that it appears as a single line of
> continuous information across the page.
>
> Thanks
> Ian
>|||This sounds more like a query problem than a report problem. Is there a
reason that you are not joining the tables in the source query? Then each
record returned will contain all your information -- details, actuals, and
budgets. When you say single line of data, I presume that there is only one
row of data for each ID. Is that not the case?
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"IanP" <IanP@.discussions.microsoft.com> wrote in message
news:9F30A2FE-A28F-45E6-989C-913EB78FD61A@.microsoft.com...
>I have a set of data tables with their own datasets from different database
> tables. They go across the report page and all need to be linked together
> via an ID number. How can this be achieved or is there a better way of
> doing
> it other than in report tables?
>
> The data tables across the report page are:
>
> Details, Actuals and Budgets ie., ID number in Details need to be linked
> to
> the same ID in Actuals and Budgets so that it appears as a single line of
> continuous information across the page.
>
> Thanks
> Ian
>|||Thank you Charles and Jeff
Yes jeff it is a db design problem, I was not involved in its design, and
now it is being revisited and Jeff, your note sounds promising so I will go
back and revisit what I was experimenting with
Ian
"Jeff A. Stucker" wrote:
> This sounds more like a query problem than a report problem. Is there a
> reason that you are not joining the tables in the source query? Then each
> record returned will contain all your information -- details, actuals, and
> budgets. When you say single line of data, I presume that there is only one
> row of data for each ID. Is that not the case?
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "IanP" <IanP@.discussions.microsoft.com> wrote in message
> news:9F30A2FE-A28F-45E6-989C-913EB78FD61A@.microsoft.com...
> >I have a set of data tables with their own datasets from different database
> > tables. They go across the report page and all need to be linked together
> > via an ID number. How can this be achieved or is there a better way of
> > doing
> > it other than in report tables?
> >
> >
> > The data tables across the report page are:
> >
> >
> > Details, Actuals and Budgets ie., ID number in Details need to be linked
> > to
> > the same ID in Actuals and Budgets so that it appears as a single line of
> > continuous information across the page.
> >
> >
> > Thanks
> >
> > Ian
> >
>
>
Showing posts with label across. Show all posts
Showing posts with label across. Show all posts
Wednesday, March 28, 2012
Tuesday, March 20, 2012
Report Builder Error When Running Report
Hello everyone,
I've come across an unusual error on some of our machines. I'm trying to track down why only certain ones will give an error. I thought it might have something to do with .NET 2.0 not being installed, but I assume that they wouldn't even be able to launch Report Builder if this is the case. I'm at the point now where I've been told these machines have .NET 2.0. I plan on trying to verify soon, but I thought I'd get everyone's opinion on what it could be.
With even the simplist report (just 1 field dragged onto the report) the following error appears: "Report execution error The report might not be valid or the server could not process the data." When I clicked on the details, the following appears: "The numeric portion of 0pt cannot be parsed as a unit of type Point. -- The type initializer for 'Microsoft.ReportingServices.Design.Constants' threw an exception."
This model was created with Business Intelligence Development Studio and we're using SQL 2005 Enterprise.
Anyone have any thoughts on what this could be or how I can better troubleshoot it?
Thanks,
Dan
Alright, I think I've narrowed the problem down, although I still don't have a viable solution.
I had a similar problem with Analysis Services. The user could not browse a cube or dimension. We ended up solving this by recreating the user's profile.
Using that knowledge, I was working with someone from our desktop group today. We had the user who was having this unusual error try logging in on another machine. When she did this, the Report Builder worked perfectly. I am not personally well versed on how anything with profiles work, but it appears that the new profile that was put on the the new machine was different than the older one, and everything worked.
At this point I'm still stuck not knowing how to troubleshoot it any further. The desktop group is going to try to look at the profiles and determine what the differences might be, but I don't know how well that will work.
Re-creating everyone's profiles is not going to work (especially for what could be 100 users) and this will definately lose some of our user support. I understand this might not be the type of question for this group, but If anyone has come across a similar situation like this, I would appreciate some insight.
Regards,
Dan
|||Hi Dan,
can you send an RDL that causes the problem?
In Report Builder, create report, go to File -> Save to File...
almineev atatat microsoft dotdot com
|||I'm fairly confident it doesn't have anything to do with the RDL/Report itself, but doing this allowed me to get some more details about what is happening.
I’m having trouble saving the report on that user’s computer.I have tried saving to a file and saving to the My Reports folder (this user has admin privs on the report server).It gives this error: Operation failed An error occurred while processing your request. Save your report and restart the application. The contents of the details section is the same as the first message I posted.
We tried one more thing.We attempted to close the report builder app (using the X on the toolbar) and it prompted to save.We clicked yes and we got a much more detailed stack trace.I have included this below. I excluded the assembly information.
Dan
************** Exception Text **************
System.TypeInitializationException: The type initializer for 'Microsoft.ReportingServices.Design.Constants' threw an exception. > System.FormatException: The numeric portion of 0pt cannot be parsed as a unit of type Point.
at Microsoft.ReportingServices.Design.RdlModel.Unit.Init(String value, CultureInfo culture, UnitType defaultType)
at Microsoft.ReportingServices.Design.RdlModel.Unit..ctor(String value, CultureInfo culture, UnitType defaultType)
at Microsoft.ReportingServices.Design.RdlModel.StyleUnit..ctor(String exprStr)
at Microsoft.ReportingServices.Design.Constants..cctor()
End of inner exception stack trace
at Microsoft.ReportingServices.Design.RdlModel.Margins.set_Top(Unit value)
at Microsoft.ReportingServices.Design.RdlModel.Margins..ctor()
at Microsoft.ReportingServices.Design.RdlModel.Report..ctor()
at Microsoft.ReportingServices.ReportBuilder.ReportModel.Report.WriteTo(TextWriter w)
at Microsoft.ReportingServices.ReportBuilder.MainForm.GetReportRdlBytes()
at Microsoft.ReportingServices.ReportBuilder.MainForm.SaveReportToServer(String reportPath)
at Microsoft.ReportingServices.ReportBuilder.MainForm.SaveReportToServer(Boolean doSaveAs)
at Microsoft.ReportingServices.ReportBuilder.MainForm.CanDiscardChanges()
at Microsoft.ReportingServices.ReportBuilder.MainForm.OnClosing(CancelEventArgs e)
at System.Windows.Forms.Form.WmClose(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
Wednesday, March 7, 2012
Report Builder - Entities list behaving poorly.
While attempting to create a new report, I came across what I feel is some
odd behavior. I select an item in the Entities list, then drag an item from
the newly populated Fields list to the report area. Once I drop the field
item, the Entities list changes scope showing only the Entity from which I
made a selection and any items within it. I can't get back up to the main
list unless I undo the operation.
Being relatively new to this domain, I considered that I might be expecting
behavior that I shouldn't be, however, when I checked the tutorial provided,
the instructions led me to believe I was right in being perplexed, thus this
post.
Anyone have info?
Thank you,
Scottits a normal behavior.
The model only present entities which can be used regarding what you
currently have in the report.
of your entities have no links between them, there are not available to the
user.
This insure that the user will not select unrelated entities and generates
bad queries. (like full outer join queries)
You have to create some roles between your entities to allow the user to go
from 1 entity to another.
"ScottT" <ScottT@.discussions.microsoft.com> wrote in message
news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
> While attempting to create a new report, I came across what I feel is some
> odd behavior. I select an item in the Entities list, then drag an item
> from
> the newly populated Fields list to the report area. Once I drop the field
> item, the Entities list changes scope showing only the Entity from which I
> made a selection and any items within it. I can't get back up to the main
> list unless I undo the operation.
> Being relatively new to this domain, I considered that I might be
> expecting
> behavior that I shouldn't be, however, when I checked the tutorial
> provided,
> the instructions led me to believe I was right in being perplexed, thus
> this
> post.
> Anyone have info?
> Thank you,
> Scott
>|||I'm not sure if I'd call it "normal".
From my initial investigations there has to actually be a foreign key on the
tables in SQL Server.
If this is the case and you can't have "logical" relationships between
entities, than this will make things very difficult for a lot of people.
It will make report builder unusable for databases with complex
relationships between entities and legacy systems.
You should be able to have no actual foreign keys if you don't want to
(however bad practice this is).
"Jéjé" wrote:
> its a normal behavior.
> The model only present entities which can be used regarding what you
> currently have in the report.
> of your entities have no links between them, there are not available to the
> user.
> This insure that the user will not select unrelated entities and generates
> bad queries. (like full outer join queries)
> You have to create some roles between your entities to allow the user to go
> from 1 entity to another.
> "ScottT" <ScottT@.discussions.microsoft.com> wrote in message
> news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
> >
> > While attempting to create a new report, I came across what I feel is some
> > odd behavior. I select an item in the Entities list, then drag an item
> > from
> > the newly populated Fields list to the report area. Once I drop the field
> > item, the Entities list changes scope showing only the Entity from which I
> > made a selection and any items within it. I can't get back up to the main
> > list unless I undo the operation.
> >
> > Being relatively new to this domain, I considered that I might be
> > expecting
> > behavior that I shouldn't be, however, when I checked the tutorial
> > provided,
> > the instructions led me to believe I was right in being perplexed, thus
> > this
> > post.
> >
> > Anyone have info?
> >
> > Thank you,
> > Scott
> >
>
>|||Reportbuilder is not designed for complex schemas!
the system doesnt support complex relationships and other business rules.
Reportbuilder is (more) designed to give access to a well formatted
database, I mean a database with a "beautyfull" schema (good and simple
relationship).
Also, Reportbuilder is designed to be very intuitive for the end user. so
when the user start to play with the customer name, reportbuilder will
display only customer's related information because the MUST go through
these known entities to continue his report.
A user can't select a customer name and a product name as is, the user MUST
select the customer name, then the sales then the product, because the
question is "what are the product sales to my customers?"
the job you have to do is to create this easy to use reportbuilder model in
front of your database to insure that an end user can use your database
without any SQL knowledge.
if your database is too complex, you have to work hard to generate a report
builder model.
"James" <James@.discussions.microsoft.com> wrote in message
news:16CD66FF-16CA-44F6-9E50-496026C88464@.microsoft.com...
> I'm not sure if I'd call it "normal".
> From my initial investigations there has to actually be a foreign key on
> the
> tables in SQL Server.
> If this is the case and you can't have "logical" relationships between
> entities, than this will make things very difficult for a lot of people.
> It will make report builder unusable for databases with complex
> relationships between entities and legacy systems.
> You should be able to have no actual foreign keys if you don't want to
> (however bad practice this is).
> "Jéjé" wrote:
>> its a normal behavior.
>> The model only present entities which can be used regarding what you
>> currently have in the report.
>> of your entities have no links between them, there are not available to
>> the
>> user.
>> This insure that the user will not select unrelated entities and
>> generates
>> bad queries. (like full outer join queries)
>> You have to create some roles between your entities to allow the user to
>> go
>> from 1 entity to another.
>> "ScottT" <ScottT@.discussions.microsoft.com> wrote in message
>> news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
>> >
>> > While attempting to create a new report, I came across what I feel is
>> > some
>> > odd behavior. I select an item in the Entities list, then drag an item
>> > from
>> > the newly populated Fields list to the report area. Once I drop the
>> > field
>> > item, the Entities list changes scope showing only the Entity from
>> > which I
>> > made a selection and any items within it. I can't get back up to the
>> > main
>> > list unless I undo the operation.
>> >
>> > Being relatively new to this domain, I considered that I might be
>> > expecting
>> > behavior that I shouldn't be, however, when I checked the tutorial
>> > provided,
>> > the instructions led me to believe I was right in being perplexed, thus
>> > this
>> > post.
>> >
>> > Anyone have info?
>> >
>> > Thank you,
>> > Scott
>> >
>>|||I agree with what you're saying about the reportbuilder being a simple tool
for users without much or any knowledge of SQL or the particular database.
The bit I don't understand is why we are so limited in what we can do when
building the model.
This is a layer of abstraction and so it shouldn't matter if the actual
database is complicated underneath as long as the final model you present the
user with is straight forward.
Whats the point of having the layer of abstraction if everything has to be
in the database, you might as well report directly from the database.
As far as you know, is there definitely no way to say 2 tables are related
in your model that don't have an actual foreign key in SQL Server?
"Jéjé" wrote:
> Reportbuilder is not designed for complex schemas!
> the system doesnt support complex relationships and other business rules.
> Reportbuilder is (more) designed to give access to a well formatted
> database, I mean a database with a "beautyfull" schema (good and simple
> relationship).
> Also, Reportbuilder is designed to be very intuitive for the end user. so
> when the user start to play with the customer name, reportbuilder will
> display only customer's related information because the MUST go through
> these known entities to continue his report.
> A user can't select a customer name and a product name as is, the user MUST
> select the customer name, then the sales then the product, because the
> question is "what are the product sales to my customers?"
> the job you have to do is to create this easy to use reportbuilder model in
> front of your database to insure that an end user can use your database
> without any SQL knowledge.
> if your database is too complex, you have to work hard to generate a report
> builder model.
>
> "James" <James@.discussions.microsoft.com> wrote in message
> news:16CD66FF-16CA-44F6-9E50-496026C88464@.microsoft.com...
> > I'm not sure if I'd call it "normal".
> > From my initial investigations there has to actually be a foreign key on
> > the
> > tables in SQL Server.
> > If this is the case and you can't have "logical" relationships between
> > entities, than this will make things very difficult for a lot of people.
> > It will make report builder unusable for databases with complex
> > relationships between entities and legacy systems.
> > You should be able to have no actual foreign keys if you don't want to
> > (however bad practice this is).
> >
> > "Jéjé" wrote:
> >
> >> its a normal behavior.
> >> The model only present entities which can be used regarding what you
> >> currently have in the report.
> >> of your entities have no links between them, there are not available to
> >> the
> >> user.
> >> This insure that the user will not select unrelated entities and
> >> generates
> >> bad queries. (like full outer join queries)
> >>
> >> You have to create some roles between your entities to allow the user to
> >> go
> >> from 1 entity to another.
> >>
> >> "ScottT" <ScottT@.discussions.microsoft.com> wrote in message
> >> news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
> >> >
> >> > While attempting to create a new report, I came across what I feel is
> >> > some
> >> > odd behavior. I select an item in the Entities list, then drag an item
> >> > from
> >> > the newly populated Fields list to the report area. Once I drop the
> >> > field
> >> > item, the Entities list changes scope showing only the Entity from
> >> > which I
> >> > made a selection and any items within it. I can't get back up to the
> >> > main
> >> > list unless I undo the operation.
> >> >
> >> > Being relatively new to this domain, I considered that I might be
> >> > expecting
> >> > behavior that I shouldn't be, however, when I checked the tutorial
> >> > provided,
> >> > the instructions led me to believe I was right in being perplexed, thus
> >> > this
> >> > post.
> >> >
> >> > Anyone have info?
> >> >
> >> > Thank you,
> >> > Scott
> >> >
> >>
> >>
> >>
>
>|||there is an abstraction layer, the DSV.
in the DSV you can create links that doesn't exists in the database, you can
add a layer of queries and other calculated columns etc...
So if there is no foreign key in the database, you can create a logical
relationship in the DSV.
you can use unrelated tables in the model.
but if there is no links, then the user can't use these 2 tables at the same
time in report builder.
"James" <James@.discussions.microsoft.com> wrote in message
news:6879B37B-A3DC-4871-B254-80BC0B93648D@.microsoft.com...
>I agree with what you're saying about the reportbuilder being a simple tool
> for users without much or any knowledge of SQL or the particular database.
> The bit I don't understand is why we are so limited in what we can do when
> building the model.
> This is a layer of abstraction and so it shouldn't matter if the actual
> database is complicated underneath as long as the final model you present
> the
> user with is straight forward.
> Whats the point of having the layer of abstraction if everything has to be
> in the database, you might as well report directly from the database.
> As far as you know, is there definitely no way to say 2 tables are related
> in your model that don't have an actual foreign key in SQL Server?
> "Jéjé" wrote:
>> Reportbuilder is not designed for complex schemas!
>> the system doesnt support complex relationships and other business rules.
>> Reportbuilder is (more) designed to give access to a well formatted
>> database, I mean a database with a "beautyfull" schema (good and simple
>> relationship).
>> Also, Reportbuilder is designed to be very intuitive for the end user. so
>> when the user start to play with the customer name, reportbuilder will
>> display only customer's related information because the MUST go through
>> these known entities to continue his report.
>> A user can't select a customer name and a product name as is, the user
>> MUST
>> select the customer name, then the sales then the product, because the
>> question is "what are the product sales to my customers?"
>> the job you have to do is to create this easy to use reportbuilder model
>> in
>> front of your database to insure that an end user can use your database
>> without any SQL knowledge.
>> if your database is too complex, you have to work hard to generate a
>> report
>> builder model.
>>
>> "James" <James@.discussions.microsoft.com> wrote in message
>> news:16CD66FF-16CA-44F6-9E50-496026C88464@.microsoft.com...
>> > I'm not sure if I'd call it "normal".
>> > From my initial investigations there has to actually be a foreign key
>> > on
>> > the
>> > tables in SQL Server.
>> > If this is the case and you can't have "logical" relationships between
>> > entities, than this will make things very difficult for a lot of
>> > people.
>> > It will make report builder unusable for databases with complex
>> > relationships between entities and legacy systems.
>> > You should be able to have no actual foreign keys if you don't want to
>> > (however bad practice this is).
>> >
>> > "Jéjé" wrote:
>> >
>> >> its a normal behavior.
>> >> The model only present entities which can be used regarding what you
>> >> currently have in the report.
>> >> of your entities have no links between them, there are not available
>> >> to
>> >> the
>> >> user.
>> >> This insure that the user will not select unrelated entities and
>> >> generates
>> >> bad queries. (like full outer join queries)
>> >>
>> >> You have to create some roles between your entities to allow the user
>> >> to
>> >> go
>> >> from 1 entity to another.
>> >>
>> >> "ScottT" <ScottT@.discussions.microsoft.com> wrote in message
>> >> news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
>> >> >
>> >> > While attempting to create a new report, I came across what I feel
>> >> > is
>> >> > some
>> >> > odd behavior. I select an item in the Entities list, then drag an
>> >> > item
>> >> > from
>> >> > the newly populated Fields list to the report area. Once I drop the
>> >> > field
>> >> > item, the Entities list changes scope showing only the Entity from
>> >> > which I
>> >> > made a selection and any items within it. I can't get back up to
>> >> > the
>> >> > main
>> >> > list unless I undo the operation.
>> >> >
>> >> > Being relatively new to this domain, I considered that I might be
>> >> > expecting
>> >> > behavior that I shouldn't be, however, when I checked the tutorial
>> >> > provided,
>> >> > the instructions led me to believe I was right in being perplexed,
>> >> > thus
>> >> > this
>> >> > post.
>> >> >
>> >> > Anyone have info?
>> >> >
>> >> > Thank you,
>> >> > Scott
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks,
I was trying to do it through the designer view of the report model.
I didn't realise I wanted to be in the designer view of the Data Source View
instead.
"Jéjé" wrote:
> there is an abstraction layer, the DSV.
> in the DSV you can create links that doesn't exists in the database, you can
> add a layer of queries and other calculated columns etc...
> So if there is no foreign key in the database, you can create a logical
> relationship in the DSV.
> you can use unrelated tables in the model.
> but if there is no links, then the user can't use these 2 tables at the same
> time in report builder.
>
> "James" <James@.discussions.microsoft.com> wrote in message
> news:6879B37B-A3DC-4871-B254-80BC0B93648D@.microsoft.com...
> >I agree with what you're saying about the reportbuilder being a simple tool
> > for users without much or any knowledge of SQL or the particular database.
> >
> > The bit I don't understand is why we are so limited in what we can do when
> > building the model.
> > This is a layer of abstraction and so it shouldn't matter if the actual
> > database is complicated underneath as long as the final model you present
> > the
> > user with is straight forward.
> > Whats the point of having the layer of abstraction if everything has to be
> > in the database, you might as well report directly from the database.
> >
> > As far as you know, is there definitely no way to say 2 tables are related
> > in your model that don't have an actual foreign key in SQL Server?
> >
> > "Jéjé" wrote:
> >
> >> Reportbuilder is not designed for complex schemas!
> >> the system doesnt support complex relationships and other business rules.
> >>
> >> Reportbuilder is (more) designed to give access to a well formatted
> >> database, I mean a database with a "beautyfull" schema (good and simple
> >> relationship).
> >>
> >> Also, Reportbuilder is designed to be very intuitive for the end user. so
> >> when the user start to play with the customer name, reportbuilder will
> >> display only customer's related information because the MUST go through
> >> these known entities to continue his report.
> >> A user can't select a customer name and a product name as is, the user
> >> MUST
> >> select the customer name, then the sales then the product, because the
> >> question is "what are the product sales to my customers?"
> >>
> >> the job you have to do is to create this easy to use reportbuilder model
> >> in
> >> front of your database to insure that an end user can use your database
> >> without any SQL knowledge.
> >> if your database is too complex, you have to work hard to generate a
> >> report
> >> builder model.
> >>
> >>
> >> "James" <James@.discussions.microsoft.com> wrote in message
> >> news:16CD66FF-16CA-44F6-9E50-496026C88464@.microsoft.com...
> >> > I'm not sure if I'd call it "normal".
> >> > From my initial investigations there has to actually be a foreign key
> >> > on
> >> > the
> >> > tables in SQL Server.
> >> > If this is the case and you can't have "logical" relationships between
> >> > entities, than this will make things very difficult for a lot of
> >> > people.
> >> > It will make report builder unusable for databases with complex
> >> > relationships between entities and legacy systems.
> >> > You should be able to have no actual foreign keys if you don't want to
> >> > (however bad practice this is).
> >> >
> >> > "Jéjé" wrote:
> >> >
> >> >> its a normal behavior.
> >> >> The model only present entities which can be used regarding what you
> >> >> currently have in the report.
> >> >> of your entities have no links between them, there are not available
> >> >> to
> >> >> the
> >> >> user.
> >> >> This insure that the user will not select unrelated entities and
> >> >> generates
> >> >> bad queries. (like full outer join queries)
> >> >>
> >> >> You have to create some roles between your entities to allow the user
> >> >> to
> >> >> go
> >> >> from 1 entity to another.
> >> >>
> >> >> "ScottT" <ScottT@.discussions.microsoft.com> wrote in message
> >> >> news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
> >> >> >
> >> >> > While attempting to create a new report, I came across what I feel
> >> >> > is
> >> >> > some
> >> >> > odd behavior. I select an item in the Entities list, then drag an
> >> >> > item
> >> >> > from
> >> >> > the newly populated Fields list to the report area. Once I drop the
> >> >> > field
> >> >> > item, the Entities list changes scope showing only the Entity from
> >> >> > which I
> >> >> > made a selection and any items within it. I can't get back up to
> >> >> > the
> >> >> > main
> >> >> > list unless I undo the operation.
> >> >> >
> >> >> > Being relatively new to this domain, I considered that I might be
> >> >> > expecting
> >> >> > behavior that I shouldn't be, however, when I checked the tutorial
> >> >> > provided,
> >> >> > the instructions led me to believe I was right in being perplexed,
> >> >> > thus
> >> >> > this
> >> >> > post.
> >> >> >
> >> >> > Anyone have info?
> >> >> >
> >> >> > Thank you,
> >> >> > Scott
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
odd behavior. I select an item in the Entities list, then drag an item from
the newly populated Fields list to the report area. Once I drop the field
item, the Entities list changes scope showing only the Entity from which I
made a selection and any items within it. I can't get back up to the main
list unless I undo the operation.
Being relatively new to this domain, I considered that I might be expecting
behavior that I shouldn't be, however, when I checked the tutorial provided,
the instructions led me to believe I was right in being perplexed, thus this
post.
Anyone have info?
Thank you,
Scottits a normal behavior.
The model only present entities which can be used regarding what you
currently have in the report.
of your entities have no links between them, there are not available to the
user.
This insure that the user will not select unrelated entities and generates
bad queries. (like full outer join queries)
You have to create some roles between your entities to allow the user to go
from 1 entity to another.
"ScottT" <ScottT@.discussions.microsoft.com> wrote in message
news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
> While attempting to create a new report, I came across what I feel is some
> odd behavior. I select an item in the Entities list, then drag an item
> from
> the newly populated Fields list to the report area. Once I drop the field
> item, the Entities list changes scope showing only the Entity from which I
> made a selection and any items within it. I can't get back up to the main
> list unless I undo the operation.
> Being relatively new to this domain, I considered that I might be
> expecting
> behavior that I shouldn't be, however, when I checked the tutorial
> provided,
> the instructions led me to believe I was right in being perplexed, thus
> this
> post.
> Anyone have info?
> Thank you,
> Scott
>|||I'm not sure if I'd call it "normal".
From my initial investigations there has to actually be a foreign key on the
tables in SQL Server.
If this is the case and you can't have "logical" relationships between
entities, than this will make things very difficult for a lot of people.
It will make report builder unusable for databases with complex
relationships between entities and legacy systems.
You should be able to have no actual foreign keys if you don't want to
(however bad practice this is).
"Jéjé" wrote:
> its a normal behavior.
> The model only present entities which can be used regarding what you
> currently have in the report.
> of your entities have no links between them, there are not available to the
> user.
> This insure that the user will not select unrelated entities and generates
> bad queries. (like full outer join queries)
> You have to create some roles between your entities to allow the user to go
> from 1 entity to another.
> "ScottT" <ScottT@.discussions.microsoft.com> wrote in message
> news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
> >
> > While attempting to create a new report, I came across what I feel is some
> > odd behavior. I select an item in the Entities list, then drag an item
> > from
> > the newly populated Fields list to the report area. Once I drop the field
> > item, the Entities list changes scope showing only the Entity from which I
> > made a selection and any items within it. I can't get back up to the main
> > list unless I undo the operation.
> >
> > Being relatively new to this domain, I considered that I might be
> > expecting
> > behavior that I shouldn't be, however, when I checked the tutorial
> > provided,
> > the instructions led me to believe I was right in being perplexed, thus
> > this
> > post.
> >
> > Anyone have info?
> >
> > Thank you,
> > Scott
> >
>
>|||Reportbuilder is not designed for complex schemas!
the system doesnt support complex relationships and other business rules.
Reportbuilder is (more) designed to give access to a well formatted
database, I mean a database with a "beautyfull" schema (good and simple
relationship).
Also, Reportbuilder is designed to be very intuitive for the end user. so
when the user start to play with the customer name, reportbuilder will
display only customer's related information because the MUST go through
these known entities to continue his report.
A user can't select a customer name and a product name as is, the user MUST
select the customer name, then the sales then the product, because the
question is "what are the product sales to my customers?"
the job you have to do is to create this easy to use reportbuilder model in
front of your database to insure that an end user can use your database
without any SQL knowledge.
if your database is too complex, you have to work hard to generate a report
builder model.
"James" <James@.discussions.microsoft.com> wrote in message
news:16CD66FF-16CA-44F6-9E50-496026C88464@.microsoft.com...
> I'm not sure if I'd call it "normal".
> From my initial investigations there has to actually be a foreign key on
> the
> tables in SQL Server.
> If this is the case and you can't have "logical" relationships between
> entities, than this will make things very difficult for a lot of people.
> It will make report builder unusable for databases with complex
> relationships between entities and legacy systems.
> You should be able to have no actual foreign keys if you don't want to
> (however bad practice this is).
> "Jéjé" wrote:
>> its a normal behavior.
>> The model only present entities which can be used regarding what you
>> currently have in the report.
>> of your entities have no links between them, there are not available to
>> the
>> user.
>> This insure that the user will not select unrelated entities and
>> generates
>> bad queries. (like full outer join queries)
>> You have to create some roles between your entities to allow the user to
>> go
>> from 1 entity to another.
>> "ScottT" <ScottT@.discussions.microsoft.com> wrote in message
>> news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
>> >
>> > While attempting to create a new report, I came across what I feel is
>> > some
>> > odd behavior. I select an item in the Entities list, then drag an item
>> > from
>> > the newly populated Fields list to the report area. Once I drop the
>> > field
>> > item, the Entities list changes scope showing only the Entity from
>> > which I
>> > made a selection and any items within it. I can't get back up to the
>> > main
>> > list unless I undo the operation.
>> >
>> > Being relatively new to this domain, I considered that I might be
>> > expecting
>> > behavior that I shouldn't be, however, when I checked the tutorial
>> > provided,
>> > the instructions led me to believe I was right in being perplexed, thus
>> > this
>> > post.
>> >
>> > Anyone have info?
>> >
>> > Thank you,
>> > Scott
>> >
>>|||I agree with what you're saying about the reportbuilder being a simple tool
for users without much or any knowledge of SQL or the particular database.
The bit I don't understand is why we are so limited in what we can do when
building the model.
This is a layer of abstraction and so it shouldn't matter if the actual
database is complicated underneath as long as the final model you present the
user with is straight forward.
Whats the point of having the layer of abstraction if everything has to be
in the database, you might as well report directly from the database.
As far as you know, is there definitely no way to say 2 tables are related
in your model that don't have an actual foreign key in SQL Server?
"Jéjé" wrote:
> Reportbuilder is not designed for complex schemas!
> the system doesnt support complex relationships and other business rules.
> Reportbuilder is (more) designed to give access to a well formatted
> database, I mean a database with a "beautyfull" schema (good and simple
> relationship).
> Also, Reportbuilder is designed to be very intuitive for the end user. so
> when the user start to play with the customer name, reportbuilder will
> display only customer's related information because the MUST go through
> these known entities to continue his report.
> A user can't select a customer name and a product name as is, the user MUST
> select the customer name, then the sales then the product, because the
> question is "what are the product sales to my customers?"
> the job you have to do is to create this easy to use reportbuilder model in
> front of your database to insure that an end user can use your database
> without any SQL knowledge.
> if your database is too complex, you have to work hard to generate a report
> builder model.
>
> "James" <James@.discussions.microsoft.com> wrote in message
> news:16CD66FF-16CA-44F6-9E50-496026C88464@.microsoft.com...
> > I'm not sure if I'd call it "normal".
> > From my initial investigations there has to actually be a foreign key on
> > the
> > tables in SQL Server.
> > If this is the case and you can't have "logical" relationships between
> > entities, than this will make things very difficult for a lot of people.
> > It will make report builder unusable for databases with complex
> > relationships between entities and legacy systems.
> > You should be able to have no actual foreign keys if you don't want to
> > (however bad practice this is).
> >
> > "Jéjé" wrote:
> >
> >> its a normal behavior.
> >> The model only present entities which can be used regarding what you
> >> currently have in the report.
> >> of your entities have no links between them, there are not available to
> >> the
> >> user.
> >> This insure that the user will not select unrelated entities and
> >> generates
> >> bad queries. (like full outer join queries)
> >>
> >> You have to create some roles between your entities to allow the user to
> >> go
> >> from 1 entity to another.
> >>
> >> "ScottT" <ScottT@.discussions.microsoft.com> wrote in message
> >> news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
> >> >
> >> > While attempting to create a new report, I came across what I feel is
> >> > some
> >> > odd behavior. I select an item in the Entities list, then drag an item
> >> > from
> >> > the newly populated Fields list to the report area. Once I drop the
> >> > field
> >> > item, the Entities list changes scope showing only the Entity from
> >> > which I
> >> > made a selection and any items within it. I can't get back up to the
> >> > main
> >> > list unless I undo the operation.
> >> >
> >> > Being relatively new to this domain, I considered that I might be
> >> > expecting
> >> > behavior that I shouldn't be, however, when I checked the tutorial
> >> > provided,
> >> > the instructions led me to believe I was right in being perplexed, thus
> >> > this
> >> > post.
> >> >
> >> > Anyone have info?
> >> >
> >> > Thank you,
> >> > Scott
> >> >
> >>
> >>
> >>
>
>|||there is an abstraction layer, the DSV.
in the DSV you can create links that doesn't exists in the database, you can
add a layer of queries and other calculated columns etc...
So if there is no foreign key in the database, you can create a logical
relationship in the DSV.
you can use unrelated tables in the model.
but if there is no links, then the user can't use these 2 tables at the same
time in report builder.
"James" <James@.discussions.microsoft.com> wrote in message
news:6879B37B-A3DC-4871-B254-80BC0B93648D@.microsoft.com...
>I agree with what you're saying about the reportbuilder being a simple tool
> for users without much or any knowledge of SQL or the particular database.
> The bit I don't understand is why we are so limited in what we can do when
> building the model.
> This is a layer of abstraction and so it shouldn't matter if the actual
> database is complicated underneath as long as the final model you present
> the
> user with is straight forward.
> Whats the point of having the layer of abstraction if everything has to be
> in the database, you might as well report directly from the database.
> As far as you know, is there definitely no way to say 2 tables are related
> in your model that don't have an actual foreign key in SQL Server?
> "Jéjé" wrote:
>> Reportbuilder is not designed for complex schemas!
>> the system doesnt support complex relationships and other business rules.
>> Reportbuilder is (more) designed to give access to a well formatted
>> database, I mean a database with a "beautyfull" schema (good and simple
>> relationship).
>> Also, Reportbuilder is designed to be very intuitive for the end user. so
>> when the user start to play with the customer name, reportbuilder will
>> display only customer's related information because the MUST go through
>> these known entities to continue his report.
>> A user can't select a customer name and a product name as is, the user
>> MUST
>> select the customer name, then the sales then the product, because the
>> question is "what are the product sales to my customers?"
>> the job you have to do is to create this easy to use reportbuilder model
>> in
>> front of your database to insure that an end user can use your database
>> without any SQL knowledge.
>> if your database is too complex, you have to work hard to generate a
>> report
>> builder model.
>>
>> "James" <James@.discussions.microsoft.com> wrote in message
>> news:16CD66FF-16CA-44F6-9E50-496026C88464@.microsoft.com...
>> > I'm not sure if I'd call it "normal".
>> > From my initial investigations there has to actually be a foreign key
>> > on
>> > the
>> > tables in SQL Server.
>> > If this is the case and you can't have "logical" relationships between
>> > entities, than this will make things very difficult for a lot of
>> > people.
>> > It will make report builder unusable for databases with complex
>> > relationships between entities and legacy systems.
>> > You should be able to have no actual foreign keys if you don't want to
>> > (however bad practice this is).
>> >
>> > "Jéjé" wrote:
>> >
>> >> its a normal behavior.
>> >> The model only present entities which can be used regarding what you
>> >> currently have in the report.
>> >> of your entities have no links between them, there are not available
>> >> to
>> >> the
>> >> user.
>> >> This insure that the user will not select unrelated entities and
>> >> generates
>> >> bad queries. (like full outer join queries)
>> >>
>> >> You have to create some roles between your entities to allow the user
>> >> to
>> >> go
>> >> from 1 entity to another.
>> >>
>> >> "ScottT" <ScottT@.discussions.microsoft.com> wrote in message
>> >> news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
>> >> >
>> >> > While attempting to create a new report, I came across what I feel
>> >> > is
>> >> > some
>> >> > odd behavior. I select an item in the Entities list, then drag an
>> >> > item
>> >> > from
>> >> > the newly populated Fields list to the report area. Once I drop the
>> >> > field
>> >> > item, the Entities list changes scope showing only the Entity from
>> >> > which I
>> >> > made a selection and any items within it. I can't get back up to
>> >> > the
>> >> > main
>> >> > list unless I undo the operation.
>> >> >
>> >> > Being relatively new to this domain, I considered that I might be
>> >> > expecting
>> >> > behavior that I shouldn't be, however, when I checked the tutorial
>> >> > provided,
>> >> > the instructions led me to believe I was right in being perplexed,
>> >> > thus
>> >> > this
>> >> > post.
>> >> >
>> >> > Anyone have info?
>> >> >
>> >> > Thank you,
>> >> > Scott
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks,
I was trying to do it through the designer view of the report model.
I didn't realise I wanted to be in the designer view of the Data Source View
instead.
"Jéjé" wrote:
> there is an abstraction layer, the DSV.
> in the DSV you can create links that doesn't exists in the database, you can
> add a layer of queries and other calculated columns etc...
> So if there is no foreign key in the database, you can create a logical
> relationship in the DSV.
> you can use unrelated tables in the model.
> but if there is no links, then the user can't use these 2 tables at the same
> time in report builder.
>
> "James" <James@.discussions.microsoft.com> wrote in message
> news:6879B37B-A3DC-4871-B254-80BC0B93648D@.microsoft.com...
> >I agree with what you're saying about the reportbuilder being a simple tool
> > for users without much or any knowledge of SQL or the particular database.
> >
> > The bit I don't understand is why we are so limited in what we can do when
> > building the model.
> > This is a layer of abstraction and so it shouldn't matter if the actual
> > database is complicated underneath as long as the final model you present
> > the
> > user with is straight forward.
> > Whats the point of having the layer of abstraction if everything has to be
> > in the database, you might as well report directly from the database.
> >
> > As far as you know, is there definitely no way to say 2 tables are related
> > in your model that don't have an actual foreign key in SQL Server?
> >
> > "Jéjé" wrote:
> >
> >> Reportbuilder is not designed for complex schemas!
> >> the system doesnt support complex relationships and other business rules.
> >>
> >> Reportbuilder is (more) designed to give access to a well formatted
> >> database, I mean a database with a "beautyfull" schema (good and simple
> >> relationship).
> >>
> >> Also, Reportbuilder is designed to be very intuitive for the end user. so
> >> when the user start to play with the customer name, reportbuilder will
> >> display only customer's related information because the MUST go through
> >> these known entities to continue his report.
> >> A user can't select a customer name and a product name as is, the user
> >> MUST
> >> select the customer name, then the sales then the product, because the
> >> question is "what are the product sales to my customers?"
> >>
> >> the job you have to do is to create this easy to use reportbuilder model
> >> in
> >> front of your database to insure that an end user can use your database
> >> without any SQL knowledge.
> >> if your database is too complex, you have to work hard to generate a
> >> report
> >> builder model.
> >>
> >>
> >> "James" <James@.discussions.microsoft.com> wrote in message
> >> news:16CD66FF-16CA-44F6-9E50-496026C88464@.microsoft.com...
> >> > I'm not sure if I'd call it "normal".
> >> > From my initial investigations there has to actually be a foreign key
> >> > on
> >> > the
> >> > tables in SQL Server.
> >> > If this is the case and you can't have "logical" relationships between
> >> > entities, than this will make things very difficult for a lot of
> >> > people.
> >> > It will make report builder unusable for databases with complex
> >> > relationships between entities and legacy systems.
> >> > You should be able to have no actual foreign keys if you don't want to
> >> > (however bad practice this is).
> >> >
> >> > "Jéjé" wrote:
> >> >
> >> >> its a normal behavior.
> >> >> The model only present entities which can be used regarding what you
> >> >> currently have in the report.
> >> >> of your entities have no links between them, there are not available
> >> >> to
> >> >> the
> >> >> user.
> >> >> This insure that the user will not select unrelated entities and
> >> >> generates
> >> >> bad queries. (like full outer join queries)
> >> >>
> >> >> You have to create some roles between your entities to allow the user
> >> >> to
> >> >> go
> >> >> from 1 entity to another.
> >> >>
> >> >> "ScottT" <ScottT@.discussions.microsoft.com> wrote in message
> >> >> news:14E3E2DB-B9CD-4D3D-9176-3B1FA5D7E4E0@.microsoft.com...
> >> >> >
> >> >> > While attempting to create a new report, I came across what I feel
> >> >> > is
> >> >> > some
> >> >> > odd behavior. I select an item in the Entities list, then drag an
> >> >> > item
> >> >> > from
> >> >> > the newly populated Fields list to the report area. Once I drop the
> >> >> > field
> >> >> > item, the Entities list changes scope showing only the Entity from
> >> >> > which I
> >> >> > made a selection and any items within it. I can't get back up to
> >> >> > the
> >> >> > main
> >> >> > list unless I undo the operation.
> >> >> >
> >> >> > Being relatively new to this domain, I considered that I might be
> >> >> > expecting
> >> >> > behavior that I shouldn't be, however, when I checked the tutorial
> >> >> > provided,
> >> >> > the instructions led me to believe I was right in being perplexed,
> >> >> > thus
> >> >> > this
> >> >> > post.
> >> >> >
> >> >> > Anyone have info?
> >> >> >
> >> >> > Thank you,
> >> >> > Scott
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Saturday, February 25, 2012
Report across multiple db
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.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.
>>
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.
>>
Reporint the last entry...
Hi,
OK, Im somewhat new to writing SQL queries and came across a problem that seems like it should be easy, but I cant figure out how to do this.
I have 2 tables with a 1 to many relationships.
The many table has a text field and a date field.
I want to report the last (most recent) row added to the 2nd table, by linking the two tables and somehow showing the last text field entered.
How can I do this?
ThanksYou can use a timestamp field in your 'many' table and use a MAX(ts_field) in your query, you will find more information on that topic in BOL.|||OK,
I must be missing something...
Here is the code for the 2nd table:
SELECT MAX(date), memo
FROM TABLE2
GROUP by memo
This does not work... how can I insure I select only the MAX date?
Thanks|||timestamp is an internal type that uses a code for each row, the higher ts mark is, the earlier insert/update was done.
So, add another field to your many table (ts_field timestamp) and perform some inserts/updates and use ts_field as any other field, lets say SELECT * FROM MANY ORDER BY TS_FIELD must show your records in updated/inserted order, ok?|||That gives me the records in order, however, I want on my 'report' only the most recent entry for each linked item between the two tables.
So if I have a quote number in table1 and 5 rows that have the same quote number in table2, I want in my join to create only once record-set that will include table1 values and the last record from table2.
Thanks|||try:
select value_field, max(date_field)
from many
where timestamp_field = (select max(timestamp_field)
from many m
where many.value_field = m.value_field)
group by value_field
note that max(date_field) has nothing to do with timestamp, is just like min(date_field) because the group by clause.
the trick is in the timestamp field.
later you can add the ONE table.|||Bad design=bad result. Why are you using 1:N when you need 1:1 ?
Use TR on {active table} to track changes to {audit table}.
/*
"OneTable" - "id" PK
"ManyTable" - "newid" PK,"id", "text", "date"
*/
select m."id",xx."date",xx."newid",m."text",o.*
from "OneTable" o
join "ManyTable" m on o."id"=m."id"
join
(
select m."id",x."date","newid"=max("newid")
from "ManyTable" m
join
(
select "id","date"=max("date")
from "ManyTable"
group by "id"
) x on m."id"=xx."id"
) xx on m."newid"=xx."newid"
This query is designed for more non-sorting columns than 1 ("text")
Not tested. Post creating query.|||OK,
Im thick today
I could not follow the advice.
Here is a part of the code Im trying to use. I have included the main table (QuoteMaster) with joins to two other tables (In reality there are several other joins all from the main table).
The QUOTE is a unique key in the main table and repeats in the QuoteNotes table.
The date field is a date/time stamp.
Im trying to get a single record-set for each quote that will have the latest MEMO field from the QuoteNotes table. There arent entries in the notes table for each quote, but they may repeat
---
Here is the code:
---
SELECT QuoteMaster.Quote, QuoteMaster.CustID,
QuoteNotes.Memo, QuoteNotes.Date,
[Product Group].PDescr
FROM QuoteMaster LEFT OUTER JOIN
[Product Group] ON
QuoteMaster.ProdGrp = [Product Group].ProdGrp LEFT OUTER
JOIN
QuoteNotes ON
QuoteMaster.Quote = QuoteNotes.Quote|||1. I wrote "Not tested. Post creating query." and you have posted nothing. I mean DDL script (CREATE TABLES, PK, FK).
I still do not know, what PK has your QuoteNotes. (Quote,"date"),(Quote,"date",Memo) are near candidate keys,
but can duplicities be there? Should I reverse engineer your design?
2. So I assume QuoteNotes PK(Quote,"date")
CREATE TABLE [Product Group] (ProdGrp int primary key,PDescr varchar(8000))
CREATE TABLE QuoteMaster(Quote int primary key,CustID int null,ProdGrp int null
,foreign key (ProdGrp) references [Product Group](ProdGrp))
CREATE TABLE QuoteNotes (Quote int not null,"date" datetime not null,Memo varchar(8000) not null
,primary key (Quote,"date"),foreign key (Quote) references QuoteMaster(Quote))
SELECT qm.Quote, qm.CustID, qn2.Memo, qn2."Date", pg.PDescr
from QuoteMaster qm
left join QuoteNotes qn2 on qm.Quote=qn2.Quote
left join
(
select qn1.Quote,"date"=max(qn1."date")
from QuoteNotes qn1
group by qn1.Quote
) X on qn2.Quote=X.Quote and qn2."date"=X."date"
left join [Product Group] pg on qm.ProdGrp = pg.ProdGrp
OK, Im somewhat new to writing SQL queries and came across a problem that seems like it should be easy, but I cant figure out how to do this.
I have 2 tables with a 1 to many relationships.
The many table has a text field and a date field.
I want to report the last (most recent) row added to the 2nd table, by linking the two tables and somehow showing the last text field entered.
How can I do this?
ThanksYou can use a timestamp field in your 'many' table and use a MAX(ts_field) in your query, you will find more information on that topic in BOL.|||OK,
I must be missing something...
Here is the code for the 2nd table:
SELECT MAX(date), memo
FROM TABLE2
GROUP by memo
This does not work... how can I insure I select only the MAX date?
Thanks|||timestamp is an internal type that uses a code for each row, the higher ts mark is, the earlier insert/update was done.
So, add another field to your many table (ts_field timestamp) and perform some inserts/updates and use ts_field as any other field, lets say SELECT * FROM MANY ORDER BY TS_FIELD must show your records in updated/inserted order, ok?|||That gives me the records in order, however, I want on my 'report' only the most recent entry for each linked item between the two tables.
So if I have a quote number in table1 and 5 rows that have the same quote number in table2, I want in my join to create only once record-set that will include table1 values and the last record from table2.
Thanks|||try:
select value_field, max(date_field)
from many
where timestamp_field = (select max(timestamp_field)
from many m
where many.value_field = m.value_field)
group by value_field
note that max(date_field) has nothing to do with timestamp, is just like min(date_field) because the group by clause.
the trick is in the timestamp field.
later you can add the ONE table.|||Bad design=bad result. Why are you using 1:N when you need 1:1 ?
Use TR on {active table} to track changes to {audit table}.
/*
"OneTable" - "id" PK
"ManyTable" - "newid" PK,"id", "text", "date"
*/
select m."id",xx."date",xx."newid",m."text",o.*
from "OneTable" o
join "ManyTable" m on o."id"=m."id"
join
(
select m."id",x."date","newid"=max("newid")
from "ManyTable" m
join
(
select "id","date"=max("date")
from "ManyTable"
group by "id"
) x on m."id"=xx."id"
) xx on m."newid"=xx."newid"
This query is designed for more non-sorting columns than 1 ("text")
Not tested. Post creating query.|||OK,
Im thick today
I could not follow the advice.
Here is a part of the code Im trying to use. I have included the main table (QuoteMaster) with joins to two other tables (In reality there are several other joins all from the main table).
The QUOTE is a unique key in the main table and repeats in the QuoteNotes table.
The date field is a date/time stamp.
Im trying to get a single record-set for each quote that will have the latest MEMO field from the QuoteNotes table. There arent entries in the notes table for each quote, but they may repeat
---
Here is the code:
---
SELECT QuoteMaster.Quote, QuoteMaster.CustID,
QuoteNotes.Memo, QuoteNotes.Date,
[Product Group].PDescr
FROM QuoteMaster LEFT OUTER JOIN
[Product Group] ON
QuoteMaster.ProdGrp = [Product Group].ProdGrp LEFT OUTER
JOIN
QuoteNotes ON
QuoteMaster.Quote = QuoteNotes.Quote|||1. I wrote "Not tested. Post creating query." and you have posted nothing. I mean DDL script (CREATE TABLES, PK, FK).
I still do not know, what PK has your QuoteNotes. (Quote,"date"),(Quote,"date",Memo) are near candidate keys,
but can duplicities be there? Should I reverse engineer your design?
2. So I assume QuoteNotes PK(Quote,"date")
CREATE TABLE [Product Group] (ProdGrp int primary key,PDescr varchar(8000))
CREATE TABLE QuoteMaster(Quote int primary key,CustID int null,ProdGrp int null
,foreign key (ProdGrp) references [Product Group](ProdGrp))
CREATE TABLE QuoteNotes (Quote int not null,"date" datetime not null,Memo varchar(8000) not null
,primary key (Quote,"date"),foreign key (Quote) references QuoteMaster(Quote))
SELECT qm.Quote, qm.CustID, qn2.Memo, qn2."Date", pg.PDescr
from QuoteMaster qm
left join QuoteNotes qn2 on qm.Quote=qn2.Quote
left join
(
select qn1.Quote,"date"=max(qn1."date")
from QuoteNotes qn1
group by qn1.Quote
) X on qn2.Quote=X.Quote and qn2."date"=X."date"
left join [Product Group] pg on qm.ProdGrp = pg.ProdGrp
Subscribe to:
Posts (Atom)