Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
Piush

Awaiting Email Confirmation
Registered:
Posts: 6
Reply with quote  #1 
Hi hoping someone can help me on something similar to a previous post titled "Grid from %SelectSQL" that I'm trying to achive.  I'm trying to get my grid to display values in the first column (using field in table) and display values in the second column (SQL calculation based on value of first column). Example:

Table is eEvent

Column1: List of all unique "eUserName" based on eMapName, eToStage etc.
Column2 (alias): Sum of unique eFolderID based on value of column1 for each row.

Seems like it should be a basic GROUP and COUNT but I'm finding the grid a little restrictive!
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #2 
Write out the SQL, and then apply that to the grid.

If it's a struggle getting the SQL right - don't blame the grid, however!

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
Piush

Awaiting Email Confirmation
Registered:
Posts: 6
Reply with quote  #3 
No... my SQL is fine:

select eUserName, count (eUserName) AS cntUser
from dbo.eEvent
where eUserName <> '' and eMapName = 'Trinicom Request'
Group By eUserName

...executes fine in SQL Studio.

Initially I tried to insert the code into tab 'Grid', field 'Row(s)'... no luck.  Then tried to insert directly into  tab 'Columns', field 'Column name' as indicated in the post titled "Grid from %SelectSQL" by 'tspafford'... no luck.  Then altered the statement as suggested with "%%" and "%'" but still nothing!

Am I making a 
fundamental mistake somewhere?
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #4 
Yes, you are making a mistake, mainly by making it far more complicated than it needs to be. Just use the 'normal' way to set up a grid. That thread was describing an extreme hack.

The grid properties should be:
Tables: eEvent
Rows: eUserName <> '' and eMapName = 'Trinicom Request'
Group By eUserName

(personally I would reverse the conditions as the DBMS may evaluate them in order, and that could take a long time)

Columns:
eUserName
count (eUserName) AS cntUser

I hope it is reasonably clear how the Designer then builds the SQL to populate the grid. This is what I mean by writing out the SQL, then populate the grid properties with the required SQL.

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
Kal

New Member
Registered:
Posts: 3
Reply with quote  #5 

Do the SQL functions Grouping Sets or Rollup work work within the Grid.
Group By, Count and Count Distinct seem to work fine

I am currently getting the following error message:

Failed to process 'eExecuteSQL' Metastorm engine Database Connector request. ErrorCode: '-2147217900'. Description: '[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'SETS'.'. Source: 'Microsoft OLE DB Provider for ODBC Drivers'. SQL State: '37000'. NativeErrorCode: '102' Procedure GetRecordSet call failed.  Position: 360

I have tried both the below
(drpDepartment <> '') GROUP BY GROUPING SETS ((drpDepartment, drpMenu1Level1),(drpMenu1Level1),())


(drpDepartment <> '') GROUP BY ROLLUP (drpDepartment, drpMenu1Level1)

0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #6 
I have no idea what it even is. Anything that can be represented as a single column can be entered as such. Beyond that, I suggest using a view and showing that in the grid.
__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!