Tuesday, July 21, 2015

SSRS – Chart based Drill-down Report

A picture is worth a thousand words – especially when business is trying to find relationships and understand in their data, which could include thousands or even millions of variables and representation of data visualization through the chart reports become very urgent to take the right action for the business. A chart based drill down report is similar to row drill down report but main difference is that it’s based on the category groups and will provide the additional details of that particular category. 

One of the biggest challenges for business users is deciding which visual should be used to best represent the information. Better understanding of the business requirements, chart reports play a very important role to create dynamic Dashboard reports. 
For example; sales admin wants to see the performance of their products in the different regions such as France, England and USA etc. and he is also interested to see the performance of the products in the states of any specific country.  

To meet the requirement, Chart Graph reports are the best option to represent the sales data to state or cities level. We are going to assume that we have Sales Summary data table in our database having the following information –

StateCode
StateName
CountryCode
CountryName
SalesYear
TotalSales
TotalTax
BY
Bayern
DE
Germany
2008
1981811.89
172758.34
HE
Hessen
DE
Germany
2008
2981811.89
272758.34
HH
Hamburg
DE
Germany
2008
2581811.89
342758.34
FR
France
FR
France
2008
3298789.76
306620.72
1
Ain
FR
France
2008
4298789.76
346620.72
2
Aisne
FR
France
2008
1298789.76
106620.72
3
Allier
FR
France
2008
1598789.76
166620.72
ENG
England
GB
United Kingdom
2008
2487391.50
220058.34
AL
Alabama
US
United States
2008
1181754.12
113381.69
AR
Arkansas
US
United States
2008
1323769.09
128255.76
DE
Delaware
US
United States
2008
1059022.59
101832.09
VI
Virgin Islands
VI
Virgin Islands, U.S.
2008
1181754.12
113381.69


We need to display the above information in our SSRS report and Report header should be like as “Regional - Sales Summary Report”. Whenever user clicks on the country category column then it will drill the chart graph and show the state sales summary of that particular country category as shown below:


After click on the country France column then Report will show the following states sales information in the France country- 


We will apply the following things to the graph report–
  • Add country parameter to the report and set ALL as default value for the graph report means to display all countries on the graph. This value will be changed automatically whenever drill down apply to the report.
  • We need to set the dynamic X- axis title for various drill-down based on the report parameter.
  • We need to set the axis title dynamically to set countries name and states name based on the report parameter.
  • The same report will call again whenever drill request will send to server.  

To create Graph Drill Down report, we will follow the following steps as shown below:
1) In our existing report server project, we will add a new report as “Regional - Sales Summary Report” as shown below:



2) Now, we need to create a dataset for the report as shown below:



3) Add Dataset will launch Dataset properties window. We need to set the name as SalesSummary for the Dataset and choose a dataset embedded in my report and choose Query Type Text as shown below:
1    
SELECT [StateProvinceCode]
      ,[StateProvinceName]
      ,[CountryRegionCode]
      ,[CountryRegionName]
      ,[SalesYear]
      ,[TotalSales]
      ,[TotalTax]
FROM [dbo].[SalesSummary]
Where (@Country = 'ALL' OR [CountryRegionCode]=@Country )
  

  


4) After click on the OK button, a dataset has been added in the report data with parameters also as shown below:


5) Click on the Country parameter and set the default value “ALL” as shown below:



6) Now right click on the report, click on Chart from Insert as shown below:



7) From Chart Type window, choose 3-D Clustered Column Chart and click OK button as shown below:
 

8) Now change the Chart Title as “Regional – Sales Summary” and set the dataset to the column chart in the Chart properties window as shown below:



9) Now, we need to set the Tax sales amount and Total sales amount to the column chart. For this, click on the chart legend as shown below:



10) We need to set values in chart data which are work for Y-axis data representation. Choose Total Tax and Totals Sales as shown below: 



11) To set the values in the X-axis, we need to fill the values in Chart Data in Category groups as shown below:



12) We need to set the dynamic X-axis category labels. Right click in Chart Data’s Category Groups as shown below:



13) In Category Group Properties window, In the label, we need to set the conditional expression based on the report parameter. 
  
=IIF(Parameters!Country.Value="ALL",
Fields!CountryRegionName.Value, Fields!StateProvinceName.Value)

  





14) Now click on OK button and view the preview of the report as shown below-



15) Now, we need to set the dynamic X - Axis Title as shown below which is also based on the country report parameter:


16) In the Axis Title Properties, we need to set the Title Text such as following condition as shown below: 

=IIF(Parameters!Country.Value="ALL", "Sales in countries"
, "Sales in "+Fields!CountryRegionName.Value + "'s cities")
+ " in year " + Convert.ToString(Fields!SalesYear.Value)




17) Click on the OK button and need to set the Y-axis title also as shown below-
  


18) Now click on OK button and view the preview of the report as shown below-



19) In the chart report, we need to set the column data labels now. For this, right click on the columns and click on the Show Data Labels as shown below: (do this for both columns)
  




20) Now, we need to set the drill down option on the column. For this, right click on the column (do this for both columns) and click on the Series Properties as shown below:



21) In the Series Properties window, we need to do the following things-
a. Choose the page Action,
b. Select Go to Report radio button,
c. Specify the current report name,
d. Add parameter country and fill the value as [CountryRegionCode] from the values list 
as shown below:


22) Click on the OK button and preview the report as shown below - 



After click on the country France column then Report will show the following states sales information in the France country- 



This completes the tutorial section of the simple chart based drill report.

Other Drill Reports in SSRS
1) To learn the Row Drill Down Report, Click here.
2) To learn the Row Drill Through Report, Click here.
3) To learn the Column based Drill Down Report, Click here.

How can you create your first sub report. Click here.

53 comments:

  1. Top of the RDBMS... we can but Top of the Cube, we have generated chart report and whenever click the chart will display the drill down report.... Is this possible top of the CUBE...

    ReplyDelete
  2. What is being passed to the Stored Procedure are the argument values of the parameters. This said if you use a linked server to your OLAP server and an OpenQuery, you can use a generalized MDX query to extract the necessary data AND then filter the data via a standard T-sql predicate.
    Select ..... from OpenQuery("myLinkedServer", 'MDX query goes here') a
    where field1 = argument1 and field2 = argument2
    Do have a look at some of the BI articles on SQLShack dot com

    ReplyDelete
  3. These are probably the most common types of everyday use cordless drills you will find on the market today. The big advantage with the low rpm/high torque setting is that now you can drive screws and lag bolts at the low rpm needed without having to try to hold the trigger in a certain position. cordless screwdriver

    ReplyDelete
  4. Thanks for this very useful Information. Drill-drown reports are one way of linking two or more reports, so that one report shows summary information and a second report is linked from it to show finer grained detail.
    SSRS Tutorial

    ReplyDelete
  5. There are even more variations and innovations in the works as I write this. While the scope of this guide will not be able to cover every type of power drill in existence, we are going to try to cover most of the bases.https://simplehandyman.co.uk/best-table-saw-uk-reviews/

    ReplyDelete
  6. It's really a nice experience to read your post. Thank you for sharing this useful information.
    Data Modeling Training

    Mule ESB Training

    ReplyDelete
  7. The most ideal approach to limit the sort of drill you ought to get is to make sense of how you plan to utilize your drill.Best cordless drills

    ReplyDelete
  8. you are able to take the help of experts making a call at QuickBooks Payroll Support Phone Number Well! If you’re not in a position to customize employee payroll in.

    ReplyDelete
  9. You are always able to relate with us at our QuickBooks Support contact number to extract the very best support services from our highly dedicated and supportive QuickBooks Customer Support Number executives at any point of the time as most of us is oftentimes prepared to work with you.

    ReplyDelete
  10. Regardless of most of the well-known QuickBooks Premier features you may find difficulty at some steps. QuickBooks Customer Support Number is the foremost destination to call in the period of such crisis.

    ReplyDelete
  11. QuickBooks Enterprise Tech Support Number will be the first point of contact to report the matter where you get your entire worries taken care off and assured solutions straight away offers you complete peace of mind.

    ReplyDelete
  12. If this doesn’t help you, go on and connect with us at QuickBooks Support Phone Number. A lot of us works 24*7 and serve its customers with excellent service each time they contact us.

    ReplyDelete
  13. For such type of information, be always in contact with us through our blogs. To find the reliable supply of help to create customer checklist in QB desktop, QuickBooks online and intuit online payroll? Our Quickbooks Support Number might help you better.

    ReplyDelete
  14. You merely need certainly to avail the assistance through the technical experts by dialing the Quickbooks Support Phone Number. You could have a word of discussion together with them sharing your entire doubts, and getting the essential productive solutions.

    ReplyDelete
  15. Your tech support team team is just an individual tap away, dial our QuickBooks Support Phone Number and experience our best hassle-free tech support team.

    ReplyDelete
  16. In conclusion, don’t hesitate to call us on our QuickBooks Support Phone Number. We have been surely here for you personally. In conclusion, any error, any problem, any bug or whatever else pertaining to QuickBooks related problem, just call our QuickBooks Tech Support Number. Surely, call our QuickBooks Support contact number.

    ReplyDelete
  17. Quickbooks Support Phone Number service number accords assistance to the QuickBooks users’ worldwide. The support team can be reached through various modes such as: phone support, email support, live chat, FAQ, QuickBooks community etc. Solving the Quickbooks related problems and issue Remotely . QuickBooks in current time is Number #1 accounting software popular in the USA , Canada , Europe and Australian market for business management.

    ReplyDelete
  18. When it's found and retrieved, get rid of the program such that QuickBooks Customer Support Number will not cause any longer inconvenience.If all these method still does not provide you with results, make an attempt to improve the name associated with download < nn> folder to downloadqb< nn >.old. That will have the specified effect.

    ReplyDelete
  19. QuickBooks encounter an amount of undesirable and annoying errors which keep persisting as time passes if you don't resolved instantly. Considered one of such QuickBooks issue is Printer issue which mainly arises as a result of a number of hardware and software problems in QuickBooks, printer or drivers. You can actually resolve this error by using the below troubleshooting steps you can simply contact our Support For QuickBooks available at.

    ReplyDelete
  20. You're able to resolve this error by using the below troubleshooting steps you may also simply contact our QuickBooks Support Phone Number available at.You should run QuickBooks Phone Support Number print and pdf repair tool to determine and fix the errors in printer settings prior to starting the troubleshooting.

    ReplyDelete
  21. Different styles of queries or QuickBooks Payroll Tech Support Number related issue, then you're way in the right direction. You simply give single ring at our toll-free intuit QuickBooks Payroll support phone number .

    ReplyDelete
  22. The process to install and set up QuickBooks Support Phone Number on Windows is same as that of Mac. The system requirements for the installation process may vary slightly for both the operating system. You just need to take care of that and just ensure that you check and verify them first, before starting with the procedure. Getting instant and effective help for any matter of concern is what the user’s desire for. With QuickBooks, you can rest assured about getting the most desirable and efficacious help on every issue that you might encounter yourself with. You just need to avail the help from the technical experts by dialing the QuickBooks Support. You can have a word of discussion with them sharing all your doubts, and getting the most productive solutions.

    ReplyDelete
  23. Repairing does not resolve the matter. The QuickBooks Error 3371 usually occurs when a person carries out a system restore or relocates the program files to a new PC or disk drive. The reason for the error code is said to be the Entitlement file.

    ReplyDelete
  24. It’s another fabulous feature of QuickBooks Payroll Support Phone Number service, it really is a site where your entire valuable employees will get the data of your own paychecks. It saves even more time consumed by doing printing and mailing paystubs each day or replacing lost or damaged paystubs.

    ReplyDelete
  25. QuickBooks Enterprise Support Phone Number has almost eliminated the typical accounting process. Along with a wide range of tools and automations, it provides a wide range of industry verticals with specialized reporting formats and tools.

    ReplyDelete
  26. Quickbooks Payroll Support could be the toll-free quantity of where our skilled, experienced and responsible team are available 24*7 at your service. There are a selection of errors that pop up in QuickBooks Payroll which are taken care of by our highly knowledgeable and dedicated customer support executives.

    ReplyDelete
  27. Well! The QuickBooks Payroll Support Phone Number world is extremely crucial and important as well. The person who has deficiencies in knowledge find it hard to have fun with all options. You can either perform payment processing in desktop or cloud, both ways are a little different but provide the same results.

    ReplyDelete
  28. We suggest anyone to join our services just giving ring at toll-free QuickBooks Enterprise Support Number to enable you to fix registration, installation, import expert and plenty of other related issues to the enterprise version.

    ReplyDelete
  29. Any errors related to QuickBooks software may damage your data you could fix them through QuickBooks Tech Support Number and QuickBooks 24/7 Support will always be there to greatly help. We solve, manage, and help one to cope with any technical problems and guarantees that this program works well.

    ReplyDelete
  30. QuickBooks accounting software is integrated with different products like QuickBooks Enterprise(developed by Intuit) and tools like QuickBooks file doctor. And it offers a number of incredible features (Business plans, cash flow projections, and advanced inventory ) that make it unique from other Quickbooks product. It is really best suited for small to medium businesses though it is incredibly expensive. No doubt, it has good compatibility with almost every version of Windows OS, Mac OS, iOS, and Android. Yet you may require an efficient QuickBooks Enterprise Support Number to handle payroll management, account management, inventory, and other accounting activities. AccountWizy provides you an ideal and efficient QuickBooks Enterprise Customer service to make it all possible.

    ReplyDelete
  31. Intuit offers many official help pages. It has a different contact us page for different countries like United States, Canada, United Kingdom. It is the best place to go for help, support and advice about using QuickBooks products.You can further read the contact details about how to Contact QuickBooks Phone Number. QuickBooks technical support team are active for only 5 days (Mon-Fri) in a week. The QuickBooks Tech Support Number is accessible these days.

    ReplyDelete
  32. The retail businesses always consume a lot of time. Retail sellers have to sell their goods to achieve maximum profit. QuickBooks Enterprise can easily manage your retail business by availing its silent features. This software is helpful to manage your data, pricing, reports in the most efficient manner.With the help of advanced inventory feature, you can manage the inventory easily. You can easily handle the data with this software like vendor lists, items and customers. This software is helpful in adding thousands of price rules. for any support QuickBooks Enterprise Support Phone Number

    ReplyDelete
  33. QuickBooks Premier Support Phone Number advisors are certified Pro-advisors’ and has forte in furnishing any kind of technical issues for QuickBooks. These are typically expert and certified technicians of their domains like QuickBooks accounting,QuickBooks Payroll, Point of Sales, QuickBooks Merchant Services and Inventory issues to provide 24/7 service to our esteemed customers.

    ReplyDelete
  34. Welcome aboard, to your support site par excellence where your entire worries linked to the functioning of QuickBooks Enterprise is supposed to be addressed by our world-class team of QuickBooks Enterprise Tech Support Phone Number within the blink of an eye. If you should be experiencing any hiccups in running the Enterprise version of the QuickBooks software for your needs, it is best to not waste another second in looking for a solution for the problems.

    ReplyDelete
  35. QuickBooks Tech Support Phone Number is accounting software, which is a cloud-based application produced by Inuit Inc. In fact, the application has been developed with the intention of keeping a safe record of financial needs for the business. Additionally, it is a user-friendly accounting software; an easy task to maintain; assisting the company in keeping the records of financial transactions, and a whole lot more features.

    ReplyDelete
  36. The QuickBooks Support Number is toll-free and also the professional technicians handling your support call will come up with a sudden solution that may permanently solve the glitches. The Intuit customer care can take in almost any kind of queries pertaining to installing a certain type of QuickBooks software, to learn about the newly available subscription plans, backing up data, fixing errors with a saved QOB file and much more.

    ReplyDelete
  37. The program has made the accounting easier for everyone and has now helped them in managing and organizing business smartly and effectively. However, after having numerous of positive point, this program is inclined towards errors and bugs. If you're also hunting for the solutions linked to the accounting software, we recommend one to contact QuickBooks Tech Support Phone Number.

    ReplyDelete
  38. VAT for a transaction made through QuickBooks will probably be instantly calculated and reflected upon your QuickBooks account, yet, if at any complication arises with calculating the VAT taxes; go ahead and reach the to instantly solve the issues. QuickBooks Tech Support can be acquired 24/7 to produce much-needed integration related support.

    ReplyDelete

  39. The QuickBooks Tech Support is available 24/7 to provide much-needed integration related support and to promptly make use of QuickBooks Premier with other Microsoft Office software packages.

    ReplyDelete
  40. Quickbooks Support Telephone Number
    QuickBooks has completely transformed the way people used to operate their business earlier. To get familiar with it, you should welcome this positive change.Supervisors at QuickBooks Technical Support Number have trained all of their executives to combat the issues in this software. Utilizing the introduction of modern tools and approaches to QuickBooks, you can test new techniques to carry out various business activities. Basically, this has automated several tasks that have been being done manually for a long time. There are lots of versions of QuickBooks and each one has a unique features.

    ReplyDelete
  41. Hey! Your blog is really powerful and upto the mark. It provides great exposure of the concerned topic. If you are facing difficulty in using QuickBooks as your primary accounting software, call on QuickBooks Error Support Phone Number +1-888-238-7409. They have highly experienced support team members who provides step-by-step solutions for all your errors. Visit us: - https://www.error.enetquickbookenterprise.com/

    ReplyDelete
  42. I appreciate the profitable data you offer in your blog. A debt of gratitude is in order for posting it..Quickbooks Technical Support

    ReplyDelete
  43. Hi! Amazing work. Your post is very convincing and informative. I really like your work. keep up the good and effective work. You just nailed it, my friend. QuickBooks is acclaimed accounting software that has flourished many business firms. By installing QuickBooks software, you can save your time, resources and money. You can even get immediate help and support for QuickBooks errors at QuickBooks Tech Support Phone Number 1-855-236-7529.
    Read more: https://binged.it/2L6AXds

    ReplyDelete
  44. Wow! This is very nice and informative. This type of blogs is rarely found on internet these days. I will suggest you to write more and more. In case you are a QuickBooks software user and looking for technical assistance in the software then call now at QuickBooks Helpline Number +1-844-200-2627. These guys are very talented and well-versed with the software and its setbacks. You can avail their remote tech support services at comfort pf your home as they are available 24x7 for you.
    Read More: https://www.enetservepartners.com/quickbooks-helpline-number/

    ReplyDelete
  45. Hey! What a great post. Your post is so chock full of innovative and informative details. Have you heard about QuickBooks software? It is one of the leading accounting software in the current time. This software offers various lucrative features. Plus, the team at QuickBooks Support Phone Number 1-833-441-8848 is placed to help you round the clock.

    ReplyDelete
  46. Wow! What an amazing blog. I like your blogs, as they have a great balance between technical and non-technical content. Your business has a great deal with the point of sale customer interaction. To upgrade your accounting process, use QuickBooks POS as your accounting software. It provides great feedback for the customers and can help you grow your business. You can get its support on Contact Number for QuickBooks Support 1-833-441-8848.

    ReplyDelete
  47. I would like to update you regarding the reliable technical services available at QuickBooks Support Phone Number +1 833-228-2822. You might contact them 24/7 to get technical aid. The panel of experienced members goes through the problem and brings out simplified possibilities almost instantly.
    Read More: QuickBooks Error 80070057

    ReplyDelete
  48. QuickBooks has lots of features which can fulfil business accounting need. To get solutions for the errors, contact us on our QuickBooks Support Phone Number +1 800- 329-0391. We use the latest technology for solving your errors. Our service is available for you 24X7 with certified ProAdvisor. We offer service at the lowest price possible, with 100% working and effective solutions. Our support also has data protection and privacy policy. Visit us- https://www.coldesk.com

    ReplyDelete

  49. This is an amazing post, with such authentic information and the crispy writing your post has won my heart. QuickBooks is world-class accounting software and also is host to several glitches and snags. However, to get it fixed instantly and perfectly, contact QuickBooks Helpline Number 1-888-238-7409.and the experts are ready to answer all your queries and solve your entire problem. Visit us:-https://www.qbsoln.com/

    ReplyDelete
  50. Nice Blog ! For getting more information and fixing your QuickBooks issues, dial our QuickBooks Toll Free Phone Number 1-800-986-4607 for QuickBooks error.
    View on Map: https://tinyurl.com/ut8xnsx

    ReplyDelete
  51. Good! The way you explained it is quite impressive. In contrast to your post, I would like to talk about QuickBooks, which is an accounting program that helps you to handle your accounting activities quickly. But, over a time you would need some support as several small problems may arise. That time just dial the QuickBooks Support Phone Number +1 833-441-8848 and enjoy the best possible support.

    ReplyDelete
  52. Great! Continue posting these stuffs. It's fun. I l would also like sharing a thought on QuickBooks–a user-friendly accounting program used by practitioners around the world. While you may have small glitches, you shouldn't panic, since QuickBooks Support Phone Number +1 833-441-8848 is accessible24x7 for your assistance. Ring instantly! And connect with the best executive to fix your issue.

    ReplyDelete
  53. Great! I must say this blog post is awesome. Besides, I really like the use of QuickBooks, an exemplary accounting program that functions in depth and simplicity for the administration of accounting. Moreover, you can dial QuickBooks Desktop Support Phone Number +1 833-441-8848 if you encounter any problem. Call us anytime as we operate round the clock.

    ReplyDelete

Popular Posts

Get Sponsored by Big Brands