Help with layout of a 'details' report

I'm trying to layout a report with the data from specific rows placed
in TextBoxes, as I want to use the report for printing out details.
My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
QuestionID Answer
--
100 Some text
101 31-Jul-07
102 More text
etc.
I want to position the Answer for a particular QuestionID in a
TextBox, as follows:
[Answer for QuestionId 100] [Answer for QuestionId 101]
[ Answer for Question Id 102
]
I need to be able to vary the position and size of each TextBox, which
I'm not sure I can do if I embed them in a table/list.
I know that I can pivot the data in the query before I fill the
dataset, which would give me a single row with a column for each
question. However I'm dealing with about 100 question id and I was
hoping to avoid the complex pivot query etc (even using SQL Server
2005 Pivot function).
Cheers,
Canice.On Jul 31, 9:19 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> I'm trying to layout a report with the data from specific rows placed
> in TextBoxes, as I want to use the report for printing out details.
> My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> QuestionID Answer
> --
> 100 Some text
> 101 31-Jul-07
> 102 More text
> etc.
> I want to position the Answer for a particular QuestionID in a
> TextBox, as follows:
> [Answer for QuestionId 100] [Answer for QuestionId 101]
> [ Answer for Question Id 102
> ]
> I need to be able to vary the position and size of each TextBox, which
> I'm not sure I can do if I embed them in a table/list.
> I know that I can pivot the data in the query before I fill the
> dataset, which would give me a single row with a column for each
> question. However I'm dealing with about 100 question id and I was
> hoping to avoid the complex pivot query etc (even using SQL Server
> 2005 Pivot function).
> Cheers,
> Canice.
>From what you have described, you may want to have the report broken
into columns. You should use a table control and then follow these
steps to limit the records in the table for column handling.
1. In the 'Layout' view of BIDS, select the table.
2. Right click the table and select 'Properties.'
3. On the 'Groups' tab, select the 'Add...' button.
4. Below 'Group on:' and 'Expression' enter the following:
'=Ceiling(RowNumber(Nothing)/18)'
5. Select 'Page break at end'
5. Select 'OK' and 'OK' again.
[NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
NumberOfRowsPerColumn) ]
Then, to set the number of columns, you would do the following:
1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
2. Select 'Report Properties...' from the drop-down menu.
3. Select the 'Layout' tab.
4. Below 'Columns:' select the number of columns to split the report
into.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
>
> > I'm trying to layout a report with the data from specific rows placed
> > in TextBoxes, as I want to use the report for printing out details.
> > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > QuestionID Answer
> > --
> > 100 Some text
> > 101 31-Jul-07
> > 102 More text
> > etc.
> > I want to position the Answer for a particular QuestionID in a
> > TextBox, as follows:
> > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > [ Answer for Question Id 102
> > ]
> > I need to be able to vary the position and size of each TextBox, which
> > I'm not sure I can do if I embed them in a table/list.
> > I know that I can pivot the data in the query before I fill the
> > dataset, which would give me a single row with a column for each
> > question. However I'm dealing with about 100 question id and I was
> > hoping to avoid the complex pivot query etc (even using SQL Server
> > 2005 Pivot function).
> > Cheers,
> >Canice.
> >From what you have described, you may want to have the report broken
> into columns. You should use a table control and then follow these
> steps to limit the records in the table for column handling.
> 1. In the 'Layout' view of BIDS, select the table.
> 2. Right click the table and select 'Properties.'
> 3. On the 'Groups' tab, select the 'Add...' button.
> 4. Below 'Group on:' and 'Expression' enter the following:
> '=Ceiling(RowNumber(Nothing)/18)'
> 5. Select 'Page break at end'
> 5. Select 'OK' and 'OK' again.
> [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> NumberOfRowsPerColumn) ]
> Then, to set the number of columns, you would do the following:
> 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> 2. Select 'Report Properties...' from the drop-down menu.
> 3. Select the 'Layout' tab.
> 4. Below 'Columns:' select the number of columns to split the report
> into.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Hi Enrique,
Thats for the reply but I don't think your solution is what I was
looking for.
>From my (very basic) knowledge of SSRS, your solution will wrap the
DataSet returned into a number of columns/rows on multiple pages.
What I was looking for is a more free-form layout, where I can place
TextBox fields for specific answers anywhere on the form (this is for
a print layout). The expression for each TextBox would be as follows:
=IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
Where the value 42231 represents a particular answer, and each TextBox
would have a different question id.
I also need all the results on a single page, again so that I can use
it for printing.
Thanks,
Canice.|||On Aug 1, 9:22 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
>
> > On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
> > > I'm trying to layout a report with the data from specific rows placed
> > > in TextBoxes, as I want to use the report for printing out details.
> > > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > > QuestionID Answer
> > > --
> > > 100 Some text
> > > 101 31-Jul-07
> > > 102 More text
> > > etc.
> > > I want to position the Answer for a particular QuestionID in a
> > > TextBox, as follows:
> > > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > > [ Answer for Question Id 102
> > > ]
> > > I need to be able to vary the position and size of each TextBox, which
> > > I'm not sure I can do if I embed them in a table/list.
> > > I know that I can pivot the data in the query before I fill the
> > > dataset, which would give me a single row with a column for each
> > > question. However I'm dealing with about 100 question id and I was
> > > hoping to avoid the complex pivot query etc (even using SQL Server
> > > 2005 Pivot function).
> > > Cheers,
> > >Canice.
> > >From what you have described, you may want to have the report broken
> > into columns. You should use a table control and then follow these
> > steps to limit the records in the table for column handling.
> > 1. In the 'Layout' view of BIDS, select the table.
> > 2. Right click the table and select 'Properties.'
> > 3. On the 'Groups' tab, select the 'Add...' button.
> > 4. Below 'Group on:' and 'Expression' enter the following:
> > '=Ceiling(RowNumber(Nothing)/18)'
> > 5. Select 'Page break at end'
> > 5. Select 'OK' and 'OK' again.
> > [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> > NumberOfRowsPerColumn) ]
> > Then, to set the number of columns, you would do the following:
> > 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> > 2. Select 'Report Properties...' from the drop-down menu.
> > 3. Select the 'Layout' tab.
> > 4. Below 'Columns:' select the number of columns to split the report
> > into.
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> Hi Enrique,
> Thats for the reply but I don't think your solution is what I was
> looking for.
> >From my (very basic) knowledge of SSRS, your solution will wrap the
> DataSet returned into a number of columns/rows on multiple pages.
> What I was looking for is a more free-form layout, where I can place
> TextBox fields for specific answers anywhere on the form (this is for
> a print layout). The expression for each TextBox would be as follows:
> =IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
> Where the value 42231 represents a particular answer, and each TextBox
> would have a different question id.
> I also need all the results on a single page, again so that I can use
> it for printing.
> Thanks,
> Canice.
Unfortunately, this type of layout is not supported in SSRS. Aside
from the text boxes being individually placed (as you suggested) and
using expression references to the same or different datasets, there
are not very many options available. In order to make the expressions
work that you mentioned above, you will need to use aggregates (i.e.,
=IIf(Max(Fields!QuestionId.Value) = 42231, Fields!Answer.Value, "")).
Another option, but still not free form, is to use a table control and
play w/the standard expressions and border styles based on expressions
to give the appearance of free form. Of course, this can be a bit
tricky and will not completely fit your purpose. The closest thing I
can think of to your desired outcome (if PDF export is an option), is
an open source library alternative (iTextSharp) which has some good
documentation (http://sourceforge.net/projects/itextsharp/
http://itextsharp.sourceforge.net/tutorial/ ); however, you will need
to be comfortable w/C# or VB.NET and use the library as part of a
custom application. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 2, 12:16 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Aug 1, 9:22 am, Canice <canice.b...@.crestsolutions.ie> wrote:
>
> > On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
> > > On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
> > > > I'm trying to layout a report with the data from specific rows placed
> > > > in TextBoxes, as I want to use the report for printing out details.
> > > > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > > > QuestionID Answer
> > > > --
> > > > 100 Some text
> > > > 101 31-Jul-07
> > > > 102 More text
> > > > etc.
> > > > I want to position the Answer for a particular QuestionID in a
> > > > TextBox, as follows:
> > > > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > > > [ Answer for Question Id 102
> > > > ]
> > > > I need to be able to vary the position and size of each TextBox, which
> > > > I'm not sure I can do if I embed them in a table/list.
> > > > I know that I can pivot the data in the query before I fill the
> > > > dataset, which would give me a single row with a column for each
> > > > question. However I'm dealing with about 100 question id and I was
> > > > hoping to avoid the complex pivot query etc (even using SQL Server
> > > > 2005 Pivot function).
> > > > Cheers,
> > > >Canice.
> > > >From what you have described, you may want to have the report broken
> > > into columns. You should use a table control and then follow these
> > > steps to limit the records in the table for column handling.
> > > 1. In the 'Layout' view of BIDS, select the table.
> > > 2. Right click the table and select 'Properties.'
> > > 3. On the 'Groups' tab, select the 'Add...' button.
> > > 4. Below 'Group on:' and 'Expression' enter the following:
> > > '=Ceiling(RowNumber(Nothing)/18)'
> > > 5. Select 'Page break at end'
> > > 5. Select 'OK' and 'OK' again.
> > > [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> > > NumberOfRowsPerColumn) ]
> > > Then, to set the number of columns, you would do the following:
> > > 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> > > 2. Select 'Report Properties...' from the drop-down menu.
> > > 3. Select the 'Layout' tab.
> > > 4. Below 'Columns:' select the number of columns to split the report
> > > into.
> > > Hope this helps.
> > > Regards,
> > > Enrique Martinez
> > > Sr. Software Consultant
> > Hi Enrique,
> > Thats for the reply but I don't think your solution is what I was
> > looking for.
> > >From my (very basic) knowledge of SSRS, your solution will wrap the
> > DataSet returned into a number of columns/rows on multiple pages.
> > What I was looking for is a more free-form layout, where I can place
> > TextBox fields for specific answers anywhere on the form (this is for
> > a print layout). The expression for each TextBox would be as follows:
> > =IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
> > Where the value 42231 represents a particular answer, and each TextBox
> > would have a different question id.
> > I also need all the results on a single page, again so that I can use
> > it for printing.
> > Thanks,
> > Canice.
> Unfortunately, this type of layout is not supported in SSRS. Aside
> from the text boxes being individually placed (as you suggested) and
> using expression references to the same or different datasets, there
> are not very many options available. In order to make the expressions
> work that you mentioned above, you will need to use aggregates (i.e.,
> =IIf(Max(Fields!QuestionId.Value) = 42231, Fields!Answer.Value, "")).
> Another option, but still not free form, is to use a table control and
> play w/the standard expressions and border styles based on expressions
> to give the appearance of free form. Of course, this can be a bit
> tricky and will not completely fit your purpose. The closest thing I
> can think of to your desired outcome (if PDF export is an option), is
> an open source library alternative (iTextSharp) which has some good
> documentation (http://sourceforge.net/projects/itextsharp/http://itextsharp.sourceforge.net/tutorial/); however, you will need
> to be comfortable w/C# or VB.NET and use the library as part of a
> custom application. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Thanks again Enrique, at least you've eliminated that possibility.
I'm a C# developer so using the iTextSharp API shouldn't be a problem,
however I was hoping to avoid going down the PDF route from past
experiences.
Looks like I might have to do the pivot operation after all, or use
ASP.NET to do the layout (its from a web-app).
Cheers,
Canice.|||On Aug 2, 4:06 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> On Aug 2, 12:16 am, EMartinez <emartinez...@.gmail.com> wrote:
>
> > On Aug 1, 9:22 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> > > On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
> > > > On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
> > > > > I'm trying to layout a report with the data from specific rows placed
> > > > > in TextBoxes, as I want to use the report for printing out details.
> > > > > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > > > > QuestionID Answer
> > > > > --
> > > > > 100 Some text
> > > > > 101 31-Jul-07
> > > > > 102 More text
> > > > > etc.
> > > > > I want to position the Answer for a particular QuestionID in a
> > > > > TextBox, as follows:
> > > > > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > > > > [ Answer for Question Id 102
> > > > > ]
> > > > > I need to be able to vary the position and size of each TextBox, which
> > > > > I'm not sure I can do if I embed them in a table/list.
> > > > > I know that I can pivot the data in the query before I fill the
> > > > > dataset, which would give me a single row with a column for each
> > > > > question. However I'm dealing with about 100 question id and I was
> > > > > hoping to avoid the complex pivot query etc (even using SQL Server
> > > > > 2005 Pivot function).
> > > > > Cheers,
> > > > >Canice.
> > > > >From what you have described, you may want to have the report broken
> > > > into columns. You should use a table control and then follow these
> > > > steps to limit the records in the table for column handling.
> > > > 1. In the 'Layout' view of BIDS, select the table.
> > > > 2. Right click the table and select 'Properties.'
> > > > 3. On the 'Groups' tab, select the 'Add...' button.
> > > > 4. Below 'Group on:' and 'Expression' enter the following:
> > > > '=Ceiling(RowNumber(Nothing)/18)'
> > > > 5. Select 'Page break at end'
> > > > 5. Select 'OK' and 'OK' again.
> > > > [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> > > > NumberOfRowsPerColumn) ]
> > > > Then, to set the number of columns, you would do the following:
> > > > 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> > > > 2. Select 'Report Properties...' from the drop-down menu.
> > > > 3. Select the 'Layout' tab.
> > > > 4. Below 'Columns:' select the number of columns to split the report
> > > > into.
> > > > Hope this helps.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. Software Consultant
> > > Hi Enrique,
> > > Thats for the reply but I don't think your solution is what I was
> > > looking for.
> > > >From my (very basic) knowledge of SSRS, your solution will wrap the
> > > DataSet returned into a number of columns/rows on multiple pages.
> > > What I was looking for is a more free-form layout, where I can place
> > > TextBox fields for specific answers anywhere on the form (this is for
> > > a print layout). The expression for each TextBox would be as follows:
> > > =IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
> > > Where the value 42231 represents a particular answer, and each TextBox
> > > would have a different question id.
> > > I also need all the results on a single page, again so that I can use
> > > it for printing.
> > > Thanks,
> > > Canice.
> > Unfortunately, this type of layout is not supported in SSRS. Aside
> > from the text boxes being individually placed (as you suggested) and
> > using expression references to the same or different datasets, there
> > are not very many options available. In order to make the expressions
> > work that you mentioned above, you will need to use aggregates (i.e.,
> > =IIf(Max(Fields!QuestionId.Value) = 42231, Fields!Answer.Value, "")).
> > Another option, but still not free form, is to use a table control and
> > play w/the standard expressions and border styles based on expressions
> > to give the appearance of free form. Of course, this can be a bit
> > tricky and will not completely fit your purpose. The closest thing I
> > can think of to your desired outcome (if PDF export is an option), is
> > an open source library alternative (iTextSharp) which has some good
> > documentation (http://sourceforge.net/projects/itextsharp/http://itextsharp.sourcefo...however, you will need
> > to be comfortable w/C# or VB.NET and use the library as part of a
> > custom application. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> Thanks again Enrique, at least you've eliminated that possibility.
> I'm a C# developer so using the iTextSharp API shouldn't be a problem,
> however I was hoping to avoid going down the PDF route from past
> experiences.
> Looks like I might have to do the pivot operation after all, or use
> ASP.NET to do the layout (its from a web-app).
> Cheers,
> Canice.
You're welcome. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant