Design of report that has rows down and columns across

[Former Member]

I am not sure how to design this report. There are 30 columns normal with rows going down but then 3 columns of data for each month indicated in a date range (up to 2 years) that a user selected and is passed into report.  These "amounts" will be summed to the level of the rest of the columns in that row including that particular date (month/year). I looked at a crosstab but I don't think it will work for that many columns going across.  I am wondering if I need to put it in a stored procedure but then from what I read SAP does not support multi valued parameters passed in to stored procedures (and this report uses the multi valued parameters).  Any suggestions please?

I have attached a sample of the output (open with Excel to get best format).  The 201504 columns (AA) to 201603 (BJ) is the date range the user entered.  I can take start date and code for 2 years (even though user did not select all those months).

sample.txt.zip

[Former Member · Accepted solution]

Thank you Noel.  I am trying it to get it to work. It is proving to be challenging to get all the columns and exactly as the user wants it.  This is replacing their current report and they have been quite adamant that the column headers be exactly what they have today.

[nscheaffer · Accepted solution]

I apologize. I thought this was a WebI report.

I'll see if I can figure this out in in Crystal Reports.

Noel

[nscheaffer · Accepted solution]

I have never done a crosstab in Crystal. Perhaps this video will help you out or spark an idea that gets you going.

https://www.youtube.com/watch?v=Vp3_Vy1eSXo

Noel

[abhilash_kumar · Accepted solution]

Hi Laurie,

A crosstab will not work for this report! Mainly because this involves multiple fields being used as columns.

Like you said, you would need to code this to return data for two years (laid out in columns). You can then suppress the date columns you don't need based on the date range selected by the user.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash/Noel,

I have gotten the columns going across using formulas but for each year month it is on a separate row. I tried to see if I could add another group and just sum those fields up to get 1 row across but the formula is not available to sum.  I even thought of seeing if I could send the data to a subreport and group/sum there but I'm not sure if that is the right way either.  Any ideas please?  If not, I guess my only other option is to write a stored procedure and then have to do a subreport any way because of the multi value parameters.  I was trying to avoid that but maybe that is best option? THANK YOU BOTH for your suggestions!

[DellSC · Accepted solution]

I usually do this sort of thing using a command.  What type of database are you connecting to and can you post the query the query that Crystal has created for your report along with info about which fields are used for columns and which are used for rows?  What are your parameters?  I should be able to come up with a query you can use.

Using this technique, you shouldn't need to use a subreport to get the dynamic parameters.  You can used the technique I outline in my blog here:  to configure the parameters.

-Dell

[Former Member · Accepted solution]

I like to use Commands as well but the only problem is that there is a date range parameter. The users really like the way it works and I wasn't able to get a date range to work with a command (and I read that it won't but if you know a way around that please let me know!).  But I am wondering if I can have the main report just pull the parameters and then pass to a subreport that is a command?  I don't know - I am trying anything I can.  It is an Oracle database. There are more parameters that are selectable by the users but are not displayed via show sql.  Also in the query but through the record selection:

(not HasValue({?JURISDICTION}) OR {SDR_SWITCH_USAGE_REPORT_P.JURISDICTION} = {?JURISDICTION}) and

(not(hasvalue({?USAGE_PERIOD}))

or {SDR_SWITCH_USAGE_REPORT_P.USAGE_THRU_DATE} <= maximum({?USAGE_PERIOD})) and

(not(hasvalue({?USAGE_PERIOD}))

or {SDR_SWITCH_USAGE_REPORT_P.USAGE_FROM_DATE} >= minimum({?USAGE_PERIOD})) and

(not HasValue({?USAGE_TYPE}) OR {SDR_SWITCH_USAGE_REPORT_P.USAGE_TYPE} = {?USAGE_TYPE}) and

(not HasValue({?DIRECTION}) OR {SDR_SWITCH_USAGE_REPORT_P.SAMS_DIRECTION_IND} = {?DIRECTION}) and

(not HasValue({?STATE}) OR {SDR_SWITCH_USAGE_REPORT_P.STATE} = {?STATE}) and

(not HasValue({?ATT_ENTITY}) OR {SDR_SWITCH_USAGE_REPORT_P.ATT_ENTITY} = {?ATT_ENTITY}) and

{SDR_SWITCH_USAGE_REPORT_P.CO_CODE} IN Split(trim(Uppercase({?CO_CODE})),",") and

{SDR_SWITCH_USAGE_REPORT_P.BILLED_MONTH} = {?BILLED_MONTH} and

(not HasValue({?SUPPLIER_TYPE}) OR {SDR_SWITCH_USAGE_REPORT_P.SAMS_SUPPLIER_TYPE} = {?SUPPLIER_TYPE})

The sql:

SELECT "SDR_SWITCH_USAGE_REPORT_P"."BILLED_MONTH", "SDR_SWITCH_USAGE_REPORT_P"."CO_CODE", "SDR_SWITCH_USAGE_REPORT_P"."ATT_ENTITY", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_SUPPLIER_CODE", "SDR_SWITCH_USAGE_REPORT_P"."STATE_LVL_CO_CODE", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_CO_NAME", "SDR_SWITCH_USAGE_REPORT_P"."STATE", "SDR_SWITCH_USAGE_REPORT_P"."NON_MECH_IND", "SDR_SWITCH_USAGE_REPORT_P"."BILL_BOOK_ONLY_IND", "SDR_SWITCH_USAGE_REPORT_P"."TOA", "SDR_SWITCH_USAGE_REPORT_P"."FG", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_DIRECTION_IND", "SDR_SWITCH_USAGE_REPORT_P"."USAGE_TYPE", "SDR_SWITCH_USAGE_REPORT_P"."RATE_ELEMENT", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_RATE_ELEMENT_DESC", "SDR_SWITCH_USAGE_REPORT_P"."ROUTING_IND", "SDR_SWITCH_USAGE_REPORT_P"."ADW_TRANSPORT_RATE_ELEM_DESC", "SDR_SWITCH_USAGE_REPORT_P"."TRANSPORT_RATE_ELEMENT_IND", "SDR_SWITCH_USAGE_REPORT_P"."USAGE_RATE", "SDR_SWITCH_USAGE_REPORT_P"."MILEAGE_QUANTITY", "SDR_SWITCH_USAGE_REPORT_P"."BILL_STRUCTURE", "SDR_SWITCH_USAGE_REPORT_P"."VOIP_USAGE_IND", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_SUPPLIER_NAME", "SDR_SWITCH_USAGE_REPORT_P"."USAGE_FROM_DATE", "SDR_SWITCH_USAGE_REPORT_P"."USAGE_THRU_DATE", "SDR_SWITCH_USAGE_REPORT_P"."JURISDICTION", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_SUPPLIER_TYPE", "SDR_SWITCH_USAGE_REPORT_P"."USAGE_QUANTITY", "SDR_SWITCH_USAGE_REPORT_P"."ORIG_USAGE_AMT", "SDR_SWITCH_USAGE_REPORT_P"."UPDATED_USAGE_AMT", SAMSPGM.DETERMINE_NORMALIZED_SUPPLIER ("SDR_SWITCH_USAGE_REPORT_P"."CO_CODE",
"SDR_SWITCH_USAGE_REPORT_P"."SAMS_SUPPLIER_CODE","SDR_SWITCH_USAGE_REPORT_P"."STATE","SDR_SWITCH_USAGE_REPORT_P"."BILLED_MONTH",
SAMS_SUPPLIER_NAME)
FROM   "SAMS"."SDR_SWITCH_USAGE_REPORT_P" "SDR_SWITCH_USAGE_REPORT_P"
WHERE  "SDR_SWITCH_USAGE_REPORT_P"."CO_CODE"='5173' AND ("SDR_SWITCH_USAGE_REPORT_P"."BILLED_MONTH">='201505' AND "SDR_SWITCH_USAGE_REPORT_P"."BILLED_MONTH"<='201506')
ORDER BY "SDR_SWITCH_USAGE_REPORT_P"."ATT_ENTITY", "SDR_SWITCH_USAGE_REPORT_P"."CO_CODE", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_SUPPLIER_CODE", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_SUPPLIER_TYPE", "SDR_SWITCH_USAGE_REPORT_P"."STATE_LVL_CO_CODE", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_CO_NAME", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_SUPPLIER_NAME", "SDR_SWITCH_USAGE_REPORT_P"."STATE", "SDR_SWITCH_USAGE_REPORT_P"."NON_MECH_IND", "SDR_SWITCH_USAGE_REPORT_P"."BILL_BOOK_ONLY_IND", "SDR_SWITCH_USAGE_REPORT_P"."TOA", "SDR_SWITCH_USAGE_REPORT_P"."FG", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_DIRECTION_IND", "SDR_SWITCH_USAGE_REPORT_P"."JURISDICTION", "SDR_SWITCH_USAGE_REPORT_P"."USAGE_TYPE", "SDR_SWITCH_USAGE_REPORT_P"."SAMS_RATE_ELEMENT_DESC", "SDR_SWITCH_USAGE_REPORT_P"."ROUTING_IND", "SDR_SWITCH_USAGE_REPORT_P"."TRANSPORT_RATE_ELEMENT_IND", "SDR_SWITCH_USAGE_REPORT_P"."USAGE_RATE", "SDR_SWITCH_USAGE_REPORT_P"."MILEAGE_QUANTITY", "SDR_SWITCH_USAGE_REPORT_P"."BILL_STRUCTURE", "SDR_SWITCH_USAGE_REPORT_P"."VOIP_USAGE_IND"

So the records come out with bill month as a row but I need to have the orig usage amt, updated_usage_amt, and usage_qty for each bill month across the top as a column.   So if 4 months of data it will produce 12 columns across. Month 1 usage qty, month 1 orig amt, month 1 updated, month 2 usage qty, month 2 orig amt, month 2 updated, etc.  I have written a formula to get the rows across but it still lists for each row of data and for row 1 the month 1's will display (month 2 and 3 zero's), then row 2 will have month 1 zero's and 2 with data and 3 with zero's, etc.).  I thought maybe I could sum or max the formula columns but it won't let me do that either. 

[DellSC · Accepted solution]

Out of all of these fields, which ones will actually appear on the report?

Also, to do a date range in a command, you actually have to have two parameters - one for start date and one for end date.  The other thing is that you can't have optional parameters in a command (or stored procedure...)  So, what I do is use the technique I outline in my blog post that I linked to above to set each dynamic parameter based on its own command that includes "*ALL" at the top of the list  (that's what the asterisk is for) and that defaults to *ALL.  These extra commands are used ONLY in the dynamic parameters - no fields from them are used on the report.

-Dell

[Former Member · Accepted solution]

They are allowed up to 24 months on the output.  I took starting month as 201505.  I will attach sample output.

[Former Member · Accepted solution]

Reattaching file. It will not let me upload excel so I converted it to a text file. Doesn't look like the Excel file but you can see columns,

across report example output.txt.zip

[Former Member · Accepted solution]

THANK YOU FOR LOOKING AT THIS!

[DellSC · Accepted solution]

What are you grouping on in your report?  Actually, if you change the extension on your .rpt file to ".txt", you can attach it here.

I have the logic figured out, I just need to make sure I have the correct fields in the correct places.

Thanks!

-Dell

[Former Member · Accepted solution]

It groups on all records except for the 3 fields being summed across. There is a "down"version of this report (users have a choice for down or across) that runs fine which has the billed_month in the group by but I took it out of the "across" because I don't want a new row for each billed_month.  So the order by I believe has all the values used in the grouping that is in the report. AWESOME!

[DellSC · Accepted solution]

Ok, I think I've got this right - you may still have to tweak it a bit, but the attached SQL should give you the idea of how to do this (I use this technique frequently to pivot data).  ALL of the sums are done in the query - nothing is required in Crystal.

I put all of the parameters except {?USAGE_PERIOD} into the SQL.  I assumed they were all multi-select.  If they're not, change "in" to "=" and put single-quotes around any parameter the provides a string value.  Also, please see my blog for info about working with parameters in a command and how to do dynamic parameters in a command.

The first part of the query is a 'Using" clause that will build a list of the months based on the start and end dates.  I assumed that the BILLED_MONTH value is a number when I created this.  If it isn't you'll have to look at converting the data to strings.  Any month that is outside of the selected date range will have a value of 0.  So, you can use that value to suppress data columns that are not in the date range.  For example, if the user selects a 12 month range, then months 1 through 12 will have non-zero values and months 13 to 24 will all be 0.

Please let me know if you have any questions.

-Dell

Message was edited by: Dell Stinnett-Christy

UpdatedSQL.txt.zip

[Former Member · Accepted solution]

Hi Dell,

This sql is amazing! THANK YOU!  One question - do you see a row for each year month when you run your version?  For example if I do 201504 and 201505 I will have a row for each but it populates the right amount in each column. Do I need to do a select off this sql and sum to get 1 row? I didn't know if you see the same thing or maybe I may missing something?  Thanks again!

[DellSC · Accepted solution]

It shouldn't.  However, here is how I might handle that:

1.  Create a group for each row that will display - you can do this by concatenating the values of all of the non-month fields.

2.  Suppress the group header and the details sections.

3.  Put your data in the group footer section.  Instead of placing the number fields in there, place a summary with either the sum or the max of the number.

-Dell

[Former Member · Accepted solution]

I agree! I was surprised and see nothing in the output that is causing it to display the multiple rows other than the summed data which the grouping of all the non summed should handle.  Thank you again! 

[nscheaffer]

I think a cross tab is the way to go for your situation. The number of columns won't be a issue from technical perspective.  Here is what it should look like using the eFashion universe.

To add multiple columns in the field of the crosstab I put Quantity Sold on first and then had to insert a column to the right and then drag Sales Revenue to it.

Let us know how it goes for you.

Noel

[Former Member]

Is it possible that you could attach the .rpt you created please? I do not know how you got rid of all the totals and repeated the state.   My attempt - the first column only lists once and on the change. Then for each row I get a total. I suppressed the grand total but I did not see an option to suppress at all the levels. Your output looks SO MUCH better than mine!  I want a crosstab that looks like an Excel file. Well my users do.  Thanks again!