I'm probably missing something obvious but thought I would ask here. I am working on developing various Velixo reports in Excel and trying to structure it so that multiple separate queries can be displayed one after another vertically—each showing a different set of data. The challenge I'm facing is that when the first query expands (i.e., returns more rows than expected), it overlaps the second query below it. This also applies when I want to have manual formatting of subtotals etc in report (i.e. I want all asset accounts in the balance sheet shown in one section then have the liabilities below in a second query to allow me to have a formatted subtotal and gap).
I.e. when I run a query for a given branch/duration the resulting list of data can vary greatly.
I’m aware of the Auto Hide Rows feature, which works great for hiding unused rows within the query range itself, but it doesn’t seem to help with hiding rows that aren't part of the resulting query range or how I can use the structuring to just arrange for the automatic shifting the next query down to make room for the expanded data above.
Is there a best practice or recommended method in Velixo for dynamically stacking queries or making room for variable result lenght like this?
Any tips or examples would be greatly appreciated!
Thanks in advance
0 Votes
H
Harry Lewis posted
19 days ago
Best Answer
Hi Mark -
The Auto-Hide feature would be the way to handle this. Here is a rudimentary example of how I would go about it.
I have a report similar to what you describe. I have multiple queries, one after the other:
You can see that I've left extra blank space between them to account for expansion.
I then introduce a function to examine if there is data in column C:
I now have zero and non-zero data in column J. I can use the auto-hide feature on that column.
You can see that the blank rows (which have a zero in column J) are now hidden.
If my data changes:
My report adjust automatically.
Obviously, I need to leave enough blank space to accommodate the largest expected data set.
Another option, of course, is to put each query on its own worksheet. I could then use functionality - such as the Velixo COLLATE function or other Excel functionality - to combine the individual queries into a single display on another sheet.
1 Votes
2 Comments
Sorted by
M
Mark Sposted
15 days ago
Very clever, thanks this works well. I can leave plenty of spare rows for growth. I thought there must be a way to achieve this!
0 Votes
H
Harry Lewisposted
19 days ago
Answer
Hi Mark -
The Auto-Hide feature would be the way to handle this. Here is a rudimentary example of how I would go about it.
I have a report similar to what you describe. I have multiple queries, one after the other:
You can see that I've left extra blank space between them to account for expansion.
I then introduce a function to examine if there is data in column C:
I now have zero and non-zero data in column J. I can use the auto-hide feature on that column.
You can see that the blank rows (which have a zero in column J) are now hidden.
If my data changes:
My report adjust automatically.
Obviously, I need to leave enough blank space to accommodate the largest expected data set.
Another option, of course, is to put each query on its own worksheet. I could then use functionality - such as the Velixo COLLATE function or other Excel functionality - to combine the individual queries into a single display on another sheet.
0 Votes
Hi everyone,
I'm probably missing something obvious but thought I would ask here. I am working on developing various Velixo reports in Excel and trying to structure it so that multiple separate queries can be displayed one after another vertically—each showing a different set of data. The challenge I'm facing is that when the first query expands (i.e., returns more rows than expected), it overlaps the second query below it. This also applies when I want to have manual formatting of subtotals etc in report (i.e. I want all asset accounts in the balance sheet shown in one section then have the liabilities below in a second query to allow me to have a formatted subtotal and gap).
I.e. when I run a query for a given branch/duration the resulting list of data can vary greatly.
I’m aware of the Auto Hide Rows feature, which works great for hiding unused rows within the query range itself, but it doesn’t seem to help with hiding rows that aren't part of the resulting query range or how I can use the structuring to just arrange for the automatic shifting the next query down to make room for the expanded data above.
Is there a best practice or recommended method in Velixo for dynamically stacking queries or making room for variable result lenght like this?
Any tips or examples would be greatly appreciated!
Thanks in advance
0 Votes
Harry Lewis posted 19 days ago Best Answer
Hi Mark -
The Auto-Hide feature would be the way to handle this. Here is a rudimentary example of how I would go about it.
I have a report similar to what you describe. I have multiple queries, one after the other:
You can see that I've left extra blank space between them to account for expansion.
I then introduce a function to examine if there is data in column C:
I now have zero and non-zero data in column J. I can use the auto-hide feature on that column.
You can see that the blank rows (which have a zero in column J) are now hidden.
If my data changes:
My report adjust automatically.
Obviously, I need to leave enough blank space to accommodate the largest expected data set.
Another option, of course, is to put each query on its own worksheet. I could then use functionality - such as the Velixo COLLATE function or other Excel functionality - to combine the individual queries into a single display on another sheet.
1 Votes
2 Comments
Mark S posted 15 days ago
Very clever, thanks this works well. I can leave plenty of spare rows for growth. I thought there must be a way to achieve this!
0 Votes
Harry Lewis posted 19 days ago Answer
Hi Mark -
The Auto-Hide feature would be the way to handle this. Here is a rudimentary example of how I would go about it.
I have a report similar to what you describe. I have multiple queries, one after the other:
You can see that I've left extra blank space between them to account for expansion.
I then introduce a function to examine if there is data in column C:
I now have zero and non-zero data in column J. I can use the auto-hide feature on that column.
You can see that the blank rows (which have a zero in column J) are now hidden.
If my data changes:
My report adjust automatically.
Obviously, I need to leave enough blank space to accommodate the largest expected data set.
Another option, of course, is to put each query on its own worksheet. I could then use functionality - such as the Velixo COLLATE function or other Excel functionality - to combine the individual queries into a single display on another sheet.
1 Votes
Login or Sign up to post a comment