1. Home
  2. Forum
  3. Dashboard Platform
  4. How to Construct Expression from sql

How to Construct Expression from sql

Hi Teammate,

I need your assistance to come up with one samples step by step  for me, which i'm learning to create dashboard. I watch your video several times but i'm still lost.
How could i create a dashboard using cards widgets 'Total Sales Of Palm Oil'  but my users will be able to input three parameters  which is @Transaksistart (start date) Datepicker widgets, @Transaksiend (end dates) datepicker widgets and third parameter @kodpp (provinces) using mode  column (data source).
I'm  lost at Assign Data because i need to construct expression  column instead of  sql query. 


This is my samples from sql  query which I  use run it within my Report Server ,

SELECT sum(t_amaun)
,[dbo_ref_code].[kod_pp] AS [dbo_ref_code_kod_pp]
,[dbo_ref_code].[nama_pp] AS [dbo_ref_code_nama_pp]
,[dbo_txnlog].[t_t_tran] AS [dbo_txnlog_t_t_tran]
,[dbo_txnlog].[t_ref_no] AS [dbo_txnlog_t_ref_no]
,[dbo_txnlog].[t_keterangan] AS [dbo_txnlog_t_keterangan]
,[dbo_txnlog].[t_kkcr] AS [dbo_txnlog_t_kkcr]
,[dbo_txnlog].[t_kkdb] AS [dbo_txnlog_t_kkdb]
,[dbo_txnlog].[t_amaun] AS [dbo_txnlog_t_amaun]
,[dbo_txnlog].[t_kod_caw] AS [dbo_txnlog_t_kod_caw]
 FROM 
[dbo].[ref_code] AS [dbo_ref_code] Inner Join [dbo].[txnlog] AS [dbo_txnlog] ON  [dbo_ref_code].[kod_pp] = [dbo_txnlog].[t_kod_caw]
             where t_t_tran >= @TransaksiStart and t_t_Tran <=@Transaksiend
             and t_kod_caw like @kodpp
             and t_keterangan like '%Sales Of Palm Oil%'     
             group by t_kod_caw

I'm not sure of my request is within your support scope, but i trying my best to understand how to create a Dashboard but your Report Server & Report Designer is AWESOME.

Best Regards,

Hidzir Adam,
Kuala Lumpur


7 Replies
KM Kavitha Murugesan Syncfusion Team November 23, 2017 11:53 AM UTC

Hi Hidzir, 
 
You can achieve the equivalent functionality in design view for the provided query. ('%Sales Of Palm Oil%'). To achieve your requirement use Data filter of data source to filter the column which contains of ‘Sales Of Palm Oil’.  Please follow the below steps. 
 
  1. Open the data source tab and click the highlighted button to Open the Data filter window.
 
 
 
  1. Click Add button and Select column name where the Sales Of Palm Oil is present from drop down box.
 
 
 
  1. Select Contains option and enter the value which you want to filter (This is equivalent to like %word%  in SQL query).
 
 
 
  1. Click Update button to apply filter.
 
 
Now you can use card widget to show the data for ‘Total Sales of Palm Oil’. 
 
Please let me know for any concerns. 
 
Regards, 
Kavitha M. 
 


HA Hidzir Adam November 24, 2017 01:36 AM UTC

Hi Ms Kavitha,

Thank you for your guide & support, What i can conclude is that byusing Filter with 'contains'  don't need to use '%' wildcard. My next question  if i wanted to have interactive selection on the Dashboard for my card widgets to display results, i need to use parameter ? but when try it, my parameter with datepicker with range & dropdown (select states) widgets how to make the selection concurrently because when i excute my parameter for dropdown will excute first then only datepicker .

Best Regards,

Hidzir Adam
Kuala Lumpur 

PS Prince Solomon Bala Subramani Syncfusion Team November 24, 2017 12:45 PM UTC

Hi Hidzir, 
 
Please find the response for your query below 

Query 
Response 
Thank you for your guide & support, What i can conclude is that by using Filter with 'contains'  don't need to use '%' wildcard 
Yes filtering using the ‘contains’ don’t need to use the ‘%’ wildcard  
 My next question  if i wanted to have interactive selection on the Dashboard for my card widgets to display results, i need to use parameter ? 
but when try it, my parameter with date picker with range & dropdown (select states) widgets how to make the selection concurrently because when i execute my parameter for dropdown will execute first then only date picker . 
 You can  Configure “Dashboard Filter” in the designer to achieve your requirement.  
Using the Dashboard Filter you can remove the dropdown widget from the slave widgets of the date picker master widget . So  once you remove the drop down from the slave  when you select range in the date picker the drop down wont filter based on the range. 

To know more about the configuring Dashboard Filter please follow below link 


Also we would like to suggest to use the “Dashboard Parameter” Feature  so that you can  pass the parameter directly to the SQL Query .To execute Dashboard parameter through the code view of the Designer you can write and execute the custom SQL. To know more about writing custom SQL in the code view of the Designer please follow the below link 

To know more about the configuring Dashboard Parameter in the Dashboard Designer please follow below link 


Regards 
Prince Solomon B 


HA Hidzir Adam November 24, 2017 12:57 PM UTC

Hi Good Day Mr Prince Solomon B,

Thank you very for your updates, i would try it shortly.

Best Regards,

Hidzir Adam
Kuala Lumpur

PS Prince Solomon Bala Subramani Syncfusion Team November 27, 2017 10:59 AM UTC

Hi Hidzir, 

Most welcome . We wait to hear from you. 

Regards 
Prince Solomon B 


HA Hidzir Adam November 27, 2017 12:04 PM UTC

Hi Prince,

If i wanted  to create my own States maps widgets  of Selangor, how could go about on this matter ? i attach my states map, 
on my second question if i created a user e.g. 'Manager1'  then once this manager login into my Dashboard Server via mobile syncfusion , how could i limit  his selection of query only confine to his area data only.  What i need to do ? Each manager will need it own Data Source & separate widgets ? or i could still use within existing data source created.

Thank You, l'm looking forward on your reply.

Best Regards

Hidzir Adam
Kuala Lumpur

Attachment: SelangorMalaysia_359425e.zip

PS Prince Solomon Bala Subramani Syncfusion Team November 28, 2017 12:47 PM UTC

Hi  Hidzir, 

Please find the response for your Query below 

Query 
Response 
If i wanted  to create my own States maps widgets  of Selangor, how could go about on this matter ? i attach my states map,  
We are not providing any custom shape files. You can get shapes from open source vendors and you can use it in the map widget. 
on my second question if i created a user e.g. 'Manager1'  then once this manager login into my Dashboard Server via mobile syncfusion , how could i limit  his selection of query only confine to his area data only.  What i need to do ? Each manager will need it own Data Source & separate widgets ? or i could still use within existing data source created. 
 
You can achieve your requirement in a single dashboard report itself by using the user based filter option without creating multiple widgets for multiple users. User based filter allows you to limit the data view of the published dashboard based on the logged in user.

Please find the below steps for creating a simple user based filter which matches your requirement. 
  1. Create required users in the dashboard server  , refer the UG documentation for creating users in dashboard servers.
    https://help.syncfusion.com/dashboard-platform/dashboard-server/administration/manage-users#add-new-users
  2. Create a normal dashboard report with required data source and widgets using Syncfusion dashboard designer application.
  3. In dashboard designer application, log in your dashboard server account. After successful login the user filter option will be enabled under server menu as shown in the below image:
  4. Click the button , now the User filters window will be launched.
  5. Click on the Add filter button and select the data column.


    In this example , I am choosing ShipCountry as the filter column.
  6. Now select the fields for the users in which you allow them to view.

    In this example , I am applying filters for Argentina and Australia alone to the user named “Manager 1”.

    Belgium and brazil are applied for the user named “Manager 2”.


    Similarly choose the filters for the remaining users and save.
  7. Use the dashboard preview as option to ensure the filters .

    In this example I am previewing as Manager 1.

    Now the filters will be applied and the user can see only the data based on filter:
Please refer the UG documentation for more details on user filters and it’s full features from the below link:
https://help.syncfusion.com/dashboard-platform/dashboard-designer/compose-dashboard/configuring-user-based-filter/using-user-filter-window


 

Regards 
Prince Solomon B 


Loader.
Up arrow icon