(Sorry for the cross-post - I put this on the regular XML list by
accident.)
There there anyway to get this to work:
http://mydomain.com/some.xml&xsl=mother.xslt
where some.xml is
<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header sql:nullvalue="IsNULL">
</sql:header>
<sql:query >
SELECT * FROM sometable
FOR xml path('somenode'), root('nodes')
</sql:query>
</root>
and mother.xslt has
<xsl:variable name="employees" select="document('http://mydomain.com/
someother.xml')"/>
Note that we use windows authentication.
Based on these, it appears not to be possible:
http://msdn2.microsoft.com/en-us/library/ms763800.aspx
http://msdn2.microsoft.com/en-us/library/ms256465.aspx
Check out this article:
Communicate with a SQL Database During an XSL Transform
http://www.braintrove.com/article/30
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )
|||On Apr 26, 11:34 am, "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> Check out this article:
> Communicate with a SQL Database During an XSL Transformhttp://www.braintrove.com/article/30
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
This a good article, Greg. However, I'm trying to accomplish this
strictly with SQLXML via 1 URL. I pass the master xml doc along with
an xslt doc which itself creates a variable via another SQLXML URL.
Any more ideas about this?
Thanks!
Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts
Tuesday, March 27, 2012
Calling SQLXML From XSLT
(Sorry for the cross-post - I put this on the regular XML list by
accident.)
There there anyway to get this to work:
http://mydomain.com/some.xml&xsl=mother.xslt
where some.xml is
<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header sql:nullvalue="IsNULL">
</sql:header>
<sql:query >
SELECT * FROM sometable
FOR xml path('somenode'), root('nodes')
</sql:query>
</root>
and mother.xslt has
<xsl:variable name="employees" select="document('http://mydomain.com/
someother.xml')"/>
Note that we use windows authentication.
Based on these, it appears not to be possible:
http://msdn2.microsoft.com/en-us/library/ms763800.aspx
http://msdn2.microsoft.com/en-us/library/ms256465.aspxCheck out this article:
Communicate with a SQL Database During an XSL Transform
http://www.braintrove.com/article/30
--=20
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )|||On Apr 26, 11:34 am, "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> Check out this article:
> Communicate with a SQL Database During an XSL Transformhttp://www.braintro
ve.com/article/30
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
This a good article, Greg. However, I'm trying to accomplish this
strictly with SQLXML via 1 URL. I pass the master xml doc along with
an xslt doc which itself creates a variable via another SQLXML URL.
Any more ideas about this?
Thanks!
accident.)
There there anyway to get this to work:
http://mydomain.com/some.xml&xsl=mother.xslt
where some.xml is
<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header sql:nullvalue="IsNULL">
</sql:header>
<sql:query >
SELECT * FROM sometable
FOR xml path('somenode'), root('nodes')
</sql:query>
</root>
and mother.xslt has
<xsl:variable name="employees" select="document('http://mydomain.com/
someother.xml')"/>
Note that we use windows authentication.
Based on these, it appears not to be possible:
http://msdn2.microsoft.com/en-us/library/ms763800.aspx
http://msdn2.microsoft.com/en-us/library/ms256465.aspxCheck out this article:
Communicate with a SQL Database During an XSL Transform
http://www.braintrove.com/article/30
--=20
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )|||On Apr 26, 11:34 am, "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> Check out this article:
> Communicate with a SQL Database During an XSL Transformhttp://www.braintro
ve.com/article/30
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
This a good article, Greg. However, I'm trying to accomplish this
strictly with SQLXML via 1 URL. I pass the master xml doc along with
an xslt doc which itself creates a variable via another SQLXML URL.
Any more ideas about this?
Thanks!
Monday, March 19, 2012
Calling a webservice from a SQL Trigger
Please,
is there a way i can execute a webservice from within a SQL Server 2k
Trigger? Sending and receiving XML values?
Tks,
Fabiano
Hi
Maybe you can do this through a COM object and the sp_OA* procedures?
John
"Fabiano" <a@.a.com.br> wrote in message
news:%23WqHeXqlEHA.1644@.tk2msftngp13.phx.gbl...
> Please,
> is there a way i can execute a webservice from within a SQL Server 2k
> Trigger? Sending and receiving XML values?
> Tks,
> Fabiano
>
>
is there a way i can execute a webservice from within a SQL Server 2k
Trigger? Sending and receiving XML values?
Tks,
Fabiano
Hi
Maybe you can do this through a COM object and the sp_OA* procedures?
John
"Fabiano" <a@.a.com.br> wrote in message
news:%23WqHeXqlEHA.1644@.tk2msftngp13.phx.gbl...
> Please,
> is there a way i can execute a webservice from within a SQL Server 2k
> Trigger? Sending and receiving XML values?
> Tks,
> Fabiano
>
>
Calling a webservice from a SQL Trigger
Please,
is there a way i can execute a webservice from within a SQL Server 2k
Trigger? Sending and receiving XML values?
Tks,
FabianoHi
Maybe you can do this through a COM object and the sp_OA* procedures?
John
"Fabiano" <a@.a.com.br> wrote in message
news:%23WqHeXqlEHA.1644@.tk2msftngp13.phx.gbl...
> Please,
> is there a way i can execute a webservice from within a SQL Server 2k
> Trigger? Sending and receiving XML values?
> Tks,
> Fabiano
>
>
is there a way i can execute a webservice from within a SQL Server 2k
Trigger? Sending and receiving XML values?
Tks,
FabianoHi
Maybe you can do this through a COM object and the sp_OA* procedures?
John
"Fabiano" <a@.a.com.br> wrote in message
news:%23WqHeXqlEHA.1644@.tk2msftngp13.phx.gbl...
> Please,
> is there a way i can execute a webservice from within a SQL Server 2k
> Trigger? Sending and receiving XML values?
> Tks,
> Fabiano
>
>
Wednesday, March 7, 2012
Call for Gurus!. converting msSQl schema into standard format (DDL, XML)
Hi,
I am wondering if any of the DB Gurus out there help me finding a way to export mssql SCHEMA (not the actual data) into any standard language, like DDL, XML. At this point converting to XML would be the ideal but I am open to DDL format also.
I would love to hear any feedback about "If this is even possible
or not ?".
thanks,
tinkiSchema can be in a SQL script form. So, ideally this tool that
I am looking for will convery this SQL script info to DDL or
XML.
Thanks in advance,
Tinki|||Have you tried Enterprise Manager?|||DDL, or Data Definition Language, is RDBMS-specific, meaning that it is not like COBOL, VBScript, or XML. DDL for Oracle will look differently than DDL for Sybase for the same set of tables, while certain objects in Oracle will not even be found in Sybase. XML on the other hand is a standard.
Try DBArtisan from Embarcadero. Even though it only has an option "Extract Data as XML", it also allows you to generate the schema portion of your data.|||mySQL and SQL Server have very similar DDLs|||I'm glad|||Originally posted by Paul Young
Have you tried Enterprise Manager?
yes, I have tried Enterprise manager. It lets me export data to SQL script (which is very database specific). what I am looking at something
which can convert this SQL script to something more wodely accepted as a general standard so that my rest of the code does not get too
much database dependent.
tinki|||Originally posted by rdjabarov
DDL, or Data Definition Language, is RDBMS-specific, meaning that it is not like COBOL, VBScript, or XML. DDL for Oracle will look differently than DDL for Sybase for the same set of tables, while certain objects in Oracle will not even be found in Sybase. XML on the other hand is a standard.
Try DBArtisan from Embarcadero. Even though it only has an option "Extract Data as XML", it also allows you to generate the schema portion of your data.
thanks rdjabarov, I will try DBArtisan.
I have tried XMLSPY but it couldn't get to export schema. It was exporting only the data.
tinki.|||Originally posted by rdjabarov
I'm glad
I'm glad your glad!
Should have read tinki's post a little closer, thought he wanted a MSSQL to MYSQL convertion.|||DBArtisan is somewhat pricy, but a must-have tool. Also does lots of other things.|||Originally posted by tinki
what I am looking at something which can convert this SQL script to something more wodely accepted as a general standard so that my rest of the code does not get too much database dependent.
tinki
Are you actually looking for a db-independent db structure, rather than exporting your current db structure? Then, you should take these recommendations into account:
1) use short (table & field) identifiers (Informix 7, for instance, has a maximum length of 18, and Oracle 40)
2) use just characters, digits and the underscore for your identifiers
3) use just ANSI SQL data types, and not, for example, BOOLEAN (BIT)
...
I'm sure that the real gurus in this forum can easely enhance this list.
However, using a data model with no db-specific properties makes it easy to build a db-independent application, with or without a middleware encapsulating db-specific properties.
I am wondering if any of the DB Gurus out there help me finding a way to export mssql SCHEMA (not the actual data) into any standard language, like DDL, XML. At this point converting to XML would be the ideal but I am open to DDL format also.
I would love to hear any feedback about "If this is even possible
or not ?".
thanks,
tinkiSchema can be in a SQL script form. So, ideally this tool that
I am looking for will convery this SQL script info to DDL or
XML.
Thanks in advance,
Tinki|||Have you tried Enterprise Manager?|||DDL, or Data Definition Language, is RDBMS-specific, meaning that it is not like COBOL, VBScript, or XML. DDL for Oracle will look differently than DDL for Sybase for the same set of tables, while certain objects in Oracle will not even be found in Sybase. XML on the other hand is a standard.
Try DBArtisan from Embarcadero. Even though it only has an option "Extract Data as XML", it also allows you to generate the schema portion of your data.|||mySQL and SQL Server have very similar DDLs|||I'm glad|||Originally posted by Paul Young
Have you tried Enterprise Manager?
yes, I have tried Enterprise manager. It lets me export data to SQL script (which is very database specific). what I am looking at something
which can convert this SQL script to something more wodely accepted as a general standard so that my rest of the code does not get too
much database dependent.
tinki|||Originally posted by rdjabarov
DDL, or Data Definition Language, is RDBMS-specific, meaning that it is not like COBOL, VBScript, or XML. DDL for Oracle will look differently than DDL for Sybase for the same set of tables, while certain objects in Oracle will not even be found in Sybase. XML on the other hand is a standard.
Try DBArtisan from Embarcadero. Even though it only has an option "Extract Data as XML", it also allows you to generate the schema portion of your data.
thanks rdjabarov, I will try DBArtisan.
I have tried XMLSPY but it couldn't get to export schema. It was exporting only the data.
tinki.|||Originally posted by rdjabarov
I'm glad
I'm glad your glad!
Should have read tinki's post a little closer, thought he wanted a MSSQL to MYSQL convertion.|||DBArtisan is somewhat pricy, but a must-have tool. Also does lots of other things.|||Originally posted by tinki
what I am looking at something which can convert this SQL script to something more wodely accepted as a general standard so that my rest of the code does not get too much database dependent.
tinki
Are you actually looking for a db-independent db structure, rather than exporting your current db structure? Then, you should take these recommendations into account:
1) use short (table & field) identifiers (Informix 7, for instance, has a maximum length of 18, and Oracle 40)
2) use just characters, digits and the underscore for your identifiers
3) use just ANSI SQL data types, and not, for example, BOOLEAN (BIT)
...
I'm sure that the real gurus in this forum can easely enhance this list.
However, using a data model with no db-specific properties makes it easy to build a db-independent application, with or without a middleware encapsulating db-specific properties.
Saturday, February 25, 2012
Caliing a webservice from a trigger
Please,
is there a way i can execute a webservice from within a SQL Server 2k
Trigger? Sending and receiving XML values?
Tks,
Fabiano
This is not directly supported or recommended.
You probably could write your own custom code using an extended stored proc
or using sp_OA stored procs.
Best regards
Michael
"Fabiano" <a@.a.com.br> wrote in message
news:%23CzqpXqlEHA.1244@.TK2MSFTNGP15.phx.gbl...
> Please,
> is there a way i can execute a webservice from within a SQL Server 2k
> Trigger? Sending and receiving XML values?
> Tks,
> Fabiano
>
>
is there a way i can execute a webservice from within a SQL Server 2k
Trigger? Sending and receiving XML values?
Tks,
Fabiano
This is not directly supported or recommended.
You probably could write your own custom code using an extended stored proc
or using sp_OA stored procs.
Best regards
Michael
"Fabiano" <a@.a.com.br> wrote in message
news:%23CzqpXqlEHA.1244@.TK2MSFTNGP15.phx.gbl...
> Please,
> is there a way i can execute a webservice from within a SQL Server 2k
> Trigger? Sending and receiving XML values?
> Tks,
> Fabiano
>
>
Friday, February 24, 2012
Calculations in Matrix report
I have a Report Designer problem, whereby I need to perform a calculation on
each row of data laid out in a crosstab.
My datasource is a flat xml file (read into a dataset), and consists of the
following repeat base units:-
<base>
<StartDate>2006-04-10</StartDate>
<EndDate>2006-04-17</EndDate>
<Category>Books</Category>
<Amount>100</Amount>
</base>
<base>
<StartDate>2006-04-18</StartDate>
<EndDate>2006-04-25</EndDate>
<Category>CDs</Category>
<Amount>200</Amount>
</base>
...etc
I need to present this in the following way:-
Week 1 Week 2
10th-17th April 18th-25th April Calculation
Category
Books 100 300 week2 - week1
CDs 200 400 week2 - week1
..etc
The two options I have in the designer toolbox are:-
1) To use a matrix - this allows variable column data (period start date to
period end date), with variable row data (category), to be laid out as a
crosstab, but I can find no way of identifying individual cells within the
matrix to use in the calculation at the end of each row. Matrices only
appear to support totals/subtotals'
2) To use a table - is it possible to present the repeat base units shown
above in the form of a table? I can find the max EndDate in the dataset, but
is there some method of filtering the data so I can work out (period + 1
week), (period + 2 weeks), etc and then reference individual cells in the
table for the calculation'
Neither approach appears to allow crosstab representation of the data, with
calculations on each row. Is this possible?
I would like to avoid having to assemble the raw data in the form of fixed
'buckets', i.e.
<base>
<Category>Books</Category>
<Period1>2006-04-10</Period1>
<Amount1>100</Amount1>
<Period2>2006-04-18</Period2>
<Amount2>200</Amount2>
<Period3>2006-04-26</Period3>
<Amount3>400</Amount3>
</base>
<base>
<Category>CDs</Category>
<Period1>2006-04-10</Period1>
<Amount1>100</Amount1>
<Period2>2006-04-18</Period2>
<Amount2>200</Amount2>
<Period3>2006-04-26</Period3>
<Amount3>400</Amount3>
</base>
...etc
Can anyone see a solution?Hi Pete,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my undestanding of this issue is : You want to add a
Calculation column in the Matrix or Table region. If I misunderstood your
concern, please feel free to let me know.
Based on my scope, I think you could add a calculated field in the dataset
and then try to present this field in the layout:
1. In the Data page in the Report Designer, click the Edit Selected dataset
button.
2. In the Dataset Dialogbox, please click the Fields Page and then type the
Field name, choose the Type as Calculated Field and then type the
Expression you want to use.
After doing this, I think you could put the calculated field in any data
region.
Hope this information will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your prompt response Wei.
Unfortunately, my issue is not as simple as adding a calculation to the
matrix/table.
My data is represented by a crosstab. I wish to add a calculation column,
which is an expression that references two preceding cells in the same row,
for example...
---Calculation
Row1 Cell1 Cell2 Cell3 Cell4 Cell4/Cell3
---
This does not appear possible with a matrix. And because I have variable
columns, it does not appear possible to lay the data out in tabular form
either (which would allow me to reference individual cells).
Can you see a solution to this more complex issue?
Thanks
Pete
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:SaUsE8MoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
> Hi Pete,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my undestanding of this issue is : You want to add
> a
> Calculation column in the Matrix or Table region. If I misunderstood your
> concern, please feel free to let me know.
> Based on my scope, I think you could add a calculated field in the dataset
> and then try to present this field in the layout:
> 1. In the Data page in the Report Designer, click the Edit Selected
> dataset
> button.
> 2. In the Dataset Dialogbox, please click the Fields Page and then type
> the
> Field name, choose the Type as Calculated Field and then type the
> Expression you want to use.
> After doing this, I think you could put the calculated field in any data
> region.
> Hope this information will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Pete,
Thank you for the update.
I would like to know what do you mean about the variable columns. Since
Reporting Services use dataset to get the data from the datasource, I would
like to know how you implement variable columns in the dataset.
If the field name does not consist, I don't think you could refer any field
in the Expression. So please let me know the exactly meaning of "variable
columns" so that I may provide further assistance. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
The dataset generated by our datasource consists of the following fields:
StartDate
EndDate
Category
Amount
i.e. the data is periodic (weekly). We need to present this as follows:
Week 1 Week 2 Week 3
10th-17th April 18th-25th April 26th-3rd
Calculation
Category
Books 100 300 200
week3 - week2
CDs 200 400 150
week3 - week2
..etc
We can do this using a matrix, with 3 groupings: Category (row), StartDate
(column), EndDate (column).
What I meant was the columns are variable in Start/EndDate. But we know no
way of performing the calculation. Is this possible?
Or is there a way to do it if we use a table instead of a matrix?
Thanks in advance
Pete
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:7vnx8XOoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
> Hi Pete,
> Thank you for the update.
> I would like to know what do you mean about the variable columns. Since
> Reporting Services use dataset to get the data from the datasource, I
> would
> like to know how you implement variable columns in the dataset.
> If the field name does not consist, I don't think you could refer any
> field
> in the Expression. So please let me know the exactly meaning of "variable
> columns" so that I may provide further assistance. Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Sorry, my last post might be confusing... the matrix should look like:
Week 1 Week 2 Week 3
10th-17th 18th-25th 26th-3rd Calculation
Category
Books 100 300 200 week3 - week2
CDs 200 400 150 week3 - week2
..etc
"Pete D" <peted@.community.nospam> wrote in message
news:u7enVZQoGHA.4176@.TK2MSFTNGP05.phx.gbl...
> Hi Wei,
> The dataset generated by our datasource consists of the following fields:
> StartDate
> EndDate
> Category
> Amount
> i.e. the data is periodic (weekly). We need to present this as follows:
> Week 1 Week 2 Week 3
> 10th-17th April 18th-25th April 26th-3rd
> Calculation
> Category
> Books 100 300 200
> week3 - week2
> CDs 200 400 150
> week3 - week2
> ..etc
> We can do this using a matrix, with 3 groupings: Category (row), StartDate
> (column), EndDate (column).
> What I meant was the columns are variable in Start/EndDate. But we know no
> way of performing the calculation. Is this possible?
> Or is there a way to do it if we use a table instead of a matrix?
> Thanks in advance
> Pete
>
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
> news:7vnx8XOoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
>> Hi Pete,
>> Thank you for the update.
>> I would like to know what do you mean about the variable columns. Since
>> Reporting Services use dataset to get the data from the datasource, I
>> would
>> like to know how you implement variable columns in the dataset.
>> If the field name does not consist, I don't think you could refer any
>> field
>> in the Expression. So please let me know the exactly meaning of "variable
>> columns" so that I may provide further assistance. Thank you!
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi Pete,
Thank you for the update. I would like to get your report file to do more
research.
Would you pleae send the report file to me?
You may send to me directly. I understand the information may be sensitive
to you, my direct email address is weilu@.ONLINE.microsoft.com ( Please
remove ONLINE when you send the email ), you may send the file to me
directly and I will keep it secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Are you able to solve this issue with Matrix, i have the similar kind
of requirement. If yes pls let me know how could you achieve it
FYI: my req is as mentioned( hope i am clear, else let me know)
Thanks in advance..
Venkat
*******************
Hello,
I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..
my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..
the report should look something like this (M1, M2 are model_id's)
Div/Sec M1 M2 Variance % Difference
----==AD--
O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91
and my query is :
select div_sec, model_ID, model_time from table1
the output of my query is below( seperated by commas)
div_sec, model_ID, model_time
O3580,M1,0.7
O3580,M2,1.47
8040 ,M1,1.33
8040 ,M2,1.33
8110 ,M1,9.98
8110 ,M2,14.47
11210 ,M1,5.44
11210 ,M2,6.36
Any help is greatly appreciated.
Thanks,
Venkat
Wei Lu [MSFT] wrote:
> Hi Pete,
> Thank you for the update. I would like to get your report file to do more
> research.
> Would you pleae send the report file to me?
> You may send to me directly. I understand the information may be sensitive
> to you, my direct email address is weilu@.ONLINE.microsoft.com ( Please
> remove ONLINE when you send the email ), you may send the file to me
> directly and I will keep it secure.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> This posting is provided "AS IS" with no warranties, and confers no right=s=2E|||Hi Pete,
Thank you for the file.
After some research, I found that we could not meet the requirement in
Matrix. The Static column in Matrix does not meet your requirement.
As for the table control, it is not suitable to use table as a dynamic
columns data region.
I will try to contact internal to check whether there is an suitable
workaround for you. I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Appreciate if you can look into my problem too(its up in this thread)..
I think its almost the same.
Let me know if you find any solution for this issue. This is a Top
reqirement for lots of my reports.
Thanks,
Venkat
Wei Lu [MSFT] wrote:
> Hi Pete,
> Thank you for the file.
> After some research, I found that we could not meet the requirement in
> Matrix. The Static column in Matrix does not meet your requirement.
> As for the table control, it is not suitable to use table as a dynamic
> columns data region.
> I will try to contact internal to check whether there is an suitable
> workaround for you. I appreciate your patience.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.|||I've posted details of my report/requirement to Wei to determine whether
there's a solution to my problem.
Its seems that Venkat and "dbro101" both have very similar issues (re:
separate postings on 12th July)
If I become aware of a solution I'll post details here.
Pete
<venkat.oar@.gmail.com> wrote in message
news:1152883485.115457.194350@.h48g2000cwc.googlegroups.com...
> Hi Wei,
> Appreciate if you can look into my problem too(its up in this thread)..
> I think its almost the same.
> Let me know if you find any solution for this issue. This is a Top
> reqirement for lots of my reports.
> Thanks,
> Venkat
> Wei Lu [MSFT] wrote:
>> Hi Pete,
>> Thank you for the file.
>> After some research, I found that we could not meet the requirement in
>> Matrix. The Static column in Matrix does not meet your requirement.
>> As for the table control, it is not suitable to use table as a dynamic
>> columns data region.
>> I will try to contact internal to check whether there is an suitable
>> workaround for you. I appreciate your patience.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi Pete,
Thank you for your followup. Since Wei is absent due to some urgent issues,
I'm continueing to help you on this issue. I've got the problem description
and sample report files from wei and will perform some research on them. I
will update you as soon as possible.
Thank you for the patience and understanding.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead|||Hello Pete,
Sorry for keep you waiting. After some further research, we've come out to
the same results as Wei has told you in the previous message, the built-in
reporting service report items(Table or Matrix) are not able to accomplish
the task. I've also involved some other SQL engineers to discuss the issue
and they've help confirmed on this. So far they pointed out that it may be
possible to use a custom data processing extension to precaculate the
additional results, and the key point is that you need to correctly pass
the parameters into the custom data processing engine. I'm sorry that we
haven't been able to help you further on this. If there is anything else we
can help, please feel free to let me know.
Thanks for your understanding!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
I was just wondering if there was any progress regarding this subject.
I am sitting with a similar problem and am unable to find a workaround.
Thanks for all the effort that has been put into solving the problem thus far.
"Steven Cheng[MSFT]" wrote:
> Hello Pete,
> Sorry for keep you waiting. After some further research, we've come out to
> the same results as Wei has told you in the previous message, the built-in
> reporting service report items(Table or Matrix) are not able to accomplish
> the task. I've also involved some other SQL engineers to discuss the issue
> and they've help confirmed on this. So far they pointed out that it may be
> possible to use a custom data processing extension to precaculate the
> additional results, and the key point is that you need to correctly pass
> the parameters into the custom data processing engine. I'm sorry that we
> haven't been able to help you further on this. If there is anything else we
> can help, please feel free to let me know.
> Thanks for your understanding!
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||I know you can put a percent of total dim calculation on a dynamic matrix
report by using the year matrix group as a component of the calculation, i.e,
"matrix1_Year". See Brian Larson's Delivering Business Intelligence book
page 596 - 597. I wish there were an example of doing a variance column, but
if it can do this calculated field, then it looks like using the same
approach could do others. This example is based on a matrix report sourced
from an Analysis Services 2005 cube.
"Glaciered Pyro" wrote:
> Hi
> I was just wondering if there was any progress regarding this subject.
> I am sitting with a similar problem and am unable to find a workaround.
> Thanks for all the effort that has been put into solving the problem thus far.
> "Steven Cheng[MSFT]" wrote:
> > Hello Pete,
> >
> > Sorry for keep you waiting. After some further research, we've come out to
> > the same results as Wei has told you in the previous message, the built-in
> > reporting service report items(Table or Matrix) are not able to accomplish
> > the task. I've also involved some other SQL engineers to discuss the issue
> > and they've help confirmed on this. So far they pointed out that it may be
> > possible to use a custom data processing extension to precaculate the
> > additional results, and the key point is that you need to correctly pass
> > the parameters into the custom data processing engine. I'm sorry that we
> > haven't been able to help you further on this. If there is anything else we
> > can help, please feel free to let me know.
> >
> > Thanks for your understanding!
> >
> > Sincerely,
> >
> > Steven Cheng
> >
> > Microsoft MSDN Online Support Lead
> >
> > ==================================================> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||I Have found that the easiest solution to this problem is
to write custom code using vb.net or c#. Though it might
not be the most efficient... it works. Just add a Table next to
the matrix, write the code to collect the data and calculate
whatever you need to, then simply call it from the table...
each row of data laid out in a crosstab.
My datasource is a flat xml file (read into a dataset), and consists of the
following repeat base units:-
<base>
<StartDate>2006-04-10</StartDate>
<EndDate>2006-04-17</EndDate>
<Category>Books</Category>
<Amount>100</Amount>
</base>
<base>
<StartDate>2006-04-18</StartDate>
<EndDate>2006-04-25</EndDate>
<Category>CDs</Category>
<Amount>200</Amount>
</base>
...etc
I need to present this in the following way:-
Week 1 Week 2
10th-17th April 18th-25th April Calculation
Category
Books 100 300 week2 - week1
CDs 200 400 week2 - week1
..etc
The two options I have in the designer toolbox are:-
1) To use a matrix - this allows variable column data (period start date to
period end date), with variable row data (category), to be laid out as a
crosstab, but I can find no way of identifying individual cells within the
matrix to use in the calculation at the end of each row. Matrices only
appear to support totals/subtotals'
2) To use a table - is it possible to present the repeat base units shown
above in the form of a table? I can find the max EndDate in the dataset, but
is there some method of filtering the data so I can work out (period + 1
week), (period + 2 weeks), etc and then reference individual cells in the
table for the calculation'
Neither approach appears to allow crosstab representation of the data, with
calculations on each row. Is this possible?
I would like to avoid having to assemble the raw data in the form of fixed
'buckets', i.e.
<base>
<Category>Books</Category>
<Period1>2006-04-10</Period1>
<Amount1>100</Amount1>
<Period2>2006-04-18</Period2>
<Amount2>200</Amount2>
<Period3>2006-04-26</Period3>
<Amount3>400</Amount3>
</base>
<base>
<Category>CDs</Category>
<Period1>2006-04-10</Period1>
<Amount1>100</Amount1>
<Period2>2006-04-18</Period2>
<Amount2>200</Amount2>
<Period3>2006-04-26</Period3>
<Amount3>400</Amount3>
</base>
...etc
Can anyone see a solution?Hi Pete,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my undestanding of this issue is : You want to add a
Calculation column in the Matrix or Table region. If I misunderstood your
concern, please feel free to let me know.
Based on my scope, I think you could add a calculated field in the dataset
and then try to present this field in the layout:
1. In the Data page in the Report Designer, click the Edit Selected dataset
button.
2. In the Dataset Dialogbox, please click the Fields Page and then type the
Field name, choose the Type as Calculated Field and then type the
Expression you want to use.
After doing this, I think you could put the calculated field in any data
region.
Hope this information will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your prompt response Wei.
Unfortunately, my issue is not as simple as adding a calculation to the
matrix/table.
My data is represented by a crosstab. I wish to add a calculation column,
which is an expression that references two preceding cells in the same row,
for example...
---Calculation
Row1 Cell1 Cell2 Cell3 Cell4 Cell4/Cell3
---
This does not appear possible with a matrix. And because I have variable
columns, it does not appear possible to lay the data out in tabular form
either (which would allow me to reference individual cells).
Can you see a solution to this more complex issue?
Thanks
Pete
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:SaUsE8MoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
> Hi Pete,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my undestanding of this issue is : You want to add
> a
> Calculation column in the Matrix or Table region. If I misunderstood your
> concern, please feel free to let me know.
> Based on my scope, I think you could add a calculated field in the dataset
> and then try to present this field in the layout:
> 1. In the Data page in the Report Designer, click the Edit Selected
> dataset
> button.
> 2. In the Dataset Dialogbox, please click the Fields Page and then type
> the
> Field name, choose the Type as Calculated Field and then type the
> Expression you want to use.
> After doing this, I think you could put the calculated field in any data
> region.
> Hope this information will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Pete,
Thank you for the update.
I would like to know what do you mean about the variable columns. Since
Reporting Services use dataset to get the data from the datasource, I would
like to know how you implement variable columns in the dataset.
If the field name does not consist, I don't think you could refer any field
in the Expression. So please let me know the exactly meaning of "variable
columns" so that I may provide further assistance. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
The dataset generated by our datasource consists of the following fields:
StartDate
EndDate
Category
Amount
i.e. the data is periodic (weekly). We need to present this as follows:
Week 1 Week 2 Week 3
10th-17th April 18th-25th April 26th-3rd
Calculation
Category
Books 100 300 200
week3 - week2
CDs 200 400 150
week3 - week2
..etc
We can do this using a matrix, with 3 groupings: Category (row), StartDate
(column), EndDate (column).
What I meant was the columns are variable in Start/EndDate. But we know no
way of performing the calculation. Is this possible?
Or is there a way to do it if we use a table instead of a matrix?
Thanks in advance
Pete
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:7vnx8XOoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
> Hi Pete,
> Thank you for the update.
> I would like to know what do you mean about the variable columns. Since
> Reporting Services use dataset to get the data from the datasource, I
> would
> like to know how you implement variable columns in the dataset.
> If the field name does not consist, I don't think you could refer any
> field
> in the Expression. So please let me know the exactly meaning of "variable
> columns" so that I may provide further assistance. Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Sorry, my last post might be confusing... the matrix should look like:
Week 1 Week 2 Week 3
10th-17th 18th-25th 26th-3rd Calculation
Category
Books 100 300 200 week3 - week2
CDs 200 400 150 week3 - week2
..etc
"Pete D" <peted@.community.nospam> wrote in message
news:u7enVZQoGHA.4176@.TK2MSFTNGP05.phx.gbl...
> Hi Wei,
> The dataset generated by our datasource consists of the following fields:
> StartDate
> EndDate
> Category
> Amount
> i.e. the data is periodic (weekly). We need to present this as follows:
> Week 1 Week 2 Week 3
> 10th-17th April 18th-25th April 26th-3rd
> Calculation
> Category
> Books 100 300 200
> week3 - week2
> CDs 200 400 150
> week3 - week2
> ..etc
> We can do this using a matrix, with 3 groupings: Category (row), StartDate
> (column), EndDate (column).
> What I meant was the columns are variable in Start/EndDate. But we know no
> way of performing the calculation. Is this possible?
> Or is there a way to do it if we use a table instead of a matrix?
> Thanks in advance
> Pete
>
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
> news:7vnx8XOoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
>> Hi Pete,
>> Thank you for the update.
>> I would like to know what do you mean about the variable columns. Since
>> Reporting Services use dataset to get the data from the datasource, I
>> would
>> like to know how you implement variable columns in the dataset.
>> If the field name does not consist, I don't think you could refer any
>> field
>> in the Expression. So please let me know the exactly meaning of "variable
>> columns" so that I may provide further assistance. Thank you!
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi Pete,
Thank you for the update. I would like to get your report file to do more
research.
Would you pleae send the report file to me?
You may send to me directly. I understand the information may be sensitive
to you, my direct email address is weilu@.ONLINE.microsoft.com ( Please
remove ONLINE when you send the email ), you may send the file to me
directly and I will keep it secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Are you able to solve this issue with Matrix, i have the similar kind
of requirement. If yes pls let me know how could you achieve it
FYI: my req is as mentioned( hope i am clear, else let me know)
Thanks in advance..
Venkat
*******************
Hello,
I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..
my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..
the report should look something like this (M1, M2 are model_id's)
Div/Sec M1 M2 Variance % Difference
----==AD--
O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91
and my query is :
select div_sec, model_ID, model_time from table1
the output of my query is below( seperated by commas)
div_sec, model_ID, model_time
O3580,M1,0.7
O3580,M2,1.47
8040 ,M1,1.33
8040 ,M2,1.33
8110 ,M1,9.98
8110 ,M2,14.47
11210 ,M1,5.44
11210 ,M2,6.36
Any help is greatly appreciated.
Thanks,
Venkat
Wei Lu [MSFT] wrote:
> Hi Pete,
> Thank you for the update. I would like to get your report file to do more
> research.
> Would you pleae send the report file to me?
> You may send to me directly. I understand the information may be sensitive
> to you, my direct email address is weilu@.ONLINE.microsoft.com ( Please
> remove ONLINE when you send the email ), you may send the file to me
> directly and I will keep it secure.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> This posting is provided "AS IS" with no warranties, and confers no right=s=2E|||Hi Pete,
Thank you for the file.
After some research, I found that we could not meet the requirement in
Matrix. The Static column in Matrix does not meet your requirement.
As for the table control, it is not suitable to use table as a dynamic
columns data region.
I will try to contact internal to check whether there is an suitable
workaround for you. I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Appreciate if you can look into my problem too(its up in this thread)..
I think its almost the same.
Let me know if you find any solution for this issue. This is a Top
reqirement for lots of my reports.
Thanks,
Venkat
Wei Lu [MSFT] wrote:
> Hi Pete,
> Thank you for the file.
> After some research, I found that we could not meet the requirement in
> Matrix. The Static column in Matrix does not meet your requirement.
> As for the table control, it is not suitable to use table as a dynamic
> columns data region.
> I will try to contact internal to check whether there is an suitable
> workaround for you. I appreciate your patience.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.|||I've posted details of my report/requirement to Wei to determine whether
there's a solution to my problem.
Its seems that Venkat and "dbro101" both have very similar issues (re:
separate postings on 12th July)
If I become aware of a solution I'll post details here.
Pete
<venkat.oar@.gmail.com> wrote in message
news:1152883485.115457.194350@.h48g2000cwc.googlegroups.com...
> Hi Wei,
> Appreciate if you can look into my problem too(its up in this thread)..
> I think its almost the same.
> Let me know if you find any solution for this issue. This is a Top
> reqirement for lots of my reports.
> Thanks,
> Venkat
> Wei Lu [MSFT] wrote:
>> Hi Pete,
>> Thank you for the file.
>> After some research, I found that we could not meet the requirement in
>> Matrix. The Static column in Matrix does not meet your requirement.
>> As for the table control, it is not suitable to use table as a dynamic
>> columns data region.
>> I will try to contact internal to check whether there is an suitable
>> workaround for you. I appreciate your patience.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi Pete,
Thank you for your followup. Since Wei is absent due to some urgent issues,
I'm continueing to help you on this issue. I've got the problem description
and sample report files from wei and will perform some research on them. I
will update you as soon as possible.
Thank you for the patience and understanding.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead|||Hello Pete,
Sorry for keep you waiting. After some further research, we've come out to
the same results as Wei has told you in the previous message, the built-in
reporting service report items(Table or Matrix) are not able to accomplish
the task. I've also involved some other SQL engineers to discuss the issue
and they've help confirmed on this. So far they pointed out that it may be
possible to use a custom data processing extension to precaculate the
additional results, and the key point is that you need to correctly pass
the parameters into the custom data processing engine. I'm sorry that we
haven't been able to help you further on this. If there is anything else we
can help, please feel free to let me know.
Thanks for your understanding!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
I was just wondering if there was any progress regarding this subject.
I am sitting with a similar problem and am unable to find a workaround.
Thanks for all the effort that has been put into solving the problem thus far.
"Steven Cheng[MSFT]" wrote:
> Hello Pete,
> Sorry for keep you waiting. After some further research, we've come out to
> the same results as Wei has told you in the previous message, the built-in
> reporting service report items(Table or Matrix) are not able to accomplish
> the task. I've also involved some other SQL engineers to discuss the issue
> and they've help confirmed on this. So far they pointed out that it may be
> possible to use a custom data processing extension to precaculate the
> additional results, and the key point is that you need to correctly pass
> the parameters into the custom data processing engine. I'm sorry that we
> haven't been able to help you further on this. If there is anything else we
> can help, please feel free to let me know.
> Thanks for your understanding!
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||I know you can put a percent of total dim calculation on a dynamic matrix
report by using the year matrix group as a component of the calculation, i.e,
"matrix1_Year". See Brian Larson's Delivering Business Intelligence book
page 596 - 597. I wish there were an example of doing a variance column, but
if it can do this calculated field, then it looks like using the same
approach could do others. This example is based on a matrix report sourced
from an Analysis Services 2005 cube.
"Glaciered Pyro" wrote:
> Hi
> I was just wondering if there was any progress regarding this subject.
> I am sitting with a similar problem and am unable to find a workaround.
> Thanks for all the effort that has been put into solving the problem thus far.
> "Steven Cheng[MSFT]" wrote:
> > Hello Pete,
> >
> > Sorry for keep you waiting. After some further research, we've come out to
> > the same results as Wei has told you in the previous message, the built-in
> > reporting service report items(Table or Matrix) are not able to accomplish
> > the task. I've also involved some other SQL engineers to discuss the issue
> > and they've help confirmed on this. So far they pointed out that it may be
> > possible to use a custom data processing extension to precaculate the
> > additional results, and the key point is that you need to correctly pass
> > the parameters into the custom data processing engine. I'm sorry that we
> > haven't been able to help you further on this. If there is anything else we
> > can help, please feel free to let me know.
> >
> > Thanks for your understanding!
> >
> > Sincerely,
> >
> > Steven Cheng
> >
> > Microsoft MSDN Online Support Lead
> >
> > ==================================================> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||I Have found that the easiest solution to this problem is
to write custom code using vb.net or c#. Though it might
not be the most efficient... it works. Just add a Table next to
the matrix, write the code to collect the data and calculate
whatever you need to, then simply call it from the table...
Subscribe to:
Posts (Atom)