after looking at this table please answer the question please make sure to answe

Question

after looking at this table please answer the question

please make sure to answer wuth pictures

thanks!

Create and save a data entry form to input more records into the table. Use the form to add 10 more records and save the database with the extra records. Create SQL queries to do the following:

Display the total ticket sales to males

Display each event (only once) in all upper-case letters

Display the total number (not $ amount) of sales of Rodeo tickets

Save all 3 queries.

Finally, export the entire table to an Excel spreadsheet and save it. Submit the database file with your form, the records you added and your queries, and also submit your Excel file.

11 ArenaTicketSales All Access Objects earch... Tables ID Event Day Gender Sales Click to Add 1 Arena Football Monda 2 Tractor Pu Monday 3Tractor Pu Monday 4 Rodeo 5 Rodeo 6 Rodeo 7 Rodeo 8 Arena Football Tuesda 9 Arena Football Tuesday $200.00 $300.00 $100.00 $250.00 $300.00 $100.00 $175.00 $175.00 $50.00 $100.00 $50.00 $100.00 $100.00 $125.00 ArenaTicketSales Monda Monday Tuesday Tuesday 10 Tractor Pull Tuesday 11 Arena Football Wednesday M 12 Tractor Pu Wednesday M 13 Rodeo 14 Rodeo Wednesday M Wednesday (New

Explanation / Answer

Use a Multivalue Parameter Inside a Report

The properties exposed on a report parameter object include IsMultiValue, Count, Value, and Label. You can access the value and label of a multivalue parameter as zero-based arrays.

=Parameters!<paramName>.IsMultiValue—The parameter defined as multivalue.

=Parameters! <paramName>.Count—Returns the number of values in the array.

=Parameters! <paramName>.Value(0)—Returns the first selected value.

=Parameters! <paramName>.Label(0)—Returns the label for the first selected value.

=Join(Parameters! <paramName>.Value)—Creates a space separated list of values.

=Join(Parameters! <paramName>.Value, ", ")—Creates a comma-separated list of values.

Pass the Value of a Multivalue Parameter to a Subreport

Directly pass the value of a multivalue parameter from the main report to the subreport:

Create a multidimensional object array on-the-fly =Split("a,b,c", ",") and pass it to a multivalue parameter of the subreport.

Pass the first selected value from the main report's parameter:

Using RDL from SQL Server Reporting Services 2005 in SQL Server Reporting Services 2000

When you open a Report Definition Language (RDL) file that was created in SQL Server 2000 Reporting Services in a SQL Server 2005 version of Report Designer, or publish it to a SQL Server 2005 report server, the file is automatically upgraded to SQL Server 2005 format. But is it possible to downgrade a RDL file so that it can be used with the 2000 version of the product again? There is no automated tool to do this, but you can modify the XML file manually to accomplish it.

To modify the RDL file

Report Samples

Reporting Services supports a variety of report items and built-in functions for report authors to use when designing reports. While creating simple reports has been made quite easy, it can be problematic to find solutions for report display and/or calculations that are not very straightforward. This section covers some of these more complex scenarios to help report developers achieve the functionality they are looking for by using combinations of or alternatives to existing features.

Custom Code for Aggregates

Aggregate of aggregates and aggregate of report items are not currently supported. There have been requests for other aggregate functions that are not currently available. Many times, the desired behavior can be achieved through custom code.

Scenario 1: Median()

Median is a commonly requested aggregate function that is not yet supported in Reporting Services.

To calculate median by using custom code

Figure 1

Figure 2 (Click on the picture for a larger image)

Figure 3 shows what the final report looks like.

Figure 3 (Click on the picture for a larger image)

Scenario 2: Distinct Sum

If a dataset contains values for duplicate records, you don't want to count those values multiple times. Reporting Services supports a CountDistinct function, but there is not a similar function for Sum. You must do this by using custom code.

To calculate distinct sum by using custom code

Figure 4

Figure 5 (Click on the picture for a larger image)

Figure 6 (Click on the picture for a larger image)

Getting Data from Different Sources

Reporting Services doesn't currently support using multiple datasets in one data region. To display an aggregate value from the dataset, specify the dataset scope in the aggregate function; for example, =Sum(Fields!Freight.Value, "DataSet2"). This syntax works even in a data region that is bound to a different dataset. If, however, you need to correlate the data from the two datasets in one data region, use the following two workarounds:

To use the subreport approach

Figure 7 (Click on the picture for a larger image)

Figure 8 (Click on the picture for a larger image)

Figure 9 shows the rendered main report.

Figure 9 (Click on the picture for a larger image)

Changing the Report Structure at Run Time

A frequently asked question is whether it is possible to change the report structure at run time. You can, of course, dynamically generate RDL files in your custom application. There are also other ways to modify the structural elements in the report definition to achieve this. This section describes various scenarios and solutions.

Dynamic Field Based on Parameter, Dynamic Grouping

How do I decide which field value to use dynamically—for example, based on a parameter in my report? How can I give my users the ability to dynamically select the fields on which to group within a report?

Use this syntax to refer to a field value in your expression:

To change the field reference and group expressions based on user input

Figure 10 (Click on the picture for a larger image)

Figure 11

Figure 12 (Click on the picture for a larger image)

Here is how the report looks when GroupOrdersBy is changed to Employee:

Figure 13 (Click on the picture for a larger image)

Changing the Filter Operator at Run Time

It is not possible to change the filter operator at run time, but you can include all the conditions in the filter expression and leave the filter operator and the value as static (filter operator is =, and filter value is =true). This way, by dynamically switching the conditions in the filter expression by using IIF, you can achieve the effect of changing the filters based on user input.

To dynamically change the filter conditions based on parameter value

Figure 14 (Click on the picture for a larger image)

Figure 15

Figure 16 shows how the report looks when the value of FilterOp is set to GreatThanOrEqual.

Figure 16 (Click on the picture for a larger image)

And Figure 17 shows when the user changes the parameter value to LessThan:

Figure 17 (Click on the picture for a larger image)

Dynamic Columns

I'd like to programmatically determine the columns to display based on what is returned by the dataset. Is that possible?

Yes, you can define a table with all the possible columns, then decide whether to show each column based on the value of the IsMissing property on Field. Here is an example that shows information from two different database tables based on user selection.

To add dynamic columns to your report

Figure 18 (Click on the picture for a larger image)

Figure 19 (Click on the picture for a larger image)

Dynamic Rows

My dataset contains a large amount of data. Can I control how many rows are shown initially through a parameter, and toggle the rest?

Yes, set the Hidden property on the rows to:

Set the ToggleItem to a text box that you want to toggle the rest of rows with, as shown in Figure 20.

Figure 20 (Click on the picture for a larger image)

InScope Function

My matrix shows the percentage of the sales to a customer out of the total sales for each employee. In the subtotal cells, this is always going to be 100 percent, which is not useful. How can I get different contents in the subtotal cells?

Use the InScope function in your expression to do this. For example, suppose you have a row grouping (called "matrix1_CustomerID"), and a column grouping (called "matrix1_EmployeeID"). To show different contents in the column subtotal cells, set the cell text-box expression to the following:

Dynamic Page Breaks

For the logical page breaks defined in the RDL, the only options seem to be turning them on or off. There is no expression for them. Is it possible to dynamically change which groups have page breaks either before or after?

It is possible, although not very straightforward. You create a dummy group with page break at end equal to true. The expression for the group will be set according to a parameter value.

To add dynamic page breaks based on the parameter value

Figure 21 (Click on the picture for a larger image)

Figure 22 (Click on the picture for a larger image)

Figure 23 shows the report when the page break is turned on.

Figure 23 (Click on the picture for a larger image)

And Figure 24 when it is turned off:

Figure 24 (Click on the picture for a larger image)

Resetting the Page Number on a Group

I have many pages and many groups in my report. I can use Globals!PageNumber to display the current page number in the page header, but I want to reset the page number back to one every time I enter a new group. How do I do this?

Resetting the page number on group breaks is not natively supported, but it can be achieved by tracking group breaks in a shared variable in custom code and subtracting off the page offset of the first page of the group from the current page number.

To reset the page number at the start of each group

Figure 25 (Click on the picture for a larger image)

Figure 26

Figure 27 (Click on the picture for a larger image)

Note   Because this solution uses static variables, if two people run the report at the exact same moment, there is a slim chance that one will break the other's variable state.

Horizontal Tables

Is it possible to rotate a table in Reporting Services? Usually a table grows vertically, based on the number of detail rows. What if I want it to grow horizontally?

Try using a matrix instead of table. The table header becomes a static matrix row header. The report detail would be the column groupings (you'll need to group on a field that's unique across different rows if you want each data row to show up as a column). Put the footer in the subtotal column (you may need to use the InScope() function to display contents different from the detail information).

Figure 28 shows a vertical table and a horizontal table that display the same set of data.

Figure 28 (Click on the picture for a larger image)

Green Bar

Can I create a table or matrix in which every other row is shaded?

Table

Use =IIF(RowNumber(Nothing) Mod 2, "AliceBlue", "White") to control the background color property conditionally.

Figure 29 (Click on the picture for a larger image)

Matrix

It is more difficult to create a matrix with every other row shaded because every row in the matrix must be a group. There is currently no GroupNumber() function on which to base a green-bar calculation. However, GroupNumber can be (mostly) simulated by using the RunningValue function to return a running distinct count of group expression values.

To create a matrix with the green-bar effect

Figure 30 (Click on the picture for a larger image)

Figure 31 (Click on the picture for a larger image)

Figure 32 shows the rendered matrix.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.