Tag: sql server

Subreports in RDLC: A Comprehensive Guide

RDLC reports are a powerful tool for data visualization, but sometimes, a single report isn’t enough to capture all the information you need. This is where subreports come in, allowing you to nest additional reports within your main report for deeper exploration. Let’s dive into the world of subreports in RDLC and see how you can leverage their power to create detailed and informative reports.

Getting Started:

  1. Open the Report Server: Navigate to your system’s Reports virtual directory in your browser (e.g., http://mysystem/Reports).
  2. Create a Shared Data Source: Click “+New” and select “DataSource” to create a data source accessible by all reports.
  3. Build Your Report: Choose “New Report” and open Report Builder (download it if necessary).
  4. Define Parameters (Optional): If your report uses stored procedures, create parameters with names matching the SP’s parameters.
  5. Connect to the Data: Select the shared data source you created and define your dataset based on it.
  6. Bind Parameters (If Used): In the dataset properties, link the report parameters to the corresponding query parameter values.
  7. Craft Your Report: Add elements to the page header, body, and footer sections using fields from your dataset.
  8. Preview and Save: Provide parameter values for testing and save the report.

Adding the Subreport:

  1. Create a New Report: In the Report Server web interface, create a new report and open it in Report Builder.
  2. Define Parameters: As before, add any necessary parameters.
  3. Embed the Subreport: Right-click the report body and select “Subreport.”
  4. Choose the Subreport: Select the previously saved report you want to embed.
  5. Connect the Dots: In the subreport’s parameter section, link its parameters to the corresponding parameters of the main report.
  6. Save and View: Save the report and enjoy your nested masterpiece in the SSRS URL!

Bonus Tips:

  • Use paging in subreports to manage large data sets.
  • Leverage Tablix data regions for horizontal scrolling within subreports.
  • Explore third-party reporting libraries for advanced subreport features.
  • Consider alternatives like drill-down reports or interactive charts for specific needs.

With this guide and a little practice, you’ll be adding subreports to your RDLC reports like a pro, enriching your data visualizations and providing deeper insights for your audience. Remember, the key is to understand the data flow and connect the dots between your main report and its subreports. So, grab your data, open Report Builder, and start subreporting!

disclaimer : I have provided some 20 bullet points to bard.google.com and this was the output.

Query to Find Table used in Stored Procedures

SELECT DISTINCT objects.name, objects.type,
comments.text proc_defintion
FROM syscomments comments
INNER JOIN sys.objects objects
ON comments.id=objects.object_id
WHERE comments.text LIKE ‘%tbl_Batch_Recipe_Ingredient_Record%’
AND objects.type=’P’

multiple references,

Find stored procedure related to table in database in SQL Server

https://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure/

SSMS Database Expanding is very slow (Resolved)

I have been facing this issue for quite some time, but not giving it enough attention although it is annoying, today i decided to find the reason, previously i tried some measures as usual with the help of search engines with no remedy, today once again i retried, seems to be one of my better days, hit on this particular link https://social.msdn.microsoft.com/Forums/sqlserver/en-US/99bbcb47-d4b5-4ec0-9e91-b1a23a655844/ssms-2012-extremely-slow-expanding-databases it states that some database may have been auto-close on or auto-shrink on, and suggested to turn them off both if we don’t have any particular reason to keep it auto-close on. Using this little script

SELECT name, is_auto_close_on, is_auto_shrink_on 
FROM master.sys.databases AS dtb 
WHERE is_auto_close_on = 1 OR is_auto_shrink_on = 1
ORDER BY name

seems to be almost 5 to 6 db are set in auto-close on state. you can either update via script or right click the db properties find options on left hand side and on the right side you can see the property with dropdown values set to false and restart the ssms. No more slow expansion of db.

another useful link on this topic https://blog.sqlauthority.com/2016/09/22/sql-server-set-auto_close-database-option-off-better-performance/