CppMax Homepage
Forum Home Forum Home > ReportMax > ReportMax
  New Posts New Posts RSS Feed - ReportMax Grouping Issue
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

ReportMax Grouping Issue

 Post Reply Post Reply
Author
Message
Matt View Drop Down
Newbie
Newbie


Joined: 15 Jul 2015
Status: Offline
Points: 4
Post Options Post Options   Thanks (0) Thanks(0)   Quote Matt Quote  Post ReplyReply Direct Link To This Post Topic: ReportMax Grouping Issue
    Posted: 15 Jul 2015 at 10:45am
Hey guys,

Was wondering if I can get some insight as to where I may be going wrong. I'm currently running some SQL in ReportsMax, and when previewing the report I have partially developed it does not group correctly. From my understanding and what I have noticed, when the group is repeated it will not appear until a new value is present; which is exactly what I do not want it to do. I've modified the SQL to attempt to combat this, which worked for another grouping issue I discovered, but doesn't fix this. Any ideas?

Thanks,
Matt

Back to Top
emadns View Drop Down
Admin Group
Admin Group


Joined: 06 Feb 2012
Status: Offline
Points: 134
Post Options Post Options   Thanks (0) Thanks(0)   Quote emadns Quote  Post ReplyReply Direct Link To This Post Posted: 15 Jul 2015 at 11:01am
What are the other grouping issue? What are you trying to do? Please make the ORDER BY field in the SQL statement to be the group DataField. If that is what you have done, then can you share the report template to support@cppmax.com along with your expectations?
Back to Top
Matt View Drop Down
Newbie
Newbie


Joined: 15 Jul 2015
Status: Offline
Points: 4
Post Options Post Options   Thanks (0) Thanks(0)   Quote Matt Quote  Post ReplyReply Direct Link To This Post Posted: 16 Jul 2015 at 4:05am
This is my SQL:

SELECT Credit_Details.Transaction_Date,
       Credit_Details.Account_Number,
       CreditAccounts.[Name],
       CASE Credit_Details.Doc_Type WHEN 'I' THEN 'Product Sales' ELSE 'Account Payments' END AS Doc_Type,
       Credit_Details.EAN,
       Credit_Details.Issue,
       Credit_Details.Trans_Des,
       Credit_Details.QTY,
       Credit_Details.Price,
       Credit_Details.Sales_Value,
       Credit_Details.VAT_Ind,
       Credit_Details.Trans_Time
  FROM LegacyData.dbo.Credit_Details Credit_Details
       INNER JOIN LegacyData.dbo.CreditAccounts CreditAccounts
          ON (Credit_Details.Account_Number = CreditAccounts.Account_Number)
GROUP BY Credit_Details.Transaction_Date,
         Credit_Details.Account_Number,
         Credit_Details.Doc_Type,
         CreditAccounts.[Name],         
         Credit_Details.EAN,
         Credit_Details.Issue,
         Credit_Details.Trans_Des,
         Credit_Details.Price,
         Credit_Details.QTY,
         Credit_Details.Sales_Value,
         Credit_Details.VAT_Ind,
         Credit_Details.Trans_Time

Essentially, I'm expecting it follow as suit:

- Date
-- Account Number
--- Doc Type
---- Details

--- Doc Type
---- Details

-- Account Number
--- Doc Type
---- Details

As so on, I'll email the report as requested. Unfortunately what it does though is (That I've noticed):

- Date
-- Account Number
--- Doc Type
---- Details

(If Doc Type is the same as previous echo of Doc Type do not echo; which I want it to echo to keep a consistent layout)
---- Details

-- Account Number
--- Doc Type (echos doc type because the value has changed)
---- Details
Back to Top
emadns View Drop Down
Admin Group
Admin Group


Joined: 06 Feb 2012
Status: Offline
Points: 134
Post Options Post Options   Thanks (0) Thanks(0)   Quote emadns Quote  Post ReplyReply Direct Link To This Post Posted: 16 Jul 2015 at 4:30am
1. Replace GROUP BY with ORDER BY. The report will do the grouping for you.

2. Do not place the DocType field in the Group Header. Create a Group Header for DocType, set it's data field to DocType but leave it blank. Place the Doctor field inside the Detail section above the Details fields and it will be repeated with every detail

Back to Top
emadns View Drop Down
Admin Group
Admin Group


Joined: 06 Feb 2012
Status: Offline
Points: 134
Post Options Post Options   Thanks (0) Thanks(0)   Quote emadns Quote  Post ReplyReply Direct Link To This Post Posted: 16 Jul 2015 at 4:33am
>> place the Doctor field inside...

I meant

Place the DocType field inside...

Back to Top
Matt View Drop Down
Newbie
Newbie


Joined: 15 Jul 2015
Status: Offline
Points: 4
Post Options Post Options   Thanks (0) Thanks(0)   Quote Matt Quote  Post ReplyReply Direct Link To This Post Posted: 16 Jul 2015 at 6:36am
That's perfect, thank you for your help!
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.06
Copyright ©2001-2016 Web Wiz Ltd.