Figure 4Matrix Report
Introduction
A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results.
Hands-on
You client needs to get the summary report of their customers shares owned. They like to have the output format be the same as spreadsheet format. You are assigned to create a grid style of data output as a spreadsheet, with rows that present customers name and columns that present stock’s symbol. The intersection of these two entities is a cell that shows the number of shares that customer has on that stock.
This report should show the number of shares of stock’s holders by each customer in each of the stocks. Make sure the cell format mask is NNN,NN0. You should use the customer and portfolio table, and put grid around each number of shares for easy reading. You should also get the totals for each column and row.
They want you to change the cell color to red if its value is greater than 2000 and do the same for rows and columns sub-total.
See Figure 4.
Your tasks are:
1- Create a matrix report.
2- Put grid around each cell.
3- Use user layout format mask for cells and sub-totals as NNN,NN0.
4- Calculate the subtotal for rows and columns.
5- Highlight the cells with any color (ex: red) if their values are significant.
6- Highlight the sub-total cells with any color (ex: pink) if their values are significant.
7- Apply template to the report.
8- Run the report.
9- Test the repot.
You will learn how to: use query builder, set table relationship, change properties from property palette, use report style matrix, make subtotal for rows and columns, use the “select parent frame” icon, create grid, use the conditional formatting.
Create a new report
In the Object Navigator, highlight the Reports item, and click on the "create" icon (Green ‘+’) to create a new report.
Build a new report Manually
In the New Report window, choose the ‘Build a new report manually’ option, and click ‘OK.’
Create a SQL box
In the ‘Data Model,’ click on the SQL icon on the vertical toolbar. Drag the ‘+’ sign in to the Data Model and click any where that you wish to have your object.
In the ‘SQL Query Statement’ box, write a SQL statement to query all customers with their stocks’ symbol and number of shares owned.
(SQL Query Statement)
SELECT last_name, stock_symbol, shares_owned
FROM portfolio
WHERE ( id = customer_id)
Then click ‘OK.’
Change a SQL box’s property
In the Data Model window, right click on the SQL box (Q_1) and open the property palette option.
In the Property Palette window, change the name to Q_PORTFOLIO. Then close the window.
Report Wizard
In the Data Model window, on the toolbar, select the Report Wizard icon.
Matrix Report
A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results.
Style, Data, Rows, Columns, Cell, Totals, and Template tabs
In the Style tab in the Report Wizard, choose the report style as Matrix with a title of ‘Customer Portfolio.’ Then click on the ‘Next’ icon.
In the Data tab, select the data that you will use in your report. You should have already had that SQL statement. Don’t change anything and click Next.
In the Rows tab, select LAST_name as a "Matrix Row Field" and click Next.
In the Columns tab, use “Stock_symbol” as a "Matrix Column Field" and click Next.
In the Cell tab, select the sum of the shares_owned as a "Matrix Cell Fields" and click Next.
In the Totals tab, to make subtotals for rows and columns, select SumShares_OWNED to calculate the sum of rows or columns {Sum(SumShares_OWNED)}. Then click Next.
In the Labels tab, delete the label for the sum of the shares owned, change Last_name to name, change stock_symbol to symbol, and then click Next.
In the Template tab, use ‘Cyan Grid’ template report and click ‘Finish.’
Navigate through a report
Now, you created a customers portfolio report. Navigate through the report.
Layout Report Editor
Use the layout report editor to change the report layout.
Layout Model
Click on the “Layout Model” icon on the top of the horizontal toolbar.
In the Layout Model window, you can change the size of each item. Use the "select parent frame" icon to go to the parent's column and resize it. When finished resizing, click on the “run” icon to run the report.
Live Pre-viewer
In the Live Pre-viewer, select the name item and then change its alignment to left or right. You can also right click on it, open its property palette, and change its alignment.
Change appearance of a report
Select the cells and change their alignment and format mask. Do the same for the columns' subtotals.
Navigate through the report.
Conditional Formatting
In the Live Pre-viewer, right click on the cells, and open the "Conditional Formatting" option.
In the Conditional Formatting window, you can define exceptions to highlight data for specified conditions with different formatting such as color.
In the Conditional Formatting window, click “New” and In the Format Exception window, change the shares owned value to red if it is greater than or equal 2000.
SHARES_OWNED >= 2000
Click “OK.”
Check the report.
Go to the last page, change the alignment and mask format for the row's subtotals; and change the color if their values are more than 7,000.
Go to the first page.
Do the same for the columns' subtotal; and change the color if their values are more than 10,000.
After testing the report output, highlight the report and save it as report number 4 in the iself folder (REPT04).
Questions:
Q: What is a Matrix Report?
Q: How do you set a table relationship in the report builder?
Q: How do you change an object using its properties palette?
Q: How can you make a sub-total for rows and columns in a matrix report?
Q: What does the Select Parent Frame icon?
Q: What is a conditional formatting in the report builder?
Q: You client needs to get the summary report of their customers shares owned. They like to have the output format be the same as spreadsheet format. You are assigned to create a grid style of data output as a spreadsheet, with rows that present customers name and columns that present stock’s symbol. The intersection of these two entities is a cell that shows the number of shares that customer has on that stock.
This report should show the number of shares of stock’s holders by each customer in each of the stocks. Make sure the cell format mask is NNN,NN0. You should use the customer and portfolio table, and put grid around each number of shares for easy reading. You should also get the totals for each column and row.
They want you to change the cell color to red if its value is greater than 2000 and do the same for rows and columns sub-total.
Your tasks are:
1- Create a matrix report.
2- Put grid around each cell.
3- Use user layout format mask for cells and sub-totals as NNN,NN0.
4- Calculate the subtotal for rows and columns.
5- Highlight the cells with any color (ex: red) if their values are significant.
6- Highlight the sub-total cells with any color (ex: pink) if their values are significant.
7- Apply template to the report.
8- Run the report.
9- Test the repot.
A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results.
Hands-on
You client needs to get the summary report of their customers shares owned. They like to have the output format be the same as spreadsheet format. You are assigned to create a grid style of data output as a spreadsheet, with rows that present customers name and columns that present stock’s symbol. The intersection of these two entities is a cell that shows the number of shares that customer has on that stock.
This report should show the number of shares of stock’s holders by each customer in each of the stocks. Make sure the cell format mask is NNN,NN0. You should use the customer and portfolio table, and put grid around each number of shares for easy reading. You should also get the totals for each column and row.
They want you to change the cell color to red if its value is greater than 2000 and do the same for rows and columns sub-total.
See Figure 4.
Your tasks are:
1- Create a matrix report.
2- Put grid around each cell.
3- Use user layout format mask for cells and sub-totals as NNN,NN0.
4- Calculate the subtotal for rows and columns.
5- Highlight the cells with any color (ex: red) if their values are significant.
6- Highlight the sub-total cells with any color (ex: pink) if their values are significant.
7- Apply template to the report.
8- Run the report.
9- Test the repot.
You will learn how to: use query builder, set table relationship, change properties from property palette, use report style matrix, make subtotal for rows and columns, use the “select parent frame” icon, create grid, use the conditional formatting.
Create a new report
In the Object Navigator, highlight the Reports item, and click on the "create" icon (Green ‘+’) to create a new report.
Build a new report Manually
In the New Report window, choose the ‘Build a new report manually’ option, and click ‘OK.’
Create a SQL box
In the ‘Data Model,’ click on the SQL icon on the vertical toolbar. Drag the ‘+’ sign in to the Data Model and click any where that you wish to have your object.
In the ‘SQL Query Statement’ box, write a SQL statement to query all customers with their stocks’ symbol and number of shares owned.
(SQL Query Statement)
SELECT last_name, stock_symbol, shares_owned
FROM portfolio
WHERE ( id = customer_id)
Then click ‘OK.’
Change a SQL box’s property
In the Data Model window, right click on the SQL box (Q_1) and open the property palette option.
In the Property Palette window, change the name to Q_PORTFOLIO. Then close the window.
Report Wizard
In the Data Model window, on the toolbar, select the Report Wizard icon.
Matrix Report
A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results.
Style, Data, Rows, Columns, Cell, Totals, and Template tabs
In the Style tab in the Report Wizard, choose the report style as Matrix with a title of ‘Customer Portfolio.’ Then click on the ‘Next’ icon.
In the Data tab, select the data that you will use in your report. You should have already had that SQL statement. Don’t change anything and click Next.
In the Rows tab, select LAST_name as a "Matrix Row Field" and click Next.
In the Columns tab, use “Stock_symbol” as a "Matrix Column Field" and click Next.
In the Cell tab, select the sum of the shares_owned as a "Matrix Cell Fields" and click Next.
In the Totals tab, to make subtotals for rows and columns, select SumShares_OWNED to calculate the sum of rows or columns {Sum(SumShares_OWNED)}. Then click Next.
In the Labels tab, delete the label for the sum of the shares owned, change Last_name to name, change stock_symbol to symbol, and then click Next.
In the Template tab, use ‘Cyan Grid’ template report and click ‘Finish.’
Navigate through a report
Now, you created a customers portfolio report. Navigate through the report.
Layout Report Editor
Use the layout report editor to change the report layout.
Layout Model
Click on the “Layout Model” icon on the top of the horizontal toolbar.
In the Layout Model window, you can change the size of each item. Use the "select parent frame" icon to go to the parent's column and resize it. When finished resizing, click on the “run” icon to run the report.
Live Pre-viewer
In the Live Pre-viewer, select the name item and then change its alignment to left or right. You can also right click on it, open its property palette, and change its alignment.
Change appearance of a report
Select the cells and change their alignment and format mask. Do the same for the columns' subtotals.
Navigate through the report.
Conditional Formatting
In the Live Pre-viewer, right click on the cells, and open the "Conditional Formatting" option.
In the Conditional Formatting window, you can define exceptions to highlight data for specified conditions with different formatting such as color.
In the Conditional Formatting window, click “New” and In the Format Exception window, change the shares owned value to red if it is greater than or equal 2000.
SHARES_OWNED >= 2000
Click “OK.”
Check the report.
Go to the last page, change the alignment and mask format for the row's subtotals; and change the color if their values are more than 7,000.
Go to the first page.
Do the same for the columns' subtotal; and change the color if their values are more than 10,000.
After testing the report output, highlight the report and save it as report number 4 in the iself folder (REPT04).
Questions:
Q: What is a Matrix Report?
Q: How do you set a table relationship in the report builder?
Q: How do you change an object using its properties palette?
Q: How can you make a sub-total for rows and columns in a matrix report?
Q: What does the Select Parent Frame icon?
Q: What is a conditional formatting in the report builder?
Q: You client needs to get the summary report of their customers shares owned. They like to have the output format be the same as spreadsheet format. You are assigned to create a grid style of data output as a spreadsheet, with rows that present customers name and columns that present stock’s symbol. The intersection of these two entities is a cell that shows the number of shares that customer has on that stock.
This report should show the number of shares of stock’s holders by each customer in each of the stocks. Make sure the cell format mask is NNN,NN0. You should use the customer and portfolio table, and put grid around each number of shares for easy reading. You should also get the totals for each column and row.
They want you to change the cell color to red if its value is greater than 2000 and do the same for rows and columns sub-total.
Your tasks are:
1- Create a matrix report.
2- Put grid around each cell.
3- Use user layout format mask for cells and sub-totals as NNN,NN0.
4- Calculate the subtotal for rows and columns.
5- Highlight the cells with any color (ex: red) if their values are significant.
6- Highlight the sub-total cells with any color (ex: pink) if their values are significant.
7- Apply template to the report.
8- Run the report.
9- Test the repot.
No comments:
Post a Comment