Stop SSRS Rendering on SQl

Sometimes occur that a specific SSRS subcription is in pending or blocked or using too time to create report. if you want to force the stop of subcrption you can do it using this two string of code directly on sql database of report server:

truncate table  [ReportServer].[dbo].[ActiveSubscriptions]
truncate table  [ReportServer].[dbo].[Notifications]
 
Done that your Subscription is stopped but his status is not refreshed and remain on pending. If you want to change Status you have to execute this script:
update ReportServer.dbo.Subscriptions
set LastStatus = ‘Done: 0 processed of 0 total; 0 errors (Stopped).’
where Description = ltrim(rtrim(SubcriptionName))
Annunci

Power BI desktop January News

Happy new year to everyone!

Here’s the complete list of January updates:

Reporting

  • Show and hide pages: you can now hide any number of pages you want through right clicking the page name and selecting hide.
  • Control data label background color for Cartesian and maps visuals:  You’ll find the option under the Data labels card in the formatting pane.
  • Increase area used for axis labels in charts:  This new slider can be used to increase the percentage of the chart used by the axis labels.
  • Bar/column padding control: added the ability to control the inner padding between bars. You can make the inner padding up to 50% of the bar’s width.
  • Show dates as a hierarchy (preview): created it automatically for you when you drag your data column into a visual.  You’ll need to turn on the preview feature switch under File > Options and Settings > Options > Preview features.
  • Add an anchor date for relative date slicer: Once you have an anchor date set, the slicer will be evaluated relative to that specific date permitting to start further back in time than today. . You can set it in the date range options in the formatting pane.
  • Top N selection in Q&A

Analytics

  • Correlation coefficient quick measure: calculates the Pearson correlation coefficient between two measures within a certain category

Custom Visuals

  • PowerApps: With the new PowerApps custom visual, you aren’t only able to quickly find insights in your data, you can take action on it as well.
  • TreeViz:you display your hierarchical data in a tree like structure. You can expand and collapse each level and decide how many children data points to show before grouping the smaller ones into a single child group.clip_image023
  • Funnel with Source by MAQ Software: The Funnel with Source custom visual is perfect to track any metric of interest over various stages along with the source of entry of the data point to the funnel.
  • clip_image025
  • Box and Whisker chart by MAQ Software:clip_image027
  • Agility Planning Matrix Light: Some benefits of this matrix visual is the ability to selectively drilldown on specific rows and to display currently applied filters on the visual itself.clip_image029
  • Image Grid: similar to a word cloud but with image:clip_image031
  • Gantt Chart by MAQ Software: This visual shows additional details of the task by providing a grid where one can view more data related to the task and can display the hierarchy of data category. Also, it can be sorted based on any data point of the task.

Data connectivity

  • Support for Azure Active Directory authentication for Azure SQL Database & Data Warehouse connectors: With this month’s update for Power BI Desktop, they are introducing a new “Microsoft Account” option within the Credentials dialog for the Azure SQL Database and Data Warehouse connectors, allowing users to authenticate using their AAD accounts.

Other

  • Advanced language settings for the Windows store app: You’ll see these new options under the Regional Settings tab in the Options dialog. Changing the application language will ignore the Windows default display language and switch to the chosen language. Changing the model language will decouple the model language from the application language for any newly created reports. (Note that this won’t affect the model language for existing reports.) Changing either of these settings requires a restart.

Determine free space for SQL Server

One of the functions of a DBA is to keep an eye on free space within the database and database files. The auto grow feature is great as a last resort, but proactively managing the database files is a better approach. Also, this information can be used to shrink data files as needed if there is a lot of free space in the files.

One option is to use sp_spaceused. If we run the following command we can see how much free space there is in the database, but this shows us total free space including the transaction log free space which may be totally misleading.

USE Test5 
GO 

sp_spaceused 

 

sp_spaceused output

Another option is to use the SpaceUsed property of the FILEPROPERTY function which will tell us how much space is used and then we can calculate free space based on the current size and what is being used.

USE Test5 
GO 

SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

Here we can see the output from the above for the Test5 database.

sql server fileproperty output

Power BI desktop march Resume

Hi everybody, today we speak about some interesting new features on power bi desktop. This upgrade is very interesting. IN particular this month:

Report view

  • Report theming preview: When you import a theme, all your charts will automatically update to use the theme colors, and you’ll have access to the theme colors from the color palette.You can upload a theme file using the Import Theme option under the Switch Theme button.clip_image004A theme file is a JSON file that includes all the colors you want us to use in your report. Se my Next article where i explain how to use it.
  • New matrix visual preview: new version of the matrix as a separate visual Once you turn on the preview by selecting New matrix visual under File > Options and settings > Options > Preview features, you will see the new matrix in the visualization pane. Benefits of this new matrix include:
    • Performance improvements
    • Drilling into hierarches
    • Stepped layout
    • Cross highlighting and filtering from matrix headers and cell contents
  • Textbox font color: added the ability to change font color of your text in textboxes
  • Numeric range slicer preview: adding a preview of a new type of slicer, the numeric range slicer. You can use this slicer type with any numeric column in your data model. You can choose to filter your data between two values, less than or equal to a value, or greater than or equal to a value
  • Percent of total on pie and donut chart data labels: added percent of total as another data label option for pie and donut charts
  • Cross-highlight using multiple series: you can now multi-select multiple series in a chart when cross-highlighting.

Analytics

  • Clustering is now generally available

Data connectivity

  • New Connector: Azure Analysis Services : adding a new connector to allow you to import data or connect live to your Analysis Services instances running on Azure.
  • Azure Data Lake Store connector is now generally available
  • DB2 connector: option to specify package collection . The DB2 connector dialog now exposes a new option to specify the package collection to connect to, within a given DB2 server.
  • Combine binaries: ability to specify sample file to use. exposing a new control in the file preview dialog to select one of the files available in the selected folder. Default selection is the first file found, but this can now be easily customized by you.

Query editing

  • Split column by delimiter: automatic detection of delimiter character