Absent Member.
Absent Member.
1751 views

SM7.10 running queries/dashboards using date range prompts

Jump to solution
We have a need to create Dashboards (using queries) for prompted Date Ranges. We are trying to use a Wizard to fulfill this requirement. Are we going down the right path? Is it possible? If not using Wizards, is there another way to prompt for user input?
We can create the onetime dashboard/query using the Search criteria of Closed After and Closed Before - but once saved - we can't change the date range next time we run it.
Any help would be wonderful!

Lara Hieke
lara.hieke@maritz.com
0 Likes
1 Solution

Accepted Solutions
Fleet Admiral Fleet Admiral
Fleet Admiral
Hmm... well, first, SM7 is not _really_ a reporting tool. From experience and from reading others with the same issue, when you allow users the ability to create complicated queries, you'll experience system-wide performance degradation. We had users who wanted to use SM7's Views and Favorites as a replacement for their reports. There was one View - All Incidents - that a group of users said they _NEEDED_ to do their jobs. We built it on a test system and did performance evaluation. When the record set got large (2000+ records) the performance for ANYONE in the system slowed to a crawl. When it got _really_ large (5000+ records) the system crashed and required a full reboot.

SM7 isn't meant to be a replacement for reports. The Views and Favorites are useful and can be used to quickly access records that need to be handled, but should _NOT_ be used as a replacement for a true reporting tool.

_BUT_ if you _need_ to have something like this, I would avoid using the Views, Favorites and Dashboards, and just have your users perform searches through SM7.1x's search screens.

From the Search Screen, the users can enter any criteria they need, and there's less system-wide degradation, even on large record sets... just the individual user experiences a slowdown.

If that's not enough, you can customize the search screen and pre-build some query parameters. For example, if you look at the Search Screen for Change records, you can see the "More Options" tab, with checkboxes. These checkboxes are pre-built queries that you can edit or create by editing the SearchConfig records for that table.

Use Database Manager and search for Table="SearchConfig". Then, search the Search Configuration records for the "cm3r" table. OOB, the pre-defined queries search for Change records that:

is high priority
is open
is closed
is scheduled to start within a week

When a user clicks any of these boxes, the system uses the value in the "Query" entry to determine which records to return.

You could edit these and build your own to make searches a little easier for users... You could make Date Range queries using expressions that evaluate off the tod() function...

in the last day: tod() - '1 00:00:00'
in the last week: tod() - '7 00:00:00'
in the last month: month(tod()) - 1

You could do User Specific queries using expressions set by the login.DEFAULT record and system/global variables:

assigned to me: assignee.name=operator()
assigned to my group: assign.dept=$lo.pm.assignments

etc...

Then, the users would fill in some search criteria, click the checkbox(es) that they want, and perform a search...

Those are just ideas and would need to be tailored, but you may want to consider that route.

View solution in original post

5 Replies
Fleet Admiral Fleet Admiral
Fleet Admiral
Can I get some clarification on what you're trying to do?

You (as a system adminstrator) can create a view that uses dynamic date ranges by editing the View record. Using Database Manager, you can search the "inbox" table, and in the "Query definition" tab, in the "Query" field, you can use expressions to set your date range.

For example, if you want to see all changes closed in the last week, your query would be:

close.time>tod()-'7 00:00:00' and close.time
That uses the tod() function (which returns the system's current date and time; NOW) and shows all the changes that were closed after 7 days before NOW and all changes that were closed before NOW.

You can use those same expressions in stored queries, if you don't want to use a View... the query expression works the same way.

But you say you're using a wizard? Do you want the users to have to manually enter dates every time? If so, that kind of defeats the purpose of setting a date range, since they'd give before and after dates manually...

So I guess I'm a little unclear on what you're trying to do.
0 Likes
Absent Member.
Absent Member.
Thank you, that is helpful, but not exactly what I'm looking for. The reason we were trying to use Wizards is because we were prompting the user for data (in this case dates).

We currently have hundreds of reports that we use in our current system (Heat - we have not gone live yet with SM)we are trying to cut down the number being recreated by using dashboards and views and combining similar reports. Some of the reports have like requirements but one group wants to run it daily, one group wants to run it weekly, some groups want to run it monthly. Some groups want to run it weekly but not every Monday - they might run the first week, second week, and third week all in the same day - we we need to be flexible with the dates - where they enter the beginning date and end date.
Wizards may not be the answer - but what I'm looking for is a way to prompt the user for data before they run the report - in this case it is dates, in another case it may be the assignment group.
I hope this helps clerify.
(example: Tickets closed by a group for date range)
0 Likes
Absent Member.. Absent Member..
Absent Member..
Lara,

You want to use daterange tools such as those found in Crystal Reports like lastfullmonth, weektodate, quarters, etc correct?

What you can do is use the login.DEFAULT format control to setup variables that contain expressions to hold these ranges. Then in your dashboard queries you reference the variables.

For example:

$mm=month(tod());$yy=year(tod());if ($mm=1) then ($lm=12;$ly=$yy - 1) else ($lm=$mm - 1;$ly=$yy);$G.lastfullmonth="open.time >= '"+$lm+"/01/"+$ly+"' and open.time <= '"+$mm+"/01/"+$yy+"'"
0 Likes
Fleet Admiral Fleet Admiral
Fleet Admiral
Hmm... well, first, SM7 is not _really_ a reporting tool. From experience and from reading others with the same issue, when you allow users the ability to create complicated queries, you'll experience system-wide performance degradation. We had users who wanted to use SM7's Views and Favorites as a replacement for their reports. There was one View - All Incidents - that a group of users said they _NEEDED_ to do their jobs. We built it on a test system and did performance evaluation. When the record set got large (2000+ records) the performance for ANYONE in the system slowed to a crawl. When it got _really_ large (5000+ records) the system crashed and required a full reboot.

SM7 isn't meant to be a replacement for reports. The Views and Favorites are useful and can be used to quickly access records that need to be handled, but should _NOT_ be used as a replacement for a true reporting tool.

_BUT_ if you _need_ to have something like this, I would avoid using the Views, Favorites and Dashboards, and just have your users perform searches through SM7.1x's search screens.

From the Search Screen, the users can enter any criteria they need, and there's less system-wide degradation, even on large record sets... just the individual user experiences a slowdown.

If that's not enough, you can customize the search screen and pre-build some query parameters. For example, if you look at the Search Screen for Change records, you can see the "More Options" tab, with checkboxes. These checkboxes are pre-built queries that you can edit or create by editing the SearchConfig records for that table.

Use Database Manager and search for Table="SearchConfig". Then, search the Search Configuration records for the "cm3r" table. OOB, the pre-defined queries search for Change records that:

is high priority
is open
is closed
is scheduled to start within a week

When a user clicks any of these boxes, the system uses the value in the "Query" entry to determine which records to return.

You could edit these and build your own to make searches a little easier for users... You could make Date Range queries using expressions that evaluate off the tod() function...

in the last day: tod() - '1 00:00:00'
in the last week: tod() - '7 00:00:00'
in the last month: month(tod()) - 1

You could do User Specific queries using expressions set by the login.DEFAULT record and system/global variables:

assigned to me: assignee.name=operator()
assigned to my group: assign.dept=$lo.pm.assignments

etc...

Then, the users would fill in some search criteria, click the checkbox(es) that they want, and perform a search...

Those are just ideas and would need to be tailored, but you may want to consider that route.

View solution in original post

Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

Hi, I need some help on same context but it is little difference. Question is,  Is it possible to get closed incidents in the range of dates(after closed and before closed) by rest service.

i have searched lot for same but i am not able to find it, It will be needfull if you help me out on the same.

Tags (2)
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.