Creating the Monday Hallmark Report
Audience: IT Support Staff
Hallmark Weekly Report
This report contains the statistical and performance information on the curly lucky, box cards, e-cards ecommerce website from both internet and in some cased from internal nodes within Hallmark. These reports are due by noon Central Standard Time on the 1st business day of the week. The hallmark weekly report resides under the following directory in the GNW shared drive:
\\Kbase\gnw shared\ITTeamFolder\OfficeOperations\Reporting\HallMark\Monday Report
The report creation consists of the following processes.
Create a new folder and name it in mm-dd format for the current date.
- Copy the contents of the previous week's folder into the newly created folder.
\\Kbase\gnw shared\ITTeamFolder\OfficeOperations\Reporting\HallMark\Monday Report\2006\mm-dd
Where mm-dd is the month-day from the previous monday Example for April 10.
\\Kbase\gnw shared\ITTeamFolder\OfficeOperations\Reporting\HallMark\Monday Report\2006\04-10
Rename the Excel worksheet Hallmark_Monday_0.mm.dd.yy.xls to current date (If doing report on a day other than Monday, use Monday's date).
Open the Hallmark_Monday_0.mm.dd.yy.xls Change the date on cell O4 of the Results tab to the date on Monday of previous week. For example, if you’re doing report on Monday 09-26-05, insert 09-19-05 in cell O4. This should change the dates across the board.
Rename the tabs at the bottom (Ecards mm-dd, Bonsai mm-dd, and Competition mm-dd) to corresponding new dates.
Open Scrapbook.xls. On row 2 of the Scrap 1 tab, there are different step numbers listed. Select the cell on row4 on the same column as the step no. (i.e. if step 1_1 is on column B, then select B4).
From the Excel Menubar, click on SQL*XL Menu Item. This will initialize SQL*XL. (Note: The menu will not drop down the first time you click it.) Once initialized, click on SQL*XL and click connect.
username: vwpoint password: vwpoint database: dvpointp
Click SQL XL Query and copy/paste the sql with the corresponding step number. (For each version number, there is a corresponding .txt file which has the sql to be run on the selected cell. For example if 1_1 is on column B, select cell B4 and copy the content of 1_1*.txt to paste onto the sql xl query. * would be replaced by a description.) Run the query. The results will be output into the Scrapbook.xls spreadsheet.
Repeat step 8 for all steps in the Scrap 1 tab. (7 altogether)
Copy the results from Scrap1 tab in Scrapbook.xls to Results tab in Hallmark_Monday_0.mm.dd.yy.xls file and insert them into appropriate places. The data from step 1_1 on the Scrap 1 tab goes to the area marked 1_1 on the Results tab of Hallmark. The same goes with rest of the steps.
Go to the Scrap 3 tab in the scrapbook.xls worksheet. Run the corresponding sql statements by first highlighting the cell under the step number. (3_1 through 3_7)
Copy corresponding result values from the Scrap 3 tab to the ICT raw breakout tab in Hallmark worksheet.
Go to the Scrap 4 tab in Scrapbook.xls. Run the corresponding queries from the corresponding text files under the marked step numbers (4_1 through 4_4).
Copy the results to corresponding rows and columns in Success Raw tab in Hallmark worksheet.
- Copy competition charts under the Results tab in Hallmark worksheet (cells 029 through T67) and Paste them to Competition results in Scrapbook.xls.
In the Hallmark_Monday worksheet, there are tabs that lists the competition performance. Competition for each day is listed in a different tab. Get the total response time from the competition tab in Scrapbook and add the numbers to the text box over each graph for each day in the Hallmark Worksheet.
- Go through reports, verify things are good and send the report to Account Management for review.
After receiving approval from a member of Account Management, copy the contents of the newly created folder (including the Hallmark_Monday_0.mm.dd.yy.xls file) to following location on the shared drive.
\\Kbase\gnw shared\ITTeamFolder\OfficeOperations\Reporting\HallMark\Monday Report\2006\
Post the finalized Hallmark_Monday_0.mm.dd.yy.xls report to the following location on the main production server (iprev3).
/extra/netwatch/html/data/hallmark/Weekly/2007Old Iprev2 location:
/cdrive/web/netwatch/html/data/hallmark/Weekly/2007/Example using scp to copy the file from kbase to iprev2. Note: This report is from April of 2006 Change the path to use the current year and month. If necessary create the appropriate directories on iprev2.
scp Hallmark_Monday_0.04.10.06.xls vwpoint@iprev2:/cdrive/web/netwatch/html/data/hallmark/Weekly/2006/Apr