Hey guys! Welcome to this session on MSBI. So MSBI is basically a powerful suite of
tools which provide solutions for business intelligence. So MSBI
comprises of three tools SQL Server Integration Services, SQL Server
Analytical Services and SQL Server Reporting Services. So, in today's session
we'll learn how to deliver the right information to the right users using
SSRS. So let us have a quick glance at the agenda. We'll start off with learning
how to install and run SSRS. After that, we'll learn how to create our first SSRS
project and generate a report. Furthermore, we'll look into the SSRS
architecture. After that we will see the different types of reports which can be
created in SSRS. After that we'll learn the drill down and drill through
operations and finally we'll learn the parameter properties of reports. Okay guys
let us move on with the session. When it comes to SQL Server, when it
comes to SQL Server there will be already DBS in your company in your
organization there will be DBS. It's their job to install, provide access or
restrict access ok or deploy, manage anything that happens on SQL server,
its DBS job ok. When it comes to MSBI, Microsoft Business Intelligence be it
SSRS, SSIS or SSAS, developer also need to take care of these things. DBA
may do or may not do most of the SQL Server DBS will not have much knowledge
on MSBI will be on SSRS discuss here only. So developer have to understand
how things need to be set up for SSRS. How to provide access, restrict access
troubleshoot, manage something goes wrong okay. So admin activities are part of
developer roles when it comes to image gear services okay, when it comes to MSBI
services. So pay attention for everything that a landing part of it. So don't think
that okay everything will be set up and given to him so he can start development. So
here, we will download first SQL SSRS reporting services. Download SSRS and install it. SSRS is a SQL server reporting
services, SQL server reporting services, download it and install it. Once
after you install it, the first thing that you should do is run configuration
manager. This step you need not to do if it was to 2016
ok, there was an option to auto configure. Now here you don't have, so again we are
going back like in version 2012, upto 2012, they have to manually configure
it but in 2016, it was part of the installation, again now they have
taken it out, so you have to configure it. How do you configure SSRS after
installation, you have to run configuration manager. SSRS has
its own configuration manager ok, all you had to do is go to start menu, search for SQL Server reporting
configuration manager say this one, it is right away there itself.
Reporting server configuration manager ok start this. You have to be admin on
the system, you are connecting ok. So you have to be administrator of that system.
So this is SSRS configuration manager okay. So here we'll do the basic settings
for this service even before you come here, if you want to verify whether your
installation is successful or not ok if you want to verify your installation is
successful or not, go to run -> services and check for
SSRS service. So start run prompt, services.mse, SQL server reporting
services. So it is installed successfully yeah. Whatever we installed, they're
installed successfully. Sometimes even if it installed successfully, it may not be
in running condition okay, in running mode. So you can start
the service and make sure that it is in running mode. After that, the configuration manager, now you can see that services has started, it
is running. So these three things are very important web service URL, database
and web portal URL okay these three things are very important, rest you don't
need to worry right now, rest of the things. So this three are very important
in this configuration manager. We will start with database, so you have to setup a
database for this SQL server SSRS, okay. Everything that you develop,
the reports that you design, the queries you will write their, the color formatting
anything that you do, right as part of report development that entire definition
will has to be stored in database, so that report definition has to be
stored somewhere whether it's not only a report definition even administration
administrate administrative information like who can access that report okay who
can download that report, what subscriptions will have on that report
such information can also be for such information also you need the
database ok back-end database that has to be SQL server only SQL server
database only. So before we do that, open the sequence or management studio, people who joined newly who don't know
anything about a SQL server or SSMS, I'll tell you as part of this course as
we go on, I will cover the basics also what is what, so you don't have to worry.
So this is SQL Server management studio. This is used for accessing or managing
your database. This is used by both DBS and developers who work on a SQL server. You everything is used by both. connect to localhost. So, we have two databases here and some
system databases, this two are project related databases. So now from here
to SSRS configuration manager and you'll assign a database for this
reporting services. Create a new because our database this next so this would be
database name right. So they is the database ready for us. I
mean we cannot use this database directly okay this is application
controlled database. So only SSRS can talk to this database, write
anything to database or retrieve anything from this database okay this is
for this application SSRS. So you can come back here to SSMS and you can
see that two new databases will be created, reporting server where actual
report definitions access controls and everything will be stored, this Temp DB is for runtime access, when somebody is browsing the report to hold that
temporary data, cached data this database will be used. So there is a lot of tables predefined
tables to take care of users, subscriptions, the way you recruit
definition itself. So we come to the database later okay when we are,
when we are covering advanced topics of SSRS, we will touch this database.
Until then, you just application database. Now that your database is set
up, we will go ahead and configure URLs because we want to give web portal URL
you have to apply web service URL very shortly I will tell you what are this
URL. I am here to web service URL and click on apply.
So what are the steps, we go to report configuration manager, assign database,
configure report manager URL and report server URL these two things, there are the only two things which will
be using for creating, deploying, developing, managing everything that
deals with SSRS. So now I have my set a website you see
URL so they sitting in the bath third thing that I should do is setup manager
URL to is called recruit manager okay so all you have to do is click apply here
so this apply will not be activated unless you activate web service URL so
if service URL is called report server and this is called report management so
unless you activate report server you cannot activate report manager but I
think now these are all activated there is database there is service URL and
this portal URL awesome that's it we will the URLs you I want to stop here some of you might
have a question relating things here yeah is because these are distributed
things so you'll have some doubt so somebody is asking me at the very first
screen you entered the server name is that SQL server name yes that is the
scales are on them if so when I try to enter server instance name it says
report server so you have to make sure that you are administrator on the system
even if you don't know the name you have to give localhost
ok you can give localhost so when you start report server manager right so
here if you don't know the name you can say simply local first and then it ok
you don't know the name of the server you can say localhost provided you
haven't given a different name while installing for the instance ok in your
services you have to make sure that your integration services so recording
services installed on the same instance I mean the default instance there is no
name attached to this that's it okay guys so you do I need a skill so 2017 and SSRS 2017
I have 2016 you can continue on 2016 also this is always 2017 there are no
much improvement so you can continue 2016 it's good no problem but if you
want to try or 2017 years you can go out and try and actually Isis iris is not part of
2017 installable I don't know what is the strategy they have yes 2017 we work
on windows 7 but I am really surprised why they haven't bundled as a service
along with SQL Server 2017 installation additionally see with MS BAE started in
2005 so when ye means 2017 up to 12 years
they say service was part of msbi installation this one you see reporting
services is L Server reporting services this is a link put bound road and then stalled so we
will create a shade dial okay we will say - a drive I will upload these text
files and everything will get all these links and everything that's it where
they are kidding okay so find anywhere else we continue so this is report
server URL okay report sir report service report manager URL is also same
but just it will not have okay this reports is your report manager this is
also called as portal is called as web service well so this one we will visit
later okay report server is where we deploy reports and this is where
end-user will do our reports whatever we design deploy what would be design and
deploy this is where so right now this is blank
nothing is hosted here okay nothing is deployed so nothing is posted so this is
blank page but has you create reports and as you design reports and deploy
them they will appear here they will appear here and the whoever has link and
necessary access they can browse reports browse your reports that was the role of
your configuration manager so he I will come to the questions little later
so this configurations of this is what you are doing configuration manager
before we begin with development setup now I will talk about development so for
this you need SSD T so this would be same for all the three
services yes RS and aegis you need ssgt download and it all
hisses TT sequence over data tools forward by visual studio say we can say
these words today 2017 assess TT every time they're changing this name up to
2012 this was called bids business intelligence development studio I think
most appropriate name business intelligence development studio later
from 2002 the long words they followed they started calling it data tools
greater tools 2012 2014 2016 in these three versions it was called as data
tools from down 2017 onwards they are not abbreviating this they just calling
it Visual Studio we'll start Visual Studio de 17 today is
much bigger application okay this is not only used here we are using it only for
msbi services and the MS bear development but visual story is much
bigger than that you can develop many things multiple
things so this results today is called ID okay this is not like your SMS okay
so this SMS is a clearing tool in order to work with database we have
to have we have to we need this SMS this is SSMS is called client so let me
clearly explain what is a message and what visuals with a data tool Swiss okay
clearly I will put this out for you so that we don't have confusion see you have database then to connect to
this database or to do anything on this database you need it's a service
this thing is called climb everything is climbing
you have msbi services okay all the three services SSIS this is
our s SS es and assistance he can actually call database also the service
now in order to work with MSBA services we need SS DT is equals our data tools
but this is called IDE integrated development environment
now this is a mess you will find in both development environment
and production environment this you will have only in development environment
you will not have it in production environment okay you use it only for
developing and deploying you will use this only to develop and deploy that's
all you will not use this to manage it okay
whereas SSMS is used for managing we don't develop anything here yeah we may
create some stored procedures and all that if you call it has development fine
but we don't develop a solutions here like we do in essence duty this is
mostly used for managing see why I call this as client is because you can
said this SSMS without database services also means what does
that means you have a server in your organization where database is installed
database service is installed and you have your laptop as a user you have just
your laptop and from your laptop you want to connect to the database all you
have to do is install is immense that's all so with lightweight client the
install a schism else this one then you can connect to your office server or
wherever the server is at the client location or somewhere on the cloud so
you can connect do this servers ok so you don't have to actually have database
installed database services installed on your laptop on unique on your client ok
so that's what's called client okay so that's advantage we have so but as it is
not like that this image gauge services are needed
first you have to have a miss based services installed on developments
always a river and then we put is this DT sequel server data tools which is
this when you're installing for the first
time you don't have to worry about all of this is because everything is in one
system for you other services is on one same system SMS
is on the same system they must be a services installer also
on the same system this is Duty works on the switches when you are working in a
team company if this information is I will create our first SSRS report create
our first a sorry sir right it is ready yes didi is ready so
click on file new project file new project recording services
regarding services project so you have to give a location where this solution
will be stored this is a solution this is a project name so this project will
be attached to a solution okay you can have a different name for the solution
or you can leave it the same training project create directory for solution
yes so a separate folder will be created for solution with in which your project
will be created we will discuss the solution project thing later okay once
we create the project we will understand later but the important thing that you
have to notice here is that ability to add to source control now what is source
control so this visual studio along with it has another plugin called team
foundation server of the TFS which is used for version controlling your food
okay if then people are working right so it
becomes messy who is working on what so it should have an option to check in and
check out if somebody checks out other person should not be able to update do
any changes to that file right so so that capabilities we have in Visual
Studio and that segment is called as team foundation server if you want to
add your project to that Team Foundation server we have to check this if not
doing this you you you this is the first session we are taking
for the batch okay I am taking I'm starting the first
fresh batch of start with SS iris it has so enhancing create dis projects
if this training project is a project so this project will be placed in the
solution solution is just a logical collection of projects you can have one
or more projects ideally create one project some surfacing are you sir we
can get other project also but I delete the one solution mean which will have a
project so the project is important thing the main thing is a project
everything will be done at project level solution is just like a folder which
holds all your project together come on your draw DFS also deals mainly
with solution right now in this project add a report add new item report report
one time Lula's your report designer okay so this
is a report designer I just closed this this is your report you just started
this report a blank report if you double-click on this report report
designer will open up this is report designer okay report designer has two
main things the designer view and the preview designer view is where you
develop or you design your report and you check the preview how it is going to
look on browser so you can see the TV if there is something developed of him all
right so this designer has two views designer view and freak you
you you you you now your
SSRS has two other panels after you create is a service project
and add a report you will have three segments the first is before designer
which will have designer view and preview okay then you'll have the put
data okay everything that relates to the report okay every definition the trio's
to the report will be stored here in report data then you have toolbox this
is where you have all the chart controls you click chart controls from toolbox
put them on report designer you pick data from report data and connect both
and then it is an and report data is where the deal with SQL and parameter scale you so his visit his report data record data
dependent we have just seen the record designer and this record data we have to
add a data source first we have to add a data source first before we add a data
source okay so we have to make a different we have to prepare data what
we want to show on this report we have to prepare the data so we'll go back to
the database one of our reports one of our databases here is a simple call log
they are they how many calls are made okay how many calls are list all your
keywords has to be in-depth history in your first glance and follow this course
tenets okay so this one the sample is very happy this will be wise give me now retain this query as this okay
before you take this version you had to create a data source anything she simply
a caption two variables whether it is rectal school server Teradata whatever
we create data source then we created a state connect the data source data set is your
SP is where your experience you could your SQL query unable to
connect so that the datasource fields to fields called it and federal
policy okay your data set is ready now this is what we are doing report data
get data from our report get data for our report repair SQL statement and get data for our report so now your
data set is ready along with the data source we will visit the third panel
which is your toolbox a box again this is report that was
reported on gave us from here I take chart control blind chart okay a lot getting going
deep into the details of this chart control because as of now I want to show you the
intent process they are looking and deploying the report till end users and
access this device doodle calls this is your first report we are we have a lot
of formatting to do on this report we can see that there was a drop assuming
that if these are continuous tapes and this is like today's date or s today's
date we can say that is it dropped in the amount of calls of them we have this
report we can format it but let's just go with this
the only formatting I'll do it on one date or time did this one right so these
are the calls it lasts six days now you have your report now after this so we
have designed our first report some basic changes I have done the formatting
wise we will discuss what are those formatting changes I have done later
when we detail we will discuss when we did when we discussed this chart control
in detail okay so now we have this charge control so the second tip so we
have created our first report okay what are the steps that are involved one we a
data source which is simply linked database server we need the IP address
of the database server login ID and password and database name with those
four details we can country eight a data source next create data set this is where you'll write SQL statement
to bring the data only way this writing is through statement so it's still not
having knowledge and SQL is a prerequisite for a service for any other
tool in msbi is is having knowledge and a sphere is a field so you'll write as
girl wait okay and step three and add chat control
and after adding charge control you know just like once again I will do these
steps okay see after creating I will remove this chart from here okay so this
was the empty report I had okay so first report data create data source then
create a dataset with the query that we compost then go to toolbox hook chart
control you have many things here okay actually we should start from table and
matrix but we are creating the first report
now let's go with the line report so bring chart to the uncharged control to
this panel select line chart third one let's take the first one looks
interesting this Y values white category groups yeah will not answer these
questions now when we discuss in detail about this chart control I'll answer
every person related to this section so we're good here simply protocols I ran
this holiday can scatter this will do a little formatting on this x-axis we show
it in proper date format and that this one by access flavors for
better readability enabled is thousands operator will increase this lamb length with thickness of that line so they got
it right this is our first chat our report ok so what is chat collection
octopus so sorry material good collection of chats so here this is a
chart this is only chapter title on a ticket volume and whatever they recalls
volume they completed the first part this is your part one partl is developing rapport then sir
part one is developing rapport then we'll have part two where we were
to deploy and test the report you right so we created this report yeah we
designed this report so now we have to do a second part of the process which
will open this report so we go to project for that deployment
what we need is report server URL which is this
okay we need report server URL for the project properties right click on this
project open properties and you can see here targets our URL this is already
configured if not you can paste that URL okay so that I considered if not you
think this and lick apply this is where our report we'll rename this now right click on the
project again and now this time click on decline deploying report deployment
started deploy succeeded one succeeded zero failures to the script so
deployment is successful so we go back to report server refresh this sinem
training project that is our project ok this now inside this project you will
find the report we created daily calls for me click on this triple drink you
could say that report on the browser so whoever has this link they can access
our report abut provided we do them security we add them we add the user
provide report so what we see now is report server one browser ok so this is your report on
browser ok so this is a report on browser so we can see there the calls
volume steadily increased but then and it straightaway decreased fine
so is asking whether the user need to be on my server no there but they have to
be on your domain on your network so we take this this is our report server ok
so this is a diplomatic symbol so we always have to do this provide the
target server URL target server URL and again right-click defer create other
your report will be deployed you can deploy the entire project again to the
deployment project level so that all the reports inside that project will be
applied together we can individually deploy a default deploy a particular
report this is your report on the browser so
there are two things right one was report server and other is report
manager now what have what he will have in this report manager one thing is will
not deploy reports here but then what we will have here and what what is this
used for so we'll open that you are manager looks like okay so this is the
portal looks like so whatever you deploy put-put server whatever you require to
report server can be viewed in report manager also can be viewed means
casually defer to report manager I mean actually they don't get deployed here
connected to it so what is advantage report here also but what is the
advantage of this report manager so with this report manager you can manage your
foot within manager if one writing something him access adding a user
browser setting of Cascia for performance and all we will come to this
later there are so settings there are many things that you can do you can
download a copy of this you can do direct deployment here instead of if you
don't have data tools you can if you have just the report file ok RDL we can
straight away upload it the report manager delete it all together primarily
administrative activities ok he can do administrative activities using this
report manager how can we discuss about this report manager in detail advanced
SQL services sessions but mostly with focusing on yourself let us understand
everything in one so when he installed SSRS you get
two things you get two things one is server and other is manager one is
server and other is manager this is server will deploy your report
this is manager from very manageable report I have shown you both so these
two these two are connected to internal data business there are two actually but
there are actually two databases the other one is a temp 1 Delta 2 and forth
communication between us as far as first hour and management using data tools
using data tools you will design and deploy reports to server ok using visual
studio data tools you will design and deploy the server ok and using browser
you can communicate with manager for hitman activities administration related
activities Pepa that you will coordinate browser you cannot connect to manager
using we data tools ok you can only deploy it will design and deploy to the
server using data tools for in order to connect to manager to manage reports you
will do that using browser same browser is the client site Chrome Mozilla Safari
whatever so your client browser will connect yeah right and then you know
don't search in this using request response model it will your client
browser will send a request for a specific report that report will be
returned to the browser in the form of response request and response will be
handled by server not the manager okay both request and response with reports
our database is for internally storing the data okay this is the architecture
of your SSS so this one is part you will have it
only in development phase you have it only in development is touched all
remains him in production also in production you will not have this one
but if a service service you need it both in development box and also the
production boxes are a service okay fine did you understood this one this
architecture and out slip do they mean anything okay that's now what we'll do is as part
of this training we will use adventure works database we will use adventure
works database because that is a common database even you can find it I can find
it everyone can find it from said by
Microsoft only could so actually yeah our creating first report is over yes it
says fully created first report we deployed it and we finally understood
the architecture also all the components related to the service cuts all these
are the only things we have witnesses iris server manager it is of its own
tools we have at VirtualBox GW 2017 backup or you take a lot of time fine we will
use this database in the next session and in unlearn we'll continue with our
own database okay so did you I have another database online of this so I
should not use this sphere and refer to database so on this data what we will do now let
it download continue what we will do will create platypus
these are called flight reports both your matrix and tablets tablets and
matrix reports are called flight reports okay so we have the special chart
contours for them this table and matrix SSRS mostly most of the service reports
no develop we'll be using these two controls on
because Osiris is based here this tablets and matrix reports will create
one report and will every discuss like what is a difference water discharge
reports and what are this flat reports over the different and everything so
we'll create one table report now that another report this report again it has
its own pretty designer report data and tool box simply
connecting to the same database okay CRM reporting data this now with multiple
reports under the project have to be pointed to the same database then we can
create a shade datasource interrupt report specific data source here the
data sources report specific you can see that instead of creating a data so
specific to a report let's create a one at the project level add data source all right so see the connection manager
the data source is created at a project level now we will go into a new report
so this one will be called as agent daily calls okay so this one in this
report now that we have a data source created at the project level we can
refer to that directly in our report instead of embedding connection in it we
will use a state shared data source reference which is this okay the one
created project level okay so we have a data source and we clear dataset in this
data set we have two part where it as was first if we don't know how to write SQL query
you are not very strong I don't want to write SQL query you can go to query
designer you can yes very dizzy okay so when you open purity is our
inner it will list down all the tables that are there in the state of this road
purchase if any it said he can choose from that list this table T not all the
columns was the caller ID business and cloud
status okay this for fields we want those for
field see distilleries automatic in compost for you okay so there's a data
set the simple data safe now google tool box and bring tablets is
called tablets this is tablets which has a header row and it again row now
right-click on this tablets properties and data set connect our data set one
data set that being created here so now you can choose these fields on this
tablets the first way would be caller ID then all the time and the destination I
need one more column right click on this insert column write all status a lot of
data in this report so this is your platypus will do some formatting on this
to hood so this one he can collectively select everything and switch to board
18:30 entire row and switch record all time or Mac Xbox properties 4/9 the same
formatting we did earlier on the language at all stages all status we
will do central in this region title therefore we'll bring the HTML
properties you can go for a background color now what you seeing here this kind
of report is called paginated report this is called paginated report for the
very simple reason that it runs through pages this is going through multiple
pages okay so this is called paginated report or you can call also call it as
printable report them these reports are printable report or paginated reports
now the reports that you design using matrix and tablets are called flat
reports or mostly called as these days these these tuples are called us or some
people are also referring them as and reports okay
see these are what reports are okay people are referring these as reports
now analytics that you might be hearing a lot about analytics analytics are
about this where you mainly deal with chart controls mainly deal with charts
like and many many standard he remember there are many things
medical reports the primary purpose of this report see here also here also we
can put line chart sorry a bar chart and line chart and can print that report
this report you can print even this report also ok this is not a paginated
report line charts bar charts they will not run through pages because that's not
how they are designed but you can print these reports also but these are not
analytical reports why because here on this
reports you can do three rounds and you can do so many things okay so
there are lot of possibilities so that's where the disconnect okay that's where
we tipping up happens okay so you have this
SS iris which is very good at creating this kind of flat reports bayonetted
reports printable reports are whatever so using tablets and matrix now are
usually a lot of lot many things that we can do on that report okay so which are
very specific to this a service now when you go to analytics like we have power
bi which deals with analyzing data to identify patterns actually all this
drill downs and what if anywhere you can do in is a service horoscope but mainly
we use power bi for analytics so that you can do you can identify patterns
whether insights and all that is possible using analytics now analytical
tools analytics tools like tab you click your power bi they should not be used
for creating tablets and matrix paginated because they are not good okay
if the service is good here in this domain and it can using it as it is you
can actually design this also you can do anything everything possible because the
service is highly customizable okay but the development time will be more so we
switch to analytical tools required here so both have their own significance in
their area okay so learning both is important if it be
locking that country why to learn as a service we can learn power or we can
learn this now this at or this reporting tool has gives significance showing you
this can do see like you want to send a list of all the open items to your
warehouse type what all orders to be dispatched so you need what you need
customer number contact address contact number right all that need to be printed
on a report on a paper and given hand it out to your there was this so that he
can dispatch them yes they need to be printed report okay your medical
examination report that has to be printed like the proper
parameters or indicators whatever right so those reports right your payroll
report attendance report any scientific reports with all these
printable reports mostly will be designed is he this reporting tool
standard reporting to establish matrix and then you do any takes more of
identifying patterns trends right optimization cost reduction all that
spend analysis so me is a little too on this one what will I do is you see this
is a very basic report I have done some formatting you can explore this
formatting okay so there is no limit here it's up to you version creativity
the color you choose the font style you choose the alignment you do okay this
all depends upon either you have a template from the customer you go for
the template or it's up to the developer now that this report is really good so
what we will do is we will group this report okay Brooke this report based on
your caller ID will group this report based on caller ID so what will I do
right click on this caller ID add group didn't group group by caller ID add
group header okay they are all ridings is disturbed this is a destination so we
have some call radius without names okay so the data is not consistent
okay the underlying data is not consistent query this table so it has to
be contact number but in some cases you might have names also
you see like this we will do one thing we ignore from caller ID so there is bad
data in this table we have to remove that back data so one thing we can do is
we can take only the caller ID is into starting alphabets okay so there's a
query in you can try it or else it's not good it better - okay you have to write
the display Fillion so we will go get this data we know that it is not in good
shape but anyways so now here you have this already data and everything right
so based on caller ID will we create a group so how will I create a group so
that three ways in which I think ethically right click here insert add
group are here add group are simply the best way is to come creat detail details
flow in our groups right come here of India now that you are added this as a
group header you can remove this collar and remove the actual column right-click
and delete so these are call ready wise now that we
have this crew we can design drill down and drill up so that what I have to do
open this truth properties his properties display can be profit father
ID and by default - so you have something elected so you can go to a
specific a specific user and you can see just that particular user call records
ok so you can see just that particular user call reports this is grouping on
this this is they can further group this report by Carlton Carlton and to me we got headed just based on all time we remove this axe important problem for
this also I'll I can tell you what I am doing there
you might feel it you can't reason for you they include many things the funds
will show you what will happen not to be report then we will come and see what
happen so now they can go to a particular user and for a given date of
there is only one hull placed here here for two days so what I have designed
here is now called as dildo not drill - but before that what we have done and we
have created groups okay so this is this feature is specific to your tablet's
report your tablet support your flat reports now your flat report they are
not only paginated reports you know in double reports this flat reports also
gives you the flexibility of creating groups groups and drill down okay so it's doing
both groups and drill down so their tablets you then can do pagination and
then reporting them sorting sorting and many things are possible okay this is
your flat report flat report now we will also discuss about matrix report but
later but the idea here is that these reports are created for it mainly
showing information these are called informated reports okay for displaying
information and in order to be analyzed further just to carry the business or so
for flow report supervisor reports key mute records like how many calls are
open how many calls need to be closed how many tickets are pending how many
tickets are in progress okay so such kind of information when
you are looking at a system Butler system you can pull using this simple
lightweight tractor pulse should not there won't be much of formatting on
this reports okay main purpose is to throw the information that so that's
what we use this platypus groups and drill downs in next session we will
entirely focus on this flight reports and also how to parameterize those
reports so that we can select a particular data and see them and also I
will clearly explain you what we have done here in this drill down and drill
up how to set up cowling visibility using the groups okay so today I wanted
to demonstrate how this reporting works and all and whatever I don't have proper
data set if this data is not clean this data is junk actually mostly junk okay
so the data is not clean we actually have to use ETL to clean this data like
SS is true but anyways this database is in downloaded once it is done I will
attach it and we will create some good reports he will have proper data
I think I'll control possession here and we'll give you some assignments but
before that let me take some questions so she was asking is it is taking
timestamp to group them can be extra timestamp and take on dated this is
image every country actually he noticed earlier when we did the line report we
actually did that see our query excluded time the time we casted this timestamp
as did so you only took get the same thing we can do here also be so design
open data set where you have a single query and this is a time column this
simply you can do this from ask you which is the best way okay the plan part
will be exploded and the Venus in still you're getting here times it is
different time okay this is one stupid thing as a service will do so this time
we can eliminate by doing the formatting of this text box so this is possible so
I will show you now how to see after this download completes you will get a
backup file okay dot dat so what do you have to do that backup file with our
backup file is go to this path you know C Drive Program Files SQL
Server she'll have a folder specific to service SSAS this is where SSAS this is
for us carry 13 and back up here you have to place your backup file once the
download is done here you can put your backup file so I'll ping this path this
path may be little different in your systems okay if you are not very sure
about this path you can do this so you right click on data this is how you have
to restore a backup file if you come to store database select device you CAD here this will give you the path
this is where if your backup file is already here it is convenient and the
second thing is you will not have the risk of permissions sometimes what
happens you will not have proper permissions to restore at this file
which is in another disk another Drive this so to avoid to any access related
issues that may arise ok we will do what we will simply copy that path where it
is expecting a backup file and we will go there and paste it so you do this
then any unwanted anyone return any required permissions or anything else
will be take you to it so that is why I do that actually you can put it anywhere
you want if that is a backup file if it is a data file MBS ok once you restore
right so that will come up as like database like this if one after you
restore that backup file will turn into data file that we find in data folder
because end of the day everything is fight ok whether you see those tables
there in rows and columns form it's also file in the back this is MDF ID if you
have this file itself MDF file itself then you can attach it you can attach an
MDF file but generally not find MDF I for internet because they're huge
see here this serum recording file it is 32 MB data actually data size is that
ideally but if you back it up it is just whoops baby it is extremely possible you okay that backup is along with the log
file see the log file 800 MB okay so the backup will have more data and log also
okay so log file is taking that amount of space so it's a total 368 60 MB but
if you back it up it's much smaller okay which means more and if you have a
file here what to do is you right click restore database slick divides point to
that backup file did okay he can give a dependent because we
already have scale in the fertile data business you can say actually this was where
those a backup taken three years back so that will restore and create database
here one Singh have the database he can use it whatever they you want to bring
using that's looma path files path my name is if it when you're doing the
fresh restore okay you'll not have a database like this it will be very
straightforward for you to restore this okay after you put this database in
place okay because any table and P it one simple line chart if you know to be
based on dates it can products or anything okay compose
a simple SQL query a simple SQL query on the database and create a line or a bar
chart create a line and a bar chart deploy it and show it to the next class
show it to you so these are other things that you have to do before you create
your report first you have to download that set it up configure it these three
steps you have to build configuring it then you create their to quote using not
any data but using adventureworks data so link I already gave download attach
it using this data create a basically book now I will not tell you which table
to use what data to put okay you decide on that but we come up with a report
very simple report line shuttle bar chart will deploy so this was the basic
clatter party created with tablet a little more so the database status is not Brandon so I have
consolidated adventureworks data to this table so the
flag abode for the flag product category bird subcategory standard cost yeah I'll
go with this through first this these values I will sum them so I'll
apply applications on these problems so why we have to apply aggregation - or
team right so this will be so these are there I think it in values okay so I
will give this aggregate values and subjected values to this report that I
am going to make okay product sales we need a data source so here actually the
table that we are using is in a different database
okay so right as of now you have only connection to CRM reporting so we'll
create another data source right so this is a project level data now any report
in this project can access it Jagr sauce
so but again we have to create a connected data source within the report
okay pointing to the project giggles okay so this is just a reference
in to create that referencing then we can
go on which actually brings the data where you have to write taking the same problem so what is
happening in this 2017 days of birth so we have this query it's an agitated
query okay see pay attention here I am not giving a
direct query this is an aggregated query okay a direct query would be something
like this it is transactional okay so this is a query without any obligation
so this query will written this query will return how many records
the case in segregated query has written only 17 records 17 records and if you go
with the non-accredited very you will have around sticker
request that you will have not sticky records so I am constantly giving only
abrogated equally if you want full details very you can but alder you buy a
- okay so here I have now bar these fields using these fields al period
table report for a daily occurrence of that is
first cannot add bulk columns at once er to do
here to add it one by one first then him on tax 210 denier for it sucked entity
and it cost and so on so I will select this entire row for this I can do a lot
of formatting on this but his mind Isis I can change the background color
setting borders keep the font color font style font size so there are many
possibilities multiple possibilities so this is a basic you know standard
report right so you know for a category but it can be given values for a
carriers in sub category subcategory wise totals right because the data is
already aggregated data is already aggregated you we can do this since all that amount columns right so
we can do this basic formatting on these columns we can turn this columns into so
right click on this see every cell every cell in this tab links is a text box
okay text box has its own properties whether it be background color border
fun okay it has its own formatting options so what I will do select
currency bomb okay so I will select currency format and use thousands of
related you cannot do this test box properties
eight months okay you are to do one by one you cannot select two text boxes and
applied properties at once that is not possible
but again select all text boxes and applied through formatting options life
okay if you are not able to find his options here you have to press f4 you
have to press f4 f4 will bring extended properties external properties so
therefore will bring those extended properties if I click the first scene
here so we can do much more formatting okay so now we change every go quickly we'll do this and so you can see the preview now the
amounts are formatted it looks much better right the reader readability has
increased now okay so now on this I will apply grouping on this I will apply
grouping based on product category see how grouping is done okay so say out of
being Eastern so we'll keep this report like this this
is a basic report that we have designed we have only applied formatting on this
report okay we'll give this one as is we'll make a repeat of this we'll make a
duplicate copy of this so how you can do how will you do should do that control C
control V that's control C control V will create a duplicate copy call it question two
now in this I'll create a group so the best way you
see you can create group in three days this I have discussed his installation
so you can create a group simply by right-clicking on this product category
the one you want to turn into a group further hat group
they don't move you can select the enter or you can
select the tablets and also you can add a group ok parent group
the third option and the pitman adoption is is click on this details details see
here you will have row groups column groups are not relevant here column
groups are not relevant for republics they they are applicable for mandates so
the column books are applicable for matrix so here in tavleks will only deal
with row groups I mean these row groups if you see here you already have a
default group this is called default group for katydids
what this means is this record so understand the nature of this tab Lix
how these tablets is working it has two rows one header row and one detail row
this detailed row is also called repeated okay
because this little rows repeats repeats he repeats for the number of times each
equal to number of Records that the data set returns see this data dataset
written 17 records viable so this with this row this row will be repeating 17
times to show all those records if there are any blanks nulls for yourselves it
will simply it will simply simply show blank
okay so this is your data room now this data row is grouped under row groups as
a default group this details we have certain options here like if you
open the properties of this detail group you can apply sorting on this we want to
sort by revenue problem disorder I didn't you so
here you can see this is a sending by default sorting is ascending so I will
change it to descend the highest sale value categories are shown first followed by
the least ones so you can see that the main categories bikes okay so these are
I will remove the sorting any new second one that sorted okay you can apply
filters and all but here sorting is one common future that we use for this
groups of the four groups at least now we'll create another group will
create a group over this default group so simply open this drop-down click on
add blue and click on parent group okay so based on one page on parental do you
want a header years so what will happen your data will get grouped like this okay data will get group like this remember while adding the group while
adding the group we have checked this box ad group header ad group header the
implication of that had group header is you will get this resumed row for every
group one reserved blank row for every group ok yes there is an advantage with
that I discuss that but before that see here we have group by product category
right but we also have a red category which is
there from beginning now that we already we have it as a group but don't need
this additional problem so we'll delete this column we delete this column so now this is a category and the
subcategories within that category now here you see this these are the
total values why they are total values remember because you are already
aggregated them in the Queen so these are total values now I want
hotels now these a total sets of Kerala
now I want totals at category level total said category level so what I can
do at total before simply open the default
group again the way you added a parent group I'll add totals before so when I
click this add totals before what happened was instantly his formulas with
inserted into that reserved room okay or the group header or the group hidden
so that at now you can see this total values accessories these are the total
values for bikes these are the total values for clothing desert fertilizers
so what we have done we have added we have added a group and also totals for that envy just to keep things connected are you
not on the things that I'm doing here in version 1 you we simply sorted before TRO group right this was the
version one the first report that you created
okay this default group I have shown you how you can sort your data so the font
group is the more complete data set rate how to sort this now in version two we
have added a group okay parent group with and earth with
header okay why did hidden because later we wanted to use that header for showing
Purple's so what would it be ah him came back to details and we clicked add
totals before now this version to refer they'll make another copy open this
coffee now that there is a group in place now that there is a group in place
I can apply it bill down on this I can apply drill down on this so what I will
do for a plane drill down okay so only you can apply Tilden only if there is a
group if there is no troop then you cannot build on and off very long so
what I will do he'll put the details open this crew earlier I have shown you
how to sort this default group right now I will show you how to set visibility
for this depart to show when not to show so the visibility of this default group
what I will do is I will say to bubble ass correct category okay this
correct getting the group that was local so with that what happens is you can anytime expand or collapse the
truth okay you can control how you want to
view your data okay if you have a requirement of collapsing everything
before you begin so what we can do in details through
properties here how I did that details group properties feasibility
under visibility display can be toggled by this
per category one and set this to fight so by default are you
we'll be given and you can open only the ones you're interested in
okay so this is until de miscibility why he am protected category
1 because product that everyone is a name of this parent group go to crew
here you can see this so that is the name of the text box okay that is the
name of the text box now that is perfect and emiru be selected if i click f4 it
will bring up the properties of this text box on so here you can see the name of the text box for
it category one okay per category one so that the
textbox name so any action happens on the textbox like click on link okay on
click event will be captured and traveling will be said for this group
the default group that is a can implement next level of
right so that is loop and it's drill down so this is your version 3 movie 3
we have added it on functionality we have added the Dylan functionality
now since everything is a group everything
is everything is collapsed okay so I have totals at category level I have
total is at category level if I open any group then I will have totals at
subcarrier level what if I want complete totals
so for that we have to understand this Oh you let's understand the scope of this group
okay here you see when I added total like in version two when I add totally
what actually happened every text box that every text works not only supports
formatting like earlier we have seen how we can format the text box
it not only supports formatting but also expression writing cavities okay expression writing devildis so if
you open this one text box expression you can see that it is doing summation
of this standard cost summation of the standard cost if you actually notice
did I say those summation based on category no it is simple sum formula
right it's simply summing up these values but how do you think it is able
to sum up it category level so how this division is happening because of the
scope okay you see here this L oh there is this a
low brace so this is defining the scope so this sum function which is the which
is part of this scope will do aggregation only at this groups
oke okay so now what I will do is I will right click on this observe carefully
here I will right click on this little room I will insert row below okay not
part of that I will right click here insert row outside group below
I will insert a row outside group below now it is same some formulas okay I'll
copy I will copy and I will paste them here you can do that you can copy and
paste same common same same some formula you see the expression is here some more
standard cost here you see again some non-standard first but now if you were
to clean you see these are grand totals okay because here for the sum function
the scope is entire data set because this row is not part of this group this
row is not part of this group or this group the default group or the group we
created okay so that's why here it will do grand total grand total and you open you have
subtotals this is the and total this is don't let category level this is that
purse of angular and 14 so that is something called s okay every group
there is scope so any formula you do but they will take that group scope okay and
the calculation will be applied only to that portion of data so that subset of
data finally we'll go back to version 1 and
we'll write an expression we have seen formatting right right small express purposes
in this I'll add one more column towards right okay instead of taking a field
directly from the data set I will right-click on this text box go to your
expression see here you have a lot of libraries you can use this libraries
math library text library okay and you can compose expressions you can use your
own data fields also take sales amount and from that I will remove cost and tax
and shipping charges okay if I remove these three things whatever that is
profit and this one with all the format currency format okay so now you see we have created the
right column which is not part of the data set using expressions we have
derived so whenever you see this graph this means you have a loss there going
back to our first report so here we have written a small expression which
will calculate profit right first product
category we'll sort this data based on per
category for the dice of cattle okay so there is no glue message
is the first person here I have a formula for calculating profit so now on
this text box on this text box properties for front color okay for
front color I will set an expression okay so this expression since a month - cost my mistakes - great
after among all these three things if it is less than or equal to zero okay then
read France okay if not
black right so this is a simple expression
that I am composing you see this the formal sale formula which is used
for calculating profit if that is less than zero
okay we should the front colors should be red if not black you know we have
written expression to format to set the color of this text see what I'm showing
you here is we can write expression not only for calculating the values okay
something like this not only for calculating value but also we can write
expression for formatting 1 we can format the entire row also we can set
the page color for a tight rope what we do is we have to write expression for
background color with the same expression ok we have to write it with
the expression for the background color with the same formula so likewise you
can use expressions you can use expressions for writing well for doing
calculations and also for setting for my of the crippled okay you we'll keep this report we will know the world we will take this
peacefully we will take this okay
the one without degradation so if you see here how many recalled clam here
there are sixteen 60,000 records that have 60,000 records I will create another people a new
report I had no I M the tenders you per location since okay now in this report I'll bring my cats reading matrix August
before her before we add anything we should have data source stimulant under in others in your
designated venture works and the data set in display here there's no obligation
okay now a new matrix for it
C matrix is very different okay from your tables here you will have both row
row and column okay we'll have both group and column
two we will come to columns up later Pro group nowadays road rule I will
select for it subcategory and for data is less sensible just poison and this one querido for metal
see this is matrix a very simple matrix okay product category wise it has
aggregated all the data now if you look at the query in the query you didn't had
any annotation on this way what matrix module is matrix will take care of the
aggregation part it will do the affiliation not have to do it in the
query so when isolated sales have note here when I selected since I'm not here
by default some aggregation is applied by default some aggregation is applied
if you want you can change the delegation you want you can change that aggregation
summarized by some average mean from restaurant to see how many sale Dan's
actions you have for category yes we can will much change the aggregation talk
about some means default now this is a fundamental difference between matrix
and tablets tablets by default will not give you any
aggregation okay it is his plane repeater it will repeat for the number
of Records we'll have in table for both Mac so if you the anti said it will show
everything if you give aggregated set it will make sense because it's only fitted
okay so that is why I created it and gave it to tablets now it come in it
comes to matrix have to do the abbreviation okay you just flip the data
now matrix will do the relationship because it is designed in that well you
cannot show detail transaction level data he cannot show record level data
means here how many requirement so we have the bottom is going 70 records your
tablets will so will show exactly 17 records put totals and subtotals and
will get multiples but it will show all the 17 records but Maddox is not like that Maddox will
not show detail regards the transaction level data so you have felt hundred and
sixty thousand records right so this matrix but it will show only seventeen
because there are only 17 subcategories so it will add up to each subcategory
and richer than seventeen because there is a fundamental difference between
tablets and matrix so matrix does the application we can do more we can hang my boobs look
just like so here dad you don't have to put head this time that is create
without hit it will create a group with credit category
so you come just next to this like this there is no result row for
this because we haven't added a header okay so just group added adjacent next
to this okay so product category necessaries now
to this query let's add two other heels you I'm pretty angry looks like blueberry country okay into more column those columns
I will use four column groups because that for incentives reform make another copy of this report you and this will make this more clear you third column groups will add country so
Thunder will come out like this so the same data now split up by content okay
we can add a header here also we can have a country Pembroke will add one
more co-payment group without any header so Europe North America and Pacific so
these are column groups okay these are column groups and these
are row groups so you have column groups and you have notebooks we can say color okay so we have got category for
subcategory as row groups and location group and country as column groups
now here if you want we can add one more column see this is what this is save
someone this is sales amount how do I know that
it is sales the month there are two ways to convey that
one way is you put an empty textbox anywhere on the report and say you you can do this you can put a title to
convey that you're actually looking at sales or you can add a blank row about this
insert road see when you have added a row here it is added as part of this
group okay as part of the school see you with
insert a screw so what will happen it will sleep you
take a listen when you say well debated everywhere because that is within the
group okay that's not my intention so I click here go outside who saves enough I can add one more column with so now this title doesn't make sense I
mean it made sense it says remove this title because what I'll do now I'll add
one more column inside the group okay ba one more column insert column inside the
group towards right inside the key and here I will choose cost standard cost so now I show both sale value and cost
if the cost is not formatted let's format
now this report is stolen can we implement that drill down that we
did earlier for row groups to column groups yes we can implement drill on
third column groups also see how we can't do that so we'll go to disparate
get me country you will open this group properties let's not do this directly on this
reporter you I knew all these reports are you know
practice that's why I'm creating multiple reports so that you also do
step by step now here on this report this group :
group on this column I could approve new properties this ability will be set by sales chat a group so the country is
ability whether to show country or to hide country will be decided by the
group group talking and I have set high height property there so by default all
the groups will be collapsed so per category wise these are the sales
numbers coming from different countries okay these are the same numbers coming
from different countries Europe automata and Pacific
maybe if I am interested to look at North America than this so in Iran
United States registers oh you know okay so this drill down as long as you have
groups okay within go ahead and put downs like this
right now let me learn how to parametrize a factor of three learned of
the parameter is how to set parameter string so there is always with publix
and the matrix a place and matrix if you have any doubts related to these two is
asking you is it a requirement that every time we
use a tablet's you have to do educated query no not at all
let's see this one they will put Publix without aggregated Quay okay but
in this along with this existing list of fields I will also include one more
field which is correctly you so this data will take now this data I will take now and you create a tavleks report so we're
creating essentially a tavleks report without aggregated query what will
happen you or under report data and the data
sources you so there will be potato work many a
times okay but that's the power of this tool actually so you have flexibility
great flexibility in our talk on polymer this one
customizing push report okay so now this is no transaction view where
you literally have all the transactions we'll get all those
transactions you okay so look at this report now take
some time and whereas it is not fetching 60,000 red cards okay so obviously it
will run through pages it will go through multiple pages on the
consecutive pages are not able to see it is okay because you have to do this
sitting public's properties to get header row on each page so that you can see this feather on
every page whatever okay sometimes that property
will not be untied properly so you can go to prove you properties you came together you okay no problem if this is not working so there is a Feder to get you so we try on the basic options we have
okay so that will be in a row on every patient if this is not working unit
showed if not you can do this he this early group have this advanced more okay
good at this advanced remote so this will open up starting fields so here you
see you are starting field here now this one has to be repeated on your page could then come out of this red one you
so No you whether it fits on all the pages you okay so right so what we have here is
now yeah obviously multiple pages that you have gone through but here showing
the entire information right all the data at once when you design a report
like this okay you better have some parameters so the end user will filter
what he want to see if you don't have a graduated data on the report okay and
blooping drilled on our options so what you can do is you can provide parameters
okay using these parameters in customer can pretty much control what you want to
see so for example I will set the parameter here so that emitters will
create a new parameter panic okay text character and a simple parameter all right okay
somebody types a patellar product name they should be able to see all the
transactions of that product all the transactions of that product so what do we do less than this you yes I'm back so here we have product
name so this will be used to filter this report so if something I type here
product name okay invalid product me not something like XLF yeah
that which that should get me only those transactions okay which belong to this
product so I created this parameter a very simple parameter which will show up
here now this parameter I will connect to
data set that so the first thing happens okay the parameter will not directly
filter data on the tab mix or any resort control what parameter will do is
parameter will filter data set data set will filter data set that data set will
in turn filter data on the chart and forth so open this here
right sort of seen
in question in there these are words of the parameter let me see here at product name so the
same name we gotta leave here so now I come back to review this time
I'll give product them I know this product name so I'll give it here our
product number for a serial number whatever the commonly used one so now if
I filter with that I'll get all the reports which belong to that product
this is very basic thing right is going to be sick thing people often doesn't
remember product names or product numbers or anything so they made simply
do like this you give me everything brown so what we should do in that case can
anybody tell me what we should do which operator we have to use in SQL percentage operator second you you since transactions here you in this water will do is will open this
data set and here instead of writing it as equal you so the novice here
so or almost one month in you if you type road he'll get everything
belong to road okay wrote I am L wrong road road okay so you can use like
operator and increase the function usability of this parameter okay what if
I don't know even those basic keywords I don't know
maybe something - earlier what did I tell it like Road
nothing it through status or something it just it just what worried it happen
to me Thanks
okay so see in case if every nothing comes up okay you can set a default
message I forgot to tell you this particular bit
tablets or matrix you have these no no message no no make sure you know those
no no message you later not found you can actually do
color formatting adjusting the padding in a building so some users came up and
he was looking for some rats but if nothing comes up then he may think that
your report is not working properly okay but if he sees this message no data form
means actually he D he'll get a feeling like okay such recorded a search for
this keyword so maybe there is no data for this keyboard okay so what you can
do in that case what you can do this yeah you can customize whatever you want
you can put you can you can give whatever the message that you want here rotate the fun they are not fun but unavailable please change the
selection criteria for whatever okay so what I will do now product name by a moment I will remove this pyramid
what these were sent over after xscape this
right you now under is what I will remove this
parameter correct okay if user doesn't know what to buy
what to look for what to search for okay I can give a set of predefined values so
create one first category product category now this product
category what I will do I will set up some available values oh I know what categories will be there
so there will be accessories myk's welcome okay actually style and clothing so now
we go you a little parameter like this of the
contour is important so you either know is an inert embody
what he has to type there okay so you you you so what we will do we will link this
parameter to our reporter it's very simple just like Williams product
well he ran right back go to Catalina third category equal to so guys whatever the name I'll give here
it should be exactly the name of the parameter so I said it close them and in the
report I only see data relative clothing okay
so this is a parameter with available values the way we have given available
value same way we can set a default value owns any every time this report
starts start with by States by its no bytes well start with your decor will be auto-5 are to fight
and by default it will show data / files and then you know can change and see
whatever you want again you can change and see what do you
want so this is perfect category but product
categories too broad okay and also that's a good question will
come there and also the limitation I have is the limitation I have is I have
to manually enter this once if what if new hot
category comes in tomorrow I will have no clue so I again have to
open this report and at noon instead of them what if
dynamically this parameter is populated with values
what if this parameter this dynamically populated key for discussing that
before discussing somebody's asking do we each can be put
all on select all option yes we can we'll say allow multiple values you know
multiple values buycks is by default selected so now you
get select all option and you can select all everything so maybe I want to say only access it
isn't working okay the book or it will happen it is it
throw it has thrown it can anybody tell me what happened yet this is necessary
system or queries I'll select a tool that you sky please what would be the parish
that is correct it is very issue can anybody guess what could be the
creation you have to define a date it does not
require both are attached okay narrator having on duration it's not a variable it's parameter so
government is already defined this is more yeah yeah so super money in
a skirt Palani Bob experiment instead so we have
to use in class remember when you have multiple values you have a limitation
with the scale value equal to will not support if you are supplying multiple
value so you have to change these two in close if you put in close in you are in
safe method will support single value will support multiple values so now you
will not run into that issue the only AK series and clothing data that's suicide you
you another copy of this report now here here are two limitations one is
we have to manually provide this values I don't want that and second thing is
this is to broaden a tactically okay is broad category okay with him candidly we
have a sub categories also see category field thing will not be able to because
many categories but sixty thousand reports okay what if I select category
in my parameter and then all the sub categories for that category show up so
that I choose about your sub category then the actual products come up
that will tinnitus right so whatever you do what I want is I want a cascading
parameter what I'm looking for I'm looking for the cascading parameter but
this cascading parameter I will not manually give values I will upload this
to fetch values from query here I will write one more I'll add one more data
set for the spindle is hit select you once it and this one I'll follow that emitter so this is a girl to
dataset contrary to parameter this I will character parameters or pill named
theme meet as param subjective I'll go back here the
parameter image to create a new parameter and available values instead of
specifying the manually I will choose this option get from query for its
objective okay so what will happen now you will have a list of subcategories
which are automatically which are automatically populated okay
we haven't manually right values we haven't manually pen these values are coming from this query
okay so we now solve the first problem
instead of providing values manually now we are getting values from a query okay
so any new value adds up in the database will automatically reflect in our
parameter so we don't need to worry in keeping this thing updated okay
so k will query will take care of that so now what you do the second part how do I connect these
two see what I want now if I select pipes here
only pipe subcategories of list here simply stop it
if I select accessories only accessories should be listed over here so how do I
pay attention here see how this category filtering the report when has spread
some category see here president bikes only buy three parts are coming on the
depot's or is it happening because this category is connected to the main
dataset the main dataset now what I will do instead of connecting it to the main
dataset to scatter giving I will connect this to subtitle a data set that the
data set which is getting some categories here I will write here I will
copy this and put it here okay so what it will do now anything
that you select care in the category will filter subcategory data set if some
credible data set is fatal it has a third degree the parameter is also heated right now
we have got a the main jet so here I will go and say in product subcategory okay since I have enclosed it I can
relate this to equitable say what's up from that so your bikes
are selected so only by created some categories will show up here so i select
road bikes only road bikes no data powered by this
square thing so on protein ones will be standard tabs right right here we will change this
parameter column subcarrier diameter should be applied on subcarrier so there should be ratified road bikes
Cena bikes robots this is nice taking param to by default bikes are selected
when bikes are selected here also by default everything should get selected
if that is a requirement what I have to do is for sub can be parameter different
run is also I should supplied sale data set
and what happens there in the first parameter bikes and selected in the
second parameter all the bikes on carry research by default will tomorrow I want
accessories so they're all doing silversmith and
also maybe I want a specific I want to look at it specifically mr. again they
specifically looked at those two okay so this is on you can set auto populated
parameter and a cascading parameter okay they have any doubts here do you have any doubts you okay this we will do the final
implementation we're finally in this last part of our
last demonstration in our study session can you please explain it date values what we'll do in this phone triple
dominate we will include ordered it you okay you take this one you I did and just before sales among you
conclude that further day you so these are our edits now based on this
we want to apply a parameter so these two are connected and Bertha
directly these two are connected again one is dependent on the other
subcategories dependent on Catalina okay so he can simply grant subcategory to
this data set now I'll add one more parameter you or a day
so you actually look at what are the dates we have okay we will we can do a
range of date started in date okay we can first what we do is we'll take one
standard deal just show with one standard date later our chose target and
ended so this will be of data type data and this see here you can arrange these
parameters we can set which one you want to show on top are they dictate will
directly work on this data set in axis and or I did you I know that for 2007 10:30 I have Sunday
dinner you okay so this all data will be just for
that date but maybe this cannot be the pigment requirement using the data files
Amelia okay so you may want to look at a range
start date and end date okay so you can set parameters so this parameter will name
it is started you to create our
from motometers they can actually said I will default
values also default values available values already then do through expressions or manually now that
I have started and ended I can't go here today they say
yes already between ordered darted and order tended so you can put it in parentheses
sticking through stability facilities one filter acting on it and here is
another filter city notice there is no product category filter here because
product category filter is not needed here for a category is filtering sub
category and subcategory will filter main dataset so that is fine so now maybe for that year 2007 I want
to see entire October month so it's 1st of October
technical stuff so this will get me entire one month
range of data okay like this you can set eight parameters also okay I have one
more question also can we modify this report to use ether
date parameter our category and subcategory if dirty selected and other
parameters produces it yes we can very much do that okay so you're getting me
to advanced more of requirements okay you okay so first what do you have to do is
you have to put everything that you have here in a stored procedure okay do you
have to put everything that you have here in a stored procedure and all these
three problems she'll be supplied has parameters to the
parameters of that stored procedure okay you have to set this as parameters of
that stored position if you do that what will happen means install positions you
have a concept of null parameters okay you sit for a category subcategory
ordered date everything as null parameters in valley is not supplied it
will take null but histopathology will still run okay and come here and say
product category along value okay so if it's a multi part it will not allow null
value here also you said Alan welcome here so these two will allow
null values got it started and ended but noggin so what happens is
by default this will be disabled okay so only your main parameters will work if
you want you can enable them and pass values now when they are disabled in
their null how do your data set query know that they're okay so it becomes too
complex here writing if null they do this if not now to do this it becomes
complex so what we'll do put this select query put this select query in Ashdod procedure if you put in a
stored procedure then install procedure you have a concept of the lattice you null you can say like years null means
if nothing is supplied to this parameter it will take by default don't this only four ordered it and
turned it don't do corporate category category in null value has to be hand in
a very good friend to it now that we are there let's discuss that
why it has to be differently a divide has to be in a different way yes it's a
very interesting thing in SSRS see here if I allow multiple values and I select
allow null value it will throw an error can anybody tell me why this error might
have occurred I will give you a hint this error is because of SQL query this
error is not as a service error this is an SQL query error can you tell me why
it is can anyone ever tell me by this error
this is an SQL query error you well distinct how any language so cannot
be added with other than you monitors and now it is enter and if I will review
the record this all are subjective deficients which all are correct the
very simple reason is that you are see if you know if you allow multiple values
then you know that you have to use in Clause right you have to use in plus and
in clause doesn't support null as a value in Klaus doesn't support null
as a value so simply as a service will restrict you choosing those two options
together because in doesn't support null because like Adam said null is not and
quantifiable then you are like mono said it cannot translate this as other any
other regular run you out of you Nellie is like a black phone okay hey careful
with it very careful method - so we do a very important demonstration I will show you a very
important assignment we take this or to go you College has child even know why very
shortly why and twenty disaster from back here
remove this target just can't agree ends up relative under multiple values and not default
values or mules just a simple parameter you so these notice it is also not needed
the main data set the mall is of the Kip Diskin does there
we'll add two more filters you for total 4 parameters
you you totally for parameters these four parameters there are no
available values no default values no multi values no cascading values nothing
and these four parameters I will take them as internal are hidden because you
again so nothing will show I think in swap this child you put I
will keep it here I'll go back to one of our Mattox works here define it
important a question okay in version two you they take this version too and we make a
copy of it in this I only want a month the standard
cost column I removed okay
and this is fun hmmm does you or position since parent so this is a now what I want if I click on any of the
cell say for example if I click on this particular cell okay this Patricia then all the
transactions belong to this cell should show up all the transactions belong to
this cell means all the transactions which happened in Germany Europe Germany
for the product category accessories and subcategory fenders what I want so this
column headers and row headers filter should be auto blind if I click on the
cell so that action is called drill-through okay that action is called
to do so we will be designing mountain to see how that isn't really true watch
carefully so on this cell where I am intending to
play and expecting the action so that tape so here equal to text box
properties and in this text box properties are something called action
for this action I will set it to go to a report that was will be the children
would he already know this child report has both parameters so add those four
parameters what are those four parameters for a category product
subcategory product group and country these are the four parameters you have
in that changeable number these four parameters
we will supply values from this parent to the right side we will set the
bandwidth for can digital payments of my category was up some blue illustrator if
you country so now see what happens if I click now on this number again arm
this number right on this number Europe Germany fenders accessories click this so that childhood will take all these
filters parameter values and you will see
okay sorry I missed out one thing completely sorry my mistake
go to child report here we have said parameters but we haven't connected
those parameters to the data set you for now if I select on that particular
seller i should see only those germany records per vendors all your data will be Germany and
product will be justice but the burglar didn't just stand take a yeast cell everyone united
kingdom and sucks so if we give it a get here
bad button so that he came back to apparently border we'll go back to
parent report we click this United Kingdom
yeah we can say this once you deployed to get so let's deploy this entire
project all networks will give them so far you no matter who won from 2:30 to L SQL
Server 2012 onwards okay things you and separating the poor loser you purple Chester swatting yeah okay the climate is successful we need a
fresh training project you for it still in place you say what is this what is this as a
service input RTL report definition language
it simply XML they can get up or here again it's a
simple XML file so this XML file as part of this deployment process is XML file
will be uploaded to your reports over here you can click on this parent report if you do with access to this parent
report it's enough so somebody who's viewing this replication cells right he can very much experience that
interaction that will to implemented future you in editing them realists desert kingdom 19 starts off in our
pioneering lights a two-page report full of illness from
amended you there is an African I should put that button here okay
you you can click the browser back button
say they 1310 introvert Canada is purchasing lot of cleaners
next United States next one okay the least cleaner states are in Europe
actually the same socialist in Europe okay so that's our walk with this flag
to put stable reports stable or matrix okay so you can set parameters drill
downs three throughs hooping and custom expressions okay guys we've come to the
end of this session I hope this session on msbi was
informative for you if you have any doubts feel free to comment about it
below thank you
