How to create a report with Report Builder (2)

Add parameters to a report

In the left sidebar of Report Builder, right-click the Parameters folder and select Add Parameter…

In the Report Parameter Properties window, the left panel shows these sections:

• General: select a Data type from the drop-down menu, add Name and Prompt.

• Available Values

• Default Values: select Specify values and Add to enter a default value for a parameter.

• Advanced

* Parameter data types currently supported = Text, Date/Time, and Integer.

(data types not supported at this time = Boolean and Float)

Open

Open

Add a Text parameter

In the General section, select the Text data type from the drop-down menu.

Add an Integer parameter

In the General section, select the Integer data type from the drop-down menu.

The Integer data type is used in SingleID and MultipleID query arguments (see below).

Add a Date/Time parameter

In the General section, select the Date/Time data type from the drop-down menu.

You can enter either a default date or a VisualBasic (VB) script.

• The script example here sets a default date of the first month of the current year.

• Change from Year to Month by simply inserting "Month" in place of Year.

• Results of this parameter are shown in the calendar below.

Save parameters

• Click OK to save your parameter.

• You will now see the parameter in the Parameters folder in the Report Data window.

• Make sure to save the Report builder file.

Add a custom single query

To add a custom SQL SingleID argument , you first need to write a query:

• Right-click the Datasets folder in the Report Data window and select Add Dataset.

• In the Dataset Properties window, enter a name for your query in the Name box.

• Select the radio button for Use a dataset embedded in my report.

• For Query Type, select Text.

You are now ready to enter your custom Query in the Query box.

Note: In order for the query to work in Crows Nest you must include an ID and Description.

In this example, We are pulling the ID and Description from the Employee table in the Crows Nest Database. Then, we filter by sub-query WHERE the ID in the following set of data in the parentheses. This sub-query selects Employee from the Project_Role_Assigned table where you assign roles to a project, and joins it to the Project_Role table that contains a list of roles available. So, the Project_Role_Assigned table joins the Employee and Project_Role tables. Then we can look at the Project Role description, and by using LIKE along with the % wildcards, we can have the query return any characters along with the characters Estimat. As a result of this query, any project role with the word the characters Estimat will be returned to the list.

Note: in this example, the Employee table does not have a Description, it has a ScreenName, so we must rename the ScreenName to Description for the query to work.

Here is the example run in the query designer. Query designer can be reached by right-clicking on the dataset that you created and clicking Query. This query has returned a list of employees who fulfill the criteria. The two employees are assigned the Estimator role in Crows Nest.

In the next example, we have designed a query to list all of the employees in this Crows Nest Database.

Note: You cannot use 0 as a value for the ID in Crows Nest. You may use a negative value, but you cannot use 0 as a valid ID.

Once you are satisfied with your query, you can click OK on the Dataset Properties window. Now, you are ready to create the parameter. Right-click on the Parameters folder in the Report Data window and click Add Parameter.

The Report Parameter Properties window will open. Add a name to parameter in the Name box. Add a Prompt in the Prompt box. For Data Type, select Integer from the dropdown box. For this custom single query, we are calling it Estimators.

Next, select Available Values from the left-hand pane. Select the radio button for Get values from a query. For the Dataset dropdown menu, select Estimators. For the Value field, select ID. For the Label field, select Description. Click OK.

You will now see the parameter in the Parameters folder in the Report Data window.

Make sure to save the Report Builder file.

Add a custom multi-query

To add a MultipleID parameter in Crows Nest, you need to add a custom multi-query. To do this, right-click the Datasets folder in the Report Data window and select Add Dataset.

You will now see the Dataset Properties window. Type a name for your query in the Name box. Then, select the radio button for Use a dataset embedded in my report. For Query Type, select Text. You are now ready to enter your custom Query in the Query box. The query entered in the dataset below, when added with the Estimators dataset and the parameter we will create below will allow us to select multiple estimators when running the custom report. Note: when Crows Nest runs the Employees query, it will find the MultiEstimator parameter and run a search and replace on the value you select in Crows Nest before running the query. So when using the Multiple Values option, the parameter is replaced with a comma delimited string. This is the reason we use IN to allow us enter in a list of comma separated values and match any one of the values.

Now, you are ready to create the parameter. Right-click on the Parameters folder in the Report Data window and click Add Parameter.

The Report Parameter Properties window will open. Add a name to parameter in the Name box. Add a Prompt in the Prompt box. For Data Type, select Integer from the dropdown box. For this custom multi-query, we are calling it MultiEstimator. Make sure you check the box for Allow multiple values, as this must be checked for a custom multi-query to work.

Next, select Available Values from the left-hand pane. Select the radio button for Get values from a query. For the Dataset dropdown menu, select Estimators. For the Value field, select ID. For the Label field, select Description. Click OK. Note: By combining the Employee and Estimators datasets, we are able to create a multi-query that will allow you to select multiple estimators in the custom report.

You will now see the parameter in the Parameters folder in the Report Data window.

Make sure to save the Report Builder file.

Note: you will need to click and drag or shift-click to select the multiple items.

Create output for the report

Now you need to add output for the report, otherwise the parameters will run in Crows Nest but you will receive no output. There are a number of ways to do this in Report Builder.

In our example, we will insert a table that outputs employees full names. To do this, from the main report builder main menu, click Insert, select Table, and select Table Wizard.

Next, choose the Employees dataset and click Next.

Now, drag Fullname from Available fields to Values. Click Next.

Click Next again, as the Choose the layout window does not apply to this table. Finally, click Next at the Preview window. You'll be taken back to the main Report Builder window. Now you'll just need to resize the table to your preference and you can align the text as well.

Import to Crows Nest

Now, we need to import the .rdl file we created in Report Builder into Crows Nest. From the main menu bar, click Settings, and select Custom Reports.

You will now see the Custom Reports window. In the left-hand pane you will see the Reports currently loaded in Crows Nest. In the right-hand pane you will see the Report Definition in XML.
To import your custom report, right-click in the left-hand pane and select Batch Add/Update.

You'll be prompted to open the file through Windows File Explorer. Select the .rdl file

Crows Nest will now import your .rdl file(s). If it loaded properly you will see the name of the report listed in the Report pane. The CN.rdl file we created has been loaded in. You can press the + button next to the report to see the Datasets and parameters that are part of the file.

Now you can go to the Reports form and run your custom report. The name of the custom report will be the name of .rdl file you imported.