SSRS
Data Source
To establish the connection between reporting platforms and the data sources. It has 2 types
Shared data source and normal data source(private)
Shared data source:
It is a public connection string. we can reuse the same data source on multiple reports in the entire project
If we made any changes in the connection string It automatically reflects everywhere.
It is used in real-time for multiple reports
Normal data source:
28/05
Drill through action
Used to navigate between the reports
We need to pass the parameters to it
Just create a normal report and group any column and then remove the rows by making it to show only subtotals(just remove those rows by selecting it)
It shows only the grouped column names and subtotal values.
Next, create a details report by taking another new report in that you need to pass at least one parameter
Select * from a test where place=@place
Now right click on the column (1nd report drill_through summary) → go to text box properties → goto action → goto report → specify the report (which you want the details from) → go to add → add name & value of the parameters → enter ok
Add tooltip (msg which you want to display at hand type cursor) in textbox properties
When you click on the place it navigates to the details report. if you want you can hide the parameter in parameter properties. Then the output will be as shown below
Drill down report
Create a normal tabular report group 1 column and then group another column add subtotals to each group-wise and then add total as final grand total
Now hide the details of columns
→ goto Row groups →details → goto group properties → visibility → click on hide and if you want to toggle the value just enable toggle option on below and select the required column
Now you need to hide the dept id col as shown in below
Row group → dept id → grp properties → visibility → hide → enable toggle → add column
Note : create drill down by the hierarchy of down to high
Sub Report
Create a normal dataset by using a parameter
Select * from test where place=@place
Create another report called the main report here add the dataset using required columns
the select place from test
Insert any report like table or matrix etc.. insert→ matrix →
Right click on empty col → insert → sub report →
Goto sub report properties → give name → to choose the required detail report
Goto parameters → add → place
27/05
SSRS reporting services:
Default port num: 80
Ssrs 2012
Ssrs 2016 → modern features compared to 2012
Mobile reports development, ui changes, and some other controls added in it
Ssrs 2017 → latest version
Ssrs 2019 → pro version
Features in 2017 :
add comments to the report
If you are an admin you can delete comments otherwise you can’t delete only views.
DAX queries:
We do write on ssas tabular models
Earlier we can write mdx queries only, but now we can write dax queries as well
You can write “dax queries in report builder as well as in the ssdt tools”
Rest API
We can connect to the application interfaces to fetch the data
In ssrs another feature is Query designer. You can use it to write dax queries.
It Can use it in report builder and ssdt as well.
Web service url : Give virtual directory name
Web portal url : give portal site identification name
Database: goto change database → use existing db or create new db → enter details → db name →
Report deployment:
Right-click on solution explorer → properties → target server URL → get the web service URL from the report conf manager and paste it
Report → build → deploy
Subscriptions:
can’t run with windows authentication
is a configuration that delivers a report at a specific time or in response to an event, and in a file format that you specify.
Subscriptions can be used to schedule and automate the delivery of a report and with a specific set of report parameter values.
Destination:: windows file share (shared drive ) or email
Types
Standard subscription → generate and deliver one report
Created and managed by individual users and contains the static values
Data-driven subscription → generate and deliver one report for each row in a dataset
It should be noted that data-driven subscriptions are only available in Enterprise Edition.
It gets subscription information at run time by querying an external data source that provides values used to specify a recipient, report parameters, or application format.
Instead of deleting subscriptions We can enable or disable subscription in ssrs 2016 or 2017
Prerequisites:
SQL server agent should be in running mode → Because the schedule is managed by sql server agent
The report should be deployed and running fine without any errors
Need to setup Subscription config
Report → click on 3 dots → goto manage → goto data sources(on left menu) → check for the sql server credentials test them if it's fine then goto subscriptions give these details
Description
Owner
Choose the type of subscription
Daily Schedule
Start time and dates
Destination → windows file share (shared drive ) or email
Add delivery options
Even after you disable the subscription, It runs but is not able to generate a report
because it identifies as disabled.
Check it in sql server agent history
If you delete subscription then sql server job also deleted
Report parameters
It enables the users to filter the SSRS reports dynamically (or) it allows the user to enter a specific value in the textbox
It provides the output based on user input
It has 3 types
Single valued parameter
Multi-valued parameter
Cascaded parameter
Steps to create parameter:
Create new report
Create Data source
Create Parameter
Create Dataset
Link the parameter with the dataset
Design report and observe the output
Single valued:
Report data → parameters → add parameters → add report parameter properties
Name: specify the parameter
Prompt: text displays at user
Datatype: parameter data type
We can add parameters in many ways one of the way is using “@” symbol
select top(50) * from tableau where [Item Category]=@item_category
Multi valued
Report data → parameters → add parameters → add report parameter properties
Name: specify the parameter
Prompt: text displays at user
Click on allow multiple values → goto available values(on left menu) → goto get values from the query →
Datatype: parameter data type
Create 2 data sets 1 for parameter
And other is for report body
select distinct [item category] from tableau
select top(50) * from tableau where [Item Category] in (@item_categoy)
Cascaded parameter
One parameter value depends on other parameters
Require multiple datasets. For every data set need to create a parameter as specified in the above.
3 datasets → 2 for parameters and other for report body
Queries:
select distinct [item category] from tableau
select distinct [customer location] from tableau
select top(50) * from tableau where [Item Category]=@item_category and [customer location]=@ customer_location
27/05
Interactive Sorting
To apply asc / desc order to the columns
Applied on 2 types
Detail rows
groups
1. Detail rows:
To enable interactive sorting → column name → right-click on text box properties→ interactive sorting → check on enable sorting → add sort by on column(detail rows)
Result:
2. Groups:
First group the column
Goto row groups →> details & right click → groups→ parent grp→ specify the column name
Then go to the column name on table
To enable interactive sorting → column name → right click on text box properties→ interactive sorting → check on enable sorting →select grp column like below
Result :
Freezing headers at top of each page
Headers and column names should be repeated for every page
Click on empty page → right click→ add page header → then you can simply drag your text box to there
Right click on Column groups→ advanced mode→ click on details in row groups and press F4 then it opens in properties pane at right below corner → set true at “repeat new page option “
“And fixed data → true “
Image
External: through URL or remote or local folder.
Embedded: Get the image from only the local system.
Database: Get the image from the database
Insert → image→ specify path add the name of the image with extension → shows an error
Right click on image properties→ add extension file: as prefix to the image path
Grouping
Goto row groups →> details & right click → groups→ parent grp→ specify the column name as shown in the below and add checkboxes
After grouping add the grouped column on the top of the col
and merge those cells by selecting the cells and remove the grouped column
Delete the 1st row then add the another row on below the grouped column goto the option insert row→ select “outside group “ - above
Result
29/05
Conditional Formatting
We can use if and switch statements to perform conditional expressions
Create normal table report
Click on left menu bar at table → press F4 → cursor goto properties → background color → goto expressions → fields
You can change the font color also based on conditions that follow the same procedure as above
Use an expression to generate header and footer
Take a text box → expression → to add that parameter
2/06
Create date parameters in an SSRS report to filter a table of Customers
You can pass the date as a parameter and after that
Right click on your parameter → goto grp properties → change the data type of column to date/time → ok
It shows a small calendar when you passing a parameter to it
You can specify dates as default also
Right click on your parameter → goto grp properties →default values → specify values → ok
(or)
You can use the expression to set your own date functions
Cascaded:
To show values as a dropdown list
Right click on parameters → grp properties → available values → get values from query → select the respective data set and field and values
Matrix table:
01/06
Look up
Use Lookup to retrieve the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship.
03/06
Filters in ssrs
Take Dataset → Add a table -- > right click on top of cell →tablix Properties-->add expression→ operators→ value
How to change Color of Alternative Row in SSRS Report
Create a normal table report
Add another column in that pass row number expression as the following
Right-click on the box(empty row in the table) → go to expression →
Right click → expression →
Row number function
Returns a running count of the number of rows for the specified scope.
Syntax:
RowNumber(scope)
row number(nothing)
=IIF(rownumber(nothing) MOD 2, “yellow”,”gray”)
Parameters
scope
(String) The name of a dataset, data region, or group, or null (Nothing in Visual Basic), that specifies the context in which to evaluate the number of rows. Nothing specifies the outermost context, usually the report dataset.
RowNumber returns a running value of the count of rows within the specified scope, just as RunningValue returns the running value of an aggregate function. When you specify a scope, you specify when to reset the row count to 1.
a scope cannot be an expression. the scope must be a containing scope. Typical scopes, from the outermost to the innermost containment, are report dataset, data region, row groups, or column groups.
To increment values across columns, specify a scope that is the name of a column group. To increment numbers down rows, specify a scope that is the name of a row group.
The following is an expression that you can use for the BackgroundColor property of a Tablix data region
=IIF(RowNumber("GroupbyCategory") Mod 2, "White", "PaleGreen")
Running value
Returns a running aggregate of all non-null numeric values specified by the expression, evaluated for the given scope.
RunningValue(expression, function, scope)
Parameters
expression
The expression on which to perform the aggregation, for example, [Quantity].
function
(Enum) The name of the aggregate function to apply to the expression, for example, Sum. This function cannot be RunningValue, RowNumber, or Aggregate.
scope
(String) A string constant that is the name of a dataset, data region, or group, or null (Nothing in Visual Basic), that specifies the context in which to evaluate the aggregation. Nothing specifies the outermost context, usually the report dataset.
*)
The value for RunningValue resets to 0 for each new instance of the scope.
If a group is specified, the running value is reset when the group expression changes. If a data region is specified, the running value is reset for each new instance of the data region. If a dataset is specified, the running value is not reset throughout the entire dataset.
RunningValue cannot be used in a filter or sort expression.
=RunningValue(Fields!Cost.Value, Sum, Nothing)
Headers visible while scrolling
Right click on column groups → advanced mode → click at static in row grps → (table col names highlighted) → press f4 → set repeat on new page → true →
Filters at table level :
06/05
Dashboard
Score card
Charts
you can drag report dataset fields for numeric and non-numeric data to the Chart Data pane of the chart.
When you click the chart on the design surface, the Chart Data pane appears, with three areas-Category Groups, Series Groups, and Values.
If the report has a shared or embedded dataset, the fields in the dataset appear in the Report Data pane.
Drag fields from the dataset into the appropriate area of the Chart Data pane. By default, when a field is added to one of the areas of the chart
, Reporting Services calculates an aggregate for the field. You can also use series grouping to dynamically generate series.
Bar Charts
Bar charts are horizontal charts that represent or compare categorical data. They can also be used to represent negative values.
Bar charts are used
To identify the number of unique visitors that are visiting a landing page based on country.
To identify which products are more popular with customers.
Clustered column charts:
Clustered column charts are quite similar to bar charts,
the only difference is that clustered column charts group the data from the same category into a cluster.
The data within clusters can be compared with each other as well as with data from other clusters.
Gauge Charts
Gauge charts are used to show the progress towards a particular goal i.e. it helps in showing how much of a particular goal has been completed.
Gauge charts are used to represent KPIs such as the yearly sales goal of a company.
The minimum and maximum values are predetermined and the line in the middle determines how far you have progressed towards your goal.
Gauge charts are visually appealing but they can also be space-demanding as well. If you feel that less is more, then you can consider KPIs over gauge charts.
A radial gauge chart has a circular arc and shows a single value that measures progress toward a goal or a Key Performance Indicator (KPI).
The line (or needle) represents the goal or target value. The shading represents the progress toward that goal.
The value inside the arc represents the progress value.
Linked Reports
it is derived from an existing report in the report manager, and also retains the report definition.
It contains the original Report only If it is deleted/updated it will also get effected
08/06
Security
Site-level → Application level: user can see all the reports in a single application
folder/project level: user can see only specific report /folder
Report level: user can see only his esports can’t see other reports
Ssrs works for windows authentication users(active directory users) only
If you want to give permission to the other user, he might be a windows authentication user
Ssrs has only 2 roles system administrator (can delete/add/modify ) and system user (only can see report )
Site-level:
SSRS reporting services→ settings → site settings → security → add group/user → gives the name and select role of the user → ok
A guest user cant delete it only can view it
A person who is at the application level can see at the project level
Folder level :
Manage Folder → security → add group/user you will see the following options
Project → manage → security → customize security → check ok in the below type box →
You can see the add group/user →
customize security allows the user to inherit the security level from parent folder
Report level:
Project → report → manage →
Security → customize security → add group /user → permission as below → ok
To delete users :
If you want to remove the user permission at all level s then click on use security as same as parent level then it removes from all the places otherwise you need to remove at individual laces
Site-level → security → select user → delete → ok
Folder level → manage → Security → select user → delete → ok
Report level → same as above approach
Report parts in SSRS :
It helps to reuse the reports or report items to the multiple reports
Report Builder :
Used to create reports
Ssrs web portal → new → data source → new data source → properties → name , connection → ok
09/06
Change Report manager title
Site settings → general → change name → ok
Deploy Reports using BIDS
BIDS : business intelligence development studio
Right click on the project → goto properties → set the target url → ok →
Right click on report → deploy → ok
Deploy Reports using Reports Manager
Report Manager → upload → choose file →
Create Shared Data Source in Report Manager
ReportManager → new → data source → fill the properties like name and credentials etc →create → ok
Cached Reports
It is a saved copy of an already processed report
If your report is generated with massive data or it is designed from a large dataset, then it’s time-consuming to call the same report again and again.
2 ways to create cache reports
Processing options
Cache refresh options
Report → manage → cached refresh options →
Auto-Refresh the Report Data
Report → properties → other → auto refresh → set in the value (seconds)
Once the table has changed then it will directly reflected into the report
10/06
Report Parts
Report Snapshot
How to hide empty rows
Click on row menu → visibility → select show or hide based on expression → go to fx →
=IIF(Isnothing(Fields!sal.Value),true,false)
=IIF((Isnothing(Fields!sal.Value) and Isnothing(Fields!place.Value)),true,false)
Page Breaks
Row groups → group properties → page break →
11/06
Go to url action
It will be done with the help of actions in ssrs . help us to go the specific internet page or specific report in the same report server
Textbox properties → action → goto url → fx →
Enter ok
After this in the preview session there will be a hand symbol . if you click it . it will navigate to respected page
Document map
It generates tree like structure or menu like structure
It will be like an index in our report
It gives navigation links in the preview report. So that user can use those links to navigate b/w multiple pages or between reports
Categories in the row details → goto the group properties → advanced → choose your column for document map
Do the same thing for sub categories
It will be showing like in the below page
Columns → advanced→ static in row → f4 → Properties → fixed data → true
By doing this the columns will be displayed while scrolling the report
Columns → advanced→ static in row → f4 → Properties → repeat on each page → true
The columns and header will be displayed for every page
Calculated fields in ssrs
Comments
Post a Comment