Power BI Report Builder Parameters Tutorial (2022-07-11)

SQLServerCentral Newsletter for July 11, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Power BI Report Builder Parameters Tutorial]( - [DevOps 101: Unlock the value of frequent deployments with Database DevOps]( - [Datamarts and exploratory analysis using Power BI]( - [From the SQL Server Central Blogs - The First 30 Days at a New Database Job]( - [From the SQL Server Central Blogs - How do I tell if identity_insert is turned on and if so what table?]( Question of the Day - [The Ranking Function]( The Voice of the DBA  Daily Coping Tip Look back at the progress you’ve made at some goal or task I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Rebooting for a Reason I've worked with computers for a long time. I've helped support various systems and applications, both desktop and servers. One of the most common tricks that has served me well is to press to oh-en-oh-eff-eff switch twice. In other words, reboot. This is advice that many tech support people use. It's what is often recommended for everything from personal computers to mobile devices to watches to really any sort of microchip device. It's been recommended for my Tesla and for a few appliances as well. It's also incredibly frustrating advice to hear that when we expect a device to run constantly, like a watch. Why should I reboot it? Isn't your code bad? Isn't it the manufacturer's fault? Isn't this a cop-out to get me off the phone/chat/etc. and close out a call? This is likely bad code, and it might be a way to get you off the phone, but there is some rationale behind this troubleshooting step. I ran across this article on [the unreasonable effectiveness of turning computers off and on again](. It provides some reasoning why rebooting makes sense and why it can help. The short answer is this action returns the code and device to a known state. Often when things are broken, we're in an unknown state. There's also an interesting parable about writing a shell that is very strict with its evaluation of input and crashing when things aren't right. The author wrote another shell that is loose in its evaluation of input. Read the piece to see which shell actually made more sense to the programmer. I found this interesting and fun to read, and it made me feel better about needing to reboot systems. Less excited about the need to reboot a car or a plane (I've been on a 787 when it rebooted), but since I've seen the former continue to work, I'm less anxious. Take a look today and let me know if this article makes you feel a little more comfortable with giving out the "reboot" solution to others. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Power BI Report Builder Parameters Tutorial]( Daniel Calbimonte from SQLServerCentral Learn how you can adjust the way users interact with reports and change font sizes as well as add parameters for filtering. [External Article]( [DevOps 101: Unlock the value of frequent deployments with Database DevOps]( Additional Articles from Redgate Frequent deployments can give you greater flexibility in meeting changing business requirements, but if code quality is poor this may cause major headaches for your customers and your whole organization. Join Microsoft Data Platform MVP, and AWS Community Builder Grant Fritchey to discover the benefits of, and best practices for frequent deployments. [External Article]( [Datamarts and exploratory analysis using Power BI]( Additional Articles from SimpleTalk During Build 2022, Microsoft announced a new feature, Datamarts for Power BI. MVP Dennes Torres explains this new feature and provides and example. [Blog Post]( From the SQL Server Central Blogs - [The First 30 Days at a New Database Job]( Tracy Boggiano from Database Superhero’s Blog Over the last four years, ok it seems longer than that, I’ve started four jobs. A couple just weren’t good fits. One I was at for three years. I... [Blog Post]( From the SQL Server Central Blogs - [How do I tell if identity_insert is turned on and if so what table?]( Kenneth.Fisher from SQLStudies TLDR; The code to do this is below but it’s a bit hokey and probably isn’t worth it. It’s pretty ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Ranking Function I have a table with various products and associated metadata. I run this query: SELECT r.ItemName , r.ItemType , RANK () OVER (ORDER BY r.Price desc) AS Ranking FROM dbo.Ranking AS r; How many products and which types are ranked with a "2" for the Ranking column in the result set? The DDL and DML are below: CREATE TABLE dbo.Ranking ( ItemID INT NOT NULL , ItemName VARCHAR(10) , ItemType VARCHAR(20) , Qty INT , Price int ) GO INSERT dbo.Ranking (ItemID, ItemName, ItemType, Qty, Price) VALUES (1, 'Dell', 'Laptop', 2, 500) , (2, 'HP', 'Laptop', 3, 800) , (3, 'Lenovo', 'Laptop', 4, 900) , (4, 'Samsung', 'Mobile', 5, 900) , (5, 'Apple', 'Mobile', 6, 1200) , (6, 'Apple', 'Watch', 2, 400) , (7, 'Samsung', 'Watch', 3, 500) , (8, 'Garmin', 'Watch', 4, 200) , (9, 'Rolex', 'Watch', 5, 1200) GO Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Pizza Math (from a post on Twitter). I went to get a round 9 inch pizza the other day, but they had run out of those pans. They brought me two circular 5 inch pizzas. What does this mean for my hunger? Answer: I'm hungry. I get much less pizza than I expected. Explanation: While you might be tempted to say one should never eat pizza, that's certainly incorrect. This is a basic math question about the area of a circle. For a 9 inch pizza (circular), there are 254 sq. inches. A 5 inch pizza is only about 78.5 sq inches, so two of them are 157 sq inches, nearly 100 inches short.  [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2016 - Administration [First responders kit - BlitzIndex question]( - Hello, hope someone can help, maybe a simple question so i am using the Blitzindex from Brent Ozar, and its great, but one question, with some of the findings being "Missing Index" or "Index phobia", it does say estimated impact or missing index request... I am looking for a way to find out "This would […] SQL Server 2016 - Development and T-SQL [Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)]( - (1) Sort rows with year column (smallint) and month column (char(3)) == Jan, Feb, Mar, etc. == correctly, without using CASE or other conditional expressions (the simpler the better). (2) Table "emp_training" has (emp_id int, training_id int, status char(1)) where status = F(fail)/P(pass). Each employee (emp_id) may pass a class only once but may fail […] [Designing a table]( - Hi, I have this list of training courses: Course_Code|Description 1 |Health and Safety 2 |cusomter relations 3 |IT training 9 |None Thanks. I have this scenario: A worker may attend up to 4 training courses per year. I need to design a table "WorkersCourses" where I can enter multiple records per worker. This will need […] [Which table design is better ?]( - Hi, I have a scenario where a worker can do multiple training courses each year (max 4). Is it better to create a table this way:  WorkerID |Year |Courses Worker01 |2021 |CourseCode01 Worker01 |2022 |CourseCode01 Worker01 |2022 |CourseCode02 Worker01 |2022 |CourseCode03 Or this way:  WorkerID |Year|course1 |Course2 |Course3 |Course4 Worker01 |2021|CourseCode01| | | […] [CAST AS DATE Assistance]( - Can someone let me know why I'm not able to use CAST method to cast a field as date  ;WITH numbering AS ( SELECT MontaguOwner, CurrentOpportunityStatus, LastDateStatusChanged, OpportunityName, rank() OVER (PARTITION BY MontaguOwner, CurrentOpportunityStatus ORDER BY LastDateStatusChanged DESC) AS rank, row_number() OVER (PARTITION BY MontaguOwner, CurrentOpportunityStatus ORDER BY LastDateStatusChanged DESC) AS row_number FROM enrorigination.opportunities_hv […] [Put result of one query into another]( - Hi all! I have to do this 177 times more, and it must be possible to make it a bit more automatic.  I have to deliver +200 sets of queries, whish tells that two values should be updated. When finished, it will be run by my Customer. First query; SELECT per_personal_id FROM dbo.personal WHERE […] Administration - SQL Server 2014 [SQL Server 2014 Question]( - So I have inherited an older SQL 2014 Server and have been working on understanding what I need to migrate or can shutdown without affecting our current applications. There is only one DB i know for a fact that relates to a digital faxing server we use I have a collection of 6 Databases with […] SQL Server 2019 - Administration [Drop database on Secondary Node from Primary for automatic restore job]( - hi all We've ventured into the world of AAG's as we have now moved across to SQL2019. We have a Primary node and two secondary nodes. We have a training database that is autoamtically created from the main production database. This was initially added to the AAG manually and syncs fine. What we are now […] [SQL Server 2019 HA]( - Looking for a HA solution for SQL server for our branch offices. Current setup has our 3 offices running independent standalone servers with SQL server. No redundancy in place. My aim is to centralise the servers in 2 locations. Location A and B has one server each. Both running windows server vm and sql in […] SQL Server 2019 - Development [Group by column over partition ordered by date]( - Hi Everyone. I need a script to get the first and second null value from some records grouped by a column and ordered by date. I need the 311888 and 123477 itinerary groups because they have the first and second LegSegment as null value ordered by CreationDate, something like: select * from Test group by […] [Combine tables with SQL in power bi]( - Hello everyone, i hope everyone is fantastic!! I've done some SQL queries in power bi to get my data and do some ETL. I have 6 companies, therefore, 6 queries. Now i would like to append them, combine(?) them in order to have just one sales table. What would you think it would be my […] [Removing UNION and getting the same result]( - Hi, I need to update my sql script and remove the UNION but get the same result - Current result from script using Union - DDL- For Accounts and bp_table1 /****** Object: Table [dbo].[bp_Account] Script Date: 04/07/2022 10:48:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[bp_Account]( [bp_id] […] [Employee Hierarchy Recursive CTE Different Top Level Directors Overtime]( - Hi, I have looked at some examples of Employee > Manager Hierarchy using Recursive CTE. The issue I have is the Top of Chain i.e. the Chairman there was different Chairman's over a period of time for example:  Final Results I require  If you look at EmpId 129 Rafael Nadal he left at the time Jeff […] SQL Azure - Administration [Deploying a standalone Azure VM running SQL Server into an availability zone]( - If i deploy a standalone Azure VM running SQL Server into an availavility zone; what will this achieve? Will i get two load balanced copies of the instance running from VM's out of two data centers in the zone with the data being synced? I think that is what the documentation is suggesting, but I […] Design Ideas and Questions [Multiple Fact Tables]( - Please assist? I would like to do a data model of the tables here: Any idea how i would go about to break this down and build a decent data model/links to tables?   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -