All parameter implementation: At runtime itself,
the report selects “All” which selects all the columns he wants to select to
display on the report.
Advantage: When the report selects “All” it
couldn’t hit the where condition in the report.it improve the query &
report performance at run time.
1. Building Tabular Model
In Microsoft Visual Studio, Go to File menu >> New
>> Project.
Select Business Intelligence Analysis Services &
Analysis Services Tabular Project. Provide the project name & location.
It then asks for the workspace server details.
The project gets created. It has a model (bim file) which
stores all the details of the tabular project.
Create Data Source Connection. Here, we use SQLserver
connection.
We directly import five tables from Data source. The
model gets created as-
Save the project. Build & Deploy it on the required
server.
After building a simple Tabular Model, we’ll build report
on it & use various reporting features.
2. Building Report & Various Features
In Microsoft Visual Studio, Go to File menu >> New >>
Project.
Select Business Intelligence Reporting Services &
Report Server Project. Provide the project name & location.
The project gets created. In solution Explorer, Add a new
report.
We’ll make a simple Report.
Create an Analysis services Data source connection for
report, pointing to the Tabular Model created.
Then, create a dataset for the data source.
We will use DAX expressions & Query Designer &use
the design mode to command type DMX.
In the Field section of Dataset Properties, we can see
the list of all columns pertaining to the table referred. We can rename the
field names as per our need.
In the Design View, Create a simple table enlisting all
columns in the desired order.
The report gets created.
Filter Parameters & “All” Feature
To
filter records-
·
Category
Add
parameter Category for user selection.
Set
Default Values as “All”. To run the report automatically by selecting “All”
while running the report.
At runtime it comes as “All” which selects all the data
at runtime.
Dataset’s Creation:
We need to create 2 dataset’s for this report.
1. DS_Main:
DAX:
Evaluate
(
Summarize
(
Calculatetable
(
'Internet Sales',
(PATHCONTAINS
(@Category,'Product Category'[Product Category
Name]) || @Category=" All" ) ),
'Product
Category'[Product Category Name],
'Product
Subcategory'[Product Subcategory Name],
'Product'[Product Name],
'Date'[Calendar Year],
"Total Sales Amount", sum('Internet Sales'[Sales
Amount])
)
)
Order by 'Product Category'[Product
Category Name] ASC
We have to pass the pathcontains with OR (||) condition
to select “ All” by default.
Note:
Provide the space for “ All” to display on top the data.
2. DS_Fltr_Category
We’ll create a separate dataset DS_Fltr_Category to
populate list of values for Category parameter using table ‘product Category’.
DAX:
Evaluate
(
Summarize
(
(
AddColumns (
(
Calculatetable
(
Summarize
(
'Product Category',
Rollup ('Product
Category'[Product Category Name])
)
)
),
"Category",
If (ISBlank ([Product Category Name]), " All",[Product
Category Name] )
)
),
[Category]
)
)
ORDER BY [Category] ASC
It will list all the Categories in a sorted order.
We’ll add a new parameter ‘Category’ and allow multiple
values. Allow multiple values option enables user to select more than values
from the available list.
It will take all available values from dataset ds_fltr_category
values.
Give the space on value “ All” to get the All on top of the data.
The Parameter is defined and JOIN expression is given to
handle multiple values of Category selected by user.
By default “All” selects by during runtime of the report
to display.
