October 14, 2010 at 1:31 pm
Good afternoon everyone. I work for a CPA firm and when we produce documents, some are have Letters for page numbers, which we call Exhibit letters.
I have a report that has groups in it. Everytime a page displays a new group, I would like for the Exhibit letter to increment by one.
For example, the first two pages of the report display Exhibit A because the group was two pages worth of data. Then when the page break kicks in, the third page should display Exhibit B. And then so on and so on. Now sometimes we have a large report that might have to double up on the Exhibit letters such AA and so on.
What've done so far:
--Added a Page Footer
--Created a parameter called Enter Starting Exhibit Letter and inserted the parameter into the Page Footer.
Everytime I run the report, the letter A is displayed on every page of the report, that's if the user entered A as the starting exhibit letter.
The way we made it work in crystal, we first created a formula called GroupNo. It has this syntax:
whilePrintingRecords;
numberVar GroupNo := GroupNo + 1;
Then in the page footer, this syntax was entered:
'Exhibit' + ' ' & chr({@GroupNo} + Asc ({?Exhibit Start Letter}) -2 )
Hope my explaination was clear enough. Can someone hopefully assist me with this?
Thank you!
October 14, 2010 at 2:00 pm
Here's some T-SQL code to convert a number into a corresponding letter. It should be relatively straightforward to convert into VB/C+ for code in Reporting Services:
DECLARE @page int,
@cPage varchar(5),
@pageChar1 int,
@pageChar2 int;
SET @page = 26;
SELECT @pageChar1 = @page/26,
@pageChar2 = @page % 26,
@cPage = CASE WHEN @pageChar1 > 1 AND @pageChar2 = 0 THEN CHAR(63+@pageChar1)
WHEN @pageChar1 > 0 AND @pageChar2 = 0 THEN ''
WHEN @pageChar1 > 0 THEN CHAR(64+@pageChar1)
ELSE '' END +
CASE WHEN @pageChar2 = 0 THEN 'Z'
ELSE CHAR(64 + @pageChar2)
END;
SELECT @page, @pageChar1, @pageChar2, @cPage;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply