All of your videos are just PERFECT, in fact more than perfect. They are so clearly explained and you really take the time out to make the videos knowledge enriched by telling all the details necessary. Thank you for posting all your videos. Keep the great work going.
Just repeating what others had already stated...great videos, very organized. There is a progression, adding more knowledge from one video to the next. Thank you for this.
Great guides these. We are in the process of migrating from business objects and Oracle databases to SSIS and SSRS. Quite a steep learning curve without a universe behind it and getting in the mind set of using stored procedures for our queries. Thanks again for these :)
Absolutely brilliant! I truly didn't know how cool SSRS was, so many companies are hung up on SAS and Crystal Reports! You've made this package (and SQL Server) easy and enjoyable to understand! By using the Movies DB I have a much better understanding of the results that I'm trying to achieve, AdventureWorks is just too big for a beginner like me! Thanks and keep up the great work
I am really enjoying these videos, THANK YOU!! I'm transitioning jobs from a crystal 2013 based position to an SSRS position and this is helping my learning curve greatly!!
:) yes, problem solved, thank you. For those who have the same problem I had: Install the SQL Server 2012 with SP1, the filename is "SQLEXPRADV_x64_ENU.exe" Express with Advanced Services (SQLEXPRADV) •This package contains all the components of SQL Server Express including the full version of SQL Server 2012 Management Studio with SP1. This is a larger download than “with Tools,” as it also includes both Full Text Search and Reporting Services.
Hi there. Your videos massively helped me understand SSRS which was unknown to me before. I'm trying to learn more about SSRS to help me in my future job prospects. I recently left a job where I was using Microstrategy as the primary BI reporting application. However, Microstrategy is very niche and most companies use Microsoft's SQL stack as it is cheaper. I'm still going through your walkthrough videos, but aside from this do you recommend any additional resources regarding sql server/ssrs learning? Many Thanks
This video will always come handy. Good job!!!. Question: what is the best practice or what is the most efficient way, is it by calculating the column right from the source(For example like calculating the Net_Profit in the Store procedure used in the Data set or calculating it in SSRS like you are demonstrating? Thanks in anticipation.
Nice tutorial and I will certainly return to it often, but it wasn't what I was looking for this time. I'm trying to get the sum of a column that has been grouped and is itself a countdistinct expression. Any suggestions?
Thanks a lot sir. Wonderful explanation. I got a question here. If i have a table Italy NR FY14Jan 800 Italy COS FY14Jan 900 How can i find ratio of NR/COS in SSRS report. Thanks in Advance
Hi Thank you for all your tutorials they are so helpful. Is there a way to use count and iif to return only the count of rows where their value is say greater than zero. I tried =Count(IIF(fields!field_name.value 0, 1, nothing) or iif(fields!field_name.value 0, Count(fields!field_name.value, nothing) but I either get 1 or nothing - which of course makes sense but I can't figure out how to get a count only for rows where a value not equal to zero exists. Thanks for any help on this.
Hello, WiseOwlTutorials! How to calculate filed with textbox value? Example(=Fields!SaleQty.Value/txtGrandTotal_SaleQty.Value) Show error like this:txtGrandTotal_SaleQty.Value is not declared it may be inaccessible due to its protection level
Hello, Finally some conclusions...After "SQL Server Data Tools" instalation, the SQL Server services were stopped (I just have to start them and enable the protocols).
These tutorials are fantastic, but does anyone know why the logic for calculating the film birthday as greater than today worked? Why didn't that operation make the code yield a result of the birthday date (including the year) as being LESS than the value of today's date including the year? How did the code know to only examine the day and month?
Great videos bro, thank you so much. Small doubt what is the expression that helps me to find Middle Name 'Jamie Lee Curtis' Here I want to show 'Lee' as Middle Name
Now I've: 1- SQL Server Express 2008R2 and SQL Server Express 2012 working fine. 2- VS2008+BIDS = Works perfect, like in WiseOwlTutorials (with Bus.Intel template). 3- VS2010 Ultimate + "SQL Server Data Tools V.10.3.21208" = Works, but Missing the Business Intelligence template, so I can't preview or "Add Calculated Field..." Do you have any ideia how to add the template in VS2010? Thank You.
Hello! Thank you for this great tutorial. What if you are trying to calculate percentage that represents every film budget dollars from the total or sum of this column. I am trying this formula (=100*Fields!WHours.Value/Sum(Fields!WHours.Value)) but getting errors... What am I doing wrong?
Hello, I don't have the option "Add Calculated Field..." It's because I'm not using "SQL Server Data Tools" for VS2010 ? I had installed "SQL Server Data Tools" but for some reason my "SQL express server 2008R2" instante stoped working, and I had to uninstall "SQL Server Data Tools". There are another way to add calculated fields in the dataset to use in the report table? Thank You,
I have calculated Total by using "Add Total" function in the matrix for each hour. Now I want to calculate the percentage of that calculated total on an hourly basis. Can you please help me with this.
Hello Great WiseOwl! I am trying to summarize the values of three different datasets for each Salesperson row in a single matrix that shows only total units and sales for each dataset per salesperson, like a crosstab. Would this be Lookup() or LookupSet() and do you have any videos addressing my issue? I can't imagine this is a unique need. I've hunted can't find anything helpful though. Thanks if you can help.
I have stored procedure in SQL, In this Stored procedure i have written 8 Select statement. When i run this stored procedure in SQL it gives all result, but when in run this in Dataset of SSRS it return only first select statement result from stored procedure. How to get all result of stored procedure in SSRS. Kindly help. Thanks in advance.
Hi WiseOwl. Is there a way to calculate an aggregation based on dates. e.g. Data stored in a matrix is sales, column is month and calender year. each month shows 3 years. i want the calculated field to take the most recent months and calculate the percentage change between them.
Hi there, I was wondering if there is a way to do a calculation based on aggregates? E.g. Total Sales (sum), Total Costs (sum) then an expression to calculate the profit margin?
Hi there again! :) Got a question about using Visibility/Hiding a grouped Row or a Tablix. Google search comes up with something like this: =IIf(CountRows("Items") = 0 , True, False) I don't want the parameter to be fixed ex: ('Item'), but to be according to the fields. If there is a data for a field, lets say LCApprovals then, show the row/tablix else hide! how can i achieve this! Thank you in advance :)
Hi, I have installed: Visual Studio 2010 Ultimate Ver.10.0.40219.1 SP1Rel SQL Express server 2008R2 SQL Express Server 2012 After the "SQL Server Data Tools" installation (on Windows8 pro), I can´t access my SQL instances using Server Management Studio or Visual Studio (Server Explorer or SQL Server Object Explorer). I get Connection errors (error:40, error:2 and error:26). If I open SQL server configuration Manager, all the protocols are enabled, but the SQL service failed 0x800706be. Thank you
really useful videos! Can somebody navigate or help me how to calculate QUANTITY of users or name of products in the table then show it in the report ?
Has anyone tried using a calculated field to analyse a query field whose values are calculated by pulling data from another dataset by a lookup function? I'm getting no where trying this and would appreciate any advice.
Thank you! It appears there may be a bit of confusion so apologies for this. The calculated field does not use a lookup though it attempts to analyse a query field in the same table that does use a lookup (to another dataset). So far I have given up and am trying to find a work around!
How do you convert Julian date to date Julian date = 735600 Date 2015-01-01 00:00:00.000 In a SQL query I use this, to convert from Julian date to date: SELECT DATEADD(dd,( table.date_due - (SIGN(table.date_due)*693596)),'19000101') AS due_date from table
Hi if possible can you please help me to rewrite this calculation within Report Builder Thank you COUNTD( IF [Curriculum] = "CDTS Project Coordinator Certification Schedule Management" and [Status] = "Completed" or [Status] = "Acquired" THEN [Learner NUID] END) Thank you
Hi Joe, is this from Tableau? In Report Builder there's a CountDistinct function and an IIf (that's not a typo, it's spelt with two Is). This example would count the unique film titles: =CountDistinct(Fields!Title.Value) This would count the unique film titles where the Oscar wins is greater than 0: =CountDistinct( IIf( Fields!OscarWins.Value>0, Fields!Title.Value, 0)) This would count the unique film titles where the Oscar wins is greater than 0 an the genre is Drama: =CountDistinct( IIf( Fields!OscarWins.Value>0 And Fields!Genre.Value = "Drama", Fields!Title.Value, 0)) I hope that points you in the right direction!
@@WiseOwlTutorials thank you for ur reply. Yes it’s tableau. If I understand ur reply correctly. I need to make three separate calculations on top of each other. First counting the learner NUID. If that’s greater then 0 then do the if statements
@@joefromdc Hi Joe, no the three examples above are independent of each other, I included them to show how to build up the syntax. The final example is the closest to the one you need - you can include multiple conditions in the IIf function using And and Or operators in the same way you've done in your Tableau example. The main difference is that the IIf function in Report Builder requires a third argument which says what to do if the logical test returns False - it appears that this is optional in Tableau. You might find the videos in this playlist helpful ruclips.net/p/PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU Part 7.1 to 7.12 cover how to create expressions in Report Builder (Part 7.2 covers the IIf function) and Part 9.1 to 9.10 cover using aggregate functions and how scope works. I hope that helps!
@@joefromdc Hi Joe, I think your expression would need to look something like this: =CountDistinct( IIf( Fields!Curriculum.Value = "CDTS Project Coordinator Certification Schedule Management" And (Fields!Status.Value = "Completed" Or Fields!Status.Value = "Acquired"), Fields![Learner NUID].Value, 0))
All of your videos are just PERFECT, in fact more than perfect. They are so clearly explained and you really take the time out to make the videos knowledge enriched by telling all the details necessary. Thank you for posting all your videos. Keep the great work going.
Just repeating what others had already stated...great videos, very organized. There is a progression, adding more knowledge from one video to the next. Thank you for this.
You are absolutely clear with your explanation on SSRS .I did enjoy your tutorials .Pleas keep this up. "Clear and concise" is the WORD.
Wonderful wonderful wonderful .. I really need it . so no need to spend a lot of money to learn SSRS . You're the best and God bless
These tutorials are fantastic!!! A fabulous way to learn about SSRS. Thank you Andrew!!!!
Great guides these. We are in the process of migrating from business objects and Oracle databases to SSIS and SSRS. Quite a steep learning curve without a universe behind it and getting in the mind set of using stored procedures for our queries.
Thanks again for these :)
You are the best of the best! An awesome Teacher. The pace is great, explanations are great and I am fast becoming a BI expert. Thank you
Absolutely brilliant! I truly didn't know how cool SSRS was, so many companies are hung up on SAS and Crystal Reports! You've made this package (and SQL Server) easy and enjoyable to understand! By using the Movies DB I have a much better understanding of the results that I'm trying to achieve, AdventureWorks is just too big for a beginner like me! Thanks and keep up the great work
Great SSRS videos!!! All of them. The way you explain is very clear. Thank you very much. Good Work.
I am really enjoying these videos, THANK YOU!! I'm transitioning jobs from a crystal 2013 based position to an SSRS position and this is helping my learning curve greatly!!
Absolutely you deserve NOBLE PRIZE...!! Thank you so much for making these videos.
Great tutorial! I'm glad to see that the expression builder was borrowed from Access. Makes it easy to figure out.
Excellent tutorials and explained in a very clear way. I can recommend them to anyone
Thanks
These videos are superb and deserve a lot of support. "Unfortunately", indeed( 3:50 ).
Brilliant tutorial videos, coming in handy trying to update my SQL skills whilst looking for employment.
Awesome Bro I am new to this field,Your tutorial is More helpful to learn SSRS Thank you Bro!!!!
Thank you so much for sharing such wonderful tutorial videos. May god reward you Andrew
:) yes, problem solved, thank you.
For those who have the same problem I had:
Install the SQL Server 2012 with SP1, the filename is "SQLEXPRADV_x64_ENU.exe" Express with Advanced Services (SQLEXPRADV) •This package contains all the components of SQL Server Express including the full version of SQL Server 2012 Management Studio with SP1. This is a larger download than “with Tools,” as it also includes both Full Text Search and Reporting Services.
I just love your videos, I watch them daily and practice at the same time., helps A LOT!
Great sense of humor too!
thumbs up!
Another great work of yours, my friend. Congrats!
SSRS best kept secret appreciate the thorough walkthroughs.
Your tutorial helps me a lot ! Thank you so much ! You rock !
So very helpful! Thanks for the detailed information!
great videos! very clear and organized! thanks
Your videos are really helpful, thank you so much and keep uploading more videos
Awesome lectures.. Really helpful.. Thank you So Much..
fantastic videos and a lovely accent!
please,keep going
you are the best
:D thank you for the comments and support!
Brilliant. Thanks for your tutorial.
Thank you for these tutorials! :)
gr8 work, thanks its really helpful for quick start for a beginner.
Thank you for such a wonderful explanation
Simply PERFECT !!!
Thank you
Hi there. Your videos massively helped me understand SSRS which was unknown to me before. I'm trying to learn more about SSRS to help me in my future job prospects. I recently left a job where I was using Microstrategy as the primary BI reporting application. However, Microstrategy is very niche and most companies use Microsoft's SQL stack as it is cheaper.
I'm still going through your walkthrough videos, but aside from this do you recommend any additional resources regarding sql server/ssrs learning?
Many Thanks
Enjoyed this video. But I find difficulty in understanding that Birthday and onwards.
Thanks for educating me SSRS
This video will always come handy. Good job!!!.
Question: what is the best practice or what is the most efficient way, is it by calculating the column right from the source(For example like calculating the Net_Profit in the Store procedure used in the Data set or calculating it in SSRS like you are demonstrating?
Thanks in anticipation.
THANKS VERY MUCH
what the name of the presenter?. i want to congratulate him personally. he has a lucid and spontaneous way of expressing.
Thanks for being a blessing to so many.
How do I get this database - Movies DB in other to work along with your lessons?
Thank you!
Nice tutorial and I will certainly return to it often, but it wasn't what I was looking for this time. I'm trying to get the sum of a column that has been grouped and is itself a countdistinct expression. Any suggestions?
Thanks a lot sir. Wonderful explanation.
I got a question here. If i have a table
Italy NR FY14Jan 800
Italy COS FY14Jan 900
How can i find ratio of NR/COS in SSRS report. Thanks in Advance
Hi Thank you for all your tutorials they are so helpful. Is there a way to use count and iif to return only the count of rows where their value is say greater than zero. I tried =Count(IIF(fields!field_name.value 0, 1, nothing) or iif(fields!field_name.value 0, Count(fields!field_name.value, nothing) but I either get 1 or nothing - which of course makes sense but I can't figure out how to get a count only for rows where a value not equal to zero exists. Thanks for any help on this.
Hello, WiseOwlTutorials!
How to calculate filed with textbox value?
Example(=Fields!SaleQty.Value/txtGrandTotal_SaleQty.Value)
Show error like this:txtGrandTotal_SaleQty.Value is not declared it may be inaccessible due to its protection level
To 7:00 is it possible to add a colour? If lost than text in red
Hello, Finally some conclusions...After "SQL Server Data Tools" instalation, the SQL Server services were stopped (I just have to start them and enable the protocols).
How I can pass a column value within a query through a parameter
i don't know why but it is accepting the segoe ui font
These tutorials are fantastic, but does anyone know why the logic for calculating the film birthday as greater than today worked? Why didn't that operation make the code yield a result of the birthday date (including the year) as being LESS than the value of today's date including the year? How did the code know to only examine the day and month?
Is there a way to take the difference of columns in a matrix? For instance, I want to calculated current month sales - last month sales.
just a question. when is the time that you will do the calculation part on the DataSet and when will it be to do that on the actual column properties?
Great videos bro, thank you so much.
Small doubt what is the expression that helps me to find Middle Name
'Jamie Lee Curtis'
Here I want to show 'Lee' as Middle Name
Now I've:
1- SQL Server Express 2008R2 and SQL Server Express 2012 working fine.
2- VS2008+BIDS = Works perfect, like in WiseOwlTutorials (with Bus.Intel template).
3- VS2010 Ultimate + "SQL Server Data Tools V.10.3.21208" = Works, but Missing the Business Intelligence template, so I can't preview or "Add Calculated Field..."
Do you have any ideia how to add the template in VS2010?
Thank You.
Hello! Thank you for this great tutorial. What if you are trying to calculate percentage that represents every film budget dollars from the total or sum of this column. I am trying this formula (=100*Fields!WHours.Value/Sum(Fields!WHours.Value)) but getting errors... What am I doing wrong?
Hello,
I don't have the option "Add Calculated Field..."
It's because I'm not using "SQL Server Data Tools" for VS2010 ?
I had installed "SQL Server Data Tools" but for some reason my "SQL express server 2008R2" instante stoped working, and I had to uninstall "SQL Server Data Tools".
There are another way to add calculated fields in the dataset to use in the report table?
Thank You,
I have calculated Total by using "Add Total" function in the matrix for each hour. Now I want to calculate the percentage of that calculated total on an hourly basis. Can you please help me with this.
Hello Great WiseOwl! I am trying to summarize the values of three different datasets for each Salesperson row in a single matrix that shows only total units and sales for each dataset per salesperson, like a crosstab. Would this be Lookup() or LookupSet() and do you have any videos addressing my issue? I can't imagine this is a unique need. I've hunted can't find anything helpful though. Thanks if you can help.
I have stored procedure in SQL, In this Stored procedure i have written 8 Select statement.
When i run this stored procedure in SQL it gives all result, but when in run this in Dataset of SSRS it return only first select statement result from stored procedure.
How to get all result of stored procedure in SSRS.
Kindly help.
Thanks in advance.
Hi thank you, is there a way to sum values of a column if it's true a condition in another column. ex: sum(A.Value) if B.Value= "S"
Hi WiseOwl. Is there a way to calculate an aggregation based on dates. e.g. Data stored in a matrix is sales, column is month and calender year. each month shows 3 years. i want the calculated field to take the most recent months and calculate the percentage change between them.
Hi there, I was wondering if there is a way to do a calculation based on aggregates? E.g. Total Sales (sum), Total Costs (sum) then an expression to calculate the profit margin?
I dont have that option of add a query field, do you know why? I only have the option to refresh, delete, and dataset properties...
Hi there again! :)
Got a question about using Visibility/Hiding a grouped Row or a Tablix.
Google search comes up with something like this:
=IIf(CountRows("Items") = 0 , True, False)
I don't want the parameter to be fixed ex: ('Item'), but to be according to the fields.
If there is a data for a field, lets say LCApprovals then, show the row/tablix else hide!
how can i achieve this!
Thank you in advance :)
Stumbled upon the answer! :)
=Iif(IsNothing(Fields!LCApprovals.Value),True,False)
works! :)
cheers!
how would you calculate for example business days
PLEASE WE NEED TUTORIALS ON SQL ADMINISTRATIONS TOO PLEASE PLEASE PLEASE !! THANKS
Can you do a sumif or if example?
Hi, I have installed:
Visual Studio 2010 Ultimate Ver.10.0.40219.1 SP1Rel
SQL Express server 2008R2
SQL Express Server 2012
After the "SQL Server Data Tools" installation (on Windows8 pro), I can´t access my SQL instances using Server Management Studio or Visual Studio (Server Explorer or SQL Server Object Explorer). I get Connection errors (error:40, error:2 and error:26).
If I open SQL server configuration Manager, all the protocols are enabled, but the SQL service failed 0x800706be.
Thank you
really useful videos! Can somebody navigate or help me how to calculate QUANTITY of users or name of products in the table then show it in the report ?
Has anyone tried using a calculated field to analyse a query field whose values are calculated by pulling data from another dataset by a lookup function? I'm getting no where trying this and would appreciate any advice.
that sentence is a bit of a mouthful!! I tried my best to explain and i really hope it is comprehensible!
Thank you! It appears there may be a bit of confusion so apologies for this. The calculated field does not use a lookup though it attempts to analyse a query field in the same table that does use a lookup (to another dataset). So far I have given up and am trying to find a work around!
How do you convert Julian date to date
Julian date = 735600
Date 2015-01-01 00:00:00.000
In a SQL query I use this, to convert from Julian date to date:
SELECT DATEADD(dd,( table.date_due - (SIGN(table.date_due)*693596)),'19000101') AS due_date from table
*****
I have tried that and I get this error:
Adding a value to a 'datetime' column caused an overflow.
***** There where some Fields that only had 5 digits instead 6, so I changed them to 0 and it works now.
Hi if possible can you please help me to rewrite this calculation within Report Builder Thank you
COUNTD(
IF [Curriculum] = "CDTS Project Coordinator Certification Schedule Management"
and [Status] = "Completed" or [Status] = "Acquired"
THEN [Learner NUID] END)
Thank you
Hi Joe, is this from Tableau? In Report Builder there's a CountDistinct function and an IIf (that's not a typo, it's spelt with two Is). This example would count the unique film titles:
=CountDistinct(Fields!Title.Value)
This would count the unique film titles where the Oscar wins is greater than 0:
=CountDistinct(
IIf(
Fields!OscarWins.Value>0,
Fields!Title.Value,
0))
This would count the unique film titles where the Oscar wins is greater than 0 an the genre is Drama:
=CountDistinct(
IIf(
Fields!OscarWins.Value>0 And
Fields!Genre.Value = "Drama",
Fields!Title.Value,
0))
I hope that points you in the right direction!
@@WiseOwlTutorials thank you for ur reply. Yes it’s tableau.
If I understand ur reply correctly. I need to make three separate calculations on top of each other. First counting the learner NUID. If that’s greater then 0 then do the if statements
@@joefromdc Hi Joe, no the three examples above are independent of each other, I included them to show how to build up the syntax. The final example is the closest to the one you need - you can include multiple conditions in the IIf function using And and Or operators in the same way you've done in your Tableau example. The main difference is that the IIf function in Report Builder requires a third argument which says what to do if the logical test returns False - it appears that this is optional in Tableau. You might find the videos in this playlist helpful ruclips.net/p/PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU Part 7.1 to 7.12 cover how to create expressions in Report Builder (Part 7.2 covers the IIf function) and Part 9.1 to 9.10 cover using aggregate functions and how scope works.
I hope that helps!
@@WiseOwlTutorials thanks again for helping. I watched the video but I’m still confused.🥲
@@joefromdc Hi Joe, I think your expression would need to look something like this:
=CountDistinct(
IIf(
Fields!Curriculum.Value = "CDTS Project Coordinator Certification Schedule Management" And
(Fields!Status.Value = "Completed" Or Fields!Status.Value = "Acquired"),
Fields![Learner NUID].Value,
0))
will you provide me a data As used in the Tutorials.
Hi! You can find a link in the video description to download a script which will create the Movies database. I hope it helps!
Someone is not a Titanic fan *High Five!* :D
@ 3:45 ahahahahaha unfortunately
Not a Titanic fan then?