1 of 1 people found this helpful
CONTHIST is the table you want.
Where are you building this report/query/? There's an excellent DASHBOARD tool built into GoldMine that this might be a perfect job for.
Thank you very much for your answer, I appreciate that!
Anyway, at the moment I manage create a matrix table like I want from DASHBOARD.
I just have a one problem with that, how can I sort that list weekly?
I know it will be in parameter when we set up "Data Source", but I am having difficulty to create set up.
My goal is to have the report like my previous post and have drop down button to select which week that I want to select.
If you have already built the query that pull the needed data from GM database you can use power bi desktop to create your dynamic report the only con with it is that you can't go from it to the specific contact record in GM which you can do with GM dashboard.
Power Bi Desktop can be downloaded fro Microsoift. It's free and very power full with lots of filtering tools and drill down options.
You'd probably want to make a dropdown with the week numbers and then add a runtime parameter to your data source that incorporates the week number into a comparison to ONDATE.
Of course, now we're getting into minutiae of dashboard design and plenty of assumptions must be made about what you've already built in there, so it's tough to offer much further guidance here on a forum. If you'd like to contact me directly, I can possibly further assist with (or simply DO) your dashboard work.
I am having difficulty to set up "runtime parameter".
I still do not understand how can connect the dropdown with weeks with the data souce, like the one in "Activity Dashboard" has
"quarter" or "yearly".
Could you please explain about this?
ps. do you want me to create a new thread for this question?
It is somewhat complicated and may be a bit beyond this forum format to fully discuss.
In short, you are basically making a variable that represents a piece of the where clause.
That variable clause then gets values dropped into it via events happening on the drop-down box (such as the user changing the selected value.)
You might want to clone an existing dashboard and then take a look at the guts to get a feel for the mechanics of it all.
The most annoying problem you'll probably face is producing a drop-down with the numbers 1 - 52 in it... ;)
I already copy paste the "Activity Summary Dashboard" and play with the runtime parameter.
However, I still cannot figure it out how table can connect with drop down list button.
What I did was I delete the drop down list (year) and create exact new one (same detail in properties, data binding and source) but it still does not work.
Is there any extra step that I have to do to set it up?
1 of 1 people found this helpful
For creating a drop down list of the week in the year You should create a new manually data source that will give you the numbers from 1 to 53.
The query would be something like this
select 1 as text,1 as value UNION ALL
select 2,2 UNION ALL
select 3,3 UNION ALL
select 4,4 UNION ALL
select 5,5 UNION ALL
select 6,6 UNION ALL
select 7,7 UNION ALL
select 8,8 UNION ALL
select 9,9 UNION ALL
select 10,10 UNION ALL
select 11,11 UNION ALL
select 12,12 UNION ALL
select 13,13 UNION ALL
select 14,14 UNION ALL
select 15,15 UNION ALL
select 16,16 UNION ALL
select 17,17 UNION ALL
select 18,18 UNION ALL
select 19,19 UNION ALL
select 20,20 UNION ALL
select 21,21 UNION ALL
select 22,22 UNION ALL
select 23,23 UNION ALL
select 24,24 UNION ALL
select 25,25 UNION ALL
select 26,26 UNION ALL
select 27,27 UNION ALL
select 28,28 UNION ALL
select 29,29 UNION ALL
select 30,30 UNION ALL
select 31,31 UNION ALL
select 32,32 UNION ALL
select 33,33 UNION ALL
select 34,34 UNION ALL
select 35,35 UNION ALL
select 36,36 UNION ALL
select 37,37 UNION ALL
select 38,38 UNION ALL
select 39,39 UNION ALL
select 40,40 UNION ALL
select 41,41 UNION ALL
select 42,42 UNION ALL
select 43,43 UNION ALL
select 44,44 UNION ALL
select 45,45 UNION ALL
select 46,46 UNION ALL
select 47,47 UNION ALL
select 48,48 UNION ALL
select 49,49 UNION ALL
select 50,50 UNION ALL
select 51,51 UNION ALL
select 52,52 UNION ALL
I would add to it this week and last week
Insert into your dashboard a drop-down list which is data source should be the data source created by the above query.
You will have to add to the drop-down list an event that will update the chart or table view when you change the week in the drop-list.
This event should trigger runtime parameter in the query behind the table view or chart.
As I have wrote above you will be able to do it easier with power bi desktop.
I gave you here only the basic of what you should do.
Send me your main query and I will try to help you.
Anyway the runtime parameter should be something like
The question is do you have it to start it with AND
From my experience the best way to know what query is running is to run profiler and see what query is running and change things so GM will set the query in the right way.
Otherwise you are like a blind man.
Thank you for your reply!
Good news! I managed to fix my query and create an event to connect the table with the drop down list.
However, when I try to do exact the same thing with the chart, it does not work.
My query for the chart is:
SELECT userid, count(*) as issued from conthist
WHERE srectype = 'A'
AND datepart(ww, ondate) = datepart(ww, getdate())
GROUP by userid
ORDER by issued desc
With RUNTIME parameter:
datepart(ww,ondate) = <<VALUE>>