Translate

Monday, April 25, 2011

How to Setup Report Parameters to Default Based On User Credentials

You've been asked to build a sales report for your client. One of the requirements is to have the sales region automatically filter to the sales reps region when the user accesses the report. You have the report built, but are not sure how to set the report to automatically filter based on the specific user of the report.
I this article I will walk you through how to construct parameters to default values based on user credentials. For demonstration purposes I am using Sales Region as it is a common filter in sales reports. However, you can use these same instructions for any number of parameters.
(Note: The following article was written using SQL Server 2008 R2 and backwards compatibility has not been tested.)

Assumptions:

  • You have access to a sql server database and the ability to create tables.
  • You have a Reporting Services report already built and need to change the report behavior to default parameter values based on user credentials.

Create Report Users Table

You'll need a place to store user credentials and parameter values. To do this, create a report users table in your sql server database.

CREATE TABLE [dbo].[ReportUsers](
 [UserID] [int] IDENTITY(1,1) NOT NULL,
 [UserName] [nvarchar](50) NOT NULL,
 [SalesRegion] [nvarchar](50) NOT NULL
) ON [PRIMARY]
At this point you don't need to populate the table with any data. We'll get to that in a moment. The key fields are UserName which will be the value passed from the Reporting Services report and SalesRegion which will hold the parameter value.
You'll need to ensure that your parameter matches the data type of the field you are creating in your report users table. For example, if your parameter is nvarchar(100) you'll need to make your SalesRegion field nvarchar(100).

Create UserID Parameter in Your Report

To create the UserID parameter, start by creating a parameter in reporting services.

Setup the General Settings:

  • Data Type must be Text
  • Visibility is set to Hidden (your choice here, but users won't be changing this value so Hidden works best).

Click on Available Values and enter [&UserID] into the Label and Value boxes.
  • [&UserID] is a built in variable in reporting services.
  • You can add this value using the expressions button or simply type it into the label and value fields.

Click on Default Values and [&UserID] into the value box.

Click ok.
One last step, you need to ensure that the parameter you just created is at the top of the list of parameters for your report.
If parUserID is not at the top of the list, click on the parameter and click the up arrow until it is. Your parameters should now look something like this.
(Note: Reporting services sets parameters in order or precedence based on the top to bottom list. Our UserID parameter needs to be first on the list in order for the Sales Region parameter to receive its value.)

Sales Region Data Set

So far you've done the basic stuff. Now comes the part I found most difficult. Rest assured though, I am going to take you through this step-by-step and show you some tricks along the way.
I'm assuming you already have a sales region parameter setup. So let's take a look at how you constructed that parameter.
First open the SalesRegion data set.


In order for the Sales Region parameter to accept the UserID parameter, you need to setup your query to identify the user and find the correct sales region value. The query below does this for you:
IF @parUserID IN (SELECT UserName FROM dbo.ReportUsers)
BEGIN
   SELECT DISTINCT 
     SalesRegion
   FROM datatable 
   WHERE 
      (SalesRegion IN
      (SELECT SalesRegion
       FROM ReportUsers
      WHERE (UserName IN (@parUserID))))
END
ELSE
 SELECT DISTINCT 
 SalesRegion
 FROM datatable
 WHERE SalesRegion IN ('Central', 'West', 'East')
Add this query to your Sales Region data set. As shown in the screenshot.
This SQL statement is doing two major things for you. First it is looking for the user's UserID in the ReportUser's table we created. And secondly, if that userID is not found it is selecting all sales regions. This is critical when you have users who are not assigned a sales region or are not in your report user table.

Sales Region Data Set #2

Create another data set and this time call the data set "dsSalesRegion2". Set your query as such:
SELECT DISTINCT 
 SalesRegion
 FROM datatable
 WHERE SalesRegion IN ('Central', 'West', 'East')
Your datasets on your report should now look like this:

Sales Region Parameter

Open your sales region parameter and go to the Available Values settings. You're going to set your Available Values settings to the Sales Region #2 data set. This comes in handy when you have users are not assigned to specific regions such as sales directors or GM level users.


Set the default values:



You have effectively setup the sales region parameter to default based on the user credentials and also setup the parameter to display all regions. This allows your users to select any region they want even though the report auto defaults.

Report User Data

Now that you have your report user table created, your userID parameter setup and your sales region parameters ready to go, all you need to do is add data to your report user table.
The UserID variable in Reporting Services uses DOMAINNAME\username as it's format. So when you add users to your report user table, you'll need to use this same format when inserting records. For example:
INSERT INTO dbo.ReportUsersVALUES
 (
 'ACME\jsmith',
 'Central'
 )
I suggest you first add yourself to this table so you can unit test the report. If you have done everything correctly, the next time you run the report your sales region should automatically default to the value you entered into the report users table.

Helpful Tips

  1. At first you may want to make your parUserID parameter visible to help with testing.
  2. You cannot Cache a temporary copy of the report in report manager if you using a Shared Dataset with stored credentials.
  3. If you only want report users to view their default region, you can set the Available Values in the Sales Region Parameter to use the dsSalesRegion data set instead of the dsSalesRegion2 data set.

Conclusion

SQL Server Reporting Services built-in functionality and some strategic SQL code is all you need to default parameter values based on user credentials. In this article, I explained how to accomplish this using a common filter such as sales region. You should now be able to take this knowledge and apply it to any user driven parameter.

Saturday, April 16, 2011

What’s new in Dynamics Ax 2012?

Finally Microsoft has released some documents about the long waited Microsoft Dynamics AX 2012These new training materials highlight the new features in Microsoft Dynamics AX 2012.  These materials are currently downloadable on PartnerSource (Partner Service Plan required).   They will be orderable (can be purchased) beginning April 21.

Wednesday, April 13, 2011

Tablix Tutorial - Reporting Services


Tablix Tutorial - Reporting Services


Table + Matrix + List = Tablix

The tablix, the SSRS vehicle used to display rows of data, is a new report item introduced in SQL Server Reporting Services 2008.  Three previous report items: the table, the matrix, and the list, were combined into one: the tablix, due to their similarities.  A view of what the tablix contains is shown in Figure 1.
Figure 1. Tablix View


To start using the tablix, look at the Toolbox in Business Intelligence Development Studio (BIDS); however, you will not see a tablix report item.  Instead, you will see the table, the matrix, and the list are shown separately, as seen in Figure 2.  No matter which item you pull to the designer, Reporting Services will still use a tablix under the covers.
Figure 2. Toolbox Showing the Tablix Templates
Note: If you do not see the Toolbox in BIDS, click the View menu > Toolbox selection or type Ctrl + Alt + X.  In the Toolbox, all report items are under the Report Items header.
Let's start by looking at the first tablix template, the table.

Table

A table is simply an object that displays rows of data that can be grouped and aggregated.  If our data contains a static list of columns, we want to use the Table report item.  Let's start by pulling over the Table report item from the Toolbox.  Since we've already created our data source and dataset, we can start setting up the table.  Let's begin by selecting data for the table to display.  Each report item can only show data from one dataset, so you will need to specify the desired dataset.  Set the dataset by going to the Properties pane > DataSetName property > and selecting your dataset.  The Properties pane can be seen in Figure 3.


Figure 3. Properties Pane with DataSetName Property
Now, we can use the dataset fields to design the display of the table.  There are a few different ways to set up the fields on our table:
·         Pull over the field from the Report Data pane
·         Select the field from the drop down list in the top right corner of each textbox as shown in Figure 4
·         Go into the textbox's properties and select the field from the dropdown list
 Figure 4. Table Field Selection
Whichever method you use, a simple layout will look similar to Figure 5.
Figure 5. Simple Table Layout

Grouping
A tablix displays a table row for every dataset row through the "Details" group.  The Details group provides a basis to help us understand other grouping concepts.  Every time Reporting Services sees a unique row, it will create a new detail group.  In the same way, additional groupings will create a new group for every unique value that it finds.  Let's walk through creating a group to illustrate the concept.
Begin by going to the Grouping panes.  In editions prior to SSRS 2008, you can find these panes under the Properties window of the table and matrix.  In later editions, you will see these panes at the bottom of the designer in BIDS, as shown in Figure 6.

Figure 6. Row and Column Grouping Windows
Because we are working on a table template, let's create a row group.  Select the down arrow to the right of the Details group in the Row Groups pane.  You have multiple options, including Add Group.  Select the Add Group option > Parent Group option, and select the desired column.  Check the checkboxes to include a group header and footer in the Tablix group window.  A completed screen can be seen in Figure 7.

Figure 7. Completed Tablix Group Window
Once you select OK, you will see the table design, as shown in Figure 8.

Figure 8. Table Design with Group
You describe each column by typing a title or aggregation into the header row or footer row.  Let's create a total for the group by using an expression.  Right-click on the intersection of the last column and last row of your table.  Each intersection contains a textbox, so you are actually clicking on the textbox that marks the spot!  Select Text Box Properties… to open the Text Box Properties screen.  In the Value field, enter =SUM(Fields!TotalDue.Value) as shown in Figure 9.

Figure 9. Text Box Properties
Before running the report, let's look at the matrix template of the tablix.  We discussed grouping before covering the matrix because it is the core of understanding the difference between a table and the matrix, coming up next.

Matrix

While very similar to a table, the matrix adds the ability to group and aggregate on columns as well as rows.  This results in the display of data growing along the x-axis as well as the y-axis.  You create column groups similar to row groups, except instead of making the changes in the Row Groups pane, you use the Column Groups pane.  An example of a report that utilizes the matrix template shows sales for each country for several months.  The design view of this report can be seen in Figure 10.

Figure 10. Matrix Layout

List

The list is often forgotten when discussing the tablix because it just too darn simple!  A list contains one row and one column and repeats for every dataset row.  If you need to create a report that doesn't follow any of the formats we've seen up to this point, you'll want to use a list.  You can place textboxes within the list to create a freeform design to match any design your end users can dream up.

Formatting

Your end users may also want to format the data in different ways.  Because formatting is applied at the text box level, you can make these changes to the tablix in table, matrix, or list form.  On our existing table-based report, let's make some formatting changes: Column sorting and Font properties.
Column Sorting
Almost every report you write needs column sorting to show your data in a particular order.  Begin by single left-clicking anywhere in the tablix.  Once the table is highlighted, you will see a little gray square in the top left corner of the tablix.  Right-click on this square, and select Tablix Properties…, as shown in Figure 11.

Figure 11. Select Tablix Properties window
In the Properties window, select the left menu entitled Sorting.  Select the desired column under Column, and A to Z underOrder to sort the column in ascending order.  The completed screen can be seen in Figure 12.  Click OK to exit the menu.  Keep in mind that any group can also have a sort, so if the output is not as expected, ensure other sorts are not overriding the one you just created.

Figure 12. Completed Sorting Pane
Font Properties
Next, let's move onto font properties, a fancy term for making the font look pretty.  To use this feature, select all header columns in the tablix.  In the Properties pane, go to the Font property > FontWeight property > Bold selection.   Selecting this property can be seen in Figure 13.

Figure 13. Properties Pane with Font Property Selection
After all of the preparing, grouping, and formatting, we have a finished report!  Select the Preview button to see what we have completed up to this point, as shown in Figure 14.

Figure 14. Final Report View



Saturday, April 9, 2011

Tricks X++

Tricks with X++ embedded SQL
There are some tricks that you can do in X++ SQL. They’re not really well known because they’re not suited for normal use and there are not many clues in the editor that they exist. But they do come in handy every now and then.
Getting data without declaring a table variable
It’s possible to get data from the database without declaring any table variables. It goes something like this:
static void Job3(Args _args)
{
    // No variables!
    ;

    print (select CustTable).AccountNum;
    pause;
}
Note the parentheses around the select statement. This tells the compiler to create a temporary, anonymous buffer to hold the record. The select statement uses the exact table name and is immediately followed by a field name to get a value from a field.
If you use this there’s no Intellisense to help you look up the field name because the editor doesn’t know the type of the anonymous buffer.
This technique is often used in exist() methods on tables.

Getting a value from a field if you only know the field ID

Sometimes you don’t know the exact field name but you do know a field ID. Even in cases like this it’s possible to write an X++ SQL statement without knowing any field names.
Take a look at this example.
static void Job4(Args _args)
{
    CustTable   custTable;
    FieldId     fieldId;
    ;

    fieldId = fieldNum(CustTable, AccountNum); // This could be passed as a method parameter

    select custTable; // Get first record

    print custTable.(fieldId); // Print account number

    select custTable
        where custTable.(fieldId) == '1101'// Where clause based on field ID

    print custTable.Name;
    pause;
}
It’s possible to use field IDs to retrieve values as well as use them in a where clause. Again parentheses are the key. Instead of writing a regular field name, wrap the ID in parentheses and you’re done. This makes it possible create generic code to handle any table and field without knowing the types at compile time. If you dig around in the classes for importing and exporting data you’ll find some examples.


Wednesday, April 6, 2011

Newsletter-Style Report (Reporting Services)

How to: Create a Newsletter-Style Report (Reporting Services)



Multicolumn, newsletter-style reports display report data down multiple adjacent columns. A newsletter-style layout applies to the entire report. When you define more than one column in a report, Report Designer calculates the width of the columns in the report, the width of the report, and the width of the space between columns based on the number of columns. It then displays a reduced design surface so that you can place report items on the report that will fit within the column. Note that the layout of the entire report must be placed on the reduced design surface. Additional columns are displayed so that you can verify that the number of columns you defined will fit the page size dimension. You can adjust page size, padding, and margins to fit more columns on the page.
Only PDF and Image renderers support newsletter-style reports.


To create a newsletter-style report
1.        In Design view, select Report from the drop-down list in the Properties pane. The report properties appear in the Properties pane.
Note
If the Properties pane is not visible, click Properties Window on the View menu.

2.        In the Properties pane, expand the Columns node.
·         In Columns, type the number of columns in the report.
·         In ColumnSpacing, type the width of the space between columns.
Note
The default unit of measurement is based on the user's locale settings. To designate a different unit, type a physical unit designator such as cm, mm, pt, or pc after the numeric value.