DAX Query CALENDARAUTO()
In Power BI, you can use the pre-defined function CALENDARAUTO() to create date table with a column called “Date”. It will generate a date for current 1 year by default.
This above function will only work when you have a model with at least one column with date or datetime data type.
In the below screenshot, I tried to use CALENDARAUTO() function in an empty model.
You can see the warning message above.
“CALENDARAUTO() function can not find a base column of DateTime type in the model”
In the same scenario, I just added a simple table with one date data type column then I used the CALENDARAUTO() in a new table and this time I got the expected result.
Happy Learning. Share your comments.
SSRS Mobile Report URL
This article explains you the possibilities of passing the parameters in mobile report URL.
When we run the mobile report in report manager, usually it will open a URL with folder and report name as like below,
https:////
We have many scenarios where we need to build the shared dataset with one or multiple parameters. To filter the data either we can add the selection list in mobile report and pass the selected item value to shared dataset or we can control through mobile report URL
Scenario 1
Single Parameter
We can pass the parameter to the above URL followed with “?”. The syntax will be like below,
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername>=value
Scenario 2
Multiple Parameters
If you have multiple parameters in your dataset then you need to add “&” symbol followed by your first parameter.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value&<datasetname>.@<parametername2>=value
Scenario 3
Single Parameter with Multiple values -Repeated
To pass multiple values to single parameter then we need to repeat the same parameter with different values.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value1&><datasetname>.@<parametername1>=value2
Scenario 4
Multiple Parameter with Multiple values -Repeated
To pass multiple values to multiple parameters then we need to repeat the parameters with different values.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value1&<datasetname>.@<parametername1>=value2&<datasetname>.@<parametername2>=value1&<datasetname>.@<parametername2>=value2
Scenario 5
Parameter with Multiple values with Comma separated
We can pass multiple values with just comma separated. It eliminates the scenario of repeating the parameter with different values.
To get this solution, need to set up the shared dataset to support multiple values.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value1,value2,value3<datasetname>.@<parametername2>=value1,value2,value3
Scenario 6
Stored Procedure with custom option
The reason for using the stored procedure is to pass “ALL” values instead of trying all values with comma separated.
Limitations,
- Can pass ALL to all the parameters
- Other than ALL, only single value is supported to other parameters
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=ALL<datasetname>.@<parametername2>=value1
SSRS 2016 Branding
Microsoft SQL Server 2016 Reporting Services has multiple new features. One of the features is Branding. It enables us to customise the Report Manager Portal with our preferred colours, logo and custom colours for mobile reports, etc.
You need two files to make your own branding.
1. colors.json
2. metadata.xml
The template for the above files are available in online. You can download here and start to customise it.
There is a possibility that you can get the below error.
Actually, this error is self-explanatory and the solution is very simple.
Need to zip the above 2 files and upload that in Report manager branding section.
The reason for Error:
If you are stored the files in a folder and zip the whole folder and try to upload in branding then you will get the above error message.
Solution:
Select those files and right click and choose to send to ->Compressed. Now you can upload the file.
Latest Update on SQL Server Management Studio
This simple post highlights the top level updates on components in SQL Server Management Studio.
The latest release of SQL Server Management Studio v17 RC3 has updates on below section compare than the earlier version.
- Microsoft SQL Server Management Studio
- Microsoft Analysis Services Client Tools
DrillThrough in SSRS Mobile Reports
The drillthrough option is more familiar to us as it is one of the options in SSRS and we use this option in almost all reporting projects.
We have a same drillthrough option available in the latest release of SSRS 2016 Mobile reports. The mechanism of the option is same for both the cases (RDL Report & Mobile Report).
We need two reports. We can pass the value from one report to another to filter the data in the second report.
This article explains that how we can configure drillthrough in Mobile report.
Pre-Requisites:
- Mobile Report Publisher
- Mobile report with parameterized shared dataset
Considering above pre-requisites are fulfilled.
Follow the below steps to configure DrillThrough
Step 1: Open a mobile report publisher
Step 2: Drag and drop the report elements and add the data for those elements
Step 3: Choose the report element where you need to apply the drill through.
Step 4: Click “Drillthrough target”. You can see two options, 1. Mobile report 2.Custom URL
Step 5: Choose Mobile report option. It will show all your mobile reports in report manager
Step 6: Choose the report which has the parameter on it.
Step 7: Choose the field of the first report as an input parameter for the second report.
Step 8: Save the report and open in report manager.
Step 9: Click the value from the drillthrough column. It will redirect to the second report with filtered value.
To remove or edit the drill through, click the same Drillthrough target.
Additional Information:
There is no drillthrough option available for below report elements
- Pie Chart
- Funnel Chart
- Tree Map
SSRS Mobile Report with Cascading Parameter
Configuring a cascading parameter for SSRS report is easy. We used to play with multiple datasets. To make it simple, if we have two parameters in a report, the second parameter values are based on first parameter selected value.
Example,
I have following two parameters in a report.
- Country
- City
Country Parameter will show all the available country and city parameter will show all the cities irrespective of country. In a business scenario, this needs to be fixed as users will expect to see only cities relevant to the countries.
If user Selects India, then the city should show only the cities from India.
Check out my below post for cascading parameter in SSRS report.
The same logic can be applied in SSRS mobile report but with different procedure.
Pre-Requisites:
- Shared Dataset
- Mobile Report Publisher
In this scenario, I am taking AdventureWorksDW database for better understanding.
Follow the below steps,
Step 1: Create a shared Dataset and deploy to report manager.
Step 2: Open Mobile report publisher and add the shared dataset. In this case dataset from AdventureWorksDW database.
Step 3: Drag and Drop two selection list. You can maximise or minimise to show only one value at a time as like below.
Step 4: Go to Data and Add data. Here I added a shared dataset which contains Product Category Name and Product Subcategory Name fields.
Step 5: Rename the Selection list 1 to Category and selection list 2 to Sub Category
Step 6: Choose our dataset for both the selection lists. Make sure to choose Category Key and label for selection list 1 and Subcategory Key and Label for selection list 2.
Step 7: Add the checkbox on “Filter these dataset when selection is made”. Choose the Product Category.
Step 8: Click the options on subcategory data, choose Category check box under “Filtered by”
Step 9: By default “Allow select all” option is ON, now off for both the selection list.
Step 10: Preview the report. You can check the sub category shows only Bike information. If we choose some other value in category then it will show respective sub category results.
SQL Server Drop Database User
This blog post explains the issue that we usually face while dropping the users from the database and how can we resolve the issue.
We can create a user for database easily by any of the following user types,
- SQL User with login
- SQL User without login
- User mapped to a certificate
- User mapped to a asymmetric key
- Windows User
If we want to drop the user then we can write drop statement but it will throw a below error in case the user owned any of the schemas.
Msg 15138, Level 16, State 1, Line 20
The database principal owns a schema in the database, and cannot be dropped.
To resolve the issue, Follow the below steps.
Step 1: Check the schema that owned by the user.
SELECT *
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘Username’)
Step 2: Change the schema back to dbo.
ALTER AUTHORIZATION ON SCHEMA::<schemaname> TO dbo;
Step 3: Drop the user
DROP USER <username>
SharePoint List to Power BI & Extending Power BI to SharePoint
This article explains to you that how we can use SharePoint list as a source for Power BI and also extending the Power BI report to embed on SharePoint.
I am following the same strategy in all my blogs. Reading a big paragraph is tough than a step by step points as it is easy to follow and remember.
Flow,
Pre-Requisites:
- SharePoint List URL
- SharePoint Credentials (On-premises or O365)
- Power BI Account
- Power BI Desktop
Follow the below steps,
Step 1: Create a SharePoint list as per your business needs. It can be simple or complex with calculated columns. In my case, I created a simple SharePoint list with some sample content as like below.
Step 2: Get the SharePoint URL where the list is created.
https://xxxxx.sharepoint.com/Training
Step 3: Open Power BI Desktop and click Get Data.
Step 4: Choose SharePoint Online List, if you use O365. Click Connect
Step 5: Pass the O365 SharePoint URL and credentials.
Step 6: Choose the list and click Load.
Step 7: It will load all the columns from the SharePoint list. Choose the visualization and map with the respective fields.
Step 8: Save and publish the report to Power BI online services
Step 9: Login to app.powerbi.com with your credentials.
Step 10: Open your report from your workspace where you published.
Embed the Report in SharePoint
So far, we created a report using SharePoint list. Now we are going to embed the report in SharePoint.
Follow the below steps.
Step 1: Open the report in app.powerbi.com
Step 2: If we have O365 E series license then we can use “Embed in SharePoint Online (preview)” option otherwise choose the below option.
Step 3: It will ask you to publish.
Step 4: Finally, it will generate URL and iframe code to place on any web page of SharePoint.
Step 5: Go to SharePoint page and edit where you want to embed this report.
Step 6: Click Insert and choose the Embed Code
Step 7: Copy and paste the iframe
Step 8: Save the page.
Share your comments below.
Related Post: