Hi,
I've Got two rectangles which contains report data. (Second one just under
the first one). Visibility of each of them is checked with dataset values.
The point is that I want the second rectangle to move at the top when the
first one's visibility is set to false.
Whatever I try, when the first one is hidden, the second one do not take its
place and I'm facing a huge blank section before it.
Is it possible to change rectangle position dynamically ? Is there a
solution for this problem ?
Thanks for you help.Found the trick.
Just have to insert each of my rectangle in a Table for which I set
visibility expression on row.
Franck wrote:
> Hi,
> I've Got two rectangles which contains report data. (Second one just under
> the first one). Visibility of each of them is checked with dataset values.
> The point is that I want the second rectangle to move at the top when the
> first one's visibility is set to false.
> Whatever I try, when the first one is hidden, the second one do not take its
> place and I'm facing a huge blank section before it.
> Is it possible to change rectangle position dynamically ? Is there a
> solution for this problem ?
> Thanks for you help.
Showing posts with label items. Show all posts
Showing posts with label items. Show all posts
Monday, March 19, 2012
Dynamic Heriarchical Data
I have a situation where I have a lot of dynamic heirarchical data. I work for a manufacturing company, and as such, items we produce have Bills-of Materials (BOM). Some of our BOMs have items on them that have BOMs as well. This nesting can go as deep as 20 levels. Some components on each of the BOMs have a flag set on them.
I need to know the best way to drill through all of these BOMs to get a list of all the items that are flagged.
I tried to sketch out what I'm talking about in the attached image.Originally posted by joshplummer
I have a situation where I have a lot of dynamic heirarchical data. I work for a manufacturing company, and as such, items we produce have Bills-of Materials (BOM). Some of our BOMs have items on them that have BOMs as well. This nesting can go as deep as 20 levels. Some components on each of the BOMs have a flag set on them.
I need to know the best way to drill through all of these BOMs to get a list of all the items that are flagged.
I tried to sketch out what I'm talking about in the attached image.
Can you provide us with DDL and some sample data to play around with ??|||I don't know if I can get exactly what you need. I can describe it further if that would help. It is fairly complex:
Table
-BOM_Master
Fields
-BOM Version [PK] (varchar(20))
-BOM ID [PK] - Is actually an Item # from our Inventory DB (varchar(20))
-BOM Date (Date)
Table
-BOM_Version
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM Version Date (Date)
Table
-BOM_Detail
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM ID [PK][FK-BOM_Master] (varchar(20))
-BOM Line [PK] (Int)
-BOM Line Description (varchar(50))
-BOM Flag (bit)
This is a simplified version of our actual tables, and since this is our business system, I shouldn't post DDL or data since the sample data we use is our real data, just in a test environment.|||First I am assuming that all components at each level have the same structure and that the BOM cannot be recursive (ie BOM1 cannot be called at a lower level); this makes logical sense BUT data entry is data entry.
If you create a parent-child relationship on each BOM the level which it is at could become irelevant.
By passing the BOM_ID of the first level you want to search, a recursive query (read CURSOR -- Sorry Brett) can return a recordset (temporary table? - sorry again Brett) including the search level defined as the recurrsion number in the CURSOR. This recordset can be used to build your output. The output would be based on passing the Child_BOM_ID as the parameter of the next query.
I don't have time right now to example the query but I am doing something similar for an organization hierarchy.|||You are correct. A BOM cannot call itself. So, BOM1 cannot have BOM1 as a component.
I thought about the recursive function, but haven't attempted it yet. I have done that before for a location heirarchy. But, for some reason, this seems more complicated, and I can't nail down exactly what to do.|||Transact-SQL Cookbook by Ales Spetic & Jonathan Gennick.
This book has a whole chapter devoted on hierarchies in SQL Server.
Check out www.oreilly.com for more information.
Hope this helps.|||You will need to create a reference table with BOM_ID,Child BOM_ID.
The BOM_ID can be duplicates to include multiple Children. the joint BOM_ID + Child BOM_ID will create a unique record.
When a BOM is added, the subsequent children must be added. Peferably this table should be populated with the subcomponents first then referenced by its parent component.
Running a CURSOR through this table will allow you to build all of the required BOM entries in a temporary table which can then be linked with the master components table to generate your output.
It is important that each "widget" is regarded as a potential parent and a potential child. This should not be any more complex than any other Cursor based query.
The only concren that I have is for the following example:
the inital BOM calls for 3 typeA screws a sublevel BOM calls for 2 more typeA screws.
Do they get reported seperately or within a common pool?|||Unfortunatley, I don't have control over how the data is created. I have to work with the structure that is created by the applicaion, which isn't very friendly with objects outside of it's own little world.|||Josh,
This is ugly but it could work.
The flag is actually irrelevant. The inner join of BOM_Detail to BOM_Master will supercede it (or match it)
It should be possible to do this but you will need to maintain three tables in your cursor:
the accumulated BOMS [ACCUM_BOM_DETAIL]
the BOMS to search on for children [SEARCH_BOM_DETAIL]
the BOMS returned from the search [RETURNED_BOM_DETAIL]
Thus, start with the initial BOM as the search in [SEARCH_BOM_DETAIL]
1 - WITH CURSOR, return all BOMS at the next level (append to [RETURNED_BOM_DETAIL])
2 -when CURSOR EOF ([SEARCH_BOM_DETAIL]) append [SEARCH_BOM_DETAIL] records into [ACCUM_BOM_DETAIL]
3 - clear [SEARCH_BOM_DETAIL]
4 - copy [RETURNED_BOM_DETAIL] into [SEARCH_BOM_DETAIL] (next
5 - clear [RETURNED_BOM_DETAIL]
run steps 1-5 until the SEARCH returns a NULL Recordset.
The result will be the BOM DETAIL for every sub level. You should be abe to manage the inner joins and versioning through your query.
Good Luck
Originally posted by joshplummer
I don't know if I can get exactly what you need. I can describe it further if that would help. It is fairly complex:
Table
-BOM_Master
Fields
-BOM Version [PK] (varchar(20))
-BOM ID [PK] - Is actually an Item # from our Inventory DB (varchar(20))
-BOM Date (Date)
Table
-BOM_Version
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM Version Date (Date)
Table
-BOM_Detail
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM ID [PK][FK-BOM_Master] (varchar(20))
-BOM Line [PK] (Int)
-BOM Line Description (varchar(50))
-BOM Flag (bit)
This is a simplified version of our actual tables, and since this is our business system, I shouldn't post DDL or data since the sample data we use is our real data, just in a test environment.|||Could you post some sample code that does what you're suggesting. I don't think I've done anything like that using cursors...
That sounds like a good idea. Any indications of performance? I know cursors can be a little cumbersome.|||Unfortunately I cannot.
Paid by the hour. and don't want to lose my contract. I'll see what I can do tonight but no promises.
Originally posted by joshplummer
Could you post some sample code that does what you're suggesting. I don't think I've done anything like that using cursors...
That sounds like a good idea. Any indications of performance? I know cursors can be a little cumbersome.|||You don't need a cursor to do this (unless you are paid by the hour).
In the BOM_Master table, you say that ID represents an item from you inventory. Is this the value that may also represent another BOM through which you need to drill down?
BOM_Version > BOM_Master > BOM_Detail|||The only issue is the BOM within a BOM within a BOM ...
The query needs to be recursive until there are no more BOMS (i.e. returned recordset is null). I agree that the internal queries will not need to be CURSORS, but to build the n layers of the hierarchy you need to rerun the query.
The biggest problem will be how to handle duplicates. For example a screw is a component of the main BOM and of a level 3 component.
Unfortunately for joshplummer this is nota simple task.
Originally posted by blindman
You don't need a cursor to do this (unless you are paid by the hour).
In the BOM_Master table, you say that ID represents an item from you inventory. Is this the value that may also represent another BOM through which you need to drill down?
BOM_Version > BOM_Master > BOM_Detail|||This can be accomplished by creating a table variable, seeding it with the initial value, and then running this single skeleton SQL statement:
while @.@.Rowcount > 0
insert into TableVariable
select Datatable.ChildData
from DataTable
inner join TableVariable on DataTable.ParentID = TableVariable.ID
where not exists (select ID from TableVariable where TableVariable.ID = Datatable.ChildData)|||I appreciate everyone's help with this. I was able to get the process working using a recursive cursor based stored proc. It's fairly simple, to be honest, and it's very quick. It screams through a 3000+ line BOM in less than a second. And, the results are spot on!
Once again, thanks for the help everyone.|||Good to hear.
Originally posted by joshplummer
I appreciate everyone's help with this. I was able to get the process working using a recursive cursor based stored proc. It's fairly simple, to be honest, and it's very quick. It screams through a 3000+ line BOM in less than a second. And, the results are spot on!
Once again, thanks for the help everyone.|||Cursors are crutches for VB programmers. Learn to use set-based operations.|||Blindman,
I appologize, I didn't see your original posting.
Your statements were correct.
If you have an alternative to the cursor, I'd love to see it.|||Not 100% sure about your table relationships and keys, but here is an example that returns the flagged BOM_Details for a specified BOM, including sub-BOMs:
--------------------
declare @.ParentBOM varchar(20)
declare @.BOM_List table
(BOM_Version varchar(20) Primary Key Clustered)
--First, seed the BOM_List table
insert into @.BOM_List
(BOM_Version)
select BOM_Version
from BOM_Master
where BOM_Version = @.ParentBOM
--This ensures that the BOM actually exists in the BOM_Master table
--Now collect all the sub-records
while @.@.Rowcount > 0 --This executes if the previous statement was successfull.
insert into @.BOM_List
(BOM_Version)
select BOM_Master.BOM_ID --The child record
from BOM_Master
inner join @.BOM_List BOM_List on BOM_Master.BOM_Version = BOM_List.BOM_Version
--This clause ensures that we don't try to insert a BOM more than once
where not exists (select * from @.BOM_List CurrentRecords where CurrentRecords.BOM_Version = BOM_Master.BOM_ID)
--Now you can join your BOM_List table to your details table:
select *
from BOM_Detail
inner join @.BOM_List BOM_List on BOM_Detail.BOM_Version = BOM_List.BOM_Version
where BOM_Detail.Flag = 1
--------------------
I need to know the best way to drill through all of these BOMs to get a list of all the items that are flagged.
I tried to sketch out what I'm talking about in the attached image.Originally posted by joshplummer
I have a situation where I have a lot of dynamic heirarchical data. I work for a manufacturing company, and as such, items we produce have Bills-of Materials (BOM). Some of our BOMs have items on them that have BOMs as well. This nesting can go as deep as 20 levels. Some components on each of the BOMs have a flag set on them.
I need to know the best way to drill through all of these BOMs to get a list of all the items that are flagged.
I tried to sketch out what I'm talking about in the attached image.
Can you provide us with DDL and some sample data to play around with ??|||I don't know if I can get exactly what you need. I can describe it further if that would help. It is fairly complex:
Table
-BOM_Master
Fields
-BOM Version [PK] (varchar(20))
-BOM ID [PK] - Is actually an Item # from our Inventory DB (varchar(20))
-BOM Date (Date)
Table
-BOM_Version
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM Version Date (Date)
Table
-BOM_Detail
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM ID [PK][FK-BOM_Master] (varchar(20))
-BOM Line [PK] (Int)
-BOM Line Description (varchar(50))
-BOM Flag (bit)
This is a simplified version of our actual tables, and since this is our business system, I shouldn't post DDL or data since the sample data we use is our real data, just in a test environment.|||First I am assuming that all components at each level have the same structure and that the BOM cannot be recursive (ie BOM1 cannot be called at a lower level); this makes logical sense BUT data entry is data entry.
If you create a parent-child relationship on each BOM the level which it is at could become irelevant.
By passing the BOM_ID of the first level you want to search, a recursive query (read CURSOR -- Sorry Brett) can return a recordset (temporary table? - sorry again Brett) including the search level defined as the recurrsion number in the CURSOR. This recordset can be used to build your output. The output would be based on passing the Child_BOM_ID as the parameter of the next query.
I don't have time right now to example the query but I am doing something similar for an organization hierarchy.|||You are correct. A BOM cannot call itself. So, BOM1 cannot have BOM1 as a component.
I thought about the recursive function, but haven't attempted it yet. I have done that before for a location heirarchy. But, for some reason, this seems more complicated, and I can't nail down exactly what to do.|||Transact-SQL Cookbook by Ales Spetic & Jonathan Gennick.
This book has a whole chapter devoted on hierarchies in SQL Server.
Check out www.oreilly.com for more information.
Hope this helps.|||You will need to create a reference table with BOM_ID,Child BOM_ID.
The BOM_ID can be duplicates to include multiple Children. the joint BOM_ID + Child BOM_ID will create a unique record.
When a BOM is added, the subsequent children must be added. Peferably this table should be populated with the subcomponents first then referenced by its parent component.
Running a CURSOR through this table will allow you to build all of the required BOM entries in a temporary table which can then be linked with the master components table to generate your output.
It is important that each "widget" is regarded as a potential parent and a potential child. This should not be any more complex than any other Cursor based query.
The only concren that I have is for the following example:
the inital BOM calls for 3 typeA screws a sublevel BOM calls for 2 more typeA screws.
Do they get reported seperately or within a common pool?|||Unfortunatley, I don't have control over how the data is created. I have to work with the structure that is created by the applicaion, which isn't very friendly with objects outside of it's own little world.|||Josh,
This is ugly but it could work.
The flag is actually irrelevant. The inner join of BOM_Detail to BOM_Master will supercede it (or match it)
It should be possible to do this but you will need to maintain three tables in your cursor:
the accumulated BOMS [ACCUM_BOM_DETAIL]
the BOMS to search on for children [SEARCH_BOM_DETAIL]
the BOMS returned from the search [RETURNED_BOM_DETAIL]
Thus, start with the initial BOM as the search in [SEARCH_BOM_DETAIL]
1 - WITH CURSOR, return all BOMS at the next level (append to [RETURNED_BOM_DETAIL])
2 -when CURSOR EOF ([SEARCH_BOM_DETAIL]) append [SEARCH_BOM_DETAIL] records into [ACCUM_BOM_DETAIL]
3 - clear [SEARCH_BOM_DETAIL]
4 - copy [RETURNED_BOM_DETAIL] into [SEARCH_BOM_DETAIL] (next
5 - clear [RETURNED_BOM_DETAIL]
run steps 1-5 until the SEARCH returns a NULL Recordset.
The result will be the BOM DETAIL for every sub level. You should be abe to manage the inner joins and versioning through your query.
Good Luck
Originally posted by joshplummer
I don't know if I can get exactly what you need. I can describe it further if that would help. It is fairly complex:
Table
-BOM_Master
Fields
-BOM Version [PK] (varchar(20))
-BOM ID [PK] - Is actually an Item # from our Inventory DB (varchar(20))
-BOM Date (Date)
Table
-BOM_Version
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM Version Date (Date)
Table
-BOM_Detail
Fields
-BOM Version [PK][FK-BOM_Master] (varchar(20))
-BOM ID [PK][FK-BOM_Master] (varchar(20))
-BOM Line [PK] (Int)
-BOM Line Description (varchar(50))
-BOM Flag (bit)
This is a simplified version of our actual tables, and since this is our business system, I shouldn't post DDL or data since the sample data we use is our real data, just in a test environment.|||Could you post some sample code that does what you're suggesting. I don't think I've done anything like that using cursors...
That sounds like a good idea. Any indications of performance? I know cursors can be a little cumbersome.|||Unfortunately I cannot.
Paid by the hour. and don't want to lose my contract. I'll see what I can do tonight but no promises.
Originally posted by joshplummer
Could you post some sample code that does what you're suggesting. I don't think I've done anything like that using cursors...
That sounds like a good idea. Any indications of performance? I know cursors can be a little cumbersome.|||You don't need a cursor to do this (unless you are paid by the hour).
In the BOM_Master table, you say that ID represents an item from you inventory. Is this the value that may also represent another BOM through which you need to drill down?
BOM_Version > BOM_Master > BOM_Detail|||The only issue is the BOM within a BOM within a BOM ...
The query needs to be recursive until there are no more BOMS (i.e. returned recordset is null). I agree that the internal queries will not need to be CURSORS, but to build the n layers of the hierarchy you need to rerun the query.
The biggest problem will be how to handle duplicates. For example a screw is a component of the main BOM and of a level 3 component.
Unfortunately for joshplummer this is nota simple task.
Originally posted by blindman
You don't need a cursor to do this (unless you are paid by the hour).
In the BOM_Master table, you say that ID represents an item from you inventory. Is this the value that may also represent another BOM through which you need to drill down?
BOM_Version > BOM_Master > BOM_Detail|||This can be accomplished by creating a table variable, seeding it with the initial value, and then running this single skeleton SQL statement:
while @.@.Rowcount > 0
insert into TableVariable
select Datatable.ChildData
from DataTable
inner join TableVariable on DataTable.ParentID = TableVariable.ID
where not exists (select ID from TableVariable where TableVariable.ID = Datatable.ChildData)|||I appreciate everyone's help with this. I was able to get the process working using a recursive cursor based stored proc. It's fairly simple, to be honest, and it's very quick. It screams through a 3000+ line BOM in less than a second. And, the results are spot on!
Once again, thanks for the help everyone.|||Good to hear.
Originally posted by joshplummer
I appreciate everyone's help with this. I was able to get the process working using a recursive cursor based stored proc. It's fairly simple, to be honest, and it's very quick. It screams through a 3000+ line BOM in less than a second. And, the results are spot on!
Once again, thanks for the help everyone.|||Cursors are crutches for VB programmers. Learn to use set-based operations.|||Blindman,
I appologize, I didn't see your original posting.
Your statements were correct.
If you have an alternative to the cursor, I'd love to see it.|||Not 100% sure about your table relationships and keys, but here is an example that returns the flagged BOM_Details for a specified BOM, including sub-BOMs:
--------------------
declare @.ParentBOM varchar(20)
declare @.BOM_List table
(BOM_Version varchar(20) Primary Key Clustered)
--First, seed the BOM_List table
insert into @.BOM_List
(BOM_Version)
select BOM_Version
from BOM_Master
where BOM_Version = @.ParentBOM
--This ensures that the BOM actually exists in the BOM_Master table
--Now collect all the sub-records
while @.@.Rowcount > 0 --This executes if the previous statement was successfull.
insert into @.BOM_List
(BOM_Version)
select BOM_Master.BOM_ID --The child record
from BOM_Master
inner join @.BOM_List BOM_List on BOM_Master.BOM_Version = BOM_List.BOM_Version
--This clause ensures that we don't try to insert a BOM more than once
where not exists (select * from @.BOM_List CurrentRecords where CurrentRecords.BOM_Version = BOM_Master.BOM_ID)
--Now you can join your BOM_List table to your details table:
select *
from BOM_Detail
inner join @.BOM_List BOM_List on BOM_Detail.BOM_Version = BOM_List.BOM_Version
where BOM_Detail.Flag = 1
--------------------
Friday, February 24, 2012
Dynamic columns in RS reports
I have to design a report that will have a dynamic number of columns, in a
dynamic order.
For example: items and a selection from their attributes
ITEM Attr 1 Attr 2
Attr3 Attr 4
I1 5 Realy large text
........................................ Short text
12305
etc...
I know the matrix is used to obtain dynamic number of columns, but the
problem is that the columns in matrix have the same width
and i need different column widths (for text attributes and numeric
attributes for example), and the column width can have only numeric
(and fixed) values - i mean you can put there a function call..
Is there any way other than generating the rdl at run time to obtain such
dynamic table ?
Thanks in advance for any advice.This is not really feasible as far as I can tell, in the way you are
asking.
You can't set column width dynamically, which sounds like a big problem
for you.
But you may be able to get some way towards it if you compromise a
little.
In terms of no. of columns, you'd need to set a maximum number, make
sure you always return that number in the dataset even if they contain
nulls you can then allow for the maximum in your report and just hide
or show them as needed. You'd have to do it this way because you will
be referring to the columns with a field name and the report validates
that those fields will be returned when it renders.
You can then hide table columns or text boxes in a list control based
on the number of columns you're expecting that scenario. You could have
several table columns geared to one dataset column, all set differently
according to data type, then hide the columns that you don't need based
on whats returned in the query.
Chris
Razvan Popov wrote:
> I have to design a report that will have a dynamic number of columns,
> in a dynamic order.
> For example: items and a selection from their attributes
> ITEM Attr 1 Attr 2
> Attr3 Attr 4
> I1 5 Realy large text
> ........................................ Short text
> 12305
> etc...
> I know the matrix is used to obtain dynamic number of columns, but
> the problem is that the columns in matrix have the same width
> and i need different column widths (for text attributes and numeric
> attributes for example), and the column width can have only numeric
> (and fixed) values - i mean you can put there a function call..
> Is there any way other than generating the rdl at run time to obtain
> such dynamic table ?
> Thanks in advance for any advice.|||Thank you for your suggestion. But my problem is a little worse than that:
I don't have a maximum number of columns (the user can add attributes and
select them into the report at runtime).
And if i set a maximum number of X columns as constraint. I don't know which
will be the X attributes the user selected to
be displayed into report and for that I have no way of presetting the width
of the columns (which should be dependent on attribute type)..
I have as the last option to generate dinamically the RDL (or parts of it)
but this is prety complex solution and i would like to be sure that there is
no
simpler solution before i start coding such scenario...
Thx
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> This is not really feasible as far as I can tell, in the way you are
> asking.
> You can't set column width dynamically, which sounds like a big problem
> for you.
> But you may be able to get some way towards it if you compromise a
> little.
> In terms of no. of columns, you'd need to set a maximum number, make
> sure you always return that number in the dataset even if they contain
> nulls you can then allow for the maximum in your report and just hide
> or show them as needed. You'd have to do it this way because you will
> be referring to the columns with a field name and the report validates
> that those fields will be returned when it renders.
> You can then hide table columns or text boxes in a list control based
> on the number of columns you're expecting that scenario. You could have
> several table columns geared to one dataset column, all set differently
> according to data type, then hide the columns that you don't need based
> on whats returned in the query.
> Chris|||Razvan,
You've got a pretty complex scenario, I think you're going to need a
complex solution!
It sounds like you're developing a form of end-user report builder?
Have you taken a look at Cizer (www.cizer.com) or Report Builder in
SQL2005?
Chris
Razvan Popov wrote:
> Thank you for your suggestion. But my problem is a little worse than
> that: I don't have a maximum number of columns (the user can add
> attributes and select them into the report at runtime).
> And if i set a maximum number of X columns as constraint. I don't
> know which will be the X attributes the user selected to
> be displayed into report and for that I have no way of presetting the
> width of the columns (which should be dependent on attribute type)..
> I have as the last option to generate dinamically the RDL (or parts
> of it) but this is prety complex solution and i would like to be sure
> that there is no
> simpler solution before i start coding such scenario...
> Thx
>
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> > This is not really feasible as far as I can tell, in the way you are
> > asking.
> > You can't set column width dynamically, which sounds like a big
> > problem for you.
> >
> > But you may be able to get some way towards it if you compromise a
> > little.
> > In terms of no. of columns, you'd need to set a maximum number, make
> > sure you always return that number in the dataset even if they
> > contain nulls you can then allow for the maximum in your report and
> > just hide or show them as needed. You'd have to do it this way
> > because you will be referring to the columns with a field name and
> > the report validates that those fields will be returned when it
> > renders.
> >
> > You can then hide table columns or text boxes in a list control
> > based on the number of columns you're expecting that scenario. You
> > could have several table columns geared to one dataset column, all
> > set differently according to data type, then hide the columns that
> > you don't need based on whats returned in the query.
> >
> > Chris|||Thank you for your help..
If i find any solution simpler than generating rdls dynamically i'll post it
here..
Razvan
dynamic order.
For example: items and a selection from their attributes
ITEM Attr 1 Attr 2
Attr3 Attr 4
I1 5 Realy large text
........................................ Short text
12305
etc...
I know the matrix is used to obtain dynamic number of columns, but the
problem is that the columns in matrix have the same width
and i need different column widths (for text attributes and numeric
attributes for example), and the column width can have only numeric
(and fixed) values - i mean you can put there a function call..
Is there any way other than generating the rdl at run time to obtain such
dynamic table ?
Thanks in advance for any advice.This is not really feasible as far as I can tell, in the way you are
asking.
You can't set column width dynamically, which sounds like a big problem
for you.
But you may be able to get some way towards it if you compromise a
little.
In terms of no. of columns, you'd need to set a maximum number, make
sure you always return that number in the dataset even if they contain
nulls you can then allow for the maximum in your report and just hide
or show them as needed. You'd have to do it this way because you will
be referring to the columns with a field name and the report validates
that those fields will be returned when it renders.
You can then hide table columns or text boxes in a list control based
on the number of columns you're expecting that scenario. You could have
several table columns geared to one dataset column, all set differently
according to data type, then hide the columns that you don't need based
on whats returned in the query.
Chris
Razvan Popov wrote:
> I have to design a report that will have a dynamic number of columns,
> in a dynamic order.
> For example: items and a selection from their attributes
> ITEM Attr 1 Attr 2
> Attr3 Attr 4
> I1 5 Realy large text
> ........................................ Short text
> 12305
> etc...
> I know the matrix is used to obtain dynamic number of columns, but
> the problem is that the columns in matrix have the same width
> and i need different column widths (for text attributes and numeric
> attributes for example), and the column width can have only numeric
> (and fixed) values - i mean you can put there a function call..
> Is there any way other than generating the rdl at run time to obtain
> such dynamic table ?
> Thanks in advance for any advice.|||Thank you for your suggestion. But my problem is a little worse than that:
I don't have a maximum number of columns (the user can add attributes and
select them into the report at runtime).
And if i set a maximum number of X columns as constraint. I don't know which
will be the X attributes the user selected to
be displayed into report and for that I have no way of presetting the width
of the columns (which should be dependent on attribute type)..
I have as the last option to generate dinamically the RDL (or parts of it)
but this is prety complex solution and i would like to be sure that there is
no
simpler solution before i start coding such scenario...
Thx
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> This is not really feasible as far as I can tell, in the way you are
> asking.
> You can't set column width dynamically, which sounds like a big problem
> for you.
> But you may be able to get some way towards it if you compromise a
> little.
> In terms of no. of columns, you'd need to set a maximum number, make
> sure you always return that number in the dataset even if they contain
> nulls you can then allow for the maximum in your report and just hide
> or show them as needed. You'd have to do it this way because you will
> be referring to the columns with a field name and the report validates
> that those fields will be returned when it renders.
> You can then hide table columns or text boxes in a list control based
> on the number of columns you're expecting that scenario. You could have
> several table columns geared to one dataset column, all set differently
> according to data type, then hide the columns that you don't need based
> on whats returned in the query.
> Chris|||Razvan,
You've got a pretty complex scenario, I think you're going to need a
complex solution!
It sounds like you're developing a form of end-user report builder?
Have you taken a look at Cizer (www.cizer.com) or Report Builder in
SQL2005?
Chris
Razvan Popov wrote:
> Thank you for your suggestion. But my problem is a little worse than
> that: I don't have a maximum number of columns (the user can add
> attributes and select them into the report at runtime).
> And if i set a maximum number of X columns as constraint. I don't
> know which will be the X attributes the user selected to
> be displayed into report and for that I have no way of presetting the
> width of the columns (which should be dependent on attribute type)..
> I have as the last option to generate dinamically the RDL (or parts
> of it) but this is prety complex solution and i would like to be sure
> that there is no
> simpler solution before i start coding such scenario...
> Thx
>
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> > This is not really feasible as far as I can tell, in the way you are
> > asking.
> > You can't set column width dynamically, which sounds like a big
> > problem for you.
> >
> > But you may be able to get some way towards it if you compromise a
> > little.
> > In terms of no. of columns, you'd need to set a maximum number, make
> > sure you always return that number in the dataset even if they
> > contain nulls you can then allow for the maximum in your report and
> > just hide or show them as needed. You'd have to do it this way
> > because you will be referring to the columns with a field name and
> > the report validates that those fields will be returned when it
> > renders.
> >
> > You can then hide table columns or text boxes in a list control
> > based on the number of columns you're expecting that scenario. You
> > could have several table columns geared to one dataset column, all
> > set differently according to data type, then hide the columns that
> > you don't need based on whats returned in the query.
> >
> > Chris|||Thank you for your help..
If i find any solution simpler than generating rdls dynamically i'll post it
here..
Razvan
Sunday, February 19, 2012
Dynamic columns in RS reports
I have to design a report that will have a dynamic number of columns, in a
dynamic order.
For example: items and a selection from their attributes
ITEM Attr 1 Attr 2
Attr3 Attr 4
I1 5 Realy large text
........................................ Short text
12305
etc...
I know the matrix is used to obtain dynamic number of columns, but the
problem is that the columns in matrix have the same width
and i need different column widths (for text attributes and numeric
attributes for example), and the column width can have only numeric
(and fixed) values - i mean you can put there a function call..
Is there any way other than generating the rdl at run time to obtain such
dynamic table ?
Thanks in advance for any advice.This is not really feasible as far as I can tell, in the way you are
asking.
You can't set column width dynamically, which sounds like a big problem
for you.
But you may be able to get some way towards it if you compromise a
little.
In terms of no. of columns, you'd need to set a maximum number, make
sure you always return that number in the dataset even if they contain
nulls you can then allow for the maximum in your report and just hide
or show them as needed. You'd have to do it this way because you will
be referring to the columns with a field name and the report validates
that those fields will be returned when it renders.
You can then hide table columns or text boxes in a list control based
on the number of columns you're expecting that scenario. You could have
several table columns geared to one dataset column, all set differently
according to data type, then hide the columns that you don't need based
on whats returned in the query.
Chris
Razvan Popov wrote:
> I have to design a report that will have a dynamic number of columns,
> in a dynamic order.
> For example: items and a selection from their attributes
> ITEM Attr 1 Attr 2
> Attr3 Attr 4
> I1 5 Realy large text
> ........................................ Short text
> 12305
> etc...
> I know the matrix is used to obtain dynamic number of columns, but
> the problem is that the columns in matrix have the same width
> and i need different column widths (for text attributes and numeric
> attributes for example), and the column width can have only numeric
> (and fixed) values - i mean you can put there a function call..
> Is there any way other than generating the rdl at run time to obtain
> such dynamic table ?
> Thanks in advance for any advice.|||Thank you for your suggestion. But my problem is a little worse than that:
I don't have a maximum number of columns (the user can add attributes and
select them into the report at runtime).
And if i set a maximum number of X columns as constraint. I don't know which
will be the X attributes the user selected to
be displayed into report and for that I have no way of presetting the width
of the columns (which should be dependent on attribute type)..
I have as the last option to generate dinamically the RDL (or parts of it)
but this is prety complex solution and i would like to be sure that there is
no
simpler solution before i start coding such scenario...
Thx
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> This is not really feasible as far as I can tell, in the way you are
> asking.
> You can't set column width dynamically, which sounds like a big problem
> for you.
> But you may be able to get some way towards it if you compromise a
> little.
> In terms of no. of columns, you'd need to set a maximum number, make
> sure you always return that number in the dataset even if they contain
> nulls you can then allow for the maximum in your report and just hide
> or show them as needed. You'd have to do it this way because you will
> be referring to the columns with a field name and the report validates
> that those fields will be returned when it renders.
> You can then hide table columns or text boxes in a list control based
> on the number of columns you're expecting that scenario. You could have
> several table columns geared to one dataset column, all set differently
> according to data type, then hide the columns that you don't need based
> on whats returned in the query.
> Chris|||Razvan,
You've got a pretty complex scenario, I think you're going to need a
complex solution!
It sounds like you're developing a form of end-user report builder?
Have you taken a look at Cizer (www.cizer.com) or Report Builder in
SQL2005?
Chris
Razvan Popov wrote:
> Thank you for your suggestion. But my problem is a little worse than
> that: I don't have a maximum number of columns (the user can add
> attributes and select them into the report at runtime).
> And if i set a maximum number of X columns as constraint. I don't
> know which will be the X attributes the user selected to
> be displayed into report and for that I have no way of presetting the
> width of the columns (which should be dependent on attribute type)..
> I have as the last option to generate dinamically the RDL (or parts
> of it) but this is prety complex solution and i would like to be sure
> that there is no
> simpler solution before i start coding such scenario...
> Thx
>
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> > This is not really feasible as far as I can tell, in the way you are
> > asking.
> > You can't set column width dynamically, which sounds like a big
> > problem for you.
> >
> > But you may be able to get some way towards it if you compromise a
> > little.
> > In terms of no. of columns, you'd need to set a maximum number, make
> > sure you always return that number in the dataset even if they
> > contain nulls you can then allow for the maximum in your report and
> > just hide or show them as needed. You'd have to do it this way
> > because you will be referring to the columns with a field name and
> > the report validates that those fields will be returned when it
> > renders.
> >
> > You can then hide table columns or text boxes in a list control
> > based on the number of columns you're expecting that scenario. You
> > could have several table columns geared to one dataset column, all
> > set differently according to data type, then hide the columns that
> > you don't need based on whats returned in the query.
> >
> > Chris|||Thank you for your help..
If i find any solution simpler than generating rdls dynamically i'll post it
here..
Razvan
dynamic order.
For example: items and a selection from their attributes
ITEM Attr 1 Attr 2
Attr3 Attr 4
I1 5 Realy large text
........................................ Short text
12305
etc...
I know the matrix is used to obtain dynamic number of columns, but the
problem is that the columns in matrix have the same width
and i need different column widths (for text attributes and numeric
attributes for example), and the column width can have only numeric
(and fixed) values - i mean you can put there a function call..
Is there any way other than generating the rdl at run time to obtain such
dynamic table ?
Thanks in advance for any advice.This is not really feasible as far as I can tell, in the way you are
asking.
You can't set column width dynamically, which sounds like a big problem
for you.
But you may be able to get some way towards it if you compromise a
little.
In terms of no. of columns, you'd need to set a maximum number, make
sure you always return that number in the dataset even if they contain
nulls you can then allow for the maximum in your report and just hide
or show them as needed. You'd have to do it this way because you will
be referring to the columns with a field name and the report validates
that those fields will be returned when it renders.
You can then hide table columns or text boxes in a list control based
on the number of columns you're expecting that scenario. You could have
several table columns geared to one dataset column, all set differently
according to data type, then hide the columns that you don't need based
on whats returned in the query.
Chris
Razvan Popov wrote:
> I have to design a report that will have a dynamic number of columns,
> in a dynamic order.
> For example: items and a selection from their attributes
> ITEM Attr 1 Attr 2
> Attr3 Attr 4
> I1 5 Realy large text
> ........................................ Short text
> 12305
> etc...
> I know the matrix is used to obtain dynamic number of columns, but
> the problem is that the columns in matrix have the same width
> and i need different column widths (for text attributes and numeric
> attributes for example), and the column width can have only numeric
> (and fixed) values - i mean you can put there a function call..
> Is there any way other than generating the rdl at run time to obtain
> such dynamic table ?
> Thanks in advance for any advice.|||Thank you for your suggestion. But my problem is a little worse than that:
I don't have a maximum number of columns (the user can add attributes and
select them into the report at runtime).
And if i set a maximum number of X columns as constraint. I don't know which
will be the X attributes the user selected to
be displayed into report and for that I have no way of presetting the width
of the columns (which should be dependent on attribute type)..
I have as the last option to generate dinamically the RDL (or parts of it)
but this is prety complex solution and i would like to be sure that there is
no
simpler solution before i start coding such scenario...
Thx
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> This is not really feasible as far as I can tell, in the way you are
> asking.
> You can't set column width dynamically, which sounds like a big problem
> for you.
> But you may be able to get some way towards it if you compromise a
> little.
> In terms of no. of columns, you'd need to set a maximum number, make
> sure you always return that number in the dataset even if they contain
> nulls you can then allow for the maximum in your report and just hide
> or show them as needed. You'd have to do it this way because you will
> be referring to the columns with a field name and the report validates
> that those fields will be returned when it renders.
> You can then hide table columns or text boxes in a list control based
> on the number of columns you're expecting that scenario. You could have
> several table columns geared to one dataset column, all set differently
> according to data type, then hide the columns that you don't need based
> on whats returned in the query.
> Chris|||Razvan,
You've got a pretty complex scenario, I think you're going to need a
complex solution!
It sounds like you're developing a form of end-user report builder?
Have you taken a look at Cizer (www.cizer.com) or Report Builder in
SQL2005?
Chris
Razvan Popov wrote:
> Thank you for your suggestion. But my problem is a little worse than
> that: I don't have a maximum number of columns (the user can add
> attributes and select them into the report at runtime).
> And if i set a maximum number of X columns as constraint. I don't
> know which will be the X attributes the user selected to
> be displayed into report and for that I have no way of presetting the
> width of the columns (which should be dependent on attribute type)..
> I have as the last option to generate dinamically the RDL (or parts
> of it) but this is prety complex solution and i would like to be sure
> that there is no
> simpler solution before i start coding such scenario...
> Thx
>
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> > This is not really feasible as far as I can tell, in the way you are
> > asking.
> > You can't set column width dynamically, which sounds like a big
> > problem for you.
> >
> > But you may be able to get some way towards it if you compromise a
> > little.
> > In terms of no. of columns, you'd need to set a maximum number, make
> > sure you always return that number in the dataset even if they
> > contain nulls you can then allow for the maximum in your report and
> > just hide or show them as needed. You'd have to do it this way
> > because you will be referring to the columns with a field name and
> > the report validates that those fields will be returned when it
> > renders.
> >
> > You can then hide table columns or text boxes in a list control
> > based on the number of columns you're expecting that scenario. You
> > could have several table columns geared to one dataset column, all
> > set differently according to data type, then hide the columns that
> > you don't need based on whats returned in the query.
> >
> > Chris|||Thank you for your help..
If i find any solution simpler than generating rdls dynamically i'll post it
here..
Razvan
Subscribe to:
Posts (Atom)