Adding a new parameter to 'every sale dialog'

7 posts / 0 new
Last post
Markus Tanner
Adding a new parameter to 'every sale dialog'

Hi i added the projecttype to the standardtable of dsale in datacentral:

 

 

 ,isnull(case

 

 

  when s.project_id != 0 then (select (select top 1 name from =dv(dbowner);.PROJTYPE where ProjType_id=prj.type_idx) from =dv(dbowner);.project prj where prj.project_id = s.project_id)
 end,'=dv(string_not_available);') as [ProjectType]
 
which does display me the value of projecttype in every sale dashboard, fine so far
 
The i added a new parameter based on the table dsale where i've chosen this new field to display/value and sorting. 
 
But How do a get this new parameter to all may sale dashboards? i guess i need to link this new parameter somewhere to all dashboards? but where? i've looked into the row defintion and in the dashboards but the new parameter is not available.
 
-> Did you ever created a dadresses? i need to create a new adress dashboard based on some udef's and if someone has the basic sql for contact and person i don't need to do i from scratch.
Erik
Erik's picture
Include the new parameter in the datatable

You need to tell the datatable which parameters it should use. There are two ways of acheiving this:

1) Standard.
Go to the column tab in the datatable and click the parameter icon as shown below.

Press the icon to open the dialog for the parameter connection, and select the parameter you want to add. Click ADD.
Save the updated data table definition. Logg out/inn and the new parameter will appear everywhere this data table is used.

2) Inline.
This method gives better performance and is preferred especially if the query is working with large tables. Here is an example where all the parameters have been implementet as inline mostly for performance:

You will find the GUID of the parameter in the parameter dialog. Like this:

It can be usefull to test the inline parameter definition in the scripting engine tester. Go to System admin -> Analysis Dashboard -> Scripting Engine expression tester.
Input the selected value from the parameter you want to test and click Ajax parse. Use [ALL] to test the behavior when ALL is the selected option (if relevant).

Markus Tanner
Hi Erik

Hi Erik

That worked perfectly

another issue was the view for addresses any sql allready?

-> Did you ever created a dadresses? i need to create a new adress dashboard based on some udef's and if someone has the basic sql for contact and person i don't need to do i from scratch.

 And i have a general problem/question concerning contact/person interessts. I need to set a parameter for these interess fields. But since the interessts are one to many i don't have any idea how to set up the parameter?

in straight SQL my parameter would be like   where contact_id in (select contact_id form contactinterest where cinterest_idx in (PARAMETER1-x))

the problem might also be, that i want to choose not just 1 interesst, but many (interest 1-x) 

Any idea or hint?

Erik
Erik's picture
Include addresses and more...

This is how to include addresses for both persons and contacts. See the examples below:

-- Contacts with their addresses:
select
    c.contact_id as [Contact Id]
    ,c.name as [Contact]
    ,c.department as [Department]
    ,isnull(cty.name,'-') as [Country]
    ,isnull(adr.zipcode,'-') as [Zip]
    ,isnull(adr.city,'-') as [City]
    ,isnull(adr.zipcode,'-') as [State]
    ,isnull(adr.address1,'-') as [Address1]
from
    crm7.contact c
    left join crm7.country cty on cty.country_id = c.country_id -- Nationality
    left join crm7.address adr on c.contact_id = adr.owner_id -- Address
        and atype_idx = 1 -- Addresses for contacts only


-- Persons with their addresses:
select
    p.person_id as [Person Id]
    ,p.firstname + isnull(' ' + p.middleName + ' ',' ') + p.lastname as [Person]
    ,isnull(cty.name,'-') as [Country]
    ,isnull(adr.zipcode,'-') as [Zip]
    ,isnull(adr.city,'-') as [City]
    ,isnull(adr.zipcode,'-') as [State]
    ,isnull(adr.address1,'-') as [Address1]
from
    crm7.person p
    left join crm7.country cty on cty.country_id = p.country_id -- Nationality
    left join crm7.address adr on p.person_id = adr.owner_id -- Address
        and adr.atype_idx = 2 -- Addresses for persons only

 

This SQL may be used to create a data table for the Contact interest parameter:

select
    ContInt_id as [val]
    ,name as [disp]
    ,rank as [Rank]
from
    crm7.CONTINT
where
    deleted = 0

The parameter definition must then be created, and should look something like this:

 

And then you may want to include it as an inline parameter in the main data table like this:

For direct text copy & paste:
  and exists (select 1 from crm7.contactinterest cint where cint.contact_id = c.contact_id =param("id":"d8a9e0cf-0b45-4e97-aa37-4a6e6f61cd00","colref":"cint.cinterest_idx","output":"total","alloutput":"or 1=1");)

The GUID number (bolded) will be different on your system, so you must substitute the GUID with your own. You can find it in the parameter definition dialog (see screen shot further above).

Markus Tanner
Hi Erik

Hi Erik

Perfect, the package worked as intended and i could create, what i need for this customer. Even multiselections for interessts worked.

Just some tipps/ideas for the adresses:

I guess for a release we/you should include also a 'contact'-only table without person. The reason for this is, that when having multiple persons you also get several times the same company in your dataset. So when you create graphs or calculations on values per company (typical would be a udef like 'turnoverlastyear') then this results in a wrong statistic (3 persons on a company will get you 3 times the same turnover on this company).

Maybe it's easier to to have a company table with some totals on activities/sales +++ 

and one with persons where you count some totals on activities per person or project memberships, this could also be a person left join contact query

so that one not mix up company data with person data.

But as said the one you provided was perfect for me.

thx for your excellent support!!!

w.mak@tignl.eu
SaleType

Hi,
Do you also have an example how I can add the saletype to the script and use it also as parameter.
I don't get ik to work

Reg,
Wouter

Erik
Erik's picture
Sale type parameter

Hi,

The Sale type parameter is included in the dSale and dSaleNarrow datatables. If you wish to include it in your own datatables the easiest way of acheiving that is to copy the inline definition from dSale. It appears like this (use for copy & paste):

=param("id":"9f069714-64fb-44a9-b3b9-aaf7faee4999","colref":"s.saleType_id","output":"Total");
Log in or register to post comments