A number of times over the years I've been looking for a way to tell if a cell in Excel is on a row that is hidden without having to resort to VBA. Now that I've found it it seems blindingly obvious. I share it here just in case someone else might not have seen it.
A B
1 1
2 =IF(SUBTOTAL(109,A1)=1,"Visible","Hidden")
If row 1 is visible B2 will display "Visible" otherwise it will display "Hidden".
Now I just need to wait until the need arises again before I get to use it. I can't remember what I was doing or why I needed it but I'm sure it'll come up again!
Tuesday 1 November 2011
Friday 26 August 2011
Running Totals using SQL and the Text File ODBC driver
With a data file containing detailed dated transactions how can you use SQL to record a running total?
Here's the RTData.txt data file:
Schema.ini looks as follows:
[RTData.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
Col1=Item Text width 255
Col2=Date Date
Col3=Amount Currency
The SQL written to show the running totals is as follows:
SELECT Cur.Item, Cur.Date, Cur.Amount, Sum(Cum.Amount) AS 'Running Total'
FROM RTData.txt Cum, RTData.txt Cur
WHERE Cum.Item = Cur.Item AND Cum.Date <= Cur.Date
GROUP BY Cur.Item, Cur.Date, Cur.Amount
ORDER BY Cur.Item, Cur.Date
The output is as follows:
It adds another layer of complexity if we want to generate monthly totals and also keep a running total. This SQL will do it:
SELECT Cur.item, Cur.month, sum(Cur.Amount)/count(Cur.Amount) AS Amount, sum(Cum.Amount) AS 'Running Total'
FROM (SELECT RTD.Item, (year(RTD.Date)*100)+month(RTD.Date) AS Month, Sum(RTD.Amount) AS Amount
FROM RTData.txt RTD
Group By RTD.Item, (year(RTD.Date)*100)+month(RTD.Date)) as Cur,
(SELECT RTD.Item, (year(RTD.Date)*100)+month(RTD.Date) AS Month, Sum(RTD.Amount) AS Amount
FROM RTData.txt RTD
Group By RTD.Item, (year(RTD.Date)*100)+month(RTD.Date)) as Cum
WHERE Cum.Item = Cur.Item AND Cum.Month <= Cur.Month
Group By Cur.Item, Cur.Month
ORDER BY Cur.Item, Cur.Month
The output is as follows:
The trick here is to realise that the Amount column inflates artificially as we progress and we need to correct for this by dividing by count(Cur.Amount) which returns 1, 2, 3 ... with successive records.
Comments welcome.
Here's the RTData.txt data file:
Date
|
Amount
| |
A
|
01/01/2011
|
12.30
|
A
|
15/01/2011
|
16.00
|
B
|
16/01/2011
|
5.69
|
A
|
01/02/2011
|
65.12
|
B
|
01/02/2011
|
0.58
|
A
|
14/02/2011
|
8.91
|
A
|
28/02/2011
|
1.00
|
A
|
15/03/2011
|
9.00
|
B
|
01/04/2011
|
31.45
|
B
|
01/05/2011
|
738.00
|
B
|
21/05/2011
|
9.11
|
A
|
21/05/2011
|
10.93
|
Schema.ini looks as follows:
[RTData.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
Col1=Item Text width 255
Col2=Date Date
Col3=Amount Currency
The SQL written to show the running totals is as follows:
SELECT Cur.Item, Cur.Date, Cur.Amount, Sum(Cum.Amount) AS 'Running Total'
FROM RTData.txt Cum, RTData.txt Cur
WHERE Cum.Item = Cur.Item AND Cum.Date <= Cur.Date
GROUP BY Cur.Item, Cur.Date, Cur.Amount
ORDER BY Cur.Item, Cur.Date
The output is as follows:
| Item | Date | Amount | Running Total |
| A | 01/01/2011 | 12.30 | 12.30 |
| A | 15/01/2011 | 16.00 | 28.30 |
| A | 01/02/2011 | 65.12 | 93.42 |
| A | 14/02/2011 | 8.91 | 102.33 |
| A | 28/02/2011 | 1.00 | 103.33 |
| A | 15/03/2011 | 9.00 | 112.33 |
| A | 21/05/2011 | 10.93 | 123.26 |
| B | 16/01/2011 | 5.69 | 5.69 |
| B | 01/02/2011 | 0.58 | 6.27 |
| B | 01/04/2011 | 31.45 | 37.72 |
| B | 01/05/2011 | 738.00 | 775.72 |
| B | 21/05/2011 | 9.11 | 784.83 |
It adds another layer of complexity if we want to generate monthly totals and also keep a running total. This SQL will do it:
SELECT Cur.item, Cur.month, sum(Cur.Amount)/count(Cur.Amount) AS Amount, sum(Cum.Amount) AS 'Running Total'
FROM (SELECT RTD.Item, (year(RTD.Date)*100)+month(RTD.Date) AS Month, Sum(RTD.Amount) AS Amount
FROM RTData.txt RTD
Group By RTD.Item, (year(RTD.Date)*100)+month(RTD.Date)) as Cur,
(SELECT RTD.Item, (year(RTD.Date)*100)+month(RTD.Date) AS Month, Sum(RTD.Amount) AS Amount
FROM RTData.txt RTD
Group By RTD.Item, (year(RTD.Date)*100)+month(RTD.Date)) as Cum
WHERE Cum.Item = Cur.Item AND Cum.Month <= Cur.Month
Group By Cur.Item, Cur.Month
ORDER BY Cur.Item, Cur.Month
The output is as follows:
| Item | Month | Amount | Running Total |
| A | 201101 | 28.30 | 28.30 |
| A | 201102 | 75.03 | 103.33 |
| A | 201103 | 9.00 | 112.33 |
| A | 201105 | 10.93 | 123.26 |
| B | 201101 | 5.69 | 5.69 |
| B | 201102 | 0.58 | 6.27 |
| B | 201104 | 31.45 | 37.72 |
| B | 201105 | 747.11 | 784.83 |
The trick here is to realise that the Amount column inflates artificially as we progress and we need to correct for this by dividing by count(Cur.Amount) which returns 1, 2, 3 ... with successive records.
Comments welcome.
Tuesday 2 August 2011
Dealing with mixed data types in Excel ODBC queries (2)
Shortly after posting the first message on this topic I had cause to use it in a real life situation. All was fine until a cell in my text field had more than 255 (?) characters. It broke the solution!
I haven't had time to fully investigate exact limits in all of this but I think the Excel ODBC driver will only accept up to about 255 characters in a "Text" field. If the driver comes across more than 255 characters it interprets it as a "Memo" type content (up to 32k characters?) and the attempt to query fails at the record that contains the offending data. How to work around?
Using the IMEX=2 approach from the previous article, firstly, I appended 256 spaces into the first rows of the field that needs to be "Memo". This was to force the majority type interpretation of this field to be "Memo". Once this had been done I discovered that the query no longer failed but it only returned the first 253 characters of the "Memo" field. Why I'm getting 253 I'm not sure but it seems this is a limitation of the Excel ODBC driver when dealing with "Memo" fields. However, I discovered that if I constructed additional calculated fields in my SQL I could bring back the entire contents of the "Memo" field in 253 character chunks. The SQL select clause entries look as follows:
''+F1 as [Memo Field],
mid(''+F1,254,253) as [Memo Field 2],
mid(''+F1,507,253) as [Memo Field 3], ... and so on.
I'd be really interested if anyone is able to put some better flesh on these bones.
I haven't had time to fully investigate exact limits in all of this but I think the Excel ODBC driver will only accept up to about 255 characters in a "Text" field. If the driver comes across more than 255 characters it interprets it as a "Memo" type content (up to 32k characters?) and the attempt to query fails at the record that contains the offending data. How to work around?
Using the IMEX=2 approach from the previous article, firstly, I appended 256 spaces into the first rows of the field that needs to be "Memo". This was to force the majority type interpretation of this field to be "Memo". Once this had been done I discovered that the query no longer failed but it only returned the first 253 characters of the "Memo" field. Why I'm getting 253 I'm not sure but it seems this is a limitation of the Excel ODBC driver when dealing with "Memo" fields. However, I discovered that if I constructed additional calculated fields in my SQL I could bring back the entire contents of the "Memo" field in 253 character chunks. The SQL select clause entries look as follows:
''+F1 as [Memo Field],
mid(''+F1,254,253) as [Memo Field 2],
mid(''+F1,507,253) as [Memo Field 3], ... and so on.
I'd be really interested if anyone is able to put some better flesh on these bones.
Sunday 24 July 2011
Dealing with mixed data types in Excel ODBC queries (1)
There's a great post dealing with this at Daily Dose of Excel. However, aware that I often don't have access to the registry on the machines I access, I was interested to find out if I could make queries work no matter what the registry contents*.
The best situation to find yourself in is if your registry has an ImportMixedTypes value of Text. This allows you to set IMEX=1 in the extended properties for the query. If you set HDR=No as well, the text column headers that would be the field names if HDR=Yes, ensure that the query will return text values for every field. Criteria can suppress the display of the record containing the column headers. Data conversion calculations provide the output of the query in the appropriate data type on all records. Using this methodology all the original data will be in field names F1, F2 etc. The data conversion calculations performed in SQL can alias the field names to reflect the original field names.
Below is a select statement to demonstrate how to return each data type in the above scenario:
SELECT iif(isnumeric(F1), cdbl(F1)) as [Number],
iif(isnumeric(F2), ccur(F2),0) as [Currency],
iif(isdate(F3),cdate(F3),#1900-01-01#) as [Date],
''+F4 as [Text],
iif(F5='TRUE',cbool(1),iif(F5='FALSE',cbool(0))) as [Boolean]
The following where clause ensures that the column header record is suppressed:
WHERE F4<>'Text' or isnull(F4)
In the above the currency column populates every record with a value, effectively converting nulls to zero. Vital if any GROUP BY operations are to return a value other than null. For comparison, the number column doesn't replace the nulls. Below is the QueryTable output:
If you find that your ImportMixedTypes=Majority Type, then IMEX=1 has no practical effect. If a mixed type field is determined to be a text type, all values of other types will be suppressed. The only way to guarantee that the proper values are returned for all fields is to include at least half the number of rows specified by TypeGuess rows just below the column headers in the data. These rows can be hidden once populated with the correct types of data. This will ensure that the Majority Type identified is the correct type. With HDR=No, criteria can suppress the display of the record containing the column headers as well as the records contained in the now hidden data rows. Data conversion calculations provide the output of the query in the appropriate data type on all records. Using this methodology all the original data will be in field names F1, F2 etc. The data conversion calculations performed in SQL can alias the field names to reflect the original field names.
Below is a select statement to demonstrate how to return each data type in the above scenario:
SELECT iif(isnumeric(F1), cdbl(F1)) as [Number],
iif(isnumeric(F2), ccur(F2),0) as [Currency],
iif(isdate(F3),cdate(F3),#1900-01-01#) as [Date],
''+F4 as [Text],
iif(isnumeric(F5),cbool(F5)) as [Boolean]
The following where clause ensures that the column header record is suppressed in addition to the records contained in the now hidden data rows (populated with '0'):
WHERE F4 not in('Text','0') or isnull(F4)
The QueryTable output is identical to that shown above.
* the one exception is if the registry has TypeGuessRows=0 (scan all rows) and ImportMixedTypes=Majority Type - an unlikely scenario.
The best situation to find yourself in is if your registry has an ImportMixedTypes value of Text. This allows you to set IMEX=1 in the extended properties for the query. If you set HDR=No as well, the text column headers that would be the field names if HDR=Yes, ensure that the query will return text values for every field. Criteria can suppress the display of the record containing the column headers. Data conversion calculations provide the output of the query in the appropriate data type on all records. Using this methodology all the original data will be in field names F1, F2 etc. The data conversion calculations performed in SQL can alias the field names to reflect the original field names.
Below is a select statement to demonstrate how to return each data type in the above scenario:
SELECT iif(isnumeric(F1), cdbl(F1)) as [Number],
iif(isnumeric(F2), ccur(F2),0) as [Currency],
iif(isdate(F3),cdate(F3),#1900-01-01#) as [Date],
''+F4 as [Text],
iif(F5='TRUE',cbool(1),iif(F5='FALSE',cbool(0))) as [Boolean]
The following where clause ensures that the column header record is suppressed:
WHERE F4<>'Text' or isnull(F4)
In the above the currency column populates every record with a value, effectively converting nulls to zero. Vital if any GROUP BY operations are to return a value other than null. For comparison, the number column doesn't replace the nulls. Below is the QueryTable output:
| Number | Currency | Date | Text | Boolean |
| - | 01/01/1900 | Other fields are Text in the original data | ||
| - | 01/01/1900 | Other fields are empty in the original data | ||
| 1234 | 567.89 | 30/05/2011 | -1 | |
| 56 | 12.99 | 23/07/2011 | The line above is empty | 0 |
If you find that your ImportMixedTypes=Majority Type, then IMEX=1 has no practical effect. If a mixed type field is determined to be a text type, all values of other types will be suppressed. The only way to guarantee that the proper values are returned for all fields is to include at least half the number of rows specified by TypeGuess rows just below the column headers in the data. These rows can be hidden once populated with the correct types of data. This will ensure that the Majority Type identified is the correct type. With HDR=No, criteria can suppress the display of the record containing the column headers as well as the records contained in the now hidden data rows. Data conversion calculations provide the output of the query in the appropriate data type on all records. Using this methodology all the original data will be in field names F1, F2 etc. The data conversion calculations performed in SQL can alias the field names to reflect the original field names.
Below is a select statement to demonstrate how to return each data type in the above scenario:
SELECT iif(isnumeric(F1), cdbl(F1)) as [Number],
iif(isnumeric(F2), ccur(F2),0) as [Currency],
iif(isdate(F3),cdate(F3),#1900-01-01#) as [Date],
''+F4 as [Text],
iif(isnumeric(F5),cbool(F5)) as [Boolean]
The following where clause ensures that the column header record is suppressed in addition to the records contained in the now hidden data rows (populated with '0'):
WHERE F4 not in('Text','0') or isnull(F4)
The QueryTable output is identical to that shown above.
* the one exception is if the registry has TypeGuessRows=0 (scan all rows) and ImportMixedTypes=Majority Type - an unlikely scenario.
Tuesday 31 May 2011
Passing multiple values in a single Cognos Report Studio value prompt
I found a way to pass multiple value combinations through in a single Cognos Report Studio static choices prompt. Here I describe the generic approach using just two values passed in each available choice.
In a value prompt (P_Prompt) static DisplayValue and UseValue choices are defined as:
DisplayValue UseValue
Choice 1: ~1[Prompt value 1],~2[Prompt value 2]
Choice 2: ~1[Prompt value 2],~2[Prompt value 3]
The UseValues comprise two Member Unique Name (MUN) tokens separated by a comma and identified with a leading ~1 or ~2 (which are unique character combinations).
The key to the approach is the macro expression #join(',',substitute('~[n]','',grep('~[n]',array(split(',',prompt('P_Prompt','token'))))))# which returns the [n]th token identified in the [n] value choice made.
Here’s a description of how the macro works from the inside out. The UseValue chosen in response to the prompt is returned as a token. The split macro creates multiple tokens from the single token returned using a comma as the delimiter. The multiple tokens are placed in an array using the array macro. The grep macro identifies the single array element containing the ~[n] search item specified and the substitute macro removes the ~[n] identifier from the token. The join macro converts the array item (now only one remaining) back into a string for use as a MUN.
If I choose the DisplayValue “Choice 1” when running a report, the UseValue “~1[Prompt value 1],~2[Prompt value 2]” will be available to the underlying report. The macro calculation #join(',',substitute('~1','',grep('~1',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 1] within the report whilst #join(',',substitute('~2','',grep('~2',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 2] within the report.
Similarly, if I choose the DisplayValue “Choice 2” when running a report, the UseValue “~1[Prompt value 2],~2[Prompt value 3]” will be available to the underlying report. The macro calculation #join(',',substitute('~1','',grep('~1',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 2] within the report whilst #join(',',substitute('~2','',grep('~2',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 3] within the report.
Maybe others have found another way to achieve the same result. If so, I’d be interested in how you’ve done it.
Sunday 27 March 2011
E-reconcile for everyone in Sage Line 50 / Sage Instant
I was using the manual bank reconciliation feature in Sage Instant Accounts the other day and found it to be a bit laborious marking cash book transactions off one by one. I don't subscribe to the file transfer module offered by my bank (for an extortionate amount of money) so I thought the e-Banking, e-Reconcile option was out.
However, then I remembered some work I'd done some considerable time ago where bank accounts with tens of thousands of transactions had to be reconciled in a different software package. The only statement information available was a csv file containing statement date, reference and amount. I constructed an Excel macro to create a statement file similar to the ones produced by the bank's file transfer module (http://www-2.danskebank.com/Link/CAPDBUK/$file/CAP_DB_BRAND_UK.pdf) and, hey presto, I was able to import the necessary tens of thousands of bank statement transactions and get to work.
Thinking about my Sage situation I realised that an ordinary set of transactions saved from my basic electronic banking screens will give me a csv file containing statement date, reference and amount. Consequently, I dusted down the old Excel routine and ran a month's statement transactions through it. I enabled e-Banking within Sage and imported the statement details using the file I'd created. Now I am able to use the auto reconcile features within Sage! Nice. Even better, I find that, with a bit of pre-processing in the Excel routine, I can maximise the "full match" (reference and amount) success rate and cut down manual matching to a minimum.
It should be possible to use this methodology for any bank account, so I'm planning to implement this approach for all Sage bank accounts where file transfer facilities don't exist with the bank. I like to have software do as much work for me as possible so this fits in with my overall stratregy pretty well.
P.S. if you use Sage you may also be interested in this Excel reporting tool I've written - www.onionrs.co.uk. A video is available on YouTube.
However, then I remembered some work I'd done some considerable time ago where bank accounts with tens of thousands of transactions had to be reconciled in a different software package. The only statement information available was a csv file containing statement date, reference and amount. I constructed an Excel macro to create a statement file similar to the ones produced by the bank's file transfer module (http://www-2.danskebank.com/Link/CAPDBUK/$file/CAP_DB_BRAND_UK.pdf) and, hey presto, I was able to import the necessary tens of thousands of bank statement transactions and get to work.
Thinking about my Sage situation I realised that an ordinary set of transactions saved from my basic electronic banking screens will give me a csv file containing statement date, reference and amount. Consequently, I dusted down the old Excel routine and ran a month's statement transactions through it. I enabled e-Banking within Sage and imported the statement details using the file I'd created. Now I am able to use the auto reconcile features within Sage! Nice. Even better, I find that, with a bit of pre-processing in the Excel routine, I can maximise the "full match" (reference and amount) success rate and cut down manual matching to a minimum.
It should be possible to use this methodology for any bank account, so I'm planning to implement this approach for all Sage bank accounts where file transfer facilities don't exist with the bank. I like to have software do as much work for me as possible so this fits in with my overall stratregy pretty well.
P.S. if you use Sage you may also be interested in this Excel reporting tool I've written - www.onionrs.co.uk. A video is available on YouTube.
Subscribe to:
Posts (Atom)