master sysmessages: detail

Showing posts with label detail. Show all posts

Monday, March 26, 2012

Matrix-How to tell when you're in a subtotal cell

I have a matrix where the detail cells are set to navigate to a subreport.
However I *don't* want them to provide navigation if they're in a subtotal
row or column. I can't figure out what to call in order to determine
whether the cell is a subtotal or not.
Any ideas?This is a multi-part message in MIME format.
--=_NextPart_000_0049_01C625AB.01FC8700
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Try playing around with this formula:
=3Diif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", ="In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of =ColumnGroup1", "In Subtotal of entire matrix"))
Paste it into your usual matrix cell, and change RowGroup1 to whatever =your row group is called etc.
Kaisa M: Lindahl
"Greg S" <gregslistacct@.hotmail.com> wrote in message =news:ecBRlY7IGHA.1180@.TK2MSFTNGP09.phx.gbl...
>I have a matrix where the detail cells are set to navigate to a =subreport. > However I *don't* want them to provide navigation if they're in a =subtotal > row or column. I can't figure out what to call in order to determine > whether the cell is a subtotal or not.
> > Any ideas?
> >
--=_NextPart_000_0049_01C625AB.01FC8700
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try playing around with this formula:
=3Diif(InScope("ColumnGroup1"), =iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In =Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
Paste it into your usual matrix cell, and =change RowGroup1 to whatever your row group is called etc.
Kaisa M: Lindahl
"Greg S" wrote =in message news:ecBRlY7IGHA.1180@.TK2MSFTNGP09.phx.gbl...>I have a matrix where the =detail cells are set to navigate to a subreport. > However I *don't* want them =to provide navigation if they're in a subtotal > row or =column. I can't figure out what to call in order to determine > whether the =cell is a subtotal or not.> > Any ideas?> >

--=_NextPart_000_0049_01C625AB.01FC8700--|||This worked perfectly! Thanks.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:eWe1hKaJGHA.1832@.TK2MSFTNGP11.phx.gbl...
Try playing around with this formula:
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
ColumnGroup1", "In Subtotal of entire matrix"))
Paste it into your usual matrix cell, and change RowGroup1 to whatever your
row group is called etc.
Kaisa M: Lindahl
"Greg S" <gregslistacct@.hotmail.com> wrote in message
news:ecBRlY7IGHA.1180@.TK2MSFTNGP09.phx.gbl...
>I have a matrix where the detail cells are set to navigate to a subreport.
> However I *don't* want them to provide navigation if they're in a subtotal
> row or column. I can't figure out what to call in order to determine
> whether the cell is a subtotal or not.
> Any ideas?
>|||I am not familiar with the InScope function. You did not really use all
those words did you? For instance "in Subtotal of entire matrix" ' or did
you substitute names for your matrix in there. Like I said ... I am not
familiar with that function and I am trying to learn how to use it. This
seems like a very good example and I want to understand it correctly. Thanks.
"Greg S" wrote:
> This worked perfectly! Thanks.
>
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:eWe1hKaJGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Try playing around with this formula:
> =iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
> Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
> ColumnGroup1", "In Subtotal of entire matrix"))
> Paste it into your usual matrix cell, and change RowGroup1 to whatever your
> row group is called etc.
> Kaisa M: Lindahl
>
> "Greg S" <gregslistacct@.hotmail.com> wrote in message
> news:ecBRlY7IGHA.1180@.TK2MSFTNGP09.phx.gbl...
> >I have a matrix where the detail cells are set to navigate to a subreport.
> > However I *don't* want them to provide navigation if they're in a subtotal
> > row or column. I can't figure out what to call in order to determine
> > whether the cell is a subtotal or not.
> >
> > Any ideas?
> >
> >
>|||The InScope function relates to the name of the scope, which can be a row
group name, a column group name, a matrix name or a dataset name.
If you have one ColumnGroup called thisColumnGroup, one RowGroup called
thisRowGroup and a Matrix called thisMatrix, the following code
=iif(InScope("ColumnGroup1"),
iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"),
iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of
entire matrix"))
would be translated to
=IIF(Inscope("thisColumnGroup"),
IIF(inScope("thisRowGroup"), "This is the text that will show up in cells in
thisColumnGroup", "This is the text that will show up in the subtotal of
thisRowGroup"),
IIF(InScope("thisRowGroup"), "This is the text that will show up in the
subtotal of thisColumnGroup",
"This is the text that shows up in the intersection between your row and
column groups"))
Create a matrix with a row group and a column group, change the names of
thisColumnGroup and thisRowGroup with the names of your groups, and put the
statement above in a detail cell, and see what shows up. :)
A more normal statement would be
=IIF(Inscope("thisColumnGroup"),
IIF(inScope("thisRowGroup"), Fields!Name.Value,
um(Fields!ColumnName.Value ),
IIF(InScope("thisRowGroup"), sum(Fields!ColumnName2.Value ,
"x"))
But the best thing is to try it out, the scopes are a bit abstract, it's
easier to understand if you try playing with it. (At least that's what I do.
:) )
Kaisa M. Lindahl Lervik
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:1901C919-A4EA-48D0-B8FE-56F059A8660F@.microsoft.com...
>I am not familiar with the InScope function. You did not really use all
> those words did you? For instance "in Subtotal of entire matrix" ' or
> did
> you substitute names for your matrix in there. Like I said ... I am not
> familiar with that function and I am trying to learn how to use it. This
> seems like a very good example and I want to understand it correctly.
> Thanks.
> "Greg S" wrote:
>> This worked perfectly! Thanks.
>>
>> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
>> news:eWe1hKaJGHA.1832@.TK2MSFTNGP11.phx.gbl...
>> Try playing around with this formula:
>> =iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
>> Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
>> ColumnGroup1", "In Subtotal of entire matrix"))
>> Paste it into your usual matrix cell, and change RowGroup1 to whatever
>> your
>> row group is called etc.
>> Kaisa M: Lindahl
>>
>> "Greg S" <gregslistacct@.hotmail.com> wrote in message
>> news:ecBRlY7IGHA.1180@.TK2MSFTNGP09.phx.gbl...
>> >I have a matrix where the detail cells are set to navigate to a
>> >subreport.
>> > However I *don't* want them to provide navigation if they're in a
>> > subtotal
>> > row or column. I can't figure out what to call in order to determine
>> > whether the cell is a subtotal or not.
>> >
>> > Any ideas?
>> >
>> >
>>|||Thanks for the great explanation Kaisa ... I will try playing around with it
using this example. I appreciate your help!
"Kaisa M. Lindahl Lervik" wrote:
> The InScope function relates to the name of the scope, which can be a row
> group name, a column group name, a matrix name or a dataset name.
> If you have one ColumnGroup called thisColumnGroup, one RowGroup called
> thisRowGroup and a Matrix called thisMatrix, the following code
> =iif(InScope("ColumnGroup1"),
> iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"),
> iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of
> entire matrix"))
> would be translated to
>
> =IIF(Inscope("thisColumnGroup"),
> IIF(inScope("thisRowGroup"), "This is the text that will show up in cells in
> thisColumnGroup", "This is the text that will show up in the subtotal of
> thisRowGroup"),
> IIF(InScope("thisRowGroup"), "This is the text that will show up in the
> subtotal of thisColumnGroup",
> "This is the text that shows up in the intersection between your row and
> column groups"))
> Create a matrix with a row group and a column group, change the names of
> thisColumnGroup and thisRowGroup with the names of your groups, and put the
> statement above in a detail cell, and see what shows up. :)
> A more normal statement would be
> =IIF(Inscope("thisColumnGroup"),
> IIF(inScope("thisRowGroup"), Fields!Name.Value,
> um(Fields!ColumnName.Value ),
> IIF(InScope("thisRowGroup"), sum(Fields!ColumnName2.Value ,
> "x"))
> But the best thing is to try it out, the scopes are a bit abstract, it's
> easier to understand if you try playing with it. (At least that's what I do.
> :) )
> Kaisa M. Lindahl Lervik
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:1901C919-A4EA-48D0-B8FE-56F059A8660F@.microsoft.com...
> >I am not familiar with the InScope function. You did not really use all
> > those words did you? For instance "in Subtotal of entire matrix" ' or
> > did
> > you substitute names for your matrix in there. Like I said ... I am not
> > familiar with that function and I am trying to learn how to use it. This
> > seems like a very good example and I want to understand it correctly.
> > Thanks.
> >
> > "Greg S" wrote:
> >
> >> This worked perfectly! Thanks.
> >>
> >>
> >> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> >> news:eWe1hKaJGHA.1832@.TK2MSFTNGP11.phx.gbl...
> >> Try playing around with this formula:
> >> =iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
> >> Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
> >> ColumnGroup1", "In Subtotal of entire matrix"))
> >>
> >> Paste it into your usual matrix cell, and change RowGroup1 to whatever
> >> your
> >> row group is called etc.
> >>
> >> Kaisa M: Lindahl
> >>
> >>
> >> "Greg S" <gregslistacct@.hotmail.com> wrote in message
> >> news:ecBRlY7IGHA.1180@.TK2MSFTNGP09.phx.gbl...
> >> >I have a matrix where the detail cells are set to navigate to a
> >> >subreport.
> >> > However I *don't* want them to provide navigation if they're in a
> >> > subtotal
> >> > row or column. I can't figure out what to call in order to determine
> >> > whether the cell is a subtotal or not.
> >> >
> >> > Any ideas?
> >> >
> >> >
> >>
> >>
>
>sql

Friday, March 23, 2012

Matrix Totals - left formatted

I have successfully created a matrix consisting of payroll dates as column
headers, with task types as my rows and hours/task/day the detail data. The
columns total perfectly, but the totals only display to the RIGHT of all the
data. We display our totals FIRST, then the detail data. Can I do this?
total hrs day 1 day 2
task 1 16 8 8Yes. Click on the little green triangle in the (row/column) heading to get
the subtotal properties and look at the properties window. There is a
"Position" property which is set to "After" by default. You can set it to
"Before", which gives you the effect you want.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JeanSA" <JeanSA@.discussions.microsoft.com> wrote in message
news:C4A8ABDC-B776-4CE1-9B4B-1DDC70DD6B8A@.microsoft.com...
>I have successfully created a matrix consisting of payroll dates as column
> headers, with task types as my rows and hours/task/day the detail data.
> The
> columns total perfectly, but the totals only display to the RIGHT of all
> the
> data. We display our totals FIRST, then the detail data. Can I do this?
> total hrs day 1 day 2
> task 1 16 8 8|||Thank you. Wasn't sure what "Position" meant. I appreciate your quick
response.
"Robert Bruckner [MSFT]" wrote:
> Yes. Click on the little green triangle in the (row/column) heading to get
> the subtotal properties and look at the properties window. There is a
> "Position" property which is set to "After" by default. You can set it to
> "Before", which gives you the effect you want.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "JeanSA" <JeanSA@.discussions.microsoft.com> wrote in message
> news:C4A8ABDC-B776-4CE1-9B4B-1DDC70DD6B8A@.microsoft.com...
> >I have successfully created a matrix consisting of payroll dates as column
> > headers, with task types as my rows and hours/task/day the detail data.
> > The
> > columns total perfectly, but the totals only display to the RIGHT of all
> > the
> > data. We display our totals FIRST, then the detail data. Can I do this?
> > total hrs day 1 day 2
> > task 1 16 8 8
>
>

Monday, March 19, 2012

Matrix Page Breaks within a list / Visibility is conditional

Think I have found a bug.

I have a report that has a Parameter called "LevelOfDetail" This has 2 possible values "Summary, Detail, or Combined".

The report has a Summary Section and a subreport that holds the details.
WIthin the summary section is a Matrix (a list of all properties and some values)

If they choose to see the details, the matrix in the details section will show a break out of all this information summarized in the summary section.

The Details Matrix is set to have a page break at the beginning and end of the top level group.

I have a list control on the summary page that contains the details subreport and passes the appropriate parameter.

Everything works the way I want until I try to set visibility on sub report.

Once I set the conditional visibility of the report objects (based on the Level of Detail parameter) the page breaks are not recognized.

This is important as the user will never print the report, but will be downloading to Excel.

If the page breaks work correctly, each page is assigned a different worksheet in their downloaded workbook.

Any help, please let me know.

Page break and visibility condition can not be used for same componenet. I mean to say they are not supported by SSRS 2005. u need to go for a work around.

Priyank

Monday, March 12, 2012

Matrix Groups and Sums

I have what I thought was a pretty simple report, but have encountered two
issues.
I have three groups in this report.
1. When using sum on my detail row I am getting what appears to be a running
total in one of the columns. When I don't use sum then I get either the first
or last value for a detail record.
2. When using groups, the results are not what I would expect. It seems
almost impossible to get the grouping I would like. If I want to total on the
first group and then the second group and then a final total for all the
groups I can't. Am I missing something as this seems like it should be a
trivial effort.
If anybody can help I would appreciate it.
--
DCDUse Fields!fieldname.value (sounds like you are getting sum, first, and
last)... do not use a function at all..
Look at the sum documentation,, there is an additional parameter which
allows you to set the scope, Use a group name there and see if that helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Darryl" <ddillman@.tmsteam.com> wrote in message
news:F1127546-8532-4873-A11B-B6A7D3B8897E@.microsoft.com...
>I have what I thought was a pretty simple report, but have encountered two
> issues.
> I have three groups in this report.
> 1. When using sum on my detail row I am getting what appears to be a
> running
> total in one of the columns. When I don't use sum then I get either the
> first
> or last value for a detail record.
> 2. When using groups, the results are not what I would expect. It seems
> almost impossible to get the grouping I would like. If I want to total on
> the
> first group and then the second group and then a final total for all the
> groups I can't. Am I missing something as this seems like it should be a
> trivial effort.
> If anybody can help I would appreciate it.
> --
> DCD

Matrix Fields for Subtotal

I had a matrix which retrieves value from a database.When i put "Fields!availability.Value" in the detail cell,it shows warning about aggregate.And i am using 'subtotal'.But my problem is when i use "Sum(Fields!availability.Value)",the value itself that was displayed is different from the actual value in the database.And when i use "First!availability.Value",the subtotal only get the first value from the above rows to which to get the subtotal.Uhm can there be anyway to solve this? Thanks in advance.

Hello,

Can you please explain what you mean by 'the value itself that was displayed is different from the actual value in the database'? Do you mean that the total shown does not equal the sum of the availability values shown in the matrix report? Can you provide an example?

Did you build your matrix using the wizard or directly? Have you tried the wizard? Play around with it for a little bit to get an understanding of the way SSRS matrix reports work.

Larry Smithmier

http://Smithmier.com/Blog

|||

I am using a manually created matrix.I had said that because the value is really different from what is retrieved from the database.Instead of 0.1, the value then becomes 0.9.And same as for the other value which also change.The total is equal to the sum of the availabilty, its fine,as long as the value is correct,which for me is not.What could have possibly be the reason why the values do change?

|||Ow..this issue was closed...hehe it was my fault,because it was in my subreport but when i run it actually on my main report it just works fine..It was caused by my parameters,wrong values were being passed..hehe..Thanks

Matrix Fields for Subtotal

Hello,

Did you build your matrix using the wizard or directly? Have you tried the wizard? Play around with it for a little bit to get an understanding of the way SSRS matrix reports work.

Larry Smithmier

http://Smithmier.com/Blog

|||

Wednesday, March 7, 2012

Matrices in reporting services

Hello All,

I am attempting to produce a Summary report that will detail all financial losses incurred by a company. These losses are seperated into categories and each category has two types, direct and indirect. To date I have employed the use of multiple matrices.

The problem is that if the company has incurred no losses for a specific category then this will cause the matrix to automatically cater for the lack of this column. However I want this to show up regardless of there being data...

I cannot employ the use of tables...as it simply does not work!

Does anyone have any suggestions? Ideas?

All would be appreciated!!

Thanks

JrDevRon,

You could return "Zeros" or Blank space in your SQL where no losses occur for specific categories. This is ensure that you always return all categories regardless.

Ham

|||

Hi Ham,

Thanks for the reply!

I have this implemented already and this works fine when at least one of the cells in the same column/row has been populated. If there is no data for a specific column then the whole thing disappears regardless...this is something I do not want.

Right now I have it this way and giving me a subtotal, I also then have another row detailing the recovered amounts which apply to my categories below my subtotal, obviously due to restrictions I have to use a second matrix for this information. I then subtract the corresponding information from the subtotal. But due to the above problem these columns do not always line up on the report...

I know this sounds convoluted and more complex than it probably needs to be but I would be appreciative of any help...

Thank you all!!

Saturday, February 25, 2012

Master/Detail table insertion.

Hi Experts,

I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain

Insertion in Master Table..................................... A

Insertion in Detail Table........................................B

Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.

So please can any one tell me a good approach for this.

This my solution using trigger and SET XACT_ABORT ::

create table A (a int)

create table B (a int)

create trigger t_A on A

after insert

set xact_abort off

if @.@.error <>0

begin

print 'error in master'

rollback tran

else

begin

commit tran

insert into b

select a from inserted

-- values ('gigi')

end

if you run

insert into a values (1) the value 1 is inserted in A and B

for testing how is behavior on error of inserting in B table, unn comment "-- values ('gigi')" and comment " select a from inserted " and run the command " alter triger t_A ...." ()modifying trigger)

if you run insert into a values (2) the value 2 will be insert in A table and inserting will not be possible in B table because is an error .

So you have to modify the operation of inserting in B

" insert into b

select a from inserted

-- values ('gigi')

as your business logic request.

|||

Thanks for the reply but i dont want to use trigers because it can affect the performance.

Thanks for giving time.

|||

Did you calculate how is affected the performance ? Obviously the trigger have to be avoided but in some cases it is usually.

|||You'll probably want to encapsulate the inserts within a transaction with logic to perform a ROLLBACK if anything goes wrong or a COMMIT when all of the work is complete.

There is a vast amount of information in Books Online on transaction management.

Put simply your logic might look something like this.

BEGIN TRANSACTION

INSERT INTO A
INSERT INTO B
INSERT INTO B
INSERT INTO B

IF NO ERROR THEN

COMMIT ELSE
ROLLBACK

|||Thanks Sir,

Please can u explain that how can i send array because according to your solution i will have to use array for my detail information for example if my master table is ORDER and my detail table is ORDER_DETAIL then the list of products are to be send in array which i dont know how will i do.

If you can provide a simple code i will be thankfull to you.

Thanks.

Monday, February 20, 2012

Master/Detail table insertion.

Hi Experts,

I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain

Insertion in Master Table..................................... A

Insertion in Detail Table........................................B

So please can any one tell me a good approach for this.

This my solution using trigger and SET XACT_ABORT ::

create table A (a int)

create table B (a int)

create trigger t_A on A

after insert

set xact_abort off

if @.@.error <>0

begin

print 'error in master'

rollback tran

else

begin

commit tran

insert into b

select a from inserted

-- values ('gigi')

end

if you run

insert into a values (1) the value 1 is inserted in A and B

for testing how is behavior on error of inserting in B table, unn comment "-- values ('gigi')" and comment " select a from inserted " and run the command " alter triger t_A ...." ()modifying trigger)

if you run insert into a values (2) the value 2 will be insert in A table and inserting will not be possible in B table because is an error .

So you have to modify the operation of inserting in B

" insert into b

select a from inserted

-- values ('gigi')

as your business logic request.

|||

Thanks for the reply but i dont want to use trigers because it can affect the performance.

Thanks for giving time.

|||

Did you calculate how is affected the performance ? Obviously the trigger have to be avoided but in some cases it is usually.

|||You'll probably want to encapsulate the inserts within a transaction with logic to perform a ROLLBACK if anything goes wrong or a COMMIT when all of the work is complete.

There is a vast amount of information in Books Online on transaction management.

Put simply your logic might look something like this.

BEGIN TRANSACTION

INSERT INTO A
INSERT INTO B
INSERT INTO B
INSERT INTO B

IF NO ERROR THEN

COMMIT ELSE
ROLLBACK

|||Thanks Sir,

If you can provide a simple code i will be thankfull to you.

Thanks.

Master/Detail Query - Got confused

Hallo all,

I’ve two tables (Order_Headers and Order_Details) in SQL Server 2005:

Order_Headers

DocType Order#SubTotalVATTotal

01110018118

01220036236

Order_Details

Order#Line# QTY SKUUnit_Price

111A30

121B70

211C40

221D100

231E60

I need to query both tables and return Master/Detail XML with the following format, ie. Need to return all order lines for a particular order header#. I’m using FOR XML PATH('Document'), ROOT('XML_QueryDocument') but I’m not able to add the “<LineItem>” tag for each order line.

<XML_QueryDocument>

<Unit_Price>30</Unit_Price>

</LineItem>

<Unit_Price>70</Unit_Price>

</LineItem>

</Document>

</XML_QueryDocument>

Could anyone please shed some light on how to accomplish this? I think I need several SELECT statements but I got confused.

Thanks in advance,

Code Snippet

CREATE TABLE #Order_Headers(DocType int, OrderNum int, SubTotal int, VAT int, Total int)

CREATE TABLE #Order_Detail(OrderNum int, LineNum int, QTY int, SKU char(10), UnitPrice int)

INSERT INTO #Order_Headers SELECT 1, 1, 100, 18, 118

INSERT INTO #Order_Headers SELECT 1, 2, 200, 36, 236

INSERT INTO #Order_Detail SELECT 1, 1, 1, 'A', 30

INSERT INTO #Order_Detail SELECT 2, 1, 1, 'B', 70

INSERT INTO #Order_Detail SELECT 1, 1, 1, 'C', 40

INSERT INTO #Order_Detail SELECT 3, 1, 1, 'D', 100

INSERT INTO #Order_Detail SELECT 4, 1, 1, 'E', 60

SELECT * FROM #Order_Headers oh

FULL OUTER JOIN #Order_Detail od on oh.OrderNum = od.OrderNum

DROP TABLE #Order_Headers

DROP TABLE #Order_Detail

Adamus

|||

Thanks Adamus for your prompt reply!

If I add FOR XML PATH('Document'), ROOT('XML_QueryDocument') at the end of the SELECT statement, I get the following.

It's repeating tags DocType, OrderNum, SubTotal for each order line. This is exactly what I wanted left out. I think the query needs to create a LineItem tag for each order line and show the order header just once either at the top or at the bottom inside a <document> tag. This is where I got confused.

Thanks,

<XML_QueryDocument>
<Document>
<DocType>1</DocType>
<OrderNum>1</OrderNum>
<SubTotal>100</SubTotal>
<VAT>18</VAT>
<Total>118</Total>
<OrderNum>1</OrderNum>
<LineNum>1</LineNum>
<QTY>1</QTY>
<SKU>A </SKU>
<UnitPrice>30</UnitPrice>
</Document>
<Document>
<DocType>1</DocType>
<OrderNum>1</OrderNum>
<SubTotal>100</SubTotal>
<VAT>18</VAT>
<Total>118</Total>
<OrderNum>1</OrderNum>
<LineNum>1</LineNum>
<QTY>1</QTY>
<SKU>C </SKU>
<UnitPrice>40</UnitPrice>
</Document>

.......

|||

Perhaps something like this:

Code Snippet

SELECT
Orders.DocType,
Orders.OrderNum,
Orders.SubTotal,
Orders.VAT,
Orders.Total,
LineItem.LineNum,
LineItem.QTY,
LineItem.SKU,
LineItem.UnitPrice
FROM #Order_Headers Orders
JOIN #Order_Detail LineItem
ON Orders.OrderNum = LineItem.OrderNum
FOR XML AUTO, ELEMENTS

XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<Orders>
<DocType>1</DocType>
<OrderNum>1</OrderNum>
<SubTotal>100</SubTotal>
<VAT>18</VAT>
<Total>118</Total>
<LineItem>
<LineNum>1</LineNum>
<QTY>1</QTY>
<SKU>A </SKU>
<UnitPrice>30</UnitPrice>
</LineItem>
</Orders>
{etc.}

|||

Thanks Arnie also for a good insight.

I modified the code a little bit as the code above doesn't produce well-formed XML (it shows "XML document cannot contain multiple root level elements"). I've also changed the INSERT on #Order_Detail for order# 1 to have 3 lines

Since I only need to return a document at the time I just added a WHERE clause and that's exactly what I need.

Here's the code just in case someone else is after this also:

CREATE TABLE #Order_Headers(DocType int, OrderNum int, SubTotal int, VAT int, Total int)
CREATE TABLE #Order_Detail(OrderNum int, LineNum int, QTY int, SKU char(10), UnitPrice int)

INSERT INTO #Order_Headers SELECT 1, 1, 100, 18, 118
INSERT INTO #Order_Headers SELECT 1, 2, 200, 36, 236

INSERT INTO #Order_Detail SELECT 1, 1, 10, 'A', 30
INSERT INTO #Order_Detail SELECT 1, 2, 20, 'B', 700
INSERT INTO #Order_Detail SELECT 1, 3, 30, 'C', 4000
INSERT INTO #Order_Detail SELECT 3, 1, 1, 'D', 100
INSERT INTO #Order_Detail SELECT 4, 1, 1, 'E', 60

DROP TABLE #Order_Headers
DROP TABLE #Order_Detail

master with two detail views

Hello community,

I think my problem is easy to solve even though I did not find a solution through different tutorials and help pages. Here it is (select statements are hier simplified):

In the gridview "GridView1" I have a master record with person_id, which is the data-key-value. There is also another id-field named task_id (This record comes from a database view which joins the persons- and the tasks- table)

SelectCommand="SELECT [id], [person_id], [task_id] FROM [ViewPersonTasks] WHERE ([id] =@.id)"

For both fields I want to display details in two different detail-views. One for the person (depending on person_id) and one for the tasks (depending on the task_id).

The first one is easy. I declare a details-view for the person data based on a SqlDataSource with a control-parameter like this:

SelectCommand="SELECT [person_id], [first_name], [last_name], [birth_date] FROM [TabPersons] WHERE ([person_id] = @.person_id)"

...

But now the problem: how should I declare a parameter @.task_id for the task_id, so that the second select statement for the tasks-details-view retrievs the data for the tasks:

SelectCommand="SELECT [task_id], [task_name],[task_date], [task_description] FROM [TabTasks] WHERE ([task_id] =@.task_id)"

@.task_id should have the value from the task_id-field of the master record, displayd in the master grid-view.

Thank you in advance for your help

Hello,

I found the solution on my own.

Within the properties of the gridview with the master records the property "datakeyname" must contain both keys: person_id;task_id

And in the second details datasource the task_id can be referenced as the second (index) within the selecteddatakey-collection in a select/control paramter like this:

Perhaps this may help someone else here.

regars

dieter

Monday, March 26, 2012

Matrix-How to tell when you're in a subtotal cell

Friday, March 23, 2012

Matrix Totals - left formatted

Monday, March 19, 2012

Matrix Page Breaks within a list / Visibility is conditional

Monday, March 12, 2012

Matrix Groups and Sums

Matrix Fields for Subtotal

Matrix Fields for Subtotal

Wednesday, March 7, 2012

Matrices in reporting services

Saturday, February 25, 2012

Master/Detail table insertion.

Monday, February 20, 2012

Master/Detail table insertion.

Master/Detail Query - Got confused

master with two detail views

master sysmessages

Blog Archive

About Me