Producing several letters based on the results of a query

I have created a single page letter using a number of text boxes and one
table, which is populated with static text along with some data from a single
row results set. This works perfectly when the results set only contains one
row, but when there are multiple data rows, the report still only contains
one page, whereas I expected there to be a page for every row.
A simple way to reproduce this is to create a report with a single text box
containing the value of one column from a query that returns multiple rows.
This results in a one page report, rather than a page for each row returned.
I'd be grateful if someone can help with this - I'm hoping there's a simple
solution, like setting a 'repeating region' property.
By the way, I've tried creating a single column table in the hope I could
put the entire contents of the letter into a cell of the table and that the
table row (and therefore the letter) would then repeat for each row of data,
but it doesn't appear to be possible to fix the size of a table row and have
multiple text boxes (and a table) inside it.
Thanks in advance,
David.Hey David,
If I understand you correctly, your trying to make, basically, a form
letter, that populates the variables from a query. For example
This is my test letter to "DAVID".
Where "DAVID" is generated from column/row in your Dataset. Why can't you
make a single cell table with all the text as you want it, except insert the
Field references you need (instead of textboxes)? For Example, the cell
would look like:
= "This is my test letter to " & Fields!ContactName.Value & ". " &
Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
etc...
Once this is done, you choose the "Group" properties and place a page break
after each group (or row in this case). This creates a new letter, each on
its own pages, for each row returned.
Michael C
"David C" wrote:
> I have created a single page letter using a number of text boxes and one
> table, which is populated with static text along with some data from a single
> row results set. This works perfectly when the results set only contains one
> row, but when there are multiple data rows, the report still only contains
> one page, whereas I expected there to be a page for every row.
> A simple way to reproduce this is to create a report with a single text box
> containing the value of one column from a query that returns multiple rows.
> This results in a one page report, rather than a page for each row returned.
> I'd be grateful if someone can help with this - I'm hoping there's a simple
> solution, like setting a 'repeating region' property.
> By the way, I've tried creating a single column table in the hope I could
> put the entire contents of the letter into a cell of the table and that the
> table row (and therefore the letter) would then repeat for each row of data,
> but it doesn't appear to be possible to fix the size of a table row and have
> multiple text boxes (and a table) inside it.
> Thanks in advance,
> David.|||Thanks Michael - it was the grouping that I needed. I actually stumbled
across the same answer last night in a TechNet article
(http://technet.microsoft.com/en-us/library/ms155816.aspx).
I put a List in first and then added a single column Table to it. I then
created a group expression on the list in Properties - General - Edit Details
Group - General, as follows: -
=Ceiling(RowNumber(Nothing)/1)
I also set 'Page break at end'.
Is this what you meant?
My problem now is that I want to make some of the text bold and I thought
I'd be able to do this using the Format command - something like Format("some
text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
that?
Thanks again.
David.
"Michael C" wrote:
> Hey David,
> If I understand you correctly, your trying to make, basically, a form
> letter, that populates the variables from a query. For example
> This is my test letter to "DAVID".
> Where "DAVID" is generated from column/row in your Dataset. Why can't you
> make a single cell table with all the text as you want it, except insert the
> Field references you need (instead of textboxes)? For Example, the cell
> would look like:
> = "This is my test letter to " & Fields!ContactName.Value & ". " &
> Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> etc...
> Once this is done, you choose the "Group" properties and place a page break
> after each group (or row in this case). This creates a new letter, each on
> its own pages, for each row returned.
> Michael C
> "David C" wrote:
> > I have created a single page letter using a number of text boxes and one
> > table, which is populated with static text along with some data from a single
> > row results set. This works perfectly when the results set only contains one
> > row, but when there are multiple data rows, the report still only contains
> > one page, whereas I expected there to be a page for every row.
> >
> > A simple way to reproduce this is to create a report with a single text box
> > containing the value of one column from a query that returns multiple rows.
> > This results in a one page report, rather than a page for each row returned.
> >
> > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > solution, like setting a 'repeating region' property.
> >
> > By the way, I've tried creating a single column table in the hope I could
> > put the entire contents of the letter into a cell of the table and that the
> > table row (and therefore the letter) would then repeat for each row of data,
> > but it doesn't appear to be possible to fix the size of a table row and have
> > multiple text boxes (and a table) inside it.
> >
> > Thanks in advance,
> >
> > David.|||Hey David,
Unfortunately I do not think there is a way, and if there i do not know
it. I had asked the forum recently about 2 fonts, 1 control but got no
responses.
You have done basically what I was describing. Glad it (sort of) worked for
you.
Michael
"David C" wrote:
> Thanks Michael - it was the grouping that I needed. I actually stumbled
> across the same answer last night in a TechNet article
> (http://technet.microsoft.com/en-us/library/ms155816.aspx).
> I put a List in first and then added a single column Table to it. I then
> created a group expression on the list in Properties - General - Edit Details
> Group - General, as follows: -
> =Ceiling(RowNumber(Nothing)/1)
> I also set 'Page break at end'.
> Is this what you meant?
> My problem now is that I want to make some of the text bold and I thought
> I'd be able to do this using the Format command - something like Format("some
> text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
> that?
> Thanks again.
> David.
>
> "Michael C" wrote:
> > Hey David,
> > If I understand you correctly, your trying to make, basically, a form
> > letter, that populates the variables from a query. For example
> >
> > This is my test letter to "DAVID".
> >
> > Where "DAVID" is generated from column/row in your Dataset. Why can't you
> > make a single cell table with all the text as you want it, except insert the
> > Field references you need (instead of textboxes)? For Example, the cell
> > would look like:
> >
> > = "This is my test letter to " & Fields!ContactName.Value & ". " &
> > Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> > etc...
> >
> > Once this is done, you choose the "Group" properties and place a page break
> > after each group (or row in this case). This creates a new letter, each on
> > its own pages, for each row returned.
> >
> > Michael C
> >
> > "David C" wrote:
> >
> > > I have created a single page letter using a number of text boxes and one
> > > table, which is populated with static text along with some data from a single
> > > row results set. This works perfectly when the results set only contains one
> > > row, but when there are multiple data rows, the report still only contains
> > > one page, whereas I expected there to be a page for every row.
> > >
> > > A simple way to reproduce this is to create a report with a single text box
> > > containing the value of one column from a query that returns multiple rows.
> > > This results in a one page report, rather than a page for each row returned.
> > >
> > > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > > solution, like setting a 'repeating region' property.
> > >
> > > By the way, I've tried creating a single column table in the hope I could
> > > put the entire contents of the letter into a cell of the table and that the
> > > table row (and therefore the letter) would then repeat for each row of data,
> > > but it doesn't appear to be possible to fix the size of a table row and have
> > > multiple text boxes (and a table) inside it.
> > >
> > > Thanks in advance,
> > >
> > > David.|||Hi Michael,
The text I wanted to be in bold was on a separate line, so in the end I just
put in a separate row in the table and set the row to bold. This worked fine,
presumably because the List control was handling the grouping.
Thanks again for your help.
David.
"Michael C" wrote:
> Hey David,
> Unfortunately I do not think there is a way, and if there i do not know
> it. I had asked the forum recently about 2 fonts, 1 control but got no
> responses.
> You have done basically what I was describing. Glad it (sort of) worked for
> you.
> Michael
> "David C" wrote:
> > Thanks Michael - it was the grouping that I needed. I actually stumbled
> > across the same answer last night in a TechNet article
> > (http://technet.microsoft.com/en-us/library/ms155816.aspx).
> >
> > I put a List in first and then added a single column Table to it. I then
> > created a group expression on the list in Properties - General - Edit Details
> > Group - General, as follows: -
> >
> > =Ceiling(RowNumber(Nothing)/1)
> >
> > I also set 'Page break at end'.
> >
> > Is this what you meant?
> >
> > My problem now is that I want to make some of the text bold and I thought
> > I'd be able to do this using the Format command - something like Format("some
> > text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
> > that?
> >
> > Thanks again.
> >
> > David.
> >
> >
> >
> > "Michael C" wrote:
> >
> > > Hey David,
> > > If I understand you correctly, your trying to make, basically, a form
> > > letter, that populates the variables from a query. For example
> > >
> > > This is my test letter to "DAVID".
> > >
> > > Where "DAVID" is generated from column/row in your Dataset. Why can't you
> > > make a single cell table with all the text as you want it, except insert the
> > > Field references you need (instead of textboxes)? For Example, the cell
> > > would look like:
> > >
> > > = "This is my test letter to " & Fields!ContactName.Value & ". " &
> > > Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> > > etc...
> > >
> > > Once this is done, you choose the "Group" properties and place a page break
> > > after each group (or row in this case). This creates a new letter, each on
> > > its own pages, for each row returned.
> > >
> > > Michael C
> > >
> > > "David C" wrote:
> > >
> > > > I have created a single page letter using a number of text boxes and one
> > > > table, which is populated with static text along with some data from a single
> > > > row results set. This works perfectly when the results set only contains one
> > > > row, but when there are multiple data rows, the report still only contains
> > > > one page, whereas I expected there to be a page for every row.
> > > >
> > > > A simple way to reproduce this is to create a report with a single text box
> > > > containing the value of one column from a query that returns multiple rows.
> > > > This results in a one page report, rather than a page for each row returned.
> > > >
> > > > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > > > solution, like setting a 'repeating region' property.
> > > >
> > > > By the way, I've tried creating a single column table in the hope I could
> > > > put the entire contents of the letter into a cell of the table and that the
> > > > table row (and therefore the letter) would then repeat for each row of data,
> > > > but it doesn't appear to be possible to fix the size of a table row and have
> > > > multiple text boxes (and a table) inside it.
> > > >
> > > > Thanks in advance,
> > > >
> > > > David.

Processor Licensing question

I have a dual processor system. One processor is used for boot operations
and the other is used for Applications. If I were to install SQL Single
Processior License on this machine would that be within the Licensing
restrictions as there is the one processor for Applications or would I need
another license? Thanks.
--
George Hester
_________________________________If I remember correctly, SQL will automatically limit itself when using
the per processor license. If not, you can set the CPU affinity to 1
to make sure it only uses 1.
George Hester wrote:
> I have a dual processor system. One processor is used for boot operations
> and the other is used for Applications. If I were to install SQL Single
> Processior License on this machine would that be within the Licensing
> restrictions as there is the one processor for Applications or would I need
> another license? Thanks.
> --
> George Hester
> _________________________________|||"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1150209890.143128.128400@.f14g2000cwb.googlegroups.com...
> If I remember correctly, SQL will automatically limit itself when using
> the per processor license. If not, you can set the CPU affinity to 1
> to make sure it only uses 1.
>
With Per-Proc licensing you must license SQL Server on all processers
visible to the OS instance where SQL is running.
If you want to run a single proc of SQL on a multi-proc box that is used for
multiple different workloads, you can use Virtual Server 2003 R2, install a
new OS that can only see one processor, and install a single-proc license of
SQL Server on that OS instance.
From:
Virtualization and Multiple Instances
http://www.microsoft.com/sql/howtobuy/multipleinstances.mspx
Each virtual operating environment running SQL Sever 2005 must have a
processor license for each processor that the virtual machine accesses. If a
copy of SQL Server is running on a physical operating environment, then
processor licenses are required for all of the processors on that physical
server.
David

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan