Creating Views - to Save Filtered Lists in NAV

Do you Know you can save the Page as View with filters.

Many of time we access to Page and apply same set of Filters to fetch our data. You can save the same for quick retrival of data applying the filters automatically in future access.

You save the Page with Filters as View.

Let us see small example how to achieve it.

First we will open the List Page in my case i am having one customized Customer List.



Suppose this is the Page which i use for Balances Retrival each month and on daily basis.

I Enter Filter as Balance > 5000 & Current Month Filter whenever i wish to check balances for my reporting or followup purpose.

For Date Filter i use (-CM..CM) sothat when i open this view next month it takes the appropriate Filter for that month.



Click on the Page Name post applying all the required Filters.

Select Save View as.

Give the Name for the View.

Select the Location to save the View.

On Confirmation respond as Yes.



Your View will be Saved, You can create as much Views as desired using different Filters.



Small and good Feature.

What you are waiting for give it a try.

I will comeup with more information in my upcoming posts, till then keep exploring and learning.

 

 

Working with Alerts in Power BI

Knowing about changes to important metrics quickly some time can help you taking quick decisions or help you keep eyes on things you care most.

With data driven alerts in Power BI, you can now get notifications when a metric you care about on your dashboard exceeds a set threshold.

Alert notifications will be sent to you as an email, and appear in your notification center on the web and in mobile apps.

You can set alerts for your numeric tiles featuring cards and gauges only. You have control over how often you want to be notified about your data, and whether you want to receive an email when your data goes beyond the limits you set.

Only you can see the alerts you set, even if you share your dashboard.

Alerts only work on data that is refreshed. When data refreshes, Power BI looks to see if an alert is set for that data. If the data has reached an alert threshold, an alert is triggered.

Alerts only work with numeric data types.

Alerts only work on data that is refreshed. They do not work on static data.

Today we will see how to work with Alert feature in Power BI.

Login to your Power BI using your credentials.

Switch to your Dashboard.

I have one ready to use dashboard from my previous post to show Alert feature I am using the same.

As discussed above it works only for Numeric & Gauges Tiles only, for others you will not get even the Alert option.

Luckily I have one on my Dashboard, if want to learn how to create Numeric Tiles or more details on working with Power BI you can refer to any of my previous posts on Power BI. However you can find here one direct Link to the topic in question.

You see I have selected my Numeric Tile in below screen shot.



How to Set Data Alerts:

From a dashboard tile, select the ellipses.

Select the bell icon  to add one or more alerts.



Click on Add Alert Rule.

To start, ensure the Active slider is set to ON, and give your alert a title.



 

Set your Condition, Threshold, and Notification Frequency also don’t forget to tick on send me mail too.

Click on Save to save your Alert.

 

Receiving alerts:

When the data being tracked reaches one of the thresholds you've set, several things will happen.

Power BI checks to see if time have lapsed or more than depending on the option you selected since the last alert was sent.

As long as the data is past the threshold, you'll get an alert every hour or every 24 hours depending on option you selected.

If you've set the alert to send you an email, you'll find something like as shown in below screen, this in your Inbox.

 



 

Power BI will add a message to your Notification center and adds a new alert icon to the applicable tile as shown in below screen.



 

Access to your Notification.



 

 

Manage alerts:

There are three ways to manage your alerts:

From the dashboard tile

From the Power BI Settings menu

On an individual tile in the Mobile App



 

As we have seen above how to create Alerts follow same steps to access Alert Window.

At the Left click on Expand make Necessary Modifications and save.

At the right select the trashcan to delete the Alert.

Select cancel to return to previous window.

 



From here you can turn alerts on and off, open the Manage alerts window to make changes, or delete the alert.

 

That’s all for today.

I will come up with more details in my upcoming posts.

Till then keep exploring and learning.

 

 

Jet Professional 2017 - Introduction and Installation

Why to switch to and What's New in Jet Professional 2017?

Its valid question same came to my mind also when i heard about new Release.

Then i tried to explore a bit about it and got an information about some exciting features introduced with it. I will discuss as i progress with my evaluation and exploration with these features.

Here i am giving bit overview about some features and limit to Installation topic in this post. If you wish to know more you will have to wait for my next few upcomming posts.

As per Jet Report Sources:

Jet Professional 2017 makes sharing your reports easier than ever before!  

Reporting with (and without) the Jet add-in for Excel

The Jet Web Portal is an online interface that provides an easy-to-manage repository for your organization’s reports.

Using the web interface and Office 365 Online, you can allow any of your network users – whether they have Excel and the Jet add-in installed or not – to view reports directly from their web browser.

Jet Mobile for Jet Enterprise

Jet Professional integrates with the Jet Mobile web client – allowing you to log into one site to see and run all your reports - and to see all of your business intelligence dashboards.

And... Jet Mobile for Jet Enterprise includes many new features - making your dashboards more powerful and even easier to use.

What's New?

Publishing to the Jet Web Portal

The Jet ribbon within Excel includes the ability to publish your reports to the Jet Web Portal.  The Jet Web Portal provides a manageable repository for your organization’s reports. All your users – whether they have Excel and Jet Professional installed or not – can run and view reports directly from their web browser.

What is Jet Web Portal

Jet Professional 2017 represents a new way to manage, run and view your business reports. Designed for today’s always-connected, always-moving workforce, Jet Professional 2016 introduces a new Information Management System that allows business users access to their business reports using virtually any device through a simple web interface.

As a user, you don’t need to install anything to run and view reports. Within the Jet Web Portal, you can quickly find the report that you’re after, specify report parameters, run the report to get up-to-the-minute data and view it in Excel Online.

With features like sharing, search, version control and report permissions, Jet Professional 2017 is a complete report management system.

Will comeup with more detailed about new features in my upcomming posts.

Let us move to Installation part.

To get the installation file go to this link : https://www.jetreports.com/support/product-downloads/

After downloading you will get the Jet Professional Installation Files.Zip, extract it.



Befor you start installation make sure no Excel instance is running on your system.

I am doing simple one system Instalation, will come up with more details and other options later.

Double click to Setup file to begin with installation.



If you have Activation code enter it or you can continue and activate later.



Select instalation type as desired, i am installing All Components.



Select the features you want to install, since i am performing complete install, so i am accepting all as suggested by default.



Select the Account to run the service, and don't forget to select Add rules to Windows Firewall. Rest all as default suggested.



Select the SQL Instance for Jet Service Database and login method.



Select desired ports and host detail or accept as suggested as default.



Enter Jet Service Tier details or accept suggested as default.

Now your pre-configuration part is completed. Click on Install to proceed with Jet Components Instalation.



Click on Install to proceed.



Click on Finish to exit Instalation wizard, your Jet is now installed with above provided configuration.



Next Step is to Activate your Jet Professional.

From Jet Tab on Ribbon select Activate Jet Professional.

choose the desired option.



Enter your activation code and click on Next.



Copy the message and send to the mentioned e-mail id and click Next.



Close to exit.

Wait for the Activation Token, it may take upto 24 hours to receive mail with this code.



If you check your Start Menu you will find these components got installed.



Once you receive your Tocken.

Launch Excel, From Jet Tab, Click on Help from ribbon and select Activate License.



Select Enter provided Activation token.



Copy the Token you received via mail here and click on Activate.



If every thing is fine you will receive the Activation Successful message.

Now you are ready to start with using Jet Professional.

I will come up with more details on this in my upcomming posts till then keep exploring and learning.

 

Working with Project "Madeira" Content Pack for Power BI

In today's post we will see how to use Power BI Content Pack for Madeira.

The content pack is preconfigured to work with sales data and financial data from the demonstration company that you get when you sign up for the Project "Madeira" preview.

To see your Project "Madeira" data in Power BI, you must have the following:

• Access to Project "Madeira".


• Access to Power BI.

Before we start with connecting we will collect few information that we will require while connecting the Power BI to fetch data from Madeira.

User Name

Web Service Acess Key

Note down both value, if you don't have Web Service Access Key Generate one and set appropriate Expiration date or Set Never Expire as shown below.



Next open the Web Service Page.

Make sure all selected Web Service is running as in below screen.

The data is extracted from your Project "Madeira" company using web services to read live data. In Project "Madeira", the Web Services window lists the web services that have been set up for you, including the following that are consumed by the content pack in Power BI:

• ItemSalesAndProfit


• ItemSalesByCustomer


• powerbifinance


• SalesDashboard


• SalesOpportunities


• SalesOrdersBySalesPerson


• TopCustomerOverview

Note: If you change the name of any of these web services, the data will not show up in Power BI.

Note your URL of OData. Edit it to follow the format one shown in below screen.



Now we have all our required information.

Open Power BI site and Get Data from Services as shown in below screen.



Select Project "Madeira" Content Pack and click on Get.



Enter the URL of Web Service we get in above step.



Select Next.

Select Authentication method as Basic.

In User Name enter your user name as obtained in above step.

For Password ented the Web Service Access Key as obtained in above step.



Choose Sign In.

After few minutes/seconds churning you will be ready with your first Dashboard from Madeira.

Troubleshooting

"Parameter validation failed, please make sure all parameters are valid"
If you see this error after you enter your Project “Madeira” URL, make sure the following requirements are satisfied:

• The URL follows exactly this pattern:
  
  https://mod291611.projectmadeira.com:7048/MS/OData/Company('CRONUS%20US')


• Delete any text after the company name in parenthesis


• Make sure there are no trailing forward slash at the end of the URL.


• Make sure that it is a secure connection as indicated by the URL starting with https.

"Login failed"
If you get a "login failed" error when you log in to the dashboard, using your Project “Madeira” credentials, then this can be caused by one of the following issues:

• The account you are using does not have permissions to read the Project “Madeira” data from your account.
  
  Verify your user account in Project "Madeira", and make sure that you have used the right web service access key as the password, and then try again.


• The Project "Madeira" instance that you are trying to connect to does not have a valid SSL certificate. In this case you'll see a more detailed error message ("unable to establish trusted SSL relationship").
  
  Note: Self-signed certificates are not supported.

"Oops"
If you see an "Oops" error dialog after you pass the authentication dialog, this is most frequently caused by a problem connecting to the data for the content pack.

• Verify that the URL follows the pattern that was specified earlier:
  
  https://mod291611.projectmadeira.com:7048/MS/OData/Company('CRONUS%20US')


• A common mistake is to specify the full URL for a specific web service:
  
  https://mod291611.projectmadeira.com:7048/MS/OData/Company('CRONUS%20US')/powerbifinance


• Or you might have forgotten to specify the company name:
  
  https://mod291611.projectmadeira.com:7048/MS/OData/

Thats all for today's post.

I will comeup with more details in my upcomming posts.

Til then keep exploring and learning.

 

Happy First Birthday to Power BI!

One year ago, on July 24, 2015, the new Power BI service was released, Power BI Desktop, and Power BI Mobile as an innovative cloud-based business intelligence solution.

At the time of the launch, Microsoft Corporate Vice President James Phillips wrote that “Power BI sets the standard for modern business intelligence”.

[youtube https://www.youtube.com/watch?v=An_8wf4RwBw]

You can read more on :- https://powerbi.microsoft.com/en-us/blog/happy-first-birthday-to-power-bi/

I have written several posts for beginers you can find here:

In case you have missed my previous posts here I present the link to all previous posts below.

Microsoft Power BI – Part – I

Introduction to Power BI and Creating Report from Excel Data, Local Files.

Microsoft Power BI – Part – II

Introduction to few Features of Power BI

Microsoft Power BI – Part – III

Power BI Desktop, Creating Dataset & Reports from In Premise Database installation

Microsoft Power BI – Part – IV

Power BI Gateway usage

Microsoft Power BI – Part – V

Scheduling Refresh of Dataset & Report created using In Premise Database

Microsoft Power BI – Part – VI

Power BI Content Pack

Microsoft Power BI – Part – VII

Power BI Mobile App

Microsoft Power BI – Part – VIII

Power BI Content Pack

Microsoft Power BI – Part – IX

Power BI Publisher for Excel

Microsoft Power BI – Part – X

ANALYZE IN EXCEL.

Search the Blog site with PowerBI tag and you will find more posts explaing about PowerBI features and capabilities.

I will comeup with more details in my upcoming posts.

Till then keep exploring & Learning.

 

 

Open Report Design in Visual Studio from Custom Report Layouts

You may have noticed that when we try to edit Report Layout from Custom Report Layouts Page, it always open in Word or Report Builder no direct option to edit it in Visual Studio.

This requirement leaded to the necessity of the below customization, It can be done other ways also but the easiest way to do it one which i am going to discuss in my today's post. Hope you will like it.



I have provided with the option where user can select the desired Editor either he wants to open it in Report Builder or Visual Studio.

To achieve this i have added one field in Table 9650 - Custom Report Layout with desired options as shown below.



And made this field available in Page Custom Report Layouts - 9650 as shown below.



Now in Codeunit 9652 - Edit RDLC Report Layout I have replaced the code shown in Yellow with the code shown in Red box.





Thats all, what you are waiting for go-ahead and design the reports in your way.

I will come up with more other topics in my upcomming posts, till then keep practicing and learning.

 

Setting different Column Layout for different users

On Community Forum i came across one requirement for opening Page with different column on Page for different users.

Here i have given an demo how can be done, but will require some tweaking to achieve the actual result.

I have copied the Customer List Page and removed extra fields.



I have added one Field above Repeater so that we can decide which format we want to see on screen. User can enter 1/2/or any other value.

I have created two more variables Set1 and Set2 to show/hide fields as per the value entered in SetSelect.

Assigned the Set1 & Set2 to few columns on the Page Fields to Property Visible.

Set1 to Responsibility Center, Location Code & Currency Code.

Set2 to Post Code, Country/Region Code, Phone No. & Fax No.

Added code on OnOpenPage Trigger to select Default page Format when Page is Opened. This code you will modify as per your requirement like check the User and assign the default Value.

Added Code to SetSelect-OnValidate Trigger to select the required format of Page. You need to modify the code as per your requirement.



In this demo if user enter value for SetSelect and you will get different view of same page.

Do some research and design one as per your requirement, this way we need not to create different Pages for different Users. Same Page can serve you different purpose if limited to only different views.

I will come with more information in my upcomming posts.

Till then keep browsing and learning.

 

SQL Server Database Backup using PowerShell

In this post we will see how we can take backup of databases from SQL Server using PowerShell and schedule it as a daily run Plan.

Step-1 : We will create Powershell Script to take backup of Databases in SQL Server.



Here is the full Script for your ready refrence.

param( $serverName, $backupDirectory )

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName

$dbs = $server.Databases

foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })

{

$dbName = $database.Name

$timestamp = Get-Date -format yyyy-MM-dd-HHmmss

$targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak"

$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")

$smoBackup.Action = "Database"

$smoBackup.BackupSetDescription = "Full Backup of " + $dbName

$smoBackup.BackupSetName = $dbName + " Backup"

$smoBackup.Database = $dbName

$smoBackup.MediaDescription = "Disk"

$smoBackup.Devices.AddDevice($targetPath, "File")

$smoBackup.SqlBackup($server)

"backed up $dbName ($serverName) to $targetPath"

}

Save this Script file as ps1 extension.

You can create the script using even notepad.

 

Step 2: We will Create Batch file to call PowerShell scrip and to be used in Windows scheduler.



Save as .BAT file. Here is the batch script for ready refrence:

powershell -ExecutionPolicy RemoteSigned

-File "C:\User Data\SQL Backup\Tools\SQLServerBackupAllDatabase.ps1"

-serverName "INDEL-AXT5283NB"

-backupDirectory "C:\User Data\SQL Backup"

>> "C:\User Data\SQL Backup\LOG\\%date%.log"

Step 3 : Create a Windows Scheduler

Open Windows Task Scheduler.

Create New Task as shown below :



Enter Name & Description on General Tab as shown below:



On Trigger Tab create New Trigger and enter details as shown below :


On Action Tab Create Action and enter information as shown below : Here Select the batch file created in Step 2.



In Settings Tab do the setting as shown Below :



Click on OK to Save the Task and return to Task Scheduler Window.

Here you can see the newly created Task.



When Task is executed you will find the backup of databases at defined path in the script.



You can also find Log file at the path defined in batch.



Thats all for this post, will come up will more information in my up comming posts.